批量派工.sql

项目管理中,按科目交叉取数报表按科目交叉取数报表

  CREATE   Procedure sp_paigong    
(    
 @dDate varchar(20),   
 @cwhcode varchar(20)   
)    
as     
  
set nocount on    
declare @tmpid varchar(20)  
declare @ccode varchar(20)   
-- declare @cinvname  
declare @p0 int  
declare @p1 int  
declare @p2 int  
set @cwhcode=left(rtrim(ltrim(@cwhcode)),2)  
  
--select cinvcode,cinvname,sum(isnull(bquantity,0)) as iquantity from fc_dutyclassmain   group by cinvcode,cinvname --where cmakedate<=@dDate  
select @p0=count(*) from (select cinvcode,cinvname,sum(isnull(bquantity,0)) as iquantity from fc_dutyclassmain  group by cinvcode,cinvname) t1 
exec sp_GetId N'', N'001', N'FC_dutyclassmain', @p0, @P1 output, @P2 output  
select @tmpid=convert(varchar(20),@p0)  
select @ccode=convert(char(4),year(getdate()))+right('0000000000'+ltrim(rtrim(convert(varchar(20),convert(int,cnumber)+1))),10) from VoucherHistory  Where  CardNumber='AQ80' and cContent is NULL  
update VoucherHistory set cNumber=convert(varchar(20),convert(int,cnumber)+1) Where  CardNumber='AQ80' and cContent is NULL  
-- print @p1  
-- print @p2   
set nocount off    
--主表  
begin   
--drop table newtable  
insert into rdrecord(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,crdcode,cmaker,bpufirst,biafirst,vt_id,  
bisstqc,iproorderid,iswfcontrolled)   
values (@p1,1,N'10',N'成品入库',N'库存',@cwhcode,@dDate,@ccode,N'12',N'demo',0,0,63,0,0,0)  
end   
--子表  
begin  
select autoid =IDENTITY(int, 1, 1),@p1 as id,cinvcode,sum(isnull(bquantity,0)) as inum,sum(isnull(bquantity,0)) as iquantity,Null as iunitcost ,  
Null as iprice ,  
Null as ipunitcost ,  
Null as ipprice ,  
Null as cbatch ,  
Null as cvouchcode ,  
Null as cfree1 ,  
Null as cfree2 ,  
Null as dsdate ,  
Null as isoutquantity ,  
Null as isoutnum ,  
Null as ifnum ,  
Null as ifquantity ,  
Null as dvdate ,  
Null as itrids ,  
Null as cposition ,  
Null as cdefine22 ,  
Null as cdefine23 ,  
Null as cdefine24 ,  
Null as cdefine25 ,  
Null as cdefine26 ,  
Null as cdefine27 ,  
Null as citem_class ,  
Null as citemcode ,  
Null as cname ,  
Null as citemcname ,  
Null as cfree3 ,  
Null as cfree4 ,  
Null as cfree5 ,  
Null as cfree6 ,  
Null as cfree7 ,  
Null as cfree8 ,  
Null as cfree9 ,  
Null as cfree10 ,  
Null as cbarcode ,  
Null as inquantity ,  
Null as innum ,  
N'0601' as cassunit ,  
Null as dmadedate ,  
Null as imassdate ,  
Null as cdefine28 ,  
Null as cdefine29 ,  
Null as cdefine30 ,  
Null as cdefine31 ,  
Null as cdefine32 ,  
Null as cdefine33 ,  
Null as cdefine34 ,  
Null as cdefine35 ,  
Null as cdefine36 ,  
Null as cdefine37 ,  
Null as impoids ,  
Null as icheckids ,  
Null as cbvencode ,  
Null as cinvouchcode ,  
Null as ccheckcode ,  
Null as icheckidbaks ,  
Null as crejectcode ,  
Null as irejectids ,  
Null as ccheckpersoncode ,  
Null as dcheckdate ,  
Null as cmassunit ,  
Null as cmolotcode ,  
Null as isodid ,  
0 as brelated ,  
Null as cmworkcentercode ,  
Null as isotype ,  
Null as corufts ,  
Null as cbaccounter ,  
1 as bcosting ,  
0 as bvmiused ,  
Null as ivmisettlequantity ,  
Null as ivmisettlenum ,  
Null as cvmivencode ,  
Null as iinvsncount  
into newtable from fc_dutyclassmain   group by cinvcode,cinvname   
  
INSERT INTO rdrecords( autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch,cvouchcode,cfree1,cfree2,dsdate,isoutquantity,isoutnum,ifnum,ifquantity,dvdate,itrids,cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27
,citem_class,citemcode,cname,citemcname,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,cbarcode,inquantity,innum,cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,imp
oids,icheckids,cbvencode,cinvouchcode,ccheckcode,icheckidbaks,crejectcode,irejectids,ccheckpersoncode,dcheckdate,cmassunit,cmolotcode,isodid,brelated,cmworkcentercode,isotype,corufts,cbaccounter,bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivenco
de,iinvsncount )   
select @p2 - autoid as autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch,cvouchcode,cfree1,cfree2,dsdate,isoutquantity,isoutnum,ifnum,ifquantity,dvdate,itrids,cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27
,citem_class,citemcode,cname,citemcname,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,cbarcode,inquantity,innum,cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,imp
oids,icheckids,cbvencode,cinvouchcode,ccheckcode,icheckidbaks,crejectcode,irejectids,ccheckpersoncode,dcheckdate,cmassunit,cmolotcode,isodid,brelated,cmworkcentercode,isotype,corufts,cbaccounter,bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivenco
de,iinvsncount from newtable  
   
drop table newtable  
  
end