using AutoTurnOver.Models;
using AutoTurnOver.Models.Report;
using Dapper;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using AutoTurnOver.Models.Base;
using AutoTurnOver.DB.Base;
using AutoTurnOver.Models.stock;
using AutoTurnOver.Utility;

namespace AutoTurnOver.DB
{
    public class report : connectionHelper
    {
        /// <summary>
        /// 获取采购建议明细列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<quantity_out_stock_dto> QuantityOutStockList(quantity_out_stock_search_dto m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var list = new List<quantity_out_stock_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();
                string sql = "";
                if (m.isSum)
                {
                    sql = @"
select 
sum(t1.quantity_out_stock) as 'quantity_out_stock',
sum(t1.quantity_transfer+t1.quantity_purchase) as 'quantity_ontheway'
from dc_mid_transit 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
where t1.quantity_out_stock>0 and ( t3.buyer_name in ('张莹霞','张莹霞1','冯兆欣') and t1.warehouse_code ='GZBLWH' )  ";
                }
                else
                {
                    sql = @"
select 
t1.warehouse_code,
t2.warehouse_name,
t1.bailun_sku,
t3.sku_title_cn as 'sku_name',
t1.quantity_out_stock,
t3.buyer_name,
t1.gmt_out_stock_modified as 'update_time',
(t1.quantity_transfer+t1.quantity_purchase) as 'quantity_ontheway', -- 总在途
t1.gmt_purchase_modified,
t1.gmt_transfer_modified
from dc_mid_transit 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
where t1.quantity_out_stock>0 and ( t3.buyer_name in ('张莹霞','张莹霞1','冯兆欣') and t1.warehouse_code ='GZBLWH' ) 
  ";
                }

                if (!string.IsNullOrWhiteSpace(m.product_inner_code))
                {
                    sql += " and t3.product_inner_code=@product_inner_code ";
                    parameters.Add("product_inner_code", m.product_inner_code);
                }
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t2.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t2.area_id=" + m.warehousearea;
                }
                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += $" and t1.bailun_sku=@bailun_sku ";
                    parameters.Add("bailun_sku", m.bailun_sku);
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                if (!string.IsNullOrWhiteSpace(m.purchase_user))
                {
                    sql += " and t3.buyer_name =@buyer_name ";
                    parameters.Add("buyer_name", m.purchase_user);
                }

                if (m.isSum || limit > 10000)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<int>("select count(0) from (" + sql + ") tb1", parameters);

                    //设置默认排序字段
                    if (string.IsNullOrWhiteSpace(sort)) sort = "t1.quantity_out_stock";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        sql += " order by " + sort;

                        if (!string.IsNullOrEmpty(order))
                        {
                            sql += " " + order;
                        }
                        else
                        {
                            sql += " asc";
                        }
                    }

                    sql += " limit " + offset + "," + limit;
                }
                var obj = _connection.Query<quantity_out_stock_dto>(sql, parameters, buffered: false, commandTimeout: 0);
                return obj.AsList();

            }
            catch (Exception ex)
            {
                return list;
            }
        }

        /// <summary>
        /// 获取缺货明细列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<quantity_out_stock_detail_dto> QuantityOutStockDetailList(quantity_out_stock_detail_search_dto m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var list = new List<quantity_out_stock_detail_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();
                string sql = "";
                string countSql = "";
                countSql = @"
