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;

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)
        {
            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))
                {
                    return new List<dc_estimated_arrival_dto>();
                }
                else
                {
                    var nos = relation.Replace("[", "").Replace("]", "").Split(',')[index];
                    var datas = new List<dc_estimated_arrival_dto>();
                    if (nos != "N/A")
                    {
                        foreach (var no_str in nos.Split('*'))
                        {
                            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);

                        }

                        return datas;
                    }


                    return datas;
                }
            }
        }

        /// <summary>
        /// 查询时实库存数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static List<dc_base_stock_dto> RealtimeList(int? monitor_status, int? searchType, string key_words, string warehouse_code, string supplier_name, int offset, int limit, ref int total, string warehousetype, int? warehousearea, bool isSum = false, string order = null, string sort = null, int? has_tort = null, List<bailun_category_new_dto> categoryModels = null, string buyer_name = null,
           DateTime? start_date = null, DateTime? end_date = null, DateTime? f_start_date = null, DateTime? f_end_date = null, int? product_type = null,bool is_warehouse_sum = false)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (isSum || 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_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 (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',
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',
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,

dbw.area_name,
dbw.hq_type,

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',

t3.moq,
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'
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_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

";

                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 (start_date != null && 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(buyer_name) || product_type > 0)
            {
                countSql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (has_tort != null)
            {
                countSql += " left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku ";
            }
            if (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 (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", f_start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (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", f_end_date.Value.ToString("yyyy-MM-dd 23:59:59"));
            }
            if (start_date != null && 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", start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("end_date", 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(buyer_name))
            {
                sql += " and t3.buyer_name = @buyer_name ";
                countSql += " and t3.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", buyer_name);
            }
            if (product_type > 0)
            {
                sql += " and t3.product_type = @product_type ";
                countSql += " and t3.product_type = @product_type ";
                parameters.Add("product_type", product_type);
            }
            if (monitor_status == 1)
            {
                sql += " and t7.`status`=1 ";
                countSql += " and t7.`status`=1 ";
            }
            if (monitor_status == 0)
            {
                sql += " and ( t7.`status`=0 or t7.`status` is null ) ";
                countSql += " and ( t7.`status`=0 or t7.`status` is null ) ";
            }
            if (has_tort != null)
            {
                if (has_tort == 0)
                {
                    sql += " and t5.id is null ";
                    countSql += " and t5.id is null ";
                }
                else if (has_tort == 1)
                {
                    sql += " and t5.id is not null ";
                    countSql += " and t5.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and dbw.hq_type = @hq_type ";
                countSql += " and dbw.hq_type = @hq_type ";

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

                parameters.Add("area_id", warehousearea);
            }
            if (searchType > 0 && !string.IsNullOrWhiteSpace(key_words))
            {
                switch (searchType)
                {
                    case 1:
                        sql += " and t1.bailun_sku in @bailun_skus ";
                        countSql += " and t1.bailun_sku in @bailun_skus ";
                        parameters.Add("bailun_skus", 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", $"%{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", $"%{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", $"%{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", $"{key_words}");
                        break;
                }

            }
            if (!string.IsNullOrWhiteSpace(warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", warehouse_code);
            }
            if (!string.IsNullOrWhiteSpace(supplier_name))
            {
                sql += " and t1.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", supplier_name);
            }
            if (isSum)
            {
                total = 0;
            }
            else
            { 
                if (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 (limit > 1000)
                    {
                        total = 0;
                    }
                    else{
                        total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
                    }
                    
                }
               


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

                    if (!string.IsNullOrEmpty(order))
                    {
                        sql += " " + order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + offset + "," + 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;
        }

        /// <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)
        {
            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 ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.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)
            {
                sql += " and t1.push_date>=@btime ";
                countSql += " and t1.push_date>=@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))
            {
                sql += " and t1.platform=@platform ";
                countSql += " and t1.platform=@platform ";
                parameters.Add("platform", platform);
            }
            if (!string.IsNullOrWhiteSpace(bailun_sku))
            {
                sql += " and t1.bailun_sku in @bailun_sku ";
                countSql += " and t1.bailun_sku in @bailun_sku ";
                parameters.Add("bailun_sku", bailun_sku.Split(','));
            }

            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 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'
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} 毫秒 ");
        }
    }


}

