set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[kaipiao]
@zjm varchar(20),@dwbh varchar(20),@id int,@select int,@user_no varchar(50),@dept_id varchar(50),@user_priv varchar(50)
AS
/*
外部客户开票选择商品，select 后面字段修改的时候，顺序不要变， as及后边的表示不要改，字段数量也不要改变
*/
if (@id=0 and @select=0)--正常开票zjm
begin
select top 190 a.spid,a.spbh,a.spmch,replace(a.shpgg,'#','号') as shpgg,a.dw as dw, d.cxxx as cxxx,
case when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)) end )
/a.jlgg>10 then '10件以上'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)) end )/a.jlgg<=10 and (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg>=1 then '1到10件'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)) end )/a.jlgg<1 then
case when left(a.spbh,1) in ('Z','J','P') THEN convert(char(10) ,b.hwshl)
when (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0))<0 then '0.00'
else convert(char(10),(b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0))) end
end as kcshl,a.hshsj,a.shpchd, 'cxxx' as baozhiqi,a.pick_pack_num,
case when i.tejia is not null then i.tejia
else
case when h.diaocdj is not null then h.diaocdj ---特殊售价
when c.kehudengji='A' then
case when a.fukfs in ('A1','B1','C1') and c.huikfs like '现金%' then a.xjj
when a.fukfs in ('A1','B1','C1') and c.huikfs like '承兑%' then a.cdj
else a.cdj end
when c.kehudengji='b' then
case when a.spid in (select spid from xianshousp where is_xianshou='是') then a.lslsj
else a.xjj end
when c.kehudengji='c' then
case when a.spid in (select spid from xianshousp where is_xianshou='是') then a.lsydj
else a.lslsj end
when c.kehudengji='d' then
case when a.spid in (select spid from xianshousp where is_xianshou='是') then a.yljgj
else a.yljgj end
when c.kehudengji='Y' then case when a.zydj<>0 then a.zydj else a.xjj end
else ceiling(b.chbdj*102)/100 end
end as diaocdj ,
a.zbz hwshl,a.pick_pack_num pp,'b' as aa ,0 as bb,a.jlgg,
case when isnull(w.numlimit,0)<b.hwshl and isnull(w.numlimit,0)<>0 then w.numlimit--------------提取spwh中商品的限售量2009-05-04-17:00
else (b.hwshl-b.bkxshl-isnull(e.shl,0)) end as shl,a.jxsw,
'是' as xs,case when a.pizhwh<>'' then a.pizhwh else a.spmch end as pizhwh
from sk1.hnhb.dbo.spkfk a,hwsp b ,sk1.hnhb.dbo.mchk c ,cxxx d,cnshl e ,tejia h ,sk1.hnhb.dbo.dzsw_tjb i,spwh w
where a.beactive='是' and a.spid*=h.spid and c.dwbh*=h.dwbh
and a.spid not in (select spid from spwh where isxs='否') and a.shpchd not like '%深圳市生命力%' and (a.zjm like '%'+@zjm+'%' )
and a.spmch not like '%(停)%' and a.spid=b.spid
and (c.danwbh=@dwbh or c.dwbh=@dwbh) and b.hw='HWI00000053'
and a.spid*=w.spid------------------------------------------链接spwh表
and a.spid*=d.spid and a.spid*=e.spid and a.spid*=i.spid and i.kaiszxrq<=getdate() and i.zhongzzxrq >=getdate()
/*经营范围*/
and ((a.spbh like '%K' and c.xsdb='是') or a.spbh not like '%K') ---蛋白同化剂
and ((a.is_mhj='是' and c.is_mhj='是') or a.is_mhj<>'是') ---销售麻黄碱类2009-05-13
and ( (a.is_yds='是' and c.is_yds='是') or a.is_yds<>'是' ) ---胰岛素
and ( (a.spbh like '%J' and c.is_wf='是') or a.spbh not like '%J' ) ---二类精神
and ( (a.is_rs='是' and c.is_rs='是') or a.is_rs<>'是' ) ---妊娠
and ( ((a.spbh not in(select spbh from sk1.hnhb.dbo.spqx)) and (left(a.jzflgl,2) in('二类','三类')) and c.slxs<>'普通')
or a.spbh in(select spbh from sk1.hnhb.dbo.spqx) or (isnull(left(a.jzflgl,2),'') not in('二类','三类'))) ---三类器械
and ( (left(a.jzflgl,2)='三类' and c.slxs='三类') or isnull(left(a.jzflgl,2),'')<>'三类' )
and ( (a.jxsw='是' and (left(c.zhuypzh,2) not in ('XI','XT','XU','XV','XW','XX','XY','XZ','XG','XO'))) or a.jxsw<>'是') ---禁销外省
and ((a.sf_dzjg='是' and c.dzjg='是' ) or a.sf_dzjg<>'是' ) ---电子监管
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[kaipiao]
@zjm varchar(20),@dwbh varchar(20),@id int,@select int,@user_no varchar(50),@dept_id varchar(50),@user_priv varchar(50)
AS
/*
外部客户开票选择商品，select 后面字段修改的时候，顺序不要变， as及后边的表示不要改，字段数量也不要改变
*/
if (@id=0 and @select=0)--正常开票zjm
begin
select top 190 a.spid,a.spbh,a.spmch,replace(a.shpgg,'#','号') as shpgg,a.dw as dw, d.cxxx as cxxx,
case when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)) end )
/a.jlgg>10 then '10件以上'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)) end )/a.jlgg<=10 and (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg>=1 then '1到10件'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)) end )/a.jlgg<1 then
case when left(a.spbh,1) in ('Z','J','P') THEN convert(char(10) ,b.hwshl)
when (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0))<0 then '0.00'
else convert(char(10),(b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0))) end
end as kcshl,a.hshsj,a.shpchd, 'cxxx' as baozhiqi,a.pick_pack_num,
case when i.tejia is not null then i.tejia
else
case when h.diaocdj is not null then h.diaocdj ---特殊售价
when c.kehudengji='A' then
case when a.fukfs in ('A1','B1','C1') and c.huikfs like '现金%' then a.xjj
when a.fukfs in ('A1','B1','C1') and c.huikfs like '承兑%' then a.cdj
else a.cdj end
when c.kehudengji='b' then
case when a.spid in (select spid from xianshousp where is_xianshou='是') then a.lslsj
else a.xjj end
when c.kehudengji='c' then
case when a.spid in (select spid from xianshousp where is_xianshou='是') then a.lsydj
else a.lslsj end
when c.kehudengji='d' then
case when a.spid in (select spid from xianshousp where is_xianshou='是') then a.yljgj
else a.yljgj end
when c.kehudengji='Y' then case when a.zydj<>0 then a.zydj else a.xjj end
else ceiling(b.chbdj*102)/100 end
end as diaocdj ,
a.zbz hwshl,a.pick_pack_num pp,'b' as aa ,0 as bb,a.jlgg,
case when isnull(w.numlimit,0)<b.hwshl and isnull(w.numlimit,0)<>0 then w.numlimit--------------提取spwh中商品的限售量2009-05-04-17:00
else (b.hwshl-b.bkxshl-isnull(e.shl,0)) end as shl,a.jxsw,
'是' as xs,case when a.pizhwh<>'' then a.pizhwh else a.spmch end as pizhwh
from sk1.hnhb.dbo.spkfk a,hwsp b ,sk1.hnhb.dbo.mchk c ,cxxx d,cnshl e ,tejia h ,sk1.hnhb.dbo.dzsw_tjb i,spwh w
where a.beactive='是' and a.spid*=h.spid and c.dwbh*=h.dwbh
and a.spid not in (select spid from spwh where isxs='否') and a.shpchd not like '%深圳市生命力%' and (a.zjm like '%'+@zjm+'%' )
and a.spmch not like '%(停)%' and a.spid=b.spid
and (c.danwbh=@dwbh or c.dwbh=@dwbh) and b.hw='HWI00000053'
and a.spid*=w.spid------------------------------------------链接spwh表
and a.spid*=d.spid and a.spid*=e.spid and a.spid*=i.spid and i.kaiszxrq<=getdate() and i.zhongzzxrq >=getdate()
/*经营范围*/
and ((a.spbh like '%K' and c.xsdb='是') or a.spbh not like '%K') ---蛋白同化剂
and ((a.is_mhj='是' and c.is_mhj='是') or a.is_mhj<>'是') ---销售麻黄碱类2009-05-13
and ( (a.is_yds='是' and c.is_yds='是') or a.is_yds<>'是' ) ---胰岛素
and ( (a.spbh like '%J' and c.is_wf='是') or a.spbh not like '%J' ) ---二类精神
and ( (a.is_rs='是' and c.is_rs='是') or a.is_rs<>'是' ) ---妊娠
and ( ((a.spbh not in(select spbh from sk1.hnhb.dbo.spqx)) and (left(a.jzflgl,2) in('二类','三类')) and c.slxs<>'普通')
or a.spbh in(select spbh from sk1.hnhb.dbo.spqx) or (isnull(left(a.jzflgl,2),'') not in('二类','三类'))) ---三类器械
and ( (left(a.jzflgl,2)='三类' and c.slxs='三类') or isnull(left(a.jzflgl,2),'')<>'三类' )
and ( (a.jxsw='是' and (left(c.zhuypzh,2) not in ('XI','XT','XU','XV','XW','XX','XY','XZ','XG','XO'))) or a.jxsw<>'是') ---禁销外省
and ((a.sf_dzjg='是' and c.dzjg='是' ) or a.sf_dzjg<>'是' ) ---电子监管
(select spid from xianshousp where
((is_xianshou='否' and ( isnull(charindex(','+@user_no+',',','+kehudengji),0)>0 or isnull(charindex(','+@dwbh+',',','+userid),0)>0 or isnull(charindex(','+@user_priv+',',','+user_priv),0)>0 or isnull(charindex(','+@dept_id+',',','+dept_name),0)>0))
or (is_xianshou='是' and (isnull(charindex(','+@dwbh+',',','+userid),0)=0 and isnull(charindex(','+@user_priv+',',','+user_priv),0)=0 and isnull(charindex(','+@dept_id+',',','+dept_name),0)=0 and isnull(charindex(','+@user_no+',',','+kehudengji),0)=0 )))
)
ORDER BY a.spbh,b.kcshlend
if (@id=0 and @select=4)--正常开票zjm 中药
begin
select top 190 a.spid,a.spbh,a.spmch,replace(a.shpgg,'#','号') as shpgg,a.dw as dw, d.cxxx as cxxx,
case when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )
/a.jlgg>10 then '10件以上'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg<=10 and (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg>=1 then '1到10件'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg<1 then
case when left(a.spbh,1) in ('Z','J','P') THEN convert(char(10) ,b.hwshl)
when (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl)<0 then '0.00'
else convert(char(10),(b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl)) end
end as kcshl,a.hshsj,a.shpchd, 'cxxx' as baozhiqi,a.pick_pack_num,
case when i.tejia is not null then i.tejia
else
case when h.diaocdj is not null then h.diaocdj ---特殊售价
when c.kehudengji='A' then
case when a.fukfs in ('A1','B1','C1') and c.huikfs like '现金%' then a.xjj
when a.fukfs in ('A1','B1','C1') and c.huikfs like '承兑%' then a.cdj
else a.cdj end
when c.kehudengji='b' then a.lslsj
when c.kehudengji='c' then a.lsydj
when c.kehudengji='d' then a.yljgj
when c.kehudengji='Y' then case when a.zydj<>0 then a.zydj else a.xjj end
else ceiling(b.chbdj*102)/100 end
end as diaocdj ,
a.zbz hwshl,a.pick_pack_num pp,'b' as aa ,0 as bb,a.jlgg,
case when isnull(w.numlimit,0)<b.hwshl and isnull(w.numlimit,0)<>0 then w.numlimit--------------提取spwh中商品的限售量2009-05-04-17:00
else (b.hwshl-b.bkxshl-isnull(e.shl,0)) end as shl,a.jxsw,
'是' as xs,case when a.pizhwh<>'' then a.pizhwh else a.spmch end as pizhwh
from sk1.hnhb.dbo.spkfk a,hwsp b ,sk1.hnhb.dbo.mchk c ,cxxx d,cnshl e ,tejia h ,sk1.hnhb.dbo.dzsw_tjb i,spwh w
where a.beactive='是' and a.spid*=h.spid and c.dwbh*=h.dwbh
and a.spid not in (select spid from spwh where isxs='否')and a.shpchd not like '%深圳市生命力%'and (a.zjm like '%'+@zjm+'%' )
and a.spmch not like '%(停)%' and a.spid=b.spid
and (c.danwbh=@dwbh or c.dwbh=@dwbh) and b.hw='HWI00000053'
and a.spid*=w.spid------------------------------------------链接spwh表
and a.spid*=d.spid and a.spid*=e.spid and left(a.spbh,1) ='Z' and a.spid*=i.spid and i.kaiszxrq<=getdate() and i.zhongzzxrq >=getdate()
(select spid from xianshousp where
((is_xianshou='否' and ( isnull(charindex(','+@user_no+',',','+kehudengji),0)>0 or isnull(charindex(','+@dwbh+',',','+userid),0)>0 or isnull(charindex(','+@user_priv+',',','+user_priv),0)>0 or isnull(charindex(','+@dept_id+',',','+dept_name),0)>0))
or (is_xianshou='是' and (isnull(charindex(','+@dwbh+',',','+userid),0)=0 and isnull(charindex(','+@user_priv+',',','+user_priv),0)=0 and isnull(charindex(','+@dept_id+',',','+dept_name),0)=0 and isnull(charindex(','+@user_no+',',','+kehudengji),0)=0 )))
)
ORDER BY a.spbh,b.kcshlend
if (@id=0 and @select=4)--正常开票zjm 中药
begin
select top 190 a.spid,a.spbh,a.spmch,replace(a.shpgg,'#','号') as shpgg,a.dw as dw, d.cxxx as cxxx,
case when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )
/a.jlgg>10 then '10件以上'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg<=10 and (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg>=1 then '1到10件'
when (case when left(a.spbh,1) in ('Z','J','P') THEN b.hwshl else (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl) end )/a.jlgg<1 then
case when left(a.spbh,1) in ('Z','J','P') THEN convert(char(10) ,b.hwshl)
when (b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl)<0 then '0.00'
else convert(char(10),(b.hwshl-b.bkxshl/2-abs(b.bkxshl)/2-isnull(e.shl,0)-b.zdshl)) end
end as kcshl,a.hshsj,a.shpchd, 'cxxx' as baozhiqi,a.pick_pack_num,
case when i.tejia is not null then i.tejia
else
case when h.diaocdj is not null then h.diaocdj ---特殊售价
when c.kehudengji='A' then
case when a.fukfs in ('A1','B1','C1') and c.huikfs like '现金%' then a.xjj
when a.fukfs in ('A1','B1','C1') and c.huikfs like '承兑%' then a.cdj
else a.cdj end
when c.kehudengji='b' then a.lslsj
when c.kehudengji='c' then a.lsydj
when c.kehudengji='d' then a.yljgj
when c.kehudengji='Y' then case when a.zydj<>0 then a.zydj else a.xjj end
else ceiling(b.chbdj*102)/100 end
end as diaocdj ,
a.zbz hwshl,a.pick_pack_num pp,'b' as aa ,0 as bb,a.jlgg,
case when isnull(w.numlimit,0)<b.hwshl and isnull(w.numlimit,0)<>0 then w.numlimit--------------提取spwh中商品的限售量2009-05-04-17:00
else (b.hwshl-b.bkxshl-isnull(e.shl,0)) end as shl,a.jxsw,
'是' as xs,case when a.pizhwh<>'' then a.pizhwh else a.spmch end as pizhwh
from sk1.hnhb.dbo.spkfk a,hwsp b ,sk1.hnhb.dbo.mchk c ,cxxx d,cnshl e ,tejia h ,sk1.hnhb.dbo.dzsw_tjb i,spwh w
where a.beactive='是' and a.spid*=h.spid and c.dwbh*=h.dwbh
and a.spid not in (select spid from spwh where isxs='否')and a.shpchd not like '%深圳市生命力%'and (a.zjm like '%'+@zjm+'%' )
and a.spmch not like '%(停)%' and a.spid=b.spid
and (c.danwbh=@dwbh or c.dwbh=@dwbh) and b.hw='HWI00000053'
and a.spid*=w.spid------------------------------------------链接spwh表
and a.spid*=d.spid and a.spid*=e.spid and left(a.spbh,1) ='Z' and a.spid*=i.spid and i.kaiszxrq<=getdate() and i.zhongzzxrq >=getdate()