利用JAVA解决SQL不能实现的非同名字段行转列问题

于 2023-02-03 14:25:47 发布
阅读量233 收藏
点赞数
文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_67396349/article/details/128865335
版权
该文描述了一个Java应用，通过定时任务处理销售计划数据，从xy_stock_plan表中提取信息，转换并存储到目标表中，用于销售预测分析。应用使用了MyBatis进行数据库交互，将原始数据拆分成按月份的计划详情。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、准备工作

1.1 建表

CREATE TABLE `xy_stock_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `distributor_id` int(11) NOT NULL COMMENT '经销商ID',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户ID',
  `plan_code` varchar(255) DEFAULT NULL COMMENT '批次编号',
  `examine_state` tinyint(4) NOT NULL COMMENT '状态(0-草稿，1-审核中，2-通过，3-不通过)',
  `examine_start_time` datetime DEFAULT NULL COMMENT '提交审核时间',
  `examine_time` datetime DEFAULT NULL COMMENT '审批结束时间',
  `examine_user` varchar(255) DEFAULT NULL COMMENT '审批人',
  `examine_reason` varchar(500) DEFAULT NULL COMMENT '审批评价/原因',
  `distributor_name` varchar(255) DEFAULT NULL COMMENT '经销商名称',
  `product_code` varchar(255) DEFAULT NULL COMMENT '产品型号',
  `product_package` varchar(255) DEFAULT NULL COMMENT '封装',
  `unit_price_including_tax` decimal(20,6) DEFAULT NULL COMMENT '含税单价',
  `plan_month` datetime DEFAULT NULL COMMENT '月份',
  `first_half_month` int(11) DEFAULT NULL COMMENT '上半月数量',
  `latter_half_month` int(11) DEFAULT NULL COMMENT '下半月数量',
  `jan_count` int(11) DEFAULT NULL COMMENT '一月数量',
  `feb_count` int(11) DEFAULT NULL COMMENT '二月数量',
  `mar_count` int(11) DEFAULT NULL COMMENT '三月数量',
  `apr_count` int(11) DEFAULT NULL COMMENT '四月数量',
  `may_count` int(11) DEFAULT NULL COMMENT '五月数量',
  `jun_count` int(11) DEFAULT NULL COMMENT '六月数量',
  `jul_count` int(11) DEFAULT NULL COMMENT '七月数量',
  `aug_count` int(11) DEFAULT NULL COMMENT '八月数量',
  `sept_count` int(11) DEFAULT NULL COMMENT '九月数量',
  `oct_count` int(11) DEFAULT NULL COMMENT '十月数量',
  `nov_count` int(11) DEFAULT NULL COMMENT '十一月数量',
  `dec_count` int(11) DEFAULT NULL COMMENT '十二月数量',
  `aggregate_demand` bigint(20) DEFAULT NULL COMMENT '总需求',
  `amount_including_tax` decimal(20,6) DEFAULT NULL COMMENT '含税金额',
  `amount_not_tax` decimal(20,6) DEFAULT NULL COMMENT '未税金额',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `del_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `distributor_id` (`distributor_id`),
  KEY `customer_id` (`customer_id`)
)

1.2 插入数据

INSERT INTO xysemi_test.xy_stock_plan
(id, distributor_id, customer_id, plan_code, examine_state, examine_start_time, examine_time, examine_user, examine_reason, distributor_name, product_code, product_package, unit_price_including_tax, plan_month, first_half_month, latter_half_month, jan_count, feb_count, mar_count, apr_count, may_count, jun_count, jul_count, aug_count, sept_count, oct_count, nov_count, dec_count, aggregate_demand, amount_including_tax, amount_not_tax, create_by, create_time, update_by, update_time, remark, del_flag)
VALUES(78, 68, 118, NULL, 1, '2019-02-21 17:48:59', NULL, NULL, NULL, '微科技有限公司', 'XB8886A', 'ESOP8', 0.420000, '2022-02-21 17:48:59', 55, 99, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 154, 57.240000, 50.650000, 'DT2-003', '2022-02-21 17:48:59', NULL, '2022-11-03 15:42:06', NULL, 0);

