<?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 (find_in_set(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
            <if test="firstOrderDays!=null ">
                and (t5.first_order_days_min = 0 or t5.first_order_days_min &lt;= #{firstOrderDays})
                and (t5.first_order_days_max = 0 or t5.first_order_days_max &gt;= #{firstOrderDays})
            </if>
            and (false
            <if test="avgOrderCount30!=null ">
                or (t5.orders_avg_sampling_days = 0 or (t5.orders_avg_sampling_days = 30
                and (t5.orders_avg_min = 0 or t5.orders_avg_min &lt;= #{avgOrderCount30})
                and (t5.orders_avg_max = 0 or t5.orders_avg_max &gt;= #{avgOrderCount30})))
            </if>
            <if test="avgOrderCount14!=null ">
                or (t5.orders_avg_sampling_days = 0 or (t5.orders_avg_sampling_days = 14
                and (t5.orders_avg_min = 0 or t5.orders_avg_min &lt;= #{avgOrderCount14})
                and (t5.orders_avg_max = 0 or t5.orders_avg_max &gt;= #{avgOrderCount14})))
            </if>
            <if test="avgOrderCount7!=null ">
                or (t5.orders_avg_sampling_days = 0 or (t5.orders_avg_sampling_days = 7
                and (t5.orders_avg_min = 0 or t5.orders_avg_min &lt;= #{avgOrderCount7})
                and (t5.orders_avg_max = 0 or t5.orders_avg_max &gt;= #{avgOrderCount7})))
            </if>
            )
                 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
              ORDER BY t5.`level` desc) aaa
        GROUP BY bailun_sku, warehouse_code;
    </select>

    <select id="firsOrder" resultType="com.bailuntec.domain.entity.DcBaseOmsSku">
        select create_time, bailun_order_id
        from dc_base_oms_sku
        where bailun_sku = #{bailunSku}
          and warehouse_code = #{warehouseCode}
        order by create_time asc
        limit 1;
    </select>

    <select id="avgOrderCount" resultType="java.math.BigDecimal">
        select count(DISTINCT bailun_order_id) / #{days}
        from dc_base_oms_sku_30
        where bailun_sku = #{bailunSku}
          and warehouse_code = #{warehouseCode}
          and create_time >= date_sub(now(), interval #{days} + 1 day)
    </select>

</mapper>
