using AutoTurnOver.Models;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using AutoTurnOver.Models.ApiDto;
using MySql.Data.MySqlClient;
using System.Text;
using AutoTurnOver.Utility;
using AutoTurnOver.Models.Report;

namespace AutoTurnOver.DB
{
    public class daily : connectionHelper
    {
        /// <summary>
        /// 查询采购在途数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static IEnumerable<dc_purchase_daily_dto> List(string sku, string warehouse_code)
        {
            var sql = @" 
SELECT
	tb1.bailun_sku AS bailun_sku,
	tb1.warehouse_code AS warehouse_code,
	tb1.purchase_id AS purchase_id,
     tb1.estimated_arrival_time AS estimated_arrival_time,
	-- tb1.has_transfer AS has_transfer,
	sum( ( tb1.count - IFNULL( tb2.count, 0 ) ) ) AS dailyQuantity ,
		(case when t_w.hq_type='fba仓' then t_w.bailun_account else t_w.warehouse_name end)  as warehouse_name
FROM
	(
SELECT
	bailun_sku,
	warehouse_code,
	parent_id,
	purchase_id,
	estimated_arrival_time,
	has_transfer,
	sum( count ) AS count 
FROM
	dc_base_purchase 
WHERE
	buy_status IN ( 0, 1, 2, 3 ) 
	AND has_delete = 0 
	AND bailun_sku = @bailun_sku
	AND warehouse_code = @warehouse_code
GROUP BY
	bailun_sku,
	parent_id 
	) tb1
	LEFT JOIN ( SELECT bailun_sku, warehouse_code, parent_id, sum( count ) AS count FROM dc_base_purchase_inbound GROUP BY bailun_sku, parent_id ) tb2 ON tb1.bailun_sku = tb2.bailun_sku 
	AND tb1.parent_id = tb2.parent_id 
left join dc_base_warehouse as t_w on tb1.warehouse_code = t_w.warehouse_code
GROUP BY
	tb1.bailun_sku,
	tb1.warehouse_code,
	tb1.purchase_id
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_purchase_daily_dto>(sql, parameters);
        }

        // <summary>
        /// 查询调拨在途数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static IEnumerable<dc_transfer_daily_dto> TransferList(string sku, string warehouse_code)
        {
            var sql = @" 
SELECT
 tb11.bailun_sku bailun_sku,
 tb11.warehouse_code warehouse_code,
 tb11.transfer_order_id transfer_order_id,
 tb5.estimated_arrival_time estimated_arrival_time,
 tb11.create_time create_time,
 (
  tb11.count - IFNULL(tb4.count, 0)
 ) AS count
FROM
 (
  SELECT
   tb1.bailun_sku,
   tb1.warehouse_code,
   tb1.transfer_order_id,
   tb1.create_time,
   sum(tb1.count) count
  FROM
   dc_base_transfer_bale tb1
  LEFT JOIN dc_base_transfer_complete tb2 ON tb1.transfer_order_id = tb2.transfer_order_id
  WHERE
   tb2.bailun_sku IS NULL
  AND tb1.bailun_sku = @bailun_sku
  AND tb1.warehouse_code = @warehouse_code
  AND tb1.deleted = 0
  GROUP BY
   bailun_sku,
   warehouse_code,
   transfer_order_id
 ) tb11
LEFT JOIN (
 SELECT
  tb1.bailun_sku,
  tb1.warehouse_code,
  tb1.transfer_order_id,
  tb1.estimated_arrival_time,
  tb1.create_time,
  sum(tb1.count) count
 FROM
  dc_base_transfer_verify tb1
 LEFT JOIN dc_base_transfer_complete tb2 ON tb1.transfer_order_id = tb2.transfer_order_id
 WHERE
  tb2.bailun_sku IS NULL
 AND tb1.bailun_sku = @bailun_sku
 AND tb1.warehouse_code = @warehouse_code
 AND tb1.deleted = 0
 GROUP BY
  bailun_sku,
  warehouse_code,
  transfer_order_id
) tb5 ON tb5.bailun_sku = tb11.bailun_sku
AND tb5.warehouse_code = tb11.warehouse_code
AND tb5.transfer_order_id = tb11.transfer_order_id
LEFT JOIN (
 SELECT
  bailun_sku,
  warehouse_code,
  transfer_order_id,
  sum(count) count
 FROM
  (
   SELECT
    t1.bailun_sku,
    t1.warehouse_code,
    t2.count,
    t1.transfer_order_id
   FROM
    (
     SELECT
      max(create_time) AS create_time,
      bailun_sku,
      warehouse_code,
      transfer_order_id
     FROM
      dc_base_transfer_inbound
     WHERE
      deleted = 0
     AND bailun_sku = @bailun_sku
     AND warehouse_code = @warehouse_code
     GROUP BY
      bailun_sku,
      warehouse_code,
      transfer_order_id
    ) t1
   INNER JOIN dc_base_transfer_inbound t2 ON t1.bailun_sku = t2.bailun_sku
   AND t1.warehouse_code = t2.warehouse_code
   AND t1.transfer_order_id = t2.transfer_order_id
   AND t1.create_time = t2.create_time
   LEFT JOIN dc_base_transfer_complete tb2 ON t1.transfer_order_id = tb2.transfer_order_id
   WHERE
    tb2.bailun_sku IS NULL
  ) tb3
 GROUP BY
  bailun_sku,
  warehouse_code,
  transfer_order_id
) tb4 ON tb5.bailun_sku = tb4.bailun_sku
AND tb5.warehouse_code = tb4.warehouse_code
AND tb5.transfer_order_id = tb4.transfer_order_id
WHERE
 (
  tb11.count - IFNULL(tb4.count, 0)
 ) > 0
UNION ALL
 SELECT
  bailun_sku,
  warehouse_code,
  transfer_plan_order_id,
  NULL,
  update_time,
  count
 FROM
  dc_base_transfer_plan
 WHERE
  STATUS = 1
 AND deleted = 0
 AND bailun_sku = @bailun_sku
 AND warehouse_code = @warehouse_code ;
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_transfer_daily_dto>(sql, parameters);
        }

