zgh_产品日报表.doc

-产品日报表-exec Get_Product_Day ,0,2010-10-01,0alter proc Get_Product_DayNodeKey nvarchar(100),ID int,FDate varchar(10),FStoreID int=0asset nocount ondeclare Count int declare FSQEDate datetime -上期结存时间declare strSql varchar(8000)if exists (select * from dbo.sysobjects where id = object_id(Ndbo.Temp_Product_Day) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.Temp_Product_DayCREATE TABLE dbo.Temp_Product_Day (FID int IDENTITY (1, 1) NOT NULL ,FProductID int NULL ,FDayInNumber float default (0) NULL ,FDayInPiece float default (0) NULL ,FDayInMoney float default (0) NULL ,FMonthInNumber float default (0) NULL ,FMonthInPiece float default (0) NULL ,FMonthInMoney float default (0) NULL ,FDayOutNumber float default (0) NULL ,FDayOutPiece float default (0) NULL ,FDayOutMoney float default (0) NULL ,FMonthOutNumber float default (0) NULL ,FMonthOutPiece float default (0) NULL ,FMonthOutMoney float default (0) NULL ,FNowNumber float default (0) NULL ,FNowPiece float default (0) NULL ,FNowMoney float default (0) NULL ) ON PRIMARY-1、得到成品IDif (NodeKey= and ID0)insert into Temp_Product_Day(FProductID) values (ID)elseinsert into Temp_Product_Day(FProductID) select i.FID from Base_CProduct i left outer join t_Item on i.FParentID=t_Item.FID where t_Item.FItemClassID=35 and t_Item.FNodeKey like NodeKey + %-2.1、组合期初数，直接计算到这天位置的库存数-set FSQEDate=(select max(FEDate) from FMonth_Product where FEDateconvert(datetime,FDate)-if (FSQEDate is null) set FSQEDate=2011-12-18if (FSQEDate 2011-12-18 And a.FDate 2011-12-18 And a.FDate+ FSQEDate + And a.FDate+ FSQEDate + And a.FDate=+ FDate + set strSql=strSql + Case when FStoreID=0 then else And A.FStorageID= +rtrim(cast(FStoreID as char(8) endset strSql=strSql + and b.FCmbProductID in (select FProductID from Temp_Product_Day)set strSql=strSql + group by b.FCmbProductID) FProdset strSql=strSql + join Temp_Product_Day Temp_Product_Day on Temp_Product_Day.FProductID=FProd.FCmbProductID exec (strSql)end-3.1、得到当天入库set strSql=update Temp_Product_Day set FDayInNumber=isnull(FProd.FNumber,0),set strSql=strSql + FDayInPiece=isnull(FProd.FPiece,0),set strSql=strSql + FDayInMoney=isnull(FProd.FMoney,0) set strSql=strSql + from (select b.FCmbProductID,sum(b.FNumber*isnull(A.FType,1) as FNumber,sum(b.FPiece*isnull(A.FType,1) as FPiece,sum(b.FMoney*isnull(A.FType,1) as FMoneyset strSql=strSql + from ST_Bill_Prod_Input_Base a join ST_Bill_Prod_Input_Detail b on a.FBillID=b.FBillIDset strSql=strSql + where a.FState not in (1,3) and a.FDate=+ FDate + set strSql=strSql + Case when FStoreID=0 then else And A.FStorageID= +rtrim(cast(FStoreID as char(8) endset strSql=strSql + and b.FCmbProductID in (select FProductID from Temp_Product_Day)set strSql=strSql + group by b.FCmbProductID) FProdset strSql=strSql + join Temp_Product_Day Temp_Product_Day on Temp_Product_Day.FProductID=FProd.FCmbProductID exec (strSql)print strsql-3.2、得到当月入库数据set strSql=update Temp_Product_Day set FMonthInNumber=isnull(FProd.FNumber,0),set strSql=strSql + FMonthInPiece=isnull(FProd.FPiece,0),set strSql=strSql + FMonthInMoney=isnull(FProd.FMoney,0) set strSql=strSql + from (select b.FCmbProductID,sum(b.FNumber*isnull(A.FType,1) as FNumber,sum(b.FPiece*isnull(A.FType,1) as FPiece,sum(b.FMoney*isnull(A.FType,1) as FMoneyset strSql=strSql + from ST_Bill_Prod_Input_Base a join ST_Bill_Prod_Input_Detail b on a.FBillID=b.FBillIDset strSql=strSql + where a.FState not in (1,3) and a.FDate=+ FDate + and year(a.FDate)= +rtrim(cast(year(FDate) as char(8) + and month(a.FDate)= + rtrim(cast(month(FDate) as char(8)set strSql=strSql + Case when FStoreID=0 then else And A.FStorageID= +rtrim(cast(FStoreID as char(8) endset strSql=strSql + and b.FCmbProductID in (select FProductID from Temp_Product_Day)set strSql=strSql + group by b.FCmbProductID) FProdset strSql=strSql + join Temp_Product_Day Temp_Product_Day on Temp_Product_Day.FProductID=FProd.FCmbProductID exec (strSql)print strsql-3.3、得到当天出库set strSql=update Temp_Product_Day set FDayOutNumber=isnull(FProd.FNumber,0),set strSql=strSql + FDayOutPiece=isnull(FProd.FPiece,0),set strSql=strSql + FDayOutMoney=isnull(FProd.FMoney,0) set strSql=strSql + from (select b.FCmbProductID,sum(b.FSendGoodsWeight*isnull(A.FType,1) as FNumber,sum(b.FSendGoodsPiece*isnull(A.FType,1) as FPiece,sum(b.FSendGoodsMny*isnull(A.FType,1) as FMoneyset strSql=strSql + from ST_Bill_Prod_Output_Base a join ST_Bill_Prod_Output_Detail b on a.FBillID=b.FBillIDset strSql=strSql + where a.FState not in (1,3) and a.FDate=+ FDate + set strSql=strSql + Case when FStoreID=0 then else And A.FStorageID= +rtrim(cast(FStoreID as char(8) endset strSql=strSql + and b.FCmbProductID in (select FProductID from Temp_Product_Day)set strSql=strSql + group by b.FCmbProductID) FProdset strSql=strSql + join Temp_Product_Day Temp_Product_Day on Temp_Product_Day.FProductID=FProd.FCmbProductID exec (strSql)-3.4、得到当月出库set strSql=update Temp_Product_Day set FMonthOutNumber=isnull(FProd.FNumber,0),set strSql=strSql + FMonthOutPiece=isnull(FProd.FPiece,0),set strSql=strSql + FMonthOutMoney=isnull(FProd.FMoney,0) set strSql=strSql + from (select b.FCmbProductID,sum(b.FSendGoodsWeight*isnull(A.FType,1) as FNumber,sum(b.FSendGoodsPiece*isnull(A.FType,1) as FPiece,sum(b.FSendGoodsMny*isnull(A.FType,1) as FMoneyset strSql=strSql + from ST_Bill_Prod_Output_Base a join ST_Bill_Prod_Output_Detail b on a.FBillID=b.FBillIDset strSql=strSql + where a.FState not in (1,3) and a.FDate=+ FDate + and year(a.FDate)= + rtrim(cast(year(FDate) as char(8) + and month(a.FDate)= + rtrim(cast(month(FDate) as char(8)set strSql=strSql + Case when FStoreID=0 then else And A.FStorageID= +rtrim(cast(FStoreID as char(8) endset strSql=strSql + and b.FCmbProductID in (select FProductID from Temp_Product_Day)set strSql=strSql + group by b.FCmbProductID) FProdset strSql=strSql + join Temp_Product_Day Temp_Product_Day on Temp_Product_Day.FProductID=FProd.FCmbProductID exec (strSql)select a.FProductID,b.FCode as FProductCode,b.FName as FProductName,c.FType as FType,d.FName as FGradeName,e.FN