From d0c3258ed4871f2b9f03357bfd39b6caeff156b0 Mon Sep 17 00:00:00 2001
From: 李林 <z1292839451@163.com>
Date: 星期二, 09 一月 2024 11:07:59 +0800
Subject: [PATCH] 修订版3.0
---
data-server/src/main/java/com/yuanchu/mom/service/impl/FansSubmitServiceImpl.java | 2
data-server/src/main/java/com/yuanchu/mom/service/impl/FinanceSubmitServiceImpl.java | 16 ++
data-server/src/main/resources/mapper/FinanceSubmitMapper.xml | 14 --
data-server/src/main/java/com/yuanchu/mom/service/DataReportingService.java | 3
data-server/src/main/java/com/yuanchu/mom/dto/DataComparisonDto.java | 2
data-server/src/main/java/com/yuanchu/mom/controller/DataReportingController.java | 68 +++++++++++++
data-server/src/main/java/com/yuanchu/mom/pojo/FinanceSubmit.java | 3
data-server/src/main/java/com/yuanchu/mom/service/impl/DataReportingServiceImpl.java | 50 +++++++++
data-server/src/main/resources/mapper/DataReportingMapper.xml | 135 ++++++++++++++------------
9 files changed, 209 insertions(+), 84 deletions(-)
diff --git a/data-server/src/main/java/com/yuanchu/mom/controller/DataReportingController.java b/data-server/src/main/java/com/yuanchu/mom/controller/DataReportingController.java
index 0704d60..2aa2035 100644
--- a/data-server/src/main/java/com/yuanchu/mom/controller/DataReportingController.java
+++ b/data-server/src/main/java/com/yuanchu/mom/controller/DataReportingController.java
@@ -286,6 +286,72 @@
return Result.success(stringObjectMap);
}
- public static void main(String[] args) {
+ @ApiOperation(value = "瀵煎嚭璐㈠姟涓婃姤鍒楄〃")
+ @PostMapping("/downFinanceSubmitFile")
+ public Result downFinanceSubmitFile(@RequestBody Map<String, Object> data) {
+ Page page = JSON.parseObject(JSON.toJSONString(data.get("page")), Page.class);
+ FinanceSubmit financeSubmit = JSON.parseObject(JSON.toJSONString(data.get("entity")), FinanceSubmit.class);
+ return Result.success("瀵煎嚭鎴愬姛", dataReportingService.downFinanceSubmitFile(page, financeSubmit));
+ }
+
+ @ApiOperation(value = "瀵煎叆璐㈠姟涓婃姤鍒楄〃")
+ @PostMapping("/inputFinanceSubmitCsv")
+ public Result<?> inputFinanceSubmitCsv(@RequestParam("file") MultipartFile file) throws IOException {
+ CsvReader reader = CsvUtil.getReader();
+ CsvData data = reader.read(new InputStreamReader(file.getInputStream(), "GB2312"));
+ List<CsvRow> rows = data.getRows();
+ String format;
+ try {
+ format = new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat("yyyyMMdd").parse(rows.get(2).get(0).split("锛�")[1]));
+ } catch (ParseException e) {
+ throw new ErrorException("鏂囦欢涓棩鏈熷湪杞崲鏃跺嚭鐜伴敊璇�");
+ }
+ for (int i = 6; i < rows.size(); i++) {
+ FinanceSubmit financeSubmit = new FinanceSubmit();
+ for (int i1 = 0; i1 < rows.get(5).size(); i1++) {
+ String str = rows.get(i).get(i1);
+ if (str == null) continue;
+ switch (rows.get(5).get(i1)) {
+ case "浠ｇ悊鍟�":
+ financeSubmit.setCompany(str);
+ break;
+ case "璐︽埛鍚�":
+ financeSubmit.setName(str);
+ break;
+ case "閲戦":
+ try {
+ financeSubmit.setConsumption(new BigDecimal(str));
+ } catch (Exception e) {
+ }
+ break;
+ case "浠ｇ悊杩旂偣":
+ try {
+ financeSubmit.setAgentRebate(new BigDecimal(str));
+ } catch (Exception e) {
+ }
+ break;
+ case "鎵撴浜烘皯甯�":
+ try {
+ financeSubmit.setMoney(new BigDecimal(str));
+ } catch (Exception e) {
+ }
+ break;
+ case "鍛樺伐杩旂偣":
+ try {
+ financeSubmit.setEmployeeRebate(new BigDecimal(str));
+ } catch (Exception e) {
+ }
+ break;
+ case "鍛樺伐杩旂偣浜烘皯甯�":
+ try {
+ financeSubmit.setEmployeeRebateMoney(new BigDecimal(str));
+ } catch (Exception e) {
+ }
+ break;
+ }
+ }
+ financeSubmitService.addFinanceSubmit(financeSubmit, format);
+ }
+ return Result.success();
}
}
diff --git a/data-server/src/main/java/com/yuanchu/mom/dto/DataComparisonDto.java b/data-server/src/main/java/com/yuanchu/mom/dto/DataComparisonDto.java
index dacc791..3b72388 100644
--- a/data-server/src/main/java/com/yuanchu/mom/dto/DataComparisonDto.java
+++ b/data-server/src/main/java/com/yuanchu/mom/dto/DataComparisonDto.java
@@ -33,7 +33,6 @@
@ApiModelProperty("鐧昏鏃堕棿")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
- @ValueTableShow(4)
private LocalDateTime createTime;
@ApiModelProperty("瀵规瘮鐨勮处鍙�")
@@ -50,6 +49,5 @@
@ApiModelProperty("瀵规瘮鐨勭櫥璁版椂闂�")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
- @ValueTableShow(8)
private LocalDateTime comparisonCreateTime;
}
diff --git a/data-server/src/main/java/com/yuanchu/mom/pojo/FinanceSubmit.java b/data-server/src/main/java/com/yuanchu/mom/pojo/FinanceSubmit.java
index 4cca3bc..20dfcec 100644
--- a/data-server/src/main/java/com/yuanchu/mom/pojo/FinanceSubmit.java
+++ b/data-server/src/main/java/com/yuanchu/mom/pojo/FinanceSubmit.java
@@ -13,6 +13,7 @@
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.yuanchu.mom.annotation.ValueTableShow;
+import com.yuanchu.mom.common.OrderBy;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
@@ -27,7 +28,7 @@
@Data
@AllArgsConstructor
@NoArgsConstructor
-public class FinanceSubmit implements Serializable {
+public class FinanceSubmit extends OrderBy implements Serializable {
/**
* 涓婚敭
diff --git a/data-server/src/main/java/com/yuanchu/mom/service/DataReportingService.java b/data-server/src/main/java/com/yuanchu/mom/service/DataReportingService.java
index ea9d584..75e00b1 100644
--- a/data-server/src/main/java/com/yuanchu/mom/service/DataReportingService.java
+++ b/data-server/src/main/java/com/yuanchu/mom/service/DataReportingService.java
@@ -9,6 +9,7 @@
import com.yuanchu.mom.pojo.DataReporting;
import com.baomidou.mybatisplus.extension.service.IService;
import com.yuanchu.mom.pojo.FansSubmit;
+import com.yuanchu.mom.pojo.FinanceSubmit;
import java.util.List;
import java.util.Map;
@@ -44,4 +45,6 @@
Map<String, Object> selectDataComparisonDtoPageList(Page page, DataComparisonDto dataComparisonDto);
+ String downFinanceSubmitFile(IPage<FinanceSubmit> page, FinanceSubmit financeSubmit);
+
}
diff --git a/data-server/src/main/java/com/yuanchu/mom/service/impl/DataReportingServiceImpl.java b/data-server/src/main/java/com/yuanchu/mom/service/impl/DataReportingServiceImpl.java
index 6df59c6..3a1de81 100644
--- a/data-server/src/main/java/com/yuanchu/mom/service/impl/DataReportingServiceImpl.java
+++ b/data-server/src/main/java/com/yuanchu/mom/service/impl/DataReportingServiceImpl.java
@@ -25,9 +25,11 @@
import com.yuanchu.mom.mapper.FansSubmitMapper;
import com.yuanchu.mom.pojo.DataReporting;
import com.yuanchu.mom.pojo.FansSubmit;
+import com.yuanchu.mom.pojo.FinanceSubmit;
import com.yuanchu.mom.service.DataReportingService;
import com.yuanchu.mom.mapper.DataReportingMapper;
import com.yuanchu.mom.service.FansSubmitService;
+import com.yuanchu.mom.service.FinanceSubmitService;
import com.yuanchu.mom.utils.Jwt;
import com.yuanchu.mom.utils.QueryWrappers;
import com.yuanchu.mom.utils.ServletUtils;
@@ -74,6 +76,9 @@
@Resource
private FansSubmitService fansSubmitService;
+ @Resource
+ private FinanceSubmitService financeSubmitService;
+
@Override
public Map<String, Object> selectDataReportingList(IPage<DataReportingPageDto> page, DataReportingPageDto dataReportingPageDto) {
Map<String, Object> map = new HashMap<>();
@@ -100,6 +105,15 @@
dataReporting2.setConsumption(dataReporting.getCustomerCosts().multiply(BigDecimal.valueOf(dataReporting.getFansAdd())));
} catch (Exception e) {
}
+ try {
+ dataReporting2.setDiscountedConsumption(dataReporting.getConsumption().divide(dataReporting.getAgentRebate(), 2, RoundingMode.HALF_UP));
+ }catch (Exception e){}
+ try {
+ dataReporting2.setActualCost(dataReporting.getAccountConsumption().divide(new BigDecimal(dataReporting.getFansAdd()), 2, RoundingMode.HALF_UP));
+ }catch (Exception e){}
+ try {
+ dataReporting2.setProfit(dataReporting.getRebateConsumption().subtract(dataReporting.getConsumption()));
+ }catch (Exception e){}
return dataReportingMapper.updateById(dataReporting2);
}
@@ -144,7 +158,7 @@
if (date == null) {
date = LocalDate.now().minusDays(1).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
};
- DataReporting one = dataReportingMapper.selectOne(Wrappers.<DataReporting>lambdaQuery().eq(DataReporting::getProduct, dataReporting.getProduct()).eq(DataReporting::getName, dataReporting.getName()).like(DataReporting::getCreateTime, date));
+ DataReporting one = dataReportingMapper.selectOne(Wrappers.<DataReporting>lambdaQuery().eq(DataReporting::getProduct, dataReporting.getProduct()).eq(DataReporting::getName, dataReporting.getName()).eq(DataReporting::getCreateUser, userId).like(DataReporting::getCreateTime, date));
try {
dataReporting.setRebateConsumption(dataReporting.getAccountConsumption().divide(dataReporting.getCustomerRebate(), 2, RoundingMode.HALF_UP));
} catch (Exception e) {
@@ -199,7 +213,7 @@
CsvWriter writer = CsvUtil.getWriter(path, CharsetUtil.CHARSET_GBK);
writer.write(new String[]{"瀵煎嚭鏃堕棿锛�"+time});
writer.write(new String[]{});
- writer.write(new String[]{});
+ writer.write(new String[]{"鏃堕棿鑼冨洿锛�"+dataReportingPageDto.getCreateTime().format(DateTimeFormatter.ofPattern("yyyyMMdd"))});
writer.write(new String[]{});
writer.write(new String[]{});
List<Map<String, String>> head = JSON.parseObject(JSON.toJSONString(map.get("head")), List.class);
@@ -286,7 +300,36 @@
CsvWriter writer = CsvUtil.getWriter(path, CharsetUtil.CHARSET_GBK);
writer.write(new String[]{"瀵煎嚭鏃堕棿锛�"+time});
writer.write(new String[]{});
+ writer.write(new String[]{"鏃堕棿鑼冨洿锛�"+fansSubmit.getCreateTime().format(DateTimeFormatter.ofPattern("yyyyMMdd"))});
writer.write(new String[]{});
+ writer.write(new String[]{});
+ List<Map<String, String>> head = JSON.parseObject(JSON.toJSONString(map.get("head")), List.class);
+ String[] str = new String[head.size()];
+ for (int i = 0; i < head.size(); i++) {
+ str[i] = head.get(i).get("value");
+ }
+ writer.write(str);
+ JSON.parseObject(JSON.toJSONString(map.get("body")), IPage.class).getRecords().forEach(a->{
+ Map<String, Object> map1 = JSON.parseObject(JSON.toJSONString(a), Map.class);
+ for (int i = 0; i < head.size(); i++) {
+ str[i] = map1.get(head.get(i).get("label"))==null?null:map1.get(head.get(i).get("label"))+"";
+ }
+ writer.write(str);
+ });
+ writer.close();
+ return name;
+ }
+
+ @Override
+ public String downFinanceSubmitFile(IPage<FinanceSubmit> page, FinanceSubmit financeSubmit) {
+ Map<String, Object> map = financeSubmitService.selectFinanceSubmitList(page, financeSubmit);
+ String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
+ String name = "璐㈠姟涓婃姤" + time + ".csv";
+ String path = outPath + name;
+ CsvWriter writer = CsvUtil.getWriter(path, CharsetUtil.CHARSET_GBK);
+ writer.write(new String[]{"瀵煎嚭鏃堕棿锛�"+time});
+ writer.write(new String[]{});
+ writer.write(new String[]{"鏃堕棿鑼冨洿锛�"+financeSubmit.getCreateTime().format(DateTimeFormatter.ofPattern("yyyyMMdd"))});
writer.write(new String[]{});
writer.write(new String[]{});
List<Map<String, String>> head = JSON.parseObject(JSON.toJSONString(map.get("head")), List.class);
@@ -318,7 +361,10 @@
|| StringUtils.isEmpty(dataComparisonDto.getComparisonName())) {
map.put("body", new Page<DataComparisonDto>());
}else {
+ long startTime = System.currentTimeMillis();
IPage<DataComparisonDto> dataReportingPageDtoIPage = dataReportingMapper.selectDataComparisonDtoPageList(page, dataComparisonDto);
+ long endTime = System.currentTimeMillis();
+ System.out.println("鎵ц鏃堕棿锛�" + (endTime - startTime) + " 姣");
map.put("body", dataReportingPageDtoIPage);
}
return map;
diff --git a/data-server/src/main/java/com/yuanchu/mom/service/impl/FansSubmitServiceImpl.java b/data-server/src/main/java/com/yuanchu/mom/service/impl/FansSubmitServiceImpl.java
index 4865a3d..5177530 100644
--- a/data-server/src/main/java/com/yuanchu/mom/service/impl/FansSubmitServiceImpl.java
+++ b/data-server/src/main/java/com/yuanchu/mom/service/impl/FansSubmitServiceImpl.java
@@ -75,7 +75,7 @@
@Override
public int addFansSubmit(FansSubmit fansSubmit, String date) {
if(date == null) date = getYesterday();
- DataReporting dataReporting = dataReportingMapper.selectOne(Wrappers.<DataReporting>lambdaQuery().eq(DataReporting::getName, fansSubmit.getCustom()).eq(DataReporting::getProduct, fansSubmit.getProduct()).like(DataReporting::getCreateTime, date));
+ DataReporting dataReporting = dataReportingMapper.selectOne(Wrappers.<DataReporting>lambdaQuery().eq(DataReporting::getName, fansSubmit.getCustom()).eq(DataReporting::getProduct, fansSubmit.getProduct()).eq(DataReporting::getCreateUser, getLook.selectPowerByMethodAndUserId("selectRegistrantCountDtoPageList").get("userId")).like(DataReporting::getCreateTime, date));
if(ObjectUtil.isEmpty(dataReporting)){
throw new ErrorException(date+" 瀹㈡埛锛�"+fansSubmit.getCustom()+" 骞舵湭涓婃姤杩� "+fansSubmit.getProduct()+" 椤圭洰");
}
diff --git a/data-server/src/main/java/com/yuanchu/mom/service/impl/FinanceSubmitServiceImpl.java b/data-server/src/main/java/com/yuanchu/mom/service/impl/FinanceSubmitServiceImpl.java
index 3c4bd95..612bef2 100644
--- a/data-server/src/main/java/com/yuanchu/mom/service/impl/FinanceSubmitServiceImpl.java
+++ b/data-server/src/main/java/com/yuanchu/mom/service/impl/FinanceSubmitServiceImpl.java
@@ -11,12 +11,14 @@
import com.yuanchu.mom.pojo.DataReporting;
import com.yuanchu.mom.pojo.FansSubmit;
import com.yuanchu.mom.pojo.FinanceSubmit;
+import com.yuanchu.mom.service.DataReportingService;
import com.yuanchu.mom.service.FinanceSubmitService;
import com.yuanchu.mom.mapper.FinanceSubmitMapper;
import com.yuanchu.mom.utils.QueryWrappers;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;
+import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
@@ -55,17 +57,27 @@
@Override
public int updateFinanceSubmit(FinanceSubmit financeSubmit) {
+ DataReporting reporting = dataReportingMapper.selectById(financeSubmit.getDataId());
+ reporting.setId(financeSubmit.getDataId());
+ reporting.setAgentRebate(financeSubmit.getEmployeeRebate());
+ try {
+ reporting.setDiscountedConsumption(reporting.getConsumption().divide(reporting.getAgentRebate(), 2, RoundingMode.HALF_UP));
+ }catch (Exception e){}
+ dataReportingMapper.updateById(reporting);
return financeSubmitMapper.updateById(financeSubmit);
}
@Override
public int addFinanceSubmit(FinanceSubmit financeSubmit, String date) {
if(date == null) date = getYesterday();
- DataReporting dataReporting = dataReportingMapper.selectOne(Wrappers.<DataReporting>lambdaQuery().eq(DataReporting::getAgent, financeSubmit.getCompany()).eq(DataReporting::getName, financeSubmit.getName()).like(DataReporting::getCreateTime, date));
+ DataReporting dataReporting = dataReportingMapper.selectOne(Wrappers.<DataReporting>lambdaQuery().eq(DataReporting::getAgent, financeSubmit.getCompany()).eq(DataReporting::getName, financeSubmit.getName()).eq(DataReporting::getCreateUser, getLook.selectPowerByMethodAndUserId("selectRegistrantCountDtoPageList").get("userId")).like(DataReporting::getCreateTime, date));
if(ObjectUtil.isEmpty(dataReporting)){
- throw new ErrorException(date+" 璐︽埛锛�"+financeSubmit.getName()+" 鐨勪唬鐞嗗晢锛�"+financeSubmit.getCompany()+" 骞舵湭涓婃姤杩�");
+ throw new ErrorException(date+" 璐︽埛锛�"+financeSubmit.getName()+" 浠ｇ悊鍟嗭細"+financeSubmit.getCompany()+" 骞舵湭涓婃姤杩�");
}
dataReporting.setAgentRebate(financeSubmit.getEmployeeRebate());
+ try {
+ dataReporting.setDiscountedConsumption(dataReporting.getConsumption().divide(dataReporting.getAgentRebate(), 2, RoundingMode.HALF_UP));
+ }catch (Exception e){}
dataReportingMapper.updateById(dataReporting);
financeSubmit.setDataId(dataReporting.getId());
return financeSubmitMapper.insert(financeSubmit);
diff --git a/data-server/src/main/resources/mapper/DataReportingMapper.xml b/data-server/src/main/resources/mapper/DataReportingMapper.xml
index 22ea781..d7854d0 100644
--- a/data-server/src/main/resources/mapper/DataReportingMapper.xml
+++ b/data-server/src/main/resources/mapper/DataReportingMapper.xml
@@ -5,37 +5,37 @@
<mapper namespace="com.yuanchu.mom.mapper.DataReportingMapper">
<resultMap id="BaseResultMap" type="com.yuanchu.mom.pojo.DataReporting">
- <id property="id" column="id" jdbcType="INTEGER"/>
- <result property="department" column="department" jdbcType="VARCHAR"/>
- <result property="registrant" column="registrant" jdbcType="VARCHAR"/>
- <result property="product" column="product" jdbcType="VARCHAR"/>
- <result property="channel" column="channel" jdbcType="VARCHAR"/>
- <result property="name" column="name" jdbcType="VARCHAR"/>
- <result property="showNum" column="show_num" jdbcType="INTEGER"/>
- <result property="click" column="click" jdbcType="INTEGER"/>
- <result property="accountConsumption" column="account_consumption" jdbcType="DECIMAL"/>
- <result property="rebateConsumption" column="rebate_consumption" jdbcType="DECIMAL"/>
- <result property="consumption" column="consumption" jdbcType="DECIMAL"/>
- <result property="discountedConsumption" column="discounted_consumption" jdbcType="DECIMAL"/>
- <result property="fansAdd" column="fans_add" jdbcType="INTEGER"/>
- <result property="actualCost" column="actual_cost" jdbcType="DECIMAL"/>
- <result property="internalCosts" column="internal_costs" jdbcType="DECIMAL"/>
- <result property="customerCosts" column="customer_costs" jdbcType="DECIMAL"/>
- <result property="profit" column="profit" jdbcType="DECIMAL"/>
- <result property="agentRebate" column="agent_rebate" jdbcType="DECIMAL"/>
- <result property="customerRebate" column="customer_rebate" jdbcType="DECIMAL"/>
- <result property="remark" column="remark" jdbcType="DECIMAL"/>
- <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
- <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
- <result property="createUser" column="create_user" jdbcType="VARCHAR"/>
- <result property="updateUser" column="update_user" jdbcType="VARCHAR"/>
+ <id property="id" column="id" jdbcType="INTEGER"/>
+ <result property="department" column="department" jdbcType="VARCHAR"/>
+ <result property="registrant" column="registrant" jdbcType="VARCHAR"/>
+ <result property="product" column="product" jdbcType="VARCHAR"/>
+ <result property="channel" column="channel" jdbcType="VARCHAR"/>
+ <result property="name" column="name" jdbcType="VARCHAR"/>
+ <result property="showNum" column="show_num" jdbcType="INTEGER"/>
+ <result property="click" column="click" jdbcType="INTEGER"/>
+ <result property="accountConsumption" column="account_consumption" jdbcType="DECIMAL"/>
+ <result property="rebateConsumption" column="rebate_consumption" jdbcType="DECIMAL"/>
+ <result property="consumption" column="consumption" jdbcType="DECIMAL"/>
+ <result property="discountedConsumption" column="discounted_consumption" jdbcType="DECIMAL"/>
+ <result property="fansAdd" column="fans_add" jdbcType="INTEGER"/>
+ <result property="actualCost" column="actual_cost" jdbcType="DECIMAL"/>
+ <result property="internalCosts" column="internal_costs" jdbcType="DECIMAL"/>
+ <result property="customerCosts" column="customer_costs" jdbcType="DECIMAL"/>
+ <result property="profit" column="profit" jdbcType="DECIMAL"/>
+ <result property="agentRebate" column="agent_rebate" jdbcType="DECIMAL"/>
+ <result property="customerRebate" column="customer_rebate" jdbcType="DECIMAL"/>
+ <result property="remark" column="remark" jdbcType="DECIMAL"/>
+ <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
+ <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
+ <result property="createUser" column="create_user" jdbcType="VARCHAR"/>
+ <result property="updateUser" column="update_user" jdbcType="VARCHAR"/>
</resultMap>
<select id="selectDataReportingDtoPageList" resultType="com.yuanchu.mom.dto.DataReportingPageDto">
select * from (
select dr.*,u1.name create_user_name,u2.name update_user_name from data_reporting dr
- left join user u1 on dr.create_user = u1.id
- left join user u2 on dr.update_user = u2.id
+ left join user u1 on dr.create_user = u1.id
+ left join user u2 on dr.update_user = u2.id
) a
<if test="ew.customSqlSegment != null and ew.customSqlSegment != ''">
${ew.customSqlSegment}
@@ -43,7 +43,8 @@
</select>
<select id="selectProductCountDtoPageList" resultType="com.yuanchu.mom.dto.ProductCountDto">
select * from (
- select update_time, create_time, product, sum(consumption) consumption, sum(fans_add) fans_add, sum(customer_costs) customer_costs, create_user
+ select update_time, create_time, product, sum(consumption) consumption, sum(fans_add) fans_add,
+ sum(customer_costs) customer_costs, create_user
from data_reporting
group by product,date_format(create_time, '%Y-%m-%d')
) a
@@ -53,7 +54,12 @@
</select>
<select id="selectRegistrantCountDtoPageList" resultType="com.yuanchu.mom.dto.RegistrantCountDto">
select * from (
- select id, department, registrant, product, name, sum(show_num) show_num, sum(click) click, sum(account_consumption)account_consumption, sum(rebate_consumption) rebate_consumption,(sum(rebate_consumption)+sum(remark)) rebate_consumption2, sum(discounted_consumption) discounted_consumption, sum(fans_add) fans_add, sum(actual_cost) actual_cost, sum(customer_costs) customer_costs, sum(profit) profit, sum(agent_rebate) agent_rebate, sum(customer_rebate) customer_rebate, sum(remark) remark, create_time, update_time, create_user
+ select id, department, registrant, product, name, sum(show_num) show_num, sum(click) click,
+ sum(account_consumption)account_consumption, sum(rebate_consumption)
+ rebate_consumption,(sum(rebate_consumption)+sum(remark)) rebate_consumption2, sum(discounted_consumption)
+ discounted_consumption, sum(fans_add) fans_add, sum(actual_cost) actual_cost, sum(customer_costs)
+ customer_costs, sum(profit) profit, sum(agent_rebate) agent_rebate, sum(customer_rebate) customer_rebate,
+ sum(remark) remark, create_time, update_time, create_user
from data_reporting
group by registrant,date_format(create_time, '%Y-%m-%d')
) a
@@ -62,66 +68,73 @@
</if>
</select>
<select id="getDataFor1" resultType="java.util.Map">
- select COALESCE(sum(account_consumption), 0) data1, COALESCE(sum(consumption), 0) data2, COALESCE(sum(customer_costs),0) data3, COALESCE(sum(actual_cost), 0) data4
+ select COALESCE(sum(account_consumption), 0) data1,
+ COALESCE(sum(consumption), 0) data2,
+ COALESCE(sum(customer_costs), 0) data3,
+ COALESCE(sum(actual_cost), 0) data4
from data_reporting
- where date(create_time) like CURDATE()
+ where date(create_time) like (CURDATE() - INTERVAL 1 DAY)
</select>
<select id="getDataFor2" resultType="java.util.Map">
- SELECT sum(account_consumption) account_consumptions, DATE_FORMAT(create_time, '%m-%d') `date`, DATE_FORMAT(create_time, '%y-%m-%d') time
+ SELECT sum(account_consumption) account_consumptions,
+ DATE_FORMAT(create_time, '%m-%d') `date`,
+ DATE_FORMAT(create_time, '%y-%m-%d') time
FROM data_reporting
group by DATE_FORMAT(create_time, '%y-%m-%d')
having DATE_SUB(CURDATE(), INTERVAL 7 DAY) < time
- and time <= CURDATE()
+ and time < CURDATE()
</select>
<select id="getDataFor3" resultType="java.util.Map">
- SELECT sum(consumption) consumption, DATE_FORMAT(create_time, '%m-%d') `date`, DATE_FORMAT(create_time, '%y-%m-%d') time
+ SELECT sum(consumption) consumption,
+ DATE_FORMAT(create_time, '%m-%d') `date`,
+ DATE_FORMAT(create_time, '%y-%m-%d') time
FROM data_reporting
group by DATE_FORMAT(create_time, '%y-%m-%d')
having DATE_SUB(CURDATE(), INTERVAL 7 DAY) < time
- and time <= CURDATE()
+ and time < CURDATE()
</select>
<select id="getDataFor4" resultType="java.util.Map">
select product, sum(customer_costs) customerCosts
from data_reporting
group by product
- order by customerCosts desc limit 7
+ order by customerCosts desc
+ limit 7
</select>
<select id="getDataFor5" resultType="java.util.Map">
select product, sum(actual_cost) actualCost
from data_reporting
group by product
- order by actualCost desc limit 7
+ order by actualCost desc
+ limit 7
</select>
<select id="selectUser" resultType="java.util.Map">
- select user.department, user.name, user.account from user
+ select user.department, user.name, user.account
+ from user
where id = #{userId}
</select>
<select id="selectDataComparisonDtoPageList" resultType="com.yuanchu.mom.dto.DataComparisonDto">
- select a.account account,
- a.name name,
+ select a.account account,
+ a.name name,
a.account_consumption consumption,
- a.create_time createTime,
- b.account comparisonAccount,
- b.name comparisonName,
- b.account_consumption comparisonConsumption,
- b.create_time comparisonCreateTime
- from (select user.account,
- user.name,
- dr1.account_consumption,
- dr1.create_time
- from data_reporting dr1
- join user on dr1.create_user = user.id
- where date_format(dr1.create_time, '%Y-%m-%d') = date_format(#{dataComparisonDto.createTime}, '%Y-%m-%d')
- and user.name like concat('%', #{dataComparisonDto.name}, '%')) a,
- (select user.account,
- user.name,
- dr2.account_consumption,
- dr2.create_time
- from data_reporting dr2
- join user on dr2.create_user = user.id
- where date_format(dr2.create_time, '%Y-%m-%d') = date_format(#{dataComparisonDto.createTime}, '%Y-%m-%d')
- and user.name like concat('%', #{dataComparisonDto.comparisonName}, '%')) b
- where a.account_consumption != b.account_consumption
+ b.account comparisonAccount,
+ b.name comparisonName,
+ b.account_consumption comparisonConsumption
+ from (select dr1.*, user.id uId, user.name uName, user.account
+ from data_reporting dr1
+ join user on dr1.create_user = user.id
+ where date_format(dr1.create_time, '%Y-%m-%d') =
+ date_format(#{dataComparisonDto.createTime}, '%Y-%m-%d')
+ and user.name = #{dataComparisonDto.name}) a
+ left join (select dr2.*, user.id uId, user.name uName, user.account
+ from data_reporting dr2
+ join user on dr2.create_user = user.id
+ where date_format(dr2.create_time, '%Y-%m-%d') =
+ date_format(#{dataComparisonDto.createTime}, '%Y-%m-%d')
+ and user.name = #{dataComparisonDto.comparisonName}) b
+ on a.name = b.name
+ and a.product = b.product
+ where (a.account_consumption != b.account_consumption
+ or b.account_consumption is null)
</select>
</mapper>
diff --git a/data-server/src/main/resources/mapper/FinanceSubmitMapper.xml b/data-server/src/main/resources/mapper/FinanceSubmitMapper.xml
index e811df5..4bb0808 100644
--- a/data-server/src/main/resources/mapper/FinanceSubmitMapper.xml
+++ b/data-server/src/main/resources/mapper/FinanceSubmitMapper.xml
@@ -4,20 +4,6 @@
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yuanchu.mom.mapper.FinanceSubmitMapper">
- <resultMap id="BaseResultMap" type="com.yuanchu.mom.pojo.FinanceSubmit">
- <id property="id" column="id" jdbcType="INTEGER"/>
- <result property="name" column="name" jdbcType="VARCHAR"/>
- <result property="company" column="company" jdbcType="VARCHAR"/>
- <result property="consumption" column="consumption" jdbcType="DECIMAL"/>
- <result property="recharge" column="recharge" jdbcType="DECIMAL"/>
- <result property="refund" column="refund" jdbcType="DECIMAL"/>
- <result property="agentRebate" column="agent_rebate" jdbcType="DECIMAL"/>
- <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
- <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
- <result property="createUser" column="create_user" jdbcType="INTEGER"/>
- <result property="updateUser" column="update_user" jdbcType="INTEGER"/>
- </resultMap>
-
<select id="selectFinanceSubmitPageList" resultType="com.yuanchu.mom.pojo.FinanceSubmit">
select * from (
select fs.*, u.name create_user_name from finance_submit fs, user u
--
Gitblit v1.9.3