using AutoTurnOver.Models;
using AutoTurnOver.Models.Report;
using Dapper;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                return obj.AsList();

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


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

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

                }

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

                if (limit > 10000 || m.isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<int>(countSql, parameters, commandTimeout: 0);

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

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

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

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

        /// <summary>
        /// 缺货推送
        /// </summary>
        public  static List<dc_auto_shortage_push> ShortagePush()
        {
            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',
(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 ('AU4PXBL','BLGZ03','DYGXC','GB4PXBL','GBBLJW','GBYKD','GZBLWH','QYBLZZ','US4PXBL','USGCBL')
and ( 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
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 )";
            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',
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 ( t_db.hq_type!='国内仓'  or t3.buyer_name not in ('张莹霞','张莹霞1','黄镜洁','赵美聪') )
and t1.warehouse_code in ('AU4PXBL','BLGZ03','DYGXC','GB4PXBL','GBBLJW','GBYKD','GZBLWH','QYBLZZ','US4PXBL','USGCBL')
and (
	case 
	when t_db.hq_type='国内仓' then t2.quantity_purchase
	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_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  or
	(
		case 
		when t_stock_w.hq_type='国内仓' then t_stock_t.quantity_purchase
		else t_stock_t.quantity_transfer
		end
	)>0
	)
) -- 同属性仓库 （是否国内） 有库存，就不算 0库存
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   )";
            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);
            }
        }

        

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

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

                }

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

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

                if (limit > 10000 || m.isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<int>(countSql, parameters, commandTimeout: 0);

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

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

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

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

        /// <summary>
        /// 回货速改推送
        /// </summary>
        public static List<dc_return_goods_push> ReturnGoodsPush()
        {
            var conn = _connection;
            var shortage_list = new List<dc_return_goods_push>();
            // 回货速改推送
            string sql = @"select 
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'
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 1=1 
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, commandTimeout: 0));

            return shortage_list;

        }

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

        /// <summary>
        /// 刷新缺货数据
        /// </summary>
        public static void ResetOutofstock()
        {
            try
            {
                _connection.Execute(@" -- 刷新 订单items映射表
set session transaction isolation level read uncommitted;
start transaction;

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

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

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

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

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

INSERT into dc_base_oms_sku_outofstock_temp (
	select 
t1.*,( 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.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.bailun_interception_status not in ('Success','Part') and t1.platform_type!='FBA'
);

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

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

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

INSERT into china_warehouse_stock_temp(`bailun_sku`,`sum_usable_stock`,`warehouse_codes`) (
	SELECT
	`t1`.`bailun_sku` AS `bailun_sku`,
	sum( `t1`.`usable_stock` + t1.occupy_stock ) AS `sum_usable_stock`,
	group_concat( ( CASE WHEN ( `t1`.`usable_stock` + t1.occupy_stock > 0 ) THEN `t1`.`warehouse_code` ELSE '' END ) SEPARATOR ' | ' ) AS `warehouse_codes` 
FROM
	( `dc_base_stock` `t1` LEFT JOIN `dc_base_warehouse` `t2` ON ( ( `t1`.`warehouse_code` = `t2`.`warehouse_code` ) ) ) 
WHERE
	( ( `t2`.`hq_type` = '国内仓' ) AND ( `t1`.`warehouse_code` IN ( 'GZBLWH', 'QYBLZZ' ) ) ) 
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' )   
	 GROUP BY
	`t1`.`bailun_sku`
);

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

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

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

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

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

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

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

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

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

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

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

Truncate table dc_auto_purchase_ontheway_temp;

INSERT into dc_auto_purchase_ontheway_temp(`bailun_sku`,`warehouse_code`,`purchase_id`,`estimated_arrival_time`,`deliver_name`,`count`) (
	
SELECT
 tb1.bailun_sku AS bailun_sku,
 tb1.warehouse_code AS warehouse_code,
 tb1.purchase_id AS purchase_id,
 tb1.estimated_arrival_time AS estimated_arrival_time,
 tb1.deliver_name,
 sum( ( tb1.count - IFNULL( tb2.count, 0 ) ) ) AS count 
FROM
 (
 SELECT
  bailun_sku,
  warehouse_code,
  parent_id,
  purchase_id,
  estimated_arrival_time,
  has_transfer,
	supplier_name as 'deliver_name',
  sum( count ) AS count 
 FROM
  dc_base_purchase 
 WHERE
  buy_status IN ( 0, 1, 2, 3 ) 
  AND has_delete = 0 
 GROUP BY
  bailun_sku,
  parent_id 
 ) tb1
 LEFT JOIN ( SELECT bailun_sku, warehouse_code, parent_id, sum( count ) AS count FROM dc_base_purchase_inbound GROUP BY bailun_sku, parent_id ) tb2 ON tb1.bailun_sku = tb2.bailun_sku 
 AND tb1.parent_id = tb2.parent_id 
GROUP BY
 tb1.bailun_sku,
 tb1.warehouse_code,
 tb1.purchase_id
);

alter table dc_auto_purchase_ontheway rename dc_auto_purchase_onthewayTemp; 
alter table dc_auto_purchase_ontheway_temp rename dc_auto_purchase_ontheway; 
alter table dc_auto_purchase_onthewayTemp rename dc_auto_purchase_ontheway_temp; 
truncate table dc_auto_purchase_ontheway_temp; ");
            }
            catch (Exception ex)
            {
                _connection.Insert<dc_task_error_log>(new dc_task_error_log
                {
                    date = DateTime.Now,
                    message = ex.Message,
                    stack_trace = ex.StackTrace,
                    task_name = "ResetOutofstock"
                });
            }

        }

        public static void 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(); ;
        }



    }
}
