实现销售合同结存条件

工作笔记 专栏收录该内容
24 篇文章 0 订阅

需求:通过销售合同主表销售回款记录表关联查询出来剩余应收金额,也叫结存金额

先写后台代码:

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') &gt; 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') &lt; 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') &gt; 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') &gt; 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';
					}
				}
					
		});

显示效果如下图:

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值