电量电费构成分析

<!--电量电费构成数据--><actionname="jyfxDldfGcData"class="inmanage-ConsDftzInfoAction"method="findJyfxDldfGcData"/>publicStringfindJyfxDldfGcData()throwsIOException{MapparamMap=RequestUtils.parameterConvert(params);paramMap.put("ym",getPreYm(paramMap));super.write(response,Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsRcvblPieStr(paramMap));returnNONE;}*根据传入的参数，查询获取用户的电量电费构成(转换过后供fusionchart(饼状图)使用的xml格式数据)@paramparamscons_no:户号ym::电费年月@returnStringXML*/StringqueryConsRcvblPieStr(Mapparams);publicStringqueryConsRcvblPieStr(Mapparams){//TODOAuto-generatedmethodstubStringshow_type=(StringUtil.isNullOrEmpty(params.get("show")))?"sdl":(String)params.get("show");Stringdata_xml="";if("sdl".equals(show_type)){data_xml=this.queryConsRcvblSdlPieStr(params);}elseif("sdsr".equals(show_type)){data_xml=this.queryConsRcvblAmtPieStr(params);publicStringqueryConsRcvblAmtPieStr(Mapparams){//TODOAuto-generatedmethodstubStringBufferdataStr=newStringBuffer(WsConnectUtil.XML_TITLE);Stringym=((String)params.get("ym")).substring(0,4)+"年"+((String)params.get("ym")).substring(4,6)+"月"；Stringsub_title=(String)params.get("show_name");ListdataLst=this.queryConsRcvbl(params);}returndata_xml;}/**publicStringqueryConsRcvblSdlPieStr(Mapparams){//TODOAuto-generatedmethodstubStringBufferdataStr=newStringBuffer(WsConnectUtil.XML_TITLE);Stringym=((String)params.get("ym")).substring(0,4)+"年"+((String)params.get("ym")).substring(4,6)+"月"；Stringsub_title=(String)params.get("show_name");ListdataLst=this.queryConsRcvbl(params);publicListqueryConsRcvbl(Mapparams)(//TODOAuto-generatedmethodstubStringgroup=("1".equals(params.get("group")))?"PRC_CODE":"ELEC_TYPE_CODE”;StringBuffersql=newStringBuffer();sql.append("WITHTMPAS(");sql.append("SELECT(casewhen(,ELEC_TYPE_CODE,=,'+group+"*and"+group+"ISNULL)THEN");sql.append("(SELECTELEC_TYPE_CODEFROMEPMA.E_CAT_PRCWHEREPRC_CODE=AMT.PRC_CODE");sql.append("AWA_VN=(SELECTMAX(PARA_VN)FROMEPMA.E_BILL_PARA_VERWHEREPAR_VER_TYPE='1'ANDRELEASE_FLAG='1')ANDROWNUM=1)");sql.append("ELSE"+group+"END)"+group+",");sql.append("T_SETTLE_PQSDL,T_AMTSDSR,0SDL_TQ,0.00SDSR_TQ");sql.append("FROMEPMA.A_RCVBL_FLOWRCVBL,EPMA.ARC_E_CONS_PRC_AMTAMT");sql.append("WHERERCVBL.CALC_ID=AMT.CALC_IDANDRCVBL.CONS_NO=?");sql.append("ANDEXISTS(SELECT1FROMEPMA.E_CALC_INVWHERERCVBL_AMT_ID=RCVBL.RCVBL_AMT_IDANDNY=?ANDZHH='"+params.get("cons_no")+"')");sql.append("ANDAMT.PRC_CODE<>'0'ANDTO_NUMBER(PRC_CODE)<10000");sql.append("UNIONALL");sql.append("SELECT(casewhen(,ELEC_TYPE_CODE,=,'+group+"*and"+group+"ISNULL)THEN");sql.append("(SELECTELEC_TYPE_CODEFROMEPMA.E_CAT_PRCWHEREPRC_CODE=AMT.PRC_CODE");sql.append("iEIARA_VN=(SELECTMAX(PARA_VN)FROMEPMA.E_BILL_PARA_VERWHEREPAR_VER_TYPE='1'ANDRELEASE_FLAG='1')ANDROWNUM=1)");sql.append("ELSE"+group+"END)"+group+",");sql.append("0SDL,0.00SDSR,T_SETTLE_PQSDL_TQ,T_AMTSDSR_TQ");sql.append("FROMEPMA.A_RCVBL_FLOWRCVBL,EPMA.ARC_E_CONS_PRC_AMTAMT");sql.append("WHERERCVBL.CALC_ID=AMT.CALC_IDANDRCVBL.CONS_NO=?");sql.append("ANDEXISTS(SELECT1FROMEPMA.E_CALC_INVWHERERCVBL_AMT_ID=RCVBL.RCVBL_AMT_IDANDNY=?-'000100'ANDZHH='"+params.get("cons_no")+"')");sql.append("ANDAMT.PRC_CODE<>'0'ANDTO_NUMBER(PRC_CODE)<10000");sql.append(")");sql.append("SELECT(casewhen'PRC_CODE'='"+group+"'THEN(SELECTCAT_PRC_NAMEFROMEPMA.E_CAT_PRCWHEREPRC_CODE=TMP."+group+"ANDROWNUM=1)");sql.append("when'ELEC_TYPE_CODE'='"+group+"'THEN(SELECTNAMEFROMEPMA.P_CODEWHERECODE_SORT_ID='29001'ANDVALUE="+group+")ELSE''END)CATEGORY,");sql.append("ROUND(SUM(SDL)/10000,2)SDL,ROUND(SUM(SDSR)/10000,2)SDSR,ROUND(SUM(SDL_TQ)/10000,2)SDL_TQ,ROUND(SUM(SDSR_TQ)/10000,2)SDSR_TQFROMTMP");sql.append("GROUPBY"+group+"");returnsuper.jdbcTemplateEx.queryForList(sql.toString(),newObject[]{params.get("cons_no"),params.get("ym"),params.get("cons_no"),params.get("ym")},newint口{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR});}booleanexists_data=this.checkExistsPie(dataLst,"sdl");dataStr.append("<graphformatNumberScale='0'animation='0'baseFontSize='12'AlternateHGridColor=\"56B9F9\"");dataStr.append("decimalPrecision=\"2\"pieFillAlpha='80'pieSliceDepth='30'bgColor='DDE3D5'font=\"Arial\"");if(!exists_data){dataStr.append("showValues='0'");}dataStr.append("showValues='1'showPercentageInLabel='1'caption='"+ym+"电量构成分析(单位：万千瓦时)'subcaption='"+sub_title+"'>");if(exists_data){for(inti=0;i<dataLst.size();i++){MapdataMap=(Map)dataLst.get(i);dataStr.append("<setname='【"+dataMap.get("category")+"】'value='").append(dataMap.get("sdl")).append("'isSliced='0'/>");}}else(dataStr.append("<setname='本月无电量数据！'value='1'isSliced='0'/>");}dataStr.append("</graph>");returndataStr.toString();}//booleanexists_data=this.checkExistsPie(dataLst,"sdsr");dataStr.append("<graphformatNumberScale='0'animation='0'baseFontSize='12'AlternateHGridColor=\"56B9F9\"");dataStr.append("decimalPrecision=\"2\"pieFillAlpha='80'pieSliceDepth='30'bgColor='DDE3D5'font=\"Arial\"");if(!exists_data)(dataStr.append("showValues='0'");}dataStr.append("showValues='1'showPercentageInLabel='1'caption='"+ym+"电费构成分析(单位：万元)'subcaption='"+sub_title+"'>");if(exists_data)(for(inti=0;i<dataLst.size();i++)(MapdataMap=(Map)dataLst.get(i);dataStr.append("<setname='【"+dataMap.get("category")+"】'value='").append(dataMap.get("sdsr")).append("'isSliced='0'/>");}}else(dataStr.append("<setname='本月无电费数据！'value='1'isSliced='0'/>");}dataStr.append("</graph>");returndataStr.toString();}<!--2.1.4.电量电费构成峰谷比重数据(图)--><actionname="jyfxDldfGcBzData"class="inmanage-ConsDftzInfoAction"method="findJyfxDldfGcBzData"/>/***交易分析-电量电费构成比重@returnNONE@throwsIOExceptionERR*/publicStringfindJyfxDldfGcBzData()throwsIOException{MapparamMap=RequestUtils.parameterConvert(params);paramMap.put("ym",getPreYm(paramMap));super.write(response,Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsRcvblPie_Bz(paramMap));returnNONE;}publicStringqueryConsRcvblPie_Bz(Mapparams)(//TODOAuto-generatedmethodstubStringBufferdataStr=newStringBuffer(WsConnectUtil.XML_TITLE);Stringcategory=("sdl".equalsIgnoreCase((String)params.get("show")))?"电量":"电费"-;Stringym=((String)params.get("ym")).substring(0,4)+"年"+((String)params.get("ym")).substring(4,6)+"月"；MapconsMap=consDftzInfoDAO.queryConsInfo(params);publicMapqueryConsInfo(Mapparams)(//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTCONS_NAME,(SELECTNAMEFROM”).append(Contants.DB_SCHEMA).append("P_CODEWHERECODE_SORT_ID='29005'ANDVALUE=TRADE_CODE)TRADE_NAME");sql.append("FROM").append(Contants.DB_SCHEMA).append("C_CONSWHERECONS_NO=?");returnsuper.jdbcTemplateEx.queryForMap(sql.toString(),newObject[]{params.get("cons_no")});}//Stringtrade_name=""+consMap.get("TRADE_NAME");Stringcaption=trade_name;Stringsubcaption="";try{if(!StringUtil.isNullOrEmpty(trade_name)&&trade_name.length()>10){subcaption=trade_name.substring(10);caption=trade_name.substring(0,10);}catch(Exceptionerr){err.printStackTrace();}dataStr.append("<graphformatNumberScale='0'animation='0'baseFontSize='12'AlternateHGridColor=\"97DFF7\"");dataStr.append("decimalPrecision=\"1\"pieFillAlpha='80'pieSliceDepth='30'bgColor='DDE3D5'font=\"Arial\"");dataStr.append("showValues='1'showPercentageInLabel='1'caption='"+ym+caption+"'subcaption='"+subcaption+"(彳亍业)电量构成比重(%)'>");//if(exists_data){//for(inti=0;i<dataLst.size();i++){Mappjbz=this.queryFshzData(params);if(pjbz!=null){//if("sdl".equalsIgnoreCase((String)params.get("show"))){dataStr.append("<setname='峰期'color='DEBD53'value='").append(pjbz.get("FQDLBZ")).append("'isSliced='0'/>");dataStr.append("<setname='平期'color='AFC965'value='").append(pjbz.get("PQDLBZ")).append("'isSliced='0'/>");dataStr.append("<setname='谷期'color='DD9464'value='").append(pjbz.get("GQDLBZ")).append("'isSliced='0'/>");//}else{//dataStr.append("<setname='峰期'value='").append(pjbz.get("FQDFBZ")).append("'isSliced='0'/>");//dataStr.append("<setname='平期'value='").append(pjbz.get("PQDFBZ")).append("'isSliced='0'/>");//dataStr.append("<setname='谷期'value='").append(pjbz.get("GQDFBZ")).append("'isSliced='0'/>");//}}if(pjbz==null){dataStr.append("<setcolor='A66EDD'name='本月无"+category.replaceAll("","")+"数据！'value='1'isSliced='0'/>");}dataStr.append("</graph>");returndataStr.toString();}<!—2.1.4电量电费构成比重数据—><actionname="jyfxDldfGcBz"class="inmanage-ConsDftzInfoActionmethod="findJyfxDldfGcBzStr"/>/***交易分析-电量电费构成比重数据@returnNONE@throwslOExceptionERR*/publicStringfindJyfxDldfGcBzStr()throwsIOException{MapparamMap=RequestUtils.parameterConvert(params);paramMap.put("ym",getPreYm(paramMap));super.write(response,Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsRcvblBzStr(paramMap));returnNONE;}publicStringqueryConsRcvblBzStr(Mapparams){//TODOAuto-generatedmethodstubListdataLst=this.queryConsRcvbl(params);publicListqueryConsRcvbl(Mapparams){//TODOAuto-generatedmethodstubStringgroup=("1".equals(params.get("group")))?"PRC_CODE":"ELEC_TYPE_CODE";StringBuffersql=newStringBuffer();sql.append("WITHTMPAS(");sql.append("SELECT(casewhen('ELEC_TYPE_CODE'=''+group+"'and"+group+"ISNULL)THEN");sql.append("(SELECTELEC_TYPE_CODEFROMEPMA.E_CAT_PRCWHEREPRC_CODE=AMT.PRC_CODE");sql.append("iPIARA_VN=(SELECTMAX(PARA_VN)FROMEPMA.E_BILL_PARA_VERWHEREPAR_VER_TYPE='1'ANDRELEASE_FLAG='1')ANDROWNUM=1)");sql.append("ELSE"+group+"END)"+group+",");sql.append("T_SETTLE_PQSDL,T_AMTSDSR,0SDL_TQ,0.00SDSR_TQ");sql.append("FROMEPMA.A_RCVBL_FLOWRCVBL,EPMA.ARC_E_CONS_PRC_AMTAMT");sql.append("WHERERCVBL.CALC_ID=AMT.CALC_IDANDRCVBL.CONS_NO=?");sql.append("ANDEXISTS(SELECT1FROMEPMA.E_CALC_INVWHERERCVBL_AMT_ID=RCVBL.RCVBL_AMT_IDANDNY=?ANDZHH='"+params.get("cons_no")+"')");sql.append("ANDAMT.PRC_CODE<>'0'ANDTO_NUMBER(PRC_CODE)<10000");sql.append("UNIONALL");sql.append("SELECT(casewhen(,ELEC_TYPE_CODE,=,'+group+"1and"+group+"ISNULL)THEN");sql.append("(SELECTELEC_TYPE_CODEFROMEPMA.E_CAT_PRCWHEREPRC_CODE=AMT.PRC_CODE");sql.append("A^ARA.VN=(SELECTMAX(PARA_VN)FROMEPMA.E_BILL_PARA_VERWHEREPAR_VER_TYPE='1'ANDRELEASE_FLAG='1')ANDROWNUM=1)");sql.append("ELSE"+group+"END)"+group+",");sql.append("0SDL,0.00SDSR,T_SETTLE_PQSDL_TQ,T_AMTSDSR_TQ");sql.append("FROMEPMA.A_RCVBL_FLOWRCVBL,EPMA.ARC_E_CONS_PRC_AMTAMT");sql.append("WHERERCVBL.CALC_ID=AMT.CALC_IDANDRCVBL.CONS_NO=?");sql.append("ANDEXISTS(SELECT1FROMEPMA.E_CALC_INVWHERERCVBL_AMT_ID=RCVBL.RCVBL_AMT_IDANDNY=?-'000100'ANDZHH='"+params.get("cons_no")+"')");sql.append("ANDAMT.PRC_CODE<>'0'ANDTO_NUMBER(PRC_CODE)<10000");sql.append(")");sql.append("SELECT(casewhen'PRC_CODE'='"+group+"'THEN(SELECTCAT_PRC_NAMEFROMEPMA.E_CAT_PRCWHEREPRC_CODE=TMP."+group+"ANDROWNUM=1)");sql.append("when'ELEC_TYPE_CODE'='"+group+"'THEN(SELECTNAMEFROMEPMA.P_CODEWHERECODE_SORT_ID='29001'ANDVALUE="+group+")ELSE''END)CATEGORY,");sql.append("ROUND(SUM(SDL)/10000,2)SDL,ROUND(SUM(SDSR)/10000,2)SDSR,ROUND(SUM(SDL_TQ)/10000,2)SDL_TQ,ROUND(SUM(SDSR_TQ)/10000,2)SDSR_TQFROMTMP");sql.append("GROUPBY"+group+"");returnsuper.jdbcTemplateEx.queryForList(sql.toString(),newObject[]{params.get("cons_no"),params.get("ym"),params.get("cons_no"),params.get("ym")},newint口{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR});}MapgcMap=newHashMap();for(inti=0;i<dataLst.size();i++){Mapdata=(Map)dataLst.get(i);gcMap.put(data.get("category"),data.get("sdl"));}//平均比重Mappjbz=this.queryFshzData(params);publicMapqueryFshzData(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTSDL,TO_CHAR(FQDL)FQDL,TO_CHAR(PQDL)PQDL,TO_CHAR(GQDL)GQDL,FQDF,PQDF,GQDF,");sql.append("(CASEWHENSDL=0THEN0ELSEROUND(FQDL/SDL*100,2)END)FQDLBZ,");sql.append("(CASEWHENSDL=0THEN0ELSEROUND(GQDL/SDL*100,2)END)GQDLBZ,");sql.append("(CASEWHENSDL=0THEN0ELSEROUND(PQDL/SDL*100,2)END)PQDLBZ,");sql.append("(CASEWHENDDDF=0THEN0ELSEROUND(FQDF/DDDF*100,2)END)FQDFBZ,");sql.append("(CASEWHENDDDF=0THEN0ELSEROUND(GQDF/DDDF*100,2)END)GQDFBZ,");sql.append("(CASEWHENDDDF=0THEN0ELSEROUND(PQDF/DDDF*100,2)END)PQDFBZ");sql.append("FROMEPMA.E_SBFSHYHZWHERELENGTH(ORG_NO)=5ANDYM=?ANDHC=560");returnsuper.jdbcTemplateEx.queryForMap(sql.toString(),newObject[]{params.get("ym")},newint[]{Types.VARCHAR});}Listresults=newArrayList();results.add(pjbz);〃最优比重Mapzybz=consDftzInfoDAO.queryZyBzData(params);publicMapqueryZyBzData(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTGZBZ,FZBZFROM”).append(Contants.D吼SCHEMA).append("ARC_FSHYBZWHEREYM=?");returnsuper.jdbcTemplateEx.queryForMap(sql.toString(),newObject[]{params.get("ym")});}〃获取用户的主行业MapconsMap=consDftzInfoDAO.queryConsInfo(params);publicMapqueryConsInfo(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTCONS_NAME,(SELECTNAMEFROM”).append(Contants.DB_SCHEMA).append("P_CODEWHERECODE_SORT_ID='29005'ANDVALUE=TRADE_CODE)TRADE_NAME");sql.append("FROM").append(Contants.DB_SCHEMA).append("C_CONSWHERECONS_NO=?");returnsuper.jdbcTemplateEx.queryForMap(sql.toString(),newObject[]{params.get("cons_no")});}zybz.put("trade_name",consMap.get("TRADE_NAME"));results.add(zybz);//本户比重Mapconsbz=newHashMap();try{Floatcons_fqdl=Float.parseFloat((String)gcMap.get("峰期电量"));Floatcons_gqdl=Float.parseFloat((String)gcMap.get("谷期电量"));Floatcons_zdl=Float.parseFloat((String)gcMap.get("峰期电量"))+Float.parseFloat((String)gcMap.get("平期电量"))+Float.parseFloat((String)gcMap.get("谷期电量"))+Float.parseFloat((String)gcMap.get("非分时电量"));if(cons_zdl.doubleValue()!=0){Floatcons_fz=(cons_fqdl/cons_zdl)*100;Floatcons_gz=(cons_gqdl/cons_zdl)*100;consbz.put("cons_fz",NumberUtil.trunc(cons_fz,2));consbz.put("cons_gz",NumberUtil.trunc(cons_gz,2));}results.add(consbz);}catch(Exceptionerr){}returnJSONArray.fromOb^ect(results).toString();}//publicMapqueryFshzData(Mapparams){Stringxml_param=WsConnectUtil.convertMapForXML(params);Stringdatas=WsConnectUtil.getXmlByClient(Contants.SERVICE_DLDF_URL,xml_param,"queryFshzData");if(datas==null||"null".equalsIgnoreCase(datas)){returnnull;}returnJSONObject.fromObject(datas);}<!--电量电费构成平均电价数据--><actionname="jyfxDldfGcPrc"class="inmanage-ConsDftzInfoAction"method="findJyfxDldfGcPrcStr"/>/***交易分析-电量电费构成比重数据(平均电价)@returnNONE@throwsIOExceptionERR*/publicStringfindJyfxDldfGcPrcStr()throwsIOException{MapparamMap=RequestUtils.parameterConvert(params);paramMap.put("ym",getPreYm(paramMap));super.write(response,Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsAvgPriceStr(paramMap));returnNONE;}publicStringqueryConsAvgPriceStr(Mapparams){//TODOAuto-generatedmethodstubStringxml_param=WsConnectUtil.convertMapForXML(params);Stringdatas=WsConnectUtil.getXmlByClient(Contants.SE