复核台分摊

USE [jxgx]
GO
/****** Object:  StoredProcedure [dbo].[ncsk_ftfht]    Script Date: 01/01/2014 23:07:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ncsk_ftfht]
@billno varchar(10)
,@iszd char(1)
,@saleManId char(11)
,@orgid char(11)
AS
BEGIN


declare @errcode integer
select @errcode = 7000       
  
declare @return integer
set @return=0


 --启动事务处理
declare @tran_point int
set @tran_point = @@trancount
if @tran_point = 0
  begin tran [ncsk_ftfht]
else
  save tran [ncsk_ftfht]


if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_zjMx') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_zjMx
if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_lsMx') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_lsMx
if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_Bill') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_Bill
if exists(select 1 from MASTER.dbo.syscursors where cursor_name='pcurr')
begin
close pcurr
deallocate pcurr 
end
if @iszd = 'Y'
begin
select a.salemanid,a.Remark1,a.pxbs
,BillList =(select (stuff((select ''','''+convert(varchar(20),b.billno)
from salenotesmt b
join salestwomt b1 on b.billno = b1.billno and b.entid = b1.entid
where b.billstate = 0
and b.ruleid = '67b55qi64q7dzsq2'
and b1.isend = 'Y'
and b1.isdone = 'N'
and b.iszd = 'Y'
and b.isfh='N'
and a.salemanid = b.salemanid
and a.Remark1 = b.Remark1
and a.pxbs = b.pxbs
and b.salemanid like (case when @salemanid = '' then '%%' else @salemanid end )
--and exists(select 1 from salenotesdt z where b.billno = z.billno 
-- and b.entid = z.entid and z.whorgid = @orgid
--)
for xml path('')
),1,2,''))+'''') 
into k_Bill
from salenotesmt a
join salestwomt a1 on a.billno = a1.billno and a.entid = a1.entid
where a.billstate = 0
and a.ruleid = '67b55qi64q7dzsq2'
and a1.isend = 'Y'
and a1.isdone = 'N'
and a.iszd = 'Y'
and a.isfh='N'
and a.salemanid like (case when @salemanid = '' then '%%' else @salemanid end )
--and exists(select 1 from salenotesdt x where a.billno = x.billno 
-- and a.entid = x.entid and x.whorgid = @orgid
--)
group by a.salemanid,a.Remark1,a.pxbs
end
else
begin
select '' as salemanid,'' as Remark1,'' as pxbs,@billno as BillList into k_Bill;
end
declare @BillList varchar(8000)
declare @billcode varchar(50)
declare @recnum int
if not exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_Bill') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)
or (select count(1) from k_Bill) <= 0
begin
set @return=1
goto err_lab
end
declare pcurr cursor for select BillList from k_Bill
open pcurr
fetch next from pcurr into @BillList
while (@@fetch_status = 0)
begin
declare @fhtbh varchar(20)
declare @fhtmch varchar(20)
declare @zcqbh varchar(20)
declare @zcqmch varchar(20)
set @fhtbh = '';
set @fhtmch = '';
set @zcqbh = '';
set @zcqmch = '';
select top 1 @fhtbh = fhtbh,@fhtmch = fhtmch
from ncsk_fht
where beactive = '是'
and is_tuop='否'
--and orgid = @orgid
order by case when is_zdfht = '是' then 0 else 1 end,ztm,fhtbh

if @fhtbh = '' or @fhtbh is null
begin 
raiserror('散件复核台为空，请维护！', 16, 1);

if @@error <> 0 or @@rowcount = 0
begin
set @return=11
goto err_lab
end

end

select @zcqbh = zcqbh,@zcqmch = zcqmch 
from ncsk_nfhzcq
where fhtbh = @fhtbh
order by ztm,zcqbh

if @zcqbh = '' or @zcqbh is null
begin 
raiserror('散件内复核暂存区为空，请维护！', 16, 1);

if @@error <> 0 or @@rowcount = 0
begin
set @return=12
goto err_lab
end

end 


if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_zjMx') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_zjMx
declare @sql1 varchar(8000)
set @sql1 = 'select a.billno,a.billsn,a.entid into k_zjMx'
+ ' from salenotesdt a'
+ ' join salenotesmt b on a.billno = b.billno and a.entid = b.entid'
+ ' join storehouse c on a.whid = c.whid and a.entid = c.entid'
+ ' where a.zjnum <> 0'
+ ' and b.isfh=''N'''
+ ' and c.k_qyckfh = ''Y'''
+ ' and a.billno in ('+@BillList+')';
exec(@sql1);

if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_lsMx') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_lsMx
declare @sql2 varchar(8000)
set @sql2 = 'select a.billno,a.billsn,a.entid into k_lsMx'
+ ' from salenotesdt a'
+ ' join salenotesmt b on a.billno = b.billno and a.entid = b.entid'
+ ' join storehouse c on a.whid = c.whid and a.entid = c.entid'
+ ' where a.lsnum <> 0'
+ ' and b.isfh = ''N'''
+ ' and c.k_qyckfh = ''Y'''
+ ' and a.billno in ('+@BillList+')';

exec(@sql2);

select @recnum = recnum from billcode where ruleid = '0000000000000000'
set @billcode = 'FFD'+right('000000000000'+convert(varchar(10),@recnum+1),11)
update BillCode set recnum = @recnum + 1
where ruleid = '0000000000000000'

if (select count(1) from k_lsMx) > 0
begin 
declare @have_zj char(2)
set @have_zj ='否'
if (select count(1) from k_zjMx) > 0
begin
set @have_zj ='是'
end
--select @billcode = replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
insert into ncsk_dfhdjhz(rq,djbh,djbs,username,is_zx,piaohao,jhy,fhy
,pxy,shenhe,dwbh,ywy,fhtmch,fht,have_zj,have_ls,thfs,beizhu,nfhzcq,ontime)
select distinct a.dates,@billcode/*复核单号*/,'',c.staffname,'否',a.psdh,'','','',''
,a.clientid,d.staffname,@fhtmch,@fhtmch
,@have_zj,'是',e.dictlist,a.Remark1,@zcqbh
,a.ontime
from salenotesmt a
join(select billno,billsn,row_number() over(order by billno desc,billsn desc) as sk
from k_lsMx
) b on a.billno = b.billno and b.sk = 1
join staffdoc c on a.caozy = c.staffid and a.entid = c.entid
join staffdoc d on a.salemanid = d.staffid and a.entid = d.entid
left join dictdoc e on a.Delivery = e.dictvalue and e.fdname = 'Delivery'


