瑞联科技：Pwp3框架 调用存储过程返还数据集合 到前端界面展示
一：代码结构：
1：Js 代码结构
2：Java 代码结构
二：前端界面展示效果
为了数据安全性；界面数据做了处理
三：全端代码展示
1：main.vop
<html> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" /> <title>空白功能</title> <#include '/common/budget_head.vop'> <script src="${ctx}/config/gdsd_gkzf/func/dailyReport/itemPayReport/main.js"></script> <script type="text/javascript"> $(document).ready(function(){ $("#query_month_start").getWidget().disable('none'); $("#query_month_end").getWidget().disable('none'); $("input:radio[name='query_type']").change(function (){ //拨通 var query_type=$("input:radio[name='query_type']:checked").val() ; if(query_type=='0'){ $("#query_month_start").getWidget().disable('none'); $("#query_month_end").getWidget().disable('none'); $("#query_year").getWidget().enable('none'); $("#query_year").getWidget().setValue(); $("#query_month_start").getWidget().setValue(); $("#query_month_end").getWidget().setValue(); }else if(query_type=='1'){ $("#query_month_start").getWidget().enable('none'); $("#query_month_end").getWidget().enable('none'); $("#query_year").getWidget().disable('none'); $("#query_month_start").getWidget().setValue(); $("#query_month_end").getWidget().setValue(); $("#query_year").getWidget().setValue(); } }); }); </script> </head> <body> <div data-options="fit:true" style="height: 90%"> <!-- 查询条件 查询显示框：start --> <div id="townCondition" style="padding-left: 15px; padding-top: 10px; padding-bottom: 15px; padding-right: 15px; height: auto; background-color: #FCFCFC; "> <div id="searchUnitForm" class="widget-conditionForm" style="padding: 0px; margin: 0px;" align="center"> <table border="0" width="98%" > <tr> <td style="text-align:right;">年报/月报：</td> <td style="text-align:left"> <input type="radio" name="query_type" value="0" checked="checked" >年报</input> <input type="radio" name="query_type" value="1">月报</input> </td> <td>年度</td> <td><input class="widget-yearbox" id="query_year" name="year" style="width: 160px" editable=false /> <td>月份</td> <td><input class="widget-monthbox" id="query_month_start" name="month" style="width: 160px" editable=false /> -- <input class="widget-monthbox" id="query_month_end" name="month" style="width:160px" editable=false /> <td align="center" id="search_id" > <a href="#" class="widget-linkbutton" plain='true' iconCls="icon-search" id="searchBudgetBtn" >查询</a> <a href="#" class="widget-linkbutton" plain='true' iconCls="icon-undo" id="resetBudgetBtn">重置 </a> </td> </tr> </table> </div> </div> <div id="mainGrid"></div> </div> </body> </html>
2:main.js
$(document).ready(function(){ //主表绑定 window.mainGridBind = $W.databind.arrayDatabind({ id:'ItemPayReport', name:'主列表绑定', autoload:true, binds:['#mainGrid',"#searchUnitForm"], beforeLoad:function (param) { var query_month_start= $("#query_month_start").getWidget().getValue(); var query_month_end=$("#query_month_end").getWidget().getValue(); var query_year=$("#query_year").getWidget().getValue(); var query_type=$("input:radio[name='query_type']:checked").val() ; if(query_type =='0' && (query_year !=undefined && query_year.replace(/^\s\s*/, '').replace(/\s\s*$/, '')!='')){ param.query_type=query_type; param.query_year=query_year; param.query_month_start=query_month_start; param.query_month_end=query_month_end; }else if(query_type =='1' && (query_month_end!=undefined && query_month_start!=undefined && query_month_start.replace(/^\s\s*/, '').replace(/\s\s*$/, '')!='' && query_month_end.replace(/^\s\s*/, '').replace(/\s\s*$/, '')!='')){ param.query_type=query_type; param.query_year=query_year; param.query_month_start=query_month_start; param.query_month_end=query_month_end; } }, afterLoad:function(data){ }, onLoadError:function(data){ } }); //主表查询 window.searchUnitForm = $("#searchUnitForm").getWidget(); $('#searchBudgetBtn').click(function(){ var query_month_start= $("#query_month_start").getWidget().getValue(); var query_month_end=$("#query_month_end").getWidget().getValue(); var query_year=$("#query_year").getWidget().getValue(); var query_type=$("input:radio[name='query_type']:checked").val() ; if(query_type =='0' && (query_year==undefined || query_year.replace(/^\s\s*/, '').replace(/\s\s*$/, '')=='')){ $.messager.alert('消息','请选择年度时间！','info'); return ; }else if(query_type =='1' && (query_month_end==undefined || query_month_start==undefined || query_month_start.replace(/^\s\s*/, '').replace(/\s\s*$/, '')=='' || query_month_end.replace(/^\s\s*/, '').replace(/\s\s*$/, '')=='')){ $.messager.alert('消息','请选择月报的年月起始及终止时间 ','info'); return ; } if(query_type ==undefined || query_type.replace(/^\s\s*/, '').replace(/\s\s*$/, '')==''){ $.messager.alert('消息','请选择报表类型 ','info'); return ; } searchUnitForm.submit(); }); $('#resetBudgetBtn').click(function(){ searchUnitForm.clear(); }); window.mainGrid = $('#mainGrid').widgets({ xtype:"datagrid", idField : 'id', pagination:true, cellTip:true, cellEdit:true, border:false, rownumbers:true, autoload:true, pageSize:200, pageList: ["100","200","300","400","500"], showFooter:true, nowrap:false, exportWithColumnWidth:true, exportFileName:'',//导出的文件名，如果不写默认等于title属性 exportButton:['excel'],//显示导出按钮 exportOptions:{ //导出参数 offsetCol:0, //excel中数据列偏移量,默认是0 offsetRow:1 //excel中数据行偏移量,默认是0 }, frozenColumns:[ [ {"title":"经济分类",colspan:'2'}], [{field:'id',title:'编码',width:120,halign:'center',align:'center',hidden:'true',sortable: true,unexport:true,rowspan:'1'}, {field:'itemno',title:'编码',width:120,halign:'center',align:'left',sortable: true, rowspan:'1'}, {field:'item_name',title:'名称',width:120,halign:'center',align:'left',sortable: true,rowspan:'1'} ]], columns:[ [ {"title":"直接支付",colspan:'6'}, {"title":"授权支付",colspan:'6'}, {"title":"一般支付",colspan:'6'}, {field:'sum_amt',title:'总合计',width:160,halign:'center',align:'right',rowspan:'2', formatter:function(value,row,index){ return moneyFormat(value,2); } } ], [{field:'zjzf_zczfamt',title:'正常对碰',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'zjzf_qetkamt',title:'全额退款',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'zjzf_bftkamt',title:'部分退款',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'zjzf_zftzjfamt',title:'调账借方',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'zjzf_zftzdfamt',title:'调账贷方',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sum_zjzf',title:'合计',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sqzf_zczfamt',title:'正常对碰',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sqzf_qetkamt',title:'全都退款',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sqzf_bftkamt',title:'部分退款',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sqzf_zftzjfamt',title:'调账借方',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sqzf_zftzdfamt',title:'调账贷方',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sum_sqzf',title:'合计',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'ybzf_zczfamt',title:'正常对碰',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'ybzf_qetkamt',title:'全额退款',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'ybzf_bftkamt',title:'部分退款',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'ybzf_zftzjfamt',title:'调账借方',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'ybzf_zftzdfamt',title:'调账贷方',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}, {field:'sum_ybzf',title:'合计',width:160,halign:'center',align:'right',rowspan:'1', formatter:function(value,row,index){ return moneyFormat(value,2); }}] ] }); }); //金额格式化 function moneyFormat(value,row,index){ return formatMoney(value,2); }
3:xml
省略
4：ItemPayReport_load.srv.js
服务层 调用 java代码
var spring = require("spring"); var itemPayReportCtl = spring.getBean("itemPayReportCtl"); var query_type=param.query_type; var query_year=param.query_year; var query_month_start=param.query_month_start; var query_month_end=param.query_month_end ; var data={}; if(query_type!=undefined && query_type.replace(/^\s\s*/, '').replace(/\s\s*$/, '')!='' ){ data= itemPayReportCtl.setData(query_type,query_year,query_month_start,query_month_end) ; } return data;
四：Java 代码部分
1：Spring 注解配置
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd"> <!--日常报表--> <bean id="itemPayReportCtl" class="com.todaytech.yth.gdsd.gkzf.Report.controller.ItemPayReportCtl"> <!--(1)依赖注入,配置当前类中相应的属性--> <property name="itemPayReportService" ref="itemPayReportService"></property> </bean> <bean id="itemPayReportService" class="com.todaytech.yth.gdsd.gkzf.Report.service.imp.ItemPayReportServiceImp"></bean> </beans>
2：ItemPayReportCtl.java
/* * Copyright 2002-2007 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.springframework.stereotype; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Indicates that an annotated class is a "Controller" (e.g. a web controller). * * <p>This annotation serves as a specialization of {@link Component @Component}, * allowing for implementation classes to be autodetected through classpath scanning. * It is typically used in combination with annotated handler methods based on the * {@link org.springframework.web.bind.annotation.RequestMapping} annotation. * * @author Arjen Poutsma * @author Juergen Hoeller * @since 2.5 * @see Component * @see org.springframework.web.bind.annotation.RequestMapping * @see org.springframework.context.annotation.ClassPathBeanDefinitionScanner */ @Target({ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) @Documented @Component public @interface Controller { /** * The value may indicate a suggestion for a logical component name, * to be turned into a Spring bean in case of an autodetected component. * @return the suggested component name, if any */ String value() default ""; }
package com.todaytech.yth.gdsd.gkzf.Report.controller; import java.util.List; import org.springframework.stereotype.Controller; import com.todaytech.yth.gdsd.gkzf.Report.Dto.ItemPayReportDto; import com.todaytech.yth.gdsd.gkzf.Report.service.ItemPayReportService; /** * 查询经济分类报表数据 * @author amin * */ @Controller public class ItemPayReportCtl { private ItemPayReportService itemPayReportService; public ItemPayReportService getItemPayReportService() { return itemPayReportService; } public void setItemPayReportService(ItemPayReportService itemPayReportService) { this.itemPayReportService = itemPayReportService; } public List<ItemPayReportDto> setData(String yeartype,String year ,String montStart,String monthEnd ){ List<ItemPayReportDto> list=itemPayReportService.setData(yeartype, year, montStart, monthEnd); return list ; } }
2：ItemPayReportDto.java
package com.todaytech.yth.gdsd.gkzf.Report.Dto; /** * 经济分类报表 对象 * @author amin * */ public class ItemPayReportDto { private String item_name ; //经济分类名称 private String id ; // private String itemno ; // 经济分类编码 private String zjzf_zczfamt ; // 直接支付 正常支出 private String zjzf_qetkamt ; // 直接支付 全额退款 private String zjzf_bftkamt ; // 直接支付 部分退款 private String zjzf_zftzjfamt ; // 直接支付 支出调账借方 private String zjzf_zftzdfamt ; // 直接支付 支出调账贷方 private String sqzf_zczfamt ; // 授权支付 private String sqzf_qetkamt ; // 授权支付 private String sqzf_bftkamt ; // 授权支付 private String sqzf_zftzjfamt ; // 授权支付 private String sqzf_zftzdfamt ; // 授权支付 private String ybzf_zczfamt ; // 一般支付 private String ybzf_qetkamt ; // 一般支付 private String ybzf_bftkamt ; // 一般支付 private String ybzf_zftzjfamt ; // 一般支付 private String ybzf_zftzdfamt ;// 一般支付 private String sum_zjzf ; // 直接支付 合计 private String sum_sqzf ; // 授权支付 合计 private String sum_ybzf ; // 一般支付合计 private String sum_amt ;// 总计 public String getItem_name() { return item_name; } public void setItem_name(String item_name) { this.item_name = item_name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getItemno() { return itemno; } public void setItemno(String itemno) { this.itemno = itemno; } public String getZjzf_zczfamt() { return zjzf_zczfamt; } public void setZjzf_zczfamt(String zjzf_zczfamt) { this.zjzf_zczfamt = zjzf_zczfamt; } public String getZjzf_qetkamt() { return zjzf_qetkamt; } public void setZjzf_qetkamt(String zjzf_qetkamt) { this.zjzf_qetkamt = zjzf_qetkamt; } public String getZjzf_bftkamt() { return zjzf_bftkamt; } public void setZjzf_bftkamt(String zjzf_bftkamt) { this.zjzf_bftkamt = zjzf_bftkamt; } public String getZjzf_zftzjfamt() { return zjzf_zftzjfamt; } public void setZjzf_zftzjfamt(String zjzf_zftzjfamt) { this.zjzf_zftzjfamt = zjzf_zftzjfamt; } public String getZjzf_zftzdfamt() { return zjzf_zftzdfamt; } public void setZjzf_zftzdfamt(String zjzf_zftzdfamt) { this.zjzf_zftzdfamt = zjzf_zftzdfamt; } public String getSqzf_zczfamt() { return sqzf_zczfamt; } public void setSqzf_zczfamt(String sqzf_zczfamt) { this.sqzf_zczfamt = sqzf_zczfamt; } public String getSqzf_qetkamt() { return sqzf_qetkamt; } public void setSqzf_qetkamt(String sqzf_qetkamt) { this.sqzf_qetkamt = sqzf_qetkamt; } public String getSqzf_bftkamt() { return sqzf_bftkamt; } public void setSqzf_bftkamt(String sqzf_bftkamt) { this.sqzf_bftkamt = sqzf_bftkamt; } public String getSqzf_zftzjfamt() { return sqzf_zftzjfamt; } public void setSqzf_zftzjfamt(String sqzf_zftzjfamt) { this.sqzf_zftzjfamt = sqzf_zftzjfamt; } public String getSqzf_zftzdfamt() { return sqzf_zftzdfamt; } public void setSqzf_zftzdfamt(String sqzf_zftzdfamt) { this.sqzf_zftzdfamt = sqzf_zftzdfamt; } public String getYbzf_zczfamt() { return ybzf_zczfamt; } public void setYbzf_zczfamt(String ybzf_zczfamt) { this.ybzf_zczfamt = ybzf_zczfamt; } public String getYbzf_qetkamt() { return ybzf_qetkamt; } public void setYbzf_qetkamt(String ybzf_qetkamt) { this.ybzf_qetkamt = ybzf_qetkamt; } public String getYbzf_bftkamt() { return ybzf_bftkamt; } public void setYbzf_bftkamt(String ybzf_bftkamt) { this.ybzf_bftkamt = ybzf_bftkamt; } public String getYbzf_zftzjfamt() { return ybzf_zftzjfamt; } public void setYbzf_zftzjfamt(String ybzf_zftzjfamt) { this.ybzf_zftzjfamt = ybzf_zftzjfamt; } public String getYbzf_zftzdfamt() { return ybzf_zftzdfamt; } public void setYbzf_zftzdfamt(String ybzf_zftzdfamt) { this.ybzf_zftzdfamt = ybzf_zftzdfamt; } public String getSum_zjzf() { return sum_zjzf; } public void setSum_zjzf(String sum_zjzf) { this.sum_zjzf = sum_zjzf; } public String getSum_sqzf() { return sum_sqzf; } public void setSum_sqzf(String sum_sqzf) { this.sum_sqzf = sum_sqzf; } public String getSum_ybzf() { return sum_ybzf; } public void setSum_ybzf(String sum_ybzf) { this.sum_ybzf = sum_ybzf; } public String getSum_amt() { return sum_amt; } public void setSum_amt(String sum_amt) { this.sum_amt = sum_amt; } @Override public String toString() { return "ItemPayReportDto [item_name=" + item_name + ", id=" + id + ", itemno=" + itemno + ", zjzf_zczfamt=" + zjzf_zczfamt + ", zjzf_qetkamt=" + zjzf_qetkamt + ", zjzf_bftkamt=" + zjzf_bftkamt + ", zjzf_zftzjfamt=" + zjzf_zftzjfamt + ", zjzf_zftzdfamt=" + zjzf_zftzdfamt + ", sqzf_zczfamt=" + sqzf_zczfamt + ", sqzf_qetkamt=" + sqzf_qetkamt + ", sqzf_bftkamt=" + sqzf_bftkamt + ", sqzf_zftzjfamt=" + sqzf_zftzjfamt + ", sqzf_zftzdfamt=" + sqzf_zftzdfamt + ", ybzf_zczfamt=" + ybzf_zczfamt + ", ybzf_qetkamt=" + ybzf_qetkamt + ", ybzf_bftkamt=" + ybzf_bftkamt + ", ybzf_zftzjfamt=" + ybzf_zftzjfamt + ", ybzf_zftzdfamt=" + ybzf_zftzdfamt + ", sum_zjzf=" + sum_zjzf + ", sum_sqzf=" + sum_sqzf + ", sum_ybzf=" + sum_ybzf + ", sum_amt=" + sum_amt + "]"; } }
3:ItemPayReportService.java
package com.todaytech.yth.gdsd.gkzf.Report.service; import java.util.List; import com.todaytech.yth.gdsd.gkzf.Report.Dto.ItemPayReportDto; /** * 查询日常报表 经济分类报表 */ public interface ItemPayReportService { /** * * @param yeartype 查询类型：0 为 年 1 为月 * @param year 年度：2018 * @param montStart 起始年月：201801 * @param monthEnd 结束年月：201801 * @return */ List<ItemPayReportDto> setData(String yeartype, String year, String montStart, String monthEnd); }
4:ItemPayReportServiceImp.java
package com.todaytech.yth.gdsd.gkzf.Report.service.imp; import java.sql.CallableStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.annotation.Resource; import org.apache.log4j.Logger; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.todaytech.yth.gdsd.gkzf.Report.Dto.ItemPayReportDto; import com.todaytech.yth.gdsd.gkzf.Report.service.ItemPayReportService; import com.tt.pwp.framework.data.dao.DaoFactory; import oracle.jdbc.OracleTypes; /** * 查询日常报表 经济分类报表 数据 * @author amin * */ @Service @Transactional public class ItemPayReportServiceImp implements ItemPayReportService { private static Logger log = Logger.getLogger(ItemPayReportServiceImp.class); /** * 获取数据源 * @return */ @Resource private DaoFactory daoFactory; /** * * @param yeartype 查询类型：0 为 年 1 为月 * @param year 年度：2018 * @param montStart 起始年月：201801 * @param monthEnd 结束年月：201801 * @return */ @Override public List<ItemPayReportDto> setData(String yeartype, String year, String montStart, String monthEnd) { String procedure = "{call pro_query_itempay_temp(?,?,?,?,?)}"; List<ItemPayReportDto> list= executeSP(procedure, yeartype, year,montStart, monthEnd); return list; } /** * 调用存储过程 * @param spName */ @SuppressWarnings({ "unchecked", "rawtypes" }) public List<ItemPayReportDto> executeSP(String procedure,final String yeartype,final String year,final String montStart,final String monthEnd) { JdbcTemplate jdbcTemplate = daoFactory.getDao().getJdbcTemplate(); return (List<ItemPayReportDto>) jdbcTemplate.execute(procedure, new CallableStatementCallback() { public Object doInCallableStatement( CallableStatement cs) throws SQLException, DataAccessException { List<ItemPayReportDto> list = new ArrayList<ItemPayReportDto>(); cs.setString(1, yeartype); cs.setString(2, year); cs.setString(3, montStart); cs.setString(4, monthEnd); cs.registerOutParameter(5, OracleTypes.CURSOR); cs.execute(); ResultSet rs = (ResultSet) cs.getObject(5); while (rs.next()) { ResultSetMetaData rsMataData = rs.getMetaData(); ItemPayReportDto iprDto =new ItemPayReportDto() ; for (int i = 1; i <= rsMataData.getColumnCount(); i++) { if(rsMataData.getColumnName(i).equals("SQZF_ZCZFAMT")){ iprDto.setSqzf_zczfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ITEMNO")){ iprDto.setItemno( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SQZF_BFTKAMT")){ iprDto.setSqzf_bftkamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("YBZF_QETKAMT")){ iprDto.setYbzf_qetkamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SUM_SQZF")){ iprDto.setSum_sqzf( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SUM_YBZF")){ iprDto.setSum_ybzf( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SUM_ZJZF")){ iprDto.setSum_zjzf( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("YBZF_BFTKAMT")){ iprDto.setYbzf_bftkamt(rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ZJZF_ZFTZDFAMT")){ iprDto.setZjzf_zftzdfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ZJZF_BFTKAMT")){ iprDto.setZjzf_bftkamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ZJZF_QETKAMT")){ iprDto.setZjzf_qetkamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SQZF_ZFTZDFAMT")){ iprDto.setSqzf_zftzdfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("YBZF_ZCZFAMT")){ iprDto.setYbzf_zczfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ZJZF_ZCZFAMT")){ iprDto.setZjzf_zczfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("YBZF_ZFTZDFAMT")){ iprDto.setYbzf_zftzdfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ITEM_NAME")){ iprDto.setItem_name( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ID")){ iprDto.setId( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SQZF_QETKAMT")){ iprDto.setSqzf_qetkamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("YBZF_ZFTZJFAMT")){ iprDto.setYbzf_zftzjfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("ZJZF_ZFTZJFAMT")){ iprDto.setZjzf_zftzjfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SQZF_ZFTZJFAMT")){ iprDto.setSqzf_zftzjfamt( rs.getString(rsMataData.getColumnName(i))); } if(rsMataData.getColumnName(i).equals("SUM_AMT")){ iprDto.setSum_amt( rs.getString(rsMataData.getColumnName(i))); } } list.add(iprDto); } return list; } }); } }
5:DaoFactory
package com.tt.pwp.framework.data.dao; import com.tt.pwp.framework.datasource.DatasourceManager; import com.tt.pwp.framework.util.no.Sequence; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import javax.sql.DataSource; import org.springframework.beans.factory.InitializingBean; public class DaoFactory implements InitializingBean { private Map<String, Dao> daos = new ConcurrentHashMap(); private Dao defaultDao; private DatasourceManager datasourceManager; private MybatisConfigManager configManager; private DaoModelManager daoModelManager; private Sequence sequence; public DaoFactory() {} public Dao getDao() { return this.defaultDao; } public Dao getDao(String dataSourceId) { if (DatasourceManager.isDefaultDataSourceId(dataSourceId)) { return getDao(); } Dao dao = (Dao)this.daos.get(dataSourceId); if (dao == null) { DataSource dataSource = this.datasourceManager.getDataSource(dataSourceId); if (dataSource == null) { String message = String.format("datasource [%s] not found", new Object[] { dataSourceId }); throw new RuntimeException(message); } DaoImpl temp = new DaoImpl(dataSource); temp.setConfigManager(this.configManager); temp.setSequence(this.sequence); temp.setDaoModelManager(this.daoModelManager); dao = temp; this.daos.put(dataSourceId, dao); } return dao; } public void setConfigManager(MybatisConfigManager configManager) { this.configManager = configManager; } public void setDaoModelManager(DaoModelManager daoModelManager) { this.daoModelManager = daoModelManager; } public void setDatasourceManager(DatasourceManager datasourceManager) { this.datasourceManager = datasourceManager; } public void setSequence(Sequence sequence) { this.sequence = sequence; } public void setDefaultDao(Dao defaultDao) { this.defaultDao = defaultDao; } public void afterPropertiesSet() throws Exception { DataSource dataSource = this.datasourceManager.getDataSource(); DaoImpl defaultDao = new DaoImpl(dataSource); defaultDao.setConfigManager(this.configManager); defaultDao.setSequence(this.sequence); defaultDao.setDaoModelManager(this.daoModelManager); this.defaultDao = defaultDao; this.datasourceManager.setDao(defaultDao); } }
package com.tt.pwp.framework.datasource; import com.tt.pwp.framework.data.dao.Dao; import com.tt.pwp.framework.datasource.dialect.DialectFactory; import com.tt.pwp.framework.datasource.model.DataSourceInfo; import com.tt.pwp.framework.query.paging.PagingService; import com.tt.pwp.framework.util.VarPool; import java.sql.Connection; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import javax.annotation.Resource; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSource; import org.hibernate.dialect.Dialect; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.InitializingBean; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.jdbc.datasource.DataSourceUtils; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.util.Assert; import org.springframework.util.StringUtils; public class DatasourceManager implements InitializingBean { private static final String DEFAULT_ALIAS = "default_"; public static final String DEFAULT = "default"; public static final String DATASOURCE_KEY = "DM_DATASOURCE_KEY"; private static final Logger logger = LoggerFactory.getLogger(DatasourceManager.class); @Resource(name="dataSource") private DataSource defaultDatasource; private Dialect defaultDialect; private PagingService defaultPagingService; private com.tt.pwp.framework.datamng.db.DatabaseMetaData defaultDataMetaData; private DialectFactory dialectFactory; private Map<String, DataSource> datasources = new ConcurrentHashMap(); private Map<String, com.tt.pwp.framework.datamng.db.DatabaseMetaData> databaseMetaDatas = new ConcurrentHashMap(); private Map<String, PlatformTransactionManager> transactionManagers = new ConcurrentHashMap(); private Map<String, PagingService> pagingServices = new ConcurrentHashMap(); private Map<String, Dialect> datasourceDialects = new ConcurrentHashMap(); private Map<String, Long> datasourcesVersion = new HashMap(); private Map<String, String> dbTypes = new HashMap(); private VarPool varPool; private Dao dao; private PlatformTransactionManager transactionManager; private int maxactive = 10; @Autowired private List<PwpTransactionInfo> transactionInfos; public DatasourceManager() {} public DataSource getDataSource() { return this.defaultDatasource; } public DataSource getDataSource(String id) { if (isDefaultDataSourceId(id)) { return this.defaultDatasource; } check(id); return (DataSource)this.datasources.get(id); } public com.tt.pwp.framework.datamng.db.DatabaseMetaData getDatabaseMetaData() { return this.defaultDataMetaData; } public com.tt.pwp.framework.datamng.db.DatabaseMetaData getDatabaseMetaData(String id) { if (isDefaultDataSourceId(id)) { return this.defaultDataMetaData; } com.tt.pwp.framework.datamng.db.DatabaseMetaData databaseMetaData = (com.tt.pwp.framework.datamng.db.DatabaseMetaData)this.databaseMetaDatas.get(id); if (databaseMetaData == null) { DataSource dataSource = getDataSource(id); Assert.notNull(dataSource, String.format("dataSource %s must not be null", new Object[] { id })); databaseMetaData = new com.tt.pwp.framework.datamng.db.DatabaseMetaData(dataSource, id); this.databaseMetaDatas.put(id, databaseMetaData); } return databaseMetaData; } private void check(String id) { Long systemVersion = this.varPool.getVarAsLong("DM_DATASOURCE_KEY" + id); Long localVersion = (Long)this.datasourcesVersion.get(id); if (systemVersion.longValue() == -1L) { remove(id); throw new RuntimeException("数据源已经被删除"); } } private void remove(String id) { this.datasourcesVersion.remove(id); this.datasources.remove(id); this.pagingServices.remove(id); } public DataSource getDataSource(DataSourceInfo dataSourceInfo) { DataSource source = null; if (StringUtils.hasText(dataSourceInfo.getJndi())) { try { Context ctx = new InitialContext(); source = (DataSource)ctx.lookup(dataSourceInfo.getConnection_string()); } catch (NamingException e) { logger.error("读取" + dataSourceInfo + "数据源失败", e); } } else { BasicDataSource bds = new BasicDataSource(); bds.setDriverClassName(dataSourceInfo.getDriver_class_name()); bds.setUrl(dataSourceInfo.getConnection_string()); bds.setUsername(dataSourceInfo.getUser_name()); bds.setPassword(dataSourceInfo.getPassword()); bds.setMaxActive(this.maxactive); bds.setLogAbandoned(true); bds.setRemoveAbandoned(true); bds.setRemoveAbandonedTimeout(60); source = bds; } return source; } public Map<String, DataSource> getAllDataSource() { return this.datasources; } public Dialect getDialect(String datasourceId) { if (isDefaultDataSourceId(datasourceId)) { return this.defaultDialect; } Dialect dialect = (Dialect)this.datasourceDialects.get(datasourceId); if (dialect == null) { return this.dialectFactory.create((DataSource)this.datasources.get(datasourceId)); } return dialect; } public PlatformTransactionManager getTransactionManager() { return this.transactionManager; } public PlatformTransactionManager getTransactionManager(String datasourceId) { if (isDefaultDataSourceId(datasourceId)) { return this.transactionManager; } PlatformTransactionManager temp = (PlatformTransactionManager)this.transactionManagers.get(datasourceId); if (temp == null) { DataSource dataSource = getDataSource(datasourceId); Assert.notNull(dataSource, String.format("dataSource %s must not be null", new Object[] { datasourceId })); temp = new DataSourceTransactionManager(dataSource); this.transactionManagers.put(datasourceId, temp); } return temp; } public Dialect getDialect() { return this.defaultDialect; } public void setDefaultDatasource(DataSource defaultDatasource) { this.defaultDatasource = defaultDatasource; } public void setVarPool(VarPool varPool) { this.varPool = varPool; } public void setMaxactive(int maxactive) { this.maxactive = maxactive; } public void setTransactionManager(PlatformTransactionManager transactionManager) { this.transactionManager = transactionManager; } public void setDao(Dao dao) { this.dao = dao; } public void setDialectFactory(DialectFactory dialectFactory) { this.dialectFactory = dialectFactory; } public void afterPropertiesSet() throws Exception { this.defaultDataMetaData = new com.tt.pwp.framework.datamng.db.DatabaseMetaData(); this.defaultDataMetaData.setDataSourceId("default_"); this.defaultDataMetaData.setDataSource(this.defaultDatasource); this.defaultDialect = this.dialectFactory.create(this.defaultDatasource); this.defaultPagingService = new PagingService(); this.defaultPagingService.setDialect(this.defaultDialect); this.defaultPagingService.setDataSource(this.defaultDatasource); for (PwpTransactionInfo transactionInfo : this.transactionInfos) { Assert.notNull(transactionInfo.getId(), "bean{PwpTransactionInfo}配置id不能为空"); Assert.notNull(transactionInfo.getDataSource(), "bean{PwpTransactionInfo}配置dataSource不能为空"); String id = transactionInfo.getId(); if (!this.datasources.containsKey(id)) { this.datasources.put(id, transactionInfo.getDataSource()); this.datasourcesVersion.put(id, Long.valueOf(System.currentTimeMillis())); Dialect dialect = null; if (transactionInfo.getDialect() != null) { dialect = transactionInfo.getDialect(); } else { dialect = this.dialectFactory.create(transactionInfo.getDataSource()); } PagingService pagingService = new PagingService(); pagingService.setDataSource(transactionInfo.getDataSource()); pagingService.setDialect(dialect); this.pagingServices.put(id, pagingService); if ((dialect != null) && (!this.datasourceDialects.containsKey(id))) { this.datasourceDialects.put(id, dialect); } String dbType = this.dialectFactory.getDbtype(transactionInfo.getDataSource()); if ((dbType != null) && (!this.dbTypes.containsKey(id))) { this.dbTypes.put(id, dbType); } } else { throw new RuntimeException(String.format("数据源ID（%s）已存在", new Object[] { id })); } if ((transactionInfo.getTransactionManager() != null) && (!this.transactionManagers.containsKey(id))) { this.transactionManagers.put(id, transactionInfo.getTransactionManager()); } } } public String getDatasourceProductName(DataSource dataSource) { Connection conn = null; String dbProductName = null; try { conn = DataSourceUtils.getConnection(dataSource); java.sql.DatabaseMetaData dmd = conn.getMetaData(); dbProductName = dmd.getDatabaseProductName().toLowerCase(); } catch (SQLException e) { throw new RuntimeException(e); } finally { DataSourceUtils.releaseConnection(conn, dataSource); } return dbProductName; } public static boolean isDefaultDataSourceId(String dataSourceId) { return (StringUtils.isEmpty(dataSourceId)) || ("default".equals(dataSourceId)) || ("default_".equals(dataSourceId)); } public PagingService getPagingService() { return this.defaultPagingService; } public PagingService getPagingService(String datasourceId) { if (isDefaultDataSourceId(datasourceId)) { return this.defaultPagingService; } check(datasourceId); return (PagingService)this.pagingServices.get(datasourceId); } public String getDbTypeById(String datasourceId) { if (this.dbTypes.containsKey(datasourceId)) { return (String)this.dbTypes.get(datasourceId); } return null; } }
package com.tt.pwp.framework.util.no; import java.util.List; public abstract interface Sequence { public abstract boolean isExist(String paramString); public abstract void create(NoDefinition paramNoDefinition); public abstract void update(NoDefinition paramNoDefinition); public abstract void delete(String paramString); public abstract void resetNo(String paramString); public abstract void resetNoTo(String paramString, long paramLong); public abstract String generate(String paramString); public abstract List batchGenerate(String paramString, int paramInt); public abstract List<String> generateIdList(String paramString, int paramInt); }
package com.tt.pwp.framework.util; public abstract interface VarPool { public abstract String getVar(String paramString); public abstract int getVarAsInt(String paramString); public abstract void setVar(String paramString1, String paramString2); public abstract void setVar(String paramString, int paramInt); public abstract void setVar(String paramString, Long paramLong); public abstract Long getVarAsLong(String paramString); public abstract void incVar(String paramString); public abstract void clear(); }
package com.tt.pwp.framework.util; import java.util.Collections; import java.util.HashMap; import java.util.Map; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.DataAccessException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.support.JdbcDaoSupport; public class DBVarPool extends JdbcDaoSupport implements VarPool { private static final Logger logger = LoggerFactory.getLogger(DBVarPool.class); private static final String UPDATE_LONG = "update pwp_varpool set long_value=? where name = ?"; private static final String UPDATE_INC_LONG = "update pwp_varpool set long_value=long_value+1 where name = ?"; private static final String UPDATE_STRING = "update pwp_varpool set string_value=? where name = ?"; private static final String INSERT_LONG = "insert into pwp_varpool(name,long_value) values(?,?)"; private static final String INSERT_STRING = "insert into pwp_varpool(name,string_value) values(?,?)"; private static final String GET_LONG = "select long_value from pwp_varpool where name=?"; private static final String GET_STRING = "select string_value from pwp_varpool where name=?"; public DBVarPool() {} private Map<String, Long> checkTimeCache = Collections.synchronizedMap(new HashMap()); private Map<String, String> stringCache = Collections.synchronizedMap(new HashMap()); private Map<String, Long> longCache = Collections.synchronizedMap(new HashMap()); private Map<String, Integer> intCache = Collections.synchronizedMap(new HashMap()); private int delay = 10; public boolean isCacheInvalid(String name) { Long lastRead = (Long)this.checkTimeCache.get(name); if ((lastRead == null) || (System.currentTimeMillis() - lastRead.longValue() > this.delay * 1000)) { return true; } return false; } public String getVar(String name) { if (isCacheInvalid(name)) { String value = null; try { value = (String)getJdbcTemplate().queryForObject("select string_value from pwp_varpool where name=?", String.class, new Object[] { name }); } catch (EmptyResultDataAccessException e) { logger.debug("DB varpool不存在{}变量", name); } this.stringCache.put(name, value); this.checkTimeCache.put(name, Long.valueOf(System.currentTimeMillis())); return value; } return (String)this.stringCache.get(name); } public int getVarAsInt(String name) { Integer value = Integer.valueOf(0); if (isCacheInvalid(name)) { try { value = (Integer)getJdbcTemplate().queryForObject("select long_value from pwp_varpool where name=?", Integer.class, new Object[] { name }); if (value == null) { value = Integer.valueOf(0); } } catch (EmptyResultDataAccessException e) { logger.debug("DB varpool不存在{}变量", name); } this.intCache.put(name, value); this.checkTimeCache.put(name, Long.valueOf(System.currentTimeMillis())); return value.intValue(); } return ((Integer)this.intCache.get(name)).intValue(); } public Long getVarAsLong(String name) { Long value = Long.valueOf(0L); if (isCacheInvalid(name)) { try { value = (Long)getJdbcTemplate().queryForObject("select long_value from pwp_varpool where name=?", Long.class, new Object[] { name }); if (value == null) { value = Long.valueOf(0L); } } catch (EmptyResultDataAccessException e) { logger.debug("DB varpool不存在{}变量", name); } this.longCache.put(name, value); this.checkTimeCache.put(name, Long.valueOf(System.currentTimeMillis())); return value; } return (Long)this.longCache.get(name); } public void setVar(String name, String value) { int count = getJdbcTemplate().update("update pwp_varpool set string_value=? where name = ?", new Object[] { value, name }); if (count == 0) { try { getJdbcTemplate().update("insert into pwp_varpool(name,string_value) values(?,?)", new Object[] { name, value }); } catch (DataAccessException e) { setVar(name, value); } } this.checkTimeCache.put(name, Long.valueOf(System.currentTimeMillis())); this.stringCache.put(name, value); } public void setVar(String name, int value) { int count = getJdbcTemplate().update("update pwp_varpool set long_value=? where name = ?", new Object[] { Long.valueOf(value), name }); if (count == 0) { try { getJdbcTemplate().update("insert into pwp_varpool(name,long_value) values(?,?)", new Object[] { name, Long.valueOf(value) }); } catch (DataAccessException e) { setVar(name, value); } } this.checkTimeCache.put(name, Long.valueOf(System.currentTimeMillis())); this.intCache.put(name, Integer.valueOf(value)); } public void setVar(String name, Long value) { int count = getJdbcTemplate().update("update pwp_varpool set long_value=? where name = ?", new Object[] { value, name }); if (count == 0) { try { getJdbcTemplate().update("insert into pwp_varpool(name,long_value) values(?,?)", new Object[] { name, value }); } catch (DataAccessException e) { setVar(name, value); } } this.checkTimeCache.put(name, Long.valueOf(System.currentTimeMillis())); this.longCache.put(name, value); } public void incVar(String name) { try { int count = getJdbcTemplate().update("update pwp_varpool set long_value=long_value+1 where name = ?", new Object[] { name }); if (count == 0) { try { getJdbcTemplate().update("insert into pwp_varpool(name,long_value) values(?,?)", new Object[] { name, Long.valueOf(0L) }); } catch (DataAccessException e) { incVar(name); } } this.checkTimeCache.remove(name); } catch (DataAccessException e) { setVar(name, Long.valueOf(0L)); incVar(name); } } public void clear() { int update = getJdbcTemplate().update("delete from pwp_varpool"); logger.info("清空DB变量池{}个变量", Integer.valueOf(update)); } public void setDelay(int delay) { this.delay = delay; } public int getDelay() { return this.delay; } }
package com.tt.pwp.framework.util; import java.util.Map; public class DefaultVarPool implements VarPool { public DefaultVarPool() {} private Map<String, Object> variables = java.util.Collections.synchronizedMap(new java.util.HashMap()); public String getVar(String name) { return (String)this.variables.get(name); } public int getVarAsInt(String name) { Integer i = (Integer)this.variables.get(name); if (i == null) { return 0; } return i.intValue(); } public void setVar(String name, String value) { this.variables.put(name, value); } public void setVar(String name, int value) { this.variables.put(name, Integer.valueOf(value)); } public void incVar(String name) { synchronized (this) { Object object = this.variables.get(name); if (object == null) { object = Integer.valueOf(0); } if ((object instanceof Integer)) { int i = ((Integer)object).intValue(); i++; this.variables.put(name, Integer.valueOf(i)); } else if ((object instanceof Long)) { long i = ((Long)object).longValue(); i += 1L; this.variables.put(name, Long.valueOf(i)); } else { throw new RuntimeException("变量" + name + "不是整数"); } } } public void setVar(String name, Long value) { this.variables.put(name, value); } public Long getVarAsLong(String name) { Long i = null; Object value = this.variables.get(name); if ((value instanceof Integer)) { i = new Long(((Integer)value).intValue()); this.variables.put(name, i); } else { i = (Long)value; } if (i == null) { return new Long(0L); } return Long.valueOf(i.longValue()); } public void clear() {} }
package com.tt.pwp.framework.query.paging; import com.tt.pwp.framework.datamng.domain.ReplaceName; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.hibernate.dialect.Dialect; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.util.Assert; public class PagingService extends JdbcDaoSupport { private static final Logger logger = LoggerFactory.getLogger(PagingService.class); private Dialect dialect; private RowMapper<Map<String, Object>> rowMapper; private ReplaceName nameConvertor = ReplaceName.NULL; private int rowNumEachSection = 1000; public PagingService() {} public ReplaceName getNameConvertor() { return this.nameConvertor; } public void setNameConvertor(ReplaceName nameConvertor) { this.nameConvertor = nameConvertor; } public int getRowNumEachSection() { return this.rowNumEachSection; } public void setRowNumEachSection(int rowNumEachSection) { this.rowNumEachSection = rowNumEachSection; } public Dialect getDialect() { return this.dialect; } public void setDialect(Dialect dialect) { this.dialect = dialect; } public void query(String sqlStr, List<Object> params, int offset, int pagesize, ResultSetHandler resultsetHandler) { query(sqlStr, params, offset, pagesize, resultsetHandler, true); } public void query(String sqlStr, List<Object> params, int offset, int pagesize, ResultSetHandler resultsetHandler, boolean optimizeFirstPage) { if (this.dialect.supportsLimitOffset()) { querySupportLimitOffset(sqlStr, params, offset, pagesize, resultsetHandler, optimizeFirstPage); } else { queryNotSupportLimitOffset(sqlStr, params, offset, pagesize, resultsetHandler); } logger.info("\n executeQuery sql complete"); } public void queryForSection(String sqlStr, List<Object> params, int offset, int pagesize, SectionHandler<?> sectionHandler) { queryForSection(sqlStr, params, offset, pagesize, sectionHandler, true); } public void queryForSection(String sqlStr, List<Object> params, int offset, int pagesize, SectionHandler<?> sectionHandler, boolean optimizeFirstPage) { ResultSetHandler resultsetHandler = new SectionHandlerBridge(sectionHandler, this.rowNumEachSection, this.rowMapper); query(sqlStr, params, offset, pagesize, resultsetHandler, optimizeFirstPage); } public String genSqlWithPaging(String sql, List<Object> parameters, int offset, int pagesize) { return genSqlWithPaging(sql, parameters, offset, pagesize, true); } public String genSqlWithPaging(String sql, List<Object> parameters1, int offset, int pagesize, boolean optimizeFirstPage) { String limitsql = getLimitSql(sql, offset, pagesize, optimizeFirstPage); List<Object> pagingParameters = getPagingParameters(offset, pagesize, optimizeFirstPage); addPagingParameters(parameters1, pagingParameters); return limitsql; } protected void querySupportLimitOffset(String sqlStr, List<Object> params, int offset, int pagesize, final ResultSetHandler resultsetHandler, boolean optimizeFirstPage) { JdbcTemplate template = getJdbcTemplate(); sqlStr = genSqlWithPaging(sqlStr, params, offset, pagesize, optimizeFirstPage); final String sqlInfo = sqlStr + ":" + params; long start = System.currentTimeMillis(); logger.info("\n executeQuery sql start:" + sqlInfo); template.query(sqlStr, params.toArray(), new ResultSetExtractor() { public Object extractData(ResultSet rs) throws SQLException, DataAccessException { if (resultsetHandler != null) { resultsetHandler.onResultSetCreate(rs, sqlInfo); } while (rs.next()) { resultsetHandler.processRow(rs); } if (resultsetHandler != null) { resultsetHandler.afterResultSetProcess(sqlInfo); } return null; } }); long end = System.currentTimeMillis(); logger.info("\n executeQuery sql end in {} milliseconds:{}", Long.valueOf(end - start), sqlInfo); } protected List<Object> getPagingParameters(int offset, int pagesize, boolean optimizeFirstPage) { List<Object> pagingParameters = new ArrayList(2); if (this.dialect.bindLimitParametersInReverseOrder()) { if (this.dialect.useMaxForLimit()) { pagingParameters.add(new Integer(pagesize + offset)); } else { pagingParameters.add(new Integer(pagesize)); } if ((offset != 0) || (!optimizeFirstPage)) { pagingParameters.add(new Integer(this.dialect.convertToFirstRowValue(offset))); } } else { if ((offset != 0) || (!optimizeFirstPage)) { pagingParameters.add(new Integer(this.dialect.convertToFirstRowValue(offset))); } if (this.dialect.useMaxForLimit()) { pagingParameters.add(new Integer(pagesize + offset)); } else { pagingParameters.add(new Integer(pagesize)); } } return pagingParameters; } private String getLimitSql(String sql, int offset, int pagesize, boolean optimizeFirstPage) { if (optimizeFirstPage) { return this.dialect.getLimitString(sql, offset, pagesize); } return this.dialect.getLimitString(sql, 1, pagesize); } private void addPagingParameters(List<Object> parameters1, List<Object> pagingParameters) { if (this.dialect.bindLimitParametersFirst()) { parameters1.addAll(0, pagingParameters); } else { parameters1.addAll(pagingParameters); } } protected void queryNotSupportLimitOffset(String sqlStr, List<Object> params, final int offset, final int pagesize, final ResultSetHandler resultsetHandler) { JdbcTemplate template = getJdbcTemplate(); final String sqlInfo = sqlStr + ":" + params; long start = System.currentTimeMillis(); logger.info("\n executeQuery sql start:{}", sqlInfo); template.query(sqlStr, params.toArray(), new ResultSetExtractor() { public Object extractData(ResultSet rs) throws SQLException, DataAccessException { if (resultsetHandler != null) { resultsetHandler.onResultSetCreate(rs, sqlInfo); } long count = -1L; while (rs.next()) { count += 1L; if (count >= offset) { if (count >= offset + pagesize) { break; } resultsetHandler.processRow(rs); } } if (resultsetHandler != null) { resultsetHandler.afterResultSetProcess(sqlInfo); } return null; } }); long end = System.currentTimeMillis(); logger.info("\n executeQuery sql successful in {} milliseconds:{}", Long.valueOf(end - start), sqlInfo); } protected void initDao() throws Exception { super.initDao(); Assert.notNull(this.dialect); Assert.notNull(this.nameConvertor); this.rowMapper = createRowMapper(); } protected RowMapper<Map<String, Object>> createRowMapper() { return new DBDataExtendsColumnMap(this.nameConvertor); } } /* Location: F:\decoument\maven\repository\com\tt\pwp\pwp-framework\3.1.0.20171114.11.release\pwp-framework-3.1.0.20171114.11.release.jar * Qualified Name: com.tt.pwp.framework.query.paging.PagingService * Java Class Version: 7 (51.0) * JD-Core Version: 0.7.0.1 */
package com.tt.pwp.framework.datamng.domain; public abstract interface ReplaceName { public static final ReplaceName NULL = new ReplaceName() { public String replace(String name) { return name; } public String unReplace(String name) { return name; } }; public abstract String replace(String paramString); public abstract String unReplace(String paramString); }
package com.tt.pwp.framework.datasource.model; import com.tt.pwp.framework.data.model.ColumntTitle; import com.tt.pwp.framework.data.model.DefaultDTO; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; import org.hibernate.annotations.GenericGenerator; @Entity @Table(name="PWP_DATASOURCES") public class DataSourceInfo extends DefaultDTO { private static final long serialVersionUID = -7200905834417309264L; @Id @GeneratedValue(generator="sequence") @GenericGenerator(strategy="no", name="sequence") @Column(name="DATASOURCE_ID") @ColumntTitle("ID") private String datasource_id; @Column(name="CONNECTION_STRING") @ColumntTitle("连接字符串") private String connection_string; @Column(name="USER_NAME") @ColumntTitle("用户名") private String user_name; @Column(name="DESCRIPTION") @ColumntTitle("描述") private String description; @Column(name="DATABASE_TYPE") @ColumntTitle("数据库类型") private String database_type; @Column(name="DRIVER_CLASS_NAME") @ColumntTitle("驱动") private String driver_class_name; @Column(name="PARAMETERS") @ColumntTitle("额外参数") private String parameters; @Column(name="PASSWORD") @ColumntTitle("密码") private String password; @Column(name="JNDI") @ColumntTitle("JNDI连接字符串") private String jndi; @Column(name="DATASOURCE_NAME") @ColumntTitle("数据库名称") private String datasource_name; public DataSourceInfo() {} public String getDatasource_id() { return this.datasource_id; } public void setDatasource_id(String datasource_id) { this.datasource_id = datasource_id; } public String getConnection_string() { return this.connection_string; } public void setConnection_string(String connection_string) { this.connection_string = connection_string; } public String getUser_name() { return this.user_name; } public void setUser_name(String user_name) { this.user_name = user_name; } public String getDescription() { return this.description; } public void setDescription(String description) { this.description = description; } public String getDatabase_type() { return this.database_type; } public void setDatabase_type(String database_type) { this.database_type = database_type; } public String getDriver_class_name() { return this.driver_class_name; } public void setDriver_class_name(String driver_class_name) { this.driver_class_name = driver_class_name; } public String getParameters() { return this.parameters; } public void setParameters(String parameters) { this.parameters = parameters; } public String getPassword() { return this.password; } public void setPassword(String password) { this.password = password; } public String getJndi() { return this.jndi; } public void setJndi(String jndi) { this.jndi = jndi; } public String getDatasource_name() { return this.datasource_name; } public void setDatasource_name(String datasource_name) { this.datasource_name = datasource_name; } public int hashCode() { int prime = 31; int result = 1; result = 31 * result + (this.datasource_id == null ? 0 : this.datasource_id.hashCode()); return result; } public boolean equals(Object obj) { if (obj == null) { return false; } if (getClass() != obj.getClass()) { return false; } if (!(obj instanceof DataSourceInfo)) { return false; } DataSourceInfo other = (DataSourceInfo)obj; Object otherDatasource_id = other.getDatasource_id(); if (this.datasource_id == null) { if (otherDatasource_id != null) { return false; } } else if (!this.datasource_id.equals(otherDatasource_id)) { return false; } return true; } }
package com.tt.pwp.framework.data.model; import com.tt.pwp.framework.util.JsonUtils; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class DefaultDTO implements DTO { private static final long serialVersionUID = 1L; private UpdateFlag updateFlag = UpdateFlag.Unchanged; private List<DTO> details; private Map<String, String> properties = new HashMap(); private List<ForeignKeyMapping> foreignKeys; private Map<String, Object> cascadeNodes; private Map<String, ForeignKeyMapping> cascadeKeyMaps; public DefaultDTO() {} public UpdateFlag getUpdateFlag() { return this.updateFlag; } public void setUpdateFlag(UpdateFlag updateFlag) { this.updateFlag = updateFlag; } public <T extends DTO> List<T> getDetails() { return this.details; } public <T extends DTO> void setDetails(List<T> details) { this.details = details; } public String getProperty(String name) { return (String)this.properties.get(name); } public void setProperty(String name, String value) { this.properties.put(name, value); } public void putAllProperty(Map<String, String> properties) { this.properties.putAll(properties); } public Map<String, String> getAllProperty() { return this.properties; } public List<ForeignKeyMapping> getForeignKeys() { return this.foreignKeys; } public void setForeignKeys(List<ForeignKeyMapping> foreignKeys) { this.foreignKeys = foreignKeys; } public Map<String, String> getProperties() { return this.properties; } public void setProperties(Map<String, String> properties) { this.properties = properties; } public <T extends DTO> List<T> getDetailsByType(Class<T> clazz) { return getDetailsByType(clazz.getName()); } public <T extends DTO> List<T> getDetailsByType(String modelId) { List<T> list = new ArrayList(); if (this.details != null) { int i = 0; for (int size = this.details.size(); i < size; i++) { DTO dto = (DTO)this.details.get(i); if (dto.getClass().getName().equals(modelId)) { list.add(dto); } } } return list; } public String toJSON(String s) { return JsonUtils.fromObject(this); } public Map<String, Object> getCascadeNodes() { return this.cascadeNodes; } public void setCascadeNodes(Map<String, Object> cascadeNodes) { this.cascadeNodes = cascadeNodes; } public Map<String, ForeignKeyMapping> getCascadeKeyMaps() { return this.cascadeKeyMaps; } public void setCascadeKeyMaps(Map<String, ForeignKeyMapping> cascadeKeyMaps) { this.cascadeKeyMaps = cascadeKeyMaps; } }
package com.tt.pwp.framework.util; import java.math.BigDecimal; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import net.sf.json.JSONArray; import net.sf.json.JsonConfig; import net.sf.json.processors.JsonValueProcessor; import net.sf.json.util.CycleDetectionStrategy; import net.sf.json.util.PropertyFilter; import org.apache.commons.lang.StringUtils; import org.mozilla.javascript.ConsString; public final class JsonUtils { private static final JsonConfig jsonConfig = new JsonConfig(); private static String regx = "^((-?\\d+.?\\d*)[Ee]{1}(-?\\d+))$"; private static Pattern pattern = Pattern.compile(regx); public static final JsonValueProcessor DATE = new JsonValueProcessor() { private static final String format = "yyyy-MM-dd HH:mm:ss"; private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); public Object processArrayValue(Object value, JsonConfig jsonConfig) { return process(value); } public Object processObjectValue(String key, Object value, JsonConfig jsonConfig) { return process(value); } private Object process(Object value) { if (value == null) return ""; if ((value instanceof java.util.Date)) { return this.sdf.format((java.util.Date)value); } return value.toString(); } }; public static final JsonValueProcessor TIMESTAMP = new JsonValueProcessor() { public Object processArrayValue(Object value, JsonConfig jsonConfig) { return process(value); } public Object processObjectValue(String key, Object value, JsonConfig jsonConfig) { return process(value); } private Object process(Object value) { if (value == null) return ""; if ((value instanceof Timestamp)) { return Long.valueOf(((Timestamp)value).getTime()); } return value.toString(); } }; public static final JsonValueProcessor CONSSTRING = new JsonValueProcessor() { public Object processArrayValue(Object value, JsonConfig jsonConfig) { return process(value); } public Object processObjectValue(String key, Object value, JsonConfig jsonConfig) { return process(value); } private Object process(Object value) { return value.toString(); } }; public static final JsonValueProcessor NUMBER = new JsonValueProcessor() { public Object processArrayValue(Object value, JsonConfig jsonConfig) { return process(value); } public Object processObjectValue(String key, Object value, JsonConfig jsonConfig) { return process(value); } private Object process(Object value) { if (value == null) { return ""; } if (JsonUtils.isENum(value.toString())) { BigDecimal db = new BigDecimal(value.toString()); String ii = db.toPlainString(); return ii; } return value; } }; public static boolean isENum(String input) { return pattern.matcher(input).matches(); } static { jsonConfig.setJsonPropertyFilter(new PropertyFilter() { public boolean apply(Object source, String name, Object value) { return value == null; } }); jsonConfig.setCycleDetectionStrategy(CycleDetectionStrategy.LENIENT); jsonConfig.registerJsonValueProcessor(java.util.Date.class, DATE); jsonConfig.registerJsonValueProcessor(java.sql.Date.class, DATE); jsonConfig.registerJsonValueProcessor(Timestamp.class, TIMESTAMP); jsonConfig.registerJsonValueProcessor(ConsString.class, CONSSTRING); jsonConfig.registerJsonValueProcessor(Integer.class, NUMBER); jsonConfig.registerJsonValueProcessor(Long.class, NUMBER); jsonConfig.registerJsonValueProcessor(Float.class, NUMBER); jsonConfig.registerJsonValueProcessor(Double.class, NUMBER); } public static String fromObject(Object object) { return net.sf.json.JSONObject.fromObject(object, jsonConfig).toString(); } public static String fromObject(Collection<?> object) { return JSONArray.fromObject(object, jsonConfig).toString(); } public static String fromObject(Object[] object) { return JSONArray.fromObject(object, jsonConfig).toString(); } public static <T> T toBean(String json, Class<T> javaBean) { net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(json); return net.sf.json.JSONObject.toBean(jsonObj, javaBean); } public static <T> T toBean(String json, Class<T> javaBean, Map<String, Class<?>> propertys) { net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(json); return net.sf.json.JSONObject.toBean(jsonObj, javaBean, propertys); } public static <T> T toBean(String json, Class<T> javaBean, JsonConfig jc, Map<String, Class<?>> propertys) { net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(json, jc); return net.sf.json.JSONObject.toBean(jsonObj, javaBean, propertys); } public static <T> T toBean(String json, Class<T> javaBean, JsonConfig jc) { net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(json, jc); return net.sf.json.JSONObject.toBean(jsonObj, javaBean); } public static <T> T toBean(String json, JsonConfig jc) { net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(json, jc); return net.sf.json.JSONObject.toBean(jsonObj, jc); } public static <T> T[] toArray(String json, Class<T> javaBean) { JSONArray jsonArray = JSONArray.fromObject(json); return (Object[])JSONArray.toArray(jsonArray, javaBean); } public static <T> T[] toArray(String json, Class<T> javaBean, Map<String, Class<?>> propertys) { JSONArray jsonArray = JSONArray.fromObject(json); return (Object[])JSONArray.toArray(jsonArray, javaBean, propertys); } public static <T> Map<String, T> toMap(String json) { Map<String, T> map = new HashMap(); if (StringUtils.isBlank(json)) { return map; } Object obj = com.alibaba.fastjson.JSONObject.parseObject(json); net.sf.json.JSONObject jsonObject = net.sf.json.JSONObject.fromObject(obj); Iterator<Object> iter = jsonObject.keys(); while (iter.hasNext()) { String key = (String)iter.next(); T val = jsonObject.get(key); if ((val instanceof net.sf.json.JSONObject)) { net.sf.json.JSONObject temp = (net.sf.json.JSONObject)val; if (temp.containsKey("class_")) { try { T bean = net.sf.json.JSONObject.toBean(temp, Class.forName(temp.getString("class_"))); map.put(key, bean); } catch (ClassNotFoundException e) {} } } map.put(key, val); } return map; } public static Collection toCollection(String json, Class<?> valueClz) { JSONArray jsonArray = JSONArray.fromObject(json); return JSONArray.toCollection(jsonArray, valueClz); } private JsonUtils() {} }
package com.tt.pwp.framework.data.model; import java.lang.annotation.Annotation; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Target({java.lang.annotation.ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) public @interface ColumntTitle { String value() default ""; }
package com.tt.pwp.framework.datasource.dialect; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.SQLException; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import javax.sql.DataSource; import org.apache.commons.lang3.StringUtils; import org.hibernate.dialect.DB2Dialect; import org.hibernate.dialect.Dialect; import org.hibernate.dialect.H2Dialect; import org.hibernate.dialect.HSQLDialect; import org.springframework.beans.factory.InitializingBean; import org.springframework.jdbc.datasource.DataSourceUtils; public class DialectFactory implements InitializingBean { private Map<String, Dialect> dialects = new ConcurrentHashMap(); public DialectFactory() {} public Dialect create(DataSource dataSource) { Connection conn = null; String dbProductName = null; try { conn = DataSourceUtils.getConnection(dataSource); DatabaseMetaData dmd = conn.getMetaData(); dbProductName = dmd.getDatabaseProductName().toLowerCase(); } catch (SQLException e) { throw new RuntimeException(e); } finally { DataSourceUtils.releaseConnection(conn, dataSource); } return create(dbProductName); } public Dialect create(String dbType) { if (StringUtils.isEmpty(dbType)) { return null; } return (Dialect)this.dialects.get(dbType.toLowerCase()); } public String getDbtype(DataSource dataSource) { Connection conn = null; String dbProductName = null; try { conn = DataSourceUtils.getConnection(dataSource); DatabaseMetaData dmd = conn.getMetaData(); dbProductName = dmd.getDatabaseProductName().toLowerCase(); } catch (SQLException e) { throw new RuntimeException(e); } finally { DataSourceUtils.releaseConnection(conn, dataSource); } return dbProductName; } public void afterPropertiesSet() throws Exception { this.dialects.put("mysql", new PwpMySQL5Dialect()); this.dialects.put("oracle", new PwpOracle10gDialect()); this.dialects.put("db2", new DB2Dialect()); this.dialects.put("h2", new H2Dialect()); this.dialects.put("hsql", new HSQLDialect()); this.dialects.put("sqlserver", new PwpSQLServerDialect()); this.dialects.put("microsoft sql server", new PwpSQLServerDialect()); } }
package com.tt.pwp.framework.data.dao; import com.tt.pwp.framework.data.model.DTO; import java.io.Serializable; import java.util.List; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.core.RowMapper; public abstract interface Dao { public abstract <T extends DTO> T findById(String paramString, Serializable paramSerializable) throws DataAccessException; public abstract <T extends DTO> T findById(Class<T> paramClass, Serializable paramSerializable) throws DataAccessException; public abstract <T extends DTO> T findByIdWithExtInfo(String paramString, Serializable paramSerializable); public abstract <T extends DTO> T find(String paramString1, String paramString2, List<Object> paramList) throws DataAccessException; public abstract <T extends DTO> T find(String paramString1, String paramString2, Object... paramVarArgs); public abstract <T> List<T> findAll(String paramString); public abstract <T> List<T> findAll(String paramString1, String paramString2, List<Object> paramList); public abstract <T> List<T> findAll(String paramString1, String paramString2, Object... paramVarArgs); public abstract int count(String paramString); public abstract int count(String paramString1, String paramString2, List<Object> paramList); public abstract void query(String paramString, RowCallbackHandler paramRowCallbackHandler); public abstract void query(String paramString1, String paramString2, List<Object> paramList, RowCallbackHandler paramRowCallbackHandler); public abstract <T extends DTO> void query(String paramString, BeanCallbackHandler<T> paramBeanCallbackHandler); public abstract <T extends DTO> void query(String paramString1, String paramString2, List<Object> paramList, BeanCallbackHandler<T> paramBeanCallbackHandler); public abstract boolean isExists(String paramString, Serializable paramSerializable); public abstract <T extends DTO> T insert(T paramT); public abstract <T extends DTO> T insertSelective(T paramT); public abstract int removeById(String paramString, Serializable paramSerializable); public abstract int removeByIds(String paramString, Serializable... paramVarArgs); public abstract int removeByIds(String paramString, List<? extends Serializable> paramList); public abstract int remove(Object paramObject); public abstract int remove(String paramString1, String paramString2, List<Object> paramList); public abstract int remove(String paramString1, String paramString2, Object... paramVarArgs); public abstract <T extends DTO> int remove(List<T> paramList); public abstract int update(Object paramObject); public abstract int updateSelective(Object paramObject); public abstract DTO handle(DTO paramDTO); public abstract DTO handleSelective(DTO paramDTO); public abstract <T extends DTO> List<T> handle(List<T> paramList); public abstract <T extends DTO> List<T> handleSelective(List<T> paramList); public abstract <T extends DTO> T handle(T paramT, DaoEventListener<T> paramDaoEventListener); public abstract <T extends DTO> T handleSelective(T paramT, DaoEventListener<T> paramDaoEventListener); public abstract <T extends DTO> List<T> handle(List<T> paramList, DaoEventListener<T> paramDaoEventListener); public abstract <T extends DTO> List<T> handleSelective(List<T> paramList, DaoEventListener<T> paramDaoEventListener); public abstract <T extends DTO> RowMapper<T> getRowMapper(String paramString); public abstract JdbcTemplate getJdbcTemplate(); public abstract <T extends DTO> List<T> loadDetails(DTO paramDTO, String paramString1, String paramString2); public abstract <T extends DTO> List<T> loadDetails(DTO paramDTO, Class<T> paramClass, String paramString); public abstract boolean deleteTreeDetails(String paramString1, String paramString2, Serializable paramSerializable); public abstract <T extends DTO> boolean deleteTreeDetails(Class<T> paramClass, String paramString, Serializable paramSerializable); public abstract <T extends DTO> List<T> handleSelectiveMorphDyna(List<T> paramList); public abstract DTO handleSelectiveMorphDyna(DTO paramDTO); public abstract <T extends DTO> List<T> batchInsert(List<T> paramList); public abstract <T extends DTO> int batchUpdate(List<T> paramList); public abstract <T extends DTO> int batchRemove(List<T> paramList); public abstract <T extends DTO> List<T> batchHandle(List<T> paramList); public abstract Object handleCascadeModel(Object paramObject); } /* Location: F:\decoument\maven\repository\com\tt\pwp\pwp-framework\3.1.0.20171114.11.release\pwp-framework-3.1.0.20171114.11.release.jar * Qualified Name: com.tt.pwp.framework.data.dao.Dao * Java Class Version: 7 (51.0) * JD-Core Version: 0.7.0.1 */
package com.tt.pwp.framework.data.model; import java.io.Serializable; import java.util.List; import java.util.Map; public abstract interface DTO extends Serializable { public abstract UpdateFlag getUpdateFlag(); public abstract void setUpdateFlag(UpdateFlag paramUpdateFlag); public abstract <T extends DTO> List<T> getDetails(); public abstract <T extends DTO> void setDetails(List<T> paramList); public abstract String getProperty(String paramString); public abstract void setProperty(String paramString1, String paramString2); public abstract List<ForeignKeyMapping> getForeignKeys(); public abstract void putAllProperty(Map<String, String> paramMap); public abstract Map<String, String> getAllProperty(); public abstract Map<String, Object> getCascadeNodes(); public abstract void setCascadeNodes(Map<String, Object> paramMap); public abstract Map<String, ForeignKeyMapping> getCascadeKeyMaps(); public abstract void setCascadeKeyMaps(Map<String, ForeignKeyMapping> paramMap); } /* Location: F:\decoument\maven\repository\com\tt\pwp\pwp-framework\3.1.0.20171114.11.release\pwp-framework-3.1.0.20171114.11.release.jar * Qualified Name: com.tt.pwp.framework.data.model.DTO * Java Class Version: 7 (51.0) * JD-Core Version: 0.7.0.1 */
五：表结构及存储过程
CREATE OR REPLACE PACKAGE PKG_QUERY IS -- Author : ADMINISTRATOR -- Created : 2016/12/8 星期四 10:28:37 -- Purpose : 用做查询游标 -- Public type declarations TYPE CUR_QUERY IS REF CURSOR; END PKG_QUERY;
drop table gk_itempay_temp; ----创建经济分类报表临时表 create GLOBAL TEMPORARY table gk_itempay_temp( id number(20) , itemno varchar2(10), zjzf_zczfamt number(16,2) default 0 , zjzf_qetkamt number(16,2) default 0 , zjzf_bftkamt number(16,2) default 0 , zjzf_zftzjfamt number(16,2) default 0 , zjzf_zftzdfamt number(16,2) default 0 , sqzf_zczfamt number(16,2) default 0 , sqzf_qetkamt number(16,2) default 0 , sqzf_bftkamt number(16,2) default 0 , sqzf_zftzjfamt number(16,2)default 0 , sqzf_zftzdfamt number(16,2) default 0 , ybzf_zczfamt number(16,2)default 0 , ybzf_qetkamt number(16,2)default 0 , ybzf_bftkamt number(16,2) default 0 , ybzf_zftzjfamt number(16,2) default 0 , ybzf_zftzdfamt number(16,2) default 0 , constraint gk_itempay_temp_id primary key ( id ) ) ON COMMIT DELETE ROWS; comment on table GK_ITEMPAY_TEMP is '经济分类报表'; comment on column gk_itempay_temp.itemno is '资金来源编码'; comment on column gk_itempay_temp.zjzf_zczfamt is '直接支付正常对碰支付金额'; comment on column gk_itempay_temp.zjzf_qetkamt is '直接支付全额退款支付金额'; comment on column gk_itempay_temp.zjzf_bftkamt is '直接支付部分退款支付金额'; comment on column gk_itempay_temp.zjzf_zftzjfamt is '直接支付支付调账借方金额'; comment on column gk_itempay_temp.zjzf_zftzdfamt is '直接支付支付调账贷方金额'; comment on column gk_itempay_temp.sqzf_zczfamt is '授权支付正常对碰支付金额'; comment on column gk_itempay_temp.sqzf_qetkamt is '授权支付全额退款支付金额'; comment on column gk_itempay_temp.sqzf_bftkamt is '授权支付部分退款支付金额'; comment on column gk_itempay_temp.sqzf_zftzjfamt is '授权支付支付调账借方金额'; comment on column gk_itempay_temp.sqzf_zftzdfamt is '授权支付支付调账贷方金额'; comment on column gk_itempay_temp.ybzf_zczfamt is '一般支付正常对碰支付金额'; comment on column gk_itempay_temp.ybzf_qetkamt is '一般支付全额退款支付金额'; comment on column gk_itempay_temp.ybzf_bftkamt is '一般支付部分退款支付金额'; comment on column gk_itempay_temp.ybzf_zftzjfamt is '一般支付支付调账借方金额'; comment on column gk_itempay_temp.ybzf_zftzdfamt is '一般支付支付调账贷方金额'; insert into pwp_no (NOID, PREFIX, INITIALVALUE, BUFFERSIZE, POSTFIX, NOINCREMENT, NONAME, NOLENGTH, NOTYPE) values ('GK_ITEMPAY_TEMP_SEQ', null, 1, 10, null, 1, 'GK_ITEMPAY_TEMP_SEQ', 20, 1); insert into pwp_nodtl (NOID, PREFIX, NODAY, NEXTID, POSTFIX, NOINCREMENT, NOYEAR, NOMONTH) values ('GK_ITEMPAY_TEMP_SEQ', null, 0, 1, null, 1, 2018, 7); commit; create or replace procedure pro_query_itempay_temp ( query_type in varchar2 ,/*查询类型：0 为 年 1 为月 */ query_year in varchar2 ,/*年度：2018 */ query_month_start in varchar2 ,/*起始年月：2018-01 */ query_month_end in varchar2 ,/*结束年月：2018-01 */ v_cur out pkg_query.cur_query ) is --用于判断临时表是否存在中间变量 --查询所有语句 v_sql VARCHAR2(4000); v_year varchar2(4) :=to_char(sysdate,'yyyy') ; begin if query_type='0' then v_year:=query_year; elsif query_type='0' and query_month_start is not null then v_year:=substr(query_month_start,0,4); elsif query_type='0' and query_month_end is not null then v_year:=substr(query_month_end,0,4); end if; v_sql:='select v.* ,sum_zjzf+sum_sqzf+sum_ybzf sum_amt from ( select (select bj.econ_bdg from bs_jjflyskm bj where bj.econ_bdgid=t.itemno and bj.bdgyear='||v_year||' ) item_name, t.* , nvl(nvl(t.zjzf_zczfamt,0)-nvl(t.zjzf_qetkamt,0)-nvl(t.zjzf_bftkamt,0)-nvl(t.zjzf_zftzdfamt,0)+nvl(t.zjzf_zftzjfamt,0),0) sum_zjzf, nvl(nvl(t.sqzf_zczfamt,0)-nvl(t.sqzf_qetkamt,0)-nvl(t.sqzf_bftkamt,0)-nvl(t.sqzf_zftzdfamt,0)+nvl(t.sqzf_zftzjfamt,0),0) sum_sqzf, nvl(nvl(t.ybzf_zczfamt,0)-nvl(t.ybzf_qetkamt,0)-nvl(t.ybzf_bftkamt,0)-nvl(t.ybzf_zftzdfamt,0)+nvl(t.ybzf_zftzjfamt,0),0) sum_ybzf from gk_itempay_temp t ) v order by v.itemno asc '; dbms_output.put_line(v_sql); commit; /* 直接支付支出数=直接支付正常对碰数-全额退款-部分退款-支出调整直接支付调整前(贷方)+支出调整直接支付调整后(借方) 授权支付支出数=授权支付正常对碰数-全额退款-部分退款-支出调整授权支付调整前(贷方)+支出调整授权支付调整后(借方) 一般支付支出数=一般支付支出正常对碰数-一般支付退回正常对碰数-支出调整一般支付调整前(贷方)+支出调整一般支付调整后(借方) */ if query_type='0' then ----直接支付凭证 正常对碰支付 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(zjzf_zczfamt) zjzf_zczfamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) zjzf_zczfamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zjzfsq_detail_item i where z.voucher_type='0' and to_char(z.affirm_date,'yyyy')= query_year and z.check_status in ('1', '2', '02') and z.origin_id=i.gk_zjzfsq_detail_id(+) ) v group by itemno ) b on(a.itemno=b.itemno ) when matched then update set a.zjzf_zczfamt=nvl(a.zjzf_zczfamt,0)+b.zjzf_zczfamt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_zczfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.zjzf_zczfamt) ; ----直接支付 全额退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(zjzf_zczfamt) zjzf_zczfamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) zjzf_zczfamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zjzfsq_detail_item i where z.voucher_type='0' and to_char(z.affirm_date,'yyyy')= query_year and z.check_status in ( '2', '02') and z.origin_id=i.gk_zjzfsq_detail_id(+) ) v group by itemno ) b on(a.itemno=b.itemno) when matched then update set a.zjzf_qetkamt=nvl(a.zjzf_qetkamt,0)+b.zjzf_zczfamt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_qetkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.zjzf_zczfamt) ; ----直接支付 部分退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select gpi_itemno ,sum(gpi_amt) gpi_amt from ( select gpi.itemno gpi_itemno ,gpi.amt gpi_amt from gk_part_refundment gp , gk_part_refundment_item gpi where gp.collate_status = '1' and gp.id=gpi.gk_part_refundment_id(+) and gp.voucher_type = '0' and to_char(gp.collate_date, 'yyyy') = query_year ) v group by gpi_itemno ) b on(a.itemno=b.gpi_itemno) when matched then update set a.zjzf_bftkamt=nvl(a.zjzf_bftkamt,0)+b.gpi_amt where a.itemno=b.gpi_itemno when not matched then insert (id ,itemno,zjzf_bftkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.gpi_itemno,b.gpi_amt) ; ---- 支出账务调账 直接支付 借方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='jf' and ga.biz_status ='9' and ga.expenditure_type ='0' /*直接支付*/ and to_char(ga.create_dt,'yyyy')=query_year group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.zjzf_zftzjfamt=nvl(a.zjzf_zftzjfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_zftzjfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---- 支出账务调账 直接支付 贷方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='df' and ga.biz_status ='9' and ga.expenditure_type ='0' /*直接支付*/ and to_char(ga.create_dt,'yyyy')=query_year group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.zjzf_zftzdfamt=nvl(a.zjzf_zftzdfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_zftzdfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ----授权支付凭证 正常对碰支付 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(sqzf_zczfamt) sqzf_zczfamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) sqzf_zczfamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zfpz_item i where z.voucher_type='1' and to_char(z.affirm_date,'yyyy')=query_year and z.check_status in ('1', '2', '02') and z.id=i.gk_zfpz_id(+) ) v group by itemno ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_zczfamt=nvl(a.sqzf_zczfamt,0)+b.sqzf_zczfamt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_zczfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.sqzf_zczfamt) ; ----授权支付凭证 全额退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(sqzf_qetkamt) sqzf_qetkamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) sqzf_qetkamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zfpz_item i where z.voucher_type='1' and to_char(z.affirm_date,'yyyy')=query_year and z.check_status in ('2', '02') and z.id=i.gk_zfpz_id(+) ) v group by itemno ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_qetkamt=nvl(a.sqzf_zczfamt,0)+b.sqzf_qetkamt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_qetkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.sqzf_qetkamt) ; ----授权支付 部分退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select gpi_itemno ,sum(gpi_amt) gpi_amt from ( select gpi.itemno gpi_itemno ,gpi.amt gpi_amt from gk_part_refundment gp , gk_part_refundment_item gpi where gp.collate_status = '1' and gp.id=gpi.gk_part_refundment_id(+) and gp.voucher_type = '1' and to_char(gp.collate_date, 'yyyy') = query_year ) v group by gpi_itemno ) b on(a.itemno=b.gpi_itemno) when matched then update set a.sqzf_bftkamt=nvl(a.sqzf_bftkamt,0)+b.gpi_amt where a.itemno=b.gpi_itemno when not matched then insert (id ,itemno,sqzf_bftkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.gpi_itemno,b.gpi_amt) ; ---- 支出账务调账 授权支付 借方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='jf' and ga.biz_status ='9' and ga.expenditure_type ='1' /*授权支付*/ and to_char(ga.create_dt,'yyyy')=query_year group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_zftzjfamt=nvl(a.sqzf_zftzjfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_zftzjfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---- 支出账务调账 授权支付 贷方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='df' and ga.biz_status ='9' and ga.expenditure_type ='1' /*授权支付*/ and to_char(ga.create_dt,'yyyy')=query_year group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_zftzdfamt=nvl(a.sqzf_zftzdfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_zftzdfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---一般支付 支出 merge into gk_itempay_temp a using ( select cpdi.itemno itemno , sum((case when cpdi.amt is not null and cpdi.amt=0 and z.amt is not null then z.amt when cpdi.amt is null and z.amt is not null then z.amt when cpdi.amt is not null and cpdi.amt >0 then cpdi.amt else cpdi.amt end )) amt from gk_zfpz z ,common_pay_detail cpd,common_pay_detail_item cpdi where z.id=cpd.gk_zfpz_id and cpd.id=cpdi.common_pay_detail_id(+) and z.voucher_type = '3' and z.wf_status = '9' and to_char(z.checker_date, 'yyyy')= query_year group by cpdi.itemno ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_zczfamt=nvl(a.ybzf_zczfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_zczfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---一般支付 退款 merge into gk_itempay_temp a using ( select cdi.itemno itemno ,sum((case when cdi.amt is null and cd.back_amt is not null then cd.back_amt when cdi.amt is not null and cdi.amt =0 and cd.back_amt is not null then cd.back_amt when cdi.amt is not null and cdi.amt >0 then cdi.amt else cdi.amt end )) amt from common_req_back c , common_req_back_detail cd ,common_req_back_detail_item cdi where c.id=cd.common_req_back_id and cd.id=cdi.common_req_back_detail_id(+) and c.wf_status ='9' and to_char(c.checker_date, 'yyyy')=query_year group by cdi.itemno ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_qetkamt=nvl(a.ybzf_qetkamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_qetkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; -----支出账务调整业务_一般支付 调整后（借方） merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='jf' and ga.biz_status ='9' and ga.expenditure_type ='3' /*直接支付*/ and to_char(ga.create_dt,'yyyy')=query_year group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_zftzjfamt=nvl(a.ybzf_zftzjfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_zftzjfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; -----支出账务调整业务_一般支付 调整前（贷方） merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='df' and ga.biz_status ='9' and ga.expenditure_type ='3' /*直接支付*/ and to_char(ga.create_dt,'yyyy')=query_year group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_zftzdfamt=nvl(a.ybzf_zftzdfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_zftzdfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; elsif query_type='1' then ----直接支付凭证 正常对碰支付 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(zjzf_zczfamt) zjzf_zczfamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) zjzf_zczfamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zjzfsq_detail_item i where z.voucher_type='0' and to_char(z.affirm_date,'yyyy-mm') >= query_month_start and to_char(z.affirm_date,'yyyy-mm') <= query_month_end and z.check_status in ('1', '2', '02') and z.origin_id=i.gk_zjzfsq_detail_id(+) ) v group by itemno ) b on(a.itemno=b.itemno ) when matched then update set a.zjzf_zczfamt=nvl(a.zjzf_zczfamt,0)+b.zjzf_zczfamt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_zczfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.zjzf_zczfamt) ; ----直接支付 全额退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(zjzf_zczfamt) zjzf_zczfamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) zjzf_zczfamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zjzfsq_detail_item i where z.voucher_type='0' and to_char(z.affirm_date,'yyyy-mm') >= query_month_start and to_char(z.affirm_date,'yyyy-mm') <= query_month_end and z.check_status in ( '2', '02') and z.origin_id=i.gk_zjzfsq_detail_id(+) ) v group by itemno ) b on(a.itemno=b.itemno) when matched then update set a.zjzf_qetkamt=nvl(a.zjzf_qetkamt,0)+b.zjzf_zczfamt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_qetkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.zjzf_zczfamt) ; ----直接支付 部分退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select gpi_itemno ,sum(gpi_amt) gpi_amt from ( select gpi.itemno gpi_itemno ,gpi.amt gpi_amt from gk_part_refundment gp , gk_part_refundment_item gpi where gp.collate_status = '1' and gp.id=gpi.gk_part_refundment_id(+) and gp.voucher_type = '0' and to_char(gp.collate_date, 'yyyy-mm') >= query_month_start and to_char(gp.collate_date, 'yyyy-mm') <= query_month_end ) v group by gpi_itemno ) b on(a.itemno=b.gpi_itemno) when matched then update set a.zjzf_bftkamt=nvl(a.zjzf_bftkamt,0)+b.gpi_amt where a.itemno=b.gpi_itemno when not matched then insert (id ,itemno,zjzf_bftkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.gpi_itemno,b.gpi_amt) ; ---- 支出账务调账 直接支付 借方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='jf' and ga.biz_status ='9' and ga.expenditure_type ='0' /*直接支付*/ and to_char(ga.create_dt,'yyyy-mm') >=query_month_start and to_char(ga.create_dt,'yyyy-mm') <=query_month_end group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.zjzf_zftzjfamt=nvl(a.zjzf_zftzjfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_zftzjfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---- 支出账务调账 直接支付 贷方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='df' and ga.biz_status ='9' and ga.expenditure_type ='0' /*直接支付*/ and to_char(ga.create_dt,'yyyy-mm') >=query_month_start and to_char(ga.create_dt,'yyyy-mm') <=query_month_end group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.zjzf_zftzdfamt=nvl(a.zjzf_zftzdfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,zjzf_zftzdfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ----授权支付凭证 正常对碰支付 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(sqzf_zczfamt) sqzf_zczfamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) sqzf_zczfamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zfpz_item i where z.voucher_type='1' and to_char(z.affirm_date,'yyyy-mm') >=query_month_start and to_char(z.affirm_date,'yyyy-mm') <=query_month_end and z.check_status in ('1', '2', '02') and z.id=i.gk_zfpz_id(+) ) v group by itemno ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_zczfamt=nvl(a.sqzf_zczfamt,0)+b.sqzf_zczfamt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_zczfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.sqzf_zczfamt) ; ----授权支付凭证 全额退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select itemno ,sum(sqzf_qetkamt) sqzf_qetkamt from ( select (case when i.amt is null and z.amt is not null then z.amt when i.amt =0 and z.amt is not null then z.amt else i.amt end ) sqzf_qetkamt , (case when i.itemno is null and z.itemno is not null then z.itemno else i.itemno end ) itemno from gk_zfpz z ,gk_zfpz_item i where z.voucher_type='1' and to_char(z.affirm_date,'yyyy-mm')>=query_month_start and to_char(z.affirm_date,'yyyy-mm')<=query_month_end and z.check_status in ('2', '02') and z.id=i.gk_zfpz_id(+) ) v group by itemno ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_qetkamt=nvl(a.sqzf_zczfamt,0)+b.sqzf_qetkamt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_qetkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.sqzf_qetkamt) ; ----授权支付 部分退款 经济分类编码与金额 merge into gk_itempay_temp a using ( select gpi_itemno ,sum(gpi_amt) gpi_amt from ( select gpi.itemno gpi_itemno ,gpi.amt gpi_amt from gk_part_refundment gp , gk_part_refundment_item gpi where gp.collate_status = '1' and gp.id=gpi.gk_part_refundment_id(+) and gp.voucher_type = '1' and to_char(gp.collate_date, 'yyyy-mm') >= query_month_start and to_char(gp.collate_date, 'yyyy-mm') <= query_month_end ) v group by gpi_itemno ) b on(a.itemno=b.gpi_itemno) when matched then update set a.sqzf_bftkamt=nvl(a.sqzf_bftkamt,0)+b.gpi_amt where a.itemno=b.gpi_itemno when not matched then insert (id ,itemno,sqzf_bftkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.gpi_itemno,b.gpi_amt) ; ---- 支出账务调账 授权支付 借方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='jf' and ga.biz_status ='9' and ga.expenditure_type ='1' /*授权支付*/ and to_char(ga.create_dt,'yyyy-mm') >=query_month_start and to_char(ga.create_dt,'yyyy-mm') <=query_month_end group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_zftzjfamt=nvl(a.sqzf_zftzjfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_zftzjfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---- 支出账务调账 授权支付 贷方金额 merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='df' and ga.biz_status ='9' and ga.expenditure_type ='1' /*授权支付*/ and to_char(ga.create_dt,'yyyy-mm')>=query_month_start and to_char(ga.create_dt,'yyyy-mm')<=query_month_end group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.sqzf_zftzdfamt=nvl(a.sqzf_zftzdfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,sqzf_zftzdfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---一般支付 支出 merge into gk_itempay_temp a using ( select cpdi.itemno itemno , sum((case when cpdi.amt is not null and cpdi.amt=0 and z.amt is not null then z.amt when cpdi.amt is null and z.amt is not null then z.amt when cpdi.amt is not null and cpdi.amt >0 then cpdi.amt else cpdi.amt end )) amt from gk_zfpz z ,common_pay_detail cpd,common_pay_detail_item cpdi where z.id=cpd.gk_zfpz_id and cpd.id=cpdi.common_pay_detail_id(+) and z.voucher_type = '3' and z.wf_status = '9' and to_char(z.checker_date, 'yyyy-mm') >= query_month_start and to_char(z.checker_date, 'yyyy-mm') <= query_month_end group by cpdi.itemno ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_zczfamt=nvl(a.ybzf_zczfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_zczfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; ---一般支付 退款 merge into gk_itempay_temp a using ( select cdi.itemno itemno ,sum((case when cdi.amt is null and cd.back_amt is not null then cd.back_amt when cdi.amt is not null and cdi.amt =0 and cd.back_amt is not null then cd.back_amt when cdi.amt is not null and cdi.amt >0 then cdi.amt else cdi.amt end )) amt from common_req_back c , common_req_back_detail cd ,common_req_back_detail_item cdi where c.id=cd.common_req_back_id and cd.id=cdi.common_req_back_detail_id(+) and c.wf_status ='9' and to_char(c.checker_date, 'yyyy-mm') >=query_month_start and to_char(c.checker_date, 'yyyy-mm') <=query_month_end group by cdi.itemno ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_qetkamt=nvl(a.ybzf_qetkamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_qetkamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; -----支出账务调整业务_一般支付 调整后（借方） merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='jf' and ga.biz_status ='9' and ga.expenditure_type ='3' /*直接支付*/ and to_char(ga.create_dt,'yyyy-mm')>=query_month_start and to_char(ga.create_dt,'yyyy-mm')<=query_month_end group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_zftzjfamt=nvl(a.ybzf_zftzjfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_zftzjfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; -----支出账务调整业务_一般支付 调整前（贷方） merge into gk_itempay_temp a using ( select sum(gad.balance) amt ,gadi.econ_bdgid as itemno from gk_adjust_detail_item gadi ,gk_adjust_detail gad ,gk_adjust ga where ga.id=gad.gk_adjust_id and gad.gk_adjust_detail_id=gadi.gk_adjust_detail_id(+) and gadi.itemtype='df' and ga.biz_status ='9' and ga.expenditure_type ='3' /*直接支付*/ and to_char(ga.create_dt,'yyyy-mm')>=query_month_start and to_char(ga.create_dt,'yyyy-mm')<=query_month_end group by gadi.econ_bdgid ) b on(a.itemno=b.itemno) when matched then update set a.ybzf_zftzdfamt=nvl(a.ybzf_zftzdfamt,0)+b.amt where a.itemno=b.itemno when not matched then insert (id ,itemno,ybzf_zftzdfamt) values( nextid('GK_ITEMPAY_TEMP_SEQ'),b.itemno,b.amt) ; end if ; OPEN v_cur FOR v_sql; end pro_query_itempay_temp; /
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
