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;
using AutoTurnOver.Models.auto;

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.QuerySingleOrDefault<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,
t1.shipping_deadline_real
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.QuerySingleOrDefault<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.QuerySingleOrDefault<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)
        {
            if (platform == "wish")
            {
                return WishChinaShortagePush(platform, is_all);
            }
            else
            {
                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',
t_sp.title as 'supplier_holiday_time'
from (select * from  dc_mid_transit where warehouse_code in ( 'GZBLWH','BLGZ03','YWWH01' ) )  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' ,'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','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
left join ( select * from dc_auto_monitor_sku_type where warehouse_type='国内仓') as t7 on t1.bailun_sku = t7.bailun_sku
left join holiday_supplier_view as t_sp on t2.suppliers_id = t_sp.supplier_id
where
t_sp.supplier_id is not null or
( t1.warehouse_code in ('GZBLWH','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 t7.bailun_sku is null)
	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 ) ";
                }
                // ebay  的 10月新品 +普货全量推
                if (platform == "ebay")
                {
                    if (is_all)
                    {
                        // 全量推 只推普货
                        sql += " and t2.develop_time>='2020-10-01' and  t2.buyer_name not in ('张莹霞','赵美聪','甄杰靖','张莹霞') and t1.warehouse_code in ('GZBLWH') ";
                    }
                    else
                    {
                        // 增量推，那部分数据要剔除
                        sql += " and !(t2.develop_time>='2020-10-01' and  t2.buyer_name not in ('张莹霞','赵美聪','甄杰靖','张莹霞') and t1.warehouse_code in ('GZBLWH') ) ";
                    }
                }
                shortage_list.AddRange(_connection.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' ,
t_sp.title as 'supplier_holiday_time'
from ( select * from  dc_base_stock where warehouse_code in ( 'GZBLWH','BLGZ03','YWWH01' )  )  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','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' ,'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
left join ( select * from dc_auto_monitor_sku_type where warehouse_type='国内仓') as t7 on t1.bailun_sku = t7.bailun_sku
left join holiday_supplier_view as t_sp on t3.suppliers_id = t_sp.supplier_id
where
t_sp.supplier_id is not null or
t1.usable_stock<=0 and t1.bailun_sku!=''  
and ( t1.warehouse_code in ('GZBLWH','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 t7.bailun_sku is null)
	end
  )  -- 速卖通的必须要停止监控才推送
and IFNULL(t4.sum_usable_stock,0)<=0  -- 无库存
and  ( @platform='ebay' or 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 ) ";
                }
                // ebay  的 10月新品 +普货全量推
                if (platform == "ebay")
                {
                    if (is_all)
                    {
                        // 全量推 只推普货
                        no_library_sql += " and t3.develop_time>='2020-10-01' and  t3.buyer_name not in ('张莹霞','赵美聪','甄杰靖','张莹霞') and t1.warehouse_code in ('GZBLWH') ";
                    }
                    else
                    {
                        // 增量推，那部分数据要剔除
                        no_library_sql += " and !(t3.develop_time>='2020-10-01' and  t3.buyer_name not in ('张莹霞','赵美聪','甄杰靖','张莹霞') and t1.warehouse_code in ('GZBLWH') ) ";
                    }
                }
                shortage_list.AddRange(_connection.Query<dc_auto_shortage_push>(no_library_sql, new { platform = platform }, commandTimeout: 0));

                return shortage_list;
            }


        }

        /// <summary>
        /// 国内仓缺货计算 (wish)
        /// </summary>
        /// <param name="is_all">是否全量数据</param>
        /// <returns></returns>
        public static List<dc_auto_shortage_push> WishChinaShortagePush(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',
t_sp.title as 'supplier_holiday_time'
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 dc_auto_config_sku_warehouse as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join holiday_supplier_view as t_sp on t2.suppliers_id = t_sp.supplier_id
where
(
( t1.warehouse_code in ('GZBLWH','BLGZ03','YWWH01') ) -- 义务仓 只要停止监控 或者停售就推送
and ( t2.`status`=1 or t6.`status`=1 )
and  (ifnull(t1.quantity_unshipped,0) - ifnull(t5.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)
            {
                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 ) ";
            }
            sql += " ) or t_sp.supplier_id is not null  ";
            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' ,
t_sp.title as 'supplier_holiday_time'
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 dc_auto_config_sku_warehouse as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join holiday_supplier_view as t_sp on t3.suppliers_id = t_sp.supplier_id
where
(
t1.usable_stock<=0 and t1.bailun_sku!=''  
and ( t1.warehouse_code in ('GZBLWH','BLGZ03','YWWH01')) 
and  ( t3.`status`=1 or t6.`status`=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 ) ";
            }
            no_library_sql += " ) or t_sp.supplier_id is not null  ";
            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',
t_sp.title as 'supplier_holiday_time'
from (select * from  dc_mid_transit where warehouse_code in @warehouse_codes ) 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
left join holiday_supplier_view as t_sp on t2.suppliers_id = t_sp.supplier_id
where
t_sp.supplier_id is not null or
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',
t_sp.title as 'supplier_holiday_time'
from ( select * from dc_base_stock where warehouse_code in @warehouse_codes ) 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 
left join holiday_supplier_view as t_sp on t2.suppliers_id = t_sp.supplier_id
where
t_sp.supplier_id is not null or
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)<=0 -- (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,last_return_goods_date=now() 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.QuerySingleOrDefault<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>
        /// 查询特定sku的去年历史销量
        /// </summary>
        /// <param name="product_type"></param>
        /// <returns></returns>
        public static dynamic GetSalesTrendYear(string product_type)
        {
            var btime = DateTime.Parse(DateTime.Now.AddYears(-1).ToString("yyyy-01-01 00:00:00"));
            var etime = DateTime.Parse(btime.ToString("yyyy-12-31 23:59:59"));

            // 查询基础数据
            var base_datas = _connection.Query<dc_report_sales_trend_year>(" select * from dc_report_sales_trend_year where `date`>=@btime and `date`<=@etime and warehouse_type!='耗材仓' ", new
            {
                btime = btime,
                etime = etime
            });
            var months = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
            var legendGroups = base_datas.GroupBy(s => new { s.country_code, s.warehouse_type }).ToList();
            return new
            {
                title = "年度销量趋势表",
                legendDatas = legendGroups.Select(s => $"{s.Key.warehouse_type}-{s.Key.country_code}"),
                xAxisDatas = months.Select(s => $"{s}月"),
                seriesDatas = legendGroups.Select(s => new
                {
                    name = $"{s.Key.warehouse_type}-{s.Key.country_code}",
                    type = "line",
                    data = months.Select(m => s.Where(b => b.date.Month == m).Sum(su => su.sales))
                })
            };
        }

        /// <summary>
        /// 获取产品分类
        /// </summary>
        /// <returns></returns>
        public static List<product_type_desc_dto> GetProductTypeList()
        {
            return _connection.Query<product_type_desc_dto>("select DISTINCT product_type,product_type_desc from dc_report_sales_trend_year where  product_type>0").ToList();
        }

        /// <summary>
        /// 回货速改推送
        /// </summary>
        public static List<dc_return_goods_push> ReturnGoodsPush(string platform)
        {
            if (platform == "wish")
            {
                return ReturnGoodsPushWish(platform);
            }
            List<string> rule1 = new List<string> { "GBYKD", "JZHYBLGC", "MDBLWYT", "MXBLWYT", "BLUSGDC", "GBBMHBL", "GB4PXBL", "DEBLDG", "USGCBL", "USHWBL", "US4PXBL", "AUWYTBL", "AU4PXBL", "GBWYTUK", "GBLQST" };
            List<string> ch_rule1 = new List<string>() { "GZBLWH", "BLGZ03" };

            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)
end
)  as 'stocks',
t2.buyer_name,
t_sp.title as 'supplier_holiday_time'
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 @ch_rule1
    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 @ch_rule1
    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 @rule1
    -- 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 @rule1
        -- 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
left join holiday_supplier_view as t_sp on t2.suppliers_id = t_sp.supplier_id
where 
t_sp.supplier_id is null and
t1.platform=@platform 
and 
(
case 
    when t_db.hq_type not in ( '国内仓' ) and t2.buyer_name in ('张莹霞','张莹霞1') then ifnull(tnc1.usable_stock,0) > 1 
	when t_db.hq_type not in ( '国内仓' ) then ifnull(tnc1.usable_stock,0) > 0 
	else  ifnull(tc2.sum_unshipped_quantity,0) < ifnull(tc1.sum_usable_stock,0) 
end 
)

";
            if (platform == "ebay" && false)
            {
                sql += "  and  ( t1.has_return_goods=0 or ( t_stock.update_time>t1.last_return_goods_date and t_db.hq_type not in ( '国内仓' ) ) ) ";
            }
            else
            {
                sql += " and  t1.has_return_goods=0  ";
            }
            shortage_list.AddRange(_connection.Query<dc_return_goods_push>(sql, new { platform = platform, rule1 = rule1, ch_rule1 = ch_rule1 }, commandTimeout: 0));

            return shortage_list;

        }

        public static List<dc_return_goods_push> ReturnGoodsPushWish(string platform)
        {
            List<string> rule1 = new List<string> { "USGCBL", "BLUSGDC", "US4PXBL", "GB4PXBL" };
            List<string> ch_rule1 = new List<string>() { "GZBLWH", "BLGZ03", "YWWH01" };

            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(t_stock.usable_stock,0)  - ifnull(t3.quantity_unshipped,0)
else ifnull(tnc1.usable_stock,0)
end
)  as 'stocks',
t2.buyer_name,
t_sp.title as 'supplier_holiday_time'
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 '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 @rule1
    -- 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 @rule1
        -- 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
left join holiday_supplier_view as t_sp on t2.suppliers_id = t_sp.supplier_id
where 
t_sp.supplier_id is null and
t1.platform=@platform and t1.has_return_goods=0
and 
(
case 
    when t_db.hq_type not in ( '国内仓' ) and t2.buyer_name in ('张莹霞','张莹霞1') then ifnull(tnc1.usable_stock,0) > 1 
	when t_db.hq_type not in ( '国内仓' ) then ifnull(tnc1.usable_stock,0) > 0 
	else  ifnull(t3.quantity_unshipped,0) < ifnull(t_stock.usable_stock,0) 
end 
)
";
            shortage_list.AddRange(conn.Query<dc_return_goods_push>(sql, new { platform = platform, rule1 = rule1 }, 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 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_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 in ('张莹霞','黄境洁','古艳洋1')  then '美甲'
 when t1.buyer_name='张莹霞1'  then '精油+美容'
 when t1.buyer_name in ('赵美聪','甄杰靖') and t1.source = 1 then '服装精准开发'
 when t1.buyer_name in ('赵美聪','甄杰靖') then '服装供应商推荐'
 when t1.buyer_name not in ('赵美聪','甄杰靖') 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_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(@" -- 刷新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"
                });
            }

        }

        /// <summary>
        ///  刷新oms的缺货数据
        /// </summary>
        public static void ResetOmsOutofstock()
        {
            try
            {
                var datas = _connection.Query<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',
  t1.`shipping_deadline_real`
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'", commandTimeout: 0).ToList();

                _connection.Execute(@"
-- 清空视图表的数据
Truncate table dc_base_oms_sku_outofstock_temp;
 ", commandTimeout: 0);

                foreach (var item in datas)
                {
                    _connection.Insert(item);
                }

                _connection.Execute(@"
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);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }

        }

        /// <summary>
        /// 全量刷新入库记录
        /// </summary>
        public static void ResetStockRecord()
        {
            _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);
        }

        /// <summary>
        /// 刷新全量库存表
        /// </summary>
        public static void ResetFullStock()
        {
            _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);
        }

        /// <summary>
        /// 刷新oms最近7天的数据
        /// </summary>
        public static void ResetOms7()
        {
            _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);
        }



        /// <summary>
        /// 刷新安全库存
        /// </summary>
        public static void ResetInventory()
        {
            _connection.Execute(@"update dc_auto_jit_inventory as t1,
dc_auto_turnover as t2
set t1.safe_stock = t2.history_fourteenday_sales * 2,date = now()
where t1.bailun_sku =t2.bailun_sku and t1.warehouse_code =t2.warehouse_code", commandTimeout: 0);
        }
        //刷新上周的数据
        public static void ResetLastweekData()
        {
            _connection.Execute(@" -- 刷新 sku 上周销量
-- 清空视图表的数据
Truncate table dc_base_lastweek_data_temp;
 ", commandTimeout: 0);
            var datas = _connection.Query<dc_base_lastweek_data_temp>(@"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 
where t1.has_delete=0 and t1.company_id=1 and t2.bailun_order_status!='Canceled' 
and ( t1.bailun_order_status!='CantHandle') 
and t2.paid_time>=@btime and t2.paid_time<@etime
GROUP BY t1.bailun_sku,t1.warehouse_code ", new { btime = DateTime.Now.AddDays(-7).GetWeekFirstDayMon().ToDayHome(), etime = DateTime.Now.AddDays(-7).GetWeekLastDaySun().ToDayEnd() }).ToList();

            if (datas != null)
            {
                foreach (var item in datas)
                {
                    _connection.Insert<dc_base_lastweek_data_temp>(item);
                }
            }

            _connection.Execute(@" 
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; ", commandTimeout: 0);
        }

        public static void ResetCashFlowData()
        {
            _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 set pay_amount_30=0,income_amount_30=0;
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 set available_days=0;
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.warehouse_code))
                {
                    sql += " and t1.warehouse_code = @warehouse_code ";
                    parameters.Add("warehouse_code", search_data.warehouse_code);
                }
                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<bailun_sku_warehouse_code_dto> GetSkuWarehouseSales(bailun_sku_website_sales_search_dto search_data)
        {
            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 tableName = "dc_base_oms_sku_7";
            if (search_data.btime != null || search_data.etime != null)
            {
                tableName = "dc_base_oms_sku";
            }
            var sql = $@" select t1.*,ifnull(t2.label,'未知') as  'label',tdb.warehouse_name 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 
 t1.bailun_order_status != 'Canceled'
        and t1.has_fba_s = 0
        and t1.has_delete = 0
        and t1.has_scalp = 0
        -- and t1.has_buyer_remark = 0
        -- and t1.has_platsku_remark = 0
        and t1.has_innersale = 0 ";

            var stock_sql = @" select * from dc_daily_stock_warehouse as t1 where 1=1  ";
            DynamicParameters parameters = new DynamicParameters();
            DynamicParameters parameters_sql = new DynamicParameters();
            if (b_time != null && e_time != null)
            {
                sql += " and t1.paid_time<=@btime and t1.paid_time>=@etime ";
                parameters.Add("btime", b_time.ToString("yyyy-MM-dd 23:59:59"));
                parameters.Add("etime", e_time.ToString("yyyy-MM-dd 00:00:00"));

                stock_sql += " and t1.record_time<=@btime and t1.record_time>=@etime ";
                parameters_sql.Add("btime", b_time.ToString("yyyy-MM-dd 23:59:59"));
                parameters_sql.Add("etime", e_time.ToString("yyyy-MM-dd 00:00:00"));
            }
            if (search_data != null)
            {

                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))
                {
                    sql += " and tdb.hq_type=@warehousetype ";
                    parameters.Add("warehousetype", search_data.warehousetype);
                }
                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);
            var stock_list = _connection.Query<dc_daily_stock_warehouse>(stock_sql, parameters_sql, commandTimeout: 0);


            List<bailun_sku_warehouse_code_dto> datas = new List<bailun_sku_warehouse_code_dto>();
            var warehouse_code_list = oms_sku_list.GroupBy(s => new { s.warehouse_name, s.warehouse_code }).OrderBy(s => s.Key.warehouse_name);
            if (search_data.has_sum == true)
            {
                datas.Add(new bailun_sku_warehouse_code_dto
                {
                    label = "总计",
                    labels = new List<string> { "服装精准开发", "服装供应商推荐", "非服装精准开发", "无库普货推荐", "美甲", "精油+美容", "未知" },
                    warehouse_name = "总计",
                    warehouse_code = "",
                    days = new List<bailun_sku_warehouse_code_dto.bailun_sku_warehouse_code_date_dto>()
                });
            }
            else
            {
                foreach (var item in warehouse_code_list)
                {
                    datas.Add(new bailun_sku_warehouse_code_dto
                    {
                        label = "美甲",
                        labels = new List<string> { "美甲" },
                        warehouse_name = item.Key.warehouse_name,
                        warehouse_code = item.Key.warehouse_code,
                        days = new List<bailun_sku_warehouse_code_dto.bailun_sku_warehouse_code_date_dto>()
                    });
                }

                foreach (var item in warehouse_code_list)
                {
                    datas.Add(new bailun_sku_warehouse_code_dto
                    {
                        label = "精油+美容",
                        labels = new List<string> { "精油+美容" },
                        warehouse_name = item.Key.warehouse_name,
                        warehouse_code = item.Key.warehouse_code,
                        days = new List<bailun_sku_warehouse_code_dto.bailun_sku_warehouse_code_date_dto>()
                    });
                }

                foreach (var item in warehouse_code_list)
                {
                    datas.Add(new bailun_sku_warehouse_code_dto
                    {
                        label = "普货",
                        labels = new List<string> { "服装精准开发", "服装供应商推荐", "非服装精准开发", "无库普货推荐", "未知" },
                        warehouse_name = item.Key.warehouse_name,
                        warehouse_code = item.Key.warehouse_code,
                        days = new List<bailun_sku_warehouse_code_dto.bailun_sku_warehouse_code_date_dto>()
                    });
                }

            }


            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.warehouse_name))
                    {
                        temp_datas = temp_datas.Where(s => item.labels.Contains(s.label) && s.warehouse_name == item.warehouse_name);
                    }

                    item.days.Add(new bailun_sku_warehouse_code_dto.bailun_sku_warehouse_code_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),
                        stock = stock_list.Where(v => v.warehouse_code == item.warehouse_code && v.record_time.Year == this_time.Year && v.record_time.Month == this_time.Month && v.record_time.Day == this_time.Day).Sum(v => v.stock)
                    });
                }

                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 in ('赵美聪','甄杰靖')

  ";
                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 in ('赵美聪','甄杰靖')

  ";

                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.QuerySingleOrDefault<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();
        }

        /// <summary>
        /// 库存每周备份
        /// </summary>
        public static void StockWeekBackUp()
        {
            var btime = DateTime.Now.AddDays(-7).GetWeekFirstDayMon().ToDayHome();
            var etime = btime.GetWeekLastDaySun().ToDayEnd();
            var week_tag = $"{btime.ToString("MM-dd")} ~ {etime.ToString("MM-dd")}";
            _connection.Execute(@"
delete from dc_report_stock_week where week_tag=@week_tag;
insert into dc_report_stock_week(`stock`,`week_tag`,`warehouse_code`,`bailun_sku`,`first_day_date`,`web_site`,`sku_tag`,`stock_amount`,`is_order`,`sales`,`sales_amount`,`cost_amount`)
(
select 
t1.stock,
t1.week_tag,
t1.warehouse_code,
t1.bailun_sku,
t1.first_day_date,
t1.web_site,
t1.sku_tag,
t1.stock_amount,
1 as 'is_order',
t1.sales,
t1.sales_amount,
t1.cost_amount
from (select 
t3.usable_stock as 'stock',
@week_tag as 'week_tag',
t1.warehouse_code,
t1.bailun_sku,
@btime as 'first_day_date',
t2.area_name as 'web_site',
'' as 'sku_tag',
t3.usable_stock * ifnull(t4.unit_price,0) 'stock_amount',
t2.hq_type,
sum(t1.bailun_sku_quantity_ordered) as 'sales',
sum(t1.bailun_sku_quantity_ordered * t1.amount_sales * t1.seller_order_exchange_rate) as 'sales_amount',
sum(t1.cost_product * t1.bailun_sku_quantity_ordered ) as 'cost_amount'
from dc_base_oms_sku as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_stock 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
where t1.paid_time>=@btime and t1.paid_time<=@etime 
and t1.bailun_order_status != 'Canceled'
                     and t1.bailun_sku_quantity_picked>0
                   -- and t1.bailun_interception_status in ('None', 'Failed','Part')
                   and t1.warehouse_code != ''
                   and t1.platform_type != 'FBA'
                   and t1.company_id = 1
                   and t1.has_delete = 0
                   and t1.has_scalp = 0
                   and t1.has_buyer_remark = 0
                   and t1.has_platsku_remark = 0
                   and t1.has_fba_s = 0
                   and t1.has_innersale = 0
                   and t1.paid_time >= '2019-03-01 12:00:00'
group by t1.warehouse_code,t1.bailun_sku  ) as t1
where t1.hq_type='第三方仓库'
);
insert ignore into dc_report_stock_week(`stock`,`week_tag`,`warehouse_code`,`bailun_sku`,`first_day_date`,`web_site`,`sku_tag`,`stock_amount`,`is_order`,`sales`,`sales_amount`,`cost_amount`)
(
select 
t1.usable_stock as 'stock',
@week_tag as 'week_tag',
t1.warehouse_code,
t1.bailun_sku,
@btime as 'first_day_date',
t2.area_name as 'web_site',
'' as 'sku_tag',
t1.usable_stock * ifnull(t3.unit_price,0) 'stock_amount',
0 as 'is_order',
0 as 'sales',
0 as 'sales_amount',
0 as 'cost_amount'
from dc_base_stock 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 t2.hq_type='第三方仓库' and 
( 
	t1.usable_stock>0
)
);
update dc_report_stock_week as t1,
dc_base_sku as t2,
dc_base_puhuo as t3 
set t1.sku_tag = (
case 
when t2.buyer_name in ('古艳洋1','张莹霞','张莹霞1','黄镜洁') then '美容SKU'
when t3.bailun_sku is not null then '普货-精准开发'
when t2.develop_time>='2020-05-31' then '普货-精准开发'
else '清货'
end
)
where t1.bailun_sku = t2.bailun_sku and t1.bailun_sku = t3.bailun_sku and t1.week_tag=@week_tag;
", new { btime, etime, week_tag }, commandTimeout: 0);
        }

        public static List<report_stock_week_view_dto> ReportStockWeekView(report_stock_week_view_search_dto search)
        {
            var btime = DateTime.Now.AddDays(-(7 * 4)).GetWeekFirstDayMon().ToDayHome();
            var etime = btime.AddDays(7 * 24).GetWeekLastDaySun().ToDayEnd();
            if (search.btime != null)
            {
                btime = search.btime.Value;
            }
            if (search.etime != null)
            {
                etime = search.etime.Value;
            }
            var datas = _connection.Query<dc_report_stock_week>(" select * from dc_report_stock_week where first_day_date>=@btime and first_day_date <=@etime ", new { btime, etime });

            List<report_stock_week_view_dto> r_datas = new List<report_stock_week_view_dto>();

            var datas_web_site_group = datas.GroupBy(s => s.web_site).ToList();

            foreach (var websiteGroupItem in datas_web_site_group.OrderByDescending(s => s.Key))
            {
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "出单sku数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "不出单数量sku占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "不出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "不出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });

                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "普货-精准开发-出单sku数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "普货-精准开发-不出单数量sku占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "普货-精准开发-出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "普货-精准开发-不出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "普货-精准开发-出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "普货-精准开发-不出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });


                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "清货-出单sku数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "清货-不出单数量sku占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "清货-出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "清货-不出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "清货-出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "清货-不出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });


                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "美容SKU-出单sku数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "美容SKU-不出单数量sku占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "美容SKU-出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "美容SKU-不出单sku库存数量占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "美容SKU-出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "美容SKU-不出单sku库存金额占比", weeks = new List<report_stock_week_view_dto.week>() { } });

                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "总SKU数", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "总销售数量", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "总销售金额(RMB)", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "总销售成本(RMB)", weeks = new List<report_stock_week_view_dto.week>() { } });

                var this_date = btime.GetWeekFirstDayMon().ToDayHome();
                while (this_date <= etime)
                {
                    var b_thie_date = this_date.ToDayHome();
                    var e_thie_date = b_thie_date.GetWeekLastDaySun().ToDayEnd();

                    var this_datas = websiteGroupItem.Where(s => s.first_day_date >= b_thie_date && s.first_day_date <= e_thie_date).ToList();
                    var sum_val = this_datas.Count();
                    var puhuo_sum_val = this_datas.Where(s => s.sku_tag == "普货-精准开发").Count();
                    var qinhuo_sum_val = this_datas.Where(s => s.sku_tag == "清货").Count();
                    var meirong_sum_val = this_datas.Where(s => s.sku_tag == "美容SKU").Count();
                    var sum_stock_val = this_datas.Sum(s => s.stock);
                    var puhuo_sum_stock_val = this_datas.Where(s => s.sku_tag == "普货-精准开发").Sum(s => s.stock);
                    var qinhuo_sum_stock_val = this_datas.Where(s => s.sku_tag == "清货").Sum(s => s.stock);
                    var meirong_sum_stock_val = this_datas.Where(s => s.sku_tag == "美容SKU").Sum(s => s.stock);
                    var sum_stock_amount_val = this_datas.Sum(s => s.stock_amount);
                    var puhuo_sum_stock_amount_val = this_datas.Where(s => s.sku_tag == "普货-精准开发").Sum(s => s.stock_amount);
                    var qinhuo_sum_stock_amount_val = this_datas.Where(s => s.sku_tag == "清货").Sum(s => s.stock_amount);
                    var meirong_sum_stock_amount_val = this_datas.Where(s => s.sku_tag == "美容SKU").Sum(s => s.stock_amount);
                    var sum_sales_val = this_datas.Sum(s => s.sales);
                    var sum_sales_amount_val = this_datas.Sum(s => s.sales_amount);
                    var sum_cost_amount_amount_val = this_datas.Sum(s => s.cost_amount);
                    var chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 1).Count();
                    var puhuo_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "普货-精准开发").Count();
                    var qinhuo_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "清货").Count();
                    var meirong_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "美容SKU").Count();

                    var bu_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 0).Count();
                    var puhuo_bu_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "普货-精准开发").Count();
                    var qinhuo_bu_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "清货").Count();
                    var meirong_bu_chudan_sku_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "美容SKU").Count();
                    var chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 1).Sum(s => s.stock);
                    var puhuo_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "普货-精准开发").Sum(s => s.stock);
                    var qinhuo_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "清货").Sum(s => s.stock);
                    var meirong_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "美容SKU").Sum(s => s.stock);
                    var bu_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 0).Sum(s => s.stock);
                    var puhuo_bu_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "普货-精准开发").Sum(s => s.stock);
                    var qinhuo_bu_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "清货").Sum(s => s.stock);
                    var meirong_bu_chudan_sku_stock_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "美容SKU").Sum(s => s.stock);
                    var chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1).Sum(s => s.stock_amount);
                    var puhuo_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "普货-精准开发").Sum(s => s.stock_amount);
                    var qinhuo_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "清货").Sum(s => s.stock_amount);
                    var meirong_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "美容SKU").Sum(s => s.stock_amount);
                    var bu_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 0).Sum(s => s.stock_amount);
                    var puhuo_bu_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "普货-精准开发").Sum(s => s.stock_amount);
                    var qinhuo_bu_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "清货").Sum(s => s.stock_amount);
                    var meirong_bu_chudan_sku_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 0 && s.sku_tag == "美容SKU").Sum(s => s.stock_amount);


                    var jinzhun_sku_val = (decimal)this_datas.Where(s => s.sku_tag == "普货-精准开发").Count();
                    var jinzhun_sku_order_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "普货-精准开发").Count();
                    var jinzhun_sku_stock_amount_val = (decimal)this_datas.Where(s => s.sku_tag == "普货-精准开发").Sum(s => s.stock_amount);
                    var jinzhun_sku_order_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "普货-精准开发").Sum(s => s.stock_amount);
                    var qinhuo_sku_val = (decimal)this_datas.Where(s => s.sku_tag == "清货").Count();
                    var qinhuo_sku_order_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "清货").Count();
                    var qinhuo_sku_stock_amount_val = (decimal)this_datas.Where(s => s.sku_tag == "清货").Sum(s => s.stock_amount);
                    var qinhuo_sku_order_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "清货").Sum(s => s.stock_amount);
                    var meirong_sku_val = (decimal)this_datas.Where(s => s.sku_tag == "美容SKU").Count();
                    var meirong_sku_order_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "美容SKU").Count();
                    var meirong_sku_stock_amount_val = (decimal)this_datas.Where(s => s.sku_tag == "美容SKU").Sum(s => s.stock_amount);
                    var meirong_sku_order_stock_amount_val = (decimal)this_datas.Where(s => s.is_order == 1 && s.sku_tag == "美容SKU").Sum(s => s.stock_amount);

                    #region 普货，精准开发

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "普货-精准开发-出单sku数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = puhuo_chudan_sku_val,
                        ratio = puhuo_chudan_sku_val.Division(puhuo_sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "普货-精准开发-不出单数量sku占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = puhuo_bu_chudan_sku_val,
                        ratio = puhuo_bu_chudan_sku_val.Division(puhuo_sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "普货-精准开发-出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = puhuo_chudan_sku_stock_val,
                        ratio = puhuo_chudan_sku_stock_val.Division(puhuo_sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "普货-精准开发-不出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = puhuo_bu_chudan_sku_stock_val,
                        ratio = puhuo_bu_chudan_sku_stock_val.Division(puhuo_sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "普货-精准开发-出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = puhuo_chudan_sku_stock_amount_val,
                        ratio = puhuo_chudan_sku_stock_amount_val.Division(puhuo_sum_stock_amount_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "普货-精准开发-不出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = puhuo_bu_chudan_sku_stock_amount_val,
                        ratio = puhuo_bu_chudan_sku_stock_amount_val.Division(puhuo_sum_stock_amount_val)
                    });

                    #endregion

                    #region 美容SKU

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "美容SKU-出单sku数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = meirong_chudan_sku_val,
                        ratio = meirong_chudan_sku_val.Division(meirong_sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "美容SKU-不出单数量sku占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = meirong_bu_chudan_sku_val,
                        ratio = meirong_bu_chudan_sku_val.Division(meirong_sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "美容SKU-出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = meirong_chudan_sku_stock_val,
                        ratio = meirong_chudan_sku_stock_val.Division(meirong_sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "美容SKU-不出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = meirong_bu_chudan_sku_stock_val,
                        ratio = meirong_bu_chudan_sku_stock_val.Division(meirong_sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "美容SKU-出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = meirong_chudan_sku_stock_amount_val,
                        ratio = meirong_chudan_sku_stock_amount_val.Division(meirong_sum_stock_amount_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "美容SKU-不出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = meirong_bu_chudan_sku_stock_amount_val,
                        ratio = meirong_bu_chudan_sku_stock_amount_val.Division(meirong_sum_stock_amount_val)
                    });

                    #endregion

                    #region 清货

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "清货-出单sku数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = qinhuo_chudan_sku_val,
                        ratio = qinhuo_chudan_sku_val.Division(qinhuo_sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "清货-不出单数量sku占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = qinhuo_bu_chudan_sku_val,
                        ratio = qinhuo_bu_chudan_sku_val.Division(qinhuo_sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "清货-出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = qinhuo_chudan_sku_stock_val,
                        ratio = qinhuo_chudan_sku_stock_val.Division(qinhuo_sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "清货-不出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = qinhuo_bu_chudan_sku_stock_val,
                        ratio = qinhuo_bu_chudan_sku_stock_val.Division(qinhuo_sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "清货-出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = qinhuo_chudan_sku_stock_amount_val,
                        ratio = qinhuo_chudan_sku_stock_amount_val.Division(qinhuo_sum_stock_amount_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "清货-不出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = qinhuo_bu_chudan_sku_stock_amount_val,
                        ratio = qinhuo_bu_chudan_sku_stock_amount_val.Division(qinhuo_sum_stock_amount_val)
                    });

                    #endregion


                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "出单sku数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = chudan_sku_val,
                        ratio = chudan_sku_val.Division(sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "不出单数量sku占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = bu_chudan_sku_val,
                        ratio = bu_chudan_sku_val.Division(sum_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = chudan_sku_stock_val,
                        ratio = chudan_sku_stock_val.Division(sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "不出单sku库存数量占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = bu_chudan_sku_stock_val,
                        ratio = bu_chudan_sku_stock_val.Division(sum_stock_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = chudan_sku_stock_amount_val,
                        ratio = chudan_sku_stock_amount_val.Division(sum_stock_amount_val)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "不出单sku库存金额占比").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = bu_chudan_sku_stock_amount_val,
                        ratio = bu_chudan_sku_stock_amount_val.Division(sum_stock_amount_val)
                    });


                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "总SKU数").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = sum_val,
                        ratio = 1
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "总销售数量").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = sum_sales_val,
                        ratio = 1M
                    });
                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "总销售金额(RMB)").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = sum_sales_amount_val,
                        ratio = 1M
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "总销售成本(RMB)").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = sum_cost_amount_amount_val,
                        ratio = 1M
                    });

                    this_date = this_date.AddDays(7);
                }
            }



            return r_datas;
        }


        /// <summary>
        /// 导出海外仓明细
        /// </summary>
        /// <returns></returns>
        public static List<dc_report_stock_week_warehouse_dto> ExportTableReportStockWeekViewSkus(string web_site, string tag, string date_tag)
        {
            var datas = _connection.Query<dc_report_stock_week_warehouse_dto>(" select t1.*,t2.warehouse_name from dc_report_stock_week as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where t1.web_site=@web_site and t1.week_tag =@week_tag ", new { web_site, week_tag = date_tag }).ToList();
            if (tag == "出单sku数量占比")
            {
                datas = datas.Where(s => s.is_order == 1).ToList();
            } else if (tag == "不出单数量sku占比")
            {
                datas = datas.Where(s => s.is_order == 0).ToList();
            }
            return datas;
        }
        public static List<report_stock_week_view_dto> ReportPurchaseWeekView(report_stock_week_view_search_dto search)
        {
            var btime = DateTime.Now.AddDays(-(7 * 4)).GetWeekFirstDayMon().ToDayHome();
            var etime = btime.AddDays(7 * 24).GetWeekLastDaySun().ToDayEnd();
            if (search.btime != null)
            {
                btime = search.btime.Value;
            }
            if (search.etime != null)
            {
                etime = search.etime.Value;
            }
            var datas = _connection.Query<dc_report_purchase_week>(" select * from dc_report_purchase_week where first_day_date>=@btime and first_day_date <=@etime ", new { btime, etime });

            List<report_stock_week_view_dto> r_datas = new List<report_stock_week_view_dto>();

            var datas_web_site_group = datas.GroupBy(s => s.web_site).ToList();

            foreach (var websiteGroupItem in datas_web_site_group)
            {
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周新品下单sku个数", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周新品下单总数量", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周新品下sku金额", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周新品已调走sku个数", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周新品已调走总数量", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周新品已调走sku金额", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周补货下单sku个数", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周补货下单总数量", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周补货下单sku金额", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周补货调走sku个数", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周补货调走总数量", weeks = new List<report_stock_week_view_dto.week>() { } });
                r_datas.Add(new report_stock_week_view_dto { web_site = websiteGroupItem.Key, tag = "上周补货调走sku金额", weeks = new List<report_stock_week_view_dto.week>() { } });

                var this_date = btime.GetWeekFirstDayMon().ToDayHome();
                while (this_date <= etime)
                {
                    var b_thie_date = this_date.ToDayHome();
                    var e_thie_date = b_thie_date.GetWeekLastDaySun().ToDayEnd();

                    var this_datas = websiteGroupItem.Where(s => s.first_day_date >= b_thie_date && s.first_day_date <= e_thie_date).ToList();

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周新品下单sku个数").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 1 && s.is_new == 1).Count()
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周新品下单总数量").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 1 && s.is_new == 1).Sum(s => s.quantity_purchase)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周新品下sku金额").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 1 && s.is_new == 1).Sum(s => s.amount_paid)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周新品已调走sku个数").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 2 && s.is_new == 1).Count()
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周新品已调走总数量").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 2 && s.is_new == 1).Sum(s => s.quantity_purchase)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周新品已调走sku金额").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 2 && s.is_new == 1).Sum(s => s.amount_paid)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周补货下单sku个数").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 1 && s.is_new == 0).Count()
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周补货下单总数量").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 1 && s.is_new == 0).Sum(s => s.quantity_purchase)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周补货下单sku金额").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 1 && s.is_new == 0).Sum(s => s.amount_paid)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周补货调走sku个数").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 2 && s.is_new == 0).Count()
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周补货调走总数量").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 2 && s.is_new == 0).Sum(s => s.quantity_purchase)
                    });

                    r_datas.SingleOrDefault(s => s.web_site == websiteGroupItem.Key && s.tag == "上周补货调走sku金额").weeks.Add(new report_stock_week_view_dto.week
                    {
                        tag = $"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}",
                        val = this_datas.Where(s => s.type == 2 && s.is_new == 0).Sum(s => s.amount_paid)
                    });

                    this_date = this_date.AddDays(7);
                }
            }



            return r_datas;
        }


        public static List<string> ReportStockWeekViewGetWeekList(report_stock_week_view_search_dto search)
        {
            var weeks = new List<string>();
            var btime = DateTime.Now.AddDays(-(7 * 4)).GetWeekFirstDayMon().ToDayHome();
            var etime = btime.AddDays(7 * 4).GetWeekLastDaySun().ToDayEnd();
            if (search.btime != null)
            {
                btime = search.btime.Value;
            }
            if (search.etime != null)
            {
                etime = search.etime.Value;
            }
            var this_date = btime.GetWeekFirstDayMon().ToDayHome();
            while (this_date <= etime)
            {
                var b_thie_date = this_date.ToDayHome();
                var e_thie_date = b_thie_date.GetWeekLastDaySun().ToDayEnd();
                weeks.Add($"{b_thie_date.ToString("MM-dd")} ~ {e_thie_date.ToString("MM-dd")}");
                this_date = this_date.AddDays(7);
            }

            return weeks;
        }



        /// <summary>
        /// 库存-采购每周备份
        /// </summary>
        public static void PurchaseWeekBackUp()
        {
            _connection.Execute(@"-- 刷新首次采购时间
set session transaction isolation level read uncommitted;
start transaction;
update dc_base_stock as t1,
(select t1.bailun_sku,t1.warehouse_into_code as 'warehouse_code',min(t1.create_time) as 'min_time' from dc_base_purchase_details as t1 
where `status`!=-1
GROUP BY t1.bailun_sku,t1.warehouse_into_code) as t2
set t1.first_purchase_date = t2.min_time
where  t1.bailun_sku = t2.bailun_sku and  t1.warehouse_code=t2.warehouse_code;
", commandTimeout: 0);

            _connection.Execute(@"-- 刷新首次调拨时间

update dc_base_stock as t1,
(select t_ps.sku as 'bailun_sku',t_p.targetwareno as 'warehouse_code',min(t_p.creationtime) as 'min_time' from dc_base_transfer_info as t_p 
left join dc_base_transfer_info_skus as t_ps on t_p.`dataid` = t_ps.dataid
where t_p.`isdeleted`=0 
GROUP BY t_ps.sku,t_p.targetwareno) as t2
set t1.first_trans_date = t2.min_time
where  t1.bailun_sku = t2.bailun_sku and  t1.warehouse_code=t2.warehouse_code;
", commandTimeout: 0);

            var btime = DateTime.Now.AddDays(-7).GetWeekFirstDayMon().ToDayHome();
            var etime = btime.GetWeekLastDaySun().ToDayEnd();
            var eonthFirstDate = DateTime.Now.AddMonths(-1).GetMonthFirstDay();
            var week_tag = $"{btime.ToString("MM-dd")} ~ {etime.ToString("MM-dd")}";
            _connection.Execute(@"

delete from dc_report_purchase_week where week_tag=@week_tag;

insert into dc_report_purchase_week(`week_tag`,`type`,`warehouse_code`,`bailun_sku`,`stock`,`unit_price`,`is_new`,`quantity_purchase`,`amount_paid`,`web_site`,`first_day_date`)
(
select 
@week_tag as 'week_tag',
2 as 'type',
t_p.targetwareno as 'warehouse_code',
t_ps.sku as 'bailun_sku',
t1.usable_stock as 'stock',
t3.unit_price,
(
case
	when t_p.creationtime <= t1.first_trans_date then 1
	else 0
	end
) as 'is_new',
sum(t_ps.deliverycount) as 'quantity_purchase',
sum(t_ps.deliverycount * t3.unit_price) as 'amount_paid',
t2.area_name as 'web_site',
@btime as 'first_day_date'
from dc_base_transfer_info as t_p
left join dc_base_transfer_info_skus as t_ps on t_p.`dataid` = t_ps.dataid
left join dc_base_stock as t1 on t1.bailun_sku =t_ps.sku and t1.warehouse_code = t_p.targetwareno
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 ( t2.hq_type='第三方仓库' ) and t3.buyer_name not in('张莹霞','张莹霞1','黄镜洁','古艳洋1')
and t_p.`isdeleted`=0 and t_p.creationtime>=@btime and t_p.creationtime<=@etime
GROUP BY t_ps.sku ,t_p.targetwareno
);


insert into dc_report_purchase_week(`week_tag`,`type`,`warehouse_code`,`bailun_sku`,`stock`,`unit_price`,`is_new`,`quantity_purchase`,`amount_paid`,`web_site`,`first_day_date`)
(
select 
@week_tag as 'week_tag',
1 as 'type',
t_p.warehouse_into_code as 'warehouse_code',
t_p.bailun_sku,
t1.usable_stock as 'stock',
t3.unit_price,
(
case
	when t_p.create_time <= t1.first_purchase_date then 1
	else 0
	end
) as 'is_new',
sum(t_p.quantity_purchase) as 'quantity_purchase',
sum(t_p.amount_paid) as 'amount_paid',
t2.area_name as 'web_site',
@btime as 'first_day_date'
from dc_base_purchase_details as t_p 
left join dc_base_stock as t1 on t1.bailun_sku =t_p.bailun_sku and t1.warehouse_code = t_p.warehouse_into_code
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 ( t2.hq_type='第三方仓库'  ) and t3.buyer_name not in('张莹霞','张莹霞1','黄镜洁','古艳洋1')
and t_p.`status`!=-1 and t_p.create_time>=@btime and t_p.create_time<=@etime
GROUP BY t_p.bailun_sku ,t_p.warehouse_into_code
);


insert into dc_report_purchase_week(`week_tag`,`type`,`warehouse_code`,`bailun_sku`,`stock`,`unit_price`,`is_new`,`quantity_purchase`,`amount_paid`,`web_site`,`first_day_date`)
(
select 
@week_tag as 'week_tag',
1 as 'type',
t_p.warehouse_from_code as 'warehouse_code',
t_p.bailun_sku,
t1.usable_stock as 'stock',
t3.unit_price,
(
case
	when t_p.create_time <= t1.first_purchase_date then 1
	else 0
	end
) as 'is_new',
sum(t_p.quantity_purchase) as 'quantity_purchase',
sum(t_p.amount_paid) as 'amount_paid',
t2.area_name as 'web_site',
@btime as 'first_day_date'
from dc_base_purchase_details as t_p 
left join dc_base_stock as t1 on t1.bailun_sku =t_p.bailun_sku and t1.warehouse_code = t_p.warehouse_from_code
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 ( t_p.warehouse_from_code='GZBLWH') and t3.buyer_name not in('张莹霞','张莹霞1','黄镜洁','古艳洋1')
and t_p.`status`!=-1 and t_p.create_time>=@btime and t_p.create_time<=@etime
GROUP BY t_p.bailun_sku ,t_p.warehouse_into_code
);
", new { btime, etime, week_tag, eonthFirstDate }, commandTimeout: 0);
        }

        /// <summary>
        /// 刷新每日库存汇总
        /// </summary>
        public static void ResetDailyStockWarehouse()
        {
            _connection.Execute(@"
insert into dc_daily_stock_warehouse (warehouse_code,stock,record_time)
(
select warehouse_code,sum(usable_stock)  as 'stock', @record_time as 'record_time'
from dc_base_stock as t1
where t1.usable_stock>0
group by warehouse_code
)", new { record_time = DateTime.Now.AddDays(-1) });
        }

        /// <summary>
        /// 计算年度销量增长趋势
        /// </summary>
        public static void CalculationSalesTrend()
        {
            var btime = DateTime.Now.AddYears(-1).ToString("yyyy-01-01 00:00:00");
            var datas = _connection.Query<dc_report_sales_trend_year>(@"
select 
sum(t1.bailun_sku_quantity_ordered) as 'sales',
t2.area_name as 'country_code',
t2.hq_type as 'warehouse_type',
date_format(paid_time, '%Y-%m') as 'date_str',
paid_time as 'date',
1 as 'year_growth_rate',
now() as 'create_date',
now() as 'update_date'
from dc_base_oms_sku as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code =t2.warehouse_code
where t1.bailun_order_status != 'Canceled'
        and t1.has_delete = 0
        and t1.has_scalp = 0
        and t1.has_buyer_remark = 0
        and t1.has_platsku_remark = 0 
				and t1.paid_time>=@btime --  and t1.paid_time<'2020-01-10'
				GROUP BY t2.hq_type,t2.area_name,date_format(paid_time, '%Y-%m')
", new { btime }, commandTimeout: 0).ToList();

            foreach (var item in datas)
            {
                if (item.warehouse_type != null && item.country_code != null)
                {
                    var oldData = _connection.QuerySingleOrDefault<dc_report_sales_trend_year>(" select * from dc_report_sales_trend_year where date_str=@date_str and warehouse_type=@warehouse_type and country_code=@country_code ", new
                    {
                        country_code = item.country_code,
                        warehouse_type = item.warehouse_type,
                        date_str = item.date_str
                    });

                    if (oldData != null)
                    {
                        item.year_growth_rate = oldData.year_growth_rate;
                        item.id = oldData.id;
                    }

                    var lastMonthDateStr = item.date.AddMonths(-1).ToString("yyyy-MM");
                    var lastMonthData = datas.FirstOrDefault(s => s.country_code == item.country_code && s.warehouse_type == item.warehouse_type && s.date_str == lastMonthDateStr);
                    if (lastMonthData != null && lastMonthData.sales > 0)
                    {
                        item.month_growth_rate = item.sales / lastMonthData.sales;
                    }
                    else
                    {
                        item.month_growth_rate = 1;
                    }

                    if (item.id > 0)
                    {
                        _connection.Update(item);
                    }
                    else
                    {
                        _connection.Insert(item);
                    }
                }

            }
        }

        /// <summary>
        /// 设置默认头程方式
        /// </summary>
        public static void ResetHeadType()
        {
            var btime = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd 00:00:00");
            var stocks = _connection.Query<bailun_sku_dto>(" select t1.bailun_sku,t1.warehouse_code,t2.hq_type from dc_base_stock as t1 left join dc_base_warehouse as t2 on t1.warehouse_code =t2.warehouse_code where t1.gmt_create>=@btime ", new { btime = btime }).ToList();
            foreach (var item in stocks)
            {
                if ("fba仓".Equals(item.hq_type, StringComparison.OrdinalIgnoreCase) || "第三方仓库".Equals(item.hq_type, StringComparison.OrdinalIgnoreCase))
                {
                    // 查询是否存在
                    var count = _connection.QuerySingleOrDefault<int>(" select count(1) from dc_base_head_transport_config where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new
                    {
                        item.bailun_sku,
                        item.warehouse_code
                    });
                    if (count <= 0)
                    {
                        _connection.Insert(new dc_base_head_transport_config
                        {
                            bailun_sku = item.bailun_sku,
                            warehouse_code = item.warehouse_code,
                            create_date = DateTime.Now,
                            create_user = "system",
                            delete_user = "",
                            is_delete = 0,
                            logistics_code = "",
                            logistics_name = "",
                            head_transport = "fba仓".Equals(item.hq_type, StringComparison.OrdinalIgnoreCase) ? "海运" : "空运"
                        });
                    }
                }

            }
        }
        public class bailun_sku_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
            public string hq_type { get; set; }
        }

        /// <summary>
        /// 刷新最快入库天数
        /// </summary>
        public static void ResetFastestStorage()
        {
            var datas = _connection.Query<fastest_storage_dto>(@"select 
t1.warehouse_code,
t1.bailun_sku,
t3.`code` as 'country_code',
t1.supplier_delivery,
t1.payment_before_delivery,
t1.inspection_delivery
from dc_auto_turnover as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code =t2.warehouse_code
left join dc_base_country as t3 on t2.area_name = t3.`name`
where t2.hq_type='fba仓'").ToList();
            foreach (var item in datas)
            {
                var data = new dc_auto_fastest_storage() {
                    bailun_sku = item.bailun_sku,
                    warehouse_code = item.warehouse_code,
                    purchase_days = (item.supplier_delivery + item.payment_before_delivery + item.inspection_delivery),
                    gmt_update_date = DateTime.Now,
                    country_code = item.country_code,
                    trans_days = 15
                };
                var lms_avgs = ApiUtility.GetLmsOrderTransferAvg(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto {transfer_type  =3, country = item.country_code});
                if (lms_avgs != null && lms_avgs.Count>=1)
                {
                    data.trans_days = (int)Math.Round( lms_avgs.Min(s => s.avg_sign_days + s.avg_put_days),0);
                }
                data.id = _connection.QueryFirstOrDefault<int?>(" select id from dc_auto_fastest_storage where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ",new {
                    warehouse_code = item.warehouse_code,
                    bailun_sku = item.bailun_sku
                })??0;
                if (data.id <= 0)
                {
                    _connection.Insert(data);
                }
                else
                {
                    _connection.Update(data);
                }

            }
        }

        public class fastest_storage_dto
        {
            public string warehouse_code { get; set; }
            public string bailun_sku { get; set; }
            public string country_code { get; set; }
            public int supplier_delivery { get; set; }
            public int payment_before_delivery { get; set; }
            public int inspection_delivery { get; set; }
        }


        /// <summary>
        /// 刷新调拨预计到货
        /// </summary>
        public static void ResetTransExpectArrivaltime()
        {
            // 查询真实的调拨在途
            var datas = _connection.Query<dc_base_trans_expectarrivaltime_temp>(@"select 
t2.expectarrivaltime as 'expectarrivaltime',
t1.sku as 'bailun_sku',
t2.targetwareno as 'warehouse_code',
t1.deliverycount - t1.storagecount as 'count',
ifnull(t2.`code`,'') as 'transfer_order_id',
ifnull(t2.purchasecode,'') as 'purchase_id'
from dc_base_transfer_info_skus as t1 
left join dc_base_transfer_info as t2 on t1.dataid =t2.dataid
left join dc_base_warehouse as t3 on t2.targetwareno = t3.warehouse_code
where t2.isdeleted=0 and t2.stateid not in (5,11,12)
and t1.deliverycount > t1.storagecount
and t3.hq_type='fba仓' and t2.expectarrivaltime >='1991-01-01'").ToList();


            // 计算国内入库，还没有调拨单的数据
            var temp_p_datas = _connection.Query<dc_base_trans_expectarrivaltime_temp>(@"select 
t1.purchase_id, 
t1.bailun_sku,
t1.warehouse_into_code as 'warehouse_code' ,
t1.quantity_inbound as 'count',
'' as 'transfer_order_id',
'1991-01-01' as 'expectarrivaltime'
from dc_base_purchase_details as t1 
left join dc_base_warehouse as t2 on t1.warehouse_into_code = t2.warehouse_code
where  t1.quantity_purchase<=t1.quantity_inbound and t1.`status` not in (-1) and t2.hq_type='fba仓'
and t1.create_time>=@btime ",new { btime = DateTime.Now.AddMonths(-3)}).ToList();

            // 查询已经有调拨的采购单
            foreach (var item in temp_p_datas.GroupBy(s=>s.purchase_id))
            {
                // 查询是否已经调拨
                var exists_trans = _connection.QueryFirstOrDefault<int>("  select count(1) from  dc_base_transfer_info as t_tran where t_tran.purchasecode like @purchasecode ",new { purchasecode  = $"%{item.Key}%"});
                if (exists_trans <= 0)
                {
                    foreach (var itemSku in item.AsEnumerable())
                    {
                        itemSku.transfer_order_id = "中转仓入库，带调拨。 aims 虚拟在途";
                        // 查询历史调拨天数
                        var tran_days = _connection.QueryFirstOrDefault<int?>(" select transfer_delivery+transfer_bale_delivery+abroad_inbound_delivery from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku  = itemSku.bailun_sku , warehouse_code = itemSku.warehouse_code })??0;
                        itemSku.expectarrivaltime = DateTime.Now.AddDays(tran_days);
                        datas.Add(itemSku);
                    }
                }
            }

            _connection.Execute(" TRUNCATE table dc_base_trans_expectarrivaltime_temp; ");
            foreach (var item in datas)
            {
                _connection.Insert(item);
            }
            _connection.Execute(@"
alter table dc_base_trans_expectarrivaltime rename dc_base_trans_expectarrivaltimeTemp; 
alter table dc_base_trans_expectarrivaltime_temp rename dc_base_trans_expectarrivaltime; 
alter table dc_base_trans_expectarrivaltimeTemp rename dc_base_trans_expectarrivaltime_temp; 
truncate table dc_base_trans_expectarrivaltime_temp;   
");

          

        }
    }
}
