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

namespace AutoTurnOver.DB
{
    public class dc_auto_forecast_fluctuation : connectionHelper
    {
        /// <summary>
        /// 查询异常提醒
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static IEnumerable<AutoTurnOver.Models.dc_auto_forecast_fluctuation_dto> List(dc_auto_forecast_fluctuation_search_dto data, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var sql = "";
            if (limit > 10000)
            {
                sql = @" 
select t1.*,t2.quantity_out_stock,
(t4.count) as 'ontheway_quantity', -- 采购在途
t3.buyer_name,
t4.estimated_arrival_time,
t4.purchase_id,
t3.sku_title_cn,
(case when ( ifnull(t4.count,0)>0) and now()>t4.estimated_arrival_time then 1 else 0 end) as 'is_overdue',
t4.deliver_name,
t2.history_sevenday_sales, 
t2.history_fourteenday_sales,
t2.history_thirtyday_sales,
( case when t5.`status`=0 or t5.`status` is null then 0 else 1 end ) as 'monitor_status',
t6.usable_stock,
t3.moq,
t6.`character`,
t2.turnover_days,
t3.supplier_delivery as 'sku_supplier_delivery',
t3.transfer_delivery as 'sku_transfer_delivery'
from
dc_auto_forecast_fluctuation as t1
inner JOIN dc_auto_turnover as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku  = t3.bailun_sku
left join dc_auto_purchase_ontheway as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_base_stock as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
where 1 = 1
";
            }
            else
            {
                sql = @" 
select t1.*,t3.sku_title_cn,
t2.history_sevenday_sales,
t2.history_fourteenday_sales,
t2.history_thirtyday_sales,
( case when t5.`status`=0 or t5.`status` is null then 0 else 1 end ) as 'monitor_status',
t6.usable_stock,
t6.`character`
from
dc_auto_forecast_fluctuation as t1
inner JOIN dc_auto_turnover as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku  = t3.bailun_sku
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code 
left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_base_stock as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
where 1 = 1
";
            }

            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(data.sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku ";
                parameters.Add("bailun_sku", data.sku);
            }
            if (!string.IsNullOrWhiteSpace(data.character))
            {
                sql += " and t6.bailun_sku_warehouse_code in  ( select bailun_sku_warehouse_code from dc_base_data_character where `character`=@character )  ";
                parameters.Add("character", data.character);
            }
            if (!string.IsNullOrWhiteSpace(data.warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", data.warehouse_code);
            }
            if (data.has_solve != null)
            {
                if (data.has_solve == true)
                {
                    sql += " and t1.has_solve=1 ";
                }
                else
                {
                    sql += " and has_solve=0 ";
                }
            }
            if (data.has_confirm != null)
            {
                if (data.has_confirm == true)
                {
                    sql += " and t1.has_confirm=1 ";
                }
                else
                {
                    sql += " and t1.has_confirm=0 ";
                }
            }
            if (data.err_type != null)
            {
                sql += " and t1.`type`= " + data.err_type;
            }
            if (data.has_overdue != null)
            {
                DateTime now = DateTime.Now;
                if (data.has_overdue == true)
                {
                    sql += " and t1.end_time<=@end_time ";
                    parameters.Add("end_time", now.ToString("yyyy-MM-dd 23:59:59"));
                }
                else
                {
                    sql += " and t1.end_time>=@end_time ";
                    parameters.Add("end_time", now.ToString("yyyy-MM-dd 00:00:00"));
                }
            }
            if (!string.IsNullOrWhiteSpace(data.warehousetype))
            {
                sql += " and dbw.hq_type=" + $"'{data.warehousetype}'";
            }
            if (data.warehousearea > 0)
            {
                sql += " and dbw.area_id=" + data.warehousearea;
            }

            if (limit < 10000)
            {
                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1", parameters, commandTimeout: 0);
            }
            else
            {
                total = 0;
            }


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

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

            var obj = _connection.Query<Models.dc_auto_forecast_fluctuation_dto>(sql + " limit " + offset + "," + limit, parameters, commandTimeout: 0);

            if (limit < 10000)
            {
                if (obj != null)
                {
                    foreach (var item in obj)
                    {
                        item.purchase_advise_count = _connection.QuerySingleOrDefault<int>(" select count(1) from dc_auto_purchase_advise_detailed where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ", new { item.warehouse_code, item.bailun_sku });
                    }
                }
            }


            return obj.AsList();
        }

        /// <summary>
        /// 获取异常提醒详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static AutoTurnOver.Models.dc_auto_forecast_fluctuation Info(int id)
        {
            return _connection.QueryFirstOrDefault<AutoTurnOver.Models.dc_auto_forecast_fluctuation>("select * from dc_auto_forecast_fluctuation where id=" + id);
        }

        /// <summary>
        /// 确认异常
        /// </summary>
        /// <param name="data"></param>
        /// <param name="user"></param>
        public static void Confirm(AutoTurnOver.Models.dc_auto_forecast_fluctuation data, UserData user)
        {
            var oldData = Info(data.id);
            if (oldData == null) throw new Exception("数据不存在");

            _connection.Execute(@" update dc_auto_forecast_fluctuation 
set forecast_solve_time=@forecast_solve_time,bl_operator=@bl_operator,remark=@remark,has_confirm=1,confirm_time=@confirm_time
where id=@id", new
            {
                data.forecast_solve_time,
                data.remark,
                bl_operator = user.UserName,
                confirm_time = DateTime.Now,
                data.id
            });
        }
    }
}
