搜索
Action层publicActionForwardgetProjectPayMoneyJsonByPayType(ActionMappingmapping,ActionFormform,HttpServletRequestrequest,HttpServletResponseresponse)throwsException{JiLiangZhiFuServicejiLiangZhiFuService=this.getServiceLocator().getJiLiangZhiFuService();intyear=Integer.parseInt(request.getParameter(year));Stringdata=jiLiangZhiFuService.getProjectPayMoneyJsonByPayType(year);response.setContentType(text/xml);response.getWriter().write(data);response.getWriter().flush();response.getWriter().close();returnnull;}Service层publicStringgetProjectPayMoneyJsonByPayType(intyear){doubleallTotal=0;Mapmap=jiLiangZhiFuDao.getProjectPayMoneyByPayType(year);ListDataItemdataItemList=SessionBean.getServiceLocator().getDataItemService().getAllDataItemByTypeId(payType);Mapmap_ep=newHashMap();map_ep.put(account_id,2);map_ep.put(isSelectTwoStageProject,true);ListEngineeringPhaseengineeringPhaseList=SessionBean.getServiceLocator().getEngineeringPhaseService().getEngineeringPhaseList(map_ep).getData();Mapmap_type=newHashMap();StringBuildersb=newStringBuilder();sb.append(datetotal+engineeringPhaseList.size()+/total);for(EngineeringPhaseengineeringPhase:engineeringPhaseList){Stringep_id=engineeringPhase.getEp_id();sb.append(ProjectPayMoneyInfo);sb.append(ep_id+ep_id+/ep_id);sb.append(ep_name+engineeringPhase.getEp_name()+/ep_name);for(DataItemdataItem:dataItemList){Stringdi_id=dataItem.getId();Stringr_key=ep_id+,+di_id;if(map.containsKey(r_key)){map_type.put(di_id,map_type.containsKey(di_id)?(Double.parseDouble(map_type.get(di_id).toString())+Double.parseDouble(map.get(r_key).toString())):Double.parseDouble(map.get(r_key).toString()));sb.append(+di_id++map.get(r_key)+/+di_id+);}}//项目支付金额合计sb.append(projectTotal+map.get(engineeringPhase.getEp_id())+/projectTotal);allTotal+=Double.parseDouble(map.get(engineeringPhase.getEp_id()).toString());sb.append(/ProjectPayMoneyInfo);}//某支付类别金额合计sb.append(ProjectPayMoneyInfo);sb.append(ep_id/ep_id);sb.append(ep_name合计/ep_name);DecimalFormatdf=newDecimalFormat(#.00);for(DataItemdataItem:dataItemList){sb.append(+dataItem.getId()++String.format(%.2f,map_type.get(dataItem.getId()))+/+dataItem.getId()+);}//各项目累计合计sb.append(projectTotal+String.format(%.2f,allTotal)+/projectTotal);sb.append(/ProjectPayMoneyInfo);sb.append(/date);returnsb.toString();}Dao层publicMapgetProjectPayMoneyByPayType(finalintyear){try{return(Map)this.getHibernateTemplate().execute(newHibernateCallback(){publicObjectdoInHibernate(Sessionsession)throwsHibernateException,SQLException{Connectioncon=session.connection();Statementstmt=con.createStatement();CallableStatementcs=con.prepareCall({callproc_records(?)});cs.setInt(1,year);//ResultSetrs=stmt.executeQuery({callproc_records});ResultSetrs=cs.executeQuery();Mapmap=newHashMap();while(rs.next()){map.put(rs.getString(1),rs.getString(2));}rs.close();stmt.close();returnmap;}});}catch(org.springframework.dao.DataAccessExceptione){thrownewDataAccessException(e.getMessage(),e);}}Sql：createprocedureproc_records@yearintasDECLARE@map_tabletable(r_keyvarchar(100),r_valuevarchar(50))DECLARE@type_idvarchar(40)DECLARE@type_namevarchar(50)DECLARE@project_idvarchar(40)DECLARE@project_namevarchar(50)DECLARE@payTypeMoneynumeric(16,2)DECLARE@sumPayTypeMoneynumeric(16,2)BEGINset@payTypeMoney=0set@sumPayTypeMoney=0--查询项目列表DECLAREproject_cursorCURSORforselectep_id,ep_namefromAB_engineeringPhasewhereaccount_id='2'openproject_cursorfetchnextfromproject_cursorinto@project_id,@project_namewhile@@FETCH_STATUS=0begin--查询项目支付类别DECLAREprojectType_cursorCURSORforselectid,namefromT_DataItemwheretypeId='payType'orderbysortascopenprojectType_cursorfetchnextfromprojectType_cursorinto@type_id,@type_namewhile@@FETCH_STATUS=0begin--业务逻辑处理处理内层游标--获取一个项目某一项支付类型的数据--查询一个项目某一支付类别金额select@payTypeMoney=sum(checkProjectPayMoney)fromJiLiangZhiFuwhereprojectId=@project_idandpayType=@type_idandenterDateCAST(@yearasvarchar(4))andenterDateCAST(@year+1asvarchar(4))andtype='outer'groupbypayType--一个项目某一支付类别金额存入insertinto@map_tablevalues(@project_id+','+@type_id,@payTypeMoney)--一个项目累计支付类别金额set@sumPayTypeMoney=@sumPayTypeMoney+@payTypeMoney--一个项目某一支付类别金额重置为set@payTypeMoney=0fetchnextfromprojectType_cursorinto@type_id,@type_name--内层游标向下移动一行end--插入某一项目各个支付类别的金额合计insertinto@map_tablevalues(@project_id,@sumPayTypeMoney)set@sumPayTypeMoney=0closeprojectType_cursordeallocateprojectType_cursorfetchnextfromproject_cursorinto@project_id,@project_name--内层游标处理结束后,外层游标才继续向下移动一行endcloseproject_cursordeallocateproject_cursorselect*from@map_tableEND