Piolot_RepotForm_PeiHao/销售进度1.sql

Select
A.FDate as 'Date',
AAAA.FNAME as 'OrderName', --<EFBFBD>Ƶ<EFBFBD><EFBFBD><EFBFBD>
A.FDATE as 'OrderDate',--<EFBFBD>µ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(B.FNAME,'') as 'ClientName', --<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
A.F_contractnumber as 'ContractNumber', --ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
ISNULL(C.FNAME,'') as 'ProjectName', --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(D.FNAME,'') as 'Salesman', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա
AA.FBILLALLAMOUNT_LC as 'ContractPrice', --<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
(Select STRING_AGG(aa2.FNAME,',') as 'FNAME'
From T_AR_RECEIVEBILL aa1 JOIN T_SEC_USER aa2 on aa1.FCREATORID = aa2.FUSERID
Where FBILLNO in (Select FBILLNO3 From T_AutoWrireRecordEntry a1 JOIN T_AutoWrireRecordEntry2 a2
on a1.FID = a2.FID Where FBILLNO2 = A.FBILLNO))
as 'CollectionName', --<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
(Select STRING_AGG((CONVERT(nvarchar,aa1.FDATE,23)),',') as 'FDate'
From T_AR_RECEIVEBILL aa1 JOIN T_SEC_USER aa2 on aa1.FCREATORID = aa2.FUSERID
Where FBILLNO in (Select FBILLNO3 From T_AutoWrireRecordEntry a1 JOIN T_AutoWrireRecordEntry2 a2
on a1.FID = a2.FID Where FBILLNO2 = A.FBILLNO))
as 'CollectionDate', --<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(A.F_AMOUNT,0) as 'CollectionAmount', --<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E6.FNAME,'') as 'RefundName', --<EFBFBD>˿<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E6.FDate,'') as 'RefundDate', --<EFBFBD>˿<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E6.FREALREFUNDAMOUNT,0) as 'RefundAmount', --<EFBFBD>˿<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E1.FName,'') as 'ShipmentsName', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E1.FDate,'') as 'ShipmentsDate', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E1.FPriceUnitQty,0) as 'ShipmentsNum', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E1.FALLAMOUNT_LC,0) as 'ShipmentsAmount', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E2.FNAME,'') as 'OutShipmentsName', --<EFBFBD><EFBFBD><EFBFBD>⸺<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E2.FDate,'') as 'OutShipmentsDate', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E2.FREALQTY,0) as 'OutShipmentsNum', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E2.FALLAMOUNT_LC,0) as 'OutShipmentsAmount', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E2.FSIGNQTY,0) as 'SignNum', --ǩ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
CASE WHEN E2.FTRACESTATUS = 3 THEN '<EFBFBD><EFBFBD>ǩ<EFBFBD><EFBFBD>'
ELSE 'δǩ<EFBFBD><EFBFBD>'
END as 'IsSign', --<EFBFBD>Ƿ<EFBFBD>ǩ<EFBFBD><EFBFBD>
ISNULL(E3.FNAME,'') as 'ReturnName', --<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E3.FDate,'') as 'ReturnDate', --<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E3.FPriceUnitQty,0) as 'ReturnNum', --<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E3.FALLAMOUNT_LC,0) as 'ReturnAmount', --<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E4.FNAME,'') as 'BillingName', --<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E4.F_INVOICENUMBER,'') as 'BillingNumber', --<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E4.FDate,'') as 'BillingDate', --<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E4.FPriceQty,0) as 'BillingNum', --<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ISNULL(E4.FALLAMOUNT,0) as 'BillingAmount' --<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--0 as 'NotCollectedAmount' --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>δ<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--INTO #TEMPTABLE1
From 
T_SAL_ORDER A --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>
JOIN
T_SAL_ORDERFIN AA --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
on A.FID = AA.FID
LEFT JOIN 
T_ORG_ORGANIZATIONS_L AAA 
on AAA.FORGID = A.FSaleOrgId
LEFT JOIN 
T_SEC_USER AAAA 
on AAAA.FUSERID = A.FCREATORID
LEFT JOIN
(SELECT A2.FDATAVALUE,A1.* FROM
(Select q2.FNAME,q1.* From 
T_BD_CUSTOMER q1
JOIN
T_BD_CUSTOMER_L q2
on q1.FCUSTID = q2.FCUSTID) A1
LEFT JOIN
(Select A.FMASTERID,B.FDATAVALUE From T_BAS_ASSISTANTDATAENTRY A
JOIN T_BAS_ASSISTANTDATAENTRY_L B on A.FENTRYID = B.FENTRYID
Where A.FID = '673af6092e2877'
AND A.FDocumentStatus = 'C'
AND A.FForbidStatus = 'A') A2
on A1.F_CREDITCLASSIFICATION = A2.FMASTERID) B --<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD>
on A.FCustId = B.FCUSTID
LEFT JOIN
(Select q2.FNAME,q1.* From 
T_BAS_PREBDONE q1
JOIN
T_BAS_PREBDONE_L q2
on q1.FID = q2.FID
Where q2.FLocaleID = 2052
AND q1.FDocumentStatus = 'C'
AND q1.FForbidStatus = 'A') C --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD>Ԥ<EFBFBD><EFBFBD>1<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD>
on C.FID = A.F_projectname
LEFT JOIN
(Select q2.FNAME,q1.* From 
V_BD_SALESMAN q1
LEFT JOIN
V_BD_SALESMAN_L q2
on q1.fid = q2.fid
Where q2.FLOCALEID = 2052
AND q1.FDocumentStatus = 'C'
AND q1.FForbidStatus = 'A'
AND q1.FForbiddenStatus = '0') D --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>
on A.FSalerId = D.fid
LEFT JOIN V_SXJD_Table1 E1 --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>֪ͨ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
on A.FID = E1.FSBILLID
LEFT JOIN V_SXJD_Table2 E2 --<EFBFBD><EFBFBD><EFBFBD>۳<EFBFBD><EFBFBD>ⵥ
on A.FID = E2.FSBILLID
LEFT JOIN V_SXJD_Table3 E3 --<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
on A.FID = E3.FSBILLID
LEFT JOIN V_SXJD_Table4 E4 --Ӧ<EFBFBD>յ<EFBFBD><EFBFBD><EFBFBD>
on A.FID = E4.FSBILLID
LEFT JOIN V_SXJD_Table6 E6
on A.F_CONTRACTNUMBER = E6.F_VRYF_TEXT_83G
Where A.FDocumentStatus = 'C' AND A.FBusinessType = 'NORMAL'
--AND ('#CurrentOrgUnitId#' = '' or AAA.FNAME = '#CurrentOrgUnitId#');--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>֯

--Select *,(ContractPrice-CollectionAmount-RefundAmount) as'NotCollectedAmount'  From #TEMPTABLE1 
--Where ('#FStartDate#' = '' or DATE >= '#FStartDate#') AND ('#FEndDate#' = '' or DATE <= '#FEndDate#') AND ('#FPaperContract#' = '' or ContractNumber like '%#FPaperContract#%');


Select *  From 
            (Select A.FID From View_Table_Temp2 A JOIN View_Table_Temp3 B 
            on A.FCONTRACTNUMBER=B.FCONTRACTNUMBER AND A.FCREATEDATE = B.FCREATEDATE
            UNION ALL
            Select * From View_Table_Temp1) W