二、目标

目的：将上面的计划部分数据变成下面这张图的效果，然后合并上面两个结果集

三、java处理数据源，跑定时任务

SalesForecastTask


/**
 * 销售大数据表格-预测
 */
@Component
public class SalesForecastTask {

    private final SalesForecastService salesForecastService;

    public SalesForecastTask(SalesForecastService salesForecastService) {
        this.salesForecastService = salesForecastService;
    }

 /**
     *数据来源-计划部分--表 xy_plan_table_thirteen_two
     */
    @Scheduled(cron = "0 5 1 * * ?")
//    @Scheduled(cron = "*/5 * * * * ?")//插入数据时候测试用
    public void stockPlanFromTableThirteenTwo(){
        salesForecastService.stockPlanFromTableThirteenTwo();
    }
  /**
     * 目标表
     */
//    @Scheduled(cron = "*/5 * * * * ?")
    @Scheduled(cron = "0 13 1 * * ?")
    public void distributorSalesPlanByYearMonth() {
        salesForecastService.distributorSalesPlanByYearMonth();
    }
}

SalesForecastService

/**
 * 销售大数据表格-预测
 */
public interface SalesForecastService {

    /**
     * 13-2 数据源- 计划部分
     */
    void stockPlanFromTableThirteenTwo();

    /**
     * 目标表
     */
    void distributorSalesPlanByYearMonth();
}

SalesForecastServiceImpl

   
/**
 * 销售大数据表格-预测
 */
@Service
public class SalesForecastServiceImpl implements SalesForecastService {

