ALTER Proc [dbo].[Get_XTCPXYJBB]
(
	@FSaleOrgId nvarchar(1000),--组织
	@FCZZHTH nvarchar(1000),--纸质合同号
	--@FStartDate nvarchar(1000),
	--@FEndDate nvarchar(1000),
	@FStartDate2 nvarchar(1000),
	@FEndDate2 nvarchar(1000)
)
as

Select
	A.FID as 'FFID',
	CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY A.FId) != 1 
	Then ''
	ELSE A.F_contractnumber
	END as 'PaperContract', --纸质合同号
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY A.FId) != 1 
	Then null
	ELSE A.FAPPROVEDATE
	END as 'FDate', --下单时间
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY A.FId) != 1 
	Then ''
	ELSE W2.区域
	END as 'SBU', --SBU(明细区域)
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY A.FId) != 1 
	Then ''
	ELSE W1.FNAME
	END as 'Salesman', --业务员
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY A.FId) != 1 
	Then ''
	ELSE
	(Select FNAME From T_BAS_PREBDONE_L Where FID = A.F_projectname) 
	END as 'ProjectName', --项目名称
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then null
	ELSE
	(Select SUM(a1.FALLAMOUNT_LC) From T_SAL_ORDERENTRY_F a1 JOIN T_SAL_ORDERENTRY a2 on
	a1.FENTRYID = a2.FENTRYID Where a1.FID = A.FID AND a2.FMANUALROWCLOSE != '1')
	END as 'ContractTotal', --合同总额
	CASE WHEN C.FIsSoftware = 1
	THEN ISNULL(BB.FALLAMOUNT_LC,0)
	ELSE null
	END as 'SoftwarePrice', --软件金额
	C.FNAME as 'SystemName', --备注(系统名称)
	C.F_114 as 'ModelCategories', --型号大类
	C.F_116 as 'IndustryLine', --行业线
	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 'ProjectField', --项目所属领域
	B.FNOTE as 'Remark', --备注
	CASE WHEN --项目所属1
	(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 'DirectAgent', --直销/代理商
	(A.F_contractnumber+C.FSPECIFICATION) as 'ProductsSizeModel' --订单+软件型号
	Into #TempTable1
From--关联表
    T_SAL_ORDER A
	JOIN
	T_SAL_ORDERFIN AA 
	on AA.FID = A.FID
	LEFT JOIN
	T_ORG_ORGANIZATIONS_L AAA
	on A.FSALEORGID = AAA.FORGID
    JOIN
    T_SAL_ORDERENTRY B
    on B.FID = A.FID
	JOIN
	T_SAL_ORDERENTRY_F BB 
	on BB.FENTRYID = B.FENTRYID
	LEFT JOIN 
	V_BD_SALESMAN_L W1 
	on A.FSalerId = W1.fid
	LEFT JOIN
	(Select 名称,区域,明细区域 From ERPTOHR.HYHRV3.dbo.v_erp_empinfo Where ISNULL(明细区域,'')!='' 
	AND ISNULL(区域,'')!='' GROUP BY 名称,区域,明细区域) W2
	on W1.FNAME = W2.名称
	LEFT JOIN 
	T_BD_CUSTOMER_L K on K.FCUSTID = A.FCUSTID
	LEFT JOIN 
	(Select FNumber,FENTRYID From T_BAS_ASSISTANTDATAENTRY Where FID = '670cc977263353') W8 --合同类型
	on W8.FENTRYID = A.F_ORDERCATEGORY
    JOIN
    (Select aq.F_114,aq.FMATERIALID,qa.FNAME,aq.F_XHDL,aq.F_116,qa.FSpecification,aq.FIsSoftware 
    From 
    T_BD_MATERIAL aq
    JOIN
    T_BD_MATERIAL_L qa
    on aq.FMATERIALID = qa.FMATERIALID) C
    on B.FMaterialId = C.FMATERIALID
    --条件
    Where A.FDocumentStatus = 'C' AND W8.FNUMBER != 'Z' AND A.FSALEORGID = 100302
	AND B.FMANUALROWCLOSE != '1' AND A.FMANUALCLOSE != '1'
	AND (ISNULL(@FSaleOrgId,'') = '' or AAA.FNAME = @FSaleOrgId)
	AND (ISNULL(@FCZZHTH,'') = '' or A.F_contractnumber like '%'+@FCZZHTH+'%')
	AND (ISNULL(@FStartDate2,'') = '' or A.FAPPROVEDATE >= @FStartDate2)
	AND (ISNULL(@FEndDate2,'') = '' or A.FAPPROVEDATE < DATEADD(DAY, 1, CONVERT(DATE, @FEndDate2)))
	AND ISNULL(W2.区域,'') != '碳索智控' 
	AND ISNULL(K.FNAME,'') NOT IN ('派诺科技（香港）有限公司','武汉派诺科技发展有限公司')
	AND ISNULL(W1.FNAME,'') != '兴诺总'
Order by A.FID,A.FAPPROVEDATE desc

Select
	A.FID as 'FFID',
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then ''
	ELSE A.F_contractnumber
	END as 'PaperContract', --纸质合同号
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then null
	ELSE A.FAPPROVEDATE
	END as 'FDate', --下单时间
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then ''
	ELSE W2.区域
	END as 'SBU', --SBU(明细区域)
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then ''
	ELSE W1.FNAME
	END as 'Salesman', --业务员
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then ''
	ELSE
	(Select FNAME From T_BAS_PREBDONE_L Where FID = A.F_projectname) 
	END as 'ProjectName', --项目名称
	CASE When ROW_NUMBER() OVER (PARTITION BY A.FId ORDER BY B.FSEQ) != 1 
	Then null
	ELSE
	ISNULL(AA.FBILLALLAMOUNT_LC,0)
	END as 'ContractTotal', --合同总额
	CASE WHEN C.FIsSoftware = 1
	THEN ISNULL(BB.FALLAMOUNT_LC,0)
	ELSE null
	END as 'SoftwarePrice', --软件金额
	C.FNAME as 'SystemName', --备注(系统名称)
	C.F_114 as 'ModelCategories', --型号大类
	C.F_116 as 'IndustryLine', --行业线
	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 'ProjectField', --项目所属领域
	B.FNOTE as 'Remark', --备注
	CASE WHEN --项目所属1
	(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 'DirectAgent', --直销/代理商
	(A.F_contractnumber+C.FSPECIFICATION) as 'ProductsSizeModel' --订单+软件型号
	Into #TempTable2
From--关联表
    T_SAL_ORDER A
	JOIN
	T_SAL_ORDERFIN AA 
	on AA.FID = A.FID
	LEFT JOIN
	(Select * From T_ORG_ORGANIZATIONS_L Where FLOCALEID = 2025) AAA
	on A.FSALEORGID = AAA.FORGID
    JOIN
    T_SAL_ORDERENTRY B
    on B.FID = A.FID
	JOIN
	T_SAL_ORDERENTRY_F BB 
	on BB.FENTRYID = B.FENTRYID
	LEFT JOIN 
	V_BD_SALESMAN_L W1 
	on A.FSalerId = W1.fid
	LEFT JOIN
	(Select 名称,区域,明细区域 From ERPTOHR.HYHRV3.dbo.v_erp_empinfo Where ISNULL(明细区域,'')!='' 
	AND ISNULL(区域,'')!='' GROUP BY 名称,区域,明细区域) W2
	on W1.FNAME = W2.名称
	LEFT JOIN 
	(Select FNumber,FENTRYID From T_BAS_ASSISTANTDATAENTRY Where FID = '670cc977263353') W8 --合同类型
	on W8.FENTRYID = A.F_ORDERCATEGORY
    JOIN
    (Select aq.F_114,aq.FMATERIALID,qa.FNAME,aq.F_XHDL,aq.F_116,qa.FSpecification,aq.FIsSoftware 
    From 
    T_BD_MATERIAL aq
    JOIN
    T_BD_MATERIAL_L qa
    on aq.FMATERIALID = qa.FMATERIALID) C
    on B.FMaterialId = C.FMATERIALID
    --条件
    Where A.FDocumentStatus = 'C' 
	--AND A.FID in (Select q1.FID From T_SAL_ORDER q1 JOIN T_SAL_ORDERENTRY q2 on q1.FID = q2.FId 
	--JOIN T_BD_MATERIAL q3 on q3.FMATERIALID = q2.FMaterialId Where q3.FISSOFTWARE = 0)
	AND (ISNULL(@FSaleOrgId,'') = '' or AAA.FNAME = @FSaleOrgId)
	AND (ISNULL(@FCZZHTH,'') = '' or A.F_contractnumber like '%'+@FCZZHTH+'%')
	AND W8.FNUMBER != 'Z' AND A.FSALEORGID != 100302 AND B.FMANUALROWCLOSE != '1' AND A.FMANUALCLOSE != '1'
	AND (ISNULL(@FStartDate2,'') = '' or A.FAPPROVEDATE >= @FStartDate2)
	AND (ISNULL(@FEndDate2,'') = '' or A.FAPPROVEDATE < DATEADD(DAY, 1, CONVERT(DATE, @FEndDate2)))
Order by A.FID,A.FAPPROVEDATE desc

Select * INTO #TempTable3 From #TempTable1 UnION ALL (Select * From #TempTable2)

Select * From #TempTable3 ORder By FFID,FDate desc

DROP TABLE #TempTable1
DROP TABLE #TempTable2
DROP TABLE #TempTable3


Select F_Authorization,* From T_BD_CUSTOMER