電費賬單和實時電費賬單

實時電費賬單信息toJyfxDzzdMx_ss.actionfindConsDzzd_sspublicStringfindConsDzzd_ss(){MapparamMap=RequestUtils.parameterConvert(params);paramMap.put("flag","0");//當年年月Stringym=(String)paramMap.get("ym");DatenowDate=null;try{nowDate=DateTime.getPreviousDay(DateTime.getDate(ym.substring(0,4)+"-"+ym.substring(4,6)+"-"+DateTime.getNowDay()));}catch(Exceptionerr){}//判斷是否傳入了日期if(StringUtil.isNullOrEmpty(paramMap.get("date"))){paramMap.put("date",DateTime.dateToStr(nowDate));}request.setAttribute("zddate",paramMap.get("date"));ym=(""+paramMap.get("date")).substring(0,4)+(""+paramMap.get("date")).substring(5,7);paramMap.put("ym",ym);request.setAttribute("nowYm",ym.substring(0,4)+"年"+ym.substring(4,6)+"月");//獲取用戶的基本資料信息Mapcons_info=consDftzInfo.queryConsDetailInfo(paramMap);publicMapqueryConsDetailInfo(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTCONS_NO,CONS_NAME,ELEC_ADDR,");sql.append("(SELECTPOSTALCODE");sql.append("FROMEPMA.C_CONS_CONTACT_RELARELA,EPMA.C_CONTACTCON");sql.append("WHERERELA.CONTACT_ID=CON.CONTACT_IDANDRELA.CONS_ID=C.CONS_IDANDROWNUM=1)POST_CODE,");sql.append("(SELECTROUND(SUM(RCVBL_AMT)/10000,2)FROMEPMA.A_RCVBL_FLOWFLOW");sql.append("WHEREEXISTS(SELECT1FROMEPMA.E_CALC_INVINVWHEREINV.RCVBL_AMT_ID=FLOW.RCVBL_AMT_IDANDINV.NY=?ANDZHH='"+params.get("cons_no")+"')");sql.append("ANDFLOW.CONS_NO=C.CONS_NO");sql.append(")RCVBL_AMT,");sql.append("(SELECTROUND(SUM(THIS_RCVED_AMT)/10000,2)FROMEPMA.A_RCVED_FLOWFLOWWHEREFLOW.CONS_NO=C.CONS_NOANDRCVED_YM=?)RCVED_AMT,");sql.append("(SELECTSUM(PREPAY_AMT)FROMEPMA.A_PREPAY_FLOWWHERECONS_NO=C.CONS_NOANDPREPAY_YM=?)PREPAY_AMT");sql.append("FROMEPMA.C_CONSCWHERECONS_NO=?");MapconsMap=super.jdbcTemplateEx.queryForMap(sql.toString(),newObject[]{params.get("ym"),params.get("ym"),params.get("ym"),params.get("cons_no")},newint[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR});//用戶期初余額Stringqcye=this.queryConsQcye(params);//publicStringqueryConsQcye(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTTO_CHAR(ROUND(NVL(SUM(RCV_AMT),0)*-1,2))FROMEPMA.A_PAY_FLOWWHERECHARGE_IDIN(");sql.append("SELECTCHARGE_IDFROMEPMA.A_RCVED_FLOWRCVED,EPMA.A_RCVBL_FLOWRCVBL");sql.append("WHERERCVBL.RCVBL_AMT_ID=RCVED.RCVBL_AMT_ID");sql.append("ANDRCVBL.CALC_ID=(");sql.append("SELECTMAX(CALC_ID)FROMEPMA.A_RCVBL_FLOWWHERECONS_NO=?");sql.append("ANDRCVBL_AMT_ID<(SELECTMAX(RCVBL_AMT_ID)FROMEPMA.E_CALC_INVWHEREZHH=?ANDNY=?)");sql.append("ANDAMT_TYPE='01');sql.append(")");sql.append(")ANDTYPE_CODE='04'");return(String)super.jdbcTemplateEx.queryForObject(sql.toString(),newObject[]{params.get("cons_no"),params.get("cons_no"),params.get("ym")},newint[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR},String.class);}//consMap.put("QCYE",qcye);//用戶本期發(fā)生Stringbqfs=this.queryConsBqfs(params);//publicStringqueryConsBqfs(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTSUM(RCVBL_AMT)FROMEPMA.A_RCVBL_FLOWWHERECALC_ID=(");sql.append("SELECTMAX(CALC_ID)FROMEPMA.A_RCVBL_FLOWWHERECONS_NO=?");sql.append("ANDRCVBL_AMT_IDIN(SELECTRCVBL_AMT_IDFROMEPMA.E_CALC_INVWHEREZHH=?ANDNY=?)");sql.append("ANDAMT_TYPE='01');sql.append(")");return(String)super.jdbcTemplateEx.queryForObject(sql.toString(),newObject[]{params.get("cons_no"),params.get("cons_no"),params.get("ym")},newint[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR},String.class);}//consMap.put("BQFS",bqfs);//用戶本期應(yīng)繳consMap.put("BQYJ",queryConsBqye(consMap));//publicStringqueryConsBqye(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTTO_CHAR(?+?)FROMDUAL");return(String)super.jdbcTemplateEx.queryForObject(sql.toString(),newObject[]{params.get("QCYE"),params.get("BQFS")},newint[]{Types.VARCHAR,Types.VARCHAR},String.class);}//returnconsMap;}//request.setAttribute("cons_info",cons_info);//獲取用戶的歷史應(yīng)收電費賬單paramMap.put("group","3");Maprcvbl=consDftzInfo.queryConsRcvblInfo(paramMap);//publicMapqueryConsRcvblInfo(Mapparams){//TODOAuto-generatedmethodstubMapdataMap=newHashMap();StringBuffersql=newStringBuffer();//獲取用戶本月發(fā)行電費的所有計算標識Stringcalc_ids="";sql.append("SELECTMAX(CALC_ID)CALC_IDFROMEPMA.A_RCVBL_FLOWAWHEREA.CONS_NO=?");sql.append("ANDEXISTS(SELECT1FROMEPMA.E_CALC_INVCWHEREC.RCVBL_AMT_ID=A.RCVBL_AMT_IDANDC.NY=?ANDZHH='"+params.get("cons_no")+"')");ListcalcList=super.jdbcTemplateEx.queryForList(sql.toString(),newObject[]{params.get("cons_no"),params.get("ym")});if(calcList==null||calcList.size()==0){returndataMap;}for(inti=0;i<calcList.size();i++){if(i>0)calc_ids=calc_ids+",";MapcalcMap=(Map)calcList.get(i);calc_ids=calc_ids+calcMap.get("CALC_ID");}//獲取電費的發(fā)行日期sql.setLength(0);sql.append("SELECTTO_CHAR(SEND_DATE,'YYYY')||'年'||TO_CHAR(SEND_DATE,'MM')||'月'||TO_CHAR(SEND_DATE,'DD')||'日'SEND_DATEFROMEPMA.ARC_E_CONS_SNAPWHERECALC_IDin("+calc_ids+")");Stringsend_date=(String)super.jdbcTemplateEx.queryForObject(sql.toString(),String.class);if(StringUtil.isNullOrEmpty(send_date)){dataMap.put("send_date","");}else{dataMap.put("send_date",send_date);}//根據(jù)計算標識，獲取用戶本月發(fā)行電費的受電量信息sql.setLength(0);sql.append("SELECTDISTINCT(SELECTSP_NAMEFROMEPMA.C_SPWHERESP_ID=A.SP_ID)SP_NAME,A.SP_ID");sql.append("FROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDin("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDORDERBYA.SP_ID");ListspList=super.jdbcTemplateEx.queryForList(sql.toString());Listdata_sp_list=newArrayList();if(spList==null||spList.size()==0){dataMap.put("sp_info",data_sp_list);returndataMap;}for(inti=0;i<spList.size();i++){//定義受電點對象MapspMap=newHashMap();Mapobject_sp=(Map)spList.get(i);spMap.put("SP_ID",object_sp.get("SP_ID"));spMap.put("SP_NAME",object_sp.get("SP_NAME"));//根據(jù)受電點標識，獲取其對應(yīng)的電源信息sql.setLength(0);sql.append("SELECTDISTINCT(SELECTPS_NOFROMEPMA.C_PSWHEREPS_ID=A.PS_ID)PS_NO,A.PS_ID");sql.append("FROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDin("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID"));ListpsList=super.jdbcTemplateEx.queryForList(sql.toString());Listdata_ps_list=newArrayList();if(psList==null||psList.size()==0){spMap.put("ps_info",data_ps_list);break;}for(intps=0;ps<psList.size();ps++){//定義電源對象MappsMap=newHashMap();Mapobject_ps=(Map)psList.get(ps);Stringps_no=(StringUtil.isNullOrEmpty(object_ps.get("PS_NO")))?"":""+object_ps.get("PS_NO");psMap.put("PS_NO",ps_no);psMap.put("PS_ID",object_ps.get("PS_ID"));//根據(jù)電源信息，獲取功率因數(shù)信息sql.setLength(0);sql.append("SELECTDISTINCTNVL(ACTUAL_PF,'')ACTUAL_PF,(SELECTreplace(NAME,'考核標準','標準')FROMEPMA.P_CODEWHERECODE_SORT_ID=14004ANDVALUE=PF_STD_CODE)PF_STD");sql.append("FROMEPMA.ARC_E_CONSPRC_SNAPWHERECALC_IDIN("+calc_ids+")ANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDin("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+")ANDROWNUM=1");Mapactual_pf=newHashMap();try{actual_pf=super.jdbcTemplateEx.queryForMap(sql.toString());}catch(Exceptionerr){}psMap.put("ACTUAL_PF",actual_pf);//根據(jù)電源信息，獲取基本費結(jié)算信息sql.setLength(0);sql.append("SELECT(CASEBA_TYPE_CODEWHEN'0'THEN'計費容量'WHEN'2'THEN'自備容量'ELSE'最大需量'END)TYPE_NAME");sql.append(",SUM(BA_VALUE)BA_VALUE,SUM(BA)BAFROMEPMA.ARC_E_BASE_AMTWHEREPRC_AMT_IDIN(");sql.append("SELECTDISTINCTPRC_AMT_IDFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+"))GROUPBYBA_TYPE_CODE");Mapps_base=newHashMap();try{ps_base=super.jdbcTemplateEx.queryForMap(sql.toString());}catch(Exceptionerr){ps_base=newHashMap();}if(ps_base==null)ps_base=newHashMap();psMap.put("PS_BASE",ps_base);//根據(jù)電源信息，獲取基本費力調(diào)信息sql.setLength(0);sql.append("SELECTTO_CHAR(NVL(SUM(PF_ADJ_AMT),0))PF_ADJ_AMTFROMEPMA.ARC_E_PF_AMTWHEREPRC_AMT_IDIN(");sql.append("SELECTDISTINCTPRC_AMT_IDFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+"))ANDPRC_TS_CODE<>-10");Stringpf_adj_amt=(String)super.jdbcTemplateEx.queryForObject(sql.toString(),String.class);if(StringUtil.isNullOrEmpty(pf_adj_amt))pf_adj_amt="";";psMap.put("PF_ADJ_AMT",pf_adj_amt);//根據(jù)電源信息，獲取其對應(yīng)的計量點信息sql.setLength(0);sql.append("SELECTDISTINCT(SELECTMP_NAMEFROMEPMA.C_MPWHEREMP_ID=B.MP_ID)MP_NAME,B.MP_ID");sql.append("FROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+"ORDERBYB.MP_ID");ListmpList=super.jdbcTemplateEx.queryForList(sql.toString());Listdata_mp_list=newArrayList();if(mpList==null||mpList.size()==0){psMap.put("mp_info",data_mp_list);break;};intdata_count=0;for(intmp=0;mp<mpList.size();mp++){//定義計量點對象MapmpMap=newHashMap();Mapobject_mp=(Map)mpList.get(mp);mpMap.put("MP_ID",object_mp.get("MP_ID"));Stringmp_name=(StringUtil.isNullOrEmpty(object_mp.get("MP_NAME")))?""""+object_mp.get("MP_NAME");mpMap.put("MP_NAME",mp_name);if(mp%2==0){mpMap.put("color","1");}else{mpMap.put("color","0");}//根據(jù)計量點信息，獲取對應(yīng)的表號，以及綜合分倍率sql.setLength(0);sql.append("SELECT(SELECTASSET_NOFROMEPMA.D_METERWHEREMETER_ID=A.METER_ID)ASSET_NO,T_FACTOR");sql.append("FROMEPMA.ARC_R_DATAAWHERECALC_IDIN("+calc_ids+")ANDMP_ID="+object_mp.get("MP_ID")+"ANDROWNUM=1");Mapmp_info=newHashMap();try{mp_info=super.jdbcTemplateEx.queryForMap(sql.toString());}catch(Exceptionerr){mp_info=newHashMap();}if(mp_info==null)mp_info=newHashMap();Stringasset_no=(StringUtil.isNullOrEmpty(mp_info.get("ASSET_NO")))?"":""+mp_info.get("ASSET_NO");Stringt_factor=(StringUtil.isNullOrEmpty(mp_info.get("T_FACTOR")))?"":""+mp_info.get("T_FACTOR");mpMap.put("ASSET_NO",asset_no);mpMap.put("T_FACTOR",t_factor);//根據(jù)計量點信息，獲取抄表示數(shù)信息sql.setLength(0);sql.append("SELECT(SELECTNAMEFROMEPMA.P_CODEWHERECODE_SORT_ID=18010ANDVALUE=READ_TYPE_CODE)READ_TYPE,THIS_READ");sql.append("FROMEPMA.ARC_R_DATAAWHERECALC_IDIN("+calc_ids+")ANDMP_ID="+object_mp.get("MP_ID")+"ANDREAD_TYPE_CODE>=121ANDREAD_TYPE_CODE<=125");Listr_data=super.jdbcTemplateEx.queryForList(sql.toString());mpMap.put("R_DATA",r_data);//根據(jù)計量點信息，獲取結(jié)算電量電費信息sql.setLength(0);sql.append("SELECT(CASEPRC_TS_CODEWHEN'3'THEN'峰'WHEN'4'THEN'平'WHEN'5'THEN'谷'ELSE'非分時'END)PRC_TS_CODE,");sql.append("SUM(SETTLE_APQ)JSDL,TO_CHAR(SUM(KWH_AMT))JSDFFROMEPMA.ARC_E_KWH_AMTWHEREPRC_AMT_IDIN(");sql.append("SELECTDISTINCTPRC_AMT_IDFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+"ANDMP_ID="+object_mp.get("MP_ID")+"))");sql.append("GROUPBYPRC_TS_CODE");Listamt_list=super.jdbcTemplateEx.queryForList(sql.toString());mpMap.put("AMT_LIST",amt_list);//根據(jù)計量點信息，獲取力調(diào)數(shù)據(jù)sql.setLength(0);sql.append("SELECTTO_CHAR(NVL(SUM(PF_ADJ_AMT),0))PF_ADJ_AMTFROMEPMA.ARC_E_PF_AMTWHEREPRC_AMT_IDIN(");sql.append("SELECTDISTINCTPRC_AMT_IDFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+"ANDMP_ID="+object_mp.get("MP_ID")+"))ANDPRC_TS_CODE<>-10");Stringmp_pf_adj_amt=(String)super.jdbcTemplateEx.queryForObject(sql.toString(),String.class);mpMap.put("MP_PF_ADJ_AMT",mp_pf_adj_amt);//電源下面增加計量點mpMap.put("mp_r_count",r_data.size()+2);mpMap.put("mp_amt_count",amt_list.size()+1);mpMap.put("mp_count",(r_data.size()+amt_list.size()+amt_list.size()+5));data_count=data_count+(r_data.size()+amt_list.size()+amt_list.size()+5);data_mp_list.add(mpMap);psMap.put("mp_info",data_mp_list);psMap.put("d_count",data_count);//電源電量電費小計sql.setLength(0);sql.append("SELECTSUM(T_SETTLE_PQ)DL,TO_CHAR(SUM(T_AMT))DFFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.PRC_CODE<>'0'ANDTO_NUMBER(A.PRC_CODE)<10000");sql.append("ANDA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+")");MappsTotal=super.jdbcTemplateEx.queryForMap(sql.toString());if(psTotal==null)psTotal=newHashMap();psMap.put("psTotal",psTotal);//受電點下面增加電源data_ps_list.add(psMap);spMap.put("ps_info",data_ps_list);//受電點電量電費小計sql.setLength(0);sql.append("SELECTSUM(T_SETTLE_PQ)DL,TO_CHAR(SUM(T_AMT))DFFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.PRC_CODE<>'0'ANDTO_NUMBER(A.PRC_CODE)<10000");sql.append("ANDA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+")");MapspTotal=super.jdbcTemplateEx.queryForMap(sql.toString());if(spTotal==null)spTotal=newHashMap();spMap.put("spTotal",spTotal);}//增加受電點信息data_sp_list.add(spMap);dataMap.put("sp_info",data_sp_list);//總合計信息sql.setLength(0);sql.append("SELECTSUM(T_SETTLE_PQ)DL,TO_CHAR(SUM(T_AMT))DFFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.PRC_CODE<>'0'ANDTO_NUMBER(A.PRC_CODE)<10000");sql.append("ANDA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_ID)");Maptotal=super.jdbcTemplateEx.queryForMap(sql.toString());if(total==null)total=newHashMap();dataMap.put("total",total);}returndataMap;}//request.setAttribute("rcvbl",rcvbl);//獲取用戶的歷史預(yù)收電費賬單Listprepay_lst=consDftzInfo.queryConsHisPrepay(paramMap);//publicListqueryConsHisPrepay(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTPREPAY_AMT,PREPAY_DATE,CHARGE_NAME,PREPAY_YM,ACCT_NO,PREPAY_TYPE,RCV_ORG_NAMEFROM(");sql.append("SELECTA.PREPAY_AMT,");sql.append("(CASEWHENCHARGE_ID=-1THENA.PREPAY_DATE");sql.append("ELSE(SELECTTO_CHAR(CHARGE_DATE,'YYYY-MM-DDHH24:MI:SS')FROMEPMA.A_PAY_FLOWWHERECHARGE_ID=A.CHARGE_ID)");sql.append("END)PREPAY_DATE,");sql.append("(SELECT(SELECTNAMEFROMEPMA.O_STAFFWHEREEMP_NO=CHARGE_EMP_NO)FROMEPMA.A_PAY_FLOWWHERECHARGE_ID=A.CHARGE_ID)CHARGE_NAME,");sql.append("A.PREPAY_YM,A.ACCT_NO,");sql.append("(SELECTNAMEFROMEPMA.P_CODETWHERET.CODE_SORT_ID=15033ANDVALUE=A.PREPAY_TYPE)ASPREPAY_TYPE,");sql.append("(SELECT(CASEWHEN(SELECTORG_NAMEFROMEPMA.O_ORGWHEREORG_NO=RCV_ORG_NO)ISNULLTHEN");sql.append("(SELECTNAMEFROMEPMA.P_CODEWHERECODE_SORT_ID='10004'ANDVALUE=RCV_ORG_NO)");sql.append("ELSE(SELECTORG_NAMEFROMEPMA.O_ORGWHEREORG_NO=RCV_ORG_NO)END)");sql.append("FROMEPMA.A_PAY_FLOWWHERECHARGE_ID=A.CHARGE_ID");sql.append(")ASRCV_ORG_NAME");sql.append("FROMEPMA.A_PREPAY_FLOWAWHERECONS_NO=?)");sql.append("WHERETO_DATE(PREPAY_DATE,'YYYY-MM-DDHH24:MI:SS')<TO_DATE('"+params.get("ym")+"02','YYYY-MM-DDHH24:MI:SS')");sql.append("ANDTO_DATE(PREPAY_DATE,'YYYY-MM-DDHH24:MI:SS')>=ADD_MONTHS(TO_DATE('"+params.get("ym")+"02','YYYY-MM-DDHH24:MI:SS'),-1)");newreturnsuper.jdbcTemplateEx.queryForList(sql.toString()newObject[]{params.get("cons_no")},newint[]{Types.VARCHAR});}//request.setAttribute("prepay_lst",prepay_lst);Stringday=(DateTime.getDay((String)paramMap.get("date")"0"+DateTime.getDay((String)paramMap.get("date")):""+DateTime.getDay((String)paramMap.get("date"));Stringday_str=ym.substring(0,4)+"-"+ym.substring(4,6)+"-"+day;request.setAttribute("nowDateStr",day_str);request.setAttribute("nowDateDay",day);//獲取歷史一周日期ListdateLst=newArrayList();request.setAttribute("nowdate",DateTime.dateToStr(nowDate));//今日賬單日期try{Calendarc=Calendar.getInstance();for(inti=7;i>0;i--){c.setTime(nowDate);c.add(Calendar.DATE,-i);dateLst.add(DateTime.getNowDate(c.getTime()));}request.setAttribute("datelst",dateLst);}catch(Exceptionerr){}returnSUCCESS;}電費賬單toJyfxDzzdMx.actionfindConsDzzd()publicStringfindConsDzzd(){MapparamMap=RequestUtils.parameterConvert(params);paramMap.put("flag","1");//當年年月Stringym=getPreYm(paramMap);paramMap.put("ym",ym);request.setAttribute("zdym",ym.substring(0,4)+"年"+ym.substring(4,6)+"月");DatenowDate=DateTime.getDate(ym.substring(0,4)+"-"+ym.substring(4,6)+"-"+DateTime.getNowDay());//判斷是否傳入了日期if(StringUtil.isNullOrEmpty(paramMap.get("date"))){paramMap.put("date",DateTime.dateToStr(nowDate));}request.setAttribute("zddate",paramMap.get("date"));//獲取用戶的基本資料信息Mapcons_info=consDftzInfo.queryConsDetailInfo(paramMap);//publicMapqueryConsDetailInfo(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTCONS_NO,CONS_NAME,ELEC_ADDR,");sql.append("(SELECTPOSTALCODE");sql.append("FROMEPMA.C_CONS_CONTACT_RELARELA,EPMA.C_CONTACTCON");sql.append("WHERERELA.CONTACT_ID=CON.CONTACT_IDANDRELA.CONS_ID=C.CONS_IDANDROWNUM=1)POST_CODE,");sql.append("(SELECTROUND(SUM(RCVBL_AMT)/10000,2)FROMEPMA.A_RCVBL_FLOWFLOW");sql.append("WHEREEXISTS(SELECT1FROMEPMA.E_CALC_INVINVWHEREINV.RCVBL_AMT_ID=FLOW.RCVBL_AMT_IDANDINV.NY=?ANDZHH='"+params.get("cons_no")+"')");sql.append("ANDFLOW.CONS_NO=C.CONS_NO");sql.append(")RCVBL_AMT,");sql.append("(SELECTROUND(SUM(THIS_RCVED_AMT)/10000,2)FROMEPMA.A_RCVED_FLOWFLOWWHEREFLOW.CONS_NO=C.CONS_NOANDRCVED_YM=?)RCVED_AMT,");sql.append("(SELECTSUM(PREPAY_AMT)FROMEPMA.A_PREPAY_FLOWWHERECONS_NO=C.CONS_NOANDPREPAY_YM=?)PREPAY_AMT");sql.append("FROMEPMA.C_CONSCWHERECONS_NO=?");MapconsMap=super.jdbcTemplateEx.queryForMap(sql.toString(),newObject[]{params.get("ym"),params.get("ym"),params.get("ym"),params.get("cons_no")},newint[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR});//用戶期初余額Stringqcye=this.queryConsQcye(params);//publicStringqueryConsQcye(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTTO_CHAR(ROUND(NVL(SUM(RCV_AMT),0)*-1,2))FROMEPMA.A_PAY_FLOWWHERECHARGE_IDIN(");sql.append("SELECTCHARGE_IDFROMEPMA.A_RCVED_FLOWRCVED,EPMA.A_RCVBL_FLOWRCVBL");sql.append("WHERERCVBL.RCVBL_AMT_ID=RCVED.RCVBL_AMT_ID");sql.append("ANDRCVBL.CALC_ID=(");sql.append("SELECTMAX(CALC_ID)FROMEPMA.A_RCVBL_FLOWWHERECONS_NO=?");sql.append("ANDRCVBL_AMT_ID<(SELECTMAX(RCVBL_AMT_ID)FROMEPMA.E_CALC_INVWHEREZHH=?ANDNY=?)");sql.append("ANDAMT_TYPE='01');sql.append(")");sql.append(")ANDTYPE_CODE='04'");return(String)super.jdbcTemplateEx.queryForObject(sql.toString(),newObject[]{params.get("cons_no"),params.get("cons_no"),params.get("ym")},newint[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR},String.class);}//consMap.put("QCYE",qcye);//用戶本期發(fā)生Stringbqfs=this.queryConsBqfs(params);//publicStringqueryConsBqfs(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTSUM(RCVBL_AMT)FROMEPMA.A_RCVBL_FLOWWHERECALC_ID=(");sql.append("SELECTMAX(CALC_ID)FROMEPMA.A_RCVBL_FLOWWHERECONS_NO=?");sql.append("ANDRCVBL_AMT_IDIN(SELECTRCVBL_AMT_IDFROMEPMA.E_CALC_INVWHEREZHH=?ANDNY=?)");sql.append("ANDAMT_TYPE='01');sql.append(")");return(String)super.jdbcTemplateEx.queryForObject(sql.toString(),newObject[]{params.get("cons_no"),params.get("cons_no"),params.get("ym")},newint[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR},String.class);}//consMap.put("BQFS",bqfs);//用戶本期應(yīng)繳consMap.put("BQYJ",queryConsBqye(consMap));//publicStringqueryConsBqye(Mapparams){//TODOAuto-generatedmethodstubStringBuffersql=newStringBuffer();sql.append("SELECTTO_CHAR(?+?)FROMDUAL");return(String)super.jdbcTemplateEx.queryForObject(sql.toString(),newObject[]{params.get("QCYE"),params.get("BQFS")},newint[]{Types.VARCHAR,Types.VARCHAR},String.class);}//returnconsMap;}//request.setAttribute("cons_info",cons_info);//獲取用戶的歷史應(yīng)收電費賬單paramMap.put("group","3");Maprcvbl=consDftzInfo.queryConsRcvblInfo(paramMap);//publicMapqueryConsRcvblInfo(Mapparams){//TODOAuto-generatedmethodstubMapdataMap=newHashMap();StringBuffersql=newStringBuffer();//獲取用戶本月發(fā)行電費的所有計算標識Stringcalc_ids="";sql.append("SELECTMAX(CALC_ID)CALC_IDFROMEPMA.A_RCVBL_FLOWAWHEREA.CONS_NO=?");sql.append("ANDEXISTS(SELECT1FROMEPMA.E_CALC_INVCWHEREC.RCVBL_AMT_ID=A.RCVBL_AMT_IDANDC.NY=?ANDZHH='"+params.get("cons_no")+"')");ListcalcList=super.jdbcTemplateEx.queryForList(sql.toString(),newObject[]{params.get("cons_no"),params.get("ym")});if(calcList==null||calcList.size()==0){returndataMap;}for(inti=0;i<calcList.size();i++){if(i>0)calc_ids=calc_ids+",";MapcalcMap=(Map)calcList.get(i);calc_ids=calc_ids+calcMap.get("CALC_ID");}//獲取電費的發(fā)行日期sql.setLength(0);sql.append("SELECTTO_CHAR(SEND_DATE,'YYYY')||'年'||TO_CHAR(SEND_DATE,'MM')||'月'||TO_CHAR(SEND_DATE,'DD')||'日'SEND_DATEFROMEPMA.ARC_E_CONS_SNAPWHERECALC_IDin("+calc_ids+")");Stringsend_date=(String)super.jdbcTemplateEx.queryForObject(sql.toString(),String.class);if(StringUtil.isNullOrEmpty(send_date)){dataMap.put("send_date","");}else{dataMap.put("send_date",send_date);}//根據(jù)計算標識，獲取用戶本月發(fā)行電費的受電量信息sql.setLength(0);sql.append("SELECTDISTINCT(SELECTSP_NAMEFROMEPMA.C_SPWHERESP_ID=A.SP_ID)SP_NAME,A.SP_ID");sql.append("FROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDin("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDORDERBYA.SP_ID");ListspList=super.jdbcTemplateEx.queryForList(sql.toString());Listdata_sp_list=newArrayList();if(spList==null||spList.size()==0){dataMap.put("sp_info",data_sp_list);returndataMap;}for(inti=0;i<spList.size();i++){//定義受電點對象MapspMap=newHashMap();Mapobject_sp=(Map)spList.get(i);spMap.put("SP_ID",object_sp.get("SP_ID"));spMap.put("SP_NAME",object_sp.get("SP_NAME"));//根據(jù)受電點標識，獲取其對應(yīng)的電源信息sql.setLength(0);sql.append("SELECTDISTINCT(SELECTPS_NOFROMEPMA.C_PSWHEREPS_ID=A.PS_ID)PS_NO,A.PS_ID");sql.append("FROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDin("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID"));ListpsList=super.jdbcTemplateEx.queryForList(sql.toString());Listdata_ps_list=newArrayList();if(psList==null||psList.size()==0){spMap.put("ps_info",data_ps_list);break;}for(intps=0;ps<psList.size();ps++){//定義電源對象MappsMap=newHashMap();Mapobject_ps=(Map)psList.get(ps);Stringps_no=(StringUtil.isNullOrEmpty(object_ps.get("PS_NO")))?"":""+object_ps.get("PS_NO");psMap.put("PS_NO",ps_no);psMap.put("PS_ID",object_ps.get("PS_ID"));//根據(jù)電源信息，獲取功率因數(shù)信息sql.setLength(0);sql.append("SELECTDISTINCTNVL(ACTUAL_PF,'')ACTUAL_PF,(SELECTreplace(NAME,'考核標準','標準')FROMEPMA.P_CODEWHERECODE_SORT_ID=14004ANDVALUE=PF_STD_CODE)PF_STD");sql.append("FROMEPMA.ARC_E_CONSPRC_SNAPWHERECALC_IDIN("+calc_ids+")ANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E_PS_SNAPA,EPMA.ARC_E_MP_PARA_SNAPB");sql.append("WHEREA.CALC_IDin("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.SP_IDISNOTNULLANDA.SP_ID=B.SP_ID");sql.append("ANDA.TG_ID=B.TG_IDANDA.SP_ID="+object_sp.get("SP_ID")+"ANDA.PS_ID="+object_ps.get("PS_ID")+")ANDROWNUM=1");Mapactual_pf=newHashMap();try{actual_pf=super.jdbcTemplateEx.queryForMap(sql.toString());}catch(Exceptionerr){}psMap.put("ACTUAL_PF",actual_pf);//根據(jù)電源信息，獲取基本費結(jié)算信息sql.setLength(0);sql.append("SELECT(CASEBA_TYPE_CODEWHEN'0'THEN'計費容量'WHEN'2'THEN'自備容量'ELSE'最大需量'END)TYPE_NAME");sql.append(",SUM(BA_VALUE)BA_VALUE,SUM(BA)BAFROMEPMA.ARC_E_BASE_AMTWHEREPRC_AMT_IDIN(");sql.append("SELECTDISTINCTPRC_AMT_IDFROMEPMA.ARC_E_CONS_PRC_AMTA,EPMA.ARC_E_CONSPRC_SNAPB");sql.append("WHEREA.CALC_IDIN("+calc_ids+")ANDA.CALC_ID=B.CALC_IDANDA.PRC_SNAP_ID=B.PRC_SNAP_IDANDMP_IDIN(");sql.append("SELECTDISTINCTB.MP_IDFROMEPMA.ARC_E