    @Autowired
    private SalesForecastMapper salesForecastSMapper;
    /**
     *  数据源-计划部分
     */
    @Override
    public void stockPlanFromTableThirteenTwo() {
        List<stockPlanFromTableThirteenTwoPreBean> stockPlanFromTableThirteenTwoPres = salesForecastSMapper.stockPlanFromTableThirteenTwoPre();
        List<stockPlanFromTableThirteenTwoBean> stockPlanFromTableThirteenTwo = new ArrayList<>();
        for (stockPlanFromTableThirteenTwoPreBean plan : stockPlanFromTableThirteenTwoPres) {
            String planYear = plan.getPlanYear();
            String distributorId = plan.getDistributorId();
            String distributorName = plan.getDistributorName();
            String salesMan = plan.getSalesMan();
            String customerName = plan.getCustomerName();
            String productCode = plan.getProductCode();
            BigDecimal unitPriceIncludingTax = plan.getUnitPriceIncludingTax();
            BigDecimal unitPriceNotTax = plan.getUnitPriceNotTax();


            Long janPlanCount = plan.getJanPlanCount();
            Long febPlanCount = plan.getFebPlanCount();
            Long marPlanCount = plan.getMarPlanCount();
            Long aprPlanCount = plan.getAprPlanCount();
            Long mayPlanCount = plan.getMayPlanCount();
            Long junPlanCount = plan.getJunPlanCount();
            Long julPlanCount = plan.getJulPlanCount();
            Long augPlanCount = plan.getAugPlanCount();
            Long septPlanCount = plan.getSeptPlanCount();
            Long octPlanCount = plan.getOctPlanCount();
            Long novPlanCount = plan.getNovPlanCount();
            Long decPlanCount = plan.getDecPlanCount();

            BigDecimal janPlanMoney = plan.getJanPlanMoney();
            BigDecimal febPlanMoney = plan.getFebPlanMoney();
            BigDecimal marPlanMoney = plan.getMarPlanMoney();
            BigDecimal aprPlanMoney = plan.getAprPlanMoney();
            BigDecimal mayPlanMoney = plan.getMayPlanMoney();
            BigDecimal junPlanMoney = plan.getJunPlanMoney();
            BigDecimal julPlanMoney = plan.getJulPlanMoney();
            BigDecimal augPlanMoney = plan.getAugPlanMoney();
            BigDecimal septPlanMoney = plan.getSeptPlanMoney();
            BigDecimal octPlanMoney = plan.getOctPlanMoney();
            BigDecimal novPlanMoney = plan.getNovPlanMoney();
            BigDecimal decPlanMoney = plan.getDecPlanMoney();
            for (int i = 1; i < 13; i++) {
                stockPlanFromTableThirteenTwoBean bean = new stockPlanFromTableThirteenTwoBean();
                if (i == 1) {//1月
                    bean.setYearMonth(planYear + "-" + "01");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(janPlanCount));
                    bean.setPlanMoney(janPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 2) {//2月
                    bean.setYearMonth(planYear + "-" + "02");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(febPlanCount));
                    bean.setPlanMoney(febPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 3) {//3月
                    bean.setYearMonth(planYear + "-" + "03");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(marPlanCount));
                    bean.setPlanMoney(marPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 4) {//4月
                    bean.setYearMonth(planYear + "-" + "04");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(aprPlanCount));
                    bean.setPlanMoney(aprPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 5) {//5月
                    bean.setYearMonth(planYear + "-" + "05");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(mayPlanCount));
                    bean.setPlanMoney(mayPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 6) {//6月
                    bean.setYearMonth(planYear + "-" + "06");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(junPlanCount));
                    bean.setPlanMoney(junPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 7) {//7月
                    bean.setYearMonth(planYear + "-" + "07");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(julPlanCount));
                    bean.setPlanMoney(julPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 8) {//8月
                    bean.setYearMonth(planYear + "-" + "08");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(augPlanCount));
                    bean.setPlanMoney(augPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 9) {//9月
                    bean.setYearMonth(planYear + "-" + "09");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(septPlanCount));
                    bean.setPlanMoney(septPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 10) {//10月
                    bean.setYearMonth(planYear + "-" + "10");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(octPlanCount));
                    bean.setPlanMoney(octPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 11) {//11月
                    bean.setYearMonth(planYear + "-" + "11");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(octPlanCount));
                    bean.setPlanCount(Math.toIntExact(novPlanCount));
                    bean.setPlanMoney(novPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 12) {//12月
                    bean.setYearMonth(planYear + "-" + "12");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(decPlanCount));
                    bean.setPlanMoney(decPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
            }
        }
        if (stockPlanFromTableThirteenTwo.size() > 0) {
            salesForecastSMapper.truncateDataByTableName("xy_plan_table_thirteen_two");
            int records = salesForecastSMapper.insertstockPlanFromTableThirteenTwo(stockPlanFromTableThirteenTwo);
            System.out.println("13-2 数据源计划部分记录数" + records);
        }
    }
}

涉及的类：

stockPlanFromTableThirteenTwoPreBean

/**
 * 数据源计划部分
 * @author xhx
 * @date 2023-01-17 15:25
 */
@Data
public class stockPlanFromTableThirteenTwoPreBean {

    /**
     * 计划年
     */
    private String planYear;

    /**
     * 经销商id
     */
    private String distributorId;

    /**
     * 经销商名称
     */
    private String distributorName;

    /**
     * 业务员名称
     */
    private String salesMan;

    /**
     * 客户名称
     */
    private String customerName;

    /**
     * 出货型号-即产品型号
     */
    private String productCode;

    /**
     * 含税单价-计划
     */
    private BigDecimal unitPriceIncludingTax;

    /**
     * 不含税单价-计划
     */
    private BigDecimal unitPriceNotTax;



    /**
     * 一到十二个月计划出货数量
     */
    private Long     janPlanCount    ;
    private Long     febPlanCount    ;
    private Long     marPlanCount    ;
    private Long     aprPlanCount    ;
    private Long     mayPlanCount    ;
    private Long     junPlanCount    ;
    private Long     julPlanCount    ;
    private Long     augPlanCount    ;
    private Long     septPlanCount   ;
    private Long     octPlanCount    ;
    private Long     novPlanCount    ;
    private Long     decPlanCount    ;

