select tab.FDATE 日期,t4.BMName 销售部门,t4.SalName as 销售员,KF 客服部门,ZR 主任,GCS 工程师,t4.FNAME as 客户,tab.FBILLNO as 出库单编号,'计价' as 销售类型,
t3.FNUMBER as 物料编码,t3.FNAME as 物料名称 ,t6.FDATAVALUE as 产品系列,t5.FNAME as 单位,
nullif(FREALQTY,0) as 实发数量,
nullif(tab.F_ZTW_PRICE,0) as 标准单价,nullif(FTAXPRICE,0) as 销售单价,nullif(tab.F_LQKJ_AMOUNT,0) as BOM单价,nullif(BZPrice,0) as 标准金额,nullif(XSPrice,0) as 销售金额,nullif(CBPrice,0) as 销售成本,
convert(varchar(20),convert(decimal(18,2),(BZPrice/nullif(XSPrice,0)*100)))+'%' as 标准销售比值,
convert(varchar(20),convert(decimal(18,2),(CBPrice/nullif(XSPrice,0)*100)))+'%'as 成本销售比值
from (

select FCUSTOMERID,FBILLNO,FMATERIALID,FSALUNITID,FITEM,FDATE,
max(F_ZTW_PRICE) F_ZTW_PRICE,max(FTAXPRICE) FTAXPRICE,max(F_LQKJ_AMOUNT) F_LQKJ_AMOUNT,
sum(FREALQTY) FREALQTY,sum(BZPrice) BZPrice,sum(XSPrice) XSPrice,sum(CBPrice) CBPrice,KF,GCS,ZR from(

select convert(varchar(6),F_ZTW_SUBMITDATE,112) FDATE,t1.FCUSTOMERID,t1.FBILLNO,t2.FMATERIALID,t2F.FSALUNITID,T2.FITEM,F_ZTW_PRICE,FTAXPRICE,F_LQKJ_AMOUNT,FREALQTY,
(F_ZTW_PRICE*FREALQTY) BZPrice,(FTAXPRICE*FREALQTY) XSPrice,(F_LQKJ_AMOUNT*FREALQTY) CBPrice,DEP.FNAME KF,EMP.FNAME GCS,ZR.FNAME ZR
from T_SAL_OUTSTOCK t1
left join (select F_ZTW_SUBMITDATE,FDOCUMENTSTATUS,FSTABLENAME,FSETACCOUNTTYPE,FSBILLID,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.FSBILLID=t1.FID
left join T_SAL_OUTSTOCKENTRY  t2 on LK.FSID=t2.FENTRYID
left join T_SAL_OUTSTOCKENTRY_F t2F on t2.FENTRYID=t2F.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%') t3 on t3.FMATERIALID=t2.FMATERIALID
left join (select FMASTERID,F_LQKJ_DATE,F_LQKJ_AMOUNT from  T_LQKJ_MaterialPrice a left join T_BD_MATERIAL b on a.F_LQKJ_MATERIAL=b.FMATERIALID where FNUMBER like 'CZ%' or FNUMBER like 'CF%' or FNUMBER like 'SC%') T7 
				ON T7.F_LQKJ_DATE=convert(varchar,YEAR(F_ZTW_SUBMITDATE))+'-'+ convert(varchar,MONTH(F_ZTW_SUBMITDATE))
				 AND t3.FMASTERID=t7.FMASTERID--计划单价
left join T_BD_CUSTOMER CR on CR.FCUSTID=t1.FCUSTOMERID
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=CR.FMASTERID  and t2.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 --主任
where t2.FENTRYID is not null 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 t2f.FISFREE='0'
and  (convert(varchar(6),F_ZTW_SUBMITDATE,112) like '#FDATE#%' or '#FDATE#'='')

) T1 group by FCUSTOMERID,FBILLNO,FMATERIALID,FSALUNITID,FITEM,KF,GCS,ZR,t1.FDATE
)tab
left join (select t1.FMATERIALID,t2.FNAME,F_ZTW_PRICE,FNUMBER from T_BD_MATERIAL t1 
   left join T_BD_MATERIAL_L t2 on t1.FMATERIALID=t2.FMATERIALID where t2.FLOCALEID=2052) t3 on t3.FMATERIALID=tab.FMATERIALID
left join (SELECT t1.FCUSTID,t2.FNAME,t3.FNAME BMName,b.FNAME SalName,FNUMBER from T_BD_CUSTOMER  t1  left join  T_BD_CUSTOMER_L t2 on t1.FCUSTID=t2.FCUSTID  and t2.FLOCALEID=2052
   left join  V_BD_SALESMAN_L b on t1.FSELLER=b.fid and b.FLOCALEID=2052 left join T_BD_DEPARTMENT_L t3 on t3.FDEPTID=t1.FSALDEPTID  and t3.FLOCALEID=2052
        )t4 on t4.FCUSTID=tab.FCUSTOMERID
left join (select * from T_BD_UNIT_L where FLOCALEID=2052) t5 on t5.FUNITID=tab.FSALUNITID
LEFT join (select a.FENTRYID,FNUMBER,FLOCALEID,FDATAVALUE from T_BAS_ASSISTANTDATAENTRY a left join T_BAS_ASSISTANTDATAENTRY_L b on a.FENTRYID=b.FENTRYID where FLOCALEID=2052)t6 ON T6.FENTRYID=tab.FITEM 
where t4.FNAME!='' and (t4.FNUMBER = '#CUSTOMER#' or '#CUSTOMER#'='') and t6.FDATAVALUE!=''and (T6.FNUMBER = '#FITEM#'  or '#FITEM#' ='')
order by t4.BMName,SalName,KF,GCS,ZR,t4.FNAME ,t3.FNUMBER,t6.FDATAVALUE