if @@error <> 0 or @@rowcount = 0
begin
set @return=13
goto err_lab
end
insert into ncsk_dfhdjmx(djbh,dj_sn,spid,pihao,sxrq,pihao2,shl,baozhshl
,lingsshl,yfhshl,jhy,fhy,pxy,pxdjbh,fhtime,is_zx,fht,hw
,fhshl,nfhzcq,xgdjbh,jwh,zcqbh,chkdjbh,dwbh,gkj)
select @billcode/*复核单号*/,a.billsn,a.goodsid,a.batchcode,a.valdate,a.batchcode,a.num
,a.zjnum,a.lsnum,'0','','','','','','否',@fhtmch,a.whid,'0'
,@zcqbh,c.billcode,d.locatname,@zcqbh,c.billcode,c.clientid,a.taxprice
from salenotesdt a
join k_lsMx b on a.billno = b.billno and a.billsn = b.billsn and a.entid = b.entid
join salenotesmt c on a.billno = c.billno and a.entid = c.entid
join storeroom d on a.locatid = d.locatid and a.entid = d.entid
if @@error <> 0 or @@rowcount = 0
begin
set @return=14
goto err_lab
end

update a set a.k_fhdh = b.djbh,a.fhtmch = b.fht
from salenotesdt a
join salenotesmt d on a.billno = d.billno and a.entid = d.entid
join ncsk_dfhdjmx b on d.billcode = b.xgdjbh and a.billsn = b.dj_sn
join k_lsMx c on a.billno = c.billno and a.billsn = c.billsn

if @@error <> 0 or @@rowcount = 0
begin
set @return=24
goto err_lab
end

update ncsk_fht set ztm = isnull(ztm,0) + isnull((select count(1) from k_lsMx),0)
where rtrim(fhtbh) = rtrim(@fhtbh)
 
if @@error <> 0 or @@rowcount = 0
begin
print(@@rowcount)
set @return=15
goto err_lab
end

update ncsk_nfhzcq set ztm = isnull(ztm,0) + isnull((select count(1) from k_lsMx),0)
where rtrim(fhtbh) = rtrim(@fhtbh)
and rtrim(zcqbh) = rtrim(@zcqbh)

if @@error <> 0 or @@rowcount = 0
begin
set @return=16
goto err_lab
end

end
if (select count(1) from k_zjMx) > 0
begin
set @fhtbh = '';
set @fhtmch = '';
set @zcqbh = '';
set @zcqmch = '';
select top 1 @fhtbh = fhtbh,@fhtmch = fhtmch 
from ncsk_fht
where beactive = '是'
and is_tuop='是'
--and orgid = @orgid
order by case when is_zdfht = '是' then 0 else 1 end,ztm,fhtbh
if @fhtbh = '' or @fhtbh is null
begin 
raiserror('整件复核台为空，请维护！', 16, 1);

if @@error <> 0 or @@rowcount = 0
begin
set @return=17
goto err_lab
end

end
select @zcqbh = zcqbh,@zcqmch = zcqmch
from ncsk_nfhzcq
where fhtbh = @fhtbh
order by ztm,zcqbh
if @zcqbh = '' or @zcqbh is null
begin
raiserror('整件内复核暂存区为空，请维护！', 16, 1);

