select DepName 销售部门,SalName 销售员,t5.FNAME 客户,t3.标准金额 '计价&标准金额',t3.成本金额 '计价&销售成本',t3.销售金额 '计价&销售金额'
,convert(varchar(20),convert(decimal(18,2),(t3.标准金额/nullif(t3.销售金额,0)*100)))+'%' '计价&标准销售比值',
convert(varchar(20),convert(decimal(18,2),(t3.成本金额/nullif(t3.销售金额,0)*100)))+'%' '计价&BOM销售比值',
t2.标准金额 '承包&标准金额',t2.成本金额 '承包&销售成本',t2.销售金额 '承包&销售金额'
,convert(varchar(20),convert(decimal(18,2),(t2.标准金额/nullif(t2.销售金额,0)*100)))+'%' '承包&标准销售比值',
convert(varchar(20),convert(decimal(18,2),(t2.成本金额/nullif(t2.销售金额,0)*100)))+'%' '承包&BOM销售比值',
isnull(t3.标准金额,0)+isnull(t2.标准金额,0) '合计&标准金额',isnull(t3.成本金额,0)+isnull(t2.成本金额,0) '合计&销售成本',isnull(t3.销售金额,0)+isnull(t2.销售金额,0) '合计&销售金额',
convert(varchar(20),convert(decimal(18,2),((isnull(t3.标准金额,0)+isnull(t2.标准金额,0))/nullif(isnull(t3.销售金额,0)+isnull(t2.销售金额,0),0)*100)))+'%' '合计&标准销售比值',
convert(varchar(20),convert(decimal(18,2),((isnull(t3.成本金额,0)+isnull(t2.成本金额,0))/nullif(isnull(t3.销售金额,0)+isnull(t2.销售金额,0),0)*100)))+'%' '合计&BOM销售比值'
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) like '#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 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
	  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.FDATE))+'-'+ convert(varchar,MONTH(T1.FDATE))
		 AND t6.FMASTERID=t7.FMASTERID  --计划单价
	 where (convert(varchar(6),FDATE,112) like '#FDATE#%' or '#FDATE#'='') and 
	 F_ZTW_SETTLEMENT =1 and t1.FDOCUMENTSTATUS='C' and t1.FCANCELSTATUS='A'
	)t1  group by FMASTERID
	) t2 on t2.FMASTERID=t1.FMASTERID
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 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
	  left join T_SAL_OUTSTOCKENTRY t4 on t4.FID=t1.FID
	  left join (select F_ZTW_SUBMITDATE,FDOCUMENTSTATUS,FSTABLENAME,FSETACCOUNTTYPE,FSID 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  )LK on LK.FSID=t4.FENTRYID
	  left join T_SAL_OUTSTOCKENTRY_F t5 on t4.FENTRYID=t5.FENTRYID
	  left join (select FMATERIALID,FMASTERID,F_ZTW_PRICE,FNUMBER from  T_BD_MATERIAL where FNUMBER like 'CZ%' or FNUMBER like 'CF%' or FNUMBER like 'SC%') 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(F_ZTW_SUBMITDATE))+'-'+ convert(varchar,MONTH(F_ZTW_SUBMITDATE))
		 AND t6.FMASTERID=t7.FMASTERID  --计划单价
	 where (convert(varchar(6),F_ZTW_SUBMITDATE,112) like '#FDATE#%' or '#FDATE#'='') and
	 F_ZTW_SETTLEMENT =3 and t1.FDOCUMENTSTATUS='C' and t1.FCANCELSTATUS='A' and ( LK.FDOCUMENTSTATUS='C' or LK.FDOCUMENTSTATUS='B') and LK.FSTABLENAME='T_SAL_OUTSTOCKENTRY'
and F_ZTW_SUBMITDATE is not null and FSETACCOUNTTYPE='2' and t5.FISFREE='0'
	)t1  group by FMASTERID
) t3 on  t3.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 t3.销售金额 is not null or t2.销售金额 is not null
order by DepName ,SalName ,t5.FNAME 


