SELECT a.hdrid 项目ID, a.dtlid 子项目ID, a.protype 项目分类, a.proname 项目名称, a.subproname 子项目名称, a.proclasee 项目类别编码, g.by3 项目类别, a.dutyempcode 负责人编码, a.dutyempname 负责人名称, a.ownerid 货主ID, e.deptname 货主, a.saledeptid 销售部门ID, f.deptname 销售部门, c.cstcode 客户编码, c.dname 客户名称, h.by2 客户业态, a.gkunifygoods 品种国控统一码, d.goods 品种编码, d.name 品名, d.spec 规格, a.last_qy_billqty, a.last_qy_SUMVALUE, a.last_qy_NOTAXMONEY, a.last_qy_CWCB, a.Y_BILLQTY, a.Y_SUMVALUE_SAL, a.Y_NOTAXMONEY_SAL, a.Y_AVG_CW_SAL, a.M_BILLQTY, a. M_SUMVALUE_SAL, a.LAST_Y_BILLQTY, a.LAST_Y_SUMVALUE_SAL, a.LAST_Y_NOTAXMONEY_SAL, a.LAST_Y_AVG_CW_SAL, a.LAST_M_BILLQTY, a.LAST_M_SUMVALUE_SAL FROM ( SELECT a.hdrid, a.dtlid, a.protype, a.proname, a.subproname, a.proclasee, a.dutyempcode, a.dutyempname, a.ownerid, a.saledeptid, a.cstid, a.gkunifygoods, a.goodid, sum(last_qy_billqty) last_qy_billqty, SUM(last_qy_SUMVALUE) last_qy_SUMVALUE, SUM(last_qy_NOTAXMONEY) last_qy_NOTAXMONEY, SUM(last_qy_CWCB) last_qy_CWCB, SUM(Y_BILLQTY) Y_BILLQTY, SUM(Y_SUMVALUE_SAL) Y_SUMVALUE_SAL, SUM(Y_NOTAXMONEY_SAL) Y_NOTAXMONEY_SAL, SUM(Y_AVG_CW_SAL) Y_AVG_CW_SAL, SUM(M_BILLQTY) M_BILLQTY, SUM(M_SUMVALUE_SAL) M_SUMVALUE_SAL, SUM(LAST_Y_BILLQTY) LAST_Y_BILLQTY, SUM(LAST_Y_SUMVALUE_SAL) LAST_Y_SUMVALUE_SAL, SUM(LAST_Y_NOTAXMONEY_SAL) LAST_Y_NOTAXMONEY_SAL, SUM(LAST_Y_AVG_CW_SAL) LAST_Y_AVG_CW_SAL, SUM(LAST_M_BILLQTY) LAST_M_BILLQTY, SUM(LAST_M_SUMVALUE_SAL) LAST_M_SUMVALUE_SAL FROM ( SELECT aa.hdrid, aa.dtlid, aa.protype, aa.proname, aa.subproname, aa.proclasee, aa.dutyempcode, aa.dutyempname, a.ownerid, a.saledeptid, a.cstid, b.gkunifygoods, a.goodid, 0 last_qy_billqty, 0 last_qy_SUMVALUE, 0 last_qy_NOTAXMONEY, 0 last_qy_CWCB, a.Y_BILLQTY Y_BILLQTY, A.Y_SUMVALUE_SAL Y_SUMVALUE_SAL, A.Y_NOTAXMONEY_SAL Y_NOTAXMONEY_SAL, A.Y_AVG_CW_SAL Y_AVG_CW_SAL, A.M_BILLQTY M_BILLQTY, A.M_SUMVALUE_SAL M_SUMVALUE_SAL, a.LAST_Y_BILLQTY LAST_Y_BILLQTY, a.LAST_Y_SUMVALUE_SAL LAST_Y_SUMVALUE_SAL, A.LAST_Y_NOTAXMONEY_SAL LAST_Y_NOTAXMONEY_SAL, A.LAST_Y_AVG_CW_SAL LAST_Y_AVG_CW_SAL, A.LAST_M_BILLQTY LAST_M_BILLQTY, A.LAST_M_SUMVALUE_SAL LAST_M_SUMVALUE_SAL FROM ( SELECT b.hdrid, b.dtlid, a.protype, a.proname, b.SUBPRONAME, a.proclasee, a.dutyempcode, a.dutyempname FROM fr_project_hdr a, (SELECT * FROM fr_project_dtl WHERE stopflage='00') b WHERE a.stopflage='00' AND a.id=b.hdrid AND a.if_subpro='10' ${if(len(项目分类) == 0,"","and a.protype in ('" + 项目分类 + "')")} ${if(len(项目类别) == 0,"","and a.proclasee in ('" + 项目类别 + "')")} ${if(len(负责人) == 0,"","and a.dutyempcode in ('" + 负责人 + "')")} ${if(len(项目名称) == 0,"","and a.id in (" + 项目名称 + ")")} ${if(len(子项目名称) == 0,"","and b.dtlid in (" + 子项目名称 + ")")} UNION ALL SELECT a.id, 0 dtlid, a.protype, a.proname, NULL SUBPRONAME, a.proclasee, a.dutyempcode, a.dutyempname FROM fr_project_hdr a WHERE a.stopflage='00' AND a.if_subpro='00' ${if(len(项目分类) == 0,"","and a.protype in ('" + 项目分类 + "')")} ${if(len(项目类别) == 0,"","and a.proclasee in ('" + 项目类别 + "')")} ${if(len(负责人) == 0,"","and a.dutyempcode in ('" + 负责人 + "')")} ${if(len(项目名称) == 0,"","and a.id in (" + 项目名称 + ")")} ${if(len(子项目名称) == 0,"","and b.dtlid in (" + 子项目名称 + ")")} ) aa, fr_d_salewdrvalues a, ( SELECT hdrid, dtlid, GKUNIFYGOODS, goodid, AGPRICE FROM fr_project_goods a, cmsxty.spm_waredict_ext@cx_cms b WHERE a.stopflage='00' AND a.gkunifygoods=b.sinopharm_id ${if(len(项目分类) == 0,"","and a.protype in ('" + 项目分类 + "')")} ${if(len(项目类别) == 0,"","and a.proclasee in ('" + 项目类别 + "')")} ${if(len(负责人) == 0,"","and a.dutyempcode in ('" + 负责人 + "')")} ${if(len(项目名称) == 0,"","and a.hdrid in (" + 项目名称 + ")")} ${if(len(子项目名称) == 0,"","and b.dtlid in (" + 子项目名称 + ")")} ) b--品种 ${IF(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,"", "AND a.dtlid='"+子项目名称+"'"),1,1)>=1, ",(SELECT a.hdrid, a.dtlid, a.GKUNIFYCST, b.cstid FROM fr_project_cst a, (SELECT ID CSTID,SINOPHARM_ID FROM CMSXTY.SPM_PRODUCER_EXT@CX_CMS) b WHERE a.CSTBUTE=2 AND a.hdrid='" + 项目名称 + "' AND a.dtlid=nvl('" + 子项目名称 + "',0) AND a.GKUNIFYCST=b.SINOPHARM_ID AND a.stopflage='00' ) C" ,"") }--白名单客户 ${IF(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1, ",(SELECT a.hdrid, a.dtlid, a.GKUNIFYCST, b.cstid FROM fr_project_cst a, (SELECT by1,by5 FROM fr_bmb WHERE km='客户业态分类') (SELECT ID CSTID,SINOPHARM_ID FROM CMSXTY.SPM_PRODUCER_EXT@CX_CMS) b WHERE a.CSTBUTE=4 AND a.hdrid='" + 项目名称 + "' AND a.dtlid=nvl('" + 子项目名称 + "',0) AND a.GKUNIFYCST=b.SINOPHARM_ID AND a.stopflage='00' ) D" ,"") }--白名单客户类型 WHERE a.compid=1 AND a.month_id='202310' AND a.day_id='31' AND aa.hdrid=b.hdrid AND aa.dtlid=b.dtlid AND a.goodid=b.goodid ${IF(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 && sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)<1 , "and a.cstid=c.cstid", if(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 && sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 , "and (a.cstid=c.cstid or a.cstid=d.cstid)", if(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)<1 && sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 , "and a.cstid=d.cstid", "" ) ) )}UNION ALL SELECT aa.hdrid, aa.dtlid, aa.protype, aa.proname, aa.subproname, aa.proclasee, aa.dutyempcode, aa.dutyempname, a.ownerid, a.saledeptid, a.cstid, b.gkunifygoods, a.goodid, 0 last_qy_billqty, 0 last_qy_SUMVALUE, 0 last_qy_NOTAXMONEY, 0 last_qy_CWCB, a.Y_BILLQTY Y_BILLQTY, A.Y_SUMVALUE_SAL Y_SUMVALUE_SAL, A.Y_NOTAXMONEY_SAL Y_NOTAXMONEY_SAL, A.Y_AVG_CW_SAL Y_AVG_CW_SAL, A.M_BILLQTY M_BILLQTY, A.M_SUMVALUE_SAL M_SUMVALUE_SAL, a.LAST_Y_BILLQTY LAST_Y_BILLQTY, a.LAST_Y_SUMVALUE_SAL LAST_Y_SUMVALUE_SAL, A.LAST_Y_NOTAXMONEY_SAL LAST_Y_NOTAXMONEY_SAL, A.LAST_Y_AVG_CW_SAL LAST_Y_AVG_CW_SAL, A.LAST_M_BILLQTY LAST_M_BILLQTY, A.LAST_M_SUMVALUE_SAL LAST_M_SUMVALUE_SAL FROM ( SELECT b.hdrid, b.dtlid, a.protype, a.proname, b.SUBPRONAME, a.proclasee, a.dutyempcode, a.dutyempname FROM fr_project_hdr a, (SELECT * FROM fr_project_dtl WHERE stopflage='00') b WHERE a.stopflage='00' AND a.id=b.hdrid AND a.if_subpro='10' ${if(len(项目分类) == 0,"","and a.protype in ('" + 项目分类 + "')")} ${if(len(项目类别) == 0,"","and a.proclasee in ('" + 项目类别 + "')")} ${if(len(负责人) == 0,"","and a.dutyempcode in ('" + 负责人 + "')")} ${if(len(项目名称) == 0,"","and a.id in (" + 项目名称 + ")")} ${if(len(子项目名称) == 0,"","and b.dtlid in (" + 子项目名称 + ")")} UNION ALL SELECT a.id, 0 dtlid, a.protype, a.proname, NULL SUBPRONAME, a.proclasee, a.dutyempcode, a.dutyempname FROM fr_project_hdr a WHERE a.stopflage='00' AND a.if_subpro='00' ${if(len(项目分类) == 0,"","and a.protype in ('" + 项目分类 + "')")} ${if(len(项目类别) == 0,"","and a.proclasee in ('" + 项目类别 + "')")} ${if(len(负责人) == 0,"","and a.dutyempcode in ('" + 负责人 + "')")} ${if(len(项目名称) == 0,"","and a.id in (" + 项目名称 + ")")} ${if(len(子项目名称) == 0,"","and b.dtlid in (" + 子项目名称 + ")")} ) aa, fr_d_salewdrvalues a, ( SELECT hdrid, dtlid, GKUNIFYGOODS, goodid, AGPRICE FROM fr_project_goods a, cmsxty.spm_waredict_ext@cx_cms b WHERE a.stopflage='00' AND a.gkunifygoods=b.sinopharm_id ${if(len(项目分类) == 0,"","and a.protype in ('" + 项目分类 + "')")} ${if(len(项目类别) == 0,"","and a.proclasee in ('" + 项目类别 + "')")} ${if(len(负责人) == 0,"","and a.dutyempcode in ('" + 负责人 + "')")} ${if(len(项目名称) == 0,"","and a.hdrid in (" + 项目名称 + ")")} ${if(len(子项目名称) == 0,"","and b.dtlid in (" + 子项目名称 + ")")} ) b--品种 ${IF(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,"", "AND a.dtlid='"+子项目名称+"'"),1,1)>=1, ",(SELECT a.hdrid, a.dtlid, a.GKUNIFYCST, b.cstid FROM fr_project_cst a, (SELECT ID CSTID,SINOPHARM_ID FROM CMSXTY.SPM_PRODUCER_EXT@CX_CMS) b WHERE a.CSTBUTE=2 AND a.hdrid='" + 项目名称 + "' AND a.dtlid=nvl('" + 子项目名称 + "',0) AND a.GKUNIFYCST=b.SINOPHARM_ID AND a.stopflage='00' ) C" ,"") }--白名单客户 ${IF(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1, ",(SELECT a.hdrid, a.dtlid, a.GKUNIFYCST, b.cstid FROM fr_project_cst a, (SELECT by1,by5 FROM fr_bmb WHERE km='客户业态分类') (SELECT ID CSTID,SINOPHARM_ID FROM CMSXTY.SPM_PRODUCER_EXT@CX_CMS) b WHERE a.CSTBUTE=4 AND a.hdrid='" + 项目名称 + "' AND a.dtlid=nvl('" + 子项目名称 + "',0) AND a.GKUNIFYCST=b.SINOPHARM_ID AND a.stopflage='00' ) D" ,"") }--白名单客户类型 WHERE a.compid=1 AND a.month_id='202212' AND a.day_id='31' AND aa.hdrid=b.hdrid AND aa.dtlid=b.dtlid AND a.goodid=b.goodid ${IF(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 && sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)<1 , "and a.cstid=c.cstid", if(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 && sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 , "and (a.cstid=c.cstid or a.cstid=d.cstid)", if(sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=2 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)<1 && sql("ztsc","SELECT count(distinct CSTBUTE) from fr_project_cstattribute where 1=1 AND CSTBUTE=4 and stopflage='00'" +IF(LEN(项目名称)==0,""," AND hdrid='"+项目名称+"'")+IF(LEN(子项目名称)=0,""," AND a.dtlid='"+子项目名称+"'"),1,1)>=1 , "and a.cstid=d.cstid", "" ) ) )}--白名单客户关联条件 ) aGROUP BY a.hdrid, a.dtlid, a.protype, a.proname, a.subproname, a.proclasee, a.dutyempcode, a.dutyempname, a.ownerid, a.saledeptid, a.cstid, a.gkunifygoods, a.goodid) a,cmsxty.pub_clients@cx_cms c,cmsxty.pub_waredict@cx_cms d,cmsxty.pub_dept@cx_cms e,cmsxty.pub_dept@cx_cms f,(SELECT by2,by3 FROM fr_bmb WHERE km='项目类别') g,(SELECT by1,by2,by3 FROM FR_BMB WHERE KM ='客户业态分类') h WHERE a.cstid=c.cstid AND a.goodid=d.goodid AND a.ownerid=e.deptid AND a.saledeptid=f.deptid AND a.proclasee=g.by2(+) AND c.type=h.by1(+)
