修订版3.0

李林

2024-01-09 d0c3258ed4871f2b9f03357bfd39b6caeff156b0

修订版3.0

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

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

 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 {

    /**
    * 主键

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

}

 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;

 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()+" 项目");
        }

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

 data-server/src/main/resources/mapper/DataReportingMapper.xml

@@ -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,38 +68,48 @@
        </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) &lt; time
        and time &lt;= CURDATE()
           and time &lt; 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) &lt; time
        and time &lt;= CURDATE()
           and time &lt; 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>

@@ -101,27 +117,24 @@
        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
               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 like concat('%', #{dataComparisonDto.name}, '%')) a,
             (select user.account,
                     user.name,
                     dr2.account_consumption,
                     dr2.create_time
                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 like concat('%', #{dataComparisonDto.comparisonName}, '%')) b
        where a.account_consumption != b.account_consumption
                    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>

 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