    /**
     * 一到十二个月即贷款金额（含税）
     */
    private BigDecimal janPlanMoney ;
    private BigDecimal  febPlanMoney ;
    private BigDecimal  marPlanMoney ;
    private BigDecimal  aprPlanMoney ;
    private BigDecimal  mayPlanMoney ;
    private BigDecimal  junPlanMoney ;
    private BigDecimal  julPlanMoney ;
    private BigDecimal  augPlanMoney ;
    private BigDecimal  septPlanMoney;
    private BigDecimal  octPlanMoney ;
    private BigDecimal  novPlanMoney ;
    private BigDecimal  decPlanMoney ;
}

stockPlanFromTableThirteenTwoBean

package com.xysemi.distributor.domain.bean;

import lombok.Data;

import java.math.BigDecimal;

/**
 *
 *  数据源（计划和实际部分）
 * @author xhx
 * @date 2023-01-17 15:27
 */
@Data
public class stockPlanFromTableThirteenTwoBean {

    /**
     * id
     */
    private String id;

    /**
     * 计划年月
     */
    private String yearMonth;

    /**
     * 经销商Id
     */
    private String distributorId;

    /**
     * 经销商名称
     */
    private String distributorName;

    /**
     * 业务员名称
     */
    private String salesMan;

    /**
     * 客户名称
     */
    private String customerName;

    /**
     * 出货型号-即产品型号
     */
    private String productCode;

    /**
     * 含税单价-计划
     */
    private BigDecimal unitPriceIncludingTax;

    /**
     * 不含税单价-计划
     */
    private BigDecimal unitPriceNotTax;

    /**
     * 计划数量-即表13-2的数量字段
     */
    private Integer planCount;


    /**
     * 贷款金额（含税）
     */
    private BigDecimal planMoney;

}

SalesForecastMapper

/**
 * 销售大数据表格-预测
 */
public interface SalesForecastMapper {
    
    /**
     * 数据源-计划
     * @return
     */
    List<stockPlanFromTableThirteenTwoPreBean> stockPlanFromTableThirteenTwoPre();

 /**
     *  数据源-计划  插入数据
     * @return
     */
    int insertstockPlanFromTableThirteenTwo(@Param("list") List<stockPlanFromTableThirteenTwoBean> stockPlanFromTableThirteenTwoBeanList);

    /**
     * 根据表名清空表数据
     */
    void truncateDataByTableName(@Param("name") String tName);

 /**
     * 从数据库获取目标表的数据
     *
     * @return 返回目标表的数据
     */
    List<DistributorSalesPlanByYearMonthBean> distributorSalesPlanByYearMonth();

    /**
     * 把目标表的数据插入到数据库的表
     *
     * @return 返回目标表的数据distributorSalesPlanByYearMonthBeanList
     */
    int insertDistributorSalesPlanByYearMonthBeans(@Param("list") List<DistributorSalesPlanByYearMonthBean> distributorSalesPlanByYearMonthBeans);

}

SalesForecastMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xysemi.distributor.mapper.SalesForecastMapper">
 <!--  数据源-计划部分 映射关系-->
 <resultMap type="StockPlanFromTableThirteenTwoPreBean" id="stockPlanFromTableThirteenTwoPreMapping">
        <result property="planYear" column="plan_year"/>
        <result property="distributorId" column="distributor_id"/>
        <result property="distributorName" column="distributor_name"/>
        <result property="salesMan" column="salesman"/>
        <result property="customerName" column="customer_name"/>
        <result property="productCode" column="product_code"/>
        <result property="unitPriceIncludingTax" column="unit_price_including_tax"/>
        <result property="unitPriceNotTax" column="unit_price_not_tax"/>
        <result property="janPlanMoney" column="jan_plan_money"/>
        <result property="janPlanCount" column="jan_plan_count"/>
        <result property="febPlanMoney" column="feb_plan_money"/>
        <result property="febPlanCount" column="feb_plan_count"/>
        <result property="marPlanMoney" column="mar_plan_money"/>
        <result property="marPlanCount" column="mar_plan_count"/>
        <result property="aprPlanMoney" column="apr_plan_money"/>
        <result property="aprPlanCount" column="apr_plan_count"/>
        <result property="mayPlanMoney" column="may_plan_money"/>
        <result property="mayPlanCount" column="may_plan_count"/>
        <result property="junPlanMoney" column="jun_plan_money"/>
        <result property="junPlanCount" column="jun_plan_count"/>
        <result property="julPlanMoney" column="jul_plan_money"/>
        <result property="julPlanCount" column="jul_plan_count"/>
        <result property="augPlanMoney" column="aug_plan_money"/>
        <result property="augPlanCount" column="aug_plan_count"/>
        <result property="septPlanMoney" column="sept_plan_money"/>
        <result property="septPlanCount" column="sept_plan_count"/>
        <result property="octPlanMoney" column="oct_plan_money"/>
        <result property="octPlanCount" column="oct_plan_count"/>
        <result property="novPlanMoney" column="nov_plan_money"/>
        <result property="novPlanCount" column="nov_plan_count"/>
        <result property="decPlanMoney" column="dec_plan_money"/>
        <result property="decPlanCount" column="dec_plan_count"/>
    </resultMap>
    <sql id="stockPlanFromTableThirteenTwoPreQuerySql">
        <!-- 数据源 计划部分-->
        select
        DATE_FORMAT(plan_month,'%Y')  plan_year
        ,xd.distributor_id
        ,xd.enterprise_name distributor_name
        ,su.nick_name salesman
        ,xc.customer_name
        ,xsp.product_code
        ,round(xsp.unit_price_including_tax,2) unit_price_including_tax
        ,round(xsp.unit_price_including_tax / 1.13,2) unit_price_not_tax
        ,sum(xsp.jan_count) jan_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.jan_count)),2) jan_plan_money
        ,sum(xsp.feb_count) feb_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.feb_count)),2) feb_plan_money
        ,sum(xsp.mar_count) mar_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.mar_count)),2) mar_plan_money
        ,sum(xsp.apr_count) apr_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.apr_count)),2) apr_plan_money
        ,sum(xsp.may_count) may_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.may_count)),2) may_plan_money
        ,sum(xsp.jun_count) jun_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.jun_count)),2) jun_plan_money
        ,sum(xsp.jul_count) jul_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.jul_count)),2) jul_plan_money
        ,sum(xsp.aug_count) aug_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.aug_count)),2) aug_plan_money
        ,sum(xsp.sept_count) sept_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.sept_count)),2) sept_plan_money
        ,sum(xsp.oct_count) oct_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.oct_count)),2) oct_plan_money
        ,sum(xsp.nov_count) nov_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.nov_count)),2) nov_plan_money
        ,sum(xsp.dec_count) dec_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.dec_count)),2) dec_plan_money
        FROM
        xy_stock_plan xsp
        left join xy_distributor xd
        on xsp.distributor_id = xd.distributor_id
        LEFT JOIN sys_user su
        on xd.salesman_id = su.user_id
        LEFT JOIN xy_customers xc
        on xsp.customer_id = xc.id
        WHERE xsp.del_flag = 0
        AND xc.customer_name IS NOT NULL
        AND xsp.jan_count IS NOT NULL
        AND product_code IS NOT NULL
        AND xsp.examine_state = 2
        group by xsp.distributor_id,xsp.product_code,DATE_FORMAT(xsp.plan_month,'%Y')
    </sql>
<resultMap type="DistributorSalesPlanByYearMonthBean" id="distributorSalesPlanByYearMonthBeanResult">
        <result property="yearMonth" column="year_month"/>
        <result property="distributorId" column="distributor_id"/>
        <result property="enterpriseName" column="enterprise_name"/>
        <result property="salesMan" column="salesman"/>
        <result property="customerName" column="customer_name"/>
        <result property="productCode" column="product_code"/>
        <result property="quantity" column="quantity"/>
        <result property="unitPriceIncludingTax" column="unit_price_including_tax"/>
        <result property="unitPriceNotTax" column="unit_price_not_tax"/>
        <result property="amountIncludingTax" column="amount_including_tax"/>
        <result property="salesNotTax" column="sales_not_tax"/>
    </resultMap>
 <sql id="distributorSalesPlanByYearMonthSql">
        -- 13-2 third version
        select
        t1.year_month
        ,t1.distributor_id
        ,t1.distributor_name enterprise_name
        ,t1.salesman
        ,t1.customer_name
        ,t1.product_code
        ,IFNULL(t1.unit_price_including_tax,0) unit_price_including_tax
        ,IFNULL(t1.unit_price_not_tax,0) unit_price_not_tax
        ,IFNULL(t1.plan_count,0) quantity
        ,IFNULL(t1.plan_money,0) amount_including_tax
        ,IFNULL(t2.total_price_not_tax_sum,0)  sales_not_tax
        FROM xy_plan_table_thirteen_two t1
        left join
        (
        SELECT
        DATE_FORMAT(t2.inbound_date,'%Y-%m') inbound_date ,
        t2.distributor_name ,
        t1.product_model ,
        -- sum(t1.inbound_quantity) inbound_quantity_sum ,
        sum(t1.total_price_not_tax) total_price_not_tax_sum
        from xy_erp_inbound_info t1
        left join xy_erp_inbound t2 on t1.inbound_id = t2.id
        where t2.inbound_date is not null
        AND t1.del_flag = 0
        and t2.del_flag = 0
        and t2.inbound_type = 1
        group by
        DATE_FORMAT(t2.inbound_date,'%Y-%m') ,
        t2.distributor_name ,
        t1.product_model
        order by DATE_FORMAT(t2.inbound_date,'%Y-%m') DESC
        )t2
        ON t1.year_month = t2.inbound_date AND t1.distributor_name = t2.distributor_name AND t1.product_code = t2.product_model
        ORDER BY t1.year_month
</sql>
<select id="stockPlanFromTableThirteenTwoPre" resultMap="stockPlanFromTableThirteenTwoPreMapping">
        <include refid="stockPlanFromTableThirteenTwoPreQuerySql"/>
    </select>
 <select id="distributorSalesPlanByYearMonth" resultMap="distributorSalesPlanByYearMonthBeanResult">
        <include refid="distributorSalesPlanByYearMonthSql"/>
    </select>
 <delete id="truncateDataByTableName" parameterType="String">
        truncate ${name}
    </delete>
 <insert id="insertstockPlanFromTableThirteenTwo" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into  xy_plan_table_thirteen_two
        (
        `year_month`
        ,distributor_id
        ,distributor_name
        ,salesman
        ,customer_name
        ,product_code
        ,unit_price_including_tax
        ,unit_price_not_tax
        ,plan_count
        ,plan_money
        )
        values
        <foreach collection="list" item="bean" index="index" separator=",">
            (
            #{bean.yearMonth},
            #{bean.distributorId},
            #{bean.distributorName},
            #{bean.salesMan},
            #{bean.customerName},
            #{bean.productCode},
            #{bean.unitPriceIncludingTax},
            #{bean.unitPriceNotTax},
            #{bean.planCount},
            #{bean.planMoney}
            )
        </foreach>
    </insert>
 <insert id="insertDistributorSalesPlanByYearMonthBeans" parameterType="java.util.List" useGeneratedKeys="true"
            keyProperty="id">
        insert into stat_distributor_sales_plan
        (
        `year_month`
        ,distributor_id
        ,enterprise_name
        ,salesman
        ,customer_name
        ,product_code
        ,quantity
        ,unit_price_including_tax
        ,unit_price_not_tax
        ,amount_including_tax
        ,sales_not_tax
        )
        values
        <foreach collection="list" item="bean" index="index" separator=",">
            (
            #{bean.yearMonth},
            #{bean.distributorId},
            #{bean.enterpriseName},
            #{bean.salesMan},
            #{bean.customerName},
            #{bean.productCode},
            #{bean.quantity},
            #{bean.unitPriceIncludingTax},
            #{bean.unitPriceNotTax},
            #{bean.amountIncludingTax},
            #{bean.salesNotTax}
            )
        </foreach>
</mapper>

m0_67396349
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值