ALTER Proc [dbo].[Get_KPHCKQKCXBB]
(
	@FSaleOrgId nvarchar(1000),--组织
	@FKH nvarchar(1000),--客户
	@FCZZHTH nvarchar(1000),--纸质合同号
	@FGCXM nvarchar(1000),--工程项目
	--@FStartDate nvarchar(1000),
	--@FEndDate nvarchar(1000),
	@FStartDate2 nvarchar(1000),
	@FEndDate2 nvarchar(1000)
)
as
Select 
A.FAPPROVEDATE as '下单日期'
,A.FBILLNO as '单据编号'
,A.F_PROJECTCODE as '工程项目编码'
,A.F_CONTRACTNUMBER as '合同号'
,G.FNAME as '项目名称'
,K.FNAME as '客户名称'
,BM.FNAME as '销售部门'
,X.FNAME as '销售员'
,Y.FNAME as '制单人'
,B.FBILLALLAMOUNT_LC as '订单金额' --订单金额
,ISNULL(F.FBILLALLAMOUNT_LC,0) as '已发货金额' --已发货金额
,ISNULL(T.FBILLALLAMOUNT_LC,0) as '退货金额' --退货金额
,B.FBILLALLAMOUNT_LC-ISNULL(F.FBILLALLAMOUNT_LC,0) as '未发货金额' --未发货金额
,ISNULL(C.FBILLALLAMOUNT_LC,0)-ISNULL(TH.FBILLALLAMOUNT_LC,0) as '销售出库成本' --销售出库成本
,ISNULL(SS.FENDBALANCE,0) as '实施成本'--其他出库成本
,ISNULL(P1.FAFTERTOTALTAX,0) as '专票金额'--已开票金额
,ISNULL(P2.FAFTERTOTALTAX,0) as '普票金额'--普票金额
,F.MinFDate as '首次发货日期' --首次发货日期
,F.MaxFDate as '最后一次发货日期' --最后一次发货日期
,B.FBILLALLAMOUNT_LC - ISNULL(T.FBILLALLAMOUNT_LC,0) - ISNULL(P1.FAFTERTOTALTAX,0) - ISNULL(P2.FAFTERTOTALTAX,0) as '未开票金额' --未开票金额
,ISNULL(A.F_AMOUNT,0)+ISNULL(S.FRECAMOUNT,0) as '回款金额' --回款金额
From
T_SAL_ORDER A
JOIN
(Select FID,SUM(FBILLALLAMOUNT_LC)'FBILLALLAMOUNT_LC'
From T_SAL_ORDERFIN GROUP BY FID) B --财务信息
on A.FID = B.FID
LEFT JOIN --工程项目
(Select a1.FNUMBER,a2.FNAME 
From T_BAS_PREBDONE a1 JOIN T_BAS_PREBDONE_L a2
on a1.FID = a2.FID GROUP BY a1.FNUMBER,a2.FNAME) G
on A.F_PROJECTCODE = G.FNUMBER
LEFT JOIN
T_BD_CUSTOMER_L K --客户
on A.FCUSTID = K.FCUSTID
LEFT JOIN
V_BD_SALESMAN_L X --销售员
on X.fid = A.FSALERID
LEFT JOIN
T_SEC_USER Y --用户
on Y.FUSERID = A.FCREATORID
LEFT JOIN
T_BD_DEPARTMENT_L BM --部门
on A.FSALEDEPTID = BM.FDEPTID
LEFT JOIN
(Select a1.F_CONTRACTNUMBER,SUM(a2.FBILLALLAMOUNT_LC)'FBILLALLAMOUNT_LC' From T_SAL_RETURNSTOCK a1 JOIN T_SAL_RETURNSTOCKFIN a2 on a1.FID = a2.FID
Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.F_CONTRACTNUMBER) TH --销售退货
on TH.F_CONTRACTNUMBER = A.F_CONTRACTNUMBER
LEFT JOIN
(Select a1.F_PAPERCONTRACT,SUM(a2.FBILLALLAMOUNT_LC)'FBILLALLAMOUNT_LC',MIN(a1.FDATE) as 'MinFDate',MAX(a1.FDATE) as 'MaxFDate'
From T_SAL_DELIVERYNOTICE a1 JOIN T_SAL_DELIVERYNOTICEFIN
a2 on a1.FID = a2.FID Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.F_PAPERCONTRACT) F
on F.F_PAPERCONTRACT = A.F_CONTRACTNUMBER
LEFT JOIN
(Select a1.F_PAPERCONTRACT,SUM(a2.FBILLALLAMOUNT_LC)'FBILLALLAMOUNT_LC'
From T_SAL_RETURNNOTICE a1 JOIN T_SAL_RETURNNOTICEFIN
a2 on a1.FID = a2.FID Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.F_PAPERCONTRACT) T
on T.F_PAPERCONTRACT = A.F_CONTRACTNUMBER
LEFT JOIN
(Select a1.F_CONTRACTNUMBER,SUM(a3.FCOSTAMOUNT_LC)'FBILLALLAMOUNT_LC'
From T_SAL_OUTSTOCK a1 JOIN T_SAL_OUTSTOCKENTRY_F a3 on a1.FID = a3.FID
 Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.F_CONTRACTNUMBER) C
