sum(case B_Process when '后处理' then B_FactSum else 0 end) +
sum(case B_Process when '洗缸' then B_FactSum else 0 end) +
sum(case B_Process when '冲缸' then B_FactSum else 0 end) +
sum(case B_Process when '剥色' then B_FactSum else 0 end) +
sum(case B_Process when '落水' then B_FactSum else 0 end) as B_FactSum,
@m_SDate as 起始日期,
@m_EDate as 终止日期,
Convert(varchar(100),GetDate(),20) as B_PrintDate
from
(
Select B_Pople as B_People,B_Process,
sum(ISNULL(B_Volume,0)) as B_Volume,sum(ISNULL(B_Volume,0)) * G_CJPriceYR.B_Price as B_FactSum
From
(
Select G_YRBill.B_GangHao,G_YRBill.B_Pople,G_YRBill.B_Date,
G_YRBill.B_Rate,G_YRBill.B_Process,
G_YRBill.B_Rate * 0.1 * G_YRGang.B_OutPut as B_Volume
From G_YRBill,G_YRGang
Where 1=1
And G_YRBill.B_GangHao=G_YRGang.B_GangHao
And G_YRBill.B_Date Between cast(@m_SDate as datetime) And cast(@m_EDate as datetime)
And B_VolumeValidPeople=1
And G_YRBill.B_GangHao<>'上中'
And G_YRBill.B_GangHao<>'下中'
And G_YRBill.B_ID Not In
(
--时间段内所有公斤数<50公斤的
Select B_ID
From
(
Select G_YRBill.B_ID,
dbo.[GetQtyFact](G_YRBillDetailBarCode.B_BarCode) as B_Qty
From G_YRBill,G_YRGang,G_YRBillDetailBarCode
Where 1=1
And G_YRBill.B_GangHao=G_YRGang.B_GangHao
And G_YRBill.B_Date Between cast(@m_SDate as datetime) And cast(@m_EDate as
datetime)
And B_VolumeValidPeople=1
And G_YRBill.B_GangHao <>'上中'
And G_YRBill.B_GangHao <>'下中'
And G_YRBill.B_ID=G_YRBillDetailBarCode.B_ID
) as P
Where isnull(P.B_Qty,0)<50
)
) as P Left Outer Join G_CJPriceYR
ON G_CJPriceYR.B_Type='非中样'
And G_CJPriceYR.B_ProcessName=P.B_Process
Group by B_Pople,B_Process,G_CJPriceYR.B_Price
) as P Left Outer Join G_YRPeople
ON P.B_People=G_YRPeople.B_Name
Group by B_People,ISNULL(G_YRPeople.B_Class,'')
Order By ISNULL(G_YRPeople.B_Class,''),B_People
