
select 日期,t4.SalName 销售员,t4.DepName 销售部门,客服部门,主任, 工程师,t5.FNAME 客户名称,nullif(convert(decimal(18,4),标准金额),0) 标准金额,nullif(convert(decimal(18,4),销售金额),0) 销售金额,
nullif(convert(decimal(18,4),产品成本金额),0) 产品成本金额
,convert(varchar(20),CONVERT(DECIMAL(18,2),round((1.0*(标准金额))/(1.0*(nullif(销售金额,0))),5,5)*100))+'%'  as '标准销售比值',
convert(varchar(20),CONVERT(DECIMAL(18,2),round((1.0*(产品成本金额))/(1.0*(nullif(销售金额,0))),5,5)*100))+'%'  as 'BOM成本销售比值',
convert(varchar(20),CONVERT(DECIMAL(18,2),(销售金额-产品成本金额)/nullif(销售金额,0)*100))+'%' as '毛利率',
row_number() over( ORDER BY isnull((销售金额-产品成本金额)/nullif(销售金额,0),0) desc) as 名次 from (
select t2.FMASTERID,max(FSELLER)FSELLER from   T_SAL_OUTSTOCK  t1
left join (select FMASTERID,FCUSTID from T_BD_CUSTOMER) t2 on t2.FCUSTID=t1.FCUSTOMERID
left join (select FMASTERID,max(FSELLER) FSELLER from T_BD_CUSTOMER group by FMASTERID) t3 on t3.FMASTERID=t2.FMASTERID
where --(convert(varchar(6),FDATE,112) ='#FDATE#' or '#FDATE#'='')  and 
t1.FDOCUMENTSTATUS='C' and t1.FCANCELSTATUS='A'
group by t2.FMASTERID
)t1 
left join (--包线
	select FMASTERID,sum(FREALQTY)qty,sum(销售金额) 销售金额,sum(标准金额) 标准金额,sum(产品成本金额) 产品成本金额,日期 ,客服部门 ,主任 ,工程师 from (
		select F_LQKJ_AMOUNT,t2.FMASTERID,isnull(FTAXPRICE,0)*FREALQTY 销售金额,
	  isnull(F_ZTW_PRICE,0)*FREALQTY 标准金额,isnull(F_LQKJ_AMOUNT,0)*FREALQTY 产品成本金额,
	  FREALQTY,convert(varchar(6),YearMonth,112) 日期,DEP.FNAME 客服部门,ZR.FNAME 主任,EMP.FNAME 工程师  from (


	 select   FCUSTOMERID,a.FID,a.FDATE as YearMonth from  
	  T_SAL_OUTSTOCK a left join  T_SAL_OUTSTOCKENTRY b on a.FID=b.FID where F_ZTW_SETTLEMENT in(1)
		and (convert(varchar(6),FDATE,112) like '#FDATE#%' or '#FDATE#'='') and FDOCUMENTSTATUS='C' and FCANCELSTATUS='A' and a.FDOCUMENTSTATUS='C' and a.FCANCELSTATUS='A'
		group by a.FCUSTOMERID,a.FID,a.FDATE 
		union all 
		select  FCUSTOMERID,a.FID,F_ZTW_SUBMITDATE as YearMonth from T_SAL_OUTSTOCK a left join 
		 (select F_ZTW_SUBMITDATE,FDOCUMENTSTATUS,FSTABLENAME,FSETACCOUNTTYPE,FSID,FSBILLID from t_AR_receivable a left join t_AR_receivableEntry b on a.FID=b.FID
		left join t_AR_receivableEntry_LK c on b.FENTRYID=c.FENTRYID )c on c.FSBILLID=a.FID
		left join T_SAL_OUTSTOCKENTRY b on c.FSID=b.FENTRYID left join T_BD_MATERIAL e on b.FMATERIALID=e.FMATERIALID
		left join T_SAL_OUTSTOCKENTRY_F d on d.FENTRYID=b.FENTRYID
where F_ZTW_SETTLEMENT in(3) and (c.FDOCUMENTSTATUS='C' or c.FDOCUMENTSTATUS='B') and FSTABLENAME='T_SAL_OUTSTOCKENTRY' and FSETACCOUNTTYPE='2' and d.FISFREE='0' and a.FDOCUMENTSTATUS='C' and a.FCANCELSTATUS='A'and a.FDOCUMENTSTATUS='C' and a.FCANCELSTATUS='A'
and (FNUMBER like 'CZ%' or FNUMBER like 'CF%' or FNUMBER like 'SC%') and (convert(varchar(6),F_ZTW_SUBMITDATE,112) like '#FDATE#%' or '#FDATE#'='') group by a.FCUSTOMERID,a.FID,F_ZTW_SUBMITDATE 

) t1
	  left join (select FMASTERID,FCUSTID from T_BD_CUSTOMER) t2 on t2.FCUSTID=t1.FCUSTOMERID
	  left join (select FMASTERID,max(FSELLER) FSELLER from T_BD_CUSTOMER group by FMASTERID) t3 on t3.FMASTERID=t2.FMASTERID
	  left join T_SAL_OUTSTOCKENTRY t4 on t4.FID=t1.FID
	  left join T_SAL_OUTSTOCKENTRY_F t5 on t4.FENTRYID=t5.FENTRYID
	  left join T_BD_MATERIAL t6 on t4.FMATERIALID=t6.FMATERIALID--标准单价
	  left join (
		 select Planjg.*,wl.FMASTERID 
		 from T_LQKJ_MaterialPrice Planjg 
		 left join T_BD_MATERIAL wl on wl.FMATERIALID=Planjg.F_LQKJ_MATERIAL
		 ) T7 
		ON T7.F_LQKJ_DATE=convert(varchar,YEAR(T1.YearMonth))+'-'+ convert(varchar,MONTH(T1.YearMonth))
		 AND t6.FMASTERID=t7.FMASTERID  --计划单价
		left join (select FMASTERID,FDEPT, FENG,FSALEHELP,FITEMTYPE from  ztw_StdCostfzEntry a left join T_BD_CUSTOMER b on a.FCUSID=b.FCUSTID 
		group by FMASTERID,FDEPT, FENG,FSALEHELP,FITEMTYPE) Z  on Z.FMASTERID=t2.FMASTERID and t4.FITEM=Z.FITEMTYPE
		left join T_BD_DEPARTMENT_L DEP on DEP.FDEPTID=Z.FDEPT --客服部门
		left join T_HR_EMPINFO_L EMP on EMP.FID=Z.FENG	--工程师
		left join T_HR_EMPINFO_L ZR on ZR.FID=Z.FSALEHELP --主任
	  --(convert(varchar(6),FDATE,112) ='#FDATE#' or '#FDATE#'='')  and 
	 
	)t1  group by FMASTERID,日期,客服部门 ,主任 ,工程师
	) t2 on t2.FMASTERID=t1.FMASTERID 
	left join (select a.fid,b.FNAME SalName,c.FNAME DepName from V_BD_SALESMAN a left join V_BD_SALESMAN_L b on a.fid=b.fid left join T_BD_DEPARTMENT_L c 
on a.FDEPTID=c.FDEPTID) t4 on t4.fid=t1.FSELLER

left join (select FMASTERID,FNAME from T_BD_CUSTOMER a left join T_BD_CUSTOMER_L b on a.FCUSTID=b.FCUSTID group by FMASTERID,FNAME)t5 on t5.FMASTERID=t1.FMASTERID
where 日期 is not null