on C.F_CONTRACTNUMBER = A.F_CONTRACTNUMBER
LEFT JOIN
(SELECT F_PAPERNUMBER,SUM(FAFTERTOTALTAX)'FAFTERTOTALTAX' FROM T_IV_SALESIC 
Where FDOCUMENTSTATUS = 'C' AND F_GeneralInvoice!='Y' GROUP BY F_PAPERNUMBER) P1
on P1.F_PAPERNUMBER = A.F_CONTRACTNUMBER
LEFT JOIN
(SELECT F_PAPERNUMBER,SUM(FAFTERTOTALTAX)'FAFTERTOTALTAX' FROM T_IV_SALESIC 
Where FDOCUMENTSTATUS = 'C' AND F_GeneralInvoice ='Y' GROUP BY F_PAPERNUMBER) P2
on P2.F_PAPERNUMBER = A.F_CONTRACTNUMBER
LEFT JOIN
(Select a1.F_ZZHTH,SUM(a2.FAMOUNT)'FAMOUNT' From T_STK_MISDELIVERY a1
JOIN T_STK_MISDELIVERYENTRY	a2 on a1.FID = a2.FID
Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.F_ZZHTH) QC
on QC.F_ZZHTH = A.F_CONTRACTNUMBER
LEFT JOIN
T_ORG_ORGANIZATIONS_L Z on Z.FORGID = A.FSALEORGID
LEFT JOIN
(Select F_CONTRACTNUMBER,SUM(FRECAMOUNT) as 'FRECAMOUNT'
From T_AR_RECEIVEBILL Where FDOCUMENTSTATUS = 'C' GROUP BY F_CONTRACTNUMBER) S --收款单
on S.F_CONTRACTNUMBER = A.F_CONTRACTNUMBER
LEFT JOIN
(Select a2.FNUMBER,SUM(a1.FENDBALANCE) as 'FENDBALANCE'
From 
VIEW_GL_BALANCE a1 
JOIn 
(Select * From T_BAS_PREBDONE Where FDOCUMENTSTATUS = 'C') a2
on a1.FF100005 = a2.FID GROUP BY a2.FNUMBER) SS 
on SS.FNUMBER =  A.F_CONTRACTNUMBER
Where A.FDOCUMENTSTATUS = 'C' 
AND (ISNULL(@FSaleOrgId,'') = '' or Z.FNAME = @FSaleOrgId)
AND (ISNULL(@FKH,'') = '' or K.FNAME like '%'+@FKH+'%')
AND (ISNULL(@FCZZHTH,'') = '' or A.F_CONTRACTNUMBER like '%'+@FCZZHTH+'%')
AND (ISNULL(@FGCXM,'') = '' or G.FNAME like '%'+@FGCXM+'%')
--AND (ISNULL(@FStartDate,'') = '' or A.FDATE >= @FStartDate)
--AND (ISNULL(@FEndDate,'') = '' or A.FDATE <= @FEndDate)
AND (ISNULL(@FStartDate2,'') = '' or A.FAPPROVEDATE >= @FStartDate2)
AND (ISNULL(@FEndDate2,'') = '' or A.FAPPROVEDATE <= @FEndDate2);