if @@error <> 0 or @@rowcount = 0
begin
set @return=18
goto err_lab
end

end 
declare @have_ls char(2)
set @have_ls ='否'
if (select count(1) from k_lsMx) > 0
begin
set @have_ls ='是'
end
--select @billcode = replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--select @recnum = recnum from billcode where ruleid = '0000000000000000'
--set @billcode = 'FFD'+right('000000000000'+convert(varchar(10),@recnum+1),11)
--update BillCode set recnum = @recnum + 1
--where ruleid = '0000000000000000'
insert into Dfhdjhz_zhj(rq,djbh,djbs,username,is_zx,piaohao,jhy,fhy
,pxy,shenhe,dwbh,ywy,fhtmch,fht,have_zj,have_ls,thfs,beizhu,nfhzcq,ontime)
select distinct a.dates,@billcode/*复核单号*/,'',c.staffname,'否',a.psdh,'','','',''
,a.clientid,d.staffname,@fhtmch,@fhtmch
,'是',@have_ls,e.dictlist,a.Remark1,@zcqbh
,a.ontime
from salenotesmt a
join(select billno,billsn,row_number() over(order by billno desc,billsn desc) as sk
from k_zjMx
) b on a.billno = b.billno and b.sk = 1
join staffdoc c on a.caozy = c.staffid and a.entid = c.entid
join staffdoc d on a.salemanid = d.staffid and a.entid = d.entid
left join dictdoc e on a.Delivery = e.dictvalue and e.fdname = 'Delivery'

if @@error <> 0 or @@rowcount = 0
begin
set @return=19
goto err_lab
end


insert into Dfhdjmx_zhj(djbh,dj_sn,spid,pihao,sxrq,pihao2,shl,baozhshl
,yfhshl,jhy,fhy,pxy,pxdjbh,fhtime,is_zx,fht,hw
,nfhzcq,xgdjbh,jwh,zcqbh,chkdjbh,dwbh)
select @billcode/*复核单号*/,a.billsn,a.goodsid,a.batchcode,a.valdate,a.batchcode,a.num
,a.zjnum,'0','','','','','','否',@fhtmch,a.whid
,@zcqbh,c.billcode,d.locatname,@zcqbh,c.billcode,c.clientid
from salenotesdt a
join k_zjMx b on a.billno = b.billno and a.billsn = b.billsn and a.entid = b.entid
join salenotesmt c on a.billno = c.billno and a.entid = c.entid
join storeroom d on a.locatid = d.locatid and a.entid = d.entid
if @@error <> 0 or @@rowcount = 0
begin
set @return=20
goto err_lab
end

update a set a.k_fhdh = b.djbh,a.fhtmch = b.fht
from salenotesdt a
join salenotesmt d on a.billno = d.billno and a.entid = d.entid
join Dfhdjmx_zhj b on d.billcode = b.xgdjbh and a.billsn = b.dj_sn
join k_zjMx c on a.billno = c.billno and a.billsn = c.billsn

if @@error <> 0 or @@rowcount = 0
begin
set @return=25
goto err_lab
end

update ncsk_fht set ztm = isnull(ztm,0) + isnull((select count(1) from k_zjMx),0)
where rtrim(fhtbh) = rtrim(@fhtbh)


if @@error <> 0 or @@rowcount = 0
begin
set @return=21
goto err_lab
end

update ncsk_nfhzcq set ztm = isnull(ztm,0) + isnull((select count(1) from k_zjMx),0)
where rtrim(fhtbh) = rtrim(@fhtbh)
and rtrim(zcqbh) = rtrim(@zcqbh)


if @@error <> 0 or @@rowcount = 0
begin
set @return=22
goto err_lab
end

end

declare @upSql varchar(8000)
set @upSql = 'update salenotesmt set isfh = ''Y'' '
+ ' where billno in ('+@BillList+')';
exec(@upSql)


if @@error <> 0 or @@rowcount = 0
begin
set @return=23
goto err_lab
end

fetch next from pcurr into @BillList
end
--close pcurr
--deallocate pcurr 
 --结束事务处理
 commit_lab:
 if @tran_point = 0
  commit tran [ncsk_ftfht]
 goto return_lab
 
 err_lab:
 if @return < 10000
 set @return = cast(cast(@errcode as varchar(6)) + 
              right(cast(10000 + @return as varchar(5)),4) as int)
 
 rollback tran [ncsk_ftfht]
 
 return_lab:
 if @return > 70000010 or @return < 70000001
 begin
  if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_zjMx') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_zjMx
if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_lsMx') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_lsMx
if exists (SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(N'k_Bill') 
AND OBJECTPROPERTY(ID, 'IsTable') = 1)  drop table k_Bill
close pcurr
deallocate pcurr
 end
 return @return

END