select 
count(1)
from dc_base_oms_sku_outofstock as t1 
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
where 1=1
  ";
                if (m.isSum)
                {
                    sql = @"
select 
count(DISTINCT t1.origin_order_id) as 'sum_order_count',
count(DISTINCT case when t2.quantity_out_stock>0 then t1.bailun_sku else null end) as 'sum_sku_count'
from dc_base_oms_sku_outofstock as t1 
left join dc_mid_transit as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
where 1=1
 ";
                }
                else
                {
                    sql = @"
select 
t2.gmt_out_stock_modified,
t1.origin_order_id,
t1.platform_type,
t1.bailun_order_id,
t1.transaction_id,
t1.website,
t1.pay_time,
t1.seller_account,
t1.receipt_country,
t1.bailun_sku,
t1.bailun_sku_title_cn as 'sku_name',
t1.bailun_sku_quantity_ordered,
t1.warehouse_code,
t1.warehouse_name,
(case when t1.bailun_sku_quantity_ordered>t1.bailun_sku_quantity_picked then '缺货' else '不缺货' end) as 'is_out_stock',
t2.quantity_out_stock,
t3.sum_usable_stock,
t4.sum_unshipped_quantity,
t3.warehouse_codes,
(ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0)) as 'sum_quantity_out_stock',
(case when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t6.buyer_name not in ('张莹霞','张莹霞1') then '非国内共享仓' when ifnull(t3.sum_usable_stock,0) < ifnull(t4.sum_unshipped_quantity,0) then '缺货' else '未缺货' end) as 'is_sum_quantity_out_stock',
t6.buyer_name,
(GREATEST(t3.sum_usable_stock - t5.usable_stock,0)) as 'other_stock', -- 其他仓库存
t1.bailun_require_logistics,
t1.logistics_method_name,
( case when t7.`status`=0 or t7.`status` is null then '监控中' else '停止监控' end ) as 'monitor_status',
(case when t6.`status`=1 then '停售' else '在售' end) as 'sku_status',
(t2.quantity_transfer+t2.quantity_purchase) as 'quantity_ontheway', -- 总在途
t8.buy_ontheway_detail,
t9.items,
t6.brand,
t1.bailun_sku_quantity_shipped
from dc_base_oms_sku_outofstock as t1 
left join dc_mid_transit as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join china_warehouse_stock as t3 on t1.bailun_sku = t3.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join china_warehouse_unshipped as t4 on t1.bailun_sku = t4.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join dc_base_stock as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
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 buy_ontheway_detail as t8 on t1.bailun_sku = t8.bailun_sku and t1.warehouse_code_extend = t8.warehouse_code
left join dc_base_oms_bailun_no_map_items as t9 on t1.bailun_order_id = t9.bailun_order_id
where 1=1
  ";
                }

                if (!string.IsNullOrWhiteSpace(m.key_words) && m.search_type > 0)
                {
                    switch (m.search_type.Value)
                    {
                        case 1:
                            sql += " and t1.bailun_sku =@bailun_sku ";
                            countSql += " and t1.bailun_sku =@bailun_sku ";
                            parameters.Add("bailun_sku", m.key_words);
                            break;
                        case 2:
                            sql += " and t6.product_inner_code =@product_inner_code ";
                            countSql += " t6.product_inner_code =@product_inner_code ";
                            parameters.Add("product_inner_code", m.key_words);
                            break;
                        case 3:
                            sql += " and t1.bailun_order_id =@bailun_order_id ";
                            countSql += " and t1.bailun_order_id =@bailun_order_id ";
                            parameters.Add("bailun_order_id", m.key_words);
                            break;
                        default: break;
                    }

                }
                if( !string.IsNullOrWhiteSpace(m.platform_type))
                {
                    sql += " and t1.platform_type=@platform_type ";
                    countSql += " and t1.platform_type=@platform_type ";
                    parameters.Add("platform_type", m.platform_type);
                }
                if (m.bailun_account_id != null)
                {
                    sql += " and t1.bailun_account_id=@bailun_account_id ";
                    countSql += " and t1.bailun_account_id=@bailun_account_id ";
                    parameters.Add("bailun_account_id", m.bailun_account_id);
                }
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t_db.hq_type=" + $"'{m.warehousetype}'";
                    countSql += " and t_db.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t_db.area_id=" + m.warehousearea;
                    countSql += " and t_db.area_id=" + m.warehousearea;
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    countSql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                if (!string.IsNullOrWhiteSpace(m.purchase_user))
                {
                    sql += " and t6.buyer_name =@buyer_name ";
                    countSql += " and t6.buyer_name =@buyer_name ";
                    parameters.Add("buyer_name", m.purchase_user);
                }
                if (m.pay_time_min != null)
                {
                    sql += " and t1.pay_time >= @pay_time_min ";
                    countSql += " and t1.pay_time >= @pay_time_min ";
                    parameters.Add("pay_time_min", m.pay_time_min.Value.ToString("yyyy-MM-dd 00:00:00"));
                }
                if (m.pay_time_max != null)
                {
                    sql += " and t1.pay_time <= @pay_time_max ";
                    countSql += " and t1.pay_time <= @pay_time_max ";
                    parameters.Add("pay_time_max", m.pay_time_min.Value.ToString("yyyy-MM-dd 23:59:59"));
                }
                if (!string.IsNullOrWhiteSpace(m.shiplogistics_code))
                {
                    sql += " and t1.logistics_method_code = @shiplogistics_code ";
                    countSql += " and t1.logistics_method_code = @shiplogistics_code ";
                    parameters.Add("shiplogistics_code", m.shiplogistics_code);
                }
                if (limit > 10000 || m.isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<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<quantity_out_stock_detail_dto>(sql, parameters, buffered: false, commandTimeout: 0);

                return obj.AsList();

            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 缺货改0
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<out_stock_dto> OutStockList(out_stock_search_dto m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var list = new List<out_stock_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();
                string sql = "";
                string countSql = "";
                countSql = @"
select 
count(1)
from dc_mid_transit as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join china_warehouse_stock as t3 on t1.bailun_sku = t3.bailun_sku and t_db.hq_type='国内仓'
left join china_warehouse_unshipped as t4 on t1.bailun_sku = t4.bailun_sku and t_db.hq_type='国内仓'
where ( t_db.hq_type!='国内仓' or t2.buyer_name not in ('张莹霞','张莹霞1') ) and t_db.hq_type!='fba仓' and (case when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') then (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0)) else t1.quantity_out_stock end )>0
";
                sql = @"
select 
t2.product_code,
t1.warehouse_code,
t_db.warehouse_name,
t1.bailun_sku,
t2.sku_title_cn as 'sku_name',
(case when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') then (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0)) else t1.quantity_out_stock end ) as 'sum_quantity_out_stock',
( case when t7.`status`=0 or t7.`status` is null then '监控中' else '停止监控' end ) as 'monitor_status',
t2.buyer_name,
t8.bailun_require_logistics as 'ebay_bailun_require_logistics',
t8.logistics_method_name as 'ebay_logistics_method_name'
from dc_mid_transit as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join china_warehouse_stock as t3 on t1.bailun_sku = t3.bailun_sku and t_db.hq_type='国内仓'
left join china_warehouse_unshipped as t4 on t1.bailun_sku = t4.bailun_sku and t_db.hq_type='国内仓'
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 (
	select bailun_sku,warehouse_code,GROUP_CONCAT(DISTINCT bailun_require_logistics) as 'bailun_require_logistics',GROUP_CONCAT(DISTINCT logistics_method_name) as 'logistics_method_name' from dc_base_oms_sku_outofstock as t1 where t1.	platform_type='ebay' and t1.bailun_picking_status!='TotalPicked' GROUP BY bailun_sku,warehouse_code
) as t8 on  t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.warehouse_code
where ( t_db.hq_type!='国内仓' or t2.buyer_name not in ('张莹霞','张莹霞1') ) and t_db.hq_type!='fba仓' and (case when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') then (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0)) else t1.quantity_out_stock end )>0
  ";
                if (!string.IsNullOrWhiteSpace(m.key_words) && m.search_type > 0)
                {
                    switch (m.search_type.Value)
                    {
                        case 1:
                            sql += " and t1.bailun_sku =@bailun_sku ";
                            countSql += " and t1.bailun_sku =@bailun_sku ";
                            parameters.Add("bailun_sku", m.key_words);
                            break;
                        case 2:
                            sql += " and t2.product_code =@product_inner_code ";
                            countSql += " t2.product_code =@product_inner_code ";
                            parameters.Add("product_code", m.key_words);
                            break;
                        default: break;
                    }

                }

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t_db.hq_type=" + $"'{m.warehousetype}'";
                    countSql += " and t_db.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t_db.area_id=" + m.warehousearea;
                    countSql += " and t_db.area_id=" + m.warehousearea;
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    countSql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                if (!string.IsNullOrWhiteSpace(m.purchase_user))
                {
                    sql += " and t2.buyer_name =@buyer_name ";
                    countSql += " and t2.buyer_name =@buyer_name ";
                    parameters.Add("buyer_name", m.purchase_user);
                }

                if (limit > 10000 || m.isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<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<out_stock_dto>(sql, parameters, buffered: false, commandTimeout: 0);
                return obj.AsList();

            }
            catch (Exception ex)
            {
                return list;
            }
        }

        /// <summary>
        /// 国内仓缺货计算
        /// </summary>
        /// <param name="is_all">是否全量数据</param>
        /// <returns></returns>
        public static List<dc_auto_shortage_push> ChinaShortagePush(string platform, bool is_all = false)
        {
            var conn = _connection;
            var shortage_list = new List<dc_auto_shortage_push>();
            // 缺货推送
            string sql = @"select 
t1.bailun_sku,
t1.warehouse_code,
now() as 'push_date',
1 as 'type',
t5.usable_stock as 'stocks',
@platform as 'platform',
0 as 'has_return_goods',
t2.buyer_name,
t_db.hq_type as 'warehouse_type'
from dc_mid_transit as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join (
     select t1.bailun_sku,t2.area_id,sum(t1.usable_stock) as 'sum_usable_stock' from dc_base_stock as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ,'BLGZ03','YWWH01') 
    GROUP BY t1.bailun_sku,t2.area_id
) as t3 on t1.bailun_sku = t3.bailun_sku and t_db.area_id = t3.area_id
left join (
    select t1.bailun_sku,t2.area_id,sum(t1.quantity_unshipped) as 'sum_unshipped_quantity',sum(t1.quantity_purchase) as 'sum_quantity_purchase' from dc_mid_transit as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.quantity_unshipped>0 and t1.warehouse_code in ( 'GZBLWH', 'QYBLZZ','BLGZ03','YWWH01' )
    GROUP BY t1.bailun_sku,t2.area_id
) as t4 on t1.bailun_sku = t4.bailun_sku and  t_db.area_id = t4.area_id
left join dc_base_stock as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_auto_config_sku_warehouse as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
where
( t1.warehouse_code in ('GZBLWH', 'QYBLZZ','BLGZ03') or ( t1.warehouse_code in ('YWWH01') and ( t2.`status`=1 or t6.`status`=1 ) ) ) -- 义务仓 只要停止监控 或者停售就推送
and t1.bailun_sku not in ('942904501') 
and ( t1.quantity_purchase<=0 and (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0))>0 ) -- 有缺货
and  (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0))>0  -- 有缺货
and  ( case 
	when @platform!='aliexpress' then ( t2.buyer_name not in ('张莹霞','张莹霞1','赵美聪','黄镜洁','赵美聪','赵美聪1','秦振荣','李华娟','赵美聪-独立站') or t2.`status`=1  ) 
	else ( t2.`status`=1 or t6.`status`=1)
	end
  )  -- 速卖通的必须要停止监控才推送
and  ( ifnull(t4.sum_quantity_purchase,0)<=0 or t2.buyer_name in ('张莹霞','张莹霞1','赵美聪','黄镜洁','赵美聪','赵美聪1','秦振荣','李华娟','赵美聪-独立站')
) -- 无在途 或者 自产 （自产的有在途也要推）
and not exists ( select * from dc_auto_shortage_push_not_config where dc_auto_shortage_push_not_config.warehouse_code=t1.warehouse_code and  dc_auto_shortage_push_not_config.bailun_sku=t1.bailun_sku )
";
            if (!is_all)
            {
                sql += $" and not exists ( select * from dc_auto_shortage_push where dc_auto_shortage_push.warehouse_code = t1.warehouse_code and dc_auto_shortage_push.bailun_sku = t1.bailun_sku and dc_auto_shortage_push.`type`=1 and dc_auto_shortage_push.platform='{platform}' and dc_auto_shortage_push.has_return_goods=0 ) ";
            }
            shortage_list.AddRange(conn.Query<dc_auto_shortage_push>(sql,new { platform  = platform },commandTimeout: 0));

            // 0库存推送
            string no_library_sql = @" select t1.bailun_sku,
t1.warehouse_code,
now() as 'push_date',
0 as 'stocks',
@platform as 'platform',
0 as 'has_return_goods',
t3.buyer_name,
t_db.hq_type as 'warehouse_type',
2 as 'type' from dc_base_stock as t1  
left join dc_mid_transit as t2 on t1.warehouse_code  =t2.warehouse_code and t1.bailun_sku  =t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join (
     select t1.bailun_sku,t2.area_id,sum(t1.usable_stock) as 'sum_usable_stock' from dc_base_stock as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.warehouse_code in ( 'GZBLWH', 'QYBLZZ','BLGZ03','YWWH01' ) 
    GROUP BY t1.bailun_sku,t2.area_id
) as t4 on t1.bailun_sku = t4.bailun_sku and t_db.area_id = t4.area_id
left join (
    select t1.bailun_sku,t2.area_id,sum(t1.quantity_purchase) as 'sum_quantity_purchase' from dc_mid_transit as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.quantity_unshipped>0 and t1.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ,'BLGZ03','YWWH01')
    GROUP BY t1.bailun_sku,t2.area_id
) as t5 on t1.bailun_sku = t5.bailun_sku and  t_db.area_id = t5.area_id
left join dc_auto_config_sku_warehouse as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
where t1.usable_stock<=0 and t1.bailun_sku!=''  
and ( t1.warehouse_code in ('GZBLWH', 'QYBLZZ','BLGZ03') or ( t1.warehouse_code in ('YWWH01') and ( t3.`status`=1 or t6.`status`=1 ) ) ) -- 义务仓 只要停止监控 或者停售就推送
and t1.bailun_sku not in ('942904501') 
and  ( case 
	when @platform!='aliexpress' then ( t3.buyer_name not in ('张莹霞','张莹霞1','赵美聪','黄镜洁','赵美聪','赵美聪1','秦振荣','李华娟','赵美聪-独立站') or t3.`status`=1  ) 
	else ( t3.`status`=1 or t6.`status`=1)
	end
  )  -- 速卖通的必须要停止监控才推送
and IFNULL(t4.sum_usable_stock,0)<=0  -- 无库存
and  ( ifnull(t5.sum_quantity_purchase,0)<=0 or t3.buyer_name in ('张莹霞','张莹霞1','赵美聪','黄镜洁','赵美聪','赵美聪1','秦振荣','李华娟','赵美聪-独立站')
) -- 无在途 或者 自产 （自产的有在途也要推）
and not exists ( select * from dc_auto_shortage_push_not_config where dc_auto_shortage_push_not_config.warehouse_code=t1.warehouse_code and  dc_auto_shortage_push_not_config.bailun_sku=t1.bailun_sku )
";
            if (!is_all)
            {
                no_library_sql += $" and not exists ( select * from dc_auto_shortage_push where dc_auto_shortage_push.warehouse_code = t1.warehouse_code and dc_auto_shortage_push.bailun_sku = t1.bailun_sku and dc_auto_shortage_push.`type`=2 and dc_auto_shortage_push.platform='{platform}' and dc_auto_shortage_push.has_return_goods=0 ) ";
            }
            shortage_list.AddRange(conn.Query<dc_auto_shortage_push>(no_library_sql, new { platform  = platform },commandTimeout: 0));

            return shortage_list;

        }

        /// <summary>
        /// 国外仓缺货计算
        /// 海外仓缺货不理会，库存小于2才推
        /// </summary>
        /// <param name="is_all">是否全量数据</param>
        /// <returns></returns>
        public static List<dc_auto_shortage_push> NotChinaShortagePush(List<string> warehouse_codes,string platform, bool is_all = false)
        {
            var conn = _connection;
            var shortage_list = new List<dc_auto_shortage_push>();
            // 缺货推送
            string sql = @"select 
t1.bailun_sku,
t1.warehouse_code,
now() as 'push_date',
1 as 'type',
t5.usable_stock as 'stocks',
@platform as 'platform',
0 as 'has_return_goods',
t2.buyer_name,
t_db.hq_type as 'warehouse_type'
from dc_mid_transit as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_stock as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join (
     select t1.bailun_sku,t2.area_id,sum(t1.usable_stock) as 'usable_stock' from dc_base_stock as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.warehouse_code in @warehouse_codes
    -- and t1.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
    GROUP BY t1.bailun_sku,t2.area_id
) as t6 on t1.bailun_sku = t6.bailun_sku and t_db.area_id = t6.area_id
left join (
    select t1.bailun_sku,t2.area_id,sum(t1.quantity_unshipped) as 'quantity_unshipped' from dc_mid_transit as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    left join dc_base_stock as t3 on t1.warehouse_code = t3.warehouse_code and t1.bailun_sku = t3.bailun_sku
    where t1.quantity_unshipped>0 and t1.warehouse_code in @warehouse_codes
    -- and t3.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
    GROUP BY t1.bailun_sku,t2.area_id
) as t7 on t1.bailun_sku = t7.bailun_sku and  t_db.area_id = t7.area_id
where
t1.warehouse_code in @warehouse_codes
-- and t5.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
and ( ifnull(t6.usable_stock,0)<ifnull(t7.quantity_unshipped,0) ) -- 有缺货

";
            if (!is_all)
            {
                sql += $" and not exists ( select * from dc_auto_shortage_push where dc_auto_shortage_push.warehouse_code = t1.warehouse_code and dc_auto_shortage_push.bailun_sku = t1.bailun_sku and dc_auto_shortage_push.`type`=1 and dc_auto_shortage_push.platform='{platform}' and dc_auto_shortage_push.has_return_goods=0 ) ";
            }
            
            //shortage_list.AddRange(conn.Query<dc_auto_shortage_push>(sql,new { warehouse_codes = warehouse_codes, platform = platform }, commandTimeout: 0));

            // 0库存推送
            string no_library_sql = @" select t1.bailun_sku,
t1.warehouse_code,
now() as 'push_date',
0 as 'stocks',
@platform as 'platform',
2 as 'type' ,
0 as 'has_return_goods',
t2.buyer_name,
t_db.hq_type as 'warehouse_type'
from dc_base_stock as t1 
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join (
	 select t1.bailun_sku,t2.area_id,sum(t1.usable_stock) as 'usable_stock' from dc_base_stock as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.warehouse_code in @warehouse_codes
    -- and t1.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
    GROUP BY t1.bailun_sku,t2.area_id
) as t6 on t1.bailun_sku = t6.bailun_sku and t_db.area_id = t6.area_id 
where t1.warehouse_code in @warehouse_codes 
-- and t1.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
and ifnull(t6.usable_stock,0)<2 -- (case when t2.buyer_name in ('张莹霞','张莹霞1') and @platform='ebay'  then 2 else 0 end )
and not exists ( select * from dc_auto_shortage_push_not_config where dc_auto_shortage_push_not_config.warehouse_code=t1.warehouse_code and  dc_auto_shortage_push_not_config.bailun_sku=t1.bailun_sku  )
   ";
            if (!is_all)
            {
                no_library_sql += $" and not exists ( select * from dc_auto_shortage_push where dc_auto_shortage_push.warehouse_code = t1.warehouse_code and dc_auto_shortage_push.bailun_sku = t1.bailun_sku and dc_auto_shortage_push.`type`=2 and dc_auto_shortage_push.platform='{platform}' and dc_auto_shortage_push.has_return_goods=0  )  ";
            }
            shortage_list.AddRange(conn.Query<dc_auto_shortage_push>(no_library_sql,new { warehouse_codes  = warehouse_codes, platform = platform }, commandTimeout: 0));

            return shortage_list;

        }

        /// <summary>
        /// 缺货推送
        /// </summary>
        /// <param name="is_all">是否全量数据</param>
        /// <returns></returns>
        public static List<dc_auto_shortage_push> EbayShortagePush(bool is_all = false)
        {
            var conn = _connection;
            var shortage_list = new List<dc_auto_shortage_push>();
            // 缺货推送
            string sql = @"select 
t1.bailun_sku,
t1.warehouse_code,
now() as 'push_date',
1 as 'type',
'ebay' as 'platform',
(case when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') then t5.usable_stock else ifnull(t3.sum_usable_stock,0) end ) as 'stocks'
from dc_mid_transit as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join china_warehouse_stock as t3 on t1.bailun_sku = t3.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) 
left join china_warehouse_unshipped as t4 on t1.bailun_sku = t4.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join dc_base_stock as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
where
t1.warehouse_code in ('GBYKD','JZHYBLGC','MDBLWYT','MXBLWYT','BLUSGDC','GBBMHBL','GBBLJW','GB4PXBL','DEBLDG','QYBLZZ','USGCBL','USHWBL','US4PXBL','AUWYTBL','AU4PXBL','GBWYTUK','GBLQST','DYGXC','GZBLWH')
and ( t_db.hq_type!='国内仓' or 
	( 
		t1.bailun_sku not in ('942904501') 
		and  
		t2.buyer_name not in ('张莹霞','张莹霞1','赵美聪','黄镜洁','赵美聪','赵美聪1','秦振荣')
	)
)
and t_db.hq_type!='fba仓' 
and (
	case 
	when t_db.hq_type='国内仓' then t1.quantity_purchase
	else t1.quantity_transfer
	end
)<=0
and (case when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') then (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0)) else t1.quantity_out_stock end )>0
and not exists ( select * from dc_auto_shortage_push_not_config where dc_auto_shortage_push_not_config.warehouse_code=t1.warehouse_code and  dc_auto_shortage_push_not_config.bailun_sku=t1.bailun_sku )
";
            if (!is_all)
            {
                sql += " and not exists ( select * from dc_auto_shortage_push where dc_auto_shortage_push.warehouse_code = t1.warehouse_code and dc_auto_shortage_push.bailun_sku = t1.bailun_sku and dc_auto_shortage_push.`type`=1 and dc_auto_shortage_push.platform='ebay' ) ";
            }
            shortage_list.AddRange(conn.Query<dc_auto_shortage_push>(sql,commandTimeout: 0));

            // 0库存推送
            string no_library_sql = @" select t1.bailun_sku,
t1.warehouse_code,
now() as 'push_date',
0 as 'stocks',
'ebay' as 'platform',
2 as 'type' from dc_base_stock as t1  
left join dc_mid_transit as t2 on t1.warehouse_code  =t2.warehouse_code and t1.bailun_sku  =t2.bailun_sku
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
where t1.usable_stock<=0 and t1.bailun_sku!=''  
and t1.warehouse_code in ('GBYKD','JZHYBLGC','MDBLWYT','MXBLWYT','BLUSGDC','GBBMHBL','GBBLJW','GB4PXBL','DEBLDG','QYBLZZ','USGCBL','USHWBL','US4PXBL','AUWYTBL','AU4PXBL','GBWYTUK','GBLQST','DYGXC','GZBLWH')
and ( t_db.hq_type!='国内仓' or 
	( 
		t1.bailun_sku not in ('942904501') 
		and  
		t3.buyer_name not in ('张莹霞','张莹霞1','赵美聪','黄镜洁','赵美聪','赵美聪1','秦振荣')
	)
)
and (
	case 
	when t_db.hq_type='国内仓' then 0
	else t2.quantity_transfer
	end
)<=0 -- 国内仓有没有在途都要推 ， 国外仓有调拨在途就不推了
and not EXISTS ( 
	select * from dc_base_stock as t_stock 
	left join dc_base_warehouse as t_stock_w on t_stock.warehouse_code = t_stock_w.warehouse_code
	left join dc_mid_transit as t_stock_t on t_stock.warehouse_code  =t_stock_t.warehouse_code and t_stock.bailun_sku  =t_stock_t.bailun_sku
	where  
	t_stock.warehouse_code != 'BLGZ03'
	and ( ( t_stock_w.hq_type='国内仓' and t_db.hq_type='国内仓' ) or ( t_db.hq_type!='国内仓' and t_stock_w.hq_type!='国内仓' and t_db.area_id=t_stock_w.area_id ) ) 
	and t_stock.bailun_sku= t1.bailun_sku and  
	( t_stock.usable_stock>0  )
) -- 同属性仓库 （是否国内） 有库存，就不算 0库存

and not exists ( select * from dc_auto_shortage_push_not_config where dc_auto_shortage_push_not_config.warehouse_code=t1.warehouse_code and  dc_auto_shortage_push_not_config.bailun_sku=t1.bailun_sku )
";
            if (!is_all)
            {
                no_library_sql += " and not exists ( select * from dc_auto_shortage_push where dc_auto_shortage_push.warehouse_code = t1.warehouse_code and dc_auto_shortage_push.bailun_sku = t1.bailun_sku and dc_auto_shortage_push.`type`=2 and dc_auto_shortage_push.platform='ebay'  ) ";
            }
            shortage_list.AddRange(conn.Query<dc_auto_shortage_push>(no_library_sql, commandTimeout: 0));

            return shortage_list;

        }

        public static void AddShortagePush(IEnumerable<dc_auto_shortage_push> datas)
        {
            var conn = _connection;
            foreach (var item in datas)
            {
                conn.Insert(item);
            }
        }

        public static void removeShortagePush(IEnumerable<int> datas)
        {
            if(datas!=null && datas.Count() >= 1)
            {
                var conn = _connection;
                //foreach (var item in datas)
                //{
                //    conn.Delete<dc_auto_shortage_push>(item);
                //}
                conn.Execute(" update dc_auto_shortage_push set has_return_goods=1 where id in @ids ", new { ids = datas });
            }
            
        }



        /// <summary>
        /// 回货速改
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public static List<return_speed_change_dto> ReturnSpeedChange(return_speed_change_search_dto m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var list = new List<return_speed_change_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();
                string sql = "";
                string countSql = "";
                countSql = @"
select 
count(1)
from dc_auto_shortage_push as t1
left join dc_mid_transit as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_base_warehouse as t_db on t1.warehouse_code  = t_db.warehouse_code
left join china_warehouse_stock as t4 on t1.bailun_sku = t4.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join china_warehouse_unshipped as t5 on t1.bailun_sku = t5.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join dc_base_stock as t_stock on t1.bailun_sku = t_stock.bailun_sku and t1.warehouse_code = t_stock.warehouse_code
where 1=1
";
                sql = @"
select 
t1.id,
t2.product_code,
t1.warehouse_code,
t_db.warehouse_name,
t1.bailun_sku,
t2.sku_title_cn as 'sku_name',
(case 
when t_db.hq_type='国内仓' and (ifnull(t4.sum_usable_stock,0) - ifnull(t5.sum_unshipped_quantity,0) > 0 )  then 1
when ( t3.quantity_out_stock is null or t3.quantity_out_stock<=0 ) and ifnull(t_stock.usable_stock,0)>ifnull(t3.quantity_unshipped,0) then 1
else 0
end
) as 'has_available_stock',
 ( case when t7.`status`=0 or t7.`status` is null then '监控中' else '停止监控' end ) as 'monitor_status',
t2.buyer_name,
t8.bailun_require_logistics as 'ebay_bailun_require_logistics',
t8.logistics_method_name as 'ebay_logistics_method_name'
from dc_auto_shortage_push as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_mid_transit as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_base_warehouse as t_db on t1.warehouse_code  = t_db.warehouse_code
 left join china_warehouse_stock as t4 on t1.bailun_sku = t4.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
 left join china_warehouse_unshipped as t5 on t1.bailun_sku = t5.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join dc_base_stock as t_stock on t1.bailun_sku = t_stock.bailun_sku and t1.warehouse_code = t_stock.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 (
	select bailun_sku,warehouse_code,GROUP_CONCAT(DISTINCT bailun_require_logistics) as 'bailun_require_logistics',GROUP_CONCAT(DISTINCT logistics_method_name) as 'logistics_method_name' from dc_base_oms_sku_outofstock as t1 where t1.	platform_type='ebay' and t1.bailun_picking_status!='TotalPicked' GROUP BY bailun_sku,warehouse_code
) as t8 on  t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.warehouse_code
where 1=1
 ";
                if (!string.IsNullOrWhiteSpace(m.key_words) && m.search_type > 0)
                {
                    switch (m.search_type.Value)
                    {
                        case 1:
                            sql += " and t1.bailun_sku =@bailun_sku ";
                            countSql += " and t1.bailun_sku =@bailun_sku ";
                            parameters.Add("bailun_sku", m.key_words);
                            break;
                        case 2:
                            sql += " and t2.product_code =@product_inner_code ";
                            countSql += " t2.product_code =@product_inner_code ";
                            parameters.Add("product_code", m.key_words);
                            break;
                        case 3:
                            sql += " and t1.bailun_sku in @bailun_skus ";
                            countSql += " and t1.bailun_sku in @bailun_skus ";
                            parameters.Add("bailun_skus", m.key_words.Split(','));
                            break;
                        default: break;
                    }

                }

                if (m.has_available_stock != null)
                {
                    sql += @"  and (case 
when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') and(ifnull(t4.sum_usable_stock, 0) - ifnull(t5.sum_unshipped_quantity, 0) > 0)  then 1
when(t3.quantity_out_stock is null or t3.quantity_out_stock <= 0) and ifnull(t_stock.usable_stock,0)> ifnull(t3.quantity_unshipped, 0) then 1
else 0
end
) = @has_available_stock ";
                    countSql += @"  and (case 
when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') and(ifnull(t4.sum_usable_stock, 0) - ifnull(t5.sum_unshipped_quantity, 0) > 0)  then 1
when(t3.quantity_out_stock is null or t3.quantity_out_stock <= 0) and ifnull(t_stock.usable_stock,0)> ifnull(t3.quantity_unshipped, 0) then 1
else 0
end
) = @has_available_stock ";
                    parameters.Add("has_available_stock", m.has_available_stock == true ? 1 : 0);
                }

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t_db.hq_type=" + $"'{m.warehousetype}'";
                    countSql += " and t_db.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t_db.area_id=" + m.warehousearea;
                    countSql += " and t_db.area_id=" + m.warehousearea;
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    countSql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                if (!string.IsNullOrWhiteSpace(m.purchase_user))
                {
                    sql += " and t2.buyer_name =@buyer_name ";
                    countSql += " and t2.buyer_name =@buyer_name ";
                    parameters.Add("buyer_name", m.purchase_user);
                }

                if (limit > 10000 || m.isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<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<return_speed_change_dto>(sql, parameters, buffered: false, commandTimeout: 0);
                return obj.AsList();

            }
            catch (Exception ex)
            {
                return list;
            }
        }

        /// <summary>
        /// 回货速改推送
        /// </summary>
        public static List<dc_return_goods_push> ReturnGoodsPush(string platform)
        {
            var conn = _connection;
            var shortage_list = new List<dc_return_goods_push>();
            // 回货速改推送
            string sql = @"select 
t1.id as 'shortage_push_id',
t1.bailun_sku,
t1.warehouse_code,
now() as 'push_time',
(case 
when t_db.hq_type in ( '国内仓' )  then ifnull(tc1.sum_usable_stock,0)  - ifnull(tc2.sum_unshipped_quantity,0)
else ifnull(tnc1.usable_stock,0)  - ifnull(tnc2.quantity_unshipped,0)
end
)  as 'stocks',
t2.buyer_name
from dc_auto_shortage_push as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_mid_transit as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_base_warehouse as t_db on t1.warehouse_code  = t_db.warehouse_code
left join dc_base_stock as t_stock on t1.bailun_sku = t_stock.bailun_sku and t1.warehouse_code = t_stock.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 (
     select t1.bailun_sku,t2.area_id,sum(t1.usable_stock) as 'sum_usable_stock' from dc_base_stock as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) 
    GROUP BY t1.bailun_sku,t2.area_id
) as tc1 on t1.bailun_sku = tc1.bailun_sku and t_db.area_id = tc1.area_id
left join (
    select t1.bailun_sku,t2.area_id,sum(t1.quantity_unshipped) as 'sum_unshipped_quantity',sum(t1.quantity_purchase) as 'sum_quantity_purchase' from dc_mid_transit as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.quantity_unshipped>0 and t1.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
    GROUP BY t1.bailun_sku,t2.area_id
) as tc2 on t1.bailun_sku = tc2.bailun_sku and  t_db.area_id = tc2.area_id
left join (
     select t1.bailun_sku,t2.area_id,sum(t1.usable_stock) as 'usable_stock' from dc_base_stock as t1 
    left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
    where t1.warehouse_code in (
		'GBYKD', 'JZHYBLGC', 'MDBLWYT', 'MXBLWYT', 'BLUSGDC', 'GBBMHBL', 'GB4PXBL', 'DEBLDG', 'USGCBL', 'USHWBL', 'US4PXBL', 'AUWYTBL', 'AU4PXBL', 'GBWYTUK', 'GBLQST' 
		)
    -- and t1.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
    GROUP BY t1.bailun_sku,t2.area_id
) as tnc1 on t1.bailun_sku = tnc1.bailun_sku and t_db.area_id = tnc1.area_id
left join (
    select t1.bailun_sku,t2.area_id,sum(t1.quantity_unshipped) as 'quantity_unshipped' from dc_mid_transit as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_stock as t3 on t1.warehouse_code = t3.warehouse_code and t1.bailun_sku = t3.bailun_sku
    where t1.quantity_unshipped>0 and t1.warehouse_code in (
		'GBYKD', 'JZHYBLGC', 'MDBLWYT', 'MXBLWYT', 'BLUSGDC', 'GBBMHBL', 'GB4PXBL', 'DEBLDG', 'USGCBL', 'USHWBL', 'US4PXBL', 'AUWYTBL', 'AU4PXBL', 'GBWYTUK', 'GBLQST' 
		)
        -- and t3.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
    GROUP BY t1.bailun_sku,t2.area_id
) as tnc2 on t1.bailun_sku = tnc2.bailun_sku and  t_db.area_id = tnc2.area_id

