目录
一，js的写法
layui.use(['table', 'admin', 'ax', 'func', 'upload', 'util', 'form', 'laydate'], function () {
var $ = layui.$;
var table = layui.table;
var $ax = layui.ax;
var admin = layui.admin;
var func = layui.func;
var upload = layui.upload;
var util = layui.util;
var form = layui.form;
var laydate = layui.laydate;
var projectStatus_Global = "";
var ChargeManage = {
tableId: "chargeTable",
initDealAmount: 0,
};
ChargeManage.initColumn = function (totalData) {
return [[
{type: 'checkbox', fixed: 'left'},
{field: 'id', hide: true, title: ''},
{field: 'projectName', title: '项目名称', totalRowText: '合计：' + totalData.total + '元'},
{field: 'projectCode', title: '项目编号'},
{field: 'transactionTime', title: '成交时间'},
{field: 'companyName', title: '采购商'},
{field: 'supplierName', title: '中标'},
{field: 'dealAmount', title: '成交价（元）'},
{
field: 'projectStatus', title: '状态', templet: function (d) {
if (d.projectStatus == 'PROJECT_AGREED') {
return '<span style="color: darkorange">未付款</span>';
} else if (d.projectStatus == 'PROJECT_DEAL') {
return '<span>已结项</span>';
} else {
return '<span>/</span>';
}
}
},
{
title: '操作', templet: function (d) {
var html = '<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail" style="color: deepskyblue">详情</a>';
if (d.projectStatus == 'PROJECT_DEAL') {
html += '<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="payCost" style="color: deepskyblue">缴费情况</a>';
}
return html;
}
}
]];
}
ChargeManage.search = function (projectStatus) {
var queryData = {};
queryData['projectName'] = $('#projectName').val();
queryData['projectCode'] = $('#projectCode').val();
queryData['purchaserName'] = $('#purchaserName').val();
queryData['supplierName'] = $('#supplierName').val();
queryData['beginTime'] = $('#beginTime').val();
queryData['endTime'] = $('#endTime').val();
queryData['projectStatus'] = projectStatus;
tableInit(queryData);
/* table.reload(ChargeManage.tableId, {
where: queryData, page: {curr: 1},
done:function () {
$(' .layui-table th, .layui-table-col-set,
.layui-table-fixed-r, .layui-table-grid-down, .layui-table-page, .layui-table-tips-main, .layui-table-tool, .layui-table-total,.layui-table-view, .layui-table[lay-skin=line], .layui-table[lay-skin=row]').css({'border-width':'0px','border-style':'none'});
}});*/
}
//日期时间选择器
laydate.render({
elem: '#beginTime'
, type: 'datetime'
});
laydate.render({
elem: '#endTime'
, type: 'datetime'
});
$('#btnSearch').click(function () {
ChargeManage.search();
});
/**
* 导出按钮
*/
$('#exportData').click(function () {
debugger
var queryData = {};
queryData['projectName'] = $('#projectName').val();
queryData['projectCode'] = $('#projectCode').val();
queryData['purchaserName'] = $('#purchaserName').val();
queryData['supplierName'] = $('#supplierName').val();
queryData['beginTime'] = $('#beginTime').val();
queryData['endTime'] = $('#endTime').val();
queryData['projectStatus'] = projectStatus_Global;
console.log(queryData);
exportData(queryData);
});
/**
* 导出企业缴费情况按钮
*/
$('#exportData1').click(function () {
var queryData = {};
queryData['paymentAmount'] = $("#paymentAmount").val();
queryData['recPhone'] = $("#rec_phone").val();
queryData['companyName'] = $("#companyName").val();
queryData['projectName'] = $("#projectName").val();
queryData['recAddress'] = $("#rec_address").val();
queryData['recName'] = $("#rec_name").val();
queryData['bankAccount'] = $("#bankAccount").val();
queryData['bankOfDeposit'] = $("#bankOfDeposit").val();
queryData['regPhone'] = $("#regPhone").val();
queryData['regAddress'] = $("#regAddress").val();
queryData['mailAddress'] = $("#mailAddress").val(); //邮寄地址
queryData['taxpayerIdentification'] = $("#taxpayerIdentification").val();
console.log(queryData);
exportData1(queryData);
});
//获取按钮上未付款、已结项数据
function typeStatistic() {
var ajax = new $ax(Feng.ctxPath + "/chargeManage/statistic", function (res) {
ChargeManage.btnSatistic = res.data;
setBtn();
}, function (err) {
Feng.error("获取统计数据失败");
});
ajax.start();
}
typeStatistic();
function setBtn() {
var data = ChargeManage.btnSatistic;
for (let i = 0; i < data.length; i++) {
if (data[i].projectStatus === "PROJECT_AGREED") {
$('#projectAgree').val("未付款(" + data[i].num + ")");
} else if (data[i].projectStatus === 'PROJECT_DEAL') {
$('#projectDeal').val("已结项(" + data[i].num + ")");
}
}
//判断若按钮上没有值，则设置为0
if ($('#projectAgree').val() === '未付款') {
$('#projectAgree').val("未付款(0)");
}
if ($('#projectDeal').val() === '已结项') {
$('#projectDeal').val("已结项(0)");
}
}
//未付款
ChargeManage.projectAgree = function () {
$('#projectAgree').css({"color": "deepskyblue", "border-color": "deepskyblue"});
$('#projectDeal').css({"color": "black", "border-color": "black"});
$('#allCharge').css({"color": "black", "border-color": "black"});
projectStatus_Global = "PROJECT_AGREED";
ChargeManage.search("PROJECT_AGREED");
}
//已结项
ChargeManage.projectDeal = function () {
$('#projectAgree').css({"color": "black", "border-color": "black"});
$('#projectDeal').css({"color": "deepskyblue", "border-color": "deepskyblue"});
$('#allCharge').css({"color": "black", "border-color": "black"});
projectStatus_Global = "PROJECT_DEAL";
ChargeManage.search("PROJECT_DEAL");
}
//全部
ChargeManage.allCharge = function () {
$('#projectAgree').css({"color": "black", "border-color": "black"});
$('#projectDeal').css({"color": "black", "border-color": "black"});
$('#allCharge').css({"color": "deepskyblue", "border-color": "deepskyblue"});
projectStatus_Global = "";
ChargeManage.search("");
}
//未付款
$('#projectAgree').click(function () {
ChargeManage.projectAgree();
});
//已结项
$('#projectDeal').click(function () {
ChargeManage.projectDeal();
});
//全部
$('#allCharge').click(function () {
ChargeManage.allCharge();
});
tableInit();
function tableInit(queryData) {
new $ax(Feng.ctxPath + "/chargeManage/chargeList/total", function (res) {
var totalData = res.data.total;
ChargeManage.initDealAmount = totalData.total;
if (res.success) {
table.render({
elem: '#' + ChargeManage.tableId,
url: Feng.ctxPath + "/chargeManage/chargeList",
title: '收费数据',
totalRow: true,
cols: ChargeManage.initColumn(totalData),
where: queryData,
page: true,
skin: 'line',
height: "full-158",
parseData: function (listRes) { //res 即为原始返回的数据
return {
"code": listRes.code, //解析接口状态
"msg": listRes.message, //解析提示文本
"count": listRes.data.pageInfo.count, //解析数据长度
"data": listRes.data.pageInfo.data, //解析数据列表
/*"totalRow":{
"dealAmount": res.data.total.total
}*/
}
},
done: function () {
/* $('th').hide();//隐藏表头*/
//隐藏边框
//$('tr').css({'background-color':'transparent'});
//$('th').css({'font-weight':'bold'});
//.layui-table td 数据间隔横线 .layui-table-header：表头下横线
$(' .layui-table th, .layui-table-col-set, .layui-table-fixed-r, .layui-table-grid-down, .layui-table-page, .layui-table-tips-main, .layui-table-tool, .layui-table-total,.layui-table-view, .layui-table[lay-skin=line], .layui-table[lay-skin=row]').css({
'border-width': '0px',
'border-style': 'none'
});
}
});
} else {
Feng.error("合计数据统计失败");
}
}).set(queryData).start();
}
$('#allCharge').css({"color": "deepskyblue", "border-color": "deepskyblue"});
//工具栏事件
table.on('tool(' + ChargeManage.tableId + ')', function (obj) {
var event = obj.event;
var data = obj.data;
if (event === 'detail') {
window.location.href = Feng.ctxPath +
"/chargeManage/detail?projectId=" + data.id + "&purchaserId=" + data.companyId + "&supplierId=" + data.supplierId + "&hasItem=" + data.hasItem;
} else if (event === 'payCost') {
$.ajax({
url: Feng.ctxPath + "/chargeManage/payCostDetail",
type: 'GET',
data: {
"projectId": data.id
},
dataType: 'json',
contentType: "application/json",
success: function (res) {
console.log(res);
if (res.code !== 0) {
Feng.error(res.message);
return;
}
var resData = res.data;
$("#paymentAmount").val(resData.payment_amount);
$("#rec_phone").val(resData.rec_phone);
$("#rec_address").val(resData.rec_address);
$("#rec_name").val(resData.rec_name);
$("#mailAddress").val(resData.mail_address); //邮寄地址
$("#bankAccount").val(resData.bank_account);
$("#bankOfDeposit").val(resData.bank_of_deposit);
$("#regPhone").val(resData.reg_phone);
$("#regAddress").val(resData.reg_address);
$("#taxpayerIdentification").val(resData.taxpayer_identification);
var type = "电子发票";
if (resData.invoice_type !== "1") {
type = "纸质发票";
}
$("#invoiceType").val(type);
$("#companyName").val(resData.company_name);
var payment_type = "微信支付";
if (resData.payment_type !== "0") {
payment_type = "电汇";
}
$("#paymentType").val(payment_type);
layer.open({
type: 1
, title: '订单信息'
, area: ['500px', '550px']
, id: 'openWin1' //防止重复弹出
, content: $('#payWin')
, success: function () {
initWin(data);
}
});
}, error: function (res) {
Feng.error("系统错误!!!");
}
});
}
});
function initWin(data) {
$.ajax({
url: Feng.ctxPath + "/supplierCenter/getInvoiceInfo",//获取供应商开票信息
type: 'POST',
data: {},
dataType: 'json',
contentType: "application/json",
success: function (res) {
if (res.success) {
$('#projectId').val(data.projectId);
$('#payProjectName').val(data.projectName);
}
}
});
$.ajax({
url: Feng.ctxPath + "/supplierCenter/getReceiveInfo",
type: 'POST',
data: {},
dataType: 'json',
contentType: "application/json",
success: function (res) {
if (res.success) {
/*$('#rec_name').val(res.data.recName);
$('#rec_address').val(res.data.recAddress);
$('#rec_phone').val(res.data.recPhone);*/
}
}
});
}
ChargeManage.totalDealAmount = function (data) {
let total = 0;
for (let i = 0; i < data.length; i++) {
if (data[i].dealAmount === "" || data[i].dealAmount == null) {
continue;
}
if (isNaN(data[i].dealAmount)) {
continue;
}
total += data[i].dealAmount;
}
return total;
}
table.on('checkbox(' + ChargeManage.tableId + ')', function (obj) {
var checkStatus = table.checkStatus(ChargeManage.tableId);
//console.log(checkStatus);
var layui_table_total = $('.layui-table-total');
if (checkStatus.data.length == 0) {
layui_table_total.find('td[data-field="projectName"] div').text('合计：' +
'' + ChargeManage.initDealAmount + '元');
} else {
layui_table_total.find('td[data-field="projectName"] div').text('合计：' +
'' + ChargeManage.totalDealAmount(checkStatus.data) + '元');
}
});
function exportData(queryData) {
console.log("exportData"+queryData);
window.open(Feng.ctxPath + "/chargeManage/chargeList/export", "_self");
}
function exportData1(queryData) {
console.log("exportData1"+queryData);
window.open(Feng.ctxPath + "/chargeManage/chargeList/export1", "_self");
}
});二 ，html的写法
@layout("/common/_container.html",{js:["/assets/administrator/chargeManage.js"]}){
<div class="layui-body-header">
<span class="layui-body-header-title">收费管理</span>
</div>
<div class="layui-fluid">
<div class="layui-row layui-col-space15">
<div class="layui-col-sm12 layui-col-md12 layui-col-lg12">
<div class="layui-card">
<div class="layui-card-body">
<div class="layui-form-item">
<div class="layui-inline">
<select id="type" class="layui-select">
<option value="">全部</option>
<option value="">竞价</option>
<option value="">委托竞价</option>
<option value="">供应商入围</option>
<option value="">委托供应商入围</option>
<option value="">询比价</option>
</select>
</div>
<div class="layui-inline">
<div class="layui-input-inline">
<input id="projectName" class="layui-input" type="text" placeholder="请输入项目名称"/>
</div>
</div>
<div class="layui-inline">
<div class="layui-input-inline">
<input id="projectCode" class="layui-input" type="text" placeholder="请输入项目编号"/>
</div>
</div>
<div class="layui-inline">
<div class="layui-input-inline">
<input id="purchaserName" class="layui-input" type="text" placeholder="请输入采购商"/>
</div>
</div>
<div class="layui-inline">
<div class="layui-input-inline">
<input id="supplierName" class="layui-input" type="text" placeholder="请输入供应商"/>
</div>
</div>
<div class="layui-inline">
<div class="layui-input-inline">
<input type="button" id="btnSearch" class="layui-btn layui-btn-primary"
style="background-color:deepskyblue;color: white" value="搜索"/>
</div>
</div>
</div>
<div class="layui-form-item">
<div class="layui-inline">
<div class="layui-input-inline">
<input id="beginTime" class="layui-input" type="text" placeholder="请输入开始时间"/>
</div>
<div class="layui-form-mid">-</div>
<div class="layui-input-inline">
<input type="text" id="endTime" class="layui-input" placeholder="请输入结束时间"/>
</div>
</div>
</div>
<div class="layui-inline">
<div class="layui-input-inline">
<input type="button" id="exportData" class="layui-btn layui-btn-primary"
style="background-color:deepskyblue;color: white" value="导出成交项目"/>
</div>
<div class="layui-input-inline">
<input type="button" id="exportData1" class="layui-btn layui-btn-primary"
style="background-color:deepskyblue;color: white" value="导出企业缴费情况"/>
</div>
</div>
<div class="layui-form-item" style="background-color: #ebebeb">
<div class="layui-inline">
<label class="layui-form-label" style="font-size: larger;text-align: left">成交项目</label>
</div>
<div class="layui-inline" style="position: absolute;right: 5%;margin-top:7px; ">
<div class="layui-btn-group">
<input type="button" class="layui-btn layui-btn-sm layui-btn-primary"
style="border-radius: 5px" id="allCharge" value="全部"/>
<input type="button" class="layui-btn layui-btn-sm layui-btn-primary"
style="border-radius: 5px" id="projectAgree" value="未付款"/>
<input type="button" class="layui-btn layui-btn-sm layui-btn-primary"
style="border-radius: 5px" id="projectDeal" value="已结项"/>
</div>
</div>
</div>
<table class="layui-hide" id="chargeTable" lay-filter="chargeTable"></table>
</div>
</div>
</div>
</div>
</div>
<form class="layui-form" id="payForm" lay-filter="payForm">
<div class="layui-fluid" id="payWin" style="display: none;">
<div class="layui-row layui-col-space15">
<div class="layui-card">
<div class="layui-card-body" style="vertical-align: middle;">
<input id="fileId" name="fileId" hidden>
<input id="candidateSupplierId" name="candidateSupplierId" hidden>
<input id="projectId" name="projectId" type="hidden"/>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">支付方式</label>
<div class="layui-input-block">
<input id="paymentType" name="taxpayerIdentification" type="text"
class="layui-input-win" readonly/>
<!--<input id="payWexType" type="radio" lay-filter="pay-way" value="0" name="paymentType" title="微信支付" />
<input id="payDianType" type="radio" lay-filter="pay-way" value="1" name="" title="电汇" checked/>-->
</div>
</div>
</div>
<div id="flag">
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">缴纳公司</label>
<div class="layui-input-block">
<input id="companyId" name="companyId" type="hidden"/>
<input id="companyName" name="companyName" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">缴款项目</label>
<div class="layui-input-block">
<input id="payProjectName" name="projectName" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">发票类型</label>
<div class="layui-input-block">
<input id="invoiceType" name="taxpayerIdentification" type="text"
class="layui-input-win" readonly/>
<!--<select id="invoiceType" name="invoiceType" lay-filter="invoiceType"
class="layui-input layui-form-select" style="width: 200px;">
<option value="1">电子发票</option>
<option value="2">纸质发票</option>
</select>-->
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline">
<label class="layui-form-label">邮寄地址</label>
<div class="layui-input-block">
<input id="mailAddress" name="mailAddress" type="text" class="layui-input-win" readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">纳税人识别码</label>
<div class="layui-input-block">
<input id="taxpayerIdentification" name="taxpayerIdentification" type="text"
class="layui-input-win" readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">注册地址</label>
<div class="layui-input-block">
<input id="regAddress" name="regAddress" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">注册电话</label>
<div class="layui-input-block">
<input id="regPhone" name="regPhone" type="text" class="layui-input-win" readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">开户银行</label>
<div class="layui-input-block">
<input id="bankOfDeposit" name="bankOfDeposit" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">银行账户</label>
<div class="layui-input-block">
<input id="bankAccount" name="bankAccount" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">领取方式</label>
<div class="layui-input-block">
<input type="hidden" id="receiveType" name="receiveType" value="1">
<input id="receiveTypeName" name="receiveTypeName" type="text" value="邮箱"
class="layui-input-win" readonly/>
</div>
</div>
</div>
<div class="layui-row contacts">
<div class="layui-inline ">
<label class="layui-form-label">联系人</label>
<input type="hidden" id="receiveInfoId">
<div class="layui-input-block">
<input id="rec_name" name="rec_name" type="text" class="layui-input-win" readonly/>
</div>
</div>
</div>
<div class="layui-row contacts">
<div class="layui-inline ">
<label class="layui-form-label">联系地址</label>
<div class="layui-input-block">
<input id="rec_address" name="rec_address" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
<div class="layui-row contacts">
<div class="layui-inline ">
<label class="layui-form-label">联系电话</label>
<div class="layui-input-block">
<input id="rec_phone" name="rec_phone" type="text" class="layui-input-win"
readonly/>
</div>
</div>
</div>
</div>
<div style="text-align: center;" class="layui-row">
<div class="layui-inline ">
<div id="payImg"></div>
</div>
</div>
<div class="layui-row">
<div class="layui-inline ">
<label class="layui-form-label">应付金额</label>
<div class="layui-inline">
<input id="paymentAmount" name="paymentAmount"
style="font-weight: 900;color: #0040ff;float: left;width: 40%;" type="text"
value="0" class="layui-input-win" readonly/>
<span style="line-height: 38px\9;">元</span>
</div>
</div>
</div>
<!--<div class="layui-row" id="payFile">
<div class="layui-inline">
<label class="layui-form-label">缴费凭证<i style="color: red">*</i></label>
</div>
<div class="layui-inline">
<input id="paymentVoucher" name="paymentVoucher" type="text"
class="layui-input-win" placeholder="请上传缴费凭证" readonly/>
</div>
<div class="layui-inline">
<button type="button" class="layui-btn layui-btn-xs layui-btn-warm" id="uploadPay"> 上传 </button>
</div>
</div>-->
<!--<div class="layui-row" style="vertical-align: middle;text-align: center;margin-top: 25px;" id="btnId">
<button class="layui-btn" lay-filter="btnSubmit" lay-submit> 确认 </button>
</div>-->
</div>
</div>
</div>
</div>
</form>
<style>
.layui-input-win {
color: #595959;
display: block;
width: 100%;
padding-left: 10px;
height: 38px;
line-height: 38px \9;
border-width: 0px;
/*border-style: solid;*/
background-color: #fff;
border-radius: 2px;
}
</style>
<script type="text/html" id="tableBar">
<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail" style="color: deepskyblue">详情</a>
{{# if(d.projectStatus == 'PROJECT_DEAL'){ }}
<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail" style="color: #9dc6dd">缴费情况</a>
<a class="layui-btn layui-btn-primary layui-btn_xs" lay-event="detail" style="color: #1E90FF">开票</a>
{{# }}}
</script>
@}三，后端实体类
package cn.stylefeng.guns.modular.administrator.chargeManage.excel;
import lombok.Data;
import java.io.Serializable;
import java.math.BigInteger;
/*
收件人的信息（表cez_receive_info）
*/
public class ExcelResult1 implements Serializable {
private static final long serialVersionUID = -3559124205694754311L;
private Double paymentAmount;
private String regAddress;
private String regPhone;
private String bankOfDeposit;
private String projectName;
private String recName;
private String recPhone;
private String recAddress;
private String mailAddress; //邮寄地址
private String companyName;
private String bankAccount;
public String getMailAddress() {
return mailAddress;
}
public void setMailAddress(String mailAddress) {
this.mailAddress = mailAddress;
}
public String getProjectName() {
return projectName;
}
public void setProjectName(String projectName) {
this.projectName = projectName;
}
public String getRecName() {
return recName;
}
public void setRecName(String recName) {
this.recName = recName;
}
public String getRecPhone() {
return recPhone;
}
public void setRecPhone(String recPhone) {
this.recPhone = recPhone;
}
public String getRecAddress() {
return recAddress;
}
public void setRecAddress(String recAddress) {
this.recAddress = recAddress;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public Double getPaymentAmount() {
return paymentAmount;
}
public void setPaymentAmount(Double paymentAmount) {
this.paymentAmount = paymentAmount;
}
public String getRegAddress() {
return regAddress;
}
public void setRegAddress(String regAddress) {
this.regAddress = regAddress;
}
public String getRegPhone() {
return regPhone;
}
public void setRegPhone(String regPhone) {
this.regPhone = regPhone;
}
public String getBankOfDeposit() {
return bankOfDeposit;
}
public void setBankOfDeposit(String bankOfDeposit) {
this.bankOfDeposit = bankOfDeposit;
}
public String getBankAccount() {
return bankAccount;
}
public void setBankAccount(String bankAccount) {
this.bankAccount = bankAccount;
}
}
四，ChargeManageController
package cn.stylefeng.guns.modular.administrator.chargeManage.controller;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult;
import cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult1;
import cn.stylefeng.guns.modular.administrator.chargeManage.service.ChargeManageService;
import cn.stylefeng.guns.util.ExportExcelUtils;
import cn.stylefeng.roses.core.base.controller.BaseController;
import cn.stylefeng.roses.kernel.model.response.ResponseData;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletResponse;
import java.math.BigInteger;
import java.util.*;
/**
* 收费管理
*
* @author YanPenghui
* @Date 2021-03-10
*/
@Slf4j
@Controller
@RequestMapping("/chargeManage")
public class ChargeManageController extends BaseController {
private String PREFIX = "/administrator";
@Autowired
private ChargeManageService chargeManageService;
/**
* 收费管理 页面
*
* @author YanPenghui
* @Date 2021-03-10
*/
@RequestMapping("")
public String index() {
return PREFIX + "/chargeManage.html";
}
/**
* 收费列表
*
* @author YanPenghui
* @Date 2021-03-11
*/
@RequestMapping("/chargeList")
@ResponseBody
public ResponseData getChargeList(String projectName, String projectCode, String purchaserName,
String supplierName, Date beginTime,Date endTime,String projectStatus) {
try {
LayuiPageInfo pageInfo = chargeManageService.findPageBySpec(projectName,projectCode,purchaserName,
supplierName,beginTime,endTime,projectStatus);
/* //合计行数据
Map<String,Object> total = chargeManageService.sumLineData(projectName,projectCode,purchaserName,
supplierName,beginTime,endTime,projectStatus);*/
Map<String,Object> result = new HashMap<>();
result.put("pageInfo",pageInfo);
//result.put("total",total);
return ResponseData.success(0,"收费列表查询成功",result);
}catch (Exception e){
log.error(e.toString());
return ResponseData.error("收费列表查询失败");
}
}
/**
* 收费列表合计
*
* @author YanPenghui
* @Date 2021-03-11
*/
@RequestMapping("/chargeList/total")
@ResponseBody
public ResponseData getChargeListTotal(String projectName, String projectCode, String purchaserName,
String supplierName, Date beginTime,Date endTime,String projectStatus) {
try {
//合计行数据
Map<String,Object> total = chargeManageService.sumLineData(projectName,projectCode,purchaserName,
supplierName,beginTime,endTime,projectStatus);
Map<String,Object> result = new HashMap<>();
result.put("total",total);
return ResponseData.success(0,"收费列表合计查询成功",result);
}catch (Exception e){
log.error(e.toString());
return ResponseData.error("收费列表查询失败");
}
}
/**
* 导出成交项目
* @Description:
* @author qianfeng qf98921@outlook.com
* @date 2021/9/24 9:54
* @param
* @return
*/
@RequestMapping(value = "/chargeList/export")
public void export(String projectName, String projectCode, String purchaserName,
String supplierName, Date beginTime, Date endTime,
String projectStatus , HttpServletResponse response){
//得到所有要导出的数据
List<ExcelResult> list = chargeManageService.export(projectName,projectCode,purchaserName,
supplierName,beginTime,endTime,projectStatus);
System.out.println(list);
//定义导出的excel名字
String excelName = "成交项目表";
//获取需要转出的excel表头的map字段
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
fieldMap.put("projectName","项目名称");
fieldMap.put("projectCode","项目编号");
fieldMap.put("transactionTime","成交时间");
fieldMap.put("companyName","采购商");
fieldMap.put("supplierName","中标");
fieldMap.put("dealAmount","成交价（元）");
fieldMap.put("projectStatus","状态");
//导出用户相关信息
new ExportExcelUtils().export(excelName,list,fieldMap,response);
}
/**
* 导出缴费情况
*
* @Description:
* @author qianfeng qf98921@outlook.com
* @date 2021/9/24 9:54
* @param
* @return
*/
@RequestMapping(value = "/chargeList/export1")
public void export1(
Double paymentAmount,
String companyName,
String projectName,
String regAddress,
String regPhone,
String bankOfDeposit,
String bankAccount,
String recName,
String recPhone,
String recAddress,
String mailAddress,
HttpServletResponse response
){
//得到所有要导出的数据
List<ExcelResult1> list = chargeManageService.export1(
paymentAmount,companyName,projectName,
regAddress,regPhone, bankOfDeposit,
bankAccount,recName,recPhone,
recAddress,mailAddress);
System.out.println(list);
//定义导出的excel名字
String excelName = "导出企业缴费情况";
//获取需要转出的excel表头的map字段
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
fieldMap.put("paymentAmount","应付金额");
fieldMap.put("companyName","项目名称");
fieldMap.put("projectName","公司名称");
fieldMap.put("regAddress","注册地址");
fieldMap.put("regPhone","注册电话");
fieldMap.put("bankOfDeposit","开户银行");
fieldMap.put("bankAccount","银行账户");
fieldMap.put("recName","联系人");
fieldMap.put("recPhone","联系电话");
fieldMap.put("recAddress","联系地址");
fieldMap.put("mailAddress","邮寄地址");
//导出用户相关信息
new ExportExcelUtils().export(excelName,list,fieldMap,response);
}
/**
* 收费管理 页面
* 未付款、已结项统计
* @author YanPenghui
* @Date 2021-03-10
*/
@ResponseBody
@RequestMapping("/statistic")
public ResponseData getStatistic(){
try {
List<Map<String,Object>> result = chargeManageService.statistic();
return ResponseData.success(0,"类型统计成功",result);
}catch (Exception e){
log.error(e.toString());
return ResponseData.error("类型统计失败");
}
}
/**
* 收费管理 详情页面
*
* @author YanPenghui
* @Date 2021-03-12
*/
@RequestMapping("/detail")
public String detail() {
return PREFIX + "/chargeManageDetail.html";
}
/**
* 缴费情况数据查询
*
* @Description:
* @author qianfeng qf98921@outlook.com
* @date 2021/9/23 15:11
* @param
* @return
*/
@ResponseBody
@RequestMapping(value = "/payCostDetail")
public ResponseData payCost(String projectId){
if (StringUtils.isBlank(projectId)) {
return ResponseData.error("参数丢失");
}
Map<String,Object> dataMap=chargeManageService.getPayCost(projectId);
return ResponseData.success(0,"成功",dataMap);
}
/**
* 收费管理 详情页面
* 项目信息
* @author YanPenghui
* @Date 2021-03-12
*/
@ResponseBody
@RequestMapping("/detail/projectInfo")
public ResponseData getProjectInfo(Long projectId,Long supplierId,Boolean hasItem){
try {
Map<String,Object> result = chargeManageService.projectInfo(projectId,supplierId,hasItem);
return ResponseData.success(0,"项目信息获取成功",result);
}catch (Exception e){
log.error(e.toString());
return ResponseData.error("项目信息获取失败");
}
}
}
四-1 导出判断类：
package cn.stylefeng.guns.util;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* @Description:
* @date 2019/7/11 14:53
* @param
* @return
*/
public class ExportExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);
/**
* 导出Excel
*
* @param excelName 要导出的excel名称
* @param list 要导出的数据集合
* @param fieldMap 中英文字段对应Map，即要导出的excel表头
* @param response 使用response可以导出到浏览器
* @param <T>
*/
public static <T> void export(String excelName, List<T> list, LinkedHashMap<String, String> fieldMap, HttpServletResponse response) {
// 设置默认文件名为当前时间：年月日时分秒
if (excelName == null || excelName == "") {
excelName = new SimpleDateFormat("yyyyMMddhhmmss").format(
new Date()).toString();
}
// 设置response头信息
response.reset();
response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件
try {
response.setHeader("Content-disposition", "attachment; filename="
+ new String(excelName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
} catch (UnsupportedEncodingException e1) {
logger.info(e1.getMessage());
}
try {
//创建一个WorkBook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//在Workbook中，创建一个sheet，对应Excel中的工作薄（sheet）
HSSFSheet sheet = wb.createSheet(excelName);
//创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
//创建一个居中格式
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 填充工作表
fillSheet(sheet, list, fieldMap, style);
//将文件输出
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
logger.info("导出Excel失败！");
logger.error(e.getMessage());
}
}
/**
* 根据字段名获取字段对象
*
* @param fieldName 字段名
* @param clazz 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
logger.info("根据字段名获取字段对象:getFieldByName()");
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
//如果本类中存在该字段，则返回
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
//递归
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
/**
* 根据字段名获取字段值
*
* @param fieldName 字段名
* @param o 对象
* @return 字段值
* @throws Exception 异常
*/
public static Object getFieldValueByName(String fieldName, Object o)
throws Exception {
logger.info("根据字段名获取字段值:getFieldValueByName()");
Object value = null;
//根据字段名得到字段对象
Field field = getFieldByName(fieldName, o.getClass());
//如果该字段存在，则取出该字段的值
if (field != null) {
field.setAccessible(true);//类中的成员变量为private,在类外边使用属性值，故必须进行此操作
value = field.get(o);//获取当前对象中当前Field的value
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 "
+ fieldName);
}
return value;
}
/**
* 根据带路径或不带路径的属性名获取属性值,即接受简单属性名，
* 如userName等，又接受带路径的属性名，如student.department.name等
*
* @param fieldNameSequence 带路径的属性名或简单属性名
* @param o 对象
* @return 属性值
* @throws Exception 异常
*/
public static Object getFieldValueByNameSequence(String fieldNameSequence,
Object o) throws Exception {
logger.info("根据带路径或不带路径的属性名获取属性值,即接受简单属性名:getFieldValueByNameSequence()");
Object value = null;
// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split("\\.");
if (attributes.length == 1) {
value = getFieldValueByName(fieldNameSequence, o);
} else {
// 根据数组中第一个连接属性名获取连接属性对象，如student.department.name
Object fieldObj = getFieldValueByName(attributes[0], o);
//截取除第一个属性名之后的路径
String subFieldNameSequence = fieldNameSequence
.substring(fieldNameSequence.indexOf(".") + 1);
//递归得到最终的属性对象的值
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* 向工作表中填充数据
*
* @param sheet excel的工作表名称
* @param list 数据源
* @param fieldMap 中英文字段对应关系的Map
* @param style 表格中的格式
* @throws Exception 异常
*/
public static <T> void fillSheet(HSSFSheet sheet, List<T> list,
LinkedHashMap<String, String> fieldMap, HSSFCellStyle style) throws Exception {
logger.info("向工作表中填充数据:fillSheet()");
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];
// 填充数组
int count = 0;
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
//在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 填充表头
for (int i = 0; i < cnFields.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(cnFields[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
}
// 填充内容
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 1);
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < enFields.length; i++) {
Object objValue = getFieldValueByNameSequence(enFields[i], item);
if (enFields[i].equals("projectStatus")) {
if ("PROJECT_AGREED".equals(objValue)){
objValue="未付款";
}else if ("PROJECT_DEAL".equals(objValue)){
objValue="已结项";
}else {
objValue="未知";
}
row.createCell(i).setCellValue(objValue.toString());
continue;
}
String fieldValue = objValue == null ? "" : objValue.toString();
row.createCell(i).setCellValue(fieldValue);
}
}
}
}五，后端实现类(ChargeManageService)
package cn.stylefeng.guns.modular.administrator.chargeManage.service;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult;
import cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult1;
import cn.stylefeng.guns.modular.administrator.chargeManage.mapper.ChargeManageMapper;
import cn.stylefeng.guns.util.ExcelUtil;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* <p>
* 收费管理 服务实现类
* </p>
*
* @author YanPenghui
* @since 2021-03-11
*/
@Service
public class ChargeManageService {
@Resource
private ChargeManageMapper chargeManageMapper;
public LayuiPageInfo findPageBySpec(String projectName, String projectCode, String purchaserName,
String supplierName, Date beginTime, Date endTime, String projectStatus) {
Page pageContext = getPageContext();
IPage page = chargeManageMapper.customPageList(pageContext, projectName, projectCode, purchaserName,
supplierName, beginTime, endTime, projectStatus);
return LayuiPageFactory.createPageInfo(page);
}
public Map<String, Object> sumLineData(String projectName, String projectCode, String purchaserName,
String supplierName, Date beginTime, Date endTime, String projectStatus) {
Map<String, Object> total = chargeManageMapper.sumLineData(projectName, projectCode, purchaserName,
supplierName, beginTime, endTime, projectStatus);
return total;
}
public List<Map<String, Object>> statistic() {
return chargeManageMapper.statistic();
}
public Map<String, Object> projectInfo(Long projectId, Long supplierId, Boolean hasItem) {
return chargeManageMapper.projectInfo(projectId, supplierId, hasItem);
}
private Page getPageContext() {
return LayuiPageFactory.defaultPage();
}
public List<ExcelResult> export(String projectName,
String projectCode,
String purchaserName,
String supplierName,
Date beginTime,
Date endTime,
String status
) {
return chargeManageMapper.customList(projectName, projectCode, purchaserName,
supplierName, beginTime, endTime,status);
}
/*
导出缴费情况
*/
public List<ExcelResult1> export1(Double paymentAmount,
String companyName,
String projectName,
String regAddress,
String regPhone,
String bankOfDeposit,
String bankAccount,
String recName,
String recPhone,
String recAddress,
String mailAddress) {
return chargeManageMapper.customList1(
paymentAmount,companyName,
projectName,regAddress,regPhone,
bankOfDeposit,bankAccount,recName,recPhone,
recAddress,mailAddress);
}
public Map<String, Object> getPayCost(String projectId) {
return chargeManageMapper.getPayCost(projectId);
}
}
六，Mapper 接口
package cn.stylefeng.guns.modular.administrator.chargeManage.mapper;
import cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult;
import cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult1;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* <p>
* 收费管理 Mapper 接口
* </p>
*
* @author YanPenghui
* @since 2021-03-11
*/
public interface ChargeManageMapper {
/**
* 获取分页实体列表
*
* @author YanPenghui
* @Date 2021-03-11
*/
Page customPageList(
@Param("page") Page page,
@Param("projectName") String projectName,
@Param("projectCode") String projectCode,
@Param("purchaserName") String purchaserName,
@Param("supplierName") String supplierName,
@Param("beginTime") Date beginTime,
@Param("endTime") Date endTime,
@Param("projectStatus") String projectStatus);
List<ExcelResult> customList(
@Param("projectName") String projectName,
@Param("projectCode") String projectCode,
@Param("purchaserName") String purchaserName,
@Param("supplierName") String supplierName,
@Param("beginTime") Date beginTime,
@Param("endTime") Date endTime,
@Param("projectStatus") String projectStatus
);
/*
导出缴费情况
*/
List<ExcelResult1> customList1(@Param("paymentAmount") Double paymentAmount,
@Param("companyName") String companyName,
@Param("projectName") String projectName,
@Param("regAddress") String regAddress,
@Param("regPhone") String regPhone,
@Param("bankOfDeposit") String bankOfDeposit,
@Param("bankAccount") String bankAccount,
@Param("recName") String recName,
@Param("recPhone") String recPhone,
@Param("recAddress") String recAddress,
@Param("mailAddress") String mailAddress);
/**
* 获取合计
*
* @author YanPenghui
* @Date 2021-03-11
*/
Map<String, Object> sumLineData(
@Param("projectName") String projectName,
@Param("projectCode") String projectCode,
@Param("purchaserName") String purchaserName,
@Param("supplierName") String supplierName,
@Param("beginTime") Date beginTime,
@Param("endTime") Date endTime,
@Param("projectStatus") String projectStatus);
/**
* 获取未付款、已结项统计
*
* @author YanPenghui
* @Date 2021-03-11
*/
List<Map<String, Object>> statistic();
/**
* 获取项目信息
*
* @author YanPenghui
* @Date 2021-03-12
*/
Map<String, Object> projectInfo(
@Param("projectId") Long projectId,
@Param("supplierId") Long supplierId,
@Param("hasItem") Boolean hasItem);
Map<String, Object> getPayCost(String projectId);
}
七 操作数据库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="cn.stylefeng.guns.modular.administrator.chargeManage.mapper.ChargeManageMapper">
<select id="customPageList" resultType="map" >
SELECT p.id,p.project_name as projectName,p.project_code as projectCode,s.project_status as projectStatus,
p.transaction_time transactionTime,p.deal_amount as pDealAmount,p.has_item hasItem,
p.company_id as companyId,p.company_name as companyName,s.supplier_id as supplierId,
c.company_name as supplierName,
case when p.has_item = 0 then o.offer_amount
when p.has_item = 1 then ifnull(o.offer_amount*i.item_count,0) end as dealAmount
FROM cez_project_info p
INNER JOIN cez_supplier_sign_up s on p.id = s.project_id
INNER JOIN cez_company_info c on s.supplier_id = c.id
INNER JOIN cez_supplier_offer o on o.project_id = p.id and s.supplier_id = o.supplier_id and o.is_new = 1 and o.is_deal = 1
LEFT JOIN cez_project_item i on i.project_id = p.id and o.item_id = i.id
where 1=1
and p.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
<choose>
<when test="projectStatus != null and projectStatus != '' ">
AND s.project_status = #{projectStatus}
</when>
<otherwise>
and s.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
</otherwise>
</choose>
<if test="projectName != null and projectName != '' ">
AND p.project_name like CONCAT('%',#{projectName},'%')
</if>
<if test="projectCode != null and projectCode != '' ">
AND p.project_code = #{projectCode}
</if>
<if test="purchaserName != null and purchaserName != '' ">
AND p.company_name like CONCAT('%',#{purchaserName},'%')
</if>
<if test="supplierName != null and supplierName != '' ">
AND c.company_name like CONCAT('%',#{supplierName},'%')
</if>
<if test="beginTime != null and endTime != null ">
AND p.transaction_time >= #{beginTime}
AND p.transaction_time <= #{endTime}
</if>
<if test="beginTime != null and endTime == null ">
AND p.transaction_time >= #{beginTime}
</if>
<if test="beginTime == null and endTime != null">
AND p.transaction_time <= #{endTime}
</if>
</select>
<select id="sumLineData" resultType="map">
select sum(a.total) as total from (
SELECT
case when p.has_item = 0 then ifnull(o.offer_amount,0)
when p.has_item = 1 then ifnull(o.offer_amount*i.item_count,0) end as total
FROM cez_project_info p
INNER JOIN cez_supplier_sign_up s on p.id = s.project_id
INNER JOIN cez_company_info c on s.supplier_id = c.id
INNER JOIN cez_supplier_offer o on o.project_id = p.id and
s.supplier_id = o.supplier_id and o.is_new = 1 and
o.is_deal = 1
LEFT JOIN cez_project_item i on i.project_id = p.id and o.item_id = i.id
where 1=1
and p.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
<choose>
<when test="projectStatus != null and projectStatus != '' ">
AND s.project_status = #{projectStatus}
</when>
<otherwise>
and s.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
</otherwise>
</choose>
<if test="projectName != null and projectName != '' ">
AND p.project_name like CONCAT('%',#{projectName},'%')
</if>
<if test="projectCode != null and projectCode != '' ">
AND p.project_code = #{projectCode}
</if>
<if test="purchaserName != null and purchaserName != '' ">
AND p.company_name like CONCAT('%',#{purchaserName},'%')
</if>
<if test="supplierName != null and supplierName != '' ">
AND c.company_name like CONCAT('%',#{supplierName},'%')
</if>
<if test="beginTime != null and endTime != null ">
AND p.transaction_time >= #{beginTime}
AND p.transaction_time <= #{endTime}
</if>
<if test="beginTime != null and endTime == null ">
AND p.transaction_time >= #{beginTime}
</if>
<if test="beginTime == null and endTime != null">
AND p.transaction_time <= #{endTime}
</if>
) a
</select>
<select id="statistic" resultType="map">
SELECT s.project_status as projectStatus,count(1) as num
FROM cez_project_info p
INNER JOIN cez_supplier_sign_up s on p.id = s.project_id
INNER JOIN cez_company_info c on s.supplier_id = c.id
INNER JOIN cez_supplier_offer o on o.project_id = p.id and s.supplier_id = o.supplier_id and o.is_new = 1 and o.is_deal = 1
LEFT JOIN cez_project_item i on i.project_id = p.id and o.item_id = i.id
where 1=1 and s.project_status in ('PROJECT_DEAL','PROJECT_AGREED') AND p.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
group by s.project_status
</select>
<select id="projectInfo" resultType="map">
SELECT
ifnull(p.deal_amount,'') AS dealAmount,
ifnull(p.service_fee,'') AS serviceFee,
ifnull(p.project_status,'') AS projectStatus,
ifnull(p.project_name,'') AS projectName,
ifnull(p.project_code,'') AS projectCode,
ifnull(p.sign_up_begin,'') AS signUpBegin,
ifnull(p.sign_up_end,'') AS signUpEnd,
ifnull(p.offer_begin,'') AS offerBegin,
ifnull(p.offer_end,'') AS offerEnd,
ifnull(p.purchase_content,'') AS purchaseContent,
ifnull(r.type_name,'') AS productTypeName,
r.has_tax AS hasTax,
ifnull(r.transaction_method,'') AS transactionMethod,
ifnull(r.offer_count,'') AS offerCount,
(
CASE
WHEN p.has_item = 0 THEN
p.offer_amount_grad
WHEN p.has_item = 1 THEN
i.price_grad
END
) AS priceGrad,
ifnull(r.offer_rule,'') AS offerRule,
ifnull(i.item_name,'') AS itemName,
ifnull(i.item_count,'') AS itemCount,
ifnull(i.item_type,'') AS itemType,
(
CASE
WHEN p.has_item = 0
AND p.offer_type = 0 THEN
ifnull(p.offer_amount_begin,'')
WHEN p.has_item = 1
AND p.offer_type = 0 THEN
ifnull(i.begin_price,'') else ''
END
) AS ceilIngPrice,
(
CASE
WHEN p.has_item = 0
AND p.offer_type = 1 THEN
ifnull(p.offer_amount_begin,'')
WHEN p.has_item = 1
AND p.offer_type = 1 THEN
ifnull(i.begin_price,'') else ''
END
) AS floorPrice,
ifnull(p.company_name,'') AS purchaseOrgName,
ifnull(p.contacts,'') AS purchaserName,
ifnull(p.contacts_phone,'') as purchaserPhone,
ifnull(r.entrust_org_name,'') AS agencyOrgName,
ifnull(r.entrust_user,'') AS agencyOrgUserName,
ifnull(r.entrust_phone,'') AS agencyOrgUserPhone
FROM
cez_project_info p
LEFT JOIN cez_project_report r ON p.id = r.project_id
LEFT JOIN cez_project_item i ON p.id = i.project_id
WHERE 1=1 and
<choose>
<when test=" hasItem == 0 ">
<if test="projectId != '' and projectId != null ">
p.id = #{projectId}
</if>
</when>
<otherwise>
<if test="projectId != '' and projectId != null ">
p.id = #{projectId}
<if test="supplierId != '' and supplierId != null ">
and i.id = (select item_id from cez_supplier_offer where project_id = #{projectId} and supplier_id= #{supplierId} and is_new=1 and is_deal=1 )
</if>
</if>
</otherwise>
</choose>
</select>
<select id="customList1" resultType="cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult1">
SELECT
t.id,
t.payment_amount as paymentAmount,
c.company_name as companyName,
p.project_name as projectName,
i.reg_address as regAddress,
i.reg_phone as regPhone,
i.bank_of_deposit as bankOfDeposit,
i.bank_account as bankAccount,
r.rec_name as recName,
r.rec_phone as recPhone,
r.rec_mobile,
r.rec_address as recAddress,
r.mail_address as mailAddress
FROM
cez_supplier_payment_log AS t
left join cez_company_info as c on t.company_id=c.id
left join cez_invoice_info as i on i.company_id=c.id
left join cez_receive_info as r on r.company_id=c.id
left join cez_project_info AS p ON p.id=t.project_id
<if test="paymentAmount != null and paymentAmount != '' ">
AND t.payment_amount like CONCAT('%',#{paymentAmount},'%')
</if>
<if test="regAddress != null and regAddress != '' ">
AND invo.reg_address like CONCAT('%',#{regAddress},'%')
</if>
<if test="regPhone != null and regPhone != '' ">
AND invo.reg_phone like CONCAT('%',#{regPhone},'%')
</if>
<if test="bankOfDeposit != null and bankOfDeposit != '' ">
AND invo.bank_of_deposit like CONCAT('%',#{bankOfDeposit},'%')
</if>
<if test="bankAccount != null and bankAccount1 != '' ">
AND invo.bank_account like CONCAT('%',#{bankAccount},'%')
</if>
<if test="recName != null and recName != '' ">
AND r.rec_name like CONCAT('%',#{recName},'%')
</if>
<if test="recPhone != null and recPhone != '' ">
AND r.rec_phone like CONCAT('%',#{recPhone},'%')
</if>
<if test="recAddress != null and recAddress != '' ">
AND r.rec_address like CONCAT('%',#{recAddress},'%')
</if>
<if test="mailAddress != null and mailAddress != '' ">
AND r.mail_address like CONCAT('%',#{mailAddress},'%')
</if>
<if test="projectName != null and projectName != '' ">
AND p.project_name like CONCAT('%',#{projectName},'%')
</if>
<where>
t.payment_amount is not NULL
</where>
</select>
<select id="customList" resultType="cn.stylefeng.guns.modular.administrator.chargeManage.excel.ExcelResult">
SELECT
p.id,
p.project_name as projectName,
p.project_code as projectCode,
s.project_status as projectStatus,
p.transaction_time transactionTime,
p.deal_amount as pDealAmount,
p.has_item hasItem,
p.company_id as companyId,
p.company_name as companyName,
s.supplier_id as supplierId,
c.company_name as supplierName,
case when p.has_item = 0 then o.offer_amount
when p.has_item = 1 then ifnull(o.offer_amount*i.item_count,0) end as dealAmount
FROM cez_project_info p
INNER JOIN cez_supplier_sign_up s on p.id = s.project_id
INNER JOIN cez_company_info c on s.supplier_id = c.id
INNER JOIN cez_supplier_offer o on o.project_id = p.id and s.supplier_id = o.supplier_id and o.is_new = 1 and o.is_deal = 1
LEFT JOIN cez_project_item i on i.project_id = p.id and o.item_id = i.id
where 1=1
and p.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
<choose>
<when test="projectStatus != null and projectStatus != '' ">
AND s.project_status = #{projectStatus}
</when>
<otherwise>
and s.project_status in ('PROJECT_DEAL','PROJECT_AGREED')
</otherwise>
</choose>
<if test="projectName != null and projectName != '' ">
AND p.project_name like CONCAT('%',#{projectName},'%')
</if>
<if test="projectCode != null and projectCode != '' ">
AND p.project_code = #{projectCode}
</if>
<if test="purchaserName != null and purchaserName != '' ">
AND p.company_name like CONCAT('%',#{purchaserName},'%')
</if>
<if test="supplierName != null and supplierName != '' ">
AND c.company_name like CONCAT('%',#{supplierName},'%')
</if>
<if test="beginTime != null and endTime != null ">
AND p.transaction_time >= #{beginTime}
AND p.transaction_time <= #{endTime}
</if>
<if test="beginTime != null and endTime == null ">
AND p.transaction_time >= #{beginTime}
</if>
<if test="beginTime == null and endTime != null">
AND p.transaction_time <= #{endTime}
</if>
</select>
<select id="getPayCost" resultType="java.util.Map">
SELECT
t.id,
t.payment_amount,
t.payment_type,
t.invoice_type,
t.user_id,
c.company_name,
i.taxpayer_identification,
i.reg_address,
i.reg_phone,
i.bank_of_deposit,
i.bank_account,
r.rec_name,
r.rec_mobile,
r.rec_phone,
r.rec_address,
r.mail_address
FROM
cez_supplier_payment_log AS t
left join cez_company_info as c on t.company_id=c.id
left join cez_invoice_info as i on i.company_id=c.id
left join cez_receive_info as r on r.company_id=c.id
WHERE
project_id = #{projectId}
limit 1
</select>
</mapper>
320
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