        /// <summary>
        /// 指定日期到货的在途数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse_code"></param>
        /// <param name="index">dc_auto_inbound details 字段中的索引 索引</param>
        /// <returns></returns>
        public static List<dc_estimated_arrival_dto> EstimatedArrivalList(string sku, string warehouse_code, int index)
        {
            List<dc_estimated_arrival_dto> datas = new List<dc_estimated_arrival_dto>();
            index -= 23;
            var inbound_data = _connection.QueryFirstOrDefault<dc_auto_inbound>(" select * from dc_auto_inbound where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = sku, warehouse_code = warehouse_code });
            if (inbound_data == null) return new List<dc_estimated_arrival_dto>();
            else
            {
                // 解析 relation 字段
                var relation = inbound_data.relation;
                if (string.IsNullOrWhiteSpace(relation))
                {
                    datas = new List<dc_estimated_arrival_dto>();
                }
                else
                {
                    if (index >= 0)
                    {
                        var nos = relation.Replace("[", "").Replace("]", "").Split(',')[index];
                        datas = new List<dc_estimated_arrival_dto>();
                        if (nos != ("N/A"))
                        {
                            foreach (var no_str in nos.Split('*'))
                            {
                                if (!no_str.Contains("N/A"))
                                {
                                    var no_datas = no_str.Split('_');
                                    dc_estimated_arrival_dto data = new dc_estimated_arrival_dto();
                                    data.type = no_datas[0].Contains("p") ? 1 : no_datas[0].Contains("t") ? 2 : 0;
                                    data.no = no_datas[1];
                                    data.quantity = decimal.Parse(no_datas[2]);
                                    datas.Add(data);
                                }


                            }

                        }
                    }
                    else
                    {
                        // 查询周转表
                        var dc_auto_turnover_data = _connection.QuerySingleOrDefault<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                        {
                            bailun_sku = sku,
                            warehouse_code = warehouse_code
                        });
                        datas = new List<dc_estimated_arrival_dto>();
                        var nos_list = relation.Replace("[", "").Replace("]", "").Split(',');
                        int count = 0;
                        foreach (var nos in nos_list)
                        {
                            count++;
                            if (dc_auto_turnover_data.turnover_days < count)
                            {
                                break;
                            }
                            if (nos != ("N/A"))
                            {
                                foreach (var no_str in nos.Split('*'))
                                {
                                    try
                                    {
                                        if (!no_str.Contains("N/A"))
                                        {
                                            var no_datas = no_str.Split('_');
                                            dc_estimated_arrival_dto data = new dc_estimated_arrival_dto();
                                            data.type = no_datas[0].Contains("p") ? 1 : no_datas[0].Contains("t") ? 2 : 0;
                                            data.no = no_datas[1];
                                            data.quantity = decimal.Parse(no_datas[2]);
                                            datas.Add(data);
                                        }

                                    }
                                    catch (Exception ex)
                                    {

                                    }


                                }
                            }
                        }

                    }

                }
            }
            if (datas != null && datas.Count >= 1)
            {
                //匹配调拨单
                foreach (var item in datas)
                {
                    var temp_data = _connection.QueryFirstOrDefault<dc_estimated_arrival_dto>(@"select t1.transporttype,t2.storagecount,t2.transitcount,t2.deliverycount from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid =t2.dataid 
where t1.`code`=@code and t2.sku=@sku ", new { code = item.no, sku = sku });
                    if (temp_data != null)
                    {
                        item.storagecount = temp_data.storagecount;
                        item.transitcount = temp_data.transitcount;
                        item.deliverycount = temp_data.deliverycount;
                        item.transporttype = temp_data.transporttype;
                    }
                }
            }
            return datas;
        }

        /// <summary>
        /// 查询时实库存数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static List<dc_base_stock_dto> RealtimeList(dc_base_stock_search_dto search_data, List<bailun_category_new_dto> categoryModels, ref int total)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (search_data.isSum || search_data.is_warehouse_sum)
            {
                sql = @"
select
t1.warehouse_code,
dbw.area_name,
dbw.hq_type,
(dbw.warehouse_name)  as warehouse_name,
sum(t1.quantity_reservation) as 'quantity_reservation',
sum(t1.quantity_transport) as 'quantity_transport',
sum(t1.quantity_transfer) as 'quantity_transfer',
sum(t1.quantity_freeze) as 'quantity_freeze',
sum(t1.quantity_supplier) as 'quantity_supplier',
sum(t1.quantity_presell) as 'quantity_presell',
sum(t1.quantity_defective) as 'quantity_defective',
sum(t1.quantity_transit) as 'quantity_transit',
sum(t1.wait_check) as 'wait_check',
sum(t1.wait_putaway) as 'wait_putaway',
sum(t1.usable_stock) as 'usable_stock',
sum(t1.usable_sales) as 'usable_sales',
sum(t1.usable_sales_private) as 'usable_sales_private',
sum(t1.usable_sales_share) as 'usable_sales_share',
sum(t1.in_warehouse_share) as 'in_warehouse_share',
sum(t1.in_warehouse_private) as 'in_warehouse_private',
sum(t1.in_warehouse) as 'in_warehouse',
sum(t1.occupy_stock_share) as 'occupy_stock_share',
sum(t1.occupy_stock_private) as 'occupy_stock_private',
sum(t1.occupy_stock) as 'occupy_stock',
sum(t1.occupy_activity) as 'occupy_activity',
sum(t1.occupy_order_private) as 'occupy_order_private',
sum(t1.occupy_order_share) as 'occupy_order_share',
sum(t1.occupy_order) as 'occupy_order',

sum(t4.quantity_transfer_order) as 'quantity_transfer_order',
sum(t4.quantity_transfer_order * t3.unit_price) as 'quantity_transfer_order_amount',
sum(t4.quantity_transfer_temp_schedule) as 'quantity_transfer_temp_schedule',
sum(t4.quantity_transfer_temp_schedule * t3.unit_price) as 'quantity_transfer_temp_schedule_amount',
sum(t4.quantity_transfer_temporary_storage) as 'quantity_transfer_temporary_storage',
sum(t4.quantity_transfer_temporary_storage * t3.unit_price) as 'quantity_transfer_temporary_storage_amount',

sum(t1.usable_stock + t4.quantity_transfer  + t4.quantity_purchase ) as 'sum_actual_stock',
sum((t1.usable_stock + t4.quantity_transfer  + t4.quantity_purchase) *  t3.unit_price  )  as 'sum_actual_stock_amount',
sum(t1.usable_stock + ifnull(t4.quantity_transfer_order,0)  ) as 'sum_actual_transfer_stock',
sum((t1.usable_stock + ifnull(t4.quantity_transfer_order,0) ) *  t3.unit_price  )  as 'sum_actual_transfer_amount',

sum(t4.quantity_purchase * t3.unit_price) as 'realtime_amount_purchase',
sum(t4.quantity_transfer * t3.unit_price) as 'realtime_amount_transfer',
sum(t4.quantity_unshipped) as 'quantity_unshipped',
sum(t1.usable_stock + t1.occupy_stock) as 'sum_usable_stock',
sum((t1.usable_stock + t1.occupy_stock) * t3.unit_price) as 'sum_usable_stock_amount',

sum(t4.quantity_purchase) as 'purchase_daily_quantity',
sum(t4.quantity_purchase) as 'realtime_quantity_purchase',
sum(t4.quantity_transfer) as 'realtime_quantity_transfer',
sum(t4.quantity_out_stock) as 'quantity_out_stock',
sum((t1.usable_stock * ifnull(t3.unit_price,0))) as 'usable_stock_amount',
sum((ifnull(t4.quantity_purchase,0) + ifnull(t4.quantity_transfer,0)) * ifnull(t3.unit_price,0))  as 'on_the_way_stock_amount',
sum(ifnull(t9.sales,0))  as 'lastweek_sales',
sum(ifnull(t9.sales_amount,0))  as 'lastweek_sales_amount'
from 
dc_base_stock as t1
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_mid_transit as t4  on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_base_lastweek_data as t9 on t1.bailun_sku=t9.bailun_sku and t1.warehouse_code = t9.warehouse_code

";
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku ";
                }
                sql += " where 1=1 ";
            }
            else
            {
                sql = @" 
select
t1.*,
t3.develop_time as 'develop_time',
(dbw.warehouse_name)  as warehouse_name,
t3.sku_title_cn as 'base_sku_name',
t4.quantity_purchase as 'purchase_daily_quantity',
t3.buyer_name,t3.sku_title_en,t3.product_inner_code as 'sku_product_inner_code',
t3.category_simple_name,
t4.quantity_purchase as 'realtime_quantity_purchase',
t4.quantity_transfer as 'realtime_quantity_transfer',

t4.quantity_transfer_order as 'quantity_transfer_order',
t4.quantity_transfer_order * t3.unit_price as 'quantity_transfer_order_amount',
t4.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t4.quantity_transfer_temp_schedule * t3.unit_price as 'quantity_transfer_temp_schedule_amount',
t4.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage',
t4.quantity_transfer_temporary_storage * t3.unit_price as 'quantity_transfer_temporary_storage_amount',
t6.abroad_inbound_delivery as 'abroad_inbound_delivery',

t4.quantity_land_purchase as 'quantity_land_purchase',
t4.quantity_ocean_purchase as 'quantity_ocean_purchase',
t4.quantity_air_purchase as 'quantity_air_purchase',
t4.quantity_railway_purchase as 'quantity_railway_purchase',
t4.quantity_land_transfer as 'quantity_land_transfer',
t4.quantity_ocean_transfer as 'quantity_ocean_transfer',
t4.quantity_air_transfer as 'quantity_air_transfer',
t4.quantity_railway_transfer as 'quantity_railway_transfer',

t1.bailun_sku,t1.warehouse_code,t4.quantity_out_stock,t4.gmt_out_stock_modified,t3.weight,t3.unit_price,
(case when t5.id is null then '未侵权' else  '侵权' end) as 'has_tort',
( case when t7.`status`=0 or t7.`status` is null then 0 else 1 end ) as 'monitor_status',
( case when t7_1.`status`=0 or t7_1.`status` is null then 0 else 1 end ) as 'clearance_status',
t6.history_sevenday_sales,t6.history_fourteenday_sales,t6.history_thirtyday_sales,
t6.history_sevenday_sales_ebay,t6.history_fourteenday_sales_ebay,t6.history_thirtyday_sales_ebay,
t6.history_sevenday_sales_aliexpress,t6.history_fourteenday_sales_aliexpress,t6.history_thirtyday_sales_aliexpress,
t6.history_sevenday_sales_amazon,t6.history_fourteenday_sales_amazon,t6.history_thirtyday_sales_amazon,
t6.history_sevenday_sales_walmart,t6.history_fourteenday_sales_walmart,t6.history_thirtyday_sales_walmart,

dbw.area_name,
dbw.hq_type,
-- ifnull(t15.count,0) as 'not_trans_count',

t4.quantity_purchase * t3.unit_price as 'realtime_amount_purchase',
t4.quantity_transfer * t3.unit_price as 'realtime_amount_transfer',
t4.quantity_unshipped,
t1.usable_stock + t1.occupy_stock as 'sum_usable_stock',
(t1.usable_stock + t1.occupy_stock) * t3.unit_price as 'sum_usable_stock_amount',

(t1.usable_stock + t4.quantity_transfer  + t4.quantity_purchase ) as 'sum_actual_stock',
(t1.usable_stock + t4.quantity_transfer  + t4.quantity_purchase ) * t3.unit_price as 'sum_actual_stock_amount',
(t1.usable_stock + t4.quantity_transfer_order  ) as 'sum_actual_transfer_stock',
(t1.usable_stock + t4.quantity_transfer_order   ) * t3.unit_price as 'sum_actual_transfer_amount',

( (t6.turnover_sales+t6.quantity_safe_inventory) ) as 'turnover_days_quantity',
t6.turnover_days  as 'turnover_days',

t3.moq,
t3.character_names,
t3.tags,
(t1.usable_stock * ifnull(t3.unit_price,0)) as 'usable_stock_amount',
(ifnull(t4.quantity_purchase,0) + ifnull(t4.quantity_transfer,0)) * ifnull(t3.unit_price,0)  as 'on_the_way_stock_amount',
t3.product_size,
t3.product_type_desc,
t3.brand,
t8.oneday_sales,
t3.`status` as 'sku_status',
t3.length as 'pack_length',
t3.width as 'pack_width',
t3.height as 'pack_height',
LEAST(ifnull(t6.quantity_safe_inventory,0),t1.usable_stock) as 'real_quantity_safe_inventory',
t_storage.storage_quantity,
ifnull(t9.sales,0) as 'lastweek_sales',
ifnull(t9.sales_amount,0) as 'lastweek_sales_amount',
t10.sale_user_name as 'extend_sale_user_name',

t16.lately_transfer_expect_arrival_time,
t16.lately_transfer_order_no,
t16.lately_transfer_count,
t16.lately_transfer_trackingno,
t16.lately_transfer_transporttype,
t16.lately_transfer_logisticsname,
t6.gmt_modified as 'turnover_gmt_modified',

t3.product_line_name as 'product_line_name',

t_cost_first.cost_first as 'cost_first'

from 
dc_base_stock as t1
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
left join dc_mid_transit as t4  on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku
left join dc_auto_turnover as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join dc_auto_config_sku_warehouse as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_config_sku_warehouse_clearance as t7_1 on t1.bailun_sku_warehouse_code = t7_1.bailun_sku_warehouse_code 
left join dc_auto_sales as t8 on t1.bailun_sku=t8.bailun_sku and t1.warehouse_code = t8.warehouse_code
left join dc_base_lastweek_data as t9 on t1.bailun_sku=t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
left join dc_base_stock_extend as t10 on t1.bailun_sku=t10.bailun_sku and t1.warehouse_code = t10.warehouse_code
-- left join not_trans_view as t15 on t1.bailun_sku=t15.bailun_sku and t1.warehouse_code = t15.warehouse_code
left join dc_base_transfer_extend as t16 on t1.bailun_sku=t16.bailun_sku and t1.warehouse_code = t16.warehouse_code
left join dc_mid_cost_first as t_cost_first on t1.bailun_sku=t_cost_first.bailun_sku and t1.warehouse_code = t_cost_first.warehouse_code

";

                var storage_quantity_sql = @" left join (
select bailun_sku_warehouse_code,sum(quantity_stock) as 'storage_quantity' from dc_base_stock_record_effective where 1=1 ";

                if (search_data.start_date != null && search_data.end_date != null)
                {
                    storage_quantity_sql += " and stock_time>=@start_date and  stock_time<=@end_date ";
                }

                storage_quantity_sql += @"  GROUP BY bailun_sku_warehouse_code
) as t_storage on t1.bailun_sku_warehouse_code = t_storage.bailun_sku_warehouse_code ";
                sql += storage_quantity_sql;
                sql += " where 1=1 ";
            }


            var countSql = @" 
 select
count(1)
from 
dc_base_stock as t1
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
";
            if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
            {
                countSql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (search_data.has_tort != null)
            {
                countSql += " left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku ";
            }
            if (search_data.monitor_status != null)
            {
                countSql += " left join dc_auto_config_sku_warehouse as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code ";
            }

            countSql += "where 1=1 ";



            parameters.Add("time", DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));

