环境：
bootstrab、springboot、tomcat 、微服务、mysql、idea
ERP：静态文件，页面文件，站点文件
微服务：各类系统模块服务、项目实体类定义、各类control、接口定义和实现、dao、mybatis
目录：
一、ERP
1、html
2、js
3、API
4、control
二、微服务
1、实体
2、control
3、接口定义
4、接口实现
5、dao层
6、xml
7、util
三、页面请求
四、接口数据
五、后台数据
详细文档：
一、ERP
1、html
<!DOCTYPE html>
<html lang="zh_CN" xmlns:th="http://www.thymeleaf.org">
<meta charset="utf-8">
<head th:include="include::header">
</head>
<link rel="stylesheet" href="/css/bootstrap-select.min.css">
<link rel="stylesheet" href="/css/plugins/bootstrap/bootstrap-datepicker3.css">
<style type="text/css">
#tt td{
width: 20%;
min-width: 302px;
}
#tt .inp{
width: 100px;
}
</style>
<body class="gray-bg" id="gray-bg">
<div class="wrapper wrapper-content">
<div class="row">
<div class="col-sm-12">
<div class="ibox">
<div class="ibox-body">
<div class="fixed-table-toolbar">
<div class="form-inline" role="form">
<table width="100%" id="tt">
<tr>
<td>
<div class="form-group">
<label class="control-label">条码/包码</label>
<input id="barCode" class="form-control" style="width: 200px" type="text"/>
</div>
</td>
<td>
<div class="form-group">
<label class="control-label">回货条码</label>
<input id="returnBarCode" class="form-control" style="width: 200px" type="text"/>
</div>
</td>
<td>
<div class="form-group">
<label class="control-label">发料单号</label>
<input id="sendMaterialNO" name="sendMaterialNO" class="form-control" style="width: 200px" type="text"/>
</div>
</td>
<td>
<div class="form-group">
<label class="control-label">加工单号</label>
<input class="form-control" id="machiningBillNo" style="width: 200px" type="text"/>
</div>
</td>
<td>
<div class="form-group">
<div class="form-group">
<label class="control-label">证书编号</label>
<input class="form-control" id="intInstrumentNo" style="width: 200px" type="text"/>
</div>
</div>
</td>
<td>
<div class="form-group">
<label class="control-label">生产单号</label>
<input id="produceCodeId" class="form-control" style="width: 200px" type="text"/>
</div>
</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td colspan="2">
<div class="form-group">
<label class="control-label">发料日期</label>
<input id="sendDateStart" class="form-control" type="text"/>
<span>至</span>
<input id="sendDateEnd" class="form-control" type="text"/>
</div>
</td>
<td colspan="2">
<div class="form-group">
<label class="control-label">退货日期</label>
<input id="returnDateStart" class="form-control" type="text"/>
<span>至</span>
<input id="returnDateEnd" class="form-control" type="text"/>
</div>
</td>
<td colspan="2">
<div class="form-group">
<label class="control-label">回货日期</label>
<input id="entryDateStart" class="form-control" type="text"/>
<span>至</span>
<input id="entryDateEnd" class="form-control" type="text"/>
</div>
</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td colspan="2">
<div class="form-group">
<label class="control-label">品类</label>
<select class="form-control chosen-select" style="width: 150px;" id="category">
<option value="">全部</option>
<option value="1">钻石</option>
<option value="2">彩宝</option>
<option value="3">珍珠</option>
<option value="4">钻镶</option>
<option value="5">黄金</option>
<option value="6">K金</option>
<option value="7">铂金</option>
<option value="8">银</option>
</select>
<select id="cateId" class="selectpicker" title="全部" multiple data-live-search="true" data-live-search-placeholder="搜索">
</select>
</div>
</td>
<!-- <td>-->
<!-- <div class="form-group">-->
<!-- <label class="control-label">业务单据</label>-->
<!-- <select class="form-control chosen-select" style="width: 200px" id="businessId">-->
<!-- <option value="">全部</option>-->
<!-- <option sid="34" value="204">发料单业务</option>-->
<!-- <option sid="35" value="502">退料单业务</option>-->
<!-- </select>-->
<!-- </div>-->
<!-- </td>-->
<td>
<div class="form-group">
<label class="control-label">单据类型</label>
<select class="selectpicker" name="billTypeId" id="billTypeId" title="全部" multiple>
</select>
</div>
</td>
<td>
<div class="form-group">
<label class="control-label">供应商</label>
<select name="supplyId" id="supplyId" class="selectpicker" title="全部" multiple>
</select>
</div>
</td>
<td>
<div class="form-group" >
<label class="control-label">是否回货</label>
<select name="isReturn" id="isReturn" class="form-control chosen-select" style="width:200px;">
<option value="">全部</option>
<option value="1">全部回货</option>
<option value="2">部分回货</option>
<option value="3">未回货</option>
</select>
</div>
</td>
</tr>
<tr>
<td> </td>
</tr>
</table>
<div class="form-group">
<button class="btn btn-success" onclick="sendReturn()" style="margin-left: 50px">
<i class="fa" aria-hidden="true"></i>查询
</button>
<button class="btn btn-primary" onclick="getExportList(0)" type="button"
style="margin-left: 30px">
<i aria-hidden="true" class="fa"></i>导出
</button>
<button class="btn btn-success" onclick="sendReturnSum()" style="margin-left: 50px">
<i class="fa" aria-hidden="true"></i>发料退料统计
</button>
<button class="btn btn-primary" onclick="getExportListSum(0)" type="button"
style="margin-left: 30px">
<i aria-hidden="true" class="fa"></i>统计导出
</button>
</div>
<div style="margin-top: 20px;">
<span>发料明细</span>
<button type="button" class="btn btn-success" onclick="toListCount()" style="margin-left: 30px" >
<i class="fa" aria-hidden="true"></i>发料统计
</button>
<select id="orderBy" style="width: 90px;height: 30px;position: absolute;margin: 2px 0 0 38px;">
<option value="createTime,DESC">默认排序</option>
<option value="price,DESC">数量降序</option>
<option value="price,ASC">数量升序</option>
<option value="stoneWeight,DESC">重量降序</option>
<option value="stoneWeight,ASC">重量升序</option>
</select>
<div class="form-inline" style="float: right;font-family: '宋体';font-size: 14px;margin-top: 15px;">
<span style="margin-right:30px;">合计售价：<label id="sumPrice"></label></span>
<span style="margin-right:30px;" shiro:hasPermission="stock:sumCost">合计标准成本：<label id="sumCost"></label></span>
<span style="margin-right:30px;">合计主数量：<label id="sumMainCount"></label></span>
<span style="margin-right:80px;">合计辅数量：<label id="sumSecCount"></label></span>
</div>
<table id="sendReturnSumTable" data-mobile-responsive="true"></table>
</div>
</div>
<div style="margin-top: 20px;">
<table id="exampleTable" data-mobile-responsive="true" style="font-size: 12px"></table>
</div>
<div style="margin-top: 20px;">
<table id="sendReturnTable" data-mobile-responsive="true" style="font-size: 12px"></table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="http://127.0.0.1:8000/CLodopfuncs.js?priority=1"></script>
<div th:include="include::footer"></div>
<script type="text/javascript" src="/js/plugins/bootstrap/bootstrap-select.js" charset="UTF-8"></script>
<script type="text/javascript" src="/js/plugins/bootstrap/locales/defaults-zh_CN.min.js" charset="UTF-8"></script>
<script type="text/javascript" src="/js/plugins/bootstrap/bootstrap-datepicker.js" charset="UTF-8"></script>
<script type="text/javascript" src="/js/plugins/bootstrap/locales/bootstrap-datepicker.zh-CN.min.js"charset="UTF-8"></script>
<script type="text/javascript" src="/js/appjs/warehouse/commonWarehouse.js"></script>
<script type="text/javascript" src="/js/appjs/sendOrReturnMaterial/sendMaterialReconciliation/list.js?v=211105"></script>
<script type="text/javascript">
$('#sendDateStart').datepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
autoclose: true
})
$('#sendDateEnd').datepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
autoclose: true
});
$('#returnDateStart').datepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
autoclose: true
})
$('#returnDateEnd').datepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
autoclose: true
});
$('#entryDateStart').datepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
autoclose: true
})
$('#entryDateEnd').datepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
autoclose: true
});
</script>
</body>
</html>
2、js
var prefix = "/sendMaterialReconciliation";
$(function() {
sendReturn();
getBillTypeListforSelect('billTypeId', null, true, 34);
getSupplyNameListByCondition('supplyId',2,true,null);
getCateListForSelect('cateId',null,true,null);
$("#category").change(function(){
var category = $("#category").val();
getCateListForSelect('cateId',null,true,category);
});
return '请选择条件后进行查询，当前登录用户具有权限查询时数据量过大，因此默认不查询数据！';
});
//跳转到发料统计页面
function toListCount(){
if(!haveFranchisee && !haveCompany){
layer.msg("无发料统计数据权限");
return;
}
var addPage = layer.open({
type: 2,
title: '发料统计',
maxmin: true,
shadeClose: false, // 点击遮罩关闭层
scrollbar: false,
area: [ '810px', '550px' ],
content: prefix+"/sendReturnSumList" //iframe的url
});
layer.restore(addPage);
}
function sendReturn() {
$("#sendReturnTable").bootstrapTable('destroy');
$('#exampleTable').bootstrapTable('destroy');
$('#exampleTable').bootstrapTable({
method : 'post', // 服务器数据的请求方式 get or post
url : prefix + "/getSendReturnList", // 服务器数据的加载地址
striped : true, // 设置为true会有隔行变色效果
dataType : "json", // 服务器返回的数据类型
pagination : true, // 设置为true会在底部显示分页条
// queryParamsType : "limit",
// //设置为limit则会发送符合RESTFull格式的参数
singleSelect : false, // 设置为true将禁止多选
iconSize : 'outline',
toolbar : '#exampleToolbar',
contentType : "application/x-www-form-urlencoded",
// //发送到服务器的数据编码类型
pageSize : 10, // 如果设置了分页，每页数据条数
pageNumber : 1, // 如果设置了分布，首页页码
search : false, // 是否显示搜索框
showColumns : false, // 是否显示内容下拉框（选择显示的列）
showRefresh: false, //是否显示刷新按钮
sidePagination : "server", // 设置在哪里进行分页，可选值为"client" 或者 "server"
queryParams : function(params) {
return {
// 说明：传入后台的参数包括offset开始索引，limit步长，sort排序列，order：desc或者,以及所有列的键值对
offset : params.offset,
limit : params.limit,
billTypeId:$('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]","") : '',
supplyId:$('#supplyId').val() ? JSON.stringify($('#supplyId').val()).replace("[","").replace("]","") : '',
// billTypeIds = $('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]",""):"";
// isReturn:$('#isReturn').val() ? JSON.stringify($('#isReturn').val()) : '',
isReturn:$('#isReturn').val(),
cateId:$('#cateId').val() ? JSON.stringify($('#cateId').val()).replace("[","").replace("]","") : '',
sendMaterialNO:$('#sendMaterialNO').val(),
produceCodeId:$('#produceCodeId').val(),
barCode:$('#barCode').val(),
returnBarCode:$('#returnBarCode').val(),
machiningBillNo:$('#machiningBillNo').val(),
intInstrumentNo:$('#intInstrumentNo').val(),
sendDateStart:$('#sendDateStart').val(),
sendDateEnd:$('#sendDateEnd').val(),
returnDateStart:$('#returnDateStart').val(),
returnDateEnd:$('#returnDateEnd').val(),
entryDateStart:$('#returnDateStart').val(),
entryDateEnd:$('#returnDateEnd').val()
};
},
// 返回false将会终止请求
columns : [
{
checkbox : true
},
{
title: '加工单号/定制工单号',
field: 'machiningBillNo'
},
{
title: '生产编号',
field: 'produceCodeId'
},
{
title: '单据类型',
field: 'billTypeName'
},
{
field: 'supplyName',
title: '供应商',
formatter: function (value, row, index) {
value = row.supplyName
if(value == null){
value ="";
}
var div = "<div style='width:100px;'>"+value+"</div>";//调列宽，在td中嵌套一个div，调整div大小
return div;
}
},
{
field: 'pkgOrBarCode',
title: '条码/包码'
},
{
field: 'cate',
title: '品类',
formatter : function(value, row, index) {
return '<div style="width:70px;">'+value+'</div>';
}
},
{
field: 'mainStoneInfo',
title: '主石信息',
formatter: function (value, row, index) {
value = row.mainStoneInfo
if(value == null){
value ="";
}
var div = "<div style='width:250px;'>"+value+"</div>";//调列宽，在td中嵌套一个div，调整div大小
return div;
}
},
// {
// field: 'secStoneInfo',
// title: '副石信息',
// },
{
field: 'intInstrumentNo',
title: '国际证书编号'
},
{
field: 'mainStonePrice',
title: '主石价'
},
{
field: 'cost',
title: '标准成本'
},
{
field: 'sendDate',
title: '发料日期'
},
{
field: 'sendMaterialNO',
title: '发料单号'
},
{
field: 'sendMainCount',
title: '发料主数量'
},{
field: 'sendSecCount',
title: '发料辅数量'
},
{
field: 'sendMaterialCost',
title: '发料成本'
},
{
field: 'returnDate',
title: '退料日期'
},
{
field: 'returnBillNo',
title: '退料单号'
},
{
field: 'rerunMainCount',
title: '退料数量'
},
{
field: 'returnWeight',
title: '退料重量'
},
{
field: 'returnCost',
title: '退料成本'
},
{
field: 'entryDate',
title: '回货日期'
},
{
field: 'entryBillNo',
title: '回货单号'
},
{
field: 'entryBarCode',
title: '成品条码'
},
{
field: 'entryMainCount',
title: '回货数量'
},
{
field: 'entryWeight',
title: '回货重量'
},
{
field: 'entryCost',
title: '回货成本'
},
{
field: 'entryCate',
title: '回货品类'
},
{
field: 'productName',
title: '产品名称'
},
{
field: 'materialInfo',
title: '材质信息'
},
{
field: 'returnMainStoneInfo',
title: '回货主石信息',
formatter: function (value, row, index) {
value = row.returnMainStoneInfo
if(value == null){
value ="";
}
var div = "<div style='width:250px;'>"+value+"</div>";//调列宽，在td中嵌套一个div，调整div大小
return div;
}
},
{
field: 'returnSecStoneInfo',
title: '回货副石信息',
formatter: function (value, row, index) {
value = row.returnSecStoneInfo
if(value == null){
value ="";
}
var div = "<div style='width:50px;'>"+value+"</div>";//调列宽，在td中嵌套一个div，调整div大小
return div;
}
},
{
field: 'isReturn',
title: '是否回货',
// formatter : function(value, row, index) {
// if(value == 1){
// return '全部回货';
// }else if(value == 2){
// return '未回货';
// }else{
// return '部分回货';
// }
// }
},
{
field:'supplyOutStockNo',
title: '供应商出货单号'
}
]
});
}
function sendReturnSum() {
$("#sendReturnTable").bootstrapTable('destroy');
$('#exampleTable').bootstrapTable('destroy');
$('#sendReturnTable').bootstrapTable({
method : 'post', // 服务器数据的请求方式 get or post
url : prefix + "/sendReturnSumList", // 服务器数据的加载地址
striped : true, // 设置为true会有隔行变色效果
dataType : "json", // 服务器返回的数据类型
pagination : true, // 设置为true会在底部显示分页条
// queryParamsType : "limit",
// //设置为limit则会发送符合RESTFull格式的参数
singleSelect : false, // 设置为true将禁止多选
iconSize : 'outline',
toolbar : '#exampleToolbar',
contentType : "application/x-www-form-urlencoded",
// //发送到服务器的数据编码类型
pageSize : 10, // 如果设置了分页，每页数据条数
pageNumber : 1, // 如果设置了分布，首页页码
search : false, // 是否显示搜索框
showColumns : false, // 是否显示内容下拉框（选择显示的列）
showRefresh: false, //是否显示刷新按钮
sidePagination : "server", // 设置在哪里进行分页，可选值为"client" 或者 "server"
queryParams : function(params) {
return {
// 说明：传入后台的参数包括offset开始索引，limit步长，sort排序列，order：desc或者,以及所有列的键值对
offset : params.offset,
limit : params.limit,
billTypeId:$('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]","") : '',
supplyId:$('#supplyId').val() ? JSON.stringify($('#supplyId').val()).replace("[","").replace("]","") : '',
// billTypeIds = $('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]",""):"";
// isReturn:$('#isReturn').val() ? JSON.stringify($('#isReturn').val()) : '',
isReturn:$('#isReturn').val(),
cateId:$('#cateId').val() ? JSON.stringify($('#cateId').val()).replace("[","").replace("]","") : '',
sendMaterialNO:$('#sendMaterialNO').val(),
produceCodeId:$('#produceCodeId').val(),
barCode:$('#barCode').val(),
returnBarCode:$('#returnBarCode').val(),
machiningBillNo:$('#machiningBillNo').val(),
intInstrumentNo:$('#intInstrumentNo').val(),
sendDateStart:$('#sendDateStart').val(),
sendDateEnd:$('#sendDateEnd').val(),
returnDateStart:$('#returnDateStart').val(),
returnDateEnd:$('#returnDateEnd').val(),
entryDateStart:$('#returnDateStart').val(),
entryDateEnd:$('#returnDateEnd').val()
};
},
// 返回false将会终止请求
columns : [
{
checkbox : true
},
{
title: '供应商',
field: 'supplyName'
},
{
title: '发料数量',
field: 'sendMainCount'
},
{
title: '发料重量',
field: 'sendSecCount'
},
{
field: 'backCount',
title: '退货数量'
},
{
field: 'backSecCount',
title: '退货重量'
},
{
field: 'entryCount',
title: '入库数量',
},
{
field: 'entrySecCount',
title: '入库重量',
},
{
field: 'remainCount',
title: '数量差异'
},
{
field: 'remainSec',
title: '重量差异'
},
{
field: 'sendResult',
title: '发料结果'
},
{
field: 'sendCountResult',
title: '数量结果'
},
{
field: 'sendSecResult',
title: '重量结果'
},
{
field: 'suggest',
title: '备注说明'
}
]
});
}
function reLoad() {
$('#sendReturnTable').bootstrapTable('destroy');
$('#exampleTable').bootstrapTable('refresh')
//$('#exampleTable').bootstrapTable('destroy');
}
function getExportList(){
// var Status=$(this).find('input[name= "MsgType"] ').val();
// var urlName="";
// if (Status == 0)
// {
// urlName="/getExportList";
// }
// else{
// urlName= "/getExportList";
// };
var index = layer.load(1, {
shade: [0.5,'#000'] //0.1透明度的背景
});
$.ajax({
type: "post",
url: prefix+"/getExportList",
// url: prefix+urlName,
data: {
billTypeId:$('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]","") : '',
supplyId:$('#supplyId').val() ? JSON.stringify($('#supplyId').val()).replace("[","").replace("]","") : '',
// billTypeIds = $('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]",""):"";
isReturn:$('#isReturn').val() ? JSON.stringify($('#isReturn').val()) : '',
cateId:$('#cateId').val() ? JSON.stringify($('#cateId').val()).replace("[","").replace("]","") : '',
sendMaterialNO:$('#sendMaterialNO').val(),
produceCodeId:$('#produceCodeId').val(),
barCode:$('#barCode').val(),
returnBarCode:$('#returnBarCode').val(),
machiningBillNo:$('#machiningBillNo').val(),
intInstrumentNo:$('#intInstrumentNo').val(),
sendDateStart:$('#sendDateStart').val(),
sendDateEnd:$('#sendDateEnd').val(),
returnDateStart:$('#returnDateStart').val(),
returnDateEnd:$('#returnDateEnd').val(),
entryDateStart:$('#returnDateStart').val(),
entryDateEnd:$('#returnDateEnd').val()
},
dataType: "JSON",
success: function (result) {
layer.close(index);
if(result.code == "000000"){
layer.alert("已启动异步导出，请到导出列表中进行查看并下载");
}else{
layer.alert(result.msg);
}
},
error: function () {
layer.close(index);
layer.alert("Error");
}
});
}
function getExportListSum(){
var index = layer.load(1, {
shade: [0.5,'#000'] //0.1透明度的背景
});
$.ajax({
type: "post",
url: prefix+"/getExportListSum",
//url: prefix+urlName,
data: {
billTypeId:$('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]","") : '',
supplyId:$('#supplyId').val() ? JSON.stringify($('#supplyId').val()).replace("[","").replace("]","") : '',
// billTypeIds = $('#billTypeId').val() ? JSON.stringify($('#billTypeId').val()).replace("[","").replace("]",""):"";
isReturn:$('#isReturn').val() ? JSON.stringify($('#isReturn').val()) : '',
cateId:$('#cateId').val() ? JSON.stringify($('#cateId').val()).replace("[","").replace("]","") : '',
sendMaterialNO:$('#sendMaterialNO').val(),
produceCodeId:$('#produceCodeId').val(),
barCode:$('#barCode').val(),
returnBarCode:$('#returnBarCode').val(),
machiningBillNo:$('#machiningBillNo').val(),
intInstrumentNo:$('#intInstrumentNo').val(),
sendDateStart:$('#sendDateStart').val(),
sendDateEnd:$('#sendDateEnd').val(),
returnDateStart:$('#returnDateStart').val(),
returnDateEnd:$('#returnDateEnd').val(),
entryDateStart:$('#returnDateStart').val(),
entryDateEnd:$('#returnDateEnd').val()
},
dataType: "JSON",
success: function (result) {
layer.close(index);
if(result.code == "000000"){
layer.alert("已启动异步导出，请到导出列表中进行查看并下载");
}else{
layer.alert(result.msg);
}
},
error: function () {
layer.close(index);
layer.alert("Error");
}
});
}
3、API
package com.dj.erp.microservice.warehouseservice.sendorreturnmaterial;
import com.dj.domain.salereport.SaleReturnReport;
import com.dj.domain.salereport.StorePerformance;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto;
import com.dj.domain.warehouse.ExportBill;
import com.dj.erp.config.FeignAuthConfig;
import com.dj.erp.domain.common.PageUtils;
import com.dj.erp.domain.common.ResponseResult;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import java.util.List;
import java.util.Map;
/**
* @Description SendMaterialReconciliationDto微服务接口
* @Author jinjian
* @Date 2021/3/22
* @Version 1.0
**/
@FeignClient(name="warehouseServer",configuration = FeignAuthConfig.class)
public interface SendMaterialReconciliationServiceAPI {
@RequestMapping("/sendMaterialReconciliation/getSendReturnList")
ResponseResult<PageUtils<SendMaterialReconciliationDto>> getSendReturnList(@RequestParam Map<String,Object> param);
// ResponseResult<SendMaterialReconciliationDto> getSendReturnList(@RequestParam Map<String,Object> param);
@RequestMapping("/sendMaterialReconciliation/sendReturnSumList")
ResponseResult<PageUtils<SendMaterialReconciliationDto>> sendReturnSumList(@RequestParam Map<String,Object> param);
// ResponseResult<SendMaterialReconciliationDto> getSendReturnList(@RequestParam Map<String,Object> param);
@RequestMapping("/sendMaterialReconciliation/getExportList")
ResponseResult<List<ExportBill>> getExportList(@RequestParam Map<String,Object> param);
@RequestMapping("/sendMaterialReconciliation/getExportListSum")
ResponseResult<List<ExportBill>> getExportListSum(@RequestParam Map<String,Object> param);
}
4、control
package com.dj.erp.sendorreturnmaterial;
import com.dj.constants.WebConstants;
import com.dj.domain.log.ExportFileLog;
import com.dj.domain.salereport.SaleReturnReport;
import com.dj.domain.salereport.StorePerformance;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto;
import com.dj.erp.common.controller.BaseController;
import com.dj.erp.domain.common.PageUtils;
import com.dj.erp.domain.common.ResponseResult;
import com.dj.erp.microservice.logservice.ExportFileServiceAPI;
import com.dj.erp.microservice.warehouseservice.salereport.SaleReturnReportServiceAPI;
import com.dj.erp.microservice.warehouseservice.sendorreturnmaterial.SendMaterialReconciliationServiceAPI;
import com.dj.erp.util.ParamUtil;
import com.dj.util.DateUtils;
import com.dj.util.JSONUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.concurrent.TimeUnit;
/**
* @Description sendMaterialReconciliation相关Controller
* @Author jinjian
* @Date 2021/3/22
* @Version 1.0
**/
@Controller
@RequestMapping("/sendMaterialReconciliation")
public class SendMaterialReconciliationController extends BaseController {
private static final Logger log = LoggerFactory.getLogger(SendMaterialReconciliationController.class);
@Autowired
SaleReturnReportServiceAPI saleReturnReportServiceAPI;
@Autowired
SendMaterialReconciliationServiceAPI sendMaterialReconciliationServiceAPI;
@Autowired
StringRedisTemplate redisTemplate;
@Autowired
ExportFileServiceAPI exportFileServiceAPI;
@GetMapping
String index(){
return "sendOrReturnMaterial/sendMaterialReconciliation/list";
}
/**
* 获取销SendMaterialReconciliation数据集合
* @param param
* @return
*/
@ResponseBody
@RequestMapping("/getSendReturnList")
PageUtils<SendMaterialReconciliationDto> getSendReturnList(@RequestParam Map<String,Object> param){
ParamUtil.dealParam(param);
return sendMaterialReconciliationServiceAPI.getSendReturnList(param).getData();
}
/**
* 获取销SendMaterialReconciliation数据集合
* @param param
* @return
*/
@ResponseBody
@RequestMapping("/sendReturnSumList")
PageUtils<SendMaterialReconciliationDto> sendReturnSumList(@RequestParam Map<String,Object> param){
ParamUtil.dealParam(param);
return sendMaterialReconciliationServiceAPI.sendReturnSumList(param).getData();
}
// /**
// * 获取SendMaterialReconciliation
// * @param param
// * @return
// */
// @ResponseBody
// @RequestMapping("/getSendReturnList")
// com.dj.util.ResponseResult<SendMaterialReconciliationDto> getSendReturnList(@RequestParam Map<String,Object> param){
// return sendMaterialReconciliationServiceAPI.getSendReturnList(param);
// }
/**
* 导出SendMaterialReconciliation
* @param param
* @return
*/
@ResponseBody
@RequestMapping("/getExportList")
ResponseResult getExportList(@RequestParam Map<String,Object> param){
ResponseResult result = new ResponseResult();
ParamUtil.dealParam(param);
try {
SimpleDateFormat fdate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
String fileName = "发料退料对账表" + fdate.format(new Date())+".xlsx";
ExportFileLog fileLog = new ExportFileLog();
fileLog.setCreateBy(super.getUserId().intValue());
fileLog.setFileName(fileName);
fileLog.setModel("发料退料对账表");
fileLog.setStatus(0);
fileLog.setCreateTime(DateUtils.format(new Date(),DateUtils.DATE_TIME_PATTERN));
ResponseResult<Integer> responseResult = exportFileServiceAPI.addExportFile(fileLog);
redisTemplate.opsForValue().set("sendReturnMaterial_export_"+responseResult.getData(),JSONUtils.beanToJson(param),5, TimeUnit.DAYS);
result.setCode(WebConstants.SUCCESS);
} catch (Exception e) {
log.error("SendMaterialReconciliationController-export：",e);
result.setCode(WebConstants.ERROR);
result.setMsg("导出失败");
}
return result;
}
/**
* 导出SendMaterialReconciliation
* @param param
* @return
*/
@ResponseBody
@RequestMapping("/getExportListSum")
ResponseResult getExportListSum(@RequestParam Map<String,Object> param){
ResponseResult result = new ResponseResult();
ParamUtil.dealParam(param);
try {
SimpleDateFormat fdate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
String fileName = "发料退料对账统计表" + fdate.format(new Date())+".xlsx";
ExportFileLog fileLog = new ExportFileLog();
fileLog.setCreateBy(super.getUserId().intValue());
fileLog.setFileName(fileName);
fileLog.setModel("发料退料对账统计表");
fileLog.setStatus(0);
fileLog.setCreateTime(DateUtils.format(new Date(),DateUtils.DATE_TIME_PATTERN));
ResponseResult<Integer> responseResult = exportFileServiceAPI.addExportFile(fileLog);
redisTemplate.opsForValue().set("sendReturnMaterialSum_export_"+responseResult.getData(),JSONUtils.beanToJson(param),5, TimeUnit.DAYS);
result.setCode(WebConstants.SUCCESS);
} catch (Exception e) {
log.error("SendMaterialReconciliationController-export：",e);
result.setCode(WebConstants.ERROR);
result.setMsg("导出失败");
}
return result;
}
}
二、微服务
1、实体
package com.dj.domain.sendorreturnmaterial.dto;
import com.dj.domain.StyleMaterialStone;
import com.dj.domain.sendorreturnmaterial.SendMaterialBillMaterial;
import com.dj.domain.sendorreturnmaterial.SendMaterialProduce;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.io.Serializable;
import java.util.List;
/**
* sendMaterialReconciliation实体对象
* getSendReturnList
* @author jinjian
* @Date 2018-11-1
*/
public class SendMaterialReconciliationDto implements Serializable {
private static final long serialVersionUID = 966778587427484638L;
/** 主键id */
private Integer id;
/** 加工单id/定制工单id */
private Integer machiningId;
/** 加工单号/定制工单号*/
private String sourceBillNo;
/**来源单类型，1：加工单 2：定制工单*/
private Integer sourceBillType;
/**加工单单据类型名称*/
private String machiningBillTypeName;
/** 发料单号 */
private String billNo;
/** 单据类型id */
private String billTypeId;
/** 单据类型名称*/
private String billTypeName;
/** 供应商id,参照tb_supplier中ID */
private String supplyId;
private String returnBillNo;
/**供应商名称*/
private String supplyName;
/** 发料日期 */
private String sendDate;
/** 单据状态：1:待审核,2:已审核,3:已作废 */
private Integer state;
/** 备注 */
private String remark;
/** 录单人 */
private Integer createBy;
/**录单人名称*/
private String createName;
/** 录单时间 */
private String createTime;
/** 录单时间 */
private String sendDateStart;
/** 录单时间 */
private String sendDateEnd;
private String entryDateStart;
/** 录单时间 */
private String entryDateEnd;
/** 录单时间 */
private String returnDateStart;
/** 录单时间 */
private String returnDateEnd;
/** 审批人 */
private Integer checkBy;
/**审批人名称*/
private String checkName;
/** 审批时间 */
private String checkTime;
/** 更新人 */
private Integer updateBy;
/** 更新时间 */
private String updateTime;
/**发料主数量汇总*/
private Double sumMainCount;
/**发料辅数量汇总*/
private Double sumSecCount;
/**
* 来源单位定制工单时，定制工单的类型，1：定制销售单 2：售后订单
*/
private Integer orderType;
private int offset; //第几页
private int limit; //每页条数
private String machiningBillNo;
private String produceCodeId;
private String pkgOrBarCode;
private String cate;
private String cateId;
private String productName;
private String materialInfo;
private String mainStoneInfo;
private String secStoneInfo;
private String intInstrumentNo;
private String mainStonePrice;
private String cost;
private String sendMaterialNO;
private String sendMainCount;
private String sendSecCount;
private String sendMaterialCost;
private String count;
private String weight;
private String returnMainCount;
private String returnWeight;
private String returnBarCode;
private String returnCost;
private String returnMainStoneInfo;
private String returnSecStoneInfo;
private String isReturn;
private String supplyOutStockNo;
private String sendMaterialId;
private String machiningBillId;
private String mainCount;
private String secCount;
private String skuId;
private String barCode;
private String pkgCode;
private String returnPkgCode;
private String backPkgCode;
private String backBarCode;
private String backCount;
private String backSecCount;
private String entryCount;
private String entrySecCount;
private String remainCount;
private String remainSec;
private String sendResult;
private String sendCountResult;
private String sendSecResult;
private String entryBillNo;
private String entryBarCode;
private String entryMainCount;
private String entryWeight;
private String entryCost;
private String entryCate;
public String getSendDateStart() {
return sendDateStart;
}
public void setSendDateStart(String sendDateStart) {
this.sendDateStart = sendDateStart;
}
public String getSendDateEnd() {
return sendDateEnd;
}
public void setSendDateEnd(String sendDateEnd) {
this.sendDateEnd = sendDateEnd;
}
public String getReturnDateStart() {
return returnDateStart;
}
public void setReturnDateStart(String returnDateStart) {
this.returnDateStart = returnDateStart;
}
public String getReturnDateEnd() {
return returnDateEnd;
}
public void setReturnDateEnd(String returnDateEnd) {
this.returnDateEnd = returnDateEnd;
}
public String getCateId() {
return cateId;
}
public void setCateId(String cateId) {
this.cateId = cateId;
}
public int getOffset() {
return offset;
}
public void setOffset(int offset) {
this.offset = offset;
}
public int getLimit() {
return limit;
}
public void setLimit(int limit) {
this.limit = limit;
}
public String getEntryDateStart() {
return entryDateStart;
}
public void setEntryDateStart(String entryDateStart) {
this.entryDateStart = entryDateStart;
}
public String getEntryDateEnd() {
return entryDateEnd;
}
public void setEntryDateEnd(String entryDateEnd) {
this.entryDateEnd = entryDateEnd;
}
public String getBackCount() {
return backCount;
}
public void setBackCount(String backCount) {
this.backCount = backCount;
}
public String getBackSecCount() {
return backSecCount;
}
public void setBackSecCount(String backSecCount) {
this.backSecCount = backSecCount;
}
public String getEntryCount() {
return entryCount;
}
public void setEntryCount(String entryCount) {
this.entryCount = entryCount;
}
public String getEntrySecCount() {
return entrySecCount;
}
public void setEntrySecCount(String entrySecCount) {
this.entrySecCount = entrySecCount;
}
public String getRemainCount() {
return remainCount;
}
public void setRemainCount(String remainCount) {
this.remainCount = remainCount;
}
public String getRemainSec() {
return remainSec;
}
public void setRemainSec(String remainSec) {
this.remainSec = remainSec;
}
public String getSendResult() {
return sendResult;
}
public void setSendResult(String sendResult) {
this.sendResult = sendResult;
}
public String getSendCountResult() {
return sendCountResult;
}
public void setSendCountResult(String sendCountResult) {
this.sendCountResult = sendCountResult;
}
public String getSendSecResult() {
return sendSecResult;
}
public void setSendSecResult(String sendSecResult) {
this.sendSecResult = sendSecResult;
}
private String entryDate;
public String getReturnBillNo() {
return returnBillNo;
}
public void setReturnBillNo(String returnBillNo) {
this.returnBillNo = returnBillNo;
}
public String getEntryDate() {
return entryDate;
}
public void setEntryDate(String entryDate) {
this.entryDate = entryDate;
}
public String getEntryBillNo() {
return entryBillNo;
}
public void setEntryBillNo(String entryBillNo) {
this.entryBillNo = entryBillNo;
}
public String getEntryBarCode() {
return entryBarCode;
}
public void setEntryBarCode(String entryBarCode) {
this.entryBarCode = entryBarCode;
}
public String getEntryMainCount() {
return entryMainCount;
}
public void setEntryMainCount(String entryMainCount) {
this.entryMainCount = entryMainCount;
}
public String getEntryWeight() {
return entryWeight;
}
public void setEntryWeight(String entryWeight) {
this.entryWeight = entryWeight;
}
public String getEntryCost() {
return entryCost;
}
public void setEntryCost(String entryCost) {
this.entryCost = entryCost;
}
public String getEntryCate() {
return entryCate;
}
public void setEntryCate(String entryCate) {
this.entryCate = entryCate;
}
public String getReturnPkgCode() {
return returnPkgCode;
}
public void setReturnPkgCode(String returnPkgCode) {
this.returnPkgCode = returnPkgCode;
}
public String getBackPkgCode() {
return backPkgCode;
}
public void setBackPkgCode(String backPkgCode) {
this.backPkgCode = backPkgCode;
}
public String getBackBarCode() {
return backBarCode;
}
public void setBackBarCode(String backBarCode) {
this.backBarCode = backBarCode;
}
private String returnCount;
public void setProduceCodeId(String produceCodeId) {
this.produceCodeId = produceCodeId;
}
public void setPkgOrBarCode(String pkgOrBarCode) {
this.pkgOrBarCode = pkgOrBarCode;
}
public void setCate(String cate) {
this.cate = cate;
}
public void setProductName(String productName) {
this.productName = productName;
}
public void setMaterialInfo(String materialInfo) {
this.materialInfo = materialInfo;
}
public void setMainStoneInfo(String mainStoneInfo) {
this.mainStoneInfo = mainStoneInfo;
}
public void setSecStoneInfo(String secStoneInfo) {
this.secStoneInfo = secStoneInfo;
}
public void setIntInstrumentNo(String intInstrumentNo) {
this.intInstrumentNo = intInstrumentNo;
}
public void setMainStonePrice(String mainStonePrice) {
this.mainStonePrice = mainStonePrice;
}
public void setCost(String cost) {
this.cost = cost;
}
public void setSendMaterialNO(String sendMaterialNO) {
this.sendMaterialNO = sendMaterialNO;
}
public void setSendMainCount(String sendMainCount) {
this.sendMainCount = sendMainCount;
}
public void setSendSecCount(String sendSecCount) {
this.sendSecCount = sendSecCount;
}
public void setSendMaterialCost(String sendMaterialCost) {
this.sendMaterialCost = sendMaterialCost;
}
public void setCount(String count) {
this.count = count;
}
public void setWeight(String weight) {
this.weight = weight;
}
public void setReturnMainCount(String returnMainCount) {
this.returnMainCount = returnMainCount;
}
public void setReturnWeight(String returnWeight) {
this.returnWeight = returnWeight;
}
public void setReturnBarCode(String returnBarCode) {
this.returnBarCode = returnBarCode;
}
public void setReturnCost(String returnCost) {
this.returnCost = returnCost;
}
public void setReturnMainStoneInfo(String returnMainStoneInfo) {
this.returnMainStoneInfo = returnMainStoneInfo;
}
public void setReturnSecStoneInfo(String returnSecStoneInfo) {
this.returnSecStoneInfo = returnSecStoneInfo;
}
public void setIsReturn(String isReturn) {
this.isReturn = isReturn;
}
public void setSupplyOutStockNo(String supplyOutStockNo) {
this.supplyOutStockNo = supplyOutStockNo;
}
public void setSendMaterialId(String sendMaterialId) {
this.sendMaterialId = sendMaterialId;
}
public void setMachiningBillId(String machiningBillId) {
this.machiningBillId = machiningBillId;
}
public void setMainCount(String mainCount) {
this.mainCount = mainCount;
}
public void setSecCount(String secCount) {
this.secCount = secCount;
}
public void setSkuId(String skuId) {
this.skuId = skuId;
}
public void setBarCode(String barCode) {
this.barCode = barCode;
}
public void setPkgCode(String pkgCode) {
this.pkgCode = pkgCode;
}
public void setReturnCount(String returnCount) {
this.returnCount = returnCount;
}
public String getMachiningBillNo() {
return machiningBillNo;
}
public String getProduceCodeId() {
return produceCodeId;
}
public String getPkgOrBarCode() {
return pkgOrBarCode;
}
public String getCate() {
return cate;
}
public String getProductName() {
return productName;
}
public String getMaterialInfo() {
return materialInfo;
}
public String getMainStoneInfo() {
return mainStoneInfo;
}
public String getSecStoneInfo() {
return secStoneInfo;
}
public String getIntInstrumentNo() {
return intInstrumentNo;
}
public String getMainStonePrice() {
return mainStonePrice;
}
public String getCost() {
return cost;
}
public String getSendMaterialNO() {
return sendMaterialNO;
}
public String getSendMainCount() {
return sendMainCount;
}
public String getSendSecCount() {
return sendSecCount;
}
public String getSendMaterialCost() {
return sendMaterialCost;
}
public String getCount() {
return count;
}
public String getWeight() {
return weight;
}
public String getReturnMainCount() {
return returnMainCount;
}
public String getReturnWeight() {
return returnWeight;
}
public String getReturnBarCode() {
return returnBarCode;
}
public String getReturnCost() {
return returnCost;
}
public String getReturnMainStoneInfo() {
return returnMainStoneInfo;
}
public String getReturnSecStoneInfo() {
return returnSecStoneInfo;
}
public String getIsReturn() {
return isReturn;
}
public String getSupplyOutStockNo() {
return supplyOutStockNo;
}
public String getSendMaterialId() {
return sendMaterialId;
}
public String getMachiningBillId() {
return machiningBillId;
}
public String getMainCount() {
return mainCount;
}
public String getSecCount() {
return secCount;
}
public String getSkuId() {
return skuId;
}
public String getBarCode() {
return barCode;
}
public String getPkgCode() {
return pkgCode;
}
public String getReturnCount() {
return returnCount;
}
public void setMachiningBillNo(String machiningBillNo) {
this.machiningBillNo = machiningBillNo;
}
public static long getSerialVersionUID() {
return serialVersionUID;
}
/**发料清单集合*/
private List<SendMaterialBillMaterial> sendMaterialBillMaterialList;
/**加工单货品集合*/
private List<SendMaterialProduce> sendMaterialProduceList;
public Integer getSourceBillType() {
return sourceBillType;
}
public void setSourceBillType(Integer sourceBillType) {
this.sourceBillType = sourceBillType;
}
public List<SendMaterialBillMaterial> getSendMaterialBillMaterialList() {
return sendMaterialBillMaterialList;
}
public void setSendMaterialBillMaterialList(List<SendMaterialBillMaterial> sendMaterialBillMaterialList) {
this.sendMaterialBillMaterialList = sendMaterialBillMaterialList;
}
public List<SendMaterialProduce> getSendMaterialProduceList() {
return sendMaterialProduceList;
}
public void setSendMaterialProduceList(List<SendMaterialProduce> sendMaterialProduceList) {
this.sendMaterialProduceList = sendMaterialProduceList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getMachiningId() {
return machiningId;
}
public void setMachiningId(Integer machiningId) {
this.machiningId = machiningId;
}
public String getBillNo() {
return billNo;
}
public void setBillNo(String billNo) {
this.billNo = billNo == null ? null : billNo.trim();
}
public String getBillTypeId() {
return billTypeId;
}
public void setBillTypeId(String billTypeId) {
this.billTypeId = billTypeId;
}
public String getSupplyId() {
return supplyId;
}
public void setSupplyId(String supplyId) {
this.supplyId = supplyId;
}
public String getSendDate() {
return sendDate;
}
public void setSendDate(String sendDate) {
this.sendDate = sendDate;
}
public Integer getState() {
return state;
}
public void setState(Integer state) {
this.state = state;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark == null ? null : remark.trim();
}
public Integer getCreateBy() {
return createBy;
}
public void setCreateBy(Integer createBy) {
this.createBy = createBy;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public Integer getCheckBy() {
return checkBy;
}
public void setCheckBy(Integer checkBy) {
this.checkBy = checkBy;
}
public String getCheckTime() {
return checkTime;
}
public void setCheckTime(String checkTime) {
this.checkTime = checkTime;
}
public Integer getUpdateBy() {
return updateBy;
}
public void setUpdateBy(Integer updateBy) {
this.updateBy = updateBy;
}
public String getUpdateTime() {
return updateTime;
}
public void setUpdateTime(String updateTime) {
this.updateTime = updateTime;
}
public String getSourceBillNo() {
return sourceBillNo;
}
public void setSourceBillNo(String sourceBillNo) {
this.sourceBillNo = sourceBillNo;
}
public String getBillTypeName() {
return billTypeName;
}
public void setBillTypeName(String billTypeName) {
this.billTypeName = billTypeName;
}
public String getSupplyName() {
return supplyName;
}
public void setSupplyName(String supplyName) {
this.supplyName = supplyName;
}
public String getCreateName() {
return createName;
}
public void setCreateName(String createName) {
this.createName = createName;
}
public String getCheckName() {
return checkName;
}
public void setCheckName(String checkName) {
this.checkName = checkName;
}
public String getMachiningBillTypeName() {
return machiningBillTypeName;
}
public void setMachiningBillTypeName(String machiningBillTypeName) {
this.machiningBillTypeName = machiningBillTypeName;
}
public Double getSumMainCount() {
return sumMainCount;
}
public void setSumMainCount(Double sumMainCount) {
this.sumMainCount = sumMainCount;
}
public Double getSumSecCount() {
return sumSecCount;
}
public void setSumSecCount(Double sumSecCount) {
this.sumSecCount = sumSecCount;
}
public Integer getOrderType() {
return orderType;
}
public void setOrderType(Integer orderType) {
this.orderType = orderType;
}
public void setStyleMaterialStone(StyleMaterialStone materialStone) {
}
}
2、control
package com.derier.warehouseservice.controller.sendorreturnmaterial;
import com.derier.warehouseservice.service.sendorreturnmaterial.ISendMaterialReconciliationService;
import com.dj.constants.WebConstants;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountReq;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountResp;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto;
import com.dj.domain.warehouse.ExportBill;
import com.dj.erp.domain.common.PageUtils;
import com.dj.util.ResponseResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
import java.util.Map;
/**
* @Description sendMaterialReconciliation相关Controller
* @Author jinjian
* @Date 2021/3/22
* @Version 1.0
**/
@Controller
@RequestMapping("/sendMaterialReconciliation")
public class SendMaterialReconciliationController {
private static final Logger log = LoggerFactory.getLogger(SendMaterialReconciliationController.class);
@Autowired
ISendMaterialReconciliationService sendMaterialReconciliationService;
/**
* 获取sendMaterialReconciliationBill列表
* @param sendMaterialReconciliationBill
* @return
*/
@ResponseBody
@RequestMapping("/getSendReturnList")
ResponseResult<PageUtils<SendMaterialReconciliationDto>> getSendReturnList( SendMaterialReconciliationDto sendMaterialReconciliationBill){
//ResponseResult getSendReturnList(@RequestBody SendMaterialReconciliationDto sendMaterialReconciliationBill){
PageUtils<SendMaterialReconciliationDto> result;
try {
result = sendMaterialReconciliationService.getSendReturnList(sendMaterialReconciliationBill);
return new ResponseResult<>(WebConstants.SUCCESS, "成功", result);
} catch (Exception e) {
log.error("SendMaterialReconciliationController-getSendReturnList error：",e);
e.printStackTrace();
return new ResponseResult<PageUtils<SendMaterialReconciliationDto>>(WebConstants.ERROR,e.getMessage(),null);
}
}
/**
* 获取sendMaterialReconciliationBill列表sendReturnSumList
* @param sendMaterialReconciliationBill
* @return
*/
@ResponseBody
@RequestMapping("/sendReturnSumList")
ResponseResult<PageUtils<SendMaterialReconciliationDto>> sendReturnSumList( SendMaterialReconciliationDto sendMaterialReconciliationBill){
//ResponseResult getSendReturnList(@RequestBody SendMaterialReconciliationDto sendMaterialReconciliationBill){
PageUtils<SendMaterialReconciliationDto> result;
try {
result = sendMaterialReconciliationService.sendReturnSumList(sendMaterialReconciliationBill);
return new ResponseResult<>(WebConstants.SUCCESS, "成功", result);
} catch (Exception e) {
log.error("SendMaterialReconciliationController-sendReturnSumList error：",e);
e.printStackTrace();
return new ResponseResult<PageUtils<SendMaterialReconciliationDto>>(WebConstants.ERROR,e.getMessage(),null);
}
}
/**
* 导出查询
* @param param
* @return
*/
@ResponseBody
@RequestMapping("/getExportList")
com.dj.erp.domain.common.ResponseResult<List<ExportBill>> getExportList(@RequestParam Map<String,Object> param){
com.dj.erp.domain.common.ResponseResult result = new com.dj.erp.domain.common.ResponseResult();
result.setData(sendMaterialReconciliationService.getExportList(param));
result.setMsg("操作成功");
result.setCode(WebConstants.SUCCESS);
return result;
}
/**
* 导出查询
* @param param
* @return
*/
@ResponseBody
@RequestMapping("/exportSendBill")
com.dj.erp.domain.common.ResponseResult<List<ExportBill>> exportSendBill(@RequestParam Map<String, Object> param){
com.dj.erp.domain.common.ResponseResult<List<ExportBill>> result = new com.dj.erp.domain.common.ResponseResult<List<ExportBill>>();
result.setData(sendMaterialReconciliationService.exportSendBill(param));
result.setCode(WebConstants.SUCCESS);
return result;
}
/**
* 发料统计对账明细
* @param req
* @return
*/
@ResponseBody
@RequestMapping("/sendReturnMaterial")
com.dj.erp.domain.common.ResponseResult sendReturnMaterial(@RequestBody SendMaterialCountReq req){
SendMaterialCountResp resp;
try {
resp = sendMaterialReconciliationService.sendReturnMaterial(req,false);
return new com.dj.erp.domain.common.ResponseResult<>(WebConstants.SUCCESS,"成功",resp);
} catch (Exception e) {
e.printStackTrace();
return new com.dj.erp.domain.common.ResponseResult<>(WebConstants.ERROR,"失败",e);
}
}
/**
* 发料统计对账统计
* @param req
* @return
*/
@ResponseBody
@RequestMapping("/sendReturnMaterialSum")
com.dj.erp.domain.common.ResponseResult sendReturnMaterialSum(@RequestBody SendMaterialCountReq req){
SendMaterialCountResp resp;
try {
resp = sendMaterialReconciliationService.sendReturnMaterialSum(req,false);
return new com.dj.erp.domain.common.ResponseResult<>(WebConstants.SUCCESS,"成功",resp);
} catch (Exception e) {
e.printStackTrace();
return new com.dj.erp.domain.common.ResponseResult<>(WebConstants.ERROR,"失败",e);
}
}
}
3、接口定义
package com.derier.warehouseservice.service.sendorreturnmaterial;
import com.dj.domain.order.sales.SalesReturnBill;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountReq;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountResp;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto;
import com.dj.domain.sendorreturnmaterial.dto.SumSendMaterialReconciliationDTO;
import com.dj.domain.warehouse.ExportBill;
import com.dj.erp.domain.common.PageUtils;
import java.util.List;
import java.util.Map;
/**
* @Description SendMaterialReconciliation服务接口类
* @Author jinjian
* @Date 2021/3/22
* @Version 1.0
**/
public interface ISendMaterialReconciliationService {
List<Map<String, Object>> getList(Map<String,Object> param);
SumSendMaterialReconciliationDTO getCount(Map<String,Object> param);
/**
* 获取sendMaterialReconciliationBill列表 sendReturnSumList
* @param sendMaterialReconciliationBill
* @return
*/
PageUtils<SendMaterialReconciliationDto> getSendReturnList(SendMaterialReconciliationDto sendMaterialReconciliationBill);
/**
* 获取sendMaterialReconciliationBill列表 sendReturnSumList
* @param sendMaterialReconciliationBill
* @return
*/
PageUtils<SendMaterialReconciliationDto> sendReturnSumList(SendMaterialReconciliationDto sendMaterialReconciliationBill);
/**
* SendMaterialReconciliationDto导出查询
* @param param
* @return
*/
List<ExportBill> getExportList(Map<String,Object> param);
List<ExportBill> exportSendBill(Map<String,Object> param);
SendMaterialCountResp sendReturnMaterial(SendMaterialCountReq req, boolean isExport);
SendMaterialCountResp sendReturnMaterialSum(SendMaterialCountReq req, boolean isExport);
}
4、接口实现
package com.derier.warehouseservice.service.sendorreturnmaterial.impl;
import com.derier.warehouseservice.dao.salereport.SaleReturnReportDao;
import com.derier.warehouseservice.dao.salereport.StorePerformanceDao;
import com.derier.warehouseservice.dao.sendorreturnmaterial.SendMaterialReconciliationDao;
import com.derier.warehouseservice.microservice.LogServiceAPI;
import com.derier.warehouseservice.service.sendorreturnmaterial.ISendMaterialReconciliationService;
import com.derier.warehouseservice.util.SkuMaterialUtil;
import com.dj.constants.WebConstants;
import com.dj.domain.StyleMaterialStone;
import com.dj.domain.order.sales.SalesReturnBill;
import com.dj.domain.sendorreturnmaterial.dto.*;
import com.dj.domain.warehouse.ExportBill;
import com.dj.erp.domain.common.PageUtils;
import com.dj.erp.domain.common.ResponseResult;
import com.dj.util.ListUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @Description SendMaterialReconciliation服务实现类
* @Author gj
* @Date 2019/3/22
* @Version 1.0
**/
@Service
public class SendMaterialReconciliationService implements ISendMaterialReconciliationService {
@Autowired
SendMaterialReconciliationDao sendMaterialReconciliationDao;
@Autowired
SkuMaterialUtil skuMaterialUtil;
@Autowired
StorePerformanceDao storePerformanceDao;
@Autowired
LogServiceAPI logServiceAPI;
@Override
public PageUtils<SendMaterialReconciliationDto> getSendReturnList(SendMaterialReconciliationDto sendMaterialReconciliationBill) {
PageUtils<SendMaterialReconciliationDto> result = new PageUtils<>();
List<SendMaterialReconciliationDto> rows = null;
int count = sendMaterialReconciliationDao.count(sendMaterialReconciliationBill);
if(count > 0) {
rows = sendMaterialReconciliationDao.getSendReturnList(sendMaterialReconciliationBill);
}
result.setRows(rows);
result.setTotal(count);
return result;
}
@Override
public PageUtils<SendMaterialReconciliationDto> sendReturnSumList(SendMaterialReconciliationDto sendMaterialReconciliationBill) {
PageUtils<SendMaterialReconciliationDto> result = new PageUtils<>();
List<SendMaterialReconciliationDto> rows = null;
int count = sendMaterialReconciliationDao.sumCount(sendMaterialReconciliationBill);
if(count > 0) {
rows = sendMaterialReconciliationDao.sendReturnSumList(sendMaterialReconciliationBill);
}
result.setRows(rows);
result.setTotal(count);
return result;
}
@Override
public List<ExportBill> getExportList(Map<String, Object> param) {
List<ExportBill> list = sendMaterialReconciliationDao.getExportList(param);
System.out.println(list+"查询list");
if(list.size()>0){
StyleMaterialStone materialStone = null;
for(ExportBill temp:list){
materialStone = skuMaterialUtil.queryStyleStoneParam(temp.getSkuId());
if (materialStone != null) {
temp.setStyleMaterialStone(materialStone);
} else {
temp.setStyleMaterialStone(new StyleMaterialStone());
}
}
System.out.println(list+"list");
}
return list;
}
/**
* 获取发料退料数据集合
*
* @param param
* @return
*/
@Override
public List<ExportBill> exportSendBill(Map<String, Object> param) {
List<ExportBill> exportSendBillList = sendMaterialReconciliationDao.exportSendBill(param);
System.out.println(exportSendBillList+"查询exportSendBill");
if(exportSendBillList.size()>0){
StyleMaterialStone materialStone = null;
for(ExportBill temp:exportSendBillList){
materialStone = skuMaterialUtil.queryStyleStoneParam(temp.getSkuId());
if (materialStone != null) {
temp.setStyleMaterialStone(materialStone);
} else {
temp.setStyleMaterialStone(new StyleMaterialStone());
}
}
System.out.println(exportSendBillList+"exportSendBillList");
}
return exportSendBillList;
}
@Override
public SendMaterialCountResp sendReturnMaterial(SendMaterialCountReq req,boolean isExport) {
SendMaterialCountResp resp = new SendMaterialCountResp();
BigDecimal b;
//发料数量
double sendMaterialCount = 0d;
//已回货数量
double alreadyReturnCount = 0d;
//未回货数量
double notReturnCount = 0d;
//发料总价值
double sendMaterialPrice = 0d;
//未回货总价值
double notReturnPrice = 0d;
//未回货货品的标准成本
double notReturnCost = 0d;
//部分回货货品的标准成本
double partReturnCost = 0d;
//查询获取发料统计列表
List<SendMaterialCountDto> list = sendMaterialReconciliationDao.sendReturnMaterial(req);
List<SendMaterialCountDto> listDelivery = sendMaterialReconciliationDao.sendReturnMaterial(req);
StyleMaterialStone materialStone;
StyleMaterialStone materialStoneEntry;
double returnMainCountTemp = 0;
Map<String,Object> param = new HashMap<>();
List<String> supplyOutNoList = null;
//循环记录累计数量、查询是否回货
for(SendMaterialCountDto d : list){
//查询下游单->已审批退料单中该明细条码/包码的总数量
String[] returnCount = d.getReturnCount().split("#");
double returnMainCount = Double.parseDouble(returnCount[0]);
double returnSecCount = Double.parseDouble(returnCount[1]);
//Map<String,BigDecimal> returnMaterialMap = returnMaterialBillMaterialDao.getReturnCount(d.getSendMaterialId(),d.getPkgOrBarCode());
//查询加工单->已审批加工入库单中主石条码中该明细条码/包码的总数量
//Map<String,BigDecimal> returnMachiningMap = machiningEntryBillMaterialDao.getReturnCount(d.getMachiningBillId(),d.getPkgOrBarCode());
String[] entryCount = null;
if (d.getEntryCount() != null){
entryCount = d.getEntryCount().split("#");
}
double entryMainCount = entryCount == null ? 0d : Double.parseDouble(entryCount[0]);
double entrySecCount = entryCount == null ? 0d : Double.parseDouble(entryCount[1]);
double entryMainStonePrice = entryCount == null ? 0d : Double.parseDouble(entryCount[2]);
String entrySku = entryCount == null ? null : entryCount[3];
String returnBarCode = entryCount == null ? null : entryCount[4];
returnMainCountTemp = entryMainCount + returnMainCount;
//设置回货数量-> 加工入库单自有条码数量之和
d.setReturnMainCount(entryMainCount);
//设置回货重量 -> 加工入库单自有条码重量之和
d.setReturnWeight(entrySecCount);
//设置退货数量
d.setCount(returnMainCount);
//设置退货重量
d.setWeight(returnSecCount);
//设置回货状态
//全部回货->回货数量大于等于发料数量
if(returnMainCountTemp >= d.getSendMainCount()){
d.setIsReturn(1);
}
//未回货->回货数量为0
else if(returnMainCountTemp == 0){
d.setIsReturn(2);
if(req.getIsReturn() == null || (req.getIsReturn().intValue() == d.getIsReturn().intValue())) {
notReturnCost += d.getCost();
}
}
//部分回货->回货数量大于0，且小于发料数量
else{
d.setIsReturn(3);
//包含是否回货查询条件则不累计部门回货成本->查询条件只有‘是’、‘否’
if(req.getIsReturn() == null) {
//部分回货成本 = 回货成本*剩余回货数量(发料数量-回货数量)/发料数量
partReturnCost += (d.getSendMainCount() != 0 ? (d.getCost() * (d.getSendMainCount() - returnMainCountTemp) / d.getSendMainCount()) : 0d);
}
}
//发料成本：如果pp_send_material_bill_material表中bar_code不为空，发料成本=辅数量*主石价
if (StringUtils.isNotBlank(d.getBarCode())){
d.setSendMaterialCost(Double.parseDouble(String.format("%.3f",d.getSecCount() * (d.getMainStonePrice() == null ? 0 : d.getMainStonePrice()))));
}else{
//如果pp_send_material_bill_material表中pkg_code不为空，发料成本=辅数量/主数量*主石价
d.setSendMaterialCost(Double.parseDouble(String.format("%.3f",(d.getSecCount()/d.getMainCount()) * (d.getMainStonePrice() == null ? 0 : d.getMainStonePrice()))));
}
//回货成本：pp_machining_entry_bill_material表中self1_stone_weight*main_stone_price + self2_stone_weight*main_stone_price
if (d.getIsReturn() != 2){
d.setReturnCost(Double.parseDouble(String.format("%.3f",entrySecCount * entryMainStonePrice)));
}
if (entrySku != null){
materialStoneEntry = skuMaterialUtil.queryStyleStoneParam(entrySku);
d.setReturnMainStoneInfo(materialStoneEntry.getMainStoneParam());
d.setReturnSecStoneInfo(materialStoneEntry.getSecStoneParam());
}
d.setReturnBarCode(returnBarCode);
//统计过滤是否回货查询条件
if(req.getIsReturn() == null || (req.getIsReturn().intValue() == d.getIsReturn().intValue())) {
//导出则查询sku信息，页面展示查询不在循环查询->已改为前端当前页查询
if(isExport && StringUtils.isNotBlank(d.getSkuId())) {
//跟据skuId获取主副石等信息
materialStone = skuMaterialUtil.queryStyleStoneParam(d.getSkuId());
//设置列表展示信息
d.setCateId(materialStone.getCateId());
d.setCate(materialStone.getCateName());
d.setProductName(materialStone.getStyleName());
d.setMaterialInfo(materialStone.getStyleMaterialParam());
d.setMainStoneInfo(materialStone.getMainStoneParam());
d.setSecStoneInfo(materialStone.getSecStoneParam());
}
//累计各数量
//发料数量：查询结果中所有发料数量之和
sendMaterialCount += d.getSendMainCount();
//已回货数量：查询结果中所有回货数量之和
alreadyReturnCount += returnMainCountTemp;
//发料总价值：查询结果中的标准成本之和
sendMaterialPrice += d.getCost();
}
//查询供应商出库单号
param.clear();
param.put("machiningBillId",d.getMachiningBillId());
param.put("pkgOrBarCode",d.getPkgOrBarCode());
supplyOutNoList = sendMaterialReconciliationDao.getSupplyOutStockNo(param);
d.setSupplyOutStockNo(ListUtil.ListToString(supplyOutNoList,';'));
}
if (listDelivery != null && listDelivery.size() !=0){
for(SendMaterialCountDto d : listDelivery){
//查询下游单->已审批退料单中该明细条码/包码的总数量
double returnCost = d.getReturnCost();
double returnMainCount = d.getReturnMainCount();
double returnSecCount = d.getReturnSecCount();
returnMainCountTemp = returnMainCount;
//设置回货数量-> 加工入库单自有条码数量之和
d.setReturnMainCount(returnMainCount);
//设置回货重量 -> 加工入库单自有条码重量之和
d.setReturnWeight(returnSecCount);
//设置退货数量
d.setCount(0d);
//设置退货重量
d.setWeight(0d);
//设置回货状态
//全部回货->回货数量大于等于发料数量
if(returnMainCountTemp >= d.getSendMainCount()){
d.setIsReturn(1);
}
//未回货->回货数量为0
else if(returnMainCountTemp == 0){
d.setIsReturn(2);
if(req.getIsReturn() == null || (req.getIsReturn().intValue() == d.getIsReturn().intValue())) {
notReturnCost += d.getCost();
}
}
//部分回货->回货数量大于0，且小于发料数量
else{
d.setIsReturn(3);
//包含是否回货查询条件则不累计部门回货成本->查询条件只有‘是’、‘否’
if(req.getIsReturn() == null) {
//部分回货成本 = 回货成本*剩余回货数量(发料数量-回货数量)/发料数量
//partReturnCost += (d.getSendMainCount() != 0 ? (d.getCost() != null ? d.getCost() : 0d * (d.getSendMainCount() != null ? d.getSendMainCount() : 0d - returnMainCountTemp) / d.getSendMainCount()) : 0d);
//partReturnCost += (null != d.getCost() || d.getCost() != 0 ? d.getCost() : 0) - (d.getReturnCost() != 0 || d.getReturnCost() != null ? d.getReturnCost() : 0);
partReturnCost += (null == d.getCost() ? 0 : d.getCost()) - (null == d.getReturnCost() ? 0 : d.getReturnCost());
}
}
//回货成本：pp_machining_entry_bill_material表中self1_stone_weight*main_stone_price + self2_stone_weight*main_stone_price
if (d.getIsReturn() != 2){
d.setReturnCost(Double.parseDouble(String.format("%.3f",returnCost)));
}
//统计过滤是否回货查询条件
if(req.getIsReturn() == null || (req.getIsReturn().intValue() == d.getIsReturn().intValue())) {
//累计各数量
//发料数量：查询结果中所有发料数量之和
sendMaterialCount += d.getSendMainCount() != null ? d.getSendMainCount() : 0d;
//已回货数量：查询结果中所有回货数量之和
alreadyReturnCount += returnMainCountTemp;
//发料总价值：查询结果中的标准成本之和
sendMaterialPrice += d.getCost() != null ? d.getCost() : 0d;
}
}
}
//是否回货-过滤
if(req.getIsReturn() != null) {
list = list.stream().filter(a -> a.getIsReturn().intValue() == req.getIsReturn().intValue()).collect(Collectors.toList());
}
//未回货数量：总发料数量-总已回货数量
notReturnCount = sendMaterialCount - alreadyReturnCount;
//未回货总价值：未回货货品的标准成本 + 剩余部分回货货品的标准成本
notReturnPrice = notReturnCost + partReturnCost;
//赋值
resp.setSendMaterialCount(Double.parseDouble(String.format("%.3f",sendMaterialCount)));
resp.setAlreadyReturnCount(Double.parseDouble(String.format("%.3f",alreadyReturnCount)));
resp.setNotReturnCount(Double.parseDouble(String.format("%.3f",notReturnCount)));
resp.setSendMaterialPrice(Double.parseDouble(String.format("%.3f",sendMaterialPrice)));
resp.setNotReturnPrice(Double.parseDouble(String.format("%.3f",notReturnPrice)));
//结果列表赋值
list.addAll(listDelivery);
resp.setList(list);
return resp;
}
@Override
public SendMaterialCountResp sendReturnMaterialSum(SendMaterialCountReq req, boolean isExport) {
return null;
}
@Override
public List<Map<String, Object>> getList(Map<String, Object> param) {
return null;
}
@Override
public SumSendMaterialReconciliationDTO getCount(Map<String, Object> param) {
return sendMaterialReconciliationDao.getCount(param);
}
}
5、dao层
package com.derier.warehouseservice.dao.sendorreturnmaterial;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountDto;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountReq;
import com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto;
import com.dj.domain.sendorreturnmaterial.dto.SumSendMaterialReconciliationDTO;
import com.dj.domain.warehouse.ExportBill;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
import java.util.Map;
/**
* @Description SendMaterialReconciliationDao报表dao
* @Author jinjian
* @Date 2019/3/22
* @Version 1.0
**/
@Mapper
public interface SendMaterialReconciliationDao {
SumSendMaterialReconciliationDTO getCount(Map<String,Object> param);
List<SendMaterialReconciliationDto> getList(Map<String, Object> param);
SumSendMaterialReconciliationDTO getSumCount(Map<String, Object> param);
/**
* SendMaterialReconciliation数量
* @param sendMaterialReconciliationBill
* @return
*/
int count(SendMaterialReconciliationDto sendMaterialReconciliationBill);
int sumCount(SendMaterialReconciliationDto sendMaterialReconciliationBill);
/**
* SendMaterialReconciliation列表 sendReturnSumList
* @param sendMaterialReconciliationBill
* @return
*/
List<SendMaterialReconciliationDto> getSendReturnList(SendMaterialReconciliationDto sendMaterialReconciliationBill);
/**
* SendMaterialReconciliation列表 sendReturnSumList
* @param sendMaterialReconciliationBill
* @return
*/
List<SendMaterialReconciliationDto> sendReturnSumList(SendMaterialReconciliationDto sendMaterialReconciliationBill);
/**
* SendMaterialReconciliationDto导出查询
* @param param
* @return
*/
List<ExportBill> getExportList(Map<String,Object> param);
List<ExportBill> exportSendBill(Map<String, Object> param);
List<SendMaterialCountDto> sendReturnMaterial(SendMaterialCountReq req);
List<String> getSupplyOutStockNo(Map<String, Object> param);
}
6、xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.derier.warehouseservice.dao.sendorreturnmaterial.SendMaterialReconciliationDao">
<select id="getSendReturnList" parameterType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto"
resultType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto">
select
a.id sendMaterialId,
a.bill_no sendMaterialNO,
CONCAT(ifnull(c.produce_code,''),ifnull(d.produce_code,''),ifnull(e.source_bill_no,'')) produceCodeId,
s.simple_name supplyName,bta.bill_type_name billTypeName,
a.source_bill_no machiningBillNo,
a.mainStoneInfo,a.main_stone_price,
CONCAT(IFNULL(a.pkg_code, ''),IFNULL(a.bar_code, '')) as pkgOrBarCode,
a.int_instrument_no intInstrumentNo,
a.send_date sendDate,
a.cate,
a.main_count sendMainCount,
a.sec_count sendSecCount,
a.sendMaterialCost,
b.return_date returnDate,
b.bill_no returnBillNo,
sum(b.main_count) as rerunMainCount,
sum(b.sec_count) as returnWeight,
sum(b.cost) as returnCost,
CONCAT(ifnull(c.entry_date,''),ifnull(d.entry_date,''),ifnull(e.entry_date,'')) as entryDate,
CONCAT(ifnull(c.bill_no,''),ifnull(d.bill_no,''),ifnull(e.bill_no,'')) as entryBillNo,
CONCAT(ifnull(c.bar_code,''),ifnull(d.bar_code,''),ifnull(e.bar_code,'')) as entryBarCode,
CONCAT(ifnull((sum(c.self1_stone_count)),''),ifnull((sum(d.self1_stone_count)),''),ifnull((sum(e.self_1_stone_count)),'')) as entryMainCount,
CONCAT(ifnull((sum(c.self1_stone_weight)),''),ifnull((sum(d.self1_stone_weight)),''),ifnull((sum(e.self_1_stone_weight)),'')) as entryWeight,
CONCAT(ifnull(c.goods_assistant_name,''),ifnull(d.goods_assistant_name,''),
ifnull(e.goods_assistant_name,'')) as productName,
a.main_count-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0) 发料结果,
a.main_count 发料数量,
IFNULL(sum(b.main_count) ,0) 退货数量,
IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) 回料数量,
case a.main_count-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0)
when 0 then '全部回货' when (IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)) >0 and
a.main_count>IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) then '部分回货' else '未回货' end as isReturn
from (
-- 发料单
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(IFNULL(IF(b.cost is not null,b.cost, sm1.cost),0),0) AS sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send1_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(IFNULL(IF(b.cost is not null,b.cost, sm2.main_stone_price*SUM(b.sec_count)),0)) AS sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send2_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
-- 退料单
(select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price
,ROUND(IFNULL(IF(b.cost is not null,b.cost, sm1.cost),0),0) AS Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price
,ROUND(IFNULL(IF(b.cost is not null,b.cost, sm2.main_stone_price*SUM(b.sec_count)),0)) AS Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return2_Condition"></include>
</where>
) b on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
-- 定制入库
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
-- 加工入库单
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) d on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
left join
-- 无采购入库单
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e on e.source_bill_no=a.bill_no
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
<where>
<include refid="base_Send_Condition"></include>
</where>
group by a.id,a.supply_id,a.source_bill_no,a.bar_code,a.pkg_code,a.main_count,a.sec_count
<include refid="base_Result_Condition"></include>
ORDER BY billTypeName DESC
<if test="limit != 0">
LIMIT ${offset},${limit}
</if>
</select>
<sql id="base_Result_Condition">
<if test="isReturn == 1 and isReturn != null and isReturn != '' ">
HAVING
a.main_count=IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)
</if>
<if test="isReturn == 2 and isReturn != null and isReturn != '' ">
HAVING
a.main_count>IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)
and IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)>0
</if>
<if test="isReturn == 3 and isReturn != null and isReturn != '' ">
HAVING
IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)=0
</if>
</sql>
<sql id="base_Send_Condition">
<if test="produceCodeId != null and produceCodeId != ''">
AND CONCAT(ifnull(c.produce_code,''),ifnull(d.produce_code,''),ifnull(e.source_bill_no,'')) = #{produceCodeId}
</if>
<if test="returnBarCode != null and returnBarCode != ''">
AND CONCAT(ifnull(c.bar_code,''),ifnull(d.bar_code,''),ifnull(e.bar_code,'')) = #{returnBarCode}
</if>
<if test="entryDateStart != null and entryDateStart != ''">
AND CONCAT(ifnull(c.entry_date,''),ifnull(d.entry_date,''),ifnull(e.entry_date,'')) >= #{entryDateStart}
</if>
<if test="entryDateEnd != null and entryDateEnd != ''">
AND CONCAT(ifnull(c.entry_date,''),ifnull(d.entry_date,''),ifnull(e.entry_date,'')) <= #{entryDateEnd}
</if>
</sql>
<sql id="base_Condition">
b.state = '2'
</sql>
<sql id="base_Return1_Condition">
<if test="returnDateStart != null and returnDateStart != ''">
AND a.return_date >= #{returnDateStart}
</if>
<if test="returnDateEnd != null and returnDateEnd != ''">
AND a.return_date <= #{returnDateEnd}
</if>
</sql>
<sql id="base_Return2_Condition">
<if test="returnDateStart != null and returnDateStart != ''">
AND a.return_date >= #{returnDateStart}
</if>
<if test="returnDateEnd != null and returnDateEnd != ''">
AND a.return_date <= #{returnDateEnd}
</if>
</sql>
<sql id="base_ppm1_Condition">
sm1.bar_code!=''
</sql>
<sql id="base_ppm2_Condition">
sm2.pkg_code!=''
</sql>
<sql id="base_Send1_Condition">
a.state = '2' and sm1.bar_code!=''
<if test="id != null and id != ''">
AND a.id IN (${id})
</if>
<if test="billTypeId !=null and billTypeId !=''">
and a.bill_type_id in (${billTypeId})
</if>
<if test="supplyId !=null and supplyId !=''">
and a.supply_id in (${supplyId})
</if>
<if test="barCode != null and barCode != ''">
and b.bar_code = #{barCode}
</if>
<if test="cateId != null and cateId != ''">
AND tbc.id in (${cateId})
</if>
<if test="sendMaterialNO != null and sendMaterialNO != ''">
AND a.bill_no IN (${sendMaterialNO})
</if>
<if test="intInstrumentNo != null and intInstrumentNo != ''">
AND sm1.int_instrument_no = #{intInstrumentNo}
</if>
<if test="machiningBillNo != null and machiningBillNo != ''">
AND a.source_bill_no IN (${machiningBillNo})
</if>
<if test="billTypeId != null and billTypeId != ''">
AND a.bill_type_id IN (${billTypeId})
</if>
<if test="sendDateStart != null and sendDateStart != ''">
AND a.send_date >= #{sendDateStart}
</if>
<if test="sendDateEnd != null and sendDateEnd != ''">
AND a.send_date <= #{sendDateEnd}
</if>
</sql>
<sql id="base_Send2_Condition">
a.state = '2' and sm2.pkg_code!=''
<if test="id != null and id != ''">
AND a.id IN (${id})
</if>
<if test="billTypeId !=null and billTypeId !=''">
and a.bill_type_id in (${billTypeId})
</if>
<if test="supplyId !=null and supplyId !=''">
and a.supply_id in (${supplyId})
</if>
<if test="barCode != null and barCode != ''">
and b.pkg_code = #{barCode}
</if>
<if test="cateId != null and cateId != ''">
AND tbc.id in (${cateId})
</if>
<if test="sendMaterialNO != null and sendMaterialNO != ''">
AND a.bill_no IN (${sendMaterialNO})
</if>
<if test="intInstrumentNo != null and intInstrumentNo != ''">
AND sm2.int_instrument_no = #{intInstrumentNo}
</if>
<if test="machiningBillNo != null and machiningBillNo != ''">
AND a.source_bill_no IN (${machiningBillNo})
</if>
<if test="billTypeId != null and billTypeId != ''">
AND a.bill_type_id IN (${billTypeId})
</if>
<if test="sendDateStart != null and sendDateStart != ''">
AND a.send_date >= #{sendDateStart}
</if>
<if test="sendDateEnd != null and sendDateEnd != ''">
AND a.send_date <= #{sendDateEnd}
</if>
</sql>
<sql id="base_Send_Condition1">
b.state = '2' sm2.pkg_code!=''
<if test="id != null and id != ''">
AND b.id IN (${id})
</if>
<if test="billTypeId !=null and billTypeId !=''">
and b.bill_type_id in (${billTypeId})
</if>
<if test="supplyId !=null and supplyId !=''">
and b.supply_id in (${supplyId})
</if>
<if test="barCode != null and barCode != ''">
and (smb.bar_code = #{barCode} or (smb.pkg_code = #{barCode}))
</if>
<if test="returnBarCode != null and returnBarCode != ''">
AND smb.bar_code = #{returnBarCode}
</if>
<if test="produceCodeId != null and produceCodeId != ''">
AND smb.produce_code_id = #{produceCodeId}
</if>
<if test="cateId != null and cateId != ''">
AND tbc.id in (${cateId})
</if>
<if test="sendMaterialNO != null and sendMaterialNO != ''">
AND b.bill_no IN (${sendMaterialNO})
</if>
<if test="intInstrumentNo != null and intInstrumentNo != ''">
AND m.int_instrument_no = #{intInstrumentNo}
</if>
<if test="machiningBillNo != null and machiningBillNo != ''">
AND (mb.bill_no IN (${machiningBillNo}) or w.work_no IN (${machiningBillNo}))
</if>
<if test="billTypeId != null and billTypeId != ''">
AND b.bill_type_id IN (${billTypeId})
</if>
<if test="isReturn != null and isReturn != ''">
AND b.state IN (${isReturn})
</if>
<if test="sendDateStart != null and sendDateStart != ''">
AND b.send_date >= #{sendDateStart}
</if>
<if test="sendDateEnd != null and sendDateEnd != ''">
AND b.send_date <= #{sendDateEnd}
</if>
<if test="returnDateStart != null and returnDateStart != ''">
AND b.send_date >= #{returnDateStart}
</if>
<if test="returnDateEnd != null and returnDateEnd != ''">
AND b.send_date <= #{returnDateEnd}
</if>
</sql>
<sql id="saleReturnCondition">
<if test="1==1">
and b.state = 2
</if>
<if test="companyIdStr !=null and companyIdStr !=''">
and bc.id in (${companyIdStr})
</if>
<if test="barCode !=null and barCode !=''">
and bm.bar_code = #{barCode}
</if>
<if test="billNo !=null and billNo !=''">
and b.bill_no = #{billNo}
</if>
<if test="businessId !=null and businessId !=''">
and SUBSTR(b.bill_no,1,3) = #{businessId}
</if>
<if test="billTypeIdStr !=null and billTypeIdStr !=''">
and b.bill_type_id in (${billTypeIdStr})
</if>
<if test="storeIdStr !=null and storeIdStr !=''">
and b.store_id in (${storeIdStr})
</if>
<if test="bigAreaIdStr !=null and bigAreaIdStr !=''">
and s.big_area_id in (${bigAreaIdStr})
</if>
<if test="operationType !=null and operationType !=''">
AND s.operation_type IN (${operationType})
</if>
<if test="settlementType !=null and settlementType !=''">
AND s.settlement_type IN (${settlementType})
</if>
<if test="startEntryDate !=null and startEntryDate !=''">
and b.delivery_date >= #{startEntryDate}
</if>
<if test="endEntryDate !=null and endEntryDate !=''">
and b.delivery_date <= #{endEntryDate}
</if>
<if test="companyIdStr !=null and companyIdStr !=''">
and bc.id in (${companyIdStr})
</if>
<if test="category !=null and category !=''">
and m.sku_id in (
select DISTINCT ts.sku_id from tb_base_category tb
inner join tb_sku ts
on tb.id = ts.cate_id
where tb.category = #{category} and ts.sku_id=m.sku_id
)
</if>
<if test="cateIdStr !=null and cateIdStr !=''">
and m.sku_id in (
select DISTINCT ts.sku_id from tb_sku ts
where ts.cate_id in (${cateIdStr}) and ts.sku_id=m.sku_id
)
</if>
<if test="(startSaleTime !=null and startSaleTime !='') or (endSaleTime !=null and endSaleTime !='')">
and ( b.bill_id in (
select sd.id from sl_delivery_bill sd where 1=1
<if test="startSaleTime !=null and startSaleTime !=''">
and sd.delivery_date >= #{startSaleTime}
</if>
<if test="endSaleTime !=null and endSaleTime !=''">
and sd.delivery_date <= #{endSaleTime}
</if>
)
or b.bill_id in (
select oo.id from od_order oo where oo.order_type=1
<if test="startSaleTime !=null and startSaleTime !=''">
and oo.express_time >= #{startSaleTime}
</if>
<if test="endSaleTime !=null and endSaleTime !=''">
and oo.express_time <= #{endSaleTime}
</if>
)
)
</if>
<if test="customerId !=null and customerId !=''">
AND b.customer_id = #{customerId}
</if>
</sql>
<sql id="afterEntryCondition">
<if test="1==1">
and b.state = 2
</if>
<if test="companyIdStr !=null and companyIdStr !=''">
and bc.id in (${companyIdStr})
</if>
<if test="barCode !=null and barCode !=''">
and bm.bar_code = #{barCode}
</if>
<if test="billNo !=null and billNo !=''">
and b.bill_no = #{billNo}
</if>
<if test="businessId !=null and businessId !=''">
and SUBSTR(b.bill_no,1,3) = #{businessId}
</if>
<if test="billTypeIdStr !=null and billTypeIdStr !=''">
and b.bill_type_id in (${billTypeIdStr})
</if>
<if test="storeIdStr !=null and storeIdStr !=''">
and b.store_id in (${storeIdStr})
</if>
<if test="operationType !=null and operationType !=''">
AND s.operation_type IN (${operationType})
</if>
<if test="settlementType !=null and settlementType !=''">
AND s.settlement_type IN (${settlementType})
</if>
<if test="bigAreaIdStr !=null and bigAreaIdStr !=''">
and s.big_area_id in (${bigAreaIdStr})
</if>
<if test="startEntryDate !=null and startEntryDate !=''">
and b.entry_date >= #{startEntryDate}
</if>
<if test="endEntryDate !=null and endEntryDate !=''">
and b.entry_date <= #{endEntryDate}
</if>
<if test="category !=null and category !=''">
and m.sku_id in (
select DISTINCT ts.sku_id from tb_base_category tb
inner join tb_sku ts
on tb.id = ts.cate_id
where tb.category = #{category} and ts.sku_id=m.sku_id
)
</if>
<if test="cateIdStr !=null and cateIdStr !=''">
and m.sku_id in (
select DISTINCT ts.sku_id from tb_sku ts
where ts.cate_id in (${cateIdStr}) and ts.sku_id=m.sku_id
)
</if>
<if test="startSaleTime !=null and startSaleTime !=''">
and 1=2
</if>
<if test="endSaleTime !=null and endSaleTime !=''">
and 1=2
</if>
<if test="customerId !=null and customerId !=''">
AND b.customer_id = #{customerId}
</if>
</sql>
<select id="getCount" resultType="com.dj.domain.sendorreturnmaterial.dto.SumSendMaterialReconciliationDTO">
select COUNT(1) FROM pp_send_material_bill_material bm WHERE bm.bill_id =2
</select>
<select id="getPageList" resultType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto">
SELECT
GROUP_CONCAT(DISTINCT b.id) AS sendMaterialId,GROUP_CONCAT(DISTINCT b.bill_no) AS sendMaterialNO, b.bill_type_id AS billTypeId,
(SELECT bt.bill_type_name
FROM
st_base_bill_type bt
WHERE
bt.id = b.bill_type_id) AS billTypeName,b.send_date AS sendDate,IFNULL(mb.id, w.id) AS machiningBillId,IFNULL(mb.bill_no, w.work_no) AS machiningBillNo,
(SELECT s.simple_name
FROM
tb_supplier s
WHERE
s.id = b.supply_id) AS supplyName,CONCAT(IFNULL(smb.pkg_code, ''),IFNULL(smb.bar_code, '')) AS pkgOrBarCode,
SUM(smb.main_count) AS sendMainCount,SUM(smb.sec_count) AS sendSecCount,smb.produce_code_id AS produceCodeId,m.int_instrument_no,
IFNULL(
IF (smb.bar_code IS NOT NULL, m.cost,ROUND(m.main_stone_price * SUM(smb.sec_count),3)),0) AS cost,
m.main_stone_price,smb.sku_id,smb.bar_code,smb.pkg_code,smb.main_count,smb.sec_count,
-- 退料主数量
(SELECT
IFNULL(SUM(sm.main_count), 0)
FROM
pp_return_material_bill_material sm,pp_return_material_bill sb
WHERE
sm.bill_id = sb.id AND sb.state = '2' AND find_in_set(sb.bill_id,GROUP_CONCAT(DISTINCT b.id))
AND (sm.pkg_code = smb.pkg_code OR sm.bar_code = smb.bar_code)) AS
count,
-- 退料辅数量
(SELECT
IFNULL(SUM(sm.sec_count), 0)
FROM
pp_return_material_bill_material sm,pp_return_material_bill sb
WHERE
sm.bill_id = sb.id AND sb.state = '2' AND find_in_set(sb.bill_id,GROUP_CONCAT(DISTINCT b.id))
AND (sm.pkg_code = smb.pkg_code OR sm.bar_code = smb.bar_code)) AS
weight,
-- 入库信息
(SELECT
CONCAT(
IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
IFNULL(smb.bar_code, '')),
IFNULL(m.self1_stone_count, 0),0) +IF (m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
IFNULL(smb.bar_code, '')),IFNULL(m.self2_stone_count, 0),0 )), 3 ),0 ),'#',
IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
IFNULL(smb.bar_code, '')),
IFNULL(m.self1_stone_weight, 0), 0) +IF ( m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
IFNULL(smb.bar_code, '')),
IFNULL(m.self2_stone_weight, 0),0 )), 3 ), 0 ),'#',m.main_stone_price,'#',m.sku_id,'#',m.bar_code)
FROM
pp_machining_entry_bill_material m,
pp_machining_entry_bill eb
WHERE
m.bill_id = eb.id AND eb.state = '2' AND IF (eb.type = 1,m.machining_bill_id = mb.id, m.produce_code_id = w.id))
AS returnMainStoneInfo,
-- 入库数量
-- left(
-- (SELECT
-- CONCAT(
-- IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self1_stone_count, 0),0) +IF (m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),IFNULL(m.self2_stone_count, 0),0 )), 3 ),0 ),'#',
-- IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self1_stone_weight, 0), 0) +IF ( m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self2_stone_weight, 0),0 )), 3 ), 0 ),'#',m.main_stone_price,'#',m.sku_id,'#',m.bar_code)
-- FROM
-- pp_machining_entry_bill_material m,
-- pp_machining_entry_bill eb
-- WHERE
-- m.bill_id = eb.id AND eb.state = '2' AND IF (eb.type = 1,m.machining_bill_id = mb.id, m.produce_code_id = w.id))
-- ,1)
-- AS returnMainCount,
-- -- 入库重量
-- subString(
-- (SELECT
-- CONCAT(
-- IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self1_stone_count, 0),0) +IF (m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),IFNULL(m.self2_stone_count, 0),0 )), 3 ),0 ),'#',
-- IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self1_stone_weight, 0), 0) +IF ( m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self2_stone_weight, 0),0 )), 3 ), 0 ),'#',m.main_stone_price,'#',m.sku_id,'#',m.bar_code)
-- FROM
-- pp_machining_entry_bill_material m,
-- pp_machining_entry_bill eb
-- WHERE
-- m.bill_id = eb.id AND eb.state = '2' AND IF (eb.type = 1,m.machining_bill_id = mb.id, m.produce_code_id = w.id))
-- ,7,5)
-- AS returnWeight,
-- -- 入库条码
-- right(
-- (SELECT
-- CONCAT(
-- IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self1_stone_count, 0),0) +IF (m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),IFNULL(m.self2_stone_count, 0),0 )), 3 ),0 ),'#',
-- IFNULL(ROUND(SUM(IF (m.self1_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self1_stone_weight, 0), 0) +IF ( m.self2_stone_bar_code = CONCAT(IFNULL(smb.pkg_code, ''),
-- IFNULL(smb.bar_code, '')),
-- IFNULL(m.self2_stone_weight, 0),0 )), 3 ), 0 ),'#',m.main_stone_price,'#',m.sku_id,'#',m.bar_code)
-- FROM
-- pp_machining_entry_bill_material m,
-- pp_machining_entry_bill eb
-- WHERE
-- m.bill_id = eb.id AND eb.state = '2' AND IF (eb.type = 1,m.machining_bill_id = mb.id, m.produce_code_id = w.id))
-- ,13)
-- AS returnBarCode,
tbc.cate_name as cate,concat(k.tint,'-',k.shape,k.stone_weight,'-',k.color,'-',k.neatness,'-',k.cut,'-',k.polishing,'-',k.symmetry,'-',k.fluore) as mainStoneInfo
FROM
pp_send_material_bill_material smb
INNER JOIN pp_send_material_bill b ON smb.bill_id = b.id
INNER JOIN st_material m ON (smb.pkg_code = m.pkg_code OR smb.bar_code = m.bar_code)
LEFT JOIN pp_machining_bill mb ON b.machining_id = mb.id
LEFT JOIN od_order_work w ON b.machining_id = w.id
left join tb_sku k on k.sku_id=m.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Condition"></include>
</where>
GROUP BY
mb.id,
w.id,
b.supply_id,
mb.bill_no,
smb.pkg_code,
smb.bar_code,
smb.sku_id,
m.int_instrument_no,
m.cost,
m.main_stone_price
ORDER BY billTypeName DESC
<if test="limit != 0">
LIMIT ${offset},${limit}
</if>
</select>
<select id="getList" resultType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto">
SELECT
b.id,
b.bill_id,
b.source_bill_no,
b.bill_no,
b.source_bill_type_id,
(SELECT t.bill_type_name FROM st_base_bill_type t WHERE t.id = b.source_bill_type_id) AS sourceBillTypeName,
b.bill_type_id,
(SELECT t.bill_type_name FROM st_base_bill_type t WHERE t.id = b.bill_type_id) AS billType,
b.store_id,
(SELECT s.name FROM tb_store s WHERE s.id = b.store_id) AS storeName,
b.customer_id,
b.warehouse_id,
(SELECT w.warehouse_name FROM st_base_warehouse w WHERE w.id = b.warehouse_id) AS warehouseName,
b.location_id,
(SELECT l.storage_location_name FROM st_base_storage_location l WHERE l.id = b.location_id) as locationName,
b.delivery_date,
b.state,
b.remark,
c.customer_name,
c.customer_code,
c.phone AS customerContact,
(SELECT u.username FROM sys_user u WHERE u.user_id = b.create_by) AS createBy,
CONCAT(b.create_time,'') AS createTime,
(SELECT u.username FROM sys_user u WHERE u.user_id = b.check_by) AS checkBy,
CONCAT(b.check_time,'') AS checkTime
FROM sl_return_bill b,cus_customer c
WHERE b.customer_id = c.id
<if test="id != null and id != ''">
AND b.id IN (${id})
</if>
<if test="barCode != null and barCode != ''">
AND EXISTS(
SELECT
m.id
FROM sl_return_bill_material m
WHERE m.bill_id = b.id
AND m.bar_code = #{barCode}
)
</if>
<if test="billId != null and billId != ''">
AND b.bill_id = #{billId}
</if>
<if test="sourceBillNo != null and sourceBillNo != ''">
AND b.source_bill_no = #{sourceBillNo}
</if>
<if test="billNo != null and billNo != ''">
AND b.bill_no = #{billNo}
</if>
<if test="billTypeId != null and billTypeId != ''">
AND b.bill_type_id IN (${billTypeId})
</if>
<if test="storeId != null and storeId != ''">
AND b.store_id IN (${storeId})
</if>
<if test="state != null and state != ''">
AND b.state IN (${state})
</if>
<if test="warehouseId != null and warehouseId != ''">
AND b.warehouse_id IN (${warehouseId})
</if>
<if test="locationId != null and locationId != ''">
AND b.location_id IN (${locationId})
</if>
<if test="customerName != null and customerName != ''">
AND c.customer_name = #{customerName}
</if>
<if test="customerContact != null and customerContact != ''">
AND c.phone = #{customerContact}
</if>
<if test="customerCode != null and customerCode != ''">
AND c.customer_code = #{customerCode}
</if>
<if test="deliveryDateStart != null and deliveryDateStart != ''">
AND b.delivery_date >= #{deliveryDateStart}
</if>
<if test="deliveryDateEnd != null and deliveryDateEnd != ''">
AND b.delivery_date <= #{deliveryDateEnd}
</if>
<if test="createTimeStart != null and createTimeStart != ''">
AND DATE_FORMAT(b.create_time,'%Y-%m-%d') >= #{createTimeStart}
</if>
<if test="createTimeEnd != null and createTimeEnd != ''">
AND DATE_FORMAT(b.create_time,'%Y-%m-%d') <= #{createTimeEnd}
</if>
<if test="checkTimeStart != null and checkTimeStart != ''">
AND DATE_FORMAT(b.check_time,'%Y-%m-%d') >= #{checkTimeStart}
</if>
<if test="checkTimeEnd != null and checkTimeEnd != ''">
AND DATE_FORMAT(b.check_time,'%Y-%m-%d') <= #{checkTimeEnd}
</if>
ORDER BY b.create_time DESC
<if test="limit != 0">
LIMIT ${offset},${limit}
</if>
</select>
<select id="getSumCount" resultType="com.dj.domain.sendorreturnmaterial.dto.SumSendMaterialReconciliationDTO">
SELECT
COUNT(1)
FROM sl_return_bill b,cus_customer c
WHERE b.customer_id = c.id
<if test="id != null and id != ''">
AND b.id IN (${id})
</if>
<if test="barCode != null and barCode != ''">
AND EXISTS(
SELECT
m.id
FROM sl_return_bill_material m
WHERE m.bill_id = b.id
AND m.bar_code = #{barCode}
)
</if>
<if test="billId != null and billId != ''">
AND b.bill_id = #{billId}
</if>
<if test="sourceBillNo != null and sourceBillNo != ''">
AND b.source_bill_no = #{sourceBillNo}
</if>
<if test="billNo != null and billNo != ''">
AND b.bill_no = #{billNo}
</if>
<if test="billTypeId != null and billTypeId != ''">
AND b.bill_type_id IN (${billTypeId})
</if>
<if test="state != null and state != ''">
AND b.state IN (${state})
</if>
<if test="warehouseId != null and warehouseId != ''">
AND b.warehouse_id IN (${warehouseId})
</if>
<if test="locationId != null and locationId != ''">
AND b.location_id IN (${locationId})
</if>
<if test="customerName != null and customerName != ''">
AND c.customer_name = #{customerName}
</if>
<if test="customerContact != null and customerContact != ''">
AND c.phone = #{customerContact}
</if>
<if test="customerCode != null and customerCode != ''">
AND c.customer_code = #{customerCode}
</if>
<if test="deliveryDateStart != null and deliveryDateStart != ''">
AND b.delivery_date >= #{deliveryDateStart}
</if>
<if test="deliveryDateEnd != null and deliveryDateEnd != ''">
AND b.delivery_date <= #{deliveryDateEnd}
</if>
<if test="createTimeStart != null and createTimeStart != ''">
AND DATE_FORMAT(b.create_time,'%Y-%m-%d') >= #{createTimeStart}
</if>
<if test="createTimeEnd != null and createTimeEnd != ''">
AND DATE_FORMAT(b.create_time,'%Y-%m-%d') <= #{createTimeEnd}
</if>
<if test="checkTimeStart != null and checkTimeStart != ''">
AND DATE_FORMAT(b.check_time,'%Y-%m-%d') >= #{checkTimeStart}
</if>
<if test="checkTimeEnd != null and checkTimeEnd != ''">
AND DATE_FORMAT(b.check_time,'%Y-%m-%d') <= #{checkTimeEnd}
</if>
</select>
<select id="count" parameterType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto" resultType="java.lang.Integer">
select COUNT(1) from
(select
COUNT(1)
from (
-- 发料单
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(sum(b.sec_count)*sm1.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send1_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(sum(b.sec_count)*sm2.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send2_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
-- 退料单
(select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price
,ROUND(b.sec_count*sm1.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price
,ROUND(b.sec_count*sm2.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return2_Condition"></include>
</where>
) b on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
-- 定制入库
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
-- 加工入库单
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) d on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
left join
-- 无采购入库单
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e on e.source_bill_no=a.bill_no
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
<where>
<include refid="base_Send_Condition"></include>
</where>
group by a.id,a.supply_id,a.source_bill_no,a.bar_code,a.pkg_code,a.main_count,a.sec_count
<!-- <if test="isReturn == 1 and isReturn != null and isReturn != '' ">-->
<!-- HAVING-->
<!-- a.main_count=IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)-->
<!-- </if>-->
<!-- <if test="isReturn == 2 and isReturn != null and isReturn != '' ">-->
<!-- HAVING-->
<!-- a.main_count>IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)-->
<!-- and IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)>0-->
<!-- </if>-->
<!-- <if test="isReturn == 3 and isReturn != null and isReturn != '' ">-->
<!-- HAVING-->
<!-- IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)=0-->
<!-- </if>-->
<include refid="base_Result_Condition"></include>
) as b
</select>
<select id="getExportList" resultType="com.dj.domain.warehouse.ExportBill">
select
a.id sendMaterialId,
a.bill_no sendMaterialNO,
CONCAT(ifnull(c.produce_code,''),ifnull(d.produce_code,''),ifnull(e.source_bill_no,'')) produceCodeId,
s.simple_name supplyName,bta.bill_type_name billTypeName,
a.source_bill_no machiningBillNo,
a.mainStoneInfo,a.main_stone_price,
CONCAT(IFNULL(a.pkg_code, ''),IFNULL(a.bar_code, '')) as pkgOrBarCode,
a.int_instrument_no intInstrumentNo,
a.send_date sendDate,
a.cate,
a.main_count sendMainCount,
a.sec_count sendSecCount,
a.sendMaterialCost,
b.return_date returnDate,
b.bill_no returnBillNo,
sum(b.main_count) as rerunMainCount,
sum(b.sec_count) as returnWeight,
sum(b.cost) as returnCost,
CONCAT(ifnull(c.entry_date,''),ifnull(d.entry_date,''),ifnull(e.entry_date,'')) as entryDate,
CONCAT(ifnull(c.bill_no,''),ifnull(d.bill_no,''),ifnull(e.bill_no,'')) as entryBillNo,
CONCAT(ifnull(c.bar_code,''),ifnull(d.bar_code,''),ifnull(e.bar_code,'')) as entryBarCode,
CONCAT(ifnull((sum(c.self1_stone_count)),''),ifnull((sum(d.self1_stone_count)),''),ifnull((sum(e.self_1_stone_count)),'')) as entryMainCount,
CONCAT(ifnull((sum(c.self1_stone_weight)),''),ifnull((sum(d.self1_stone_weight)),''),ifnull((sum(e.self_1_stone_weight)),'')) as entryWeight,
CONCAT(ifnull(c.goods_assistant_name,''),ifnull(d.goods_assistant_name,''),
ifnull(e.goods_assistant_name,'')) as productName,
a.main_count-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0) 发料结果,
a.main_count 发料数量,
IFNULL(sum(b.main_count) ,0) 退货数量,
IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) 回料数量,
case a.main_count-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0)
when 0 then '全部回货' when (IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)) >0 and
a.main_count>IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) then '部分回货' else '未回货' end as isReturn
from (
-- 发料单
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(sum(b.sec_count)*sm1.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send1_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(sum(b.sec_count)*sm2.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send2_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
-- 退料单
(select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price
,ROUND(b.sec_count*sm1.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price
,ROUND(b.sec_count*sm2.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return2_Condition"></include>
</where>
) b on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
-- 定制入库
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
-- 加工入库单
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) d on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
left join
-- 无采购入库单
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e on e.source_bill_no=a.bill_no
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
<where>
<include refid="base_Send_Condition"></include>
</where>
group by a.id,a.supply_id,a.source_bill_no,a.bar_code,a.pkg_code,a.main_count,a.sec_count
<!-- <if test="isReturn == 1 and isReturn != null and isReturn != '' ">-->
<!-- HAVING-->
<!-- a.main_count=IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)-->
<!-- </if>-->
<!-- <if test="isReturn == 2 and isReturn != null and isReturn != '' ">-->
<!-- HAVING-->
<!-- a.main_count>IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)-->
<!-- and IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)>0-->
<!-- </if>-->
<!-- <if test="isReturn == 3 and isReturn != null and isReturn != '' ">-->
<!-- HAVING-->
<!-- IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)=0-->
<!-- </if>-->
<include refid="base_Result_Condition"></include>
ORDER BY billTypeName DESC
</select>
<select id="exportSendBill" resultType="com.dj.domain.warehouse.ExportBill">
select
a.id sendMaterialId,
a.bill_no sendMaterialNO,
CONCAT(ifnull(c.produce_code,''),ifnull(d.produce_code,''),ifnull(e.source_bill_no,'')) produceCodeId,
s.simple_name supplyName,bta.bill_type_name billTypeName,
a.source_bill_no machiningBillNo,
a.mainStoneInfo,a.main_stone_price,
CONCAT(IFNULL(a.pkg_code, ''),IFNULL(a.bar_code, '')) as pkgOrBarCode,
a.int_instrument_no intInstrumentNo,
a.send_date sendDate,
a.cate,
a.main_count sendMainCount,
a.sec_count sendSecCount,
a.sendMaterialCost,
b.return_date returnDate,
b.bill_no returnBillNo,
sum(b.main_count) as rerunMainCount,
sum(b.sec_count) as returnWeight,
sum(b.cost) as returnCost,
CONCAT(ifnull(c.entry_date,''),ifnull(d.entry_date,''),ifnull(e.entry_date,'')) as entryDate,
CONCAT(ifnull(c.bill_no,''),ifnull(d.bill_no,''),ifnull(e.bill_no,'')) as entryBillNo,
CONCAT(ifnull(c.bar_code,''),ifnull(d.bar_code,''),ifnull(e.bar_code,'')) as entryBarCode,
CONCAT(ifnull((sum(c.self1_stone_count)),''),ifnull((sum(d.self1_stone_count)),''),ifnull((sum(e.self_1_stone_count)),'')) as entryMainCount,
CONCAT(ifnull((sum(c.self1_stone_weight)),''),ifnull((sum(d.self1_stone_weight)),''),ifnull((sum(e.self_1_stone_weight)),'')) as entryWeight,
CONCAT(ifnull(c.goods_assistant_name,''),ifnull(d.goods_assistant_name,''),
ifnull(e.goods_assistant_name,'')) as productName,
a.main_count-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0) 发料结果,
a.main_count 发料数量,
IFNULL(sum(b.main_count) ,0) 退货数量,
IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) 回料数量,
case a.main_count-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0)
when 0 then '全部回货' when (IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0)) >0 and
a.main_count>IFNULL(sum(b.main_count) ,0)+IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) then '部分回货' else '未回货' end as isReturn
from (
-- 发料单
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(sum(b.sec_count)*sm1.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send1_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(sum(b.sec_count)*sm2.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send2_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
-- 退料单
(select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price
,ROUND(b.sec_count*sm1.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price
,ROUND(b.sec_count*sm2.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return2_Condition"></include>
</where>
) b on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
-- 定制入库
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
-- 加工入库单
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) d on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
left join
-- 无采购入库单
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e on e.source_bill_no=a.bill_no
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
<where>
<include refid="base_Send_Condition"></include>
</where>
group by a.id,a.supply_id,a.source_bill_no,a.bar_code,a.pkg_code,a.main_count,a.sec_count
</select>
<select id="getSupplyOutStockNo" resultType="java.lang.String">
select DISTINCT IFNULL(pe.supply_out_stock_no,'')
from pp_machining_entry_bill_material pe
where (pe.machining_bill_id = #{machiningBillId} OR pe.produce_code_id = #{machiningBillId})
and ( pe.self1_stone_bar_code = #{pkgOrBarCode}
or pe.self2_stone_bar_code = #{pkgOrBarCode}
)
</select>
<select id="sendReturnMaterial" resultType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialCountDto">
select s.simple_name supplyName,
sum(a.main_count) sendCount,sum(a.sec_count) sendSecCount,IFNULL(sum(b.main_count) ,0) as backCount ,IFNULL(sum(b.sec_count) ,0) as backSecCount,
IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) entryCount,
IFNULL(sum(c.self1_stone_weight),0)+IFNULL(sum(d.self1_stone_weight),0)+IFNULL(sum(e.self_1_stone_weight),0) entrySecCount,
sum(a.main_count)-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0) as remain_count,
sum(a.sec_count)-IFNULL(sum(b.sec_count) ,0)-IFNULL(sum(c.self1_stone_weight),0)-IFNULL(sum(d.self1_stone_weight),0)- IFNULL(sum(e.self_1_stone_weight),0) as remain_weight
from (
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(sum(b.sec_count)*sm1.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm1.bar_code!=''
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(sum(b.sec_count)*sm2.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm2.pkg_code!=''
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
(
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date, tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,
sm1.main_stone_price,ROUND(b.sec_count*sm1.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm1.bar_code!=''
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,
sm2.main_stone_price,ROUND(b.sec_count*sm2.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm2.pkg_code!=''
) b
on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm1.bar_code!=''
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm2.pkg_code!=''
)
c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm1.bar_code!=''
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
where sm2.pkg_code!=''
)
d
on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e
on e.source_bill_no=a.bill_no and (ifNULL(e.self_1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(e.self_1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
group by s.simple_name
</select>
<select id="sendReturnSumList"
resultType="com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto">
select s.simple_name supplyName,
sum(a.main_count) sendMainCount,
sum(a.sec_count) sendSecCount,
IFNULL(sum(b.main_count) ,0) as backCount ,
IFNULL(sum(b.sec_count) ,0) as backSecCount,
IFNULL(sum(c.self1_stone_count),0)+IFNULL(sum(d.self1_stone_count),0)+IFNULL(sum(e.self_1_stone_count),0) entryCount,
IFNULL(sum(c.self1_stone_weight),0)+IFNULL(sum(d.self1_stone_weight),0)+IFNULL(sum(e.self_1_stone_weight),0) entrySecCount,
sum(a.main_count)-IFNULL(sum(b.main_count) ,0)-IFNULL(sum(c.self1_stone_count),0)-IFNULL(sum(d.self1_stone_count),0)-IFNULL(sum(e.self_1_stone_count),0) as remainCount,
sum(a.sec_count)-IFNULL(sum(b.sec_count) ,0)-IFNULL(sum(c.self1_stone_weight),0)-IFNULL(sum(d.self1_stone_weight),0)- IFNULL(sum(e.self_1_stone_weight),0) as remainSec,
'' sendResult,'' sendCountResult,'' sendSecResult,'' remarks
from (
-- 发料单
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(sum(b.sec_count)*sm1.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send1_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(sum(b.sec_count)*sm2.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send2_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
-- 退料单
(select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price
,ROUND(b.sec_count*sm1.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price
,ROUND(b.sec_count*sm2.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return2_Condition"></include>
</where>
) b on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
-- 定制入库
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
-- 加工入库单
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) d on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
left join
-- 无采购入库单
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e on e.source_bill_no=a.bill_no
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
<where>
<include refid="base_Send_Condition"></include>
</where>
group by s.simple_name
</select>
<select id="sumCount" resultType="java.lang.Integer">
select count(1) from (
select count(1)
from (
-- 发料单
select sm1.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price,
ROUND(sum(b.sec_count)*sm1.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send1_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
UNION all
select sm2.int_instrument_no,a.bill_type_id,a.bill_no, a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code,sum(b.main_count) main_count,sum(b.sec_count) as sec_count,a.send_date
,tbc.cate_name as cate,a.state,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price,
ROUND(sum(b.sec_count)*sm2.main_stone_price,0) as sendMaterialCost
from pp_send_material_bill a
inner join pp_send_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Send2_Condition"></include>
</where>
group by a.id, a.supply_id,b.purchase_bill_id,a.source_bill_no,b.bar_code,b.pkg_code
) a
left join
-- 退料单
(select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm1.main_stone_price
,ROUND(b.sec_count*sm1.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.supply_id,b.bar_code,b.pkg_code,b.main_count,b.sec_count,a.return_date,
tbc.cate_name as cate,
concat(ifnull(k.tint,''),'-',ifnull(k.shape,''),ifnull(k.stone_weight,''),'-',ifnull(k.color,''),'-',ifnull(k.neatness,''),'-',ifnull(k.cut,''),'-',ifnull(k.polishing,''),'-',ifnull(k.symmetry,''),'-',ifnull(k.fluore,'')) as mainStoneInfo,sm2.main_stone_price
,ROUND(b.sec_count*sm2.main_stone_price,0) as Cost
from pp_return_material_bill a
inner join pp_return_material_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.pkg_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_Return2_Condition"></include>
</where>
) b on b.bill_id=a.id and ifNULL(b.bar_code,'')=ifNULL(a.bar_code,'') and ifNULL(b.pkg_code,'') =ifNULL(a.pkg_code,'')
LEFT JOIN
-- 定制入库
(
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a
inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) c on c.work_no=a.source_bill_no and c.supply_id=a.supply_id and (ifNULL(c.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(c.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
LEFT JOIN
-- 加工入库单
(
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm1 ON sm1.bar_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm1.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm1_Condition"></include>
</where>
UNION all
select a.bill_id,a.bill_no,a.bill_type_id,a.supply_id,b.work_no,b.self1_stone_bar_code,b.self1_stone_count,b.self1_stone_weight,b.produce_code, b.goods_assistant_name,b.supply_out_stock_no,a.entry_date,b.bar_code
from pp_machining_entry_bill a inner join pp_machining_entry_bill_material b on b.bill_id=a.id
left join st_material sm2 ON sm2.pkg_code=b.self1_stone_bar_code
left join tb_sku k on k.sku_id=sm2.sku_id
left join tb_base_category tbc on tbc.id=k.cate_id
<where>
<include refid="base_ppm2_Condition"></include>
</where>
) d on d.bill_id=a.purchase_bill_id and d.supply_id=a.supply_id and (ifNULL(d.self1_stone_bar_code,'')=ifNULL(a.bar_code,'') or ifNULL(d.self1_stone_bar_code,'') =ifNULL(a.pkg_code,''))
left join
-- 无采购入库单
(
select a.source_bill_no,a.bill_no,a.bill_type_id,a.id,a.supply_id,b.self_1_stone_bar_code,b.self_1_stone_weight,b.self_1_stone_count,b.goods_assistant_name,a.entry_date,b.bar_code,b.sett_bill_no
from st_entry_no_purchase_bill a
inner join st_entry_no_purchase_bill_material b on b.bill_id=a.id
where source_bill_no is not NULL
) e on e.source_bill_no=a.bill_no
inner join tb_supplier s on s.id=a.supply_id
inner join st_base_bill_type bta on bta.id = a.bill_type_id
<where>
<include refid="base_Send_Condition"></include>
</where>
group by s.simple_name ) as b
</select>
</mapper>
7、util
import com.alibaba.fastjson.JSON; import com.dj.constants.CategoryEnum; import com.dj.domain.StyleMaterialStone; import com.dj.domain.bom.MaterialHistoryMaterial; import com.dj.domain.goods.dto.StyleDataDto; import com.dj.domain.log.MaterialChangeDescripLog; import com.dj.domain.log.MaterialChangePriceLog; import com.dj.domain.log.StockHistoryMaterial; import com.dj.domain.order.OrderWork; import com.dj.domain.order.admin.OrderAdmin; import com.dj.domain.order.admin.OrderGoodsAdmin; import com.dj.domain.salereport.SaleReturnReport; import com.dj.domain.sendorreturnmaterial.dto.SendMaterialReconciliationDto; import com.dj.domain.warehouse.InstantMaterialResp; import com.dj.domain.warehouse.dto.InstantStock.InstantStockResp; import com.dj.domain.warehouse.dto.SalesDetailDTO; import com.dj.domain.warehouse.dto.SalesRecordsDTO; import com.dj.util.DateUtil; import com.dj.util.DateUtils; import com.dj.util.StringUtil; import com.dj.util.TypeToNameUtil; import org.apache.commons.lang3.StringUtils; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map;
/**
* @Description 导出工具类
* @Author gj
* @Date 2019/3/11
* @Version 1.0
**/
public class ExportUtil {
/**
*发料退料对账表
* @param list
* @return
*/
public static List<List<Object>> getSendReturnMaterialBillRows(List<SendMaterialReconciliationDto> list) {
List <List <Object>> rows = new ArrayList <>();
if (list == null || list.size() == 0) {
return rows;
}
List <Object> row;
for (SendMaterialReconciliationDto temp : list) {
row = new ArrayList <>();
row.add(temp.getMachiningBillNo());
row.add(temp.getProduceCodeId());
row.add(temp.getBillTypeName());
row.add(temp.getSupplyName());
row.add(temp.getPkgOrBarCode());
row.add(temp.getCate());
row.add(temp.getMainStoneInfo());
row.add(temp.getIntInstrumentNo());
row.add(temp.getMainStonePrice());
row.add(temp.getCost());
row.add(temp.getSendDate());
row.add(temp.getSendMaterialNO());
row.add(temp.getSendMainCount());
row.add(temp.getSecCount());
row.add(temp.getSendMaterialCost());
row.add(temp.getReturnDateEnd());
row.add(temp.getReturnBillNo());
row.add(temp.getReturnMainCount());
row.add(temp.getReturnWeight());
row.add(temp.getReturnCost());
row.add(temp.getEntryDate());
row.add(temp.getEntryBillNo());
row.add(temp.getEntryBarCode());
row.add(temp.getEntryMainCount());
row.add(temp.getEntryWeight());
row.add(temp.getEntryCost());
row.add(temp.getEntryCate());
row.add(temp.getProductName());
row.add(temp.getMaterialInfo());
row.add(temp.getReturnMainStoneInfo());
row.add(temp.getReturnSecStoneInfo());
row.add(temp.getIsReturn());
row.add(temp.getSupplyOutStockNo());
rows.add(row);
}
return rows;
}
/**
*发料退料对账表统计表
* @param list
* @return
*/
public static List<List<Object>> getSendReturnMaterialBillSumRows(List<SendMaterialReconciliationDto> list) {
List <List <Object>> rows = new ArrayList <>();
if (list == null || list.size() == 0) {
return rows;
}
List <Object> row;
for (SendMaterialReconciliationDto temp : list) {
row = new ArrayList <>();
row.add(temp.getSupplyName());
row.add(temp.getSendMainCount());
row.add(temp.getSendSecCount());
row.add(temp.getBackCount());
row.add(temp.getBackSecCount());
row.add(temp.getEntryCount());
row.add(temp.getEntrySecCount());
row.add(temp.getRemainCount());
row.add(temp.getRemainSec());
row.add(temp.getSendResult());
row.add(temp.getSendCountResult());
row.add(temp.getSendSecResult());
row.add(temp.getRemark());
rows.add(row);
}
return rows;
}
}
三、页面请求
四、接口数据
五、后台数据
扫一扫
1790
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
