需求:通过销售合同主表和销售回款记录表关联查询出来剩余应收金额,也叫结存金额
先写后台代码:
SalesContract.xml代码如下:通过用销售合同的销售合同金额-销售回款记录的已收合同金额总和 = 销售合同结存金额
fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') as unAcceptMoney
<select id="queryFinancialSalesContractList" parameterType="map"
resultType="com.yhzn.model.finance.SalesContract">
SELECT
nvl((select SUM(fd.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fd where fd.ACCEPT_ID = fs.ID),'0') as sumAcceptMoney,
fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') as unAcceptMoney,
nvl((select SUM(fi.INVOICED_AMOUNT) from FINANCLAL_SALES_INVOICING fi where fi.CONTRACT_ID = fs.ID),'0') as invoicedAmount,
(select co.UNIT_NAME from CUSTOMER_INFO co where co.ID = fs.CUSTOMER_ID) as unitName,
(select co.TYPE from CUSTOMER_INFO co where co.ID = fs.CUSTOMER_ID) as customerCategory,
(SELECT count( id ) FROM FINANCIAL_SALES_DETALL where ACCEPT_ID=fs.ID) as detallCount,
(SELECT count( id ) FROM CERTIFICATE where CONTRACT_ID=fs.ID) as certificateCount,
(SELECT count( id ) FROM FINANCLAL_SALES_INVOICING where CONTRACT_ID=fs.ID) as salesInvoicingCount,
fs.ID,
fs.CUSTOMER_ID,
fs.ACCEPT_MONEY,
fs.REMARK,
fs.CREATE_NAME,
fs.CREATE_DATE,
fs.MODIFY_NAME,
fs.MODIFY_DATE,
fs.DELETE_FLAG,
fs.REV1,
fs.REV2,
fs.REV3,
fs.CONTRACT_NAME,
fs.CONTRACT_NO,
fs.SIGN_DATE,
fs.PROVINCE_OR_OUTSIDE,
fs.CITY,
fs.INVOICED_AMOUNT,
fs.MONEY,
fs.RESPONSIBLE_PERSON
FROM
FINANCIAL_SALES_CONTRACT fs
where 1=1
<if test="customerId!=null and customerId!=''">
and fs.CUSTOMER_ID like '%${customerId}%'
</if>
<if test="city!=null and city!=''">
and fs.CITY like '%${city}%'
</if>
<if test="contractName!=null and contractName!=''">
and fs.CONTRACT_NAME like '%${contractName}%'
</if>
<if test="responsiblePerson!=null and responsiblePerson!=''">
and fs.RESPONSIBLE_PERSON like '%${responsiblePerson}%'
</if>
<if test="customerCategory!=null and customerCategory!=''">
and EXISTS(SELECT co.TYPE FROM CUSTOMER_INFO co WHERE co.ID = fs.CUSTOMER_ID and co.TYPE = #{customerCategory,jdbcType=VARCHAR})
</if>
<if test="beginDate != null and beginDate != ''">
and fs.SIGN_DATE<![CDATA[>=]]>to_date(#{beginDate},'yyyy-mm-dd')
</if>
<if test="endDate != null and endDate != ''">
and fs.SIGN_DATE <![CDATA[<]]>to_date(#{endDate},'yyyy-mm-dd')
</if>
<if test='jcMoney=="0"'>
and fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') > 0
</if>
<if test='jcMoney=="1"'>
and fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') < 60000
</if>
<if test='jcMoney=="2"'>
and fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') > 60000
</if>
order by fs.CREATE_DATE desc
</select>
前端SalesContract.jsp代码:前台显示后台查询的虚拟字段即可
{
field : 'unAcceptMoney',
title : '结存',
width : '5%',
align : 'center',
}
效果如下:
接下来要给计算出来的结存设置查询条件。
首先在前端写如下代码,jsp页面的搜索条件里面写个select下拉框,里面有四个option标签,还有value值,
select下拉框设置默认值只需要在需要设置成默认值的地方加个selected
结存条件<select id="jcMoney" name="jcMoney" class="easyui-combobox" style="width:150px;">
<option value="0">结存大于0</option>
<option value="1">结存小于60000</option>
<option value="2">结存大于60000</option>
<option value="3" selected>所有</option>
</select>
如下是查询结存条件的js方法,根据结存条件查询queryParameter.jcMoney = $("#jcMoney").combobox("getValue");
下拉框里面获取到的是$("#jcMoney").combobox("getValue")
//查询条件
function queryFun() {
var queryParameter = $('#dg').datagrid("options").queryParams;
/* 根据合同名称查询 */
queryParameter.contractName = $("#contractNameId").val();
/* 根据城市查询 */
queryParameter.city = $("#searchCity").combobox("getText");
/* 根据负责人查询 */
queryParameter.responsiblePerson = $("#searchResponsiblePerson").combobox("getText");
/* 根据客户单位名称查询 */
queryParameter.customerId = $("#searchCustomerId").combobox("getValue");
/* 根据客户类别查询 */
queryParameter.customerCategory = $("#searchCustomerCategory").combobox("getText");
/* 根据结存条件查询 */
queryParameter.jcMoney = $("#jcMoney").combobox("getValue");
/* 根据签订时间查询 */
queryParameter.beginDate = $("#f_beginDate").datebox("getValue");
/* 根据签订时间查询 */
queryParameter.endDate = $("#f_endDate").datebox("getValue");
$("#dg").datagrid("reload");
}
后台的SalesContractController类的方法如下:
String jcMoney = request.getParameter("jcMoney");//结存
parameter.put("jcMoney",jcMoney);
/**
* 查询销售合同列表
* @param request
* @return
*/
@RequestMapping(value="/queryFinancialSalesContractList", method = RequestMethod.POST)
@ResponseBody
public PageUtil queryFinancialSalesContractList(HttpServletRequest request){
//获取登录人信息
User user= (User) request.getSession().getAttribute("user");
//日志类型,操作人,操作内容,操作人IP,操作方法
sysLogService.insertSysLog("查询",user.getTrueName(),"查询人员信息列表 ",user.getLoginIp(),"/financialSalesContract/queryFinancialSalesContractList");
int page = Integer.parseInt(request.getParameter("page"));
int rows = Integer.parseInt(request.getParameter("rows"));
String city = request.getParameter("city");//城市 模糊查询的时候需要用到
String contractName = request.getParameter("contractName");//合同名称
String responsiblePerson = request.getParameter("responsiblePerson");//创建人
String customerId = request.getParameter("customerId");//客户单位名称
String customerCategory = request.getParameter("customerCategory");//客户单位名称
String jcMoney = request.getParameter("jcMoney");//结存
String beginDate = request.getParameter("beginDate");//签订开始时间
String endDate = request.getParameter("endDate");//签订结束时间
Map<String,Object> parameter = new HashMap<String,Object>();
parameter.put("city", city);
parameter.put("contractName",contractName);
parameter.put("responsiblePerson",responsiblePerson);
parameter.put("customerCategory",customerCategory);
parameter.put("jcMoney",jcMoney);
parameter.put("beginDate",beginDate);
parameter.put("endDate",endDate);
parameter.put("customerId",customerId);
PageBounds bounds = new PageBounds(page , rows );
List<SalesContract> list = salesContractService.queryFinancialSalesContractList(bounds, parameter);
// 获得结果集条总数
int total = ((PageList<SalesContract>) list).getPaginator().getTotalCount();
// 页面列表展示
PageUtil result = new PageUtil();
result.setRows(list);
result.setTotal(total);
return result;
}
SalesContract.xml文件代码如下:test里面是从前端传过来的,0是结存条件option的value
SELECT
fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') as unAcceptMoney
FROM
FINANCIAL_SALES_CONTRACT fs
where 1=1
<if test="customerCategory!=null and customerCategory!=''">
and EXISTS(SELECT co.TYPE FROM CUSTOMER_INFO co WHERE co.ID = fs.CUSTOMER_ID and co.TYPE = #{customerCategory,jdbcType=VARCHAR})
</if>
<if test='jcMoney=="0"'>
and fs.money - nvl((select SUM(fe.ACCEPT_MONEY) from FINANCIAL_SALES_DETALL fe where fe.ACCEPT_ID = fs.ID),'0') > 0
</if>
order by fs.CREATE_DATE desc
实现的效果如下:
这个采购合同计算显示为0的原因是因为我新增了一条销售合同,但是我没有加采购回款记录和采购开票记录,所以它这计算的时候没有默认值,计算出来就为0,所以我可以在PurchaseContractMapper.xml的queryFinancePurchaseContractList方法里面用oracle设置默认值的方法给子查询出来的已收合同金额总和 和 开票金额总和 nvl(" " , " ");
nvl((select SUM(fd.PAY_MONEY) from FINANCE_PURCHASE_DETAIL fd where fd.PAY_ID = f.ID),'0') as sumPayMoney,
f.MONEY-nvl((select SUM(fd.PAY_MONEY) from FINANCE_PURCHASE_DETAIL fd WHERE fd.PAY_ID = f.ID),'0') as unPayMoney,
nvl((select SUM(fp.INVOICED_AMOUNT) from FINANCLAL_PURCHASE_INVOICING fp where fp.CONTRACT_ID = f.ID),'0') as invoicedAmount,
改完之后效果如下
给合同金额,已收合同金额,结存和已开票金额计算出来的总和写一个显示合计多少
代码如下:
$('#dg').datagrid({
onLoadSuccess: function(data) {
var rows = $('#dg').datagrid('getRows') //获取当前的数据行
var ptotal = 0 //计算采购开票金额的总和
var invo = 0 //计算采购已收合同金额的总和
var sumMoney = 0;//计算采购合同金额总和
var un =0 //计算采购结存总和
for(var i = 0; i < rows.length; i++) {
ptotal += parseFloat(rows[i]['invoicedAmount']);
invo += parseFloat(rows[i]['sumPayMoney']);
sumMoney += parseFloat(rows[i]['money']);
un += parseFloat(rows[i]['unPayMoney']);
}
if(isNaN(ptotal)){
ptotal='0'
}
if(isNaN(invo)){
invo='0'
}
if(isNaN(sumMoney)){
sumMoney='0'
}
if(isNaN(un)){
un='0'
}
//新增一行显示统计信息
$('#dg').datagrid('appendRow', {
id:'unPayMoneyy',
city: '<b>合计:</b>',
invoicedAmount:ptotal,
sumPayMoney:invo,
money:sumMoney,
unPayMoney:un
});
},
rowStyler: function(index, row) {
if(row.city == '<b>合计:</b>') {
return 'background-color:#EAEAEA;color:blue';
}
}
});
显示效果如下图: