<?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.bailuntec.mapper.DcAutoSalesForecastStageConfigMapper">

    <select id="selectMatchStage" resultType="com.bailuntec.domain.entity.DcAutoSalesForecastStageConfig">
        SELECT t7.*
        from dc_auto_turnover t1
                 LEFT JOIN dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                 LEFT JOIN dc_base_stock t8 on t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.warehouse_code
                 LEFT JOIN dc_base_warehouse t6 on t1.warehouse_code = t6.warehouse_code
                 LEFT JOIN dc_auto_sales_forecast_sku_config t3 on t1.bailun_sku = t3.bailun_sku
                 LEFT JOIN dc_auto_sales_forecast_product_code_config t4 on t2.product_code = t4.product_code
                 LEFT JOIN dc_auto_sales_forecast_config t5 on (t3.config_id = t5.id or t5.id not in (select config_id
                                                                                                      from dc_auto_sales_forecast_sku_config
                                                                                                      where bailun_sku = t1.bailun_sku))
            and (t4.config_id = t5.id or t5.id not in (select config_id
                                                       from dc_auto_sales_forecast_product_code_config
                                                       where product_code = t1.product_code))
            and (t6.warehouse_code = t5.warehouse_code or length(trim(t5.warehouse_code)) = 0)
            and (t6.hq_type = t5.warehouse_type or length(trim(t5.warehouse_type)) = 0)
            and (t2.product_type = t5.category or length(trim(t5.category)) = 0)
            and (t6.area_id = t5.warehouse_area_id or length(trim(t5.warehouse_area_id)) = 0)
            and t5.is_delete = 0
                 LEFT JOIN dc_auto_sales_forecast_stage_config t7 on t7.config_id = t5.id
            and (IF(t5.screen_type = 1,
                    ((t8.first_inbound_date &lt;= t7.duration_upper_limit or t7.duration_upper_limit = 0)
                        and (t8.first_inbound_date &gt;= t7.duration_lower_limit or t7.duration_lower_limit = 0)
                        and (t7.duration_upper_limit != 0 or t7.duration_lower_limit != 0))
                        or t8.first_inbound_date is null
                        or length(trim(t8.first_inbound_date)) = 0,
                    ((t1.daily_weighted_sales &lt;= t7.sales_upper_limit or t7.sales_upper_limit = 0)
                        and (t1.daily_weighted_sales &gt;= t7.sales_lower_limit or t7.sales_lower_limit = 0)
                        and (t7.sales_upper_limit != 0 or t7.sales_lower_limit != 0))
                        or t1.daily_weighted_sales is null
                        or length(trim(t1.daily_weighted_sales)) = 0))
        where t1.bailun_sku = #{bailunSku}
          and t1.warehouse_code = #{warehouseCode}
          and t5.id is not null
        ORDER BY t5.`level` desc
        limit 1
    </select>

    <select id="selectTestSyncAutoTurnoverList" resultType="com.bailuntec.domain.entity.DcAutoTurnover">
        SELECT *
        from (SELECT t1.*
              from dc_auto_turnover t1
                       LEFT JOIN dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                       LEFT JOIN dc_base_stock t8
                                 on t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.warehouse_code
                       LEFT JOIN dc_base_warehouse t6 on t1.warehouse_code = t6.warehouse_code
                       LEFT JOIN dc_auto_sales_forecast_sku_config t3 on t1.bailun_sku = t3.bailun_sku
                       LEFT JOIN dc_auto_sales_forecast_product_code_config t4 on t2.product_code = t4.product_code

                       LEFT JOIN dc_auto_sales_forecast_config t5 on
                      (t3.config_id = t5.id or t5.id not in (select config_id
                                                             from dc_auto_sales_forecast_sku_config
                                                             where bailun_sku = t1.bailun_sku))
                      and (t4.config_id = t5.id or t5.id not in (select config_id
                                                                 from dc_auto_sales_forecast_product_code_config
                                                                 where product_code = t1.product_code))
                      and (t6.warehouse_code = t5.warehouse_code or length(trim(t5.warehouse_code)) = 0)
                      and (t6.hq_type = t5.warehouse_type or length(trim(t5.warehouse_type)) = 0)
                      and (t2.product_type = t5.category or length(trim(t5.category)) = 0)
                      and (t6.area_id = t5.warehouse_area_id or length(trim(t5.warehouse_area_id)) = 0)
                      and t5.is_delete = 0
                      and t5.id = 4
                       LEFT JOIN dc_auto_sales_forecast_stage_config t7 on t7.config_id = t5.id
                  and (IF(t5.screen_type = 1,
                          t8.first_inbound_date &lt;= t7.duration_upper_limit or
                          t8.first_inbound_date &gt;= t7.duration_lower_limit or t8.first_inbound_date is null or
                          length(trim(t8.first_inbound_date)) = 0,
                          t1.daily_weighted_sales &lt;= t7.sales_upper_limit or
                          t1.daily_weighted_sales &gt;= t7.sales_lower_limit or t1.daily_weighted_sales is null or
                          length(trim(t1.daily_weighted_sales)) = 0))
              where t5.id is not null
#                 and t1.warehouse_code = 'GB4PXBL'
#                 and t1.bailun_sku = '945104601'
              ORDER BY t5.`level` desc) aaa
        GROUP BY bailun_sku, warehouse_code;
    </select>
</mapper>
