Piolot_RepotForm_PeiHao/SQLQuery1.sql

Select
            MONTH(A.FDATE) as '月份', --月份
            (SELECT FNAME FROM T_BD_DEPARTMENT_L WHERE FDEPTID = A.FSaleDeptId) as '所属区域', --所属区域
            (Select FNAME From V_BD_SALESMAN_L Where fid = A.FSalerId) as '销售员', --销售员
            (SELECT FNAME FROM T_BD_CUSTOMER_L WHERE FCUSTID = A.FCustId) as '购货单位', --购货单位
            A.FDATE as '签订日期', --签订日期
            A.F_contractnumber as '订单号', --订单号
            C.FNAME as '产品名称', --产品名称
            C.FSPECIFICATION as '型号规格标准', --型号\规格\标准
            A.F_contractnumber+C.FSPECIFICATION as '订单号型号规格标准',
            '' as '型号分类',
            '' as '型号大类',
            '' as '业务线',
            CASE WHEN A.F_VRYF_COMBO_LSN = 'DDXS' THEN '单独销售'
            WHEN A.F_VRYF_COMBO_LSN = 'SXTXS' THEN '随系统销售'
            WHEN A.F_VRYF_COMBO_LSN = 'XTZHT' THEN '系统主合同' ELSE ''
            END as '销售类型',
            '' as 'OEM型号',--原取消字段
            '' as '具体参数',--原取消字段
            B.FQty as '数量台', --数量/台
            (Select FTaxPrice From T_SAL_ORDERENTRY_F Where FENTRYID = B.FENTRYID) as '单价元台', --单价元/台
            (Select FAllAmount_LC From T_SAL_ORDERENTRY_F Where FENTRYID = B.FENTRYID) as '合同总价', --合同总价
            (Select FStockOutQty From T_SAL_ORDERENTRY_R Where FENTRYID = B.FENTRYID) as '已交货数', --已交货数
            (Select FRemainOutQty From T_SAL_ORDERENTRY_R Where FENTRYID = B.FENTRYID) as '未交货数', --未交货数
            CASE WHEN (D.FOPENSTATUS)= 'A' Then '空' When (D.FOPENSTATUS)= 'B' Then '部分' When (D.FOPENSTATUS)= 'C' Then '完全' Else ''
            END as '开票情况', --开票情况 A.F_VRYF_Assistant_k79
            (Select FNAME From T_BAS_PREBDONE_L Where FID = A.F_projectname) as '项目名称', --项目名称
            CASE WHEN --项目所属1
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 1 
            THEN '建筑楼宇'
            WHEN --项目所属2
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 2
            THEN '医疗卫生'
            WHEN --项目所属3
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 3
            THEN '学校'
            WHEN --项目所属4
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 4
            THEN '文娱康乐'
            WHEN --项目所属5
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 5
            THEN '市政工程'
            WHEN --项目所属6
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 6
            THEN '能源'
            WHEN --项目所属7
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 7
            THEN '工业'
            WHEN --项目所属8
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 8
            THEN '交通运输'
            ELSE ''
            END as '项目所属领域', --项目所属领域
            CASE WHEN --集采业务分类
            A.F_PurchasingPro = 'LH' 
            THEN '龙湖'
            WHEN --集采业务分类
            A.F_PurchasingPro = 'BL' 
            THEN '保利地产'
            WHEN --集采业务分类
            A.F_PurchasingPro = 'HR' 
            THEN '华润'
            WHEN --集采业务分类
            A.F_PurchasingPro = 'N' 
            THEN '否'
            WHEN --集采业务分类
            A.F_PurchasingPro = 'HEF' 
            THEN '华润非集采'
            WHEN --集采业务分类
            A.F_PurchasingPro = 'LD' 
            THEN '绿地'
            ELSE ''
            END as '集采业务分类', --集采业务分类 LH=龙湖,BL=保利地产,HR=华润,N=否,HEF=华润非集采,LD=绿地
            CASE WHEN A.FBUSINESSTYPE = 'VMI' THEN 'VMI业务' WHEN A.FBUSINESSTYPE = 'DRPTRANS' THEN '分销调拨'
            WHEN A.FBUSINESSTYPE = 'STRAIGHT' THEN '直运销售' WHEN A.FBUSINESSTYPE = 'CONSIGNMENT' THEN '寄售'
            WHEN A.FBUSINESSTYPE = 'NORMAL' THEN '普通销售' WHEN A.FBUSINESSTYPE = 'DRPSALE' THEN '分销购销'
            WHEN A.FBUSINESSTYPE = 'RETURNSO' THEN '退货订单' ELSE ''
            END as '业务类型-按产品划分',
            CASE WHEN
	        (Select F_Authorization From T_BD_CUSTOMER where FCUSTID = A.FCustId) = 'Y' THEN '代理商'
	        WHEN (Select F_Authorization From T_BD_CUSTOMER where FCUSTID = A.FCustId) = 'N' THEN '直销'
	        ELSE '' 	        END as '直销经销商',
            CASE WHEN B.F_SPECIALOFFER = 0 THEN '否' WHEN B.F_SPECIALOFFER = 1 THEN '是' ELSE '' END as '是否特价',
            '' as '业绩类型',
            (Select wa.FDATAVALUE From
            T_BAS_ASSISTANTDATAENTRY aw
            JOIN 
            T_BAS_ASSISTANTDATAENTRY_L wa
            on aw.FENTRYID = wa.FENTRYID
            Where wa.FENTRYID = (Select F_CreditClassification From T_BD_CUSTOMER where FCUSTID = A.FCustId)
            AND FDocumentStatus = 'C' AND FFORBIDSTATUS = 'A') as '客户性质', --客户性质
            --(Select top 1 V1.区域 From
	           -- ERPTOHR.HYHRV3.dbo.v_erp_empinfo V1
	           -- JOIN
	           -- (Select FName From V_BD_SALESMAN_L
		          --  Where 
		          --  fid in (Select A1.FSALEID From
		          --  (Select a2.* From 
		          --  T_PerformanceSharing a1
		          --  JOIN
		          --  T_PerformanceSharingEntry a2
		          --  on a1.FID = a2.FID
		          --  Where a1.FDocumentStatus = 'C') A1
		          --  JOIN
		          --  T_PerformanceSharing_LK A2
		          --  on A2.FID = A1.FID
		          --  Where A2.FSTableName = 'T_SAL_ORDER1'
		          --  AND A2.FSId = B.FID AND A1.FSALEID = A.FSalerId
		          --  )) V2
	           -- on V1.名称 = V2.FNAME GROUP BY V1.区域)
            --as 'SBU' --SBU
            --Into #TempTable1 
			ISNULL(F.区域,'') as 'SBU',
            '' as '客户性质', --新增字段
            '' as '项目方案类型', --新增字段
            '' as '是否直签过系统', --新增字段
            '' as '所属省份', --新增字段
            '' as '表计或系统', --新增字段
            '' as '业绩分摊', --新增字段
            B.FNOTE as '备注', --新增字段
            '' as '订单类型' --新增字段
            From
            T_SAL_ORDER A
            JOIN
            T_SAL_ORDERENTRY B
            on B.FID = A.FID
            Left JOIN
            (Select aq.FMATERIALID,qa.FNAME,aq.F_XHDL,aq.F_116,qa.FSpecification 
            From 
            T_BD_MATERIAL aq
            Left JOIN
            T_BD_MATERIAL_L qa
            on aq.FMATERIALID = qa.FMATERIALID) C
            on B.FMaterialId = C.FMATERIALID
            LEFT JOIN 
            (Select A2.FOPENSTATUS,A1.FID,A1.FORDERNUMBER From T_AR_RECEIVABLEENTRY A1 JOIN T_AR_RECEIVABLE A2 on A1.FID = A2.FID
            Where A2.FSETACCOUNTTYPE = 3 Group by A2.FOPENSTATUS,A1.FID,A1.FORDERNUMBER) D on A.FBILLNO = D.FORDERNUMBER
			LEFT JOIN 
			V_BD_SALESMAN_L E on A.FSALERID = E.fid
			LEFT JOIN 
			ERPTOHR.HYHRV3.dbo.v_erp_empinfo F on E.FNAME = F.名称
            --条件
            Where A.FDocumentStatus = 'C'
            AND A.FCloseStatus = 'A'
            AND A.FCancelStatus = 'A'
            AND A.FSALEORGID = _CurrentOrgUnitId_
AND ('#FPaperContract#' = '' or A.F_contractnumber like '%#FPaperContract#%')
AND ('#FStartDate#' = '' or A.FDATE >= '#FStartDate#') 
AND ('#FEndDate#' = '' or A.FDATE <= '#FEndDate#') 
Order by A.FDATE desc