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

namespace AutoTurnOver.DB
{
   public class dc_not_stock : connectionHelper
    {
        public static List<Models.dc_not_stock> List(dc_not_stock_search_dto search, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            try
            {
                var sql = "";
                sql = @"select 
t2.source,
t2.bailun_sku,
t_not_stock.product_inner_code,
t2.sku_title_cn as 'sku_title',
t2.suppliers_link,
t2.suppliers_name,
t3.oneday_sales,
t1.history_sevenday_sales,
t4.quantity_out_stock,
t5.usable_stock,
t4.quantity_purchase,
t2.moq,
t_not_stock.test_sales,
t_not_stock.success_sales,
( case when t2.push_time>'1999-01-01' then t2.push_time else null end) as 'push_time'
from dc_not_stock_goods_sales as t_not_stock
left join dc_base_sku as t2 on t_not_stock.product_inner_code = t2.product_inner_code
left join dc_auto_turnover as t1 on t2.bailun_sku =t1.bailun_sku and t_not_stock.warehouse_code = t1.warehouse_code
left join dc_auto_sales as t3 on t1.bailun_sku=t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_mid_transit as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_base_stock as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
where 1=1
 ";

                string sqlCount = @"
select 
count(1)
from dc_not_stock_goods_sales as t_not_stock
left join dc_base_sku as t2 on t_not_stock.product_inner_code = t2.product_inner_code
left join dc_auto_turnover as t1 on t2.bailun_sku =t1.bailun_sku and t_not_stock.warehouse_code = t1.warehouse_code
left join dc_auto_sales as t3 on t1.bailun_sku=t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_mid_transit as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_base_stock as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
where 1=1
";
                DynamicParameters parameters = new DynamicParameters();
                if (search != null)
                {
                    if (search.search_type > 0 && !string.IsNullOrWhiteSpace(search.key_words))
                    {
                        switch (search.search_type)
                        {
                            case 1:
                                sql += " and t_not_stock.product_inner_code=@product_inner_code ";
                                sqlCount += " and t_not_stock.product_inner_code=@product_inner_code ";
                                parameters.Add("product_inner_code", search.key_words);
                                break;
                        }
                    }
                    if (search.push_time_min != null)
                    {
                        sql += " and t2.push_time>=@push_time_min ";
                        sqlCount += " and t2.push_time>=@push_time_min ";
                        parameters.Add("push_time_min", search.push_time_min.Value.ToString("yyyy-MM-dd 00:00:00"));
                    }
                    if (search.push_time_max != null)
                    {
                        sql += " and t2.push_time<=@push_time_max ";
                        sqlCount += " and t2.push_time<=@push_time_max ";
                        parameters.Add("push_time_max", search.push_time_max.Value.ToString("yyyy-MM-dd 23:59:59"));
                    }
                    if (search.goods_sales_min!=null)
                    {
                        sql += " and t_not_stock.success_sales>=@goods_sales_min ";
                        sqlCount += " and t_not_stock.success_sales>=@goods_sales_min ";
                        parameters.Add("goods_sales_min", search.goods_sales_min);
                    }
                    if (search.goods_sales_max != null)
                    {
                        sql += " and t_not_stock.success_sales<@goods_sales_max ";
                        sqlCount += " and t_not_stock.success_sales<@goods_sales_max ";
                        parameters.Add("goods_sales_max", search.goods_sales_max);
                    }

                    if (search.source_type !=null)
                    {
                       
                        if (search.source_type == 1)
                        {
                            //分销
                            sql += " and t2.source in (5,9,10) ";
                            sqlCount += " and t2.source in (5,9,10) ";
                        }
                        else if(search.source_type == 2)
                        {
                            //供应商推荐
                            sql += " and t2.source in (4,6) ";
                            sqlCount += " and t2.source in (4,6) ";
                        }
                        else
                        {
                            // 其他
                            sql += " and t2.source not in (4,5,6,9,10) ";
                            sqlCount += " and t2.source not in (4,5,6,9,10) ";
                        }
                        
                    }


                    if (search.success_states != null)
                    {
                        if (search.success_states == 1)
                        {
                            // 成功
                            sql += " and ( t_not_stock.success_sales>=1 and TIMESTAMPDIFF(day,t2.push_time,now())>=7 ) ";
                            sqlCount += " and ( t_not_stock.success_sales>=1 and TIMESTAMPDIFF(day,t2.push_time,now())>=7 ) ";
                        }
                        if (search.success_states == 2)
                        {
                            // 失败
                            sql += " and ( t_not_stock.test_sales<=0 and TIMESTAMPDIFF(day,t2.push_time,now())>=21 ) ";
                            sqlCount += " and ( t_not_stock.test_sales<=0  and TIMESTAMPDIFF(day,t2.push_time,now())>=21 ) ";
                        }
                        if (search.success_states == 3)
                        {
                            // 待定 
                            sql += " and !( t_not_stock.success_sales>=1 and TIMESTAMPDIFF(day,t2.push_time,now())>=7 ) and  !( t_not_stock.test_sales<=0 and TIMESTAMPDIFF(day,t2.push_time,now())>=21 ) ";
                            sqlCount += " and !( t_not_stock.success_sales>=1 and TIMESTAMPDIFF(day,t2.push_time,now())>=7 ) and  !( t_not_stock.test_sales<=0 and TIMESTAMPDIFF(day,t2.push_time,now())>=21 ) ";
                        }
                    }

                    
                }

                if (limit > 10000)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<int>(sqlCount, parameters);

                    //设置默认排序字段
                    if (string.IsNullOrWhiteSpace(sort)) sort = "t_not_stock.id";
                    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<Models.dc_not_stock>(sql, parameters,commandTimeout: 0);

                return obj.AsList();
            }
            catch (Exception ex)
            {

                return new List<Models.dc_not_stock>();
            }

        }