where 1=1 
-- t_stock.bailun_sku_warehouse_code not in ( select bailun_sku_warehouse_code from dc_auto_shortage_push_ignore )
and t1.platform=@platform and t1.has_return_goods=0
and 
(
case 
	when t_db.hq_type not in ( '国内仓' ) then ifnull(tnc1.usable_stock,0) - ifnull(tnc2.quantity_unshipped,0) >= 2 
	else  ifnull(tc2.sum_unshipped_quantity,0) < ifnull(tc1.sum_usable_stock,0) 
end 
)

";
            shortage_list.AddRange(conn.Query<dc_return_goods_push>(sql,new { platform= platform }, commandTimeout: 0));

            return shortage_list;

        }
        public static List<dc_return_goods_push> ReturnGoodsPush_bak(string platform)
        {
            var conn = _connection;
            var shortage_list = new List<dc_return_goods_push>();
            // 回货速改推送
            string sql = @"select 
t1.id as 'shortage_push_id',
t1.bailun_sku,
t1.warehouse_code,
now() as 'push_time',
(case 
when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1')   then IFNULL(t4.sum_usable_stock, 0) - IFNULL(t5.sum_unshipped_quantity, 0)
else t_stock.usable_stock - ifnull(t3.quantity_unshipped,0)
end
)  as 'stocks',
t2.buyer_name
from dc_auto_shortage_push as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_mid_transit as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_base_warehouse as t_db on t1.warehouse_code  = t_db.warehouse_code
 left join china_warehouse_stock as t4 on t1.bailun_sku = t4.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
 left join china_warehouse_unshipped as t5 on t1.bailun_sku = t5.bailun_sku and t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' )
left join dc_base_stock as t_stock on t1.bailun_sku = t_stock.bailun_sku and t1.warehouse_code = t_stock.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
where t1.platform=@platform and t1.has_return_goods=0
and (case 
when t_db.warehouse_code in ( 'GZBLWH', 'QYBLZZ' ) and t2.buyer_name not in ('张莹霞','张莹霞1') and (ifnull(t4.sum_usable_stock, 0) - ifnull(t5.sum_unshipped_quantity, 0) > 0)  then 1
when(t3.quantity_out_stock is null or t3.quantity_out_stock <= 0) and ifnull(t_stock.usable_stock,0)> ifnull(t3.quantity_unshipped, 0) then 1
else 0
end
) = 1
";
            shortage_list.AddRange(conn.Query<dc_return_goods_push>(sql,new { platform= platform }, commandTimeout: 0));

            return shortage_list;

        }

        /// <summary>
        /// 回货速改推送记录写入
        /// </summary>
        /// <param name="datas"></param>
        public static void AddReturnGoodsPush(IEnumerable<dc_return_goods_push> datas)
        {
            var conn = _connection;
            foreach (var item in datas)
            {
                conn.Insert(item);
            }
        }
        /// <summary>
        /// 标记回货
        /// </summary>
        public static int MarkReturnSpeedChange(return_speed_change_search_dto m)
        {
            int total = 0;
            var datas = ReturnSpeedChange(m, 0, int.MaxValue,ref total);
            if (datas == null)
            {
                throw new Exception("未选择数据");
            }
            _connection.Execute(" delete from dc_outofstock_history where id in @ids ",new { ids = datas.Select(s=>s.id).ToArray() });
            return datas.Count();
        }

        /// <summary>
        /// 刷新缺货数据
        /// </summary>
        public static void ResetOutofstock()
        {
            try
            {

                _connection.Execute(@" -- 刷新 订单items映射表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_oms_bailun_no_map_items_temp;

INSERT into dc_base_oms_bailun_no_map_items_temp(`bailun_order_id`,`items`) (
	select t1.bailun_order_id,GROUP_CONCAT(DISTINCT t1.item_id) as 'items' from dc_base_oms_platform_sku as t1
where t1.create_time>'2019-03-01 12:00:00' and t1.bailun_order_status = 'Handling' and t1.has_delete=0 and t1.has_scalp=0 and t1.bailun_interception_status not in ('Success','Part') and t1.platform_type!='FBA'
GROUP BY t1.bailun_order_id
);

alter table dc_base_oms_bailun_no_map_items rename dc_base_oms_bailun_no_map_itemsTemp; 
alter table dc_base_oms_bailun_no_map_items_temp rename dc_base_oms_bailun_no_map_items; 
alter table dc_base_oms_bailun_no_map_itemsTemp rename dc_base_oms_bailun_no_map_items_temp; 
truncate table dc_base_oms_bailun_no_map_items_temp;   ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新缺货明细表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_oms_sku_outofstock_temp;

INSERT into dc_base_oms_sku_outofstock_temp (
	select 
	t1.`id`,
  t1.`origin_order_id`,
  t1.`platform_type`,
  t1.`transaction_id`,
  t1.`pay_time`,
  t1.`pay_method`,
  t1.`pay_account`,
  t1.`pay_status`,
  t1.`collection_account`,
  t1.`platform_order_type`,
  t1.`website`,
  t1.`create_time`,
  t1.`paid_time`,
  t1.`refund_time`,
  t1.`seller_id`,
  t1.`bailun_account_id`,
  t1.`hq_account`,
  t1.`seller_account`,
  t1.`seller_order_id`,
  t1.`seller_email`,
  t1.`order_update_time`,
  t1.`seller_order_exchange_rate`,
  t1.`seller_other_exchange_rate`,
  t1.`finance_order_exchange_rate`,
  t1.`finance_other_exchange_rate`,
  t1.`cny_to_usd_exchange_rate`,
  t1.`order_to_usd_exchange_rate`,
  t1.`other_to_usd_exchange_rate` ,
  t1.`order_currency`,
  t1.`other_currency`,
  t1.`seller_name`,
  t1.`bailun_order_id`,
  t1.`platform_order_status`,
  t1.`bailun_order_status`,
  t1.`bailun_payment_status`,
  t1.`bailun_interception_status`,
  t1.`shipping_status`,
  t1.`bailun_sku`,
  t1.`bailun_sku_title_en`,
  t1.`bailun_sku_title_cn`,
  t1.`bailun_sku_quantity_ordered`,
  t1.`bailun_sku_quantity_shipped`,
  t1.`bailun_sku_quantity_refund`,
  t1.`bailun_sku_quantity_picked`,
  t1.`warehouse_name`,
  t1.`warehouse_code_fba`,
  t1.`warehouse_code`,
  t1.`bailun_sku_unit_price`,
  t1.`bailun_sku_weight`,
  t1.`bailun_sku_size`,
  t1.`amount_sales`,
  t1.`amount_total`,
  t1.`amount_shipping`,
  t1.`amount_product`,
  t1.`amount_tax`,
  t1.`amount_adjustment`,
  t1.`amount_gift_wrap`,
  t1.`amount_refund`,
  t1.`amount_prepaid`,
  t1.`cost_total`,
  t1.`cost_promotion`,
  t1.`cost_platform_fee`,
  t1.`cost_product`,
  t1.`cost_shipping`,
  t1.`cost_package`,
  t1.`cost_fba_fee`,
  t1.`cost_paypal_fee`,
  t1.`cost_refund_commisson`,
  t1.`cost_handle_bailun`,
  t1.`cost_handle_platform`,
  t1.`cost_tail`,
  t1.`cost_first`,
  t1.`cost_logistics`,
  t1.`profit_total`,
  t1.`profit_rate`,
  t1.`receipt_country`,
  t1.`receipt_city`,
  t1.`receipt_area`,
  t1.`receipt_address`,
  t1.`receipt_address_2`,
  t1.`receipt_postal_code`,
  t1.`receiver`,
  t1.`receiver_phone`,
  t1.`buyer_id`,
  t1.`buyer_name`,
  t1.`buyer_email`,
  t1.`has_fba_exception`,
  t1.`has_platform_exception`,
  t1.`has_cancle`,
  t1.`area_id`,
  t1.`bailun_category_id`,
  t1.`bailun_category_name`,
  t1.`gmt_create`,
  t1.`gmt_modified`,
  t1.`ratio_weight`,
  t1.`ratio_price`,
  t1.`bailun_picking_status`,
  t1.`bailun_require_logistics`,
  t1.`has_delete`,
  t1.`has_scalp`,
  t1.`logistics_method_code`,
  t1.`logistics_method_name`,
  t1.`has_innersale`,
  t1.`company_id`,
  t1.`bailun_refund_status`,
  t1.`refund_obj`,
  t1.`refund_type`,
  t1.`refund_reference_id`,
  t1.`has_fba_s`,
  t1.`bailun_sku_quantity_pushed`,
	( CASE WHEN ( `t1`.`warehouse_code` IN ( 'QYBLZZ', 'GZBLWH' ) ) THEN 'QYBLZZ-GZBLWH' ELSE `t1`.`warehouse_code` END ) as 'warehouse_code_extend'
from dc_base_oms_sku as t1 
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
where t1.company_id=1 and  t1.has_innersale=0 and t1.create_time>'2019-03-01 12:00:00' and t1.bailun_picking_status!='TotalPicked' and t1.bailun_order_status = 'Handling' and t1.has_delete=0 and t1.has_scalp=0 and t1.has_buyer_remark = 0
  and t1.has_platsku_remark = 0 and t1.bailun_interception_status not in ('Success','Part') and t1.platform_type!='FBA'
);

alter table dc_base_oms_sku_outofstock rename dc_base_oms_sku_outofstockTemp; 
alter table dc_base_oms_sku_outofstock_temp rename dc_base_oms_sku_outofstock; 
alter table dc_base_oms_sku_outofstockTemp rename dc_base_oms_sku_outofstock_temp; 
truncate table dc_base_oms_sku_outofstock_temp;     ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新缺货明细表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table china_warehouse_stock_temp;

INSERT into china_warehouse_stock_temp(`bailun_sku`,`sum_usable_stock`,`warehouse_codes`) (
	SELECT
	`t1`.`bailun_sku` AS `bailun_sku`,
	sum( `t1`.`usable_stock` + t1.occupy_stock ) AS `sum_usable_stock`,
	group_concat( ( CASE WHEN ( `t1`.`usable_stock` + t1.occupy_stock > 0 ) THEN `t1`.`warehouse_code` ELSE '' END ) SEPARATOR ' | ' ) AS `warehouse_codes` 
FROM
	( `dc_base_stock` `t1` LEFT JOIN `dc_base_warehouse` `t2` ON ( ( `t1`.`warehouse_code` = `t2`.`warehouse_code` ) ) ) 
WHERE
	( ( `t2`.`hq_type` = '国内仓' ) AND ( `t1`.`warehouse_code` IN ( 'GZBLWH', 'QYBLZZ' ,'BLGZ03' ) ) ) 
GROUP BY
	`t1`.`bailun_sku`
);
alter table china_warehouse_stock rename china_warehouse_stockTemp; 
alter table china_warehouse_stock_temp rename china_warehouse_stock; 
alter table china_warehouse_stockTemp rename china_warehouse_stock_temp; 
truncate table china_warehouse_stock_temp; ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新国内仓未发货
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table china_warehouse_unshipped_temp;

INSERT into china_warehouse_unshipped_temp(`bailun_sku`,`sum_unshipped_quantity`) (
	 SELECT
	`t1`.`bailun_sku` AS `bailun_sku`,
	sum(case when t1.bailun_order_status !='Canceled' and t1.has_delete=0 and t1.has_scalp=0  then ( `t1`.`bailun_sku_quantity_ordered` - `t1`.`bailun_sku_quantity_picked` ) else 0 end ) AS `sum_unshipped_quantity` 
FROM
	( `dc_base_oms_sku_outofstock` `t1` LEFT JOIN `dc_base_warehouse` `t2` ON ( ( `t1`.`warehouse_code` = `t2`.`warehouse_code` ) ) ) 
WHERE
    t1.has_innersale=0 
	 and `t2`.`hq_type` = '国内仓' and t1.warehouse_code IN ( 'GZBLWH', 'QYBLZZ' ,'BLGZ03'  )   
	 GROUP BY
	`t1`.`bailun_sku`
);

alter table china_warehouse_unshipped rename china_warehouse_unshippedTemp; 
alter table china_warehouse_unshipped_temp rename china_warehouse_unshipped; 
alter table china_warehouse_unshippedTemp rename china_warehouse_unshipped_temp; 
truncate table china_warehouse_unshipped_temp;  ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新缺货明细表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table buy_ontheway_detail_temp;

INSERT into buy_ontheway_detail_temp(`bailun_sku`,`warehouse_code`,`buy_ontheway_detail`) (
	select 
t1.*
from view_buy_ontheway_detail as t1
);

alter table buy_ontheway_detail rename buy_ontheway_detailTemp; 
alter table buy_ontheway_detail_temp rename buy_ontheway_detail; 
alter table buy_ontheway_detailTemp rename buy_ontheway_detail_temp; 
truncate table buy_ontheway_detail_temp;   ", commandTimeout: 0);

                //刷新无库仓商品日均
//                _connection.Execute(@" -- 刷新无库仓商品日均
//set session transaction isolation level read uncommitted;
//start transaction;

//-- 清空视图表的数据
//Truncate table dc_not_stock_goods_sales_temp;

//INSERT into dc_not_stock_goods_sales_temp(`product_inner_code`,`warehouse_code`,`test_sales`,`success_sales`) (
//	select 
//t2.product_inner_code,t2.warehouse_code,
//sum(case when TIMESTAMPDIFF(day,t2.push_time,t1.pay_time)<=21 then t1.bailun_sku_quantity_ordered else 0 end) as 'test_sales', -- 推送之后 21 日的销量
//sum(case when TIMESTAMPDIFF(day,t1.pay_time,now())<=7 then t1.bailun_sku_quantity_ordered else 0 end) as 'success_sales' -- 最近7日销量
//from dc_base_sku as t2 
//left join dc_base_oms_sku as t1 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
//left join dc_auto_config_sku_warehouse as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
//where t2.warehouse_code='BLGZ03' and (t1.id is null or t1.bailun_order_status!='Canceled') and (t3.`status`=0 or t3.`status` is null)  and t2.push_time >= '2010-01-01'
//GROUP BY t2.product_inner_code,t2.warehouse_code
//);

//alter table dc_not_stock_goods_sales rename dc_not_stock_goods_salesTemp; 
//alter table dc_not_stock_goods_sales_temp rename dc_not_stock_goods_sales; 
//alter table dc_not_stock_goods_salesTemp rename dc_not_stock_goods_sales_temp; 
//truncate table dc_not_stock_goods_sales_temp; ", commandTimeout: 0);

//                _connection.Execute(@" -- 记录历史缺货
//set session transaction isolation level read uncommitted;
//start transaction;

//insert ignore into dc_outofstock_history(`bailun_sku`,`warehouse_code`,`date`) (
//	select 
//	t1.bailun_sku,t1.warehouse_code,now() as 'date'
//	from dc_mid_transit as t1
//left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
//left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
//left join china_warehouse_stock as t3 on t1.bailun_sku = t3.bailun_sku and t_db.hq_type='国内仓'
//left join china_warehouse_unshipped as t4 on t1.bailun_sku = t4.bailun_sku and t_db.hq_type='国内仓'
//where ( t_db.hq_type!='国内仓' or t2.buyer_name not in ('张莹霞','张莹霞1') ) and t_db.hq_type!='fba仓' and (case when t_db.hq_type='国内仓' then (ifnull(t4.sum_unshipped_quantity,0) - ifnull(t3.sum_usable_stock,0)) else t1.quantity_out_stock end )>0
//) ");
                _connection.Execute(@" -- 刷新在途
set session transaction isolation level read uncommitted;
start transaction;

Truncate table dc_auto_purchase_ontheway_temp;

INSERT into dc_auto_purchase_ontheway_temp(`bailun_sku`,`warehouse_code`,`purchase_id`,`estimated_arrival_time`,`deliver_name`,`count`) (
	
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.deliver_name,
 sum( ( tb1.count - IFNULL( tb2.count, 0 ) ) ) AS count 
FROM
 (
 SELECT
  bailun_sku,
  warehouse_code,
  parent_id,
  purchase_id,
  estimated_arrival_time,
  has_transfer,
	supplier_name as 'deliver_name',
  sum( count ) AS count 
 FROM
  dc_base_purchase 
 WHERE
  buy_status IN ( 0, 1, 2, 3 ) 
  AND has_delete = 0 
 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 
GROUP BY
 tb1.bailun_sku,
 tb1.warehouse_code,
 tb1.purchase_id
);

alter table dc_auto_purchase_ontheway rename dc_auto_purchase_onthewayTemp; 
alter table dc_auto_purchase_ontheway_temp rename dc_auto_purchase_ontheway; 
alter table dc_auto_purchase_onthewayTemp rename dc_auto_purchase_ontheway_temp; 
truncate table dc_auto_purchase_ontheway_temp; ",commandTimeout:0);

                _connection.Execute(@"-- 刷新 订单sku 标签表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据

INSERT ignore dc_auto_config_sku_label(`bailun_sku`,`label`) (
	select 
t1.bailun_sku,
(
 case 
 when t1.buyer_name='李华娟'  then '无库普货推荐'
 when t1.buyer_name='张莹霞'  then '美甲'
 when t1.buyer_name='张莹霞1'  then '精油+美容'
 when t1.buyer_name='赵美聪' and t1.source = 1 then '服装精准开发'
 when t1.buyer_name='赵美聪' then '服装供应商推荐'
 when t1.buyer_name!='赵美聪' and t1.source = 1 and t1.create_time>='2019-07-01 00:00:00' then '非服装精准开发'
 else '未知'
 end
) as 'label'
from dc_base_sku as t1
);", commandTimeout: 0);

                _connection.Execute(@"-- 刷新缺货明细表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_oms_sku_7_temp;

INSERT into dc_base_oms_sku_7_temp (
	select 
	t1.`id`,
  t1.`origin_order_id`,
  t1.`platform_type`,
  t1.`transaction_id`,
  t1.`pay_time`,
  t1.`pay_method`,
  t1.`pay_account`,
  t1.`pay_status`,
  t1.`collection_account`,
  t1.`platform_order_type`,
  t1.`website`,
  t1.`create_time`,
  t1.`paid_time`,
  t1.`refund_time`,
  t1.`seller_id`,
  t1.`bailun_account_id`,
  t1.`hq_account`,
  t1.`seller_account`,
  t1.`seller_order_id`,
  t1.`seller_email`,
  t1.`order_update_time`,
  t1.`seller_order_exchange_rate`,
  t1.`seller_other_exchange_rate`,
  t1.`finance_order_exchange_rate`,
  t1.`finance_other_exchange_rate`,
  t1.`cny_to_usd_exchange_rate`,
  t1.`order_to_usd_exchange_rate`,
  t1.`other_to_usd_exchange_rate` ,
  t1.`order_currency`,
  t1.`other_currency`,
  t1.`seller_name`,
  t1.`bailun_order_id`,
  t1.`platform_order_status`,
  t1.`bailun_order_status`,
  t1.`bailun_payment_status`,
  t1.`bailun_interception_status`,
  t1.`shipping_status`,
  t1.`bailun_sku`,
  t1.`bailun_sku_title_en`,
  t1.`bailun_sku_title_cn`,
  t1.`bailun_sku_quantity_ordered`,
  t1.`bailun_sku_quantity_shipped`,
  t1.`bailun_sku_quantity_refund`,
  t1.`bailun_sku_quantity_picked`,
  t1.`warehouse_name`,
  t1.`warehouse_code_fba`,
  t1.`warehouse_code`,
  t1.`bailun_sku_unit_price`,
  t1.`bailun_sku_weight`,
  t1.`bailun_sku_size`,
  t1.`amount_sales`,
  t1.`amount_total`,
  t1.`amount_shipping`,
  t1.`amount_product`,
  t1.`amount_tax`,
  t1.`amount_adjustment`,
  t1.`amount_gift_wrap`,
  t1.`amount_refund`,
  t1.`amount_prepaid`,
  t1.`cost_total`,
  t1.`cost_promotion`,
  t1.`cost_platform_fee`,
  t1.`cost_product`,
  t1.`cost_shipping`,
  t1.`cost_package`,
  t1.`cost_fba_fee`,
  t1.`cost_paypal_fee`,
  t1.`cost_refund_commisson`,
  t1.`cost_handle_bailun`,
  t1.`cost_handle_platform`,
  t1.`cost_tail`,
  t1.`cost_first`,
  t1.`cost_logistics`,
  t1.`profit_total`,
  t1.`profit_rate`,
  t1.`receipt_country`,
  t1.`receipt_city`,
  t1.`receipt_area`,
  t1.`receipt_address`,
  t1.`receipt_address_2`,
  t1.`receipt_postal_code`,
  t1.`receiver`,
  t1.`receiver_phone`,
  t1.`buyer_id`,
  t1.`buyer_name`,
  t1.`buyer_email`,
  t1.`has_fba_exception`,
  t1.`has_platform_exception`,
  t1.`has_cancle`,
  t1.`area_id`,
  t1.`bailun_category_id`,
  t1.`bailun_category_name`,
  t1.`gmt_create`,
  t1.`gmt_modified`,
  t1.`ratio_weight`,
  t1.`ratio_price`,
  t1.`bailun_picking_status`,
  t1.`bailun_require_logistics`,
  t1.`has_delete`,
  t1.`has_scalp`,
  t1.`logistics_method_code`,
  t1.`logistics_method_name`,
  t1.`has_innersale`,
  t1.`company_id`,
  t1.`bailun_refund_status`,
  t1.`refund_obj`,
  t1.`refund_type`,
  t1.`refund_reference_id`,
  t1.`has_fba_s`,
  t1.`bailun_sku_quantity_pushed`,
	( CASE WHEN ( `t1`.`warehouse_code` IN ( 'QYBLZZ', 'GZBLWH' ) ) THEN 'QYBLZZ-GZBLWH' ELSE `t1`.`warehouse_code` END ) as 'warehouse_code_extend'
from dc_base_oms_sku as t1 
left join dc_base_warehouse as t_db on t1.warehouse_code = t_db.warehouse_code
left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
where t1.company_id=1 and  t1.has_innersale=0 and t1.create_time>@time and t1.has_delete=0 and t1.has_scalp=0  and t1.has_buyer_remark = 0
  and t1.has_platsku_remark = 0
);

alter table dc_base_oms_sku_7 rename dc_base_oms_sku_7Temp; 
alter table dc_base_oms_sku_7_temp rename dc_base_oms_sku_7; 
alter table dc_base_oms_sku_7Temp rename dc_base_oms_sku_7_temp; 
truncate table dc_base_oms_sku_7_temp;  ", new { time = DateTime.Now.AddDays(-8).ToString("yyyy-MM-dd 00:00:00") },commandTimeout:0);

                _connection.Execute(@"
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_purchase_7_temp;

insert into dc_base_purchase_7_temp( `gmt_create`, `gmt_modified`, `bailun_sku`, `purchase_id`, `warehouse_code`, `warehouse_name`, `status`, `create_time`, `update_time`, `count`, `has_transfer`,  `sku_name`,
  `estimated_arrival_time`,  `supplier_id`,  `supplier_name`,  `confirm_time`,  `pay_time`,  `pay_type`,  `price` ,  `buy_status`,  `transport_type`,  `deliver_id`,  `parent_id`,  `has_delete`,  `company_id`)
(
select 
  `gmt_create`,
  `gmt_modified`,
  `bailun_sku`,
  `purchase_id`,
  `warehouse_code`,
  `warehouse_name`,
  `status`,
  `create_time`,
  `update_time`,
  `count`,
  `has_transfer`,
  `sku_name`,
  `estimated_arrival_time`,
  `supplier_id`,
  `supplier_name`,
  `confirm_time`,
  `pay_time`,
  `pay_type`,
  `price` ,
  `buy_status`,
  `transport_type`,
  `deliver_id`,
  `parent_id`,
  `has_delete`,
  `company_id`
	from dc_base_purchase where `status`!=-1 and confirm_time>=@time
);

alter table dc_base_purchase_7 rename dc_base_purchase_7Temp; 
alter table dc_base_purchase_7_temp rename dc_base_purchase_7; 
alter table dc_base_purchase_7Temp rename dc_base_purchase_7_temp; 
truncate table dc_base_purchase_7_temp;", new { time = DateTime.Now.AddDays(-8).ToString("yyyy-MM-dd 00:00:00") }, commandTimeout: 0);

                _connection.Execute(@"-- 刷新 订单 dc_auto_monitor_sku 映射表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_auto_monitor_sku_temp;

INSERT into dc_auto_monitor_sku_temp(`bailun_sku`) (

	select 
* from monitor_sku_view

);

alter table dc_auto_monitor_sku rename dc_auto_monitor_skuTemp; 
alter table dc_auto_monitor_sku_temp rename dc_auto_monitor_sku; 
alter table dc_auto_monitor_skuTemp rename dc_auto_monitor_sku_temp; 
truncate table dc_auto_monitor_sku_temp; ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新 订单 dc_view_stock_monitor_sku 映射表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_view_stock_monitor_sku_temp;

INSERT into dc_view_stock_monitor_sku_temp(`bailun_sku`) (

	select 
* from stock_monitor_sku_view

);

alter table dc_view_stock_monitor_sku rename dc_view_stock_monitor_skuTemp; 
alter table dc_view_stock_monitor_sku_temp rename dc_view_stock_monitor_sku; 
alter table dc_view_stock_monitor_skuTemp rename dc_view_stock_monitor_sku_temp; 
truncate table dc_view_stock_monitor_sku_temp; ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新 全量库存表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_full_stock_temp;

INSERT into dc_base_full_stock_temp(`warehouse_type`,`warehouse_code`,`bailun_sku`,`usable_stock`,`quantity_safe_inventory`,`sku_name`,`unit_price`) 
(
select * from (
	 select 
 t3.hq_type as 'warehouse_type',t2.warehouse_code,t2.bailun_sku,t2.usable_stock,ifnull(t1.quantity_safe_inventory,0) as 'quantity_safe_inventory',
 ifnull(t4.sku_title_cn,'') as 'sku_name',ifnull(t4.unit_price,0) as 'unit_price'
from dc_base_stock as t2
left join dc_auto_turnover as t1 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as t3 on t2.warehouse_code = t3.warehouse_code
left join dc_base_sku as t4 on t2.bailun_sku = t4.bailun_sku

union

select '半成品仓' as 'warehouse_type',t1.warehouse_code,t1.bailun_sku,t1.usable_stock,t1.quantity_safe_inventory,t1.sku_name,t1.unit_price from dc_base_wip_stock  as t1
) as t1 
);

alter table dc_base_full_stock rename dc_base_full_stockTemp; 
alter table dc_base_full_stock_temp rename dc_base_full_stock; 
alter table dc_base_full_stockTemp rename dc_base_full_stock_temp; 
truncate table dc_base_full_stock_temp;  ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新 全量有效入库记录
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_stock_record_effective_temp;

INSERT into dc_base_stock_record_effective_temp(`gmt_create`,`gmt_modified`,`bailun_sku`,`warehouse_code`,`warehouse_name`,`quantity_stock`,`stock_time`,`has_transfer`,`bailun_sku_warehouse_code`,`bailun_sku_warehouse_area`) 
(
select t1.gmt_create,
  t1.gmt_modified,
  t1.bailun_sku,
  t1.warehouse_code,
  t1.warehouse_name ,
  t1.quantity_stock ,
  t1.stock_time,
  t1.has_transfer,
  t1.bailun_sku_warehouse_code,
	CONCAT(t1.bailun_sku,t2.hq_type,ifnull(t2.area_id,0))
	from dc_base_stock_record as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where t1.has_transfer=1 or t2.hq_type = '国内仓'
);

alter table dc_base_stock_record_effective rename dc_base_stock_record_effectiveTemp; 
alter table dc_base_stock_record_effective_temp rename dc_base_stock_record_effective; 
alter table dc_base_stock_record_effectiveTemp rename dc_base_stock_record_effective_temp; 
truncate table dc_base_stock_record_effective_temp;  ", commandTimeout: 0);

                _connection.Execute(@" -- 刷新 库存表的索引字段
update dc_base_stock as t1,dc_base_warehouse as t2
set bailun_sku_warehouse_code=concat(t1.bailun_sku,t1.warehouse_code),bailun_sku_warehouse_area=concat(t1.bailun_sku,t2.hq_type,IFNULL(t2.area_id,0)) where t1.warehouse_code = t2.warehouse_code;
update dc_base_stock_record set bailun_sku_warehouse_code=concat(bailun_sku,warehouse_code);  
update dc_base_stock_record_effective set bailun_sku_warehouse_code=concat(bailun_sku,warehouse_code);  
update dc_base_stock as t1,
(select t1.bailun_sku_warehouse_area,min(t1.stock_time) as 'min_time' from dc_base_stock_record_effective as t1 
GROUP BY t1.bailun_sku_warehouse_area) as t2
set t1.first_inbound_date = t2.min_time
where  t1.bailun_sku_warehouse_area=t2.bailun_sku_warehouse_area;", commandTimeout: 0);

                _connection.Execute(@" -- 刷新库存日志
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_stock_record_temp;

insert into dc_base_stock_record_temp(`gmt_create`,
  `gmt_modified`,
  `bailun_sku`,
  `warehouse_code`,
  `warehouse_name` ,
  `quantity_stock`,
  `stock_time`,
  `has_transfer`,
  `bailun_sku_warehouse_code`)
	(
	select gmt_create as  'gmt_create',
  gmt_modified as  'gmt_modified',
  bailun_sku as 'bailun_sku',
  warehouse_code as  'warehouse_code',
  warehouse_name as  'warehouse_name' ,
  count as  'quantity_stock',
  create_time as 'stock_time',
  0 as 'has_transfer',
  CONCAT(bailun_sku,warehouse_code) as 'bailun_sku_warehouse_code'
	from dc_base_purchase_inbound where create_time>='2020-01-01' and has_transfer=0
	);
	
	insert into dc_base_stock_record_temp(`gmt_create` ,
  `gmt_modified`,
  `bailun_sku`,
  `warehouse_code`,
  `warehouse_name` ,
  `quantity_stock`,
  `stock_time`,
  `has_transfer`,
  `bailun_sku_warehouse_code`)
	(
	select  gmt_create as 'gmt_create',
  gmt_modified as 'gmt_modified',
  bailun_sku as 'bailun_sku',
  warehouse_code as 'warehouse_code',
  warehouse_name as 'warehouse_name' ,
  count as  'quantity_stock',
  create_time as 'stock_time',
  1 as 'has_transfer',
  CONCAT(bailun_sku,warehouse_code) as  'bailun_sku_warehouse_code'
	from dc_base_transfer_complete where create_time>='2020-01-01'  and `deleted`=0
	);
	

alter table dc_base_stock_record rename dc_base_stock_recordTemp; 
alter table dc_base_stock_record_temp rename dc_base_stock_record; 
alter table dc_base_stock_recordTemp rename dc_base_stock_record_temp; 
truncate table dc_base_stock_record_temp; 



", commandTimeout: 0);
                
                _connection.Execute(@" -- 刷新sku汇总
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_sku_sum_temp;

insert into dc_base_sku_sum_temp(`bailun_sku`,
  `usable_stock`,
  `on_the_way`,
  `stock_amount`,
  `on_the_way_amount` ,
  `history_sevenday_sales`,
  `history_fourteenday_sales`,
  `history_thirtyday_sales`)
	(
	select t1.bailun_sku,sum(t1.usable_stock) as 'usable_stock',sum(ifnull(t5.quantity_transfer,0)+ifnull(t5.quantity_purchase,0)) as 'on_the_way',
sum(ifnull(t1.usable_stock,0) * ifnull(t2.unit_price,0)) as 'stock_amount',(ifnull(t5.quantity_transfer,0)+ifnull(t5.quantity_purchase,0)) as 'on_the_way_amount',
AVG(ifnull(t8.history_sevenday_sales,0)) as 'history_sevenday_sales',AVG(ifnull(t8.history_fourteenday_sales,0)) as 'history_fourteenday_sales',AVG(ifnull(t8.history_thirtyday_sales,0)) as 'history_thirtyday_sales'
from 
dc_base_stock as t1 
left join dc_base_sku as t2 on t1.bailun_sku=t2.bailun_sku 
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_config_sku_warehouse as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code 
left join dc_auto_turnover as t8 on t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.warehouse_code 

group by t1.bailun_sku
	);
	
	
alter table dc_base_sku_sum rename dc_base_sku_sumTemp; 
alter table dc_base_sku_sum_temp rename dc_base_sku_sum; 
alter table dc_base_sku_sumTemp rename dc_base_sku_sum_temp; 
truncate table dc_base_sku_sum_temp; 

", commandTimeout: 0);

                _connection.Execute(@" -- 刷新sku汇总
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_data_character_temp;

insert into dc_base_data_character_temp(`character`,  `bailun_sku`,  `warehouse_code`,`bailun_sku_warehouse_code`)
select '无库' as 'character',bailun_sku,warehouse_code,CONCAT(bailun_sku,warehouse_code) as 'bailun_sku_warehouse_code' from dc_base_stock where warehouse_code in ('BLGZ03','YWWH01');

	
insert into dc_base_data_character_temp(`character`,  `bailun_sku`,  `warehouse_code`,`bailun_sku_warehouse_code`)
select '自产' as 'character',t1.bailun_sku,t1.warehouse_code,CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code' from dc_base_stock as t1 left join dc_base_sku as t2 on t1.bailun_sku  =t2.bailun_sku where t2.buyer_name in ('张莹霞','张莹霞1');
	
insert into dc_base_data_character_temp(`character`,  `bailun_sku`,  `warehouse_code`,`bailun_sku_warehouse_code`)
select '海外仓' as 'character',t1.bailun_sku,t1.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 not in ('国内仓');

	
alter table dc_base_data_character rename dc_base_data_characterTemp; 
alter table dc_base_data_character_temp rename dc_base_data_character; 
alter table dc_base_data_characterTemp rename dc_base_data_character_temp; 
truncate table dc_base_data_character_temp; 

update dc_base_stock as t1,
 (
select bailun_sku,warehouse_code,GROUP_CONCAT(`character`) as 'character' from dc_base_data_character GROUP BY bailun_sku,warehouse_code
) as t2
set t1.`character` = t2.`character`
where t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code;



", commandTimeout: 0);


            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                _connection.Insert<dc_task_error_log>(new dc_task_error_log
                {
                    date = DateTime.Now,
                    message = ex.Message,
                    stack_trace = ex.StackTrace,
                    task_name = "ResetOutofstock"
                });
            }

        }

        //刷新上周的数据
        public static void ResetLastweekData()
        {
            _connection.Execute(@" -- 刷新 sku 上周销量
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_lastweek_data_temp;

insert into dc_base_lastweek_data_temp(`bailun_sku`,  `warehouse_code`,  `sales`,`sales_amount`)

select t1.bailun_sku,t1.warehouse_code, 
sum(t1.bailun_sku_quantity_ordered) as 'sales',
sum(t1.bailun_sku_quantity_ordered * t1.amount_sales * t1.order_to_usd_exchange_rate) as 'sales_amount'
from dc_base_oms_sku t1
join dc_base_oms_order t2 on t1.bailun_order_id=t2.bailun_order_id and t2.has_scalp=0 and t2.has_innersale=0
left join dc_base_oms_pick t3 on t1.bailun_order_id=t3.bailun_order_id and t1.bailun_sku=t3.bailun_sku and t3.has_delete=0
left join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku
left join dc_base_sku_source t5 on t4.source = t5.source
join dc_base_warehouse t6 on t3.warehouse_code=t6.warehouse_code and t6.hq_type!='国内仓'
where t1.has_delete=0 and t1.company_id=1 and t2.bailun_order_status!='Canceled' 
and (t1.platform_type!='FBA' and t1.bailun_order_status!='CantHandle') 
and t2.paid_time>=@btime and t2.paid_time<@etime 
GROUP BY t1.bailun_sku,t1.warehouse_code;

alter table dc_base_lastweek_data rename dc_base_lastweek_dataTemp; 
alter table dc_base_lastweek_data_temp rename dc_base_lastweek_data; 
alter table dc_base_lastweek_dataTemp rename dc_base_lastweek_data_temp; 
truncate table dc_base_lastweek_data_temp; ", new { btime = DateTime.Now.AddDays(-7).GetWeekFirstDayMon().ToDayHome(),etime = DateTime.Now.AddDays(-7).GetWeekLastDaySun().ToDayEnd() }, commandTimeout: 0);
        }

        public static void ResetCashFlowData()
        {
            //现金流汇总报表，9点之前还在跑数据，不用刷新
            if (DateTime.Now.Hour >= 9)
            {
                _connection.Execute(@" -- 刷新 现金流汇总表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_report_cash_flow_group_day_temp;

insert dc_report_cash_flow_group_day_temp (`platform_type`,`web_site`,`year`,`month`,`day`,`val`,`data_type`,`type`,`date`)
(
select platform_type,web_site,year(occur_time) as 'year',month(occur_time) as 'month',day(occur_time) as 'day',sum(val) as 'val',data_type,1,min(occur_time) as 'date' from dc_report_cash_flow_log where  is_delete=0  and occur_time>=@btime GROUP BY occur_time_year_month_no,day(occur_time)
)
;
", new { btime   =DateTime.Now.AddMonths(-3).ToString("yyyy-MM-01 00:00:00")}, commandTimeout: 0);

                _connection.Execute(@" -- 刷新 现金流汇总表
insert dc_report_cash_flow_group_day_temp (`platform_type`,`web_site`,`year`,`month`,`day`,`val`,`data_type`,`type`,`date`)
(
select platform_type,web_site,year(pay_time) as 'year',month(pay_time) as 'month',day(pay_time) as 'day',sum(val) as 'val',data_type,2,min(pay_time) as 'date' from dc_report_cash_flow_log where  is_delete=0 and pay_time>=@btime  GROUP BY pay_time_year_month_no,day(pay_time)
);

alter table dc_report_cash_flow_group_day rename dc_report_cash_flow_group_dayTemp; 
alter table dc_report_cash_flow_group_day_temp rename dc_report_cash_flow_group_day; 
alter table dc_report_cash_flow_group_dayTemp rename dc_report_cash_flow_group_day_temp; 
truncate table dc_report_cash_flow_group_day_temp;  
", new { btime = DateTime.Now.AddMonths(-3).ToString("yyyy-MM-01 00:00:00") }, commandTimeout: 0);

                var datePar = new {
                    btime = DateTime.Now.GetMonthFirstDay(),
                    etime = DateTime.Now.LastDayOfMonth().ToString("yyyy-MM-dd 23:59:59"),
                    lastBtime = DateTime.Now.AddMonths(-1).GetMonthFirstDay(),
                    lastEtime = DateTime.Now.AddMonths(-1).LastDayOfMonth().ToString("yyyy-MM-dd 23:59:59")
                };
                _connection.Execute(@" -- 刷新 现金流汇总表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_report_cash_flow_sku_group_temp;

insert dc_report_cash_flow_sku_group_temp(`warehouse_code`,`bailun_sku`,`balance`,`current_date_begin`,`current_date_end`,`last_date_begin`,`last_date_end`,`current_income`,`current_expend`,`last_income`,`last_expend`,`type`)
select 
warehouse_code,
bailun_sku,
sum(val) as 'balance', 
@btime  as current_date_begin,
@etime  as current_date_end,
@lastBtime   as last_date_begin,
@lastEtime   as last_date_end,
sum(case when occur_time>=@btime and data_type in (2) then val else 0 end) as 'current_income', 
sum(case when occur_time>=@btime and data_type not in (2) then val else 0 end ) as 'current_expend', 
sum(case when occur_time<@btime and data_type in (2) then val else 0 end ) as 'last_income', 
sum(case when occur_time<@btime and data_type not in (2) then  val else 0 end ) as 'last_expend',
1 as 'type'
from dc_report_cash_flow_log where occur_time>=@lastBtime and data_type in (2,3,5,8,9,10,11,12,13,14) and warehouse_code!='' and is_delete=0
GROUP BY bailun_sku,warehouse_code;

insert dc_report_cash_flow_sku_group_temp(`warehouse_code`,`bailun_sku`,`balance`,`current_date_begin`,`current_date_end`,`last_date_begin`,`last_date_end`,`current_income`,`current_expend`,`last_income`,`last_expend`,`type`)
select 
warehouse_code,
bailun_sku,
sum(val) as 'balance', 
@btime  as current_date_begin,
@etime  as current_date_end,
@lastBtime  as last_date_begin,
@lastEtime  as last_date_end,
sum(case when pay_time>=@btime and data_type in (2) then val else 0 end) as 'current_income', 
sum(case when pay_time>=@btime and data_type not in (2) then val else 0 end ) as 'current_expend', 
sum(case when pay_time<@btime and data_type in (2) then val else 0 end ) as 'last_income', 
sum(case when pay_time<@btime and data_type not in (2) then  val else 0 end ) as 'last_expend',
2 as 'type'
from dc_report_cash_flow_log where pay_time>=@lastBtime and data_type in (2,3,5,8,9,10,11,12,13,14) and warehouse_code!='' and is_delete=0
GROUP BY bailun_sku,warehouse_code;


alter table dc_report_cash_flow_sku_group rename dc_report_cash_flow_sku_groupTemp; 
alter table dc_report_cash_flow_sku_group_temp rename dc_report_cash_flow_sku_group; 
alter table dc_report_cash_flow_sku_groupTemp rename dc_report_cash_flow_sku_group_temp; 
truncate table dc_report_cash_flow_sku_group_temp; 


update dc_report_cash_flow_sku_group  set current_balance=current_income+current_expend ,last_balance=last_income+last_expend;
", datePar, commandTimeout: 0);

                _connection.Execute(@" update dc_base_stock as t1,
( select bailun_sku,warehouse_code,
abs(sum(case when data_type not in (2)  then val else 0 end )) as 'pay_amount_30',
sum(case when data_type in (2)  then val else 0 end ) as 'income_amount_30'
from dc_report_cash_flow_log where occur_time >=@btime and is_delete=0 and data_type in (2,3,5,8,9,10,11,12,13,14) GROUP BY bailun_sku,warehouse_code ) as t2
set t1.pay_amount_30  =t2.pay_amount_30, t1.income_amount_30 = t2.income_amount_30
where t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code;
", new { btime = DateTime.Now.AddDays(-30).ToDayHome()},commandTimeout: 0);

                _connection.Execute(@" update dc_base_stock as t1,
dc_auto_turnover as t2
set t1.available_days =  (t1.usable_stock / if(t2.daily_weighted_sales<=0,1,t2.daily_weighted_sales))
where t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code;
", commandTimeout: 0);
            }
        }

        /// <summary>
        /// 刷新数据 （高频）
        /// </summary>
        public static void ResetDataHighFrequencyListting()
        {
            _connection.Execute(@" -- 刷新 订单 listing_status 映射表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_base_listing_status_sum_view_temp;

INSERT into dc_base_listing_status_sum_view_temp(`bailun_sku`,`ebay_uk_china`,`ebay_uk_abroad`,`ebay_us_china`,`ebay_us_abroad`,`wish_china`,`wish_abroad`,`amazon_uk_china`,`amazon_uk_abroad`,`amazon_us_china`,`amazon_us_abroad`,`aliexpress_china`,`aliexpress_abroad`,`walmart_china`,`walmart_abroad`,`sears_china`,`sears_abroad`,`ebay_au_china`,`ebay_au_abroad`,`ebay_de_china`,`ebay_de_abroad`,`ebay_es_china`,`ebay_es_abroad`,`ebay_fr_china`,`ebay_fr_abroad`,`ebay_it_china`,`ebay_it_abroad`,`amazon_au_china`,`amazon_au_abroad`,`amazon_de_china`,`amazon_de_abroad`,`amazon_es_china`,`amazon_es_abroad`,`amazon_fr_china`,`amazon_fr_abroad`,`amazon_it_china`,`amazon_it_abroad`,`amazon_jp_china`,`amazon_jp_abroad`,`amazon_na_china`,`amazon_na_abroad`,`amazon_ca_china`,`amazon_ca_abroad`) (
select 
t1.bailun_sku,
count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='uk' and t1.`status`=1 then 1 else null end ) as 'ebay_uk_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='uk' and t1.`status`=1 then 1 else null  end) as 'ebay_uk_abroad',
count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='us' and t1.`status`=1 then 1 else null  end) as 'ebay_us_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='us' and t1.`status`=1 then 1 else null  end) as 'ebay_us_abroad',

count(case when t1.platform='wish' and t1.localtion='国内' and t1.`status`=1 then 1 else null  end) as 'wish_china',
count(case when t1.platform='wish' and t1.localtion!='国内' and t1.`status`=1 then 1 else null  end) as 'wish_abroad',

count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='uk' and t1.`status`=1 then 1 else null  end) as 'amazon_uk_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='uk' and t1.`status`=1 then 1 else null  end) as 'amazon_uk_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='us' and t1.`status`=1 then 1 else null  end) as 'amazon_us_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='us' and t1.`status`=1 then 1 else null  end) as 'amazon_us_abroad',

count(case when t1.platform='aliexpress' and t1.localtion='国内'  and t1.`status`=1 then 1 else null  end) as 'aliexpress_china',
count(case when t1.platform='aliexpress' and t1.localtion!='国内' and t1.`status`=1 then 1 else null  end) as 'aliexpress_abroad',

count(case when t1.platform='walmart' and t1.localtion='国内' and t1.`status`=1 then 1 else null  end) as 'walmart_china',
count(case when t1.platform='walmart' and t1.localtion!='国内'  and t1.`status`=1 then 1 else null  end) as 'walmart_abroad',

count(case when t1.platform='sears' and t1.localtion='国内'  and t1.`status`=1 then 1 else null  end) as 'sears_china',
count(case when t1.platform='sears' and t1.localtion!='国内'  and t1.`status`=1 then 1 else null  end) as 'sears_abroad',

count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='au' and t1.`status`=1 then 1 else null end ) as 'ebay_au_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='au' and t1.`status`=1 then 1 else null  end) as 'ebay_au_abroad',
count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='de' and t1.`status`=1 then 1 else null  end) as 'ebay_de_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='de' and t1.`status`=1 then 1 else null  end) as 'ebay_de_abroad',
count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='es' and t1.`status`=1 then 1 else null end ) as 'ebay_es_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='es' and t1.`status`=1 then 1 else null  end) as 'ebay_es_abroad',
count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='fr' and t1.`status`=1 then 1 else null  end) as 'ebay_fr_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='fr' and t1.`status`=1 then 1 else null  end) as 'ebay_fr_abroad',
count(case when t1.platform='ebay' and t1.localtion='国内' and t1.web_site='it' and t1.`status`=1 then 1 else null  end) as 'ebay_it_china',
count(case when t1.platform='ebay' and t1.localtion!='国内' and t1.web_site='it' and t1.`status`=1 then 1 else null  end) as 'ebay_it_abroad',

count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='au' and t1.`status`=1 then 1 else null end ) as 'amazon_au_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='au' and t1.`status`=1 then 1 else null  end) as 'amazon_au_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='de' and t1.`status`=1 then 1 else null  end) as 'amazon_de_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='de' and t1.`status`=1 then 1 else null  end) as 'amazon_de_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='es' and t1.`status`=1 then 1 else null end ) as 'amazon_es_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='es' and t1.`status`=1 then 1 else null  end) as 'amazon_es_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='fr' and t1.`status`=1 then 1 else null  end) as 'amazon_fr_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='fr' and t1.`status`=1 then 1 else null  end) as 'amazon_fr_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='it' and t1.`status`=1 then 1 else null  end) as 'amazon_it_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='it' and t1.`status`=1 then 1 else null  end) as 'amazon_it_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='jp' and t1.`status`=1 then 1 else null  end) as 'amazon_jp_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='jp' and t1.`status`=1 then 1 else null  end) as 'amazon_jp_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='na' and t1.`status`=1 then 1 else null  end) as 'amazon_na_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='na' and t1.`status`=1 then 1 else null  end) as 'amazon_na_abroad',
count(case when t1.platform='amazon' and t1.localtion='国内' and t1.web_site='ca' and t1.`status`=1 then 1 else null  end) as 'amazon_ca_china',
count(case when t1.platform='amazon' and t1.localtion!='国内' and t1.web_site='ca' and t1.`status`=1 then 1 else null  end) as 'amazon_ca_abroad'


from dc_base_listing_status as t1
GROUP BY t1.bailun_sku
);

alter table dc_base_listing_status_sum_view rename dc_base_listing_status_sum_viewTemp; 
alter table dc_base_listing_status_sum_view_temp rename dc_base_listing_status_sum_view; 
alter table dc_base_listing_status_sum_viewTemp rename dc_base_listing_status_sum_view_temp; 
truncate table dc_base_listing_status_sum_view_temp;    ", commandTimeout: 0);
        }


        /// <summary>
        /// 刷新库存监控数据
        /// </summary>
        public static void ResetStockMonitorData()
        {
            try
            {
                _connection.Execute(@" -- 刷新 订单dc_auto_stock_monitor映射表
set session transaction isolation level read uncommitted;
start transaction;

-- 清空视图表的数据
Truncate table dc_auto_stock_monitor_temp;

INSERT into dc_auto_stock_monitor_temp(`bailun_sku`,`china_quantity_out_stock`,`not_china_quantity_out_stock`,
`guangzhou01_usable_stock`,`guangzhou01_transfer_stock`,`haiwai_uk_usable_stock`,`haiwai_uk_transfer`,
`haiwai_us_usable_stock`,`haiwai_us_transfer`,`fba_usable_stock`,`haiwai_usable_stock`,`guangzhou01_history_sevenday_sales`,
`haiwai_uk_history_sevenday_sales`,`haiwai_us_history_sevenday_sales`,`haiwai_fba_history_sevenday_sales`) (

	select 
t1.bailun_sku,
sum(case when t2.hq_type='国内仓' then t1.quantity_out_stock else 0 end ) as 'china_quantity_out_stock', -- 国内仓总缺货
sum(case when t2.hq_type not in ('国内仓','耗材仓') then t1.quantity_out_stock else 0 end ) as 'not_china_quantity_out_stock', -- 国外仓总缺货
sum(case when t1.warehouse_code='GZBLWH' then t3.usable_stock else 0 end) as 'guangzhou01_usable_stock', -- 广州01 可用库存
sum(case when t1.warehouse_code='GZBLWH' then t1.quantity_purchase + t1.quantity_transfer else 0 end) as 'guangzhou01_transfer_stock', -- 广州01 在途
sum(case when t2.hq_type='第三方仓库' and t2.area_id=6 then t3.usable_stock else 0 end) as 'haiwai_uk_usable_stock', -- 海外仓uk仓 可配库存
sum(case when t2.hq_type='第三方仓库' and t2.area_id=6 then t1.quantity_purchase + t1.quantity_transfer else 0 end) as 'haiwai_uk_transfer', -- 海外仓uk仓 在途库存
sum(case when t2.hq_type='第三方仓库' and t2.area_id=7 then t3.usable_stock else 0 end) as 'haiwai_us_usable_stock', -- 海外仓us仓 可配库存
sum(case when t2.hq_type='第三方仓库' and t2.area_id=7 then t1.quantity_purchase + t1.quantity_transfer else 0 end) as 'haiwai_us_transfer', -- 海外仓us仓 在途库存
sum(case when t2.hq_type='FBA仓' then t3.usable_stock + t1.quantity_purchase + t1.quantity_transfer else 0 end) as 'fba_usable_stock', -- fba 可用库存
sum(case when t2.hq_type in ('第三方仓库','FBA仓') then t3.usable_stock + t1.quantity_purchase + t1.quantity_transfer else 0 end) as 'haiwai_usable_stock', -- 海外仓可用库存
sum(case when t1.warehouse_code='GZBLWH' then t5.history_sevenday_sales else 0 end) as 'guangzhou01_history_sevenday_sales', -- 广州01 日均
sum(case when t2.hq_type='第三方仓库' and t2.area_id=6  then t5.history_sevenday_sales else 0 end) as 'haiwai_uk_history_sevenday_sales', -- 海外仓uk仓 日均
sum(case when t2.hq_type='第三方仓库' and t2.area_id=7  then t5.history_sevenday_sales else 0 end) as 'haiwai_us_history_sevenday_sales', -- 海外仓us仓 日均
sum(case when t2.hq_type='FBA仓'  then t5.history_sevenday_sales else 0 end) as 'haiwai_fba_history_sevenday_sales' -- fba 仓 日均
from dc_mid_transit as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_stock as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_base_sku as t4 on t1.bailun_sku = t4.bailun_sku
left join dc_auto_turnover as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
GROUP BY t1.bailun_sku


);

alter table dc_auto_stock_monitor rename dc_auto_stock_monitorTemp; 
alter table dc_auto_stock_monitor_temp rename dc_auto_stock_monitor; 
alter table dc_auto_stock_monitorTemp rename dc_auto_stock_monitor_temp; 
truncate table dc_auto_stock_monitor_temp;   ", commandTimeout: 0);
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                _connection.Insert<dc_task_error_log>(new dc_task_error_log
                {
                    date = DateTime.Now,
                    message = ex.Message,
                    stack_trace = ex.StackTrace,
                    task_name = "ResetStockMonitorData"
                });
            }
        }
        public static void AddError(dc_task_error_log data)
        {
            _connection.Insert(data);
        }

        public static List<dc_base_oms_order> GetOmsOrder(string bailun_order_ids)
        {
            if (string.IsNullOrWhiteSpace(bailun_order_ids))
            {
                return new List<dc_base_oms_order>();
            }
            return _connection.Query<dc_base_oms_order>(" select platform_type,transaction_id,origin_order_id,bailun_order_id from dc_base_oms_order where bailun_order_id in @bailun_order_ids ", new
            {
                bailun_order_ids = bailun_order_ids.Split(',').Distinct().ToArray()
            }).ToList(); ;
        }

        public static List<dc_base_oms_sku_label> GetSkuWebsiteSalesDetailed(bailun_sku_website_sales_detailed_search_dto search_data)
        {
            var sql = @" select t1.*,t2.label from dc_base_oms_sku_7 as t1 left join dc_auto_config_sku_label as t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search_data != null)
            {
                if (!string.IsNullOrWhiteSpace(search_data.platform_type))
                {
                    sql += " and t1.platform_type in @platform_type ";
                    parameters.Add("platform_type", search_data.platform_type.Split(','));
                }
                if (!string.IsNullOrWhiteSpace(search_data.website))
                {
                    sql += " and t1.website = @website ";
                    parameters.Add("website", search_data.website);
                }
                if (!string.IsNullOrWhiteSpace(search_data.label))
                {
                    sql += " and t2.label = @label ";
                    parameters.Add("label", search_data.label);
                }
                if(search_data.year>0 && search_data.month>0 && search_data.day > 0)
                {
                    var b_time = new DateTime(search_data.year,search_data.month,search_data.day,0,0,0);
                    sql += " and t1.pay_time >= @b_time and t1.pay_time <= @e_time ";
                    parameters.Add("b_time", b_time);
                    parameters.Add("e_time", b_time.ToString("yyyy-MM-dd 23:59:59"));
                }
            }
            return _connection.Query<dc_base_oms_sku_label>(sql, parameters).AsList();
        }
        public static List<bailun_sku_website_sales_dto> GetSkuWebsiteSales(bailun_sku_website_sales_search_dto search_data)
        {
            var tableName = "dc_base_oms_sku_7";
            if (search_data.btime != null || search_data.etime != null)
            {
                tableName = "dc_base_oms_sku";
            }
            var sql = $@" select t1.*,t2.label from {tableName} as t1 left join dc_auto_config_sku_label as t2 on t1.bailun_sku = t2.bailun_sku 
left join dc_base_warehouse as tdb on t1.warehouse_code = tdb.warehouse_code where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search_data != null)
            {
                if(search_data.btime!=null && search_data.etime!=null)
                {
                    sql += " and t1.paid_time>=@btime and t1.paid_time<=@etime ";
                    parameters.Add("btime", search_data.btime.Value.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", search_data.etime.Value.ToString("yyyy-MM-dd 23:59:59"));
                }
                if (!string.IsNullOrWhiteSpace(search_data.platform_type))
                {
                    if("amazon".Equals(search_data.platform_type, StringComparison.OrdinalIgnoreCase))
                    {
                        sql += " and t1.platform_type in @platform_types ";
                        parameters.Add("platform_types", new List<string> { "Amazon", "FBA" });
                    }
                    else
                    {
                        sql += " and t1.platform_type = @platform_type ";
                        parameters.Add("platform_type", search_data.platform_type);
                    }
                    
                }
                if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
                {
                    if (search_data.warehousetype == "国内仓")
                    {
                        sql += " and tdb.hq_type=@warehousetype ";
                        parameters.Add("warehousetype", "国内仓");
                    }
                    else
                    {
                        if ("amazon".Equals(search_data.platform_type, StringComparison.OrdinalIgnoreCase))
                        {
                            sql += " and tdb.hq_type in ('fba仓') ";
                        }
                        else
                        {
                            sql += " and tdb.hq_type not in ('国内仓') ";
                        }
                    }
                }
                if (search_data.warehousearea > 0)
                {
                    sql += " and tdb.area_id = @area_id ";
                    parameters.Add("area_id", search_data.warehousearea);
                }
                if (!string.IsNullOrWhiteSpace(search_data.warehouse_code))
                {
                    sql += " and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", search_data.warehouse_code);
                }
            }
            var oms_sku_list = _connection.Query<dc_base_oms_sku_label>(sql, parameters);

           
            List<bailun_sku_website_sales_dto> datas = new List<bailun_sku_website_sales_dto>();
            var website_list = oms_sku_list.GroupBy(s=>new { s.platform_type,s.website}).Where(s=> !"EBayMotors".Equals(s.Key.website,StringComparison.OrdinalIgnoreCase)).OrderBy(s=>s.Key.platform_type);
            if (search_data.has_sum == true)
            {
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "总计",
                    labels = new List<string> { "服装精准开发", "服装供应商推荐", "非服装精准开发", "无库普货推荐","美甲", "精油+美容" },
                    website = "总计",
                    website_show = "总计",
                    platform_type = "总计",
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });
            }
            else
            {
                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "服装精准开发",
                        labels = new List<string> { "服装精准开发" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "服装精准开发",
                    labels = new List<string> { "服装精准开发" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });

                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "服装供应商推荐",
                        labels = new List<string> { "服装供应商推荐" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "服装供应商推荐",
                    labels = new List<string> { "服装供应商推荐" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });

                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "服装汇总",
                        labels = new List<string> { "服装供应商推荐", "服装精准开发" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "服装汇总",
                    labels = new List<string> { "服装供应商推荐", "服装精准开发" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });

                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "非服装精准开发",
                        labels = new List<string> { "非服装精准开发" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "非服装精准开发",
                    labels = new List<string> { "非服装精准开发" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });

                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "无库普货推荐",
                        labels = new List<string> { "无库普货推荐" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "无库普货推荐",
                    labels = new List<string> { "无库普货推荐" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });
                
                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "美甲",
                        labels = new List<string> { "美甲" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "美甲",
                    labels = new List<string> { "美甲" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });
                
                foreach (var item in website_list)
                {
                    datas.Add(new bailun_sku_website_sales_dto
                    {
                        label = "精油+美容",
                        labels = new List<string> { "精油+美容" },
                        website = item.Key.website,
                        website_show = item.Key.platform_type + "-" + item.Key.website,
                        platform_type = item.Key.platform_type,
                        days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                    });
                }
                datas.Add(new bailun_sku_website_sales_dto
                {
                    label = "精油+美容",
                    labels = new List<string> { "精油+美容" },
                    website = "小计",
                    website_show = "小计",
                    platform_type = search_data.platform_type,
                    days = new List<bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto>()
                });

            }
            
            var b_time = DateTime.Now.AddDays(-1);
            var e_time = b_time.AddDays(-7);
            if(search_data.btime!=null && search_data.etime!=null)
            {
                 e_time = search_data.btime.Value;
                b_time = search_data.etime.Value;
            }
            var this_time = b_time;
            while (this_time>= e_time)
            {
                foreach (var item in datas)
                {
                    var temp_datas = oms_sku_list.Where(s=> s.pay_time!=null && s.pay_time.Year== this_time.Year && s.pay_time.Month == this_time.Month && s.pay_time.Day== this_time.Day);
                    if (!"总计".Equals(item.website))
                    {
                        temp_datas = temp_datas.Where(s => item.labels.Contains(s.label));
                        if (!"小计".Equals(item.website))
                        {
                            temp_datas = temp_datas.Where(s => s.platform_type == item.platform_type && s.website == item.website);
                        }
                    }
                   
                    item.days.Add(new bailun_sku_website_sales_dto.bailun_sku_website_sales_date_dto {
                        year = this_time.Year,
                        month = this_time.Month,
                        day = this_time.Day,
                        count = temp_datas.Sum(s=>s.bailun_sku_quantity_ordered),
                        sum = temp_datas.Sum(s => s.bailun_sku_quantity_ordered * s.amount_sales * s.order_to_usd_exchange_rate)
                    });
                }

                this_time = this_time.AddDays(-1);
            }

            return datas;
        }

        public static List<supplier_sales_dto> SupplierSalesList(supplier_sales_search_dto m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var list = new List<supplier_sales_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();
                string sql = "";
                sql = @"
select 
t2.suppliers_id,
t2.suppliers_name,
sum(case when t1.create_time>=@time1 and t1.create_time<=@time2 then  t1.bailun_sku_quantity_ordered else 0 end) as 'yesterday_sales_count', -- 昨日销量
sum(case when t1.create_time>=@time1 and t1.create_time<=@time2 then  ROUND(t1.bailun_sku_quantity_ordered * t1.amount_sales * t1.seller_order_exchange_rate,2) else 0 end) as 'yesterday_sales_amount', 
sum(case when t1.create_time>=@time3 and t1.create_time<=@time2 then  t1.bailun_sku_quantity_ordered else 0 end) as 'sales_count_7', -- 7日销量
sum(case when t1.create_time>=@time3 and t1.create_time<=@time2 then  ROUND(t1.bailun_sku_quantity_ordered * t1.amount_sales * t1.seller_order_exchange_rate,2)  else 0 end) as 'sales_amount_7', -- 7日销量
t3.yesterday_purchase_count,
t3.yesterday_purchase_amount,
t3.purchase_count_7,
t3.purchase_amount_7
from dc_base_oms_sku_7 as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join (
	select tp_1.supplier_id,
	sum(case when tp_1.create_time>=@time1 and tp_1.create_time<=@time2  then  tp_1.count else 0 end) as 'yesterday_purchase_count', -- 昨日销量
	sum(case when tp_1.create_time>=@time1 and tp_1.create_time<=@time2 then  ROUND(tp_1.count * tp_1.price,2) else 0 end) as 'yesterday_purchase_amount', 
	sum(case when tp_1.create_time>=@time4 then tp_1.count else 0 end) as 'purchase_count_7', -- 7日采购
	sum(case when tp_1.create_time>=@time4 then ROUND(tp_1.count * tp_1.price,2)  else 0 end) as 'purchase_amount_7' -- 日采购
	from dc_base_purchase_7 as tp_1
	GROUP BY tp_1.supplier_id
) as t3 on t2.suppliers_id = t3.supplier_id
where t2.buyer_name = '赵美聪'

  ";
                string count_sql = @"
select 
1
from dc_base_oms_sku_7 as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
where t2.buyer_name = '赵美聪'

  ";

                parameters.Add("time1",DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("time2", DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 23:59:59"));
                parameters.Add("time3", DateTime.Now.AddDays(-8).ToString("yyyy-MM-dd 00:00:00"));
                parameters.Add("time4", DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd 00:00:00"));
                if (!string.IsNullOrWhiteSpace(m.supplier_name))
                {
                    sql += " and t2.suppliers_name=@suppliers_name ";
                    count_sql += " and t2.suppliers_name=@suppliers_name ";
                    parameters.Add("supplier_name", m.supplier_name);
                }

                if (m.has_sum == true)
                {
                    total = 0;
                }
                else
                {
                    sql += " GROUP BY t2.suppliers_id ";
                    count_sql += " GROUP BY t2.suppliers_id ";
                    total = _connection.ExecuteScalar<int>(count_sql, parameters);

                    //设置默认排序字段
                    if (string.IsNullOrWhiteSpace(sort)) sort = "yesterday_sales_count";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        sql += " order by " + sort;

                        if (!string.IsNullOrEmpty(order))
                        {
                            sql += " " + order;
                        }
                        else
                        {
                            sql += " desc";
                        }
                    }
                    sql += " limit " + offset + "," + limit;
                }

                
                var obj = _connection.Query<supplier_sales_dto>(sql, parameters, buffered: false, commandTimeout: 0);
                return obj.AsList();

            }
            catch (Exception ex)
            {
                return list;
            }
        }

        public static List<bailun_sku_goods_dto> GetGoods(bailun_sku_goods_search_dto search_data, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var sql = @" select t1.*,t2.product_inner_code,t2.bailun_category_name,t2.sku_title_cn  from dc_base_oms_sku_7 as t1 left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search_data != null)
            {
                if (!string.IsNullOrWhiteSpace(search_data.product_inner_code))
                {
                    sql += " and t2.product_inner_code = @product_inner_code ";
                    parameters.Add("product_inner_code", search_data.product_inner_code);
                }
                if (!string.IsNullOrWhiteSpace(search_data.platform_type))
                {
                    sql += " and t1.platform_type = @platform_type ";
                    parameters.Add("platform_type", search_data.platform_type);
                }
            }
            var oms_sku_list = _connection.Query<dc_base_oms_sku_goods_dto>(sql, parameters);


            List<bailun_sku_goods_dto> datas = new List<bailun_sku_goods_dto>();
         
            var website_list = oms_sku_list.GroupBy(s => new { s.platform_type, s.website }).OrderBy(s => s.Key.platform_type).ToList();
            if (search_data.has_sum == true)
            {
                total = 0;
                datas.Add(new bailun_sku_goods_dto
                {
                    product_inner_code ="总计",
                    bailun_category_name = "",
                    sku_title_cn = "",
                    websites = new List<bailun_sku_goods_dto.bailun_sku_goods_date_dto>()
                });
            }
            else
            {
                var product_inner_code_group_list = oms_sku_list.GroupBy(s => new { s.product_inner_code });
                total = product_inner_code_group_list.Count();
                var product_inner_code_group_list_show = product_inner_code_group_list.Take(limit).Skip(offset);
                foreach (var item in product_inner_code_group_list_show)
                {
                    datas.Add(new bailun_sku_goods_dto
                    {
                        product_inner_code = item.Key.product_inner_code,
                        bailun_category_name = item.Max(g => g.bailun_category_name),
                        sku_title_cn = item.Max(g => g.sku_title_cn),
                        websites = new List<bailun_sku_goods_dto.bailun_sku_goods_date_dto>()
                    });
                }
            }
            

            var now = DateTime.Now;
            var yesterday_time = DateTime.Now.AddDays(-1);
            var time_7 = DateTime.Parse( DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd 00:00:00"));
            foreach (var website_item in website_list)
            {
                foreach (var item in datas)
                {
                    var temp_datas = oms_sku_list.Where(s => s.platform_type == website_item.Key.platform_type && s.website == website_item.Key.website); ;
                    if (!"总计".Equals(item.product_inner_code))
                    {
                        temp_datas = temp_datas.Where(s => s.product_inner_code == item.product_inner_code);
                    }
                    var yesterday_datas = temp_datas.Where(s => s.create_time.Year == yesterday_time.Year && s.create_time.Month == yesterday_time.Month && s.create_time.Day == yesterday_time.Day);
                    var datas_7 = temp_datas.Where(s => s.create_time >= time_7);
                    var sum_count_7 = temp_datas.Sum(s=>s.bailun_sku_quantity_ordered); // 7日总销量
                    item.websites.Add(new bailun_sku_goods_dto.bailun_sku_goods_date_dto
                    {
                        platform_type = website_item.Key.platform_type,
                        website = website_item.Key.website,
                        yesterday_count = yesterday_datas.Sum(s=>s.bailun_sku_quantity_ordered),
                        yesterday_amount = yesterday_datas.Sum(s => s.bailun_sku_quantity_ordered * s.amount_sales * s.order_to_usd_exchange_rate),
                        amount_7 = datas_7.Sum(s => s.bailun_sku_quantity_ordered * s.amount_sales * s.order_to_usd_exchange_rate),
                        yesterday_average_7 = sum_count_7 / 7
                    });
                }
            }

            return datas;
        }

        public static List<platform_type_website_dto> GetPlatformWebsite()
        {
            return _connection.Query<platform_type_website_dto>("select platform_type,website from dc_base_oms_sku_7 group by platform_type,website").AsList();
        }

        /// <summary>
        /// 安全库存
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static Page<quantity_safe_inventory_dto> GetInventoryStock(quantity_safe_inventory_search_dto search_data)
        {
            string sql = $@" select t2.warehouse_type as 'hq_type',(case when t2.warehouse_type='半成品仓' then t4.warehouse_name else t3.warehouse_name end )'warehouse_name', t2.warehouse_code,sum( LEAST(ifnull(t2.quantity_safe_inventory,0),t2.usable_stock)) as 'real_quantity_safe_inventory',
sum(t2.usable_stock) as 'usable_stock',
count(DISTINCT bailun_sku) as 'sku_count',
sum(ifnull(t2.quantity_safe_inventory, 0)) as 'quantity_safe_inventory',
sum(LEAST(ifnull(t2.quantity_safe_inventory, 0), t2.usable_stock) * ifnull(t2.unit_price, 0)) as 'real_quantity_safe_inventory_amount'
from dc_base_full_stock as t2
left join dc_base_warehouse as t3 on t2.warehouse_code = t3.warehouse_code
left join dc_base_wip_warehouse as t4 on t2.warehouse_code = t4.warehouse_code
where 1=1
 ";
            string sqlCount = $@" select count(DISTINCT t2.warehouse_code) from dc_base_full_stock as t2 left join dc_base_warehouse as t3 on t2.warehouse_code = t3.warehouse_code where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search_data != null)
            {
                if (!string.IsNullOrWhiteSpace(search_data.warehouse_code))
                {
                    sql += " and t2.warehouse_code=@warehouse_code ";
                    sqlCount += " and t2.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", search_data.warehouse_code);
                }
                else
                {
                    if (search_data.warehousearea > 0)
                    {
                        sql += " and t3.area_id = @area_id ";
                        sqlCount += " and t3.area_id = @area_id ";
                        parameters.Add("area_id", search_data.warehousearea);
                    }
                    if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
                    {
                        sql += " and t2.warehouse_type = @hq_type ";
                        sqlCount += " and t2.warehouse_type = @hq_type ";
                        parameters.Add("hq_type", search_data.warehousetype);
                    }
                }
            }

            if (!search_data.is_sum)
            {
                sql += " GROUP BY t2.warehouse_code ";
            }
            var pageDatas = _connection.Page<quantity_safe_inventory_dto>(sql, search_data, parameters,isCount:false);
            if (!search_data.is_sum)
            {
                pageDatas.TotalItems = connectionHelper._connection.QueryFirstOrDefault<int?>(sqlCount, parameters) ?? 0;
                pageDatas.TotalPages = (int)Math.Ceiling(pageDatas.TotalItems * 1.0 / search_data.rows);
            }
            return pageDatas;
        }

        /// <summary>
        /// 查看半成品库存
        /// </summary>
        /// <returns></returns>
        public static Page<dc_base_wip_stock_dto> GetWipStockPage(dc_base_wip_stock_search_dto search)
        {
            var sql = " select t1.*,( LEAST(ifnull(t1.quantity_safe_inventory,0),t1.usable_stock)) as 'real_quantity_safe_inventory',t2.warehouse_name from dc_base_wip_stock as t1 left join dc_base_wip_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                sql += " and t1.bailun_sku like @bailun_sku ";
                parameters.Add("bailun_sku", search.bailun_sku);
            }
            if (!string.IsNullOrWhiteSpace(search.warehouse_code))
            {
                sql += " and t1.warehouse_code like @warehouse_code ";
                parameters.Add("warehouse_code", search.warehouse_code);
            }
            return  _connection.Page<dc_base_wip_stock_dto>(sql, search, parameters);
        }

        public static List<platform_type_website_dto> PlatformtypeWebsiteList(string platform_type)
        {
            return _connection.Query<platform_type_website_dto>(" select DISTINCT platform_type,web_site as  'website' from dc_report_cash_flow_group_day  ").AsList();
        } 
        
        public static List<string> BrandList()
        {
            return _connection.Query<string>(" SELECT DISTINCT brand FROM `dc_base_sku`  ").AsList();
        }
    }
}