            if (search_data.f_start_date != null)
            {
                sql += " and t1.`first_inbound_date`>=@f_start_date ";
                countSql += " and t1.`first_inbound_date`>=@f_start_date ";
                parameters.Add("f_start_date", search_data.f_start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (search_data.f_end_date != null)
            {
                sql += " and t1.`first_inbound_date`<=@f_end_date ";
                countSql += " and t1.`first_inbound_date`<=@f_end_date ";
                parameters.Add("f_end_date", search_data.f_end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (search_data.start_date != null && search_data.end_date != null)
            {
                sql += "  and t1.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                countSql += "  and t1.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                parameters.Add("start_date", search_data.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("end_date", search_data.end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (categoryModels != null && categoryModels.Count >= 1)
            {
                sql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
                countSql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
            }
            if (!string.IsNullOrWhiteSpace(search_data.buyer_name))
            {
                sql += " and t3.buyer_name = @buyer_name ";
                countSql += " and t3.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", search_data.buyer_name);
            }
            if (search_data.product_type > 0)
            {
                sql += " and t3.product_type = @product_type ";
                countSql += " and t3.product_type = @product_type ";
                parameters.Add("product_type", search_data.product_type);
            }
            if (search_data.monitor_status == 1)
            {
                sql += " and t7.`status`=1 ";
                countSql += " and t7.`status`=1 ";
            }
            if (search_data.monitor_status == 0)
            {
                sql += " and ( t7.`status`=0 or t7.`status` is null ) ";
                countSql += " and ( t7.`status`=0 or t7.`status` is null ) ";
            }
            if (search_data.has_tort != null)
            {
                if (search_data.has_tort == 0)
                {
                    sql += " and t5.id is null ";
                    countSql += " and t5.id is null ";
                }
                else if (search_data.has_tort == 1)
                {
                    sql += " and t5.id is not null ";
                    countSql += " and t5.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and dbw.hq_type = @hq_type ";
                countSql += " and dbw.hq_type = @hq_type ";

                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and dbw.area_id = @area_id ";
                countSql += " and dbw.area_id = @area_id ";

                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.searchType > 0 && !string.IsNullOrWhiteSpace(search_data.key_words))
            {
                switch (search_data.searchType)
                {
                    case 1:
                        sql += " and t1.bailun_sku in @bailun_skus ";
                        countSql += " and t1.bailun_sku in @bailun_skus ";
                        parameters.Add("bailun_skus", search_data.key_words.Split(','));
                        break;
                    case 2:
                        sql += " and t1.bailun_sku like @bailun_sku ";
                        countSql += " and t1.bailun_sku like @bailun_sku ";

                        parameters.Add("bailun_sku", $"%{search_data.key_words}%");
                        break;
                    case 3:
                        sql += " and t1.product_inner_code like @product_inner_code ";
                        countSql += " and t1.product_inner_code like @product_inner_code ";

                        parameters.Add("product_inner_code", $"%{search_data.key_words}%");
                        break;
                    case 4:
                        sql += " and t1.sku_title_cn like @sku_title_cn ";
                        countSql += " and t1.sku_title_cn like @sku_title_cn ";

                        parameters.Add("sku_title_cn", $"%{search_data.key_words}%");
                        break;
                    case 5:
                        sql += " and t1.product_inner_code = @product_inner_code ";
                        countSql += " and t1.product_inner_code = @product_inner_code ";
                        parameters.Add("product_inner_code", $"{search_data.key_words}");
                        break;
                }

            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.supplier_name))
            {
                sql += " and t1.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", search_data.supplier_name);
            }
            if (search_data.isSum)
            {
                total = 0;
            }
            else
            {
                if (search_data.is_warehouse_sum)
                {
                    countSql += " group by t1.warehouse_code ";
                    sql += " group by t1.warehouse_code ";

                    total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
                }
                else
                {
                    if (search_data.limit > 1000)
                    {
                        total = 0;
                    }
                    else
                    {
                        total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
                    }

                }



                if (!string.IsNullOrEmpty(search_data.sort) && !string.IsNullOrEmpty(search_data.order))
                {
                    sql += " order by " + search_data.sort;

                    if (!string.IsNullOrEmpty(search_data.order))
                    {
                        sql += " " + search_data.order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + search_data.offset + "," + search_data.limit;
            }
            var obj = _connection.Query<dc_base_stock_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            foreach (var item in obj)
            {
                item.history_sevenday_sales = item.history_sevenday_sales.Round1();
                item.history_fourteenday_sales = item.history_fourteenday_sales.Round1();
                item.history_thirtyday_sales = item.history_thirtyday_sales.Round1();
                item.history_sevenday_sales_ebay = item.history_sevenday_sales_ebay.Round1();
                item.history_fourteenday_sales_ebay = item.history_fourteenday_sales_ebay.Round1();
                item.history_thirtyday_sales_ebay = item.history_thirtyday_sales_ebay.Round1();
                item.history_sevenday_sales_aliexpress = item.history_sevenday_sales_aliexpress.Round1();
                item.history_fourteenday_sales_aliexpress = item.history_fourteenday_sales_aliexpress.Round1();
                item.history_thirtyday_sales_aliexpress = item.history_thirtyday_sales_aliexpress.Round1();
                item.history_sevenday_sales_amazon = item.history_sevenday_sales_amazon.Round1();
                item.history_fourteenday_sales_amazon = item.history_fourteenday_sales_amazon.Round1();
                item.history_thirtyday_sales_amazon = item.history_thirtyday_sales_amazon.Round1();
            }
            return obj;
        }
        public static List<dc_base_stock_fba_dto> RealtimeFbaList(dc_base_stock_search_dto search_data, List<bailun_category_new_dto> categoryModels, ref int total)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (search_data.isSum || search_data.is_warehouse_sum)
            {
                sql = @"
select 

sum(t1.amount_sales_usd_1) as 'amount_sales_usd_1',
sum(t1.bailun_sku_quantity_ordered_1) as 'bailun_sku_quantity_ordered_1',
sum(t1.amount_sales_usd_7) as 'amount_sales_usd_7',
sum(t1.bailun_sku_quantity_ordered_7) as 'bailun_sku_quantity_ordered_7',
sum(t1.amount_sales_usd_30) as 'amount_sales_usd_30',
sum(t1.bailun_sku_quantity_ordered_30) as 'bailun_sku_quantity_ordered_30',
sum(t1.amount_sales_usd_month_1) as 'amount_sales_usd_month_1',
sum(t1.bailun_sku_quantity_ordered_month_1) as 'bailun_sku_quantity_ordered_month_1',
sum(t1.amount_sales_usd_month_2) as 'amount_sales_usd_month_2',
sum(t1.bailun_sku_quantity_ordered_month_2) as 'bailun_sku_quantity_ordered_month_2',
sum(t6.quantity_purchase) as 'quantity_purchase',
sum(t6.quantity_transfer) as 'quantity_transfer',  
sum(t6.quantity_transfer_order) as 'quantity_transfer_order',
sum(t6.quantity_transfer_temp_schedule) as 'quantity_transfer_temp_schedule',
sum(t6.quantity_transfer_temporary_storage) as 'quantity_transfer_temporary_storage',
sum((t6.quantity_purchase+t6.quantity_transfer)) as 'sum_mid',
sum(ts1.usable_stock) as 'usable_stock_01',
sum(ts2.usable_stock) as 'usable_stock_02',
sum(t2.usable_stock) as 'usable_stock',
sum(((t6.quantity_purchase+t6.quantity_transfer)*t3.unit_price)) as 'sum_mid_amount',
sum(( t2.usable_stock*t3.unit_price)) as 'usable_stock_amount',
sum(((t6.quantity_purchase+t6.quantity_transfer+t2.usable_stock)*t3.unit_price)) as 'sum_amount',
sum(((t6.quantity_purchase+t6.quantity_transfer+t2.usable_stock))) as 'sum_stock'
from dc_config_fba_extend as t1
left JOIN dc_base_stock as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_base_warehouse as t4 on t1.warehouse_code = t4.warehouse_code
left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_mid_transit as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join (select ts1.bailun_sku,ts1.usable_stock  from dc_base_stock as ts1  where ts1.bailun_sku in (select bailun_sku from dc_config_fba_extend ) and ts1.warehouse_code='GZBLWH') as ts1 on t1.bailun_sku = ts1.bailun_sku
left join (select ts1.bailun_sku,ts1.usable_stock  from dc_base_stock as ts1  where ts1.bailun_sku in (select bailun_sku from dc_config_fba_extend ) and ts1.warehouse_code='GZBLZZG') as ts2 on t1.bailun_sku = ts2.bailun_sku

";
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t7  on t1.bailun_sku = t7.bailun_sku ";
                }
                sql += " where t1.bailun_sku_warehouse_code!='' ";
            }
            else
            {
                sql = @" 
select 
t1.bailun_account,t1.web_site,t1.asin,t1.bailun_sku,t1.product_type,t1.group_name,t1.sale_name,t3.sku_title_cn,t1.review_score,t1.review_count,
t1.warehouse_code,t4.warehouse_name,t4.area_name,
t1.amount_sales_usd_1,t1.bailun_sku_quantity_ordered_1,t1.amount_sales_usd_7,t1.bailun_sku_quantity_ordered_7,t1.amount_sales_usd_30,t1.bailun_sku_quantity_ordered_30,
t1.amount_sales_usd_month_1,t1.bailun_sku_quantity_ordered_month_1,t1.amount_sales_usd_month_2,t1.bailun_sku_quantity_ordered_month_2,
( case when t5.`status`=0 or t5.`status` is null then 0 else 1 end ) as 'monitor_status',
t3.buyer_name,t3.unit_price,t3.weight,
t6.quantity_purchase,t6.quantity_transfer,t6.quantity_transfer_order,t6.quantity_transfer_temp_schedule,t6.quantity_transfer_temporary_storage,(t6.quantity_purchase+t6.quantity_transfer) as 'sum_mid',
ts1.usable_stock as 'usable_stock_01',ts2.usable_stock as 'usable_stock_02', t2.usable_stock,
((t6.quantity_purchase+t6.quantity_transfer)*t3.unit_price) as 'sum_mid_amount',( t2.usable_stock*t3.unit_price) as 'usable_stock_amount',
((ifnull(t6.quantity_purchase,0)+ifnull(t6.quantity_transfer,0)+t2.usable_stock)*t3.unit_price) as 'sum_amount',
((ifnull(t6.quantity_purchase,0)+ifnull(t6.quantity_transfer,0)+t2.usable_stock)) as 'sum_stock',

t6.quantity_land_purchase as 'quantity_land_purchase',
t6.quantity_ocean_purchase as 'quantity_ocean_purchase',
t6.quantity_air_purchase as 'quantity_air_purchase',
t6.quantity_railway_purchase as 'quantity_railway_purchase',
t6.quantity_land_transfer as 'quantity_land_transfer',
t6.quantity_ocean_transfer as 'quantity_ocean_transfer',
t6.quantity_air_transfer as 'quantity_air_transfer',
t6.quantity_railway_transfer as 'quantity_railway_transfer',

t16.lately_transfer_expect_arrival_time,
t16.lately_transfer_order_no,
t16.lately_transfer_count,
t16.lately_transfer_trackingno,
t16.lately_transfer_transporttype,
t16.lately_transfer_logisticsname

from dc_config_fba_extend as t1
left JOIN dc_base_stock as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_base_warehouse as t4 on t1.warehouse_code = t4.warehouse_code
left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_mid_transit as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join (select ts1.bailun_sku,ts1.usable_stock  from dc_base_stock as ts1  where ts1.bailun_sku in (select bailun_sku from dc_config_fba_extend ) and ts1.warehouse_code='GZBLWH') as ts1 on t1.bailun_sku = ts1.bailun_sku
left join (select ts1.bailun_sku,ts1.usable_stock  from dc_base_stock as ts1  where ts1.bailun_sku in (select bailun_sku from dc_config_fba_extend ) and ts1.warehouse_code='GZBLZZG') as ts2 on t1.bailun_sku = ts2.bailun_sku
left join dc_base_tort as t7  on t1.bailun_sku = t7.bailun_sku
left join dc_base_transfer_extend as t16 on t1.bailun_sku=t16.bailun_sku and t1.warehouse_code = t16.warehouse_code
where t1.bailun_sku_warehouse_code!=''
";

            }


            var countSql = @" 
 select
count(1)
from 
dc_config_fba_extend as t1
left join dc_base_warehouse as t4 on t1.warehouse_code = t4.warehouse_code
";
            if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
            {
                countSql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (search_data.has_tort != null)
            {
                countSql += " left join dc_base_tort as t7  on t1.bailun_sku = t7.bailun_sku ";
            }
            if (search_data.monitor_status != null)
            {
                countSql += " left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code ";
            }

            countSql += "where 1=1 ";



            parameters.Add("time", DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));

            if (search_data.f_start_date != null)
            {
                sql += " and t2.`first_inbound_date`>=@f_start_date ";
                countSql += " and t2.`first_inbound_date`>=@f_start_date ";
                parameters.Add("f_start_date", search_data.f_start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (search_data.f_end_date != null)
            {
                sql += " and t2.`first_inbound_date`<=@f_end_date ";
                countSql += " and t2.`first_inbound_date`<=@f_end_date ";
                parameters.Add("f_end_date", search_data.f_end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (search_data.start_date != null && search_data.end_date != null)
            {
                sql += "  and t1.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                countSql += "  and t1.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                parameters.Add("start_date", search_data.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("end_date", search_data.end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (categoryModels != null && categoryModels.Count >= 1)
            {
                sql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
                countSql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
            }
            if (!string.IsNullOrWhiteSpace(search_data.buyer_name))
            {
                sql += " and t3.buyer_name = @buyer_name ";
                countSql += " and t3.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", search_data.buyer_name);
            }
            if (search_data.product_type > 0)
            {
                sql += " and t3.product_type = @product_type ";
                countSql += " and t3.product_type = @product_type ";
                parameters.Add("product_type", search_data.product_type);
            }
            if (search_data.monitor_status == 1)
            {
                sql += " and t5.`status`=1 ";
                countSql += " and t5.`status`=1 ";
            }
            if (search_data.monitor_status == 0)
            {
                sql += " and ( t5.`status`=0 or t5.`status` is null ) ";
                countSql += " and ( t5.`status`=0 or t5.`status` is null ) ";
            }
            if (search_data.has_tort != null)
            {
                if (search_data.has_tort == 0)
                {
                    sql += " and t7.id is null ";
                    countSql += " and t7.id is null ";
                }
                else if (search_data.has_tort == 1)
                {
                    sql += " and t7.id is not null ";
                    countSql += " and t7.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and t4.hq_type = @hq_type ";
                countSql += " and t4.hq_type = @hq_type ";

                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and t4.area_id = @area_id ";
                countSql += " and t4.area_id = @area_id ";

                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.searchType > 0 && !string.IsNullOrWhiteSpace(search_data.key_words))
            {
                switch (search_data.searchType)
                {
                    case 1:
                        sql += " and t1.bailun_sku in @bailun_skus ";
                        countSql += " and t1.bailun_sku in @bailun_skus ";
                        parameters.Add("bailun_skus", search_data.key_words.Split(','));
                        break;
                    case 2:
                        sql += " and t1.bailun_sku like @bailun_sku ";
                        countSql += " and t1.bailun_sku like @bailun_sku ";

                        parameters.Add("bailun_sku", $"%{search_data.key_words}%");
                        break;
                    case 3:
                        sql += " and t3.product_inner_code like @product_inner_code ";
                        countSql += " and t3.product_inner_code like @product_inner_code ";

                        parameters.Add("product_inner_code", $"%{search_data.key_words}%");
                        break;
                    case 4:
                        sql += " and t3.sku_title_cn like @sku_title_cn ";
                        countSql += " and t3.sku_title_cn like @sku_title_cn ";

                        parameters.Add("sku_title_cn", $"%{search_data.key_words}%");
                        break;
                    case 5:
                        sql += " and t3.product_inner_code = @product_inner_code ";
                        countSql += " and t3.product_inner_code = @product_inner_code ";
                        parameters.Add("product_inner_code", $"{search_data.key_words}");
                        break;
                    case 6:
                        sql += " and t1.asin = @asin ";
                        countSql += " and t1.asin = @asin ";
                        parameters.Add("asin", $"{search_data.key_words}");
                        break;
                }

            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.supplier_name))
            {
                sql += " and t3.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", search_data.supplier_name);
            }
            if (search_data.isSum)
            {
                total = 0;
            }
            else
            {
                if (search_data.is_warehouse_sum)
                {
                    countSql += " group by t1.warehouse_code ";
                    sql += " group by t1.warehouse_code ";

                    total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
                }
                else
                {
                    if (search_data.limit > 1000)
                    {
                        total = 0;
                    }
                    else
                    {
                        total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
                    }

                }



                if (!string.IsNullOrEmpty(search_data.sort) && !string.IsNullOrEmpty(search_data.order))
                {
                    sql += " order by " + search_data.sort;

                    if (!string.IsNullOrEmpty(search_data.order))
                    {
                        sql += " " + search_data.order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + search_data.offset + "," + search_data.limit;
            }
            var obj = _connection.Query<dc_base_stock_fba_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            return obj;
        }

        /// <summary>
        /// 缺货推送记录
        /// </summary>
        /// <returns></returns>
        public static List<dc_auto_shortage_push_route_dto> ShortagePushList(string platform, string bailun_sku, int offset, int limit, ref int total, DateTime? start_date, DateTime? end_date, string warehousecode, string warehousetype, int? warehousearea,int type = 0)
        {
            var sql = @"select t1.id, t1.platform, t1.bailun_sku,t3.warehouse_name, t1.warehouse_code, t1.push_date,t2.push_time from dc_auto_shortage_push as t1 
left join dc_return_goods_push as t2 on t1.id =t2.shortage_push_id
left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code
where 1=1 ";
           
            var countSql = " select count(1) from dc_auto_shortage_push as t1 left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code where 1=1 ";
            if (type == 1)
            {
                sql = @"select t2.id, t2.platform, t2.bailun_sku,t3.warehouse_name, t2.warehouse_code,t2.push_time 
from dc_return_goods_push as t2 
left join dc_base_warehouse as t3 on t2.warehouse_code = t3.warehouse_code
where 1=1 ";
                countSql = " select count(1) from dc_return_goods_push as t2 left join dc_base_warehouse as t3 on t2.warehouse_code = t3.warehouse_code where 1=1 ";
            }
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t3.warehouse_code=@warehouse_code ";
                countSql += " and t3.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and t3.hq_type = @hq_type ";
                countSql += " and t3.hq_type = @hq_type ";
                parameters.Add("hq_type", warehousetype);
            }
            if (warehousearea > 0)
            {
                sql += " and t3.area_id = @area_id ";
                countSql += " and t3.area_id = @area_id ";
                parameters.Add("area_id", warehousearea);
            }
            if (start_date != null)
            {
                if (type == 0)
                {
                    sql += " and t1.push_date>=@btime ";
                    countSql += " and t1.push_date>=@btime ";
                    parameters.Add("btime", start_date);
                }
                else
                {
                    sql += " and t2.push_time>=@btime ";
                    countSql += " and t2.push_time>=@btime ";
                    parameters.Add("btime", start_date);
                }
               
            }
            if (end_date != null)
            {
                sql += " and t1.push_date<=@etime ";
                countSql += " and t1.push_date<=@etime ";
                parameters.Add("etime", end_date);
            }
            if (!string.IsNullOrWhiteSpace(platform))
            {
                if (type == 0)
                {
                    sql += " and t1.platform=@platform ";
                    countSql += " and t1.platform=@platform ";
                    parameters.Add("platform", platform);

                }
                else
                {
                    sql += " and t2.platform=@platform ";
                    countSql += " and t2.platform=@platform ";
                    parameters.Add("platform", platform);
                }
                  
            }
            if (!string.IsNullOrWhiteSpace(bailun_sku))
            {
                if (type == 0)
                {
                    sql += " and t1.bailun_sku in @bailun_sku ";
                    countSql += " and t1.bailun_sku in @bailun_sku ";
                    parameters.Add("bailun_sku", bailun_sku.Split(','));

                }
                else
                {
                    sql += " and t2.bailun_sku in @bailun_sku ";
                    countSql += " and t2.bailun_sku in @bailun_sku ";
                    parameters.Add("bailun_sku", bailun_sku.Split(','));
                }
               
            }
            if (type == 1)
            {
                sql += " order by t2.id desc  ";
            }
            else
            {
                sql += " order by t1.id desc  ";
            }
          
            total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
            sql += " limit " + offset + "," + limit;
            var obj = _connection.Query<dc_auto_shortage_push_route_dto>(sql, parameters, buffered: false, commandTimeout: 0);
            return obj.AsList();
        }

        public static List<dc_task_download_dto> TaskDownloadList(string user_name, string task_name, int offset, int limit, ref int total, DateTime? start_date, DateTime? end_date)
        {
            var sql = @"select t1.* from dc_task_download as t1 
where 1=1 ";
            var countSql = " select count(1) from dc_task_download as t1 where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (start_date != null)
            {
                sql += " and t1.create_date>=@btime ";
                countSql += " and t1.create_date>=@btime ";
                parameters.Add("btime", start_date);
            }
            if (end_date != null)
            {
                sql += " and t1.create_date<=@etime ";
                countSql += " and t1.create_date<=@etime ";
                parameters.Add("etime", end_date);
            }
            if (!string.IsNullOrWhiteSpace(user_name))
            {
                sql += " and t1.create_user like @user_name ";
                countSql += " and t1.create_user like @user_name ";
                parameters.Add("user_name", $"%{user_name}%");
            }
            if (!string.IsNullOrWhiteSpace(task_name))
            {
                sql += " and t1.task_name like @task_name ";
                countSql += " and t1.task_name like @task_name ";
                parameters.Add("task_name", $"%{task_name}%");
            }

            sql += " order by t1.id desc  ";
            total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
            sql += " limit " + offset + "," + limit;
            var obj = _connection.Query<dc_task_download_dto>(sql, parameters, buffered: false, commandTimeout: 0);
            return obj.AsList();
        }

        public static string MapField(string sort)
        {
            sort = sort.Replace("-", "");
            switch (sort)
            {
                case "quantity_out_stock": return "t4.quantity_out_stock";
                default: return sort;
            }
        }

        /// <summary>
        /// 查询缺货订单
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static IEnumerable<dc_base_oms_sku_dto> OutOfStockOrderList(string sku, string warehouse_code)
        {
            var sql = @" 
  SELECT
    *
    FROM
    dc_base_oms_sku
    WHERE
        bailun_order_status not in ('Canceled')
        and bailun_interception_status in('None','Failed')
  and warehouse_code = @warehouse_code and bailun_sku=@bailun_sku
  and platform_type != 'FBA'
    AND paid_time >= '2019-03-01 12:00:00'
   and bailun_sku_quantity_ordered > bailun_sku_quantity_picked
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_base_oms_sku_dto>(sql, parameters);
        }

        public static IEnumerable<product_api_dto> GetSkus(string skus)
        {
            return _connection.Query<product_api_dto>($@"SELECT category_simple_id as 'CategorySimpleId',
category_simple_name as 'CategorySimpleName', 
buyer_name as 'Purchaser',
bailun_category_id as 'CategoryId',
bailun_category_name as 'CategoryName',
product_code as 'GoodsCode',
skums_id as 'id',
sku_image as 'Img',
sku_title_cn as 'Name',
bailun_sku as 'ProductCode',
brand as 'Brand'
FROM dc_base_sku
where bailun_sku in @skus", new { skus = skus.Split(',').ToList() });
        }

        public static List<product_type_dto> GetProductTypeList()
        {
            return _connection.Query<product_type_dto>(" select DISTINCT product_type as 'id',product_type_desc as 'name' from dc_base_sku  ").ToList();
        }

        /// <summary>
        /// 同步 pps的刊登状态
        /// </summary>
        public static void SynchroPPSSkuStatus()
        {
            int page = 1;
            int rows = 1000;
            var conn = _connection;
            conn.Execute(" Truncate table dc_base_listing_status_temp; ");
            while (true)
            {
                var now = DateTime.Now;
                List<dc_base_listing_status> add_datas = new List<dc_base_listing_status>();
                List<dc_base_listing_status> update_datas = new List<dc_base_listing_status>();

                var skus = conn.Query<string>($" select bailun_sku from dc_base_sku limit {(page - 1) * rows},{rows} ").ToList();
                page++;
                Console.WriteLine($" 刊登数据抓取 第 {page} 页 ");
                if (skus == null || skus.Count <= 0)
                {
                    break;
                }
                else
                {
                    var pps_skus = ApiUtility.GetPPSSkuStatus(new api_pps_sku_status_request_dto { sku = skus });
                    if (pps_skus != null && pps_skus.Count >= 1)
                    {
                        foreach (var item in pps_skus)
                        {
                            var item_data = new dc_base_listing_status
                            {
                                bailun_sku = item.sku,
                                platform = item.platformDesc,
                                localtion = item.shipAddress,
                                platform_sku = "",
                                status = item.statusDesc == "上架" ? 1 : 0,
                                sys_update_date = now,
                                update_date = now,
                                web_site = item.site ?? ""
                            };

                            add_datas.Add(item_data);
                            //item_data.id = conn.QueryFirstOrDefault<int?>(" select id from dc_base_listing_status where platform=@platform and web_site=@web_site and bailun_sku=@bailun_sku ", new
                            //{
                            //    platform = item_data.platform,
                            //    web_site = item_data.web_site,
                            //    bailun_sku = item_data.bailun_sku
                            //}) ?? 0;
                            //if (item_data.id > 0)
                            //{
                            //    update_datas.Add(item_data);
                            //}
                            //else
                            //{
                            //    add_datas.Add(item_data);
                            //}
                        }

                        if (add_datas != null && add_datas.Count >= 1)
                        {
                            BatchInsertDatas(conn, add_datas);
                        }

                        if (add_datas != null && add_datas.Count >= 1)
                        {
                            BatchUpdateDatas(conn, update_datas);
                        }
                    }

                }

            }

            conn.Execute(@" 
            alter table dc_base_listing_status rename dc_base_listing_statusTemp;
            alter table dc_base_listing_status_temp rename dc_base_listing_status;
            alter table dc_base_listing_statusTemp rename dc_base_listing_status_temp;
            truncate table dc_base_listing_status_temp; ");

            report.ResetDataHighFrequencyListting();

        }
        public static void BatchUpdateDatas(MyMySqlConnection conn, List<dc_base_listing_status> datas)
        {
            var sql = @"UPDATE dc_base_listing_status_temp SET bailun_sku=@bailun_sku,platform=@platform,`localtion`=@localtion,platform_sku=@platform_sku,`status`=@status,sys_update_date=@sys_update_date,update_date=@update_date,web_site=@web_site WHERE id = @id ;";
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时
            var res = conn.Execute(sql, datas);
            Console.WriteLine($" 更新 {datas.Count} 条数据，耗时 {watch.ElapsedMilliseconds} 毫秒 ");
        }

        public static void BatchInsertDatas(MyMySqlConnection conn, List<dc_base_listing_status> datas)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时
            StringBuilder sqlSb = new StringBuilder(@" insert into dc_base_listing_status_temp(`bailun_sku`,`platform`,`localtion`,`platform_sku`,`status`,`sys_update_date`,`update_date`,`web_site`)  value ");
            foreach (var item in datas)
            {
                sqlSb.Append($"('{item.bailun_sku}','{item.platform}','{item.localtion}','{item.platform_sku}',{item.status},'{item.sys_update_date.ToString("yyyy-MM-dd HH:mm:ss")}','{item.update_date.ToString("yyyy-MM-dd HH:mm:ss")}','{item.web_site}' ),");
            }
            var sql = sqlSb.ToString();
            sql = sql.Substring(0, sql.Length - 1);
            var res = conn.Execute(sql.ToString());
            Console.WriteLine($" 新增 {datas.Count} 条数据，耗时 {watch.ElapsedMilliseconds} 毫秒 ");
        }

        /// <summary>
        /// 刷新fba的过去销量
        /// </summary>
        public static void ResetFbaExtendSales(DateTime date)
        {
            var conn = _connection;
            // 时间点
            // 最近1天
            var btime1 = date.AddDays(-1).ToDayHome();
            var etime1 = date.AddDays(-1).ToDayEnd();
            var btime7 = date.AddDays(-8).ToDayHome();
            var etime7 = date.AddDays(-1).ToDayEnd();
            var btime30 = date.AddDays(-31).ToDayHome();
            var etime30 = date.AddDays(-1).ToDayEnd();

            var btime_month_1 = (DateTime.Parse(date.ToString("yyyy-MM-01 00:00:00"))).ToDayHome();
            var etime_month_1 = btime_month_1.LastDayOfMonth().ToDayEnd();
            var btime_month_2 = (DateTime.Parse(date.ToString("yyyy-MM-02 00:00:00"))).ToDayHome();
            var etime_month_2 = btime_month_2.LastDayOfMonth().ToDayEnd();
            int page = 0;
            int rows = 1000;

            while (true)
            {
                page++;
                var configs = conn.Query<dc_config_fba_extend>($@" select * from dc_config_fba_extend limit {(page - 1) * rows},{rows} ",commandTimeout:0).ToList();
                if (configs == null || configs.Count <= 0)
                {
                    break;
                }

                foreach (var item in configs)
                {
                    var tempData = conn.QueryFirstOrDefault<dc_config_fba_extend_temp_dto>($@"
select t1.bailun_sku,t1.warehouse_code,
sum(case when t1.create_time>=@btime1 and t1.create_time<=@etime1 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_1',
sum(case when t1.create_time>=@btime1 and t1.create_time<=@etime1 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_1',
sum(case when t1.create_time>=@btime7 and t1.create_time<=@etime7 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_7',
sum(case when t1.create_time>=@btime7 and t1.create_time<=@etime7 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_7',
sum(case when t1.create_time>=@btime30 and t1.create_time<=@etime30 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_30',
sum(case when t1.create_time>=@btime30 and t1.create_time<=@etime30 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_30'
from dc_base_oms_sku_30 as t1 where t1.create_time>=@btime30 and t1.create_time<=@etime30
and t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code
  and has_fba_s = 0
    and has_delete = 0
   and has_scalp = 0
  and has_buyer_remark = 0
 and has_platsku_remark = 0
  and has_innersale = 0
and bailun_order_status !='Canceled'
 and platform_order_type != '手工单'
", new { btime1, etime1, btime7, etime7, btime30, etime30, item.bailun_sku, item.warehouse_code },commandTimeout:0) ?? new dc_config_fba_extend_temp_dto();
                    conn.Execute($@" update dc_config_fba_extend set 
amount_sales_usd_1=@amount_sales_usd_1,
bailun_sku_quantity_ordered_1=@bailun_sku_quantity_ordered_1,
amount_sales_usd_7=@amount_sales_usd_7,
bailun_sku_quantity_ordered_7=@bailun_sku_quantity_ordered_7,
amount_sales_usd_30=@amount_sales_usd_30,
bailun_sku_quantity_ordered_30=@bailun_sku_quantity_ordered_30,
sales_reset_date = now()
where id=@id
", new
                    {
                        amount_sales_usd_1 = tempData.amount_sales_usd_1,
                        bailun_sku_quantity_ordered_1 = tempData.bailun_sku_quantity_ordered_1,
                        amount_sales_usd_7 = tempData.amount_sales_usd_7,
                        bailun_sku_quantity_ordered_7 = tempData.bailun_sku_quantity_ordered_7,
                        amount_sales_usd_30 = tempData.amount_sales_usd_30,
                        bailun_sku_quantity_ordered_30 = tempData.bailun_sku_quantity_ordered_30,
                        id = item.id
                    });

                    var tempMonthData = conn.QueryFirstOrDefault<dc_config_fba_extend_temp_dto>($@"
select t1.bailun_sku,t1.warehouse_code,
sum(case when t1.create_time>=@btime_month_1 and t1.create_time<=@etime_month_1 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_month_1',
sum(case when t1.create_time>=@btime_month_1 and t1.create_time<=@etime_month_1 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_month_1',
sum(case when t1.create_time>=@btime_month_2 and t1.create_time<=@etime_month_2 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_month_2',
sum(case when t1.create_time>=@btime_month_2 and t1.create_time<=@etime_month_2 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_month_2'
from dc_base_oms_sku as t1 where t1.create_time>=@btime_month_1 and t1.create_time<=@etime_month_2
and t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code
  and has_fba_s = 0
    and has_delete = 0
   and has_scalp = 0
  and has_buyer_remark = 0
 and has_platsku_remark = 0
  and has_innersale = 0
and bailun_order_status !='Canceled'
 and platform_order_type != '手工单'
", new { btime_month_1, etime_month_1, btime_month_2, etime_month_2,  item.bailun_sku, item.warehouse_code },commandTimeout:0) ?? new dc_config_fba_extend_temp_dto();
                    conn.Execute($@" update dc_config_fba_extend set 
amount_sales_usd_month_1=@amount_sales_usd_month_1,
bailun_sku_quantity_ordered_month_1=@bailun_sku_quantity_ordered_month_1,
amount_sales_usd_month_2=@amount_sales_usd_month_2,
bailun_sku_quantity_ordered_month_2=@bailun_sku_quantity_ordered_month_2,
sales_reset_date = now()
where id=@id
", new
                    {
                        amount_sales_usd_month_1 = tempMonthData.amount_sales_usd_month_1,
                        bailun_sku_quantity_ordered_month_1 = tempMonthData.bailun_sku_quantity_ordered_month_1,
                        amount_sales_usd_month_2 = tempMonthData.amount_sales_usd_month_2,
                        bailun_sku_quantity_ordered_month_2 = tempMonthData.bailun_sku_quantity_ordered_month_2,
                        amount_sales_usd_30 = tempMonthData.amount_sales_usd_30,
                        id = item.id
                    });
                }
            }



        }
        /// <summary>
        /// 刷新gmv
        /// </summary>
        public static void ResetGmvExtendSales(DateTime date)
        {
            var conn = _connection;
            // 时间点
            // 最近1天
            var btime1 = date.AddDays(-15).ToDayHome();
            var etime1 = date.AddDays(-1).ToDayEnd();
            var btime7 = date.AddDays(-8).ToDayHome();
            var etime7 = date.AddDays(-1).ToDayEnd();
            var btime30 = date.AddDays(-31).ToDayHome();
            var etime30 = date.AddDays(-1).ToDayEnd();

            var btime_month_1 = (DateTime.Parse(date.ToString("yyyy-MM-01 00:00:00"))).ToDayHome();
            var etime_month_1 = btime_month_1.LastDayOfMonth().ToDayEnd();
            var btime_month_2 = (DateTime.Parse(date.ToString("yyyy-MM-02 00:00:00"))).ToDayHome();
            var etime_month_2 = btime_month_2.LastDayOfMonth().ToDayEnd();
            int page = 0;
            int rows = 1000;

            while (true)
            {
                page++;
                var configs = conn.Query<dc_report_gmv_extend>($@" select t1.bailun_sku,t2.warehouse_code,CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code' from dc_base_stock as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where t2.hq_type='fba仓' limit {(page - 1) * rows},{rows} ",commandTimeout:0).ToList();
                if (configs == null || configs.Count <= 0)
                {
                    break;
                }

                foreach (var item in configs)
                {
                    var tempData = conn.QueryFirstOrDefault<dc_config_fba_extend_temp_dto>($@"
select t1.bailun_sku,t1.warehouse_code,
sum(case when t1.create_time>=@btime1 and t1.create_time<=@etime1 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_14',
sum(case when t1.create_time>=@btime1 and t1.create_time<=@etime1 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_14',
sum(case when t1.create_time>=@btime7 and t1.create_time<=@etime7 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_7',
sum(case when t1.create_time>=@btime7 and t1.create_time<=@etime7 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_7',
sum(case when t1.create_time>=@btime30 and t1.create_time<=@etime30 then t1.amount_sales * t1.order_to_usd_exchange_rate * t1.bailun_sku_quantity_ordered else 0 end) as 'amount_sales_usd_30',
sum(case when t1.create_time>=@btime30 and t1.create_time<=@etime30 then t1.bailun_sku_quantity_ordered else 0  end) as 'bailun_sku_quantity_ordered_30'
from dc_base_oms_sku_30 as t1 where t1.create_time>=@btime30 and t1.create_time<=@etime30
and t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code
  and has_fba_s = 0
    and has_delete = 0
   and has_scalp = 0
  and has_buyer_remark = 0
 and has_platsku_remark = 0
  and has_innersale = 0
and bailun_order_status !='Canceled'
 and platform_order_type != '手工单'
", new { btime1, etime1, btime7, etime7, btime30, etime30, item.bailun_sku, item.warehouse_code },commandTimeout:0) ?? new dc_config_fba_extend_temp_dto();
                    item.amount_sales_usd_14 = tempData.amount_sales_usd_14;
                    item.amount_sales_usd_7 = tempData.amount_sales_usd_7;
                    item.amount_sales_usd_30 = tempData.amount_sales_usd_30;
                    item.bailun_sku_quantity_ordered_14 = tempData.bailun_sku_quantity_ordered_14;
                    item.bailun_sku_quantity_ordered_7 = tempData.bailun_sku_quantity_ordered_7;
                    item.bailun_sku_quantity_ordered_30 = tempData.bailun_sku_quantity_ordered_30;
                    item.amount_sales_usd_30_weighting =(( tempData.amount_sales_usd_30 * 0.4M )+ (tempData.amount_sales_usd_14 * 0.3m)+ (tempData.amount_sales_usd_7 * 0.3M));
                    item.sales_reset_date = DateTime.Now;
                    item.id = conn.QueryFirstOrDefault<int?>("select id from dc_report_gmv_extend where bailun_sku_warehouse_code=@bailun_sku_warehouse_code",new { bailun_sku_warehouse_code  = item.bailun_sku_warehouse_code }) ??0;
                    if (item.id > 0)
                    {
                        conn.Update(item);
                    }
                    else
                    {
                        conn.Insert(item);
                    }
                }
            }



        }

        /// <summary>
        /// 查询GMV销量
        /// </summary>
        /// <param name="search_data"></param>
        /// <returns></returns>
        public static List<dc_report_gmv_extend> GetGmvList(dc_report_gmv_request_dto search_data)
        {
            var sql = @"  select * from dc_report_gmv_extend as t1 where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(search_data.bailun_sku))
            {
                sql += " and t1.bailun_sku in @bailun_sku ";
                parameters.Add("bailun_sku",search_data.bailun_sku.Split(',').ToList());
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehouse_code))
            {
                sql += " and t1.warehouse_code in @warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehouse_code.Split(',').ToList());
            }
            if (!string.IsNullOrWhiteSpace(search_data.bailun_sku_warehouse_code))
            {
                sql += " and t1.bailun_sku_warehouse_code in @bailun_sku_warehouse_code ";
                parameters.Add("bailun_sku_warehouse_code", search_data.bailun_sku_warehouse_code.Split(',').ToList());
            }
            return _connection.Query<dc_report_gmv_extend>(sql,parameters).ToList();
        }

        /// <summary>
        /// 刷新fba的昨日review
        /// </summary>
        public static void ResetFbaExtendReview(DateTime date)
        {
            // 时间点
            // 最近1天
            int page = 0;
            int rows = 100;

            while (true)
            {
                page++;
                var configs = _connection.Query<dc_config_fba_extend>($@" select * from dc_config_fba_extend limit {(page - 1) * rows},{rows} ").ToList();
                if (configs == null || configs.Count <= 0)
                {
                    break;
                }


                foreach (var item in configs.GroupBy(s => s.web_site))
                {
                    // 查询review 
                    var reviews = ApiUtility.GetReview(new api_pps_review_request_dto { Site = item.Key, Type = 1, AsinList = item.Select(s => s.asin).ToList() });
                    foreach (var asinItem in item.AsEnumerable())
                    {
                        var tempReview = reviews.FirstOrDefault(s => s.asin == asinItem.asin) ?? new api_pps_review_response_dto.result_dto.data_dto();
                        _connection.Execute($@" update dc_config_fba_extend set 
review_count=@review_count,
review_score=@review_score,
review_reset_date = now()
where id=@id
", new
                        {
                            review_count = tempReview.totalComments,
                            review_score = tempReview.score,
                            id = asinItem.id
                        });
                    }

                }
            }



        }

        /// <summary>
        /// 查询供应链在途
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static List<full_link_purchase_dto> FullLinkPurchaseList(dc_base_stock_search_dto search_data, List<bailun_category_new_dto> categoryModels, ref int total)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (search_data.isSum || search_data.is_warehouse_sum)
            {
                sql = @"
select 

sum(ifnull(t1.quantity_purchase,0)) as 'quantity_purchase',
sum(ifnull(t1.`quantity_arrival`,0)) as 'quantity_arrival',
sum(ifnull(t1.`quantity_inbound`,0)) as 'quantity_inbound',
sum(ifnull(t1.`quantity_not_inbound`,0)) as 'quantity_not_inbound'

from ( select 
t1.purchase_id,
t1.`count` as quantity_purchase,
                  t1.buy_status,
                  t1.estimated_arrival_time,
                  t1.price,
                  t3.quantity_inbound,
                  t3.quantity_arrival,
                  t3.last_signtime,
                  t1.bailun_sku,
                  t1.warehouse_code,
                  t1.transport_type,
                  sum(t2.`count`) purchase_inbound_count,
 t1.`count`  -  ifnull( sum(t2.`count`),0) as 'quantity_not_inbound'
from dc_base_purchase as t1
            left join dc_base_purchase_inbound t2 on t1.bailun_sku = t2.bailun_sku and t1.parent_id = t2.parent_id
            left join dc_base_purchase_details t3 on t1.bailun_sku = t3.bailun_sku and t1.purchase_id = t3.purchase_id
              where t1.buy_status in (0, 1, 2, 3, 7, 9)
                and t1.has_delete = 0
                and t1.`count` > 0
                and (t1.`count` > t2.`count` or t2.`count` is null)
              group by t1.id
) as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
";
                if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
                {
                    sql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
                }
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t9  on t1.bailun_sku = t9.bailun_sku ";
                }
                if (search_data.monitor_status != null)
                {
                    sql += " left join dc_auto_config_sku_warehouse as t10 on t1.bailun_sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
                }
                sql += " where  t1.quantity_not_inbound>0  ";
            }
            else
            {
                sql = @" 
select 
t1.id as 'sysId',
t1.create_time as 'create_time',
t1.bailun_sku,
t1.warehouse_code as 'warehouse_code',
t2.warehouse_name as 'warehouse_name',
t3.sku_title_cn as 'product_title',
t3.buyer_name as 'buyer_name',
t3.product_type_desc as 'product_type_desc',


t4.usable_stock as 'stock',
t5.quantity_transfer as 'quantity_transfer',
t5.quantity_transfer_order as 'quantity_transfer_order',
t5.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t5.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage',

t6.history_sevenday_sales as 'history_sevenday_sales',
t6.history_fourteenday_sales as 'history_fourteenday_sales',
t6.history_thirtyday_sales as 'history_thirtyday_sales',
t7.type as 'forecast_type',

t1.purchase_id as 'purchase_id',
t1.quantity_purchase as 'quantity_purchase',
t1.`buy_status` as 'purchase_status',
t1.`last_signtime` as 'last_signtime',
t1.`quantity_arrival` as 'quantity_arrival',
t1.`quantity_inbound` as 'quantity_inbound',
t1.`quantity_not_inbound` as 'quantity_not_inbound',
t1.`estimated_arrival_time` as 'purchase_arrivaldate',

t8.`status` as 'prod_status',
t8.distribution_quantity as 'prod_distribution_quantity',
( t8.order_quantity - t8.distribution_quantity) as 'prod_not_distribution_quantity',
t8.prod_quantity as 'prod_quantity'

from ( select 
t1.id,
t1.create_time,
t1.purchase_id,
t1.`count` as quantity_purchase,
                  t1.buy_status,
                  t1.estimated_arrival_time,
                  t1.price,
                  t3.quantity_inbound,
                  t3.quantity_arrival,
                  t3.last_signtime,
                  t1.bailun_sku,
                  t1.warehouse_code,
                  t1.transport_type,
                  sum(t2.`count`) purchase_inbound_count,
 t1.`count`  -  ifnull( sum(t2.`count`),0) as 'quantity_not_inbound'
from dc_base_purchase as t1
            left join dc_base_purchase_inbound t2 on t1.bailun_sku = t2.bailun_sku and t1.parent_id = t2.parent_id
            left join dc_base_purchase_details t3 on t1.bailun_sku = t3.bailun_sku and t1.purchase_id = t3.purchase_id
              where t1.buy_status in (0, 1, 2, 3, 7, 9)
                and t1.has_delete = 0
                and t1.`count` > 0
                and (t1.`count` > t2.`count` or t2.`count` is null)
              group by t1.id
) as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_base_stock as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_mid_transit as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_auto_turnover as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join dc_auto_forecast_fluctuation as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_base_prod_order  as t8 on t1.bailun_sku = t8.bailun_sku and t1.purchase_id = t8.purchase_no


";
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t9  on t1.bailun_sku = t9.bailun_sku ";
                }
                if (search_data.monitor_status != null)
                {
                    sql += " left join dc_auto_config_sku_warehouse as t10 on t1.bailun_sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
                }
                sql += " where  t1.quantity_not_inbound>0 ";
            }


            var countSql = @" 
 select
count(1)
from 
( select 
t1.purchase_id,
t1.`count` as quantity_purchase,
                  t1.buy_status,
                  t1.estimated_arrival_time,
                  t1.price,
                  t3.quantity_inbound,
                  t3.quantity_arrival,
                  t3.last_signtime,
                  t1.bailun_sku,
                  t1.warehouse_code,
                  t1.transport_type,
                  sum(t2.`count`) purchase_inbound_count,
 t1.`count`  -  ifnull( sum(t2.`count`),0) as 'quantity_not_inbound'
from dc_base_purchase as t1
            left join dc_base_purchase_inbound t2 on t1.bailun_sku = t2.bailun_sku and t1.parent_id = t2.parent_id
            left join dc_base_purchase_details t3 on t1.bailun_sku = t3.bailun_sku and t1.purchase_id = t3.purchase_id
              where t1.buy_status in (0, 1, 2, 3, 7, 9)
                and t1.has_delete = 0
                and t1.`count` > 0
                and (t1.`count` > t2.`count` or t2.`count` is null)
              group by t1.id
) as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
";
            if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
            {
                countSql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (search_data.has_tort != null)
            {
                countSql += " left join dc_base_tort as t9  on t1.bailun_sku = t9.bailun_sku ";
            }
            if (search_data.monitor_status != null)
            {
                countSql += " left join dc_auto_config_sku_warehouse as t10 on t1.bailun_sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
            }

            countSql += " where  t1.quantity_not_inbound>0 ";



            parameters.Add("time", DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));

            if (search_data.f_start_date != null)
            {
                sql += " and t4.`first_inbound_date`>=@f_start_date ";
                countSql += " and t4.`first_inbound_date`>=@f_start_date ";
                parameters.Add("f_start_date", search_data.f_start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (search_data.f_end_date != null)
            {
                sql += " and t4.`first_inbound_date`<=@f_end_date ";
                countSql += " and t4.`first_inbound_date`<=@f_end_date ";
                parameters.Add("f_end_date", search_data.f_end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (search_data.start_date != null && search_data.end_date != null)
            {
                sql += "  and t4.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                countSql += "  and t4.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                parameters.Add("start_date", search_data.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("end_date", search_data.end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (categoryModels != null && categoryModels.Count >= 1)
            {
                sql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
                countSql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
            }
            if (!string.IsNullOrWhiteSpace(search_data.buyer_name))
            {
                sql += " and t3.buyer_name = @buyer_name ";
                countSql += " and t3.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", search_data.buyer_name);
            }
            if (!string.IsNullOrWhiteSpace(search_data.order_no))
            {
                sql += " and t1.purchase_id = @order_no ";
                countSql += " and t1.purchase_id= @order_no ";
                parameters.Add("order_no", search_data.order_no);
            }
            if (search_data.product_type > 0)
            {
                sql += " and t3.product_type = @product_type ";
                countSql += " and t3.product_type = @product_type ";
                parameters.Add("product_type", search_data.product_type);
            }
            if (search_data.monitor_status == 1)
            {
                sql += " and t10.`status`=1 ";
                countSql += " and t10.`status`=1 ";
            }
            if (search_data.monitor_status == 0)
            {
                sql += " and ( t10.`status`=0 or t10.`status` is null ) ";
                countSql += " and ( t10.`status`=0 or t10.`status` is null ) ";
            }
            if (search_data.has_tort != null)
            {
                if (search_data.has_tort == 0)
                {
                    sql += " and t9.id is null ";
                    countSql += " and t9.id is null ";
                }
                else if (search_data.has_tort == 1)
                {
                    sql += " and t9.id is not null ";
                    countSql += " and t9.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and t2.hq_type = @hq_type ";
                countSql += " and t2.hq_type = @hq_type ";

                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and t2.area_id = @area_id ";
                countSql += " and t2.area_id = @area_id ";

                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.searchType > 0 && !string.IsNullOrWhiteSpace(search_data.key_words))
            {
                switch (search_data.searchType)
                {
                    case 1:
                        sql += " and t1.bailun_sku in @bailun_skus ";
                        countSql += " and t1.bailun_sku in @bailun_skus ";
                        parameters.Add("bailun_skus", search_data.key_words.Split(','));
                        break;
                    case 2:
                        sql += " and t1.bailun_sku like @bailun_sku ";
                        countSql += " and t1.bailun_sku like @bailun_sku ";

                        parameters.Add("bailun_sku", $"%{search_data.key_words}%");
                        break;
                    case 3:
                        sql += " and t3.product_inner_code like @product_inner_code ";
                        countSql += " and t3.product_inner_code like @product_inner_code ";

                        parameters.Add("product_inner_code", $"%{search_data.key_words}%");
                        break;
                    case 4:
                        sql += " and t3.sku_title_cn like @sku_title_cn ";
                        countSql += " and t3.sku_title_cn like @sku_title_cn ";

                        parameters.Add("sku_title_cn", $"%{search_data.key_words}%");
                        break;
                    case 5:
                        sql += " and t3.product_inner_code = @product_inner_code ";
                        countSql += " and t3.product_inner_code = @product_inner_code ";
                        parameters.Add("product_inner_code", $"{search_data.key_words}");
                        break;
                }

            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.supplier_name))
            {
                sql += " and t3.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", search_data.supplier_name);
            }
            if (search_data.isSum)
            {
                total = 0;
            }
            else
            {
                if (search_data.is_warehouse_sum)
                {
                    countSql += " group by t1.warehouse_code ";
                    sql += " group by t1.warehouse_code ";

                    total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
                }
                else
                {
                    if (search_data.limit > 1000)
                    {
                        total = 0;
                    }
                    else
                    {
                        total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
                    }

                }



                if (!string.IsNullOrEmpty(search_data.sort) && !string.IsNullOrEmpty(search_data.order))
                {
                    sql += " order by " + search_data.sort;

                    if (!string.IsNullOrEmpty(search_data.order))
                    {
                        sql += " " + search_data.order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + search_data.offset + "," + search_data.limit;
            }
            var obj = _connection.Query<full_link_purchase_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            foreach (var item in obj)
            {
                item.history_sevenday_sales = (item.history_sevenday_sales ?? 0).Round1();
                item.history_fourteenday_sales = (item.history_fourteenday_sales ?? 0).Round1();
                item.history_thirtyday_sales = (item.history_thirtyday_sales ?? 0).Round1();
            }
            return obj;
        }

        /// <summary>
        /// 查询供应链调拨在途
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static List<order_transfer_full_link_dto> FullLinkTransferList(dc_base_stock_search_dto search_data, List<bailun_category_new_dto> categoryModels, ref int total)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (search_data.isSum || search_data.is_warehouse_sum)
            {
                sql = @"
select 

sum(ifnull(t2.deliverycount - t2.storagecount,0)) as 'not_storage'


from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid =t2.dataid
left join dc_base_warehouse as t3 on t1.targetwareno =t3.warehouse_code
left join dc_base_sku as t4 on t2.sku = t4.bailun_sku
left join dc_base_stock as t5 on t2.sku = t5.bailun_sku and t1.targetwareno = t5.warehouse_code
left join dc_auto_turnover as t6 on  t2.sku = t6.bailun_sku and t1.targetwareno = t6.warehouse_code
left join dc_auto_forecast_fluctuation as t7 on t2.sku = t7.bailun_sku and t1.targetwareno = t7.warehouse_code

";
                if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
                {
                    sql += " left join dc_base_sku as t4 on t2.sku = t4.bailun_sku ";
                }
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t9  on t2.sku = t9.bailun_sku ";
                }
                if (search_data.monitor_status != null)
                {
                    sql += " left join dc_auto_config_sku_warehouse as t10 on t2.sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
                }

                sql += "  where t1.stateid !=5 and t2.deliverycount > t2.storagecount and t1.isdeleted=0 and t1.isexception=0  ";
            }
            else
            {
                sql = @" 
select 
t2.sku,
t1.deliverytime,
t1.targetwareno as 'warehouse_code',
t3.warehouse_name as 'warehouse_name',
t4.sku_title_cn as 'sku_name',
t5.usable_stock as 'stock',
(t2.deliverycount - t2.storagecount) as 'not_storage',

t6.history_sevenday_sales as 'history_sevenday_sales',
t6.history_fourteenday_sales as 'history_fourteenday_sales',
t6.history_thirtyday_sales as 'history_thirtyday_sales',

t4.buyer_name as 'buyer_name',
t4.product_type_desc as 'product_type_desc',
t7.type as 'forecast_type',

t1.`code` as 'order_no',
t1.transporttype,
t1.stateid,
t1.creationtime,
t1.purchasecode,
t8.sign_time,
t8.put_time,

t8.trackingno,
t8.transfer_no,
t8.box_id,
t8.node_date,
t8.node_name,
(case when t1.expectarrivaltime>'1991-01-01' then t1.expectarrivaltime else null end) as 'expectarrivaltime',
t8.surplus_node_days,
t8.expectarrivaltime as 'node_expectarrivaltime',
t8.logistics_code,
t8.logistics_name,
t8.sign_exe,
(t8.quantity - t8.storage_count) as 'box_not_storage'

from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid =t2.dataid
left join dc_base_warehouse as t3 on t1.targetwareno =t3.warehouse_code
left join dc_base_sku as t4 on t2.sku = t4.bailun_sku
left join dc_base_stock as t5 on t2.sku = t5.bailun_sku and t1.targetwareno = t5.warehouse_code
left join dc_auto_turnover as t6 on  t2.sku = t6.bailun_sku and t1.targetwareno = t6.warehouse_code
left join dc_auto_forecast_fluctuation as t7 on t2.sku = t7.bailun_sku and t1.targetwareno = t7.warehouse_code
left join dc_lms_order_transfer_current_node as t8 on t1.dataid = t8.data_id and t2.sku = t8.sku

";
                if (search_data.has_tort != null)
                {
                    sql += " left join dc_base_tort as t9  on t2.sku = t9.bailun_sku ";
                }
                if (search_data.monitor_status != null)
                {
                    sql += " left join dc_auto_config_sku_warehouse as t10 on t2.sku = t10.bailun_sku and t1.targetwareno = t10.warehouse_code ";
                }
                sql += " where   t1.stateid !=5 and t2.deliverycount > t2.storagecount and t1.isdeleted=0 and t1.isexception=0 ";
            }

            var countSql = @" 
 select
1
from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid =t2.dataid
left join dc_base_warehouse as t3 on t1.targetwareno =t3.warehouse_code
left join dc_base_stock as t5 on t2.sku = t5.bailun_sku and t1.targetwareno = t5.warehouse_code
left join dc_lms_order_transfer_current_node as t8 on t1.dataid = t8.data_id and t2.sku = t8.sku
";
            if ((categoryModels != null && categoryModels.Count >= 1) || !string.IsNullOrWhiteSpace(search_data.buyer_name) || search_data.product_type > 0)
            {
                countSql += " left join dc_base_sku as t4 on t2.sku = t4.bailun_sku ";
            }
            if (search_data.has_tort != null)
            {
                countSql += " left join dc_base_tort as t9  on t2.sku = t9.bailun_sku ";
            }
            if (search_data.monitor_status != null)
            {
                countSql += " left join dc_auto_config_sku_warehouse as t10 on t2.sku = t10.bailun_sku and t1.warehouse_code = t10.warehouse_code ";
            }

            countSql += " where t1.stateid !=5 and t2.deliverycount > t2.storagecount and t1.isdeleted=0 and t1.isexception=0 ";



            parameters.Add("time", DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));

            if (search_data.f_start_date != null)
            {
                sql += " and t5.`first_inbound_date`>=@f_start_date ";
                countSql += " and t5.`first_inbound_date`>=@f_start_date ";
                parameters.Add("f_start_date", search_data.f_start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (search_data.f_end_date != null)
            {
                sql += " and t5.`first_inbound_date`<=@f_end_date ";
                countSql += " and t5.`first_inbound_date`<=@f_end_date ";
                parameters.Add("f_end_date", search_data.f_end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (search_data.start_date != null && search_data.end_date != null)
            {
                sql += "  and t5.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                countSql += "  and t5.bailun_sku_warehouse_code  in (select t_re.bailun_sku_warehouse_code from dc_base_stock_record_effective as t_re where t_re.stock_time >=@start_date and t_re.stock_time <=@end_date ) ";
                parameters.Add("start_date", search_data.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("end_date", search_data.end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (categoryModels != null && categoryModels.Count >= 1)
            {
                sql += " and t4.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
                countSql += " and t4.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
            }
            if (!string.IsNullOrWhiteSpace(search_data.buyer_name))
            {
                sql += " and t4.buyer_name = @buyer_name ";
                countSql += " and t4.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", search_data.buyer_name);
            }
            if (search_data.product_type > 0)
            {
                sql += " and t4.product_type = @product_type ";
                countSql += " and t4.product_type = @product_type ";
                parameters.Add("product_type", search_data.product_type);
            }
            if (!string.IsNullOrWhiteSpace(search_data.order_no))
            {
                sql += " and t1.`code` = @order_no ";
                countSql += " and t1.`code` = @order_no ";
                parameters.Add("order_no", search_data.order_no);
            }
            if (search_data.monitor_status == 1)
            {
                sql += " and t10.`status`=1 ";
                countSql += " and t10.`status`=1 ";
            }
            if (search_data.monitor_status == 0)
            {
                sql += " and ( t10.`status`=0 or t10.`status` is null ) ";
                countSql += " and ( t10.`status`=0 or t10.`status` is null ) ";
            }
            if (search_data.has_tort != null)
            {
                if (search_data.has_tort == 0)
                {
                    sql += " and t9.id is null ";
                    countSql += " and t9.id is null ";
                }
                else if (search_data.has_tort == 1)
                {
                    sql += " and t9.id is not null ";
                    countSql += " and t9.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and t3.hq_type = @hq_type ";
                countSql += " and t3.hq_type = @hq_type ";

                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and t3.area_id = @area_id ";
                countSql += " and t3.area_id = @area_id ";

                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.searchType > 0 && !string.IsNullOrWhiteSpace(search_data.key_words))
            {
                switch (search_data.searchType)
                {
                    case 1:
                        sql += " and t2.sku in @bailun_skus ";
                        countSql += " and t2.sku in @bailun_skus ";
                        parameters.Add("bailun_skus", search_data.key_words.Split(','));
                        break;
                    case 2:
                        sql += " and t2.sku like @bailun_sku ";
                        countSql += " and t2.sku like @bailun_sku ";

                        parameters.Add("bailun_sku", $"%{search_data.key_words}%");
                        break;
                    case 3:
                        sql += " and t4.product_inner_code like @product_inner_code ";
                        countSql += " and t4.product_inner_code like @product_inner_code ";

                        parameters.Add("product_inner_code", $"%{search_data.key_words}%");
                        break;
                    case 4:
                        sql += " and t4.sku_title_cn like @sku_title_cn ";
                        countSql += " and t4.sku_title_cn like @sku_title_cn ";

                        parameters.Add("sku_title_cn", $"%{search_data.key_words}%");
                        break;
                    case 5:
                        sql += " and t4.product_inner_code = @product_inner_code ";
                        countSql += " and t4.product_inner_code = @product_inner_code ";
                        parameters.Add("product_inner_code", $"{search_data.key_words}");
                        break;
                    case 6:
                        sql += " and t1.code = @sys_order_code ";
                        countSql += " and t1.code = @sys_order_code ";
                        parameters.Add("sys_order_code", $"{search_data.key_words}");
                        break;
                }

            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.targetwareno=@warehouse_code ";
                countSql += " and t1.targetwareno=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.supplier_name))
            {
                sql += " and t4.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", search_data.supplier_name);
            }
            if (search_data.isSum)
            {
                total = 0;
            }
            else
            {
                if (search_data.is_warehouse_sum)
                {
                    countSql += " group by t1.warehouse_code ";
                    sql += " group by t1.warehouse_code ";

                    total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
                }
                else
                {
                    sql += " group by t1.`code`,t2.sku,t8.transfer_no ";
                    countSql += " group by t1.`code`,t2.sku,t8.transfer_no ";
                    if (search_data.limit > 1000)
                    {
                        total = 0;
                    }
                    else
                    {
                        total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
                    }

                }



                if (string.IsNullOrWhiteSpace(search_data.sort))
                {
                    search_data.sort = "( ifnull(t8.expectarrivaltime,'9999-01-01') )";
                    search_data.order = "asc";
                }
                if (!string.IsNullOrEmpty(search_data.sort) && !string.IsNullOrEmpty(search_data.order))
                {
                    sql += " order by " + search_data.sort;

                    if (!string.IsNullOrEmpty(search_data.order))
                    {
                        sql += " " + search_data.order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + search_data.offset + "," + search_data.limit;
            }
            var obj = _connection.Query<order_transfer_full_link_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            foreach (var item in obj)
            {
                item.history_sevenday_sales = (item.history_sevenday_sales ?? 0).Round1();
                item.history_fourteenday_sales = (item.history_fourteenday_sales ?? 0).Round1();
                item.history_thirtyday_sales = (item.history_thirtyday_sales ?? 0).Round1();
            }
            return obj;
        }

        /// <summary>
        /// 暂存调拨单列表
        /// </summary>
        public static List<dc_base_trans_temp_schedule_dto> TempTransferList(temp_transfer_order_search_dto search, ref int total)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = " select * from dc_base_trans_temp_schedule as t1 where t1.is_delete=0 and t1.is_temp_data=1 and   t1.is_exception=0 ";
            string countSql = " select count(1) from dc_base_trans_temp_schedule as t1 where t1.is_delete=0 and t1.is_temp_data=1 and   t1.is_exception=0";
            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                sql += " and t1.sku=@bailun_sku ";
                countSql += " and t1.sku=@bailun_sku ";
                parameters.Add("bailun_sku", search.bailun_sku);
            }
            if (!string.IsNullOrWhiteSpace(search.target_ware_no))
            {
                sql += " and t1.target_ware_no=@target_ware_no ";
                countSql += " and t1.target_ware_no=@target_ware_no ";
                parameters.Add("target_ware_no", search.target_ware_no);
            }
            if (search.type == 1)
            {
                sql += " and t1.create_state=@create_state ";
                countSql += " and t1.create_state=@create_state ";
                parameters.Add("create_state", (int)dc_base_trans_temp_schedule_create_state_enum.未创建);
            }

            if (search.type == 2)
            {
                sql += " and t1.create_state=@create_state ";
                countSql += " and t1.create_state=@create_state ";
                parameters.Add("create_state", (int)dc_base_trans_temp_schedule_create_state_enum.已暂存);
            }
            total = _connection.Query<int>(countSql, parameters, commandTimeout: 0).Count();
            sql += " limit " + search.offset + "," + search.limit;
            var obj = _connection.Query<dc_base_trans_temp_schedule_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            return obj;
        }


        //刷新采购单理论交期
        public static void ResetPurchaseQuantityNotInbound()
        {
            var task_name = $"ResetPurchaseQuantityNotInboun_v2";
            var conn = MyMySqlConnection._connection; ;
            // 查询最后一次成功抓取的记录
            var last_task_synchro_log = conn.QuerySingleOrDefault<task_synchro_log>(" select  * from task_synchro_log where task_name=@task_name and status=1 order by end_time desc limit 1 ", new { task_name = task_name });

            var new_task_synchro_log = new task_synchro_log
            {
                create_date = DateTime.Now,
                end_time = DateTime.Now.AddMinutes(-2),
                status = 0,
                task_name = task_name
            };

            if (last_task_synchro_log != null)
            {
                new_task_synchro_log.start_time = last_task_synchro_log.end_time.AddMinutes(-1);
            }
            else
            {
                new_task_synchro_log.start_time = DateTime.Now.AddYears(-1);
            }

            new_task_synchro_log.id = conn.Insert(new_task_synchro_log) ?? 0;
            int count = 0;
            int page = 1;
            int rows = 1000;

            var now = DateTime.Now;
            while (true)
            {
                var datas = conn.Query<dc_base_purchase_ResetPurchaseQuantityNotInbound_dto>($@" select t1.* from dc_base_purchase as t1  
where t1.gmt_modified>=@btime and t1.gmt_modified <=@etime 
order by t1.gmt_modified asc 
 limit {(page - 1) * rows},{rows}  ", new { btime = new_task_synchro_log.start_time, etime = new_task_synchro_log.end_time }, commandTimeout: 0).ToList();
                if (datas.Count <= 0) { break; }
                page++;
                foreach (var item in datas)
                {
                    DateTime? estimated_arrival_time_aims = item.estimated_arrival_time;
                    // 计算aims的理论交期

                    //取采购平均交期
                    var ave_days = _connection.QueryFirstOrDefault<int?>(" select  average_supplier_delivery+inspection_delivery from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                    {
                        bailun_sku = item.bailun_sku,
                        warehouse_code = item.warehouse_code
                    });
                    if (ave_days != null)
                    {
                        var temp_estimated_arrival_time_aims = item.create_time.AddDays(ave_days.Value - 1);
                        if (temp_estimated_arrival_time_aims < estimated_arrival_time_aims)
                        {
                            estimated_arrival_time_aims = temp_estimated_arrival_time_aims;
                        }
                    }

                    _connection.Execute(" update dc_base_purchase set estimated_arrival_time_aims=@estimated_arrival_time_aims where id=@id ", new
                    {
                        estimated_arrival_time_aims = estimated_arrival_time_aims,
                        id = item.id
                    });

                }

            }


            new_task_synchro_log.count = count;
            new_task_synchro_log.status = 1;
            conn.Update(new_task_synchro_log);
        }

        public class dc_base_purchase_ResetPurchaseQuantityNotInbound_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
            public int id { get; set; }
            public DateTime? estimated_arrival_time { get; set; }
            public DateTime create_time { get; set; }
        }

        /// <summary>
        /// 刷新最近7天的订单总数
        /// </summary>
        public static void ResetOrders7()
        {
            // 清空数据先
            _connection.Execute(" update dc_base_stock set orders_7=0 where orders_7>0 ", commandTimeout: 0);

            var orderCounts = _connection.Query<reset_orders_7_dto>($@"select bailun_sku,warehouse_code,count(1) as 'orders_7' from dc_base_oms_sku_30 where 
  has_fba_s = 0
    and has_delete = 0
   and has_scalp = 0
  and has_buyer_remark = 0
 and has_platsku_remark = 0
  and has_innersale = 0
and bailun_order_status !='Canceled'
 and platform_order_type != '手工单'
and pay_time>=@btime and pay_time<=@etime and warehouse_code='GZBLWH' GROUP BY bailun_sku,warehouse_code",new {
                btime = DateTime.Now.AddDays(-7).ToDayHome(),
                etime = DateTime.Now.AddDays(-1).ToDayEnd(),
            },commandTimeout:0);

           

            // 写入
            foreach (var item in orderCounts)
            {
                _connection.Execute(" update dc_base_stock set orders_7=@orders_7,orders_7_reset_date=now() where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new {
                    orders_7 = item.orders_7,
                    bailun_sku = item.bailun_sku,
                    warehouse_code = item.warehouse_code
                }, commandTimeout: 0);
            }

        }

        public class reset_orders_7_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
            public int orders_7 { get; set; }
        }
    }


}