        /// <summary>
        /// 标记淘汰
        /// </summary>
        /// <param name="ids"></param>
        public static void Eliminate(string product_inner_codes)
        {
            if (!string.IsNullOrWhiteSpace(product_inner_codes))
            {
                throw new Exception("请选择商品");
            }
            MySqlConnection conn = _connection;
            conn.Open();
            IDbTransaction transaction =  conn.BeginTransaction();
            try
            {
                _connection.Execute(@" 
update dc_sku_monitor as t1 ,
dc_base_sku as t2 
set t1.is_weedout=1,weedout_updatetime=now()
where t1.sku = t2.bailun_sku and t2.product_inner_code in @product_inner_codes and t1.warehousecode='BLGZ03'
", new { product_inner_codes = product_inner_codes.Split(',') },transaction: transaction);

                _connection.Execute(@" 
 replace into dc_auto_config_sku_warehouse_copy1(`bailun_sku`,`sku_title`,`warehouse_code`,`warehouse_name`,`gmt_create`,`gmt_modified`,`status`,`bl_operator`)
(
 select 
	bailun_sku,
	'' as 'sku_title',
	'BLGZ03',
	'广州-03仓' as 'warehouse_name',
	now() as 'gmt_create',
	now() as 'gmt_modified',
	1 as 'status',
	'' as 'bl_operator'
 from dc_base_sku where product_inner_code in @product_inner_codes
); 
", new { product_inner_codes = product_inner_codes.Split(',') }, transaction: transaction);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
            }
            
        }

        /// <summary>
        /// 转仓
        /// </summary>
        public static void TransferWarehouse(string product_inner_codes)
        {
            if (string.IsNullOrWhiteSpace(product_inner_codes))
            {
                throw new Exception("请选择商品");
            }
            MySqlConnection conn = _connection;
            conn.Open();
            IDbTransaction transaction = conn.BeginTransaction();
            try
            {
                // 数据仓转移分组
                _connection.Execute(@" 
update dc_sku_monitor as t1 ,
dc_base_sku as t2 
set t1.groupname='无库分组',groupupdatetime=now()
where t1.sku = t2.bailun_sku and t2.product_inner_code in @product_inner_codes and t1.warehousecode='BLGZ03'
", new { product_inner_codes = product_inner_codes.Split(',') }, transaction: transaction);

                //修改默认发货仓
                List<SaveDefaultWaresRequestDto> requests = _connection.Query<SaveDefaultWaresRequestDto>(" select t1.bailun_sku as 'sku','GZBLWH' as 'wareCode',18 as 'areaId' from dc_base_sku as t1 where t1.product_inner_code in @product_inner_codes ", new { product_inner_codes = product_inner_codes.Split(',') }).AsList();
                if (requests != null && requests.Count>=1)
                {
                    ApiUtility.SaveDefaultWares(requests);
                }
                

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}
