Set NoCount On
SET ANSI_WARNINGS OFF
Create Table #Happen2(
FItemID int Null,
FStockID int Null,
FBatchNo NVARCHAR(200),
FQty decimal(28,10) Null,
FCUUnitQty decimal(28,10) Null,
FQty1 Decimal(28,10),
FCUUnitQty1 Decimal(28,10),
FAmount1 Decimal(28,10),
FQty2 Decimal(28,10),
FCUUnitQty2 Decimal(28,10),
FAmount2 Decimal(28,10),
FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
Create Table #Happen(
FItemID int Null,
FStockID int Null,
FBatchNo NVARCHAR(200),
FQty decimal(28,10) Null,
FCUUnitQty decimal(28,10) Null,
FQty1 Decimal(28,10),
FCUUnitQty1 Decimal(28,10),
FAmount1 Decimal(28,10),
FQty2 Decimal(28,10),
FCUUnitQty2 Decimal(28,10),
FAmount2 Decimal(28,10),
FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
Create Table #Happen1(
FItemID int Null,
FStockID int Null,
FBatchNo NVARCHAR(200),
FQty decimal(28,10) Null,
FCUUnitQty decimal(28,10) Null,
FQty1 Decimal(28,10),
FCUUnitQty1 Decimal(28,10),
FQty2 Decimal(28,10),
FCUUnitQty2 Decimal(28,10),
FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) )
Insert Into #Happen1
Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty1,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty1,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty2,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty2,
1 ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
FROM ICStockBill t11
INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID
WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
(t11.FCheckerID IS NULL OR t11.FCheckerID=0)
AND t11.FTrantype IN(1,2,5,10,40,41)
ORDER BY t11.FDate DESC),0),0,0
From t_ICItem t1
Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 ))
Join ICStockBillEntry t6 On t5.FInterID=t6.FInterID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = (case when t5.ftrantype=24 then t6.FSCStockID else t6.FDCStockID end)
Where t1.FItemID = t6.FItemID
And ((t5.FTrantype In (1,2,5,10,40) And t5.FRob =1) Or (t5.FTrantype In(21,24,28,29) And t5.FRob=-1))
AND (NOT (t5.FTrantype In (1) AND t5.FPOMode = 36681 ))
And t2.FTypeID NOT IN (504)
Insert Into #Happen1
Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t6.FBegQty Else 0 End) As FQty1,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty1,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t6.FBegQty Else 0 End) As FQty2,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty2,
1 ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
FROM ICStockBill t11
INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID
WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
(t11.FCheckerID IS NULL OR t11.FCheckerID=0)
AND t11.FTrantype IN(1,2,5,10,40,41)
ORDER BY t11.FDate DESC),0),0,0
From t_ICItem t1
Join ICInvInitial t6 On t1.FItemID = t6.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t6.FStockID
Where 1=1 And t2.FTypeID NOT IN (504)
CREATE TABLE #InventoryHanppen
(FItemID INT NOT NULL,
FStockID INT NULL,
FBatchNo Varchar(255) NULL,
FQty DECIMAL(28,10) NOT NULL,
FAmount DECIMAL(28,10) NOT NULL )
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal
FROM t_ICItem t1
INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID
LEFT JOIN t_Stock t2 ON t2.FItemID = u1.FStockID
WHERE u1.FYear=2015 AND u1.FPeriod=9 And t2.FTypeID NOT IN (504)
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,t2.FItemID,u1.FBatchNo, CASE WHEN v1.FTranType IN (1,2,5,10,40,41,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FQty ELSE -1 * u1.FQty END,
CASE WHEN v1.FTranType IN (1,2,5,10,40,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FAmount WHEN v1.FTranType=41 THEN u1.FAmtRef ELSE -1 * u1.FAmount END
FROM t_ICItem t1
INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID
INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID
LEFT JOIN t_Stock t2 ON ((v1.FTrantype=24 AND u1.FSCStockID=t2.FItemID) OR (v1.FTranType IN (1,2,5,10,21,41,28,29,43,40,100,101,102) AND u1.FDCStockID=t2.FItemID ))
WHERE v1.FDate>='2015/09/01' AND v1.FDate<='2015/09/21' And t2.FTypeID NOT IN (504)
AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102) AND (NOT (v1.FTrantype In (1) AND isnull(v1.FPOMode,0) = 36681 ))
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,t2.FItemID,u1.FBatchNo,-1 * u1.FQty ,-1 * u1.FAmount
FROM t_ICItem t1
INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID
INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID
LEFT JOIN t_Stock t2 ON u1.FSCStockID=t2.FItemID
WHERE v1.FDate>='2015/09/01' AND v1.FDate<='2015/09/21' And t2.FTypeID NOT IN (504)
AND v1.Ftrantype=41
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORY
FROM #InventoryHanppen
GROUP BY FItemID,FStockID,FBatchNo
DROP TABLE #InventoryHanppen
DELETE FROM #INVENTORY WHERE FQty<=0
Insert Into #Happen1
Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,CAST(t3.FQTY AS DECIMAL(28,10))/t7.FCoefficient As FCUUnitQty,0,0,0,0,1 ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
FROM ICStockBill t11
INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID
WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
(t11.FCheckerID IS NULL OR t11.FCheckerID=0)
AND t11.FTrantype IN(1,2,5,10,40,41)
ORDER BY t11.FDate DESC),0),0,0
From t_ICItem t1
Join #INVENTORY t3 On t1.FItemID = t3.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t3.FStockID
Where 1=1
And t2.FTypeID NOT IN (504)
Insert Into #HAPPEN2
Select t1.FITEMID,FStockID,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)
From #HAPPEN1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID
GROUP By t1.FITEMID,t1.FBatchNo,FStockID Update #Happen2 Set FQty1= FQty,FQty2=0
Where FQty-FQty1<0
Update #Happen2 Set FQty2= FQty-FQty1
Update #Happen2 Set FAmount1=FPrice*FQty1
Update #Happen2 Set FAmount2=FPrice*FQty2
Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0
Where FCUUnitQty-FCUUnitQty1<0
Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1
Insert Into #HAPPEN
Select t1.FITEMID,t1.FStockID,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,Sum(FAmount1) As FAmount1,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,Sum(FAmount2) As FAmount2,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)
From #HAPPEN2 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID
GROUP By t1.FITEMID,t1.FBatchNo,t1.FStockID
HAVING (SUM(FQTY)>=0)
SET NOCOUNT ON
CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 Varchar(355),
FNumber3 Varchar(355),
FName3 Varchar(355),
FItemID int,
FNumber Varchar(355))
INSERT INTO #ItemLevel SELECT
CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END,
'',
CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1) END,
'',
CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1) END,
'',
FItemID,FNumber FROM t_Item
WHERE FItemClassID=4
AND FDetail=1 AND FItemID In (Select Distinct FItemID from #Happen )
UPDATE t0 SET t0.FName1=t1.FName,t0.FName2=t2.FName,t0.FName3=t3.FName
FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0
left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0
left join t_Item t3 On t0.FNumber3=t3.FNumber AND t3.FItemClassID=4 AND t3.FDetail=0
CREATE TABLE #DATA(
FName1 Varchar(355) Null,
FName2 Varchar(355) Null,
FName3 Varchar(355) Null,
FStockName Varchar(355) Null, FNumber Varchar(355) null,
FShortNumber Varchar(355) null,
FName Varchar(355) null,
FModel Varchar(355) null,
FUnitName Varchar(355) null,
FCUUnitName Varchar(355) null,
FQtyDecimal smallint null,
FPriceDecimal smallint null,
FQty Decimal(28,10) Null,
FCUUnitQty Decimal(28,10) Null,
FPrice Decimal(28,10) NULL,
FCUPrice Decimal(28,10) Null,
FAmount Decimal(28,10) Null,
FQty1 Decimal(28,10),
FCUUnitQty1 Decimal(28,10),
FAmount1 Decimal(28,10),
FQty2 Decimal(28,10),
FCUUnitQty2 Decimal(28,10),
FAmount2 Decimal(28,10),
FSumSort smallint not null Default(0),
Flevel0 Decimal(10,3),
Flevel1 Decimal(10,3),
Flevel2 Decimal(10,3),
Flevel3 Decimal(10,3),
Flevel4 Decimal(10,3),
Flevel5 Decimal(10,3),
Flevel6 Decimal(10,3),
FID int IDENTITY)
INSERT INTO #DATA
SELECT tt1.FName1,tt1.FName2,tt1.FName3,t2.FName,t1.FNumber,'','','','','',MAX(t1.FQtyDecimal),MAX(t1.FPriceDecimal),Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1),
SUM(FCUUnitQty1),
SUM(FAmount1),
SUM(FQty2),
SUM(FCUUnitQty2),
SUM(FAmount2),
CASE WHEN Grouping(tt1.FName1)=1 THEN 106
WHEN Grouping(tt1.FName2)=1 THEN 107
WHEN Grouping(tt1.FName3)=1 THEN 108
WHEN Grouping(t2.FName)=1 THEN 109 WHEN Grouping(t1.FNumber)=1 THEN 110 ELSE 0 END
,0 ,0 ,0 ,0 ,0 ,0 ,0 FROM #Happen v2
Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
Left Join t_Stock t2 On v2.FStockID=t2.FItemID
Inner Join #ItemLevel tt1
On t1.FItemID=tt1.FItemID Where 1=1
Group By tt1.FName1,tt1.FName2,tt1.FName3,t2.FName,t1.FNumber WITH ROLLUP Having Sum(FQty)>0
Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4 Where t1.FNumber=t2.FNumber And t2.FUnitGroupID=t3.FUnitGroupID And t2.FStoreUnitID=t4.FMeasureUnitID And t3.FStandard=1
SELECT IDENTITY(INT,1,1) AS FLevel0,FNAME1
INTO #LEVEL0 FROM #DATA
Where FSumSort=107
UPDATE a3 SET a3.FLevel0=lv.FLevel0
FROM #DATA a3
INNER JOIN #LEVEL0 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0)
Where Not( FSumSort=106)
DROP TABLE #LEVEL0
Update #DATA set FLevel0=(Select max(ISNULL(FLevel0,0))+1 From #DATA) Where 1=1 AND FSumSort=106
SELECT IDENTITY(INT,1,1) AS FLevel1,FNAME1 , FNAME2
INTO #LEVEL1 FROM #DATA
Where FSumSort=108
ORDER BY FLEVEL0
UPDATE a3 SET a3.FLevel1=lv.FLevel1
FROM #DATA a3
INNER JOIN #LEVEL1 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0)
Where Not( FSumSort=106)
DROP TABLE #LEVEL1
SELECT IDENTITY(INT,1,1) AS FLevel2,FNAME1 ,FNAME2 , FNAME3
INTO #LEVEL2 FROM #DATA
Where FSumSort=109
ORDER BY FLEVEL0 , FLEVEL1
UPDATE a3 SET a3.FLevel2=lv.FLevel2
FROM #DATA a3
INNER JOIN #LEVEL2 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0) AND ISNULL(lv.FNAME3,0)=ISNULL(a3.FNAME3,0)
Where Not( FSumSort=106)
DROP TABLE #LEVEL2
SELECT IDENTITY(INT,1,1) AS FLevel3,FNAME1 ,FNAME2 ,FNAME3 , FSTOCKNAME
INTO #LEVEL3 FROM #DATA
Where FSumSort=110
ORDER BY FLEVEL0 , FLEVEL1, FLEVEL2
UPDATE a3 SET a3.FLevel3=lv.FLevel3
FROM #DATA a3
INNER JOIN #LEVEL3 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0) AND ISNULL(lv.FNAME3,0)=ISNULL(a3.FNAME3,0) AND ISNULL(lv.FSTOCKNAME,0)=ISNULL(a3.FSTOCKNAME,0)
Where Not( FSumSort=106)
DROP TABLE #LEVEL3
Update #DATA Set FLevel0=FLevel0+0.9 Where FSumSort=107
Update #DATA Set FLevel1=FLevel1+0.9 Where FSumSort=108
Update #DATA Set FLevel2=FLevel2+0.9 Where FSumSort=109
Update #DATA Set FLevel3=FLevel3+0.9 Where FSumSort=110
Update #Data Set FName1=isnull(FName1,'')+'(小计)' WHERE FSumSort=107
Update #Data Set FName2=isnull(FName2,'')+'(小计)' WHERE FSumSort=108
Update #Data Set FName3=isnull(FName3,'')+'(小计)' WHERE FSumSort=109
Update #Data Set FStockName=Rtrim(ISNULL(FStockName,''))+'(小计)' WHERE FSumSort=110
Update #Data Set FName1='合计' WHERE FSumSort=106
Update #Data Set FSumSort=101 WHERE FSumSort=106
SELECT * FROM #DATA
Order By FLevel0, FLevel1, FLevel2, FLevel3
DROP TABLE #DATA
DROP TABLE #ItemLevel
Drop Table #Happen
Drop Table #Happen1
Drop Table #Happen2
DROP TABLE #INVENTORY