电量电费构成分析.docx

public String findJyfxDldfGcData() throws IOExceptionMap paramMap = RequestUtils.parameterConvert(params);paramMap.put(ym, getPreYm(paramMap);super.write(response, Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsRcvblPieStr(paramMap);return NONE; * 根据传入的参数，查询获取用户的电量电费构成(转换过后供fusionchart(饼状图)使用的xml格式数据) * param params * cons_no:户号 * ym: :电费年月 * return String XML */String queryConsRcvblPieStr(Map params);public String queryConsRcvblPieStr(Map params) / TODO Auto-generated method stubString show_type = (StringUtil.isNullOrEmpty(params.get(show) ? sdl : (String) params.get(show);String data_xml = ;if (sdl.equals(show_type) data_xml = this.queryConsRcvblSdlPieStr(params); else if (sdsr.equals(show_type) data_xml = this.queryConsRcvblAmtPieStr(params);public String queryConsRcvblAmtPieStr(Map params) / TODO Auto-generated method stubStringBuffer dataStr = new StringBuffer(WsConnectUtil.XML_TITLE);String ym = (String)params.get(ym).substring(0,4)+年+(String)params.get(ym).substring(4,6)+月;String sub_title = (String)params.get(show_name);List dataLst = this.queryConsRcvbl(params);return data_xml; /* public String queryConsRcvblSdlPieStr(Map params) / TODO Auto-generated method stubStringBuffer dataStr = new StringBuffer(WsConnectUtil.XML_TITLE);String ym = (String)params.get(ym).substring(0,4)+年+(String)params.get(ym).substring(4,6)+月;String sub_title = (String)params.get(show_name);List dataLst = this.queryConsRcvbl(params);public List queryConsRcvbl(Map params) / TODO Auto-generated method stubString group = (1.equals(params.get(group) ? PRC_CODE : ELEC_TYPE_CODE;StringBuffer sql = new StringBuffer();sql.append(WITH TMP AS ( );sql.append(SELECT (case when (ELEC_TYPE_CODE=+group+ and +group+ IS NULL) THEN );sql.append( (SELECT ELEC_TYPE_CODE FROM EPMA.E_CAT_PRC WHERE PRC_CODE = AMT.PRC_CODE );sql.append( AND PARA_VN = (SELECT MAX(PARA_VN) FROM EPMA.E_BILL_PARA_VER WHERE PAR_VER_TYPE=1 AND RELEASE_FLAG=1) AND ROWNUM=1) );sql.append(ELSE +group+ END ) +group+,);sql.append(T_SETTLE_PQ SDL,T_AMT SDSR ,0 SDL_TQ ,0.00 SDSR_TQ );sql.append(FROM EPMA.A_RCVBL_FLOW RCVBL,EPMA.ARC_E_CONS_PRC_AMT AMT );sql.append(WHERE RCVBL.CALC_ID = AMT.CALC_ID AND RCVBL.CONS_NO=? );sql.append(AND EXISTS(SELECT 1 FROM EPMA.E_CALC_INV WHERE RCVBL_AMT_ID = RCVBL.RCVBL_AMT_ID AND NY=? AND ZHH=+params.get(cons_no)+) );sql.append(AND AMT.PRC_CODE0 AND TO_NUMBER(PRC_CODE)10000 );sql.append(UNION ALL );sql.append(SELECT (case when (ELEC_TYPE_CODE=+group+ and +group+ IS NULL) THEN );sql.append( (SELECT ELEC_TYPE_CODE FROM EPMA.E_CAT_PRC WHERE PRC_CODE = AMT.PRC_CODE );sql.append( AND PARA_VN = (SELECT MAX(PARA_VN) FROM EPMA.E_BILL_PARA_VER WHERE PAR_VER_TYPE=1 AND RELEASE_FLAG=1) AND ROWNUM=1) );sql.append(ELSE +group+ END ) +group+,);sql.append(0 SDL ,0.00 SDSR,T_SETTLE_PQ SDL_TQ,T_AMT SDSR_TQ ); sql.append(FROM EPMA.A_RCVBL_FLOW RCVBL,EPMA.ARC_E_CONS_PRC_AMT AMT );sql.append(WHERE RCVBL.CALC_ID = AMT.CALC_ID AND RCVBL.CONS_NO=? );sql.append(AND EXISTS(SELECT 1 FROM EPMA.E_CALC_INV WHERE RCVBL_AMT_ID = RCVBL.RCVBL_AMT_ID AND NY =? - 000100 AND ZHH=+params.get(cons_no)+) );sql.append(AND AMT.PRC_CODE0 AND TO_NUMBER(PRC_CODE)10000 );sql.append() );sql.append(SELECT (case when PRC_CODE=+group+ THEN (SELECT CAT_PRC_NAME FROM EPMA.E_CAT_PRC WHERE PRC_CODE = TMP.+group+ AND ROWNUM=1) );sql.append( when ELEC_TYPE_CODE=+group+ THEN (SELECT NAME FROM EPMA.P_CODE WHERE CODE_SORT_ID=29001 AND VALUE = +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_TQ FROM TMP );sql.append(GROUP BY +group+);return super.jdbcTemplateEx.queryForList(sql.toString(),new Objectparams.get(cons_no),params.get(ym),params.get(cons_no),params.get(ym),new intTypes.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR); boolean exists_data = this.checkExistsPie(dataLst, sdl);dataStr.append();if(exists_data) for (int i = 0; i dataLst.size(); i+) Map dataMap = (Map) dataLst.get(i);dataStr.append( );else dataStr.append( );dataStr.append();return dataStr.toString(); /boolean exists_data = this.checkExistsPie(dataLst, sdsr);dataStr.append();if(exists_data) for (int i = 0; i dataLst.size(); i+) Map dataMap = (Map) dataLst.get(i);dataStr.append( );else dataStr.append( );dataStr.append();return dataStr.toString(); /* * 交易分析-2.1.4.2电量电费构成比重 * return NONE * throws IOException ERR */public String findJyfxDldfGcBzData() throws IOExceptionMap paramMap = RequestUtils.parameterConvert(params);paramMap.put(ym, getPreYm(paramMap);super.write(response, Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsRcvblPie_Bz(paramMap);return NONE; public String queryConsRcvblPie_Bz (Map params) / TODO Auto-generated method stubStringBuffer dataStr = new StringBuffer(WsConnectUtil.XML_TITLE);String category = (sdl.equalsIgnoreCase(String)params.get(show) ? 电 量 : 电 费;String ym = (String)params.get(ym).substring(0,4)+年+(String)params.get(ym).substring(4,6)+月;Map consMap = consDftzInfoDAO.queryConsInfo(params);public Map queryConsInfo(Map params) / TODO Auto-generated method stubStringBuffer sql = new StringBuffer();sql.append(SELECT CONS_NAME,(SELECT NAME FROM ).append(Contants.DB_SCHEMA).append(P_CODE WHERE CODE_SORT_ID=29005 AND VALUE=TRADE_CODE) TRADE_NAME );sql.append(FROM ).append(Contants.DB_SCHEMA).append(C_CONS WHERE CONS_NO= ? );return super.jdbcTemplateEx.queryForMap(sql.toString(),new Objectparams.get(cons_no); /String trade_name = +consMap.get(TRADE_NAME);String caption = trade_name;String subcaption = ;try if(!StringUtil.isNullOrEmpty(trade_name)&trade_name.length()10) subcaption = trade_name.substring(10);caption = trade_name.substring(0,10);catch(Exception err) err.printStackTrace();dataStr.append();/if(exists_data) /for (int i = 0; i dataLst.size(); i+) Map pjbz = this.queryFshzData(params);if(pjbz!=null) /if(sdl.equalsIgnoreCase(String)params.get(show) dataStr.append( );dataStr.append( );dataStr.append( );/else /dataStr.append( );/dataStr.append( );/dataStr.append( );/if(pjbz=null) dataStr.append( );dataStr.append();return dataStr.toString(); /* * 交易分析-2.1.4.2电量电费构成比重数据 * return NONE * throws IOException ERR */public String findJyfxDldfGcBzStr() throws IOExceptionMap paramMap = RequestUtils.parameterConvert(params);paramMap.put(ym, getPreYm(paramMap);super.write(response, Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsRcvblBzStr(paramMap);return NONE;public String queryConsRcvblBzStr(Map params) / TODO Auto-generated method stubList dataLst = this.queryConsRcvbl(params);public List queryConsRcvbl(Map params) / TODO Auto-generated method stubString group = (1.equals(params.get(group) ? PRC_CODE : ELEC_TYPE_CODE;StringBuffer sql = new StringBuffer();sql.append(WITH TMP AS ( );sql.append(SELECT (case when (ELEC_TYPE_CODE=+group+ and +group+ IS NULL) THEN );sql.append( (SELECT ELEC_TYPE_CODE FROM EPMA.E_CAT_PRC WHERE PRC_CODE = AMT.PRC_CODE );sql.append( AND PARA_VN = (SELECT MAX(PARA_VN) FROM EPMA.E_BILL_PARA_VER WHERE PAR_VER_TYPE=1 AND RELEASE_FLAG=1) AND ROWNUM=1) );sql.append(ELSE +group+ END ) +group+,);sql.append(T_SETTLE_PQ SDL,T_AMT SDSR ,0 SDL_TQ ,0.00 SDSR_TQ );sql.append(FROM EPMA.A_RCVBL_FLOW RCVBL,EPMA.ARC_E_CONS_PRC_AMT AMT );sql.append(WHERE RCVBL.CALC_ID = AMT.CALC_ID AND RCVBL.CONS_NO=? );sql.append(AND EXISTS(SELECT 1 FROM EPMA.E_CALC_INV WHERE RCVBL_AMT_ID = RCVBL.RCVBL_AMT_ID AND NY=? AND ZHH=+params.get(cons_no)+) );sql.append(AND AMT.PRC_CODE0 AND TO_NUMBER(PRC_CODE)10000 );sql.append(UNION ALL );sql.append(SELECT (case when (ELEC_TYPE_CODE=+group+ and +group+ IS NULL) THEN );sql.append( (SELECT ELEC_TYPE_CODE FROM EPMA.E_CAT_PRC WHERE PRC_CODE = AMT.PRC_CODE );sql.append( AND PARA_VN = (SELECT MAX(PARA_VN) FROM EPMA.E_BILL_PARA_VER WHERE PAR_VER_TYPE=1 AND RELEASE_FLAG=1) AND ROWNUM=1) );sql.append(ELSE +group+ END ) +group+,);sql.append(0 SDL ,0.00 SDSR,T_SETTLE_PQ SDL_TQ,T_AMT SDSR_TQ ); sql.append(FROM EPMA.A_RCVBL_FLOW RCVBL,EPMA.ARC_E_CONS_PRC_AMT AMT );sql.append(WHERE RCVBL.CALC_ID = AMT.CALC_ID AND RCVBL.CONS_NO=? );sql.append(AND EXISTS(SELECT 1 FROM EPMA.E_CALC_INV WHERE RCVBL_AMT_ID = RCVBL.RCVBL_AMT_ID AND NY =? - 000100 AND ZHH=+params.get(cons_no)+) );sql.append(AND AMT.PRC_CODE0 AND TO_NUMBER(PRC_CODE)10000 );sql.append() );sql.append(SELECT (case when PRC_CODE=+group+ THEN (SELECT CAT_PRC_NAME FROM EPMA.E_CAT_PRC WHERE PRC_CODE = TMP.+group+ AND ROWNUM=1) );sql.append( when ELEC_TYPE_CODE=+group+ THEN (SELECT NAME FROM EPMA.P_CODE WHERE CODE_SORT_ID=29001 AND VALUE = +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_TQ FROM TMP );sql.append(GROUP BY +group+);return super.jdbcTemplateEx.queryForList(sql.toString(),new Objectparams.get(cons_no),params.get(ym),params.get(cons_no),params.get(ym),new intTypes.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR);Map gcMap = new HashMap();for(int i=0;idataLst.size();i+) Map data = (Map)dataLst.get(i);gcMap.put(data.get(category), data.get(sdl);/平均比重Map pjbz = this.queryFshzData(params);public Map queryFshzData (Map params) / TODO Auto-generated method stubStringBuffer sql = new StringBuffer();sql.append(SELECT SDL,TO_CHAR(FQDL) FQDL,TO_CHAR(PQDL) PQDL,TO_CHAR(GQDL) GQDL,FQDF,PQDF,GQDF, );sql.append(CASE WHEN SDL = 0 THEN 0 ELSE ROUND(FQDL/SDL*100,2) END) FQDLBZ, );sql.append(CASE WHEN SDL = 0 THEN 0 ELSE ROUND(GQDL/SDL*100,2) END) GQDLBZ, );sql.append(CASE WHEN SDL = 0 THEN 0 ELSE ROUND(PQDL/SDL*100,2) END) PQDLBZ, );sql.append(CASE WHEN DDDF = 0 THEN 0 ELSE ROUND(FQDF/DDDF*100,2) END) FQDFBZ, );sql.append(CASE WHEN DDDF = 0 THEN 0 ELSE ROUND(GQDF/DDDF*100,2) END) GQDFBZ, );sql.append(CASE WHEN DDDF = 0 THEN 0 ELSE ROUND(PQDF/DDDF*100,2) END) PQDFBZ ); sql.append(FROM EPMA.E_SBFSHYHZ WHERE LENGTH(ORG_NO)=5 AND YM=? AND HC=560);return super.jdbcTemplateEx.queryForMap(sql.toString(),new Objectparams.get(ym),new intTypes.VARCHAR); List results = new ArrayList();results.add(pjbz);/最优比重Map zybz = consDftzInfoDAO.queryZyBzData(params);public Map queryZyBzData(Map params) / TODO Auto-generated method stubStringBuffer sql = new StringBuffer();sql.append(SELECT GZBZ,FZBZ FROM ).append(Contants.DB_SCHEMA).append(ARC_FSHYBZ WHERE YM=?);return super.jdbcTemplateEx.queryForMap(sql.toString(),new Objectparams.get(ym);/获取用户的主行业Map consMap = consDftzInfoDAO.queryConsInfo(params); public Map queryConsInfo(Map params) / TODO Auto-generated method stubStringBuffer sql = new StringBuffer();sql.append(SELECT CONS_NAME,(SELECT NAME FROM ).append(Contants.DB_SCHEMA).append(P_CODE WHERE CODE_SORT_ID=29005 AND VALUE=TRADE_CODE) TRADE_NAME );sql.append(FROM ).append(Contants.DB_SCHEMA).append(C_CONS WHERE CONS_NO= ? );return super.jdbcTemplateEx.queryForMap(sql.toString(),new Objectparams.get(cons_no); zybz.put(trade_name, consMap.get(TRADE_NAME);results.add(zybz);/本户比重Map consbz = new HashMap();try Float cons_fqdl =Float.parseFloat(String)gcMap.get(峰期电量);Float cons_gqdl =Float.parseFloat(String)gcMap.get(谷期电量);Float cons_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) Float cons_fz = (cons_fqdl/cons_zdl)*100;Float cons_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(Exception err)return JSONArray.fromObject(results).toString();/ public Map queryFshzData(Map params) String xml_param = WsConnectUtil.convertMapForXML(params);String datas = WsConnectUtil.getXmlByClient(Contants.SERVICE_DLDF_URL, xml_param, queryFshzData);if(datas = null | null.equalsIgnoreCase(datas) return null;return JSONObject.fromObject(datas);/* * 交易分析-2.1.4.2电量电费构成比重数据(平均电价) * return NONE * throws IOException ERR */public String findJyfxDldfGcPrcStr() throws IOExceptionMap paramMap = RequestUtils.parameterConvert(params);paramMap.put(ym, getPreYm(paramMap);super.write(response, Contants.CONTENT_TYPE_IMAGE,consDftzInfo.queryConsAvgPriceStr(paramMap);return NONE;public String queryConsAvgPriceStr(Map params) / TODO Auto-generated method stubString xml_param = WsConnectUtil.convertMapForXML(params);String datas = WsConnectUtil.getXmlByClient(Contants.SERVICE_DLDF_URL, xml_param, queryAvgPriceData);return datas; public Map queryAvgPrice(Map params) / TODO Auto-generated method stubStringBuffer sql = new StringBuffer();sql.append(WITH TMP_PJ AS ( );/sql.append(SELECT SDL,NVL(DDDF,0)+NVL(XLDF,0)+NVL(RLDF,0)+NVL