实现销售合同结存条件

最新推荐文章于 2024-06-03 19:54:14 发布

凌枫zero

最新推荐文章于 2024-06-03 19:54:14 发布

阅读量222

点赞数

分类专栏：工作笔记文章标签： easyui select下拉框销售合同 java oracle

本文链接：https://blog.csdn.net/qq_43674132/article/details/106278716

版权

工作笔记专栏收录该内容

24 篇文章 1 订阅

订阅专栏

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

先写后台代码：

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

显示效果如下图：

凌枫zero

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
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=
复制链接

扫一扫

专栏目录