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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 库容查询
    /// </summary>
    public class dc_base_storage_capacity_dao : connectionHelper
    {
        public static List<dc_base_storage_capacity_dto> StorageCapacityList(dc_base_storage_capacity_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_base_storage_capacity_dto>();
            DynamicParameters parameters = new DynamicParameters();
            try
            {
                var sql = @"select t1.*,t2.`warehouse_name` as 'warehouse_name' from dc_base_storage_capacity as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t2.hq_type=@hq_type";
                    parameters.Add("hq_type", m.warehousetype);
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t2.area_id=@warehousearea ";
                    parameters.Add("warehousearea", m.warehousearea);
                }

                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and t1.bailun_sku = @bailun_sku ";
                    parameters.Add("bailun_sku", m.bailun_sku);
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " and t1.warehouse_code=@warehousecode ";
                    parameters.Add("warehousecode", m.warehousecode);
                }
                if (m.btime != null)
                {
                    sql += " and t1.update_date>=@btime ";
                    parameters.Add("btime", m.btime);
                }
                if (m.etime != null)
                {
                    sql += " and t1.update_date<=@etime ";
                    parameters.Add("etime", m.etime);
                }

                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1", parameters);

                var obj = _connection.Query<dc_base_storage_capacity_dto>(sql + " limit " + offset + "," + limit, parameters);

                return obj.AsList();

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


        public static List<dc_base_storage_capacity_log_dto> StorageCapacityLogList(dc_base_storage_capacity_log_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_base_storage_capacity_log_dto>();
            DynamicParameters parameters = new DynamicParameters();
            try
            {
                var sql = @"select t1.*,t2.`warehouse_name` as 'warehouse_name' from dc_base_storage_capacity_log as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t2.hq_type=@hq_type";
                    parameters.Add("hq_type", m.warehousetype);
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t2.area_id=@warehousearea ";
                    parameters.Add("warehousearea", m.warehousearea);
                }

                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and t1.bailun_sku = @bailun_sku ";
                    parameters.Add("bailun_sku", m.bailun_sku);
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " and t1.warehouse_code=@warehousecode ";
                    parameters.Add("warehousecode", m.warehousecode);
                }
                if (m.btime != null)
                {
                    sql += " and t1.create_date>=@btime ";
                    parameters.Add("btime", m.btime.Value.ToDayHome());
                }
                if (m.etime != null)
                {
                    sql += " and t1.create_date<=@etime ";
                    parameters.Add("etime", m.etime.Value.ToDayHome());
                }

                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1", parameters);

                sql += " order by t1.create_date desc ";
                var obj = _connection.Query<dc_base_storage_capacity_log_dto>(sql + " limit " + offset + "," + limit, parameters);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 查询库容
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static List<dc_base_storage_capacity_dto> GetList(dc_base_storage_capacity_query_dto query)
        {
            var sql = @"select t1.*,t2.`warehouse_name` as 'warehouse_name' from dc_base_storage_capacity as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1 = 1 ";
            DynamicParameters parameters = new DynamicParameters();
            sql += " and bailun_sku_warehouse_code in @bailun_sku_warehouse_codes";
            parameters.Add("bailun_sku_warehouse_codes", query.datas.Select(s => $"{s.bailun_sku}{s.warehouse_code}").ToList());
            return _connection.Query<dc_base_storage_capacity_dto>(sql, parameters).ToList();
        }

        /// <summary>
        /// 新增日志
        /// </summary>
        /// <param name="data"></param>
        /// <param name="is_share">是否需要处理共享仓</param>
        public static void AddLog(dc_base_storage_capacity_log data,int is_share = 0)
        {
            if (string.IsNullOrWhiteSpace(data.create_user))
            {
                throw new Exception("创建人必填");
            }
            var type_enum = (dc_base_storage_capacity_log_enum)data.type;
            if(type_enum.ToString()== data.type.ToString())
            {
                throw new Exception("无法识别的流水类型");
            }
            data.bailun_sku_warehouse_code = $"{data.warehouse_code}";
            var conn = MyMySqlConnection._connection;
            conn.Open();
            using (var t = conn.BeginTransaction())
            {
                try
                {
                    // 查询是否有库容记录
                    var mainData = conn.QuerySingleOrDefault<dc_base_storage_capacity>(" select * from dc_base_storage_capacity where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
                    {
                        bailun_sku_warehouse_code = data.bailun_sku_warehouse_code
                    });
                    if (mainData == null)
                    {
                        mainData = new dc_base_storage_capacity
                        {
                            bailun_sku_warehouse_code = data.bailun_sku_warehouse_code,
                            warehouse_code = data.warehouse_code,
                            account = data.account,
                            create_user = data.create_user,
                            create_date = DateTime.Now,
                            stock = 0,
                            update_date = DateTime.Now,
                            version = 0
                        };
                        mainData.id = conn.Insert(mainData) ?? 0;

                        // 添加库容监控
                        conn.Insert<dc_base_storage_capacity_config>(new dc_base_storage_capacity_config
                        {
                            bailun_sku_warehouse_code = mainData.bailun_sku_warehouse_code,
                            status = 1,
                            warehouse_code = mainData.warehouse_code
                        });
                    }

                    if (type_enum == dc_base_storage_capacity_log_enum.导入)
                    {
                        mainData.stock = 0;
                    }
                    data.quantity = CalculationVal(type_enum, data.quantity);
                    mainData.stock += data.quantity;
                    if (mainData.stock < 0)
                    {
                        throw new Exception("库容不足");
                    }
                    data.real_time_stock = mainData.stock;
                    var old_version = mainData.version;
                    mainData.version++;
                    data.create_date = DateTime.Now;
                    conn.Insert(data);
                    int line_count = conn.Execute(" update dc_base_storage_capacity set stock=@stock,version=@version,update_date=now() where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and version=@old_version ", new
                    {
                        stock = mainData.stock,
                        version = mainData.version,
                        bailun_sku_warehouse_code = mainData.bailun_sku_warehouse_code,
                        old_version = old_version
                    });
                    if (line_count <= 0)
                    {
                        AddLog(data);
                    }

                    if (is_share == 1)
                    {
                        // 如果是欧洲站点则同步更新欧洲其他仓库的库容
                        var sites = new List<string> { "西班牙", "意大利", "德国", "法国" };
                        var base_ware = conn.QueryFirstOrDefault<string>(" select area_name  from dc_base_warehouse where warehouse_code=@warehouse_code  ", new
                        {
                            warehouse_code = data.warehouse_code
                        });
                        //欧洲站点才需要处理
                        if (sites.Contains(base_ware))
                        {
                            //查询除自己以外的其他欧洲站点
                            var warehouse_codes = conn.Query<string>(@" select t1.warehouse_code from dc_base_storage_capacity as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where t2.area_name in @sites and t1.warehouse_code !=@warehouse_code and t1.account=@account  ", new
                            {
                                sites = sites,
                                warehouse_code = data.warehouse_code,
                                account = mainData.account
                            }).ToList();
                            if (warehouse_codes != null && warehouse_codes.Count >= 1)
                            {
                                foreach (var item in warehouse_codes)
                                {
                                    var otherData = data.ToJson().ToObj<dc_base_storage_capacity_log>();
                                    otherData.warehouse_code = item;
                                    AddLog(otherData, 1);
                                }
                            }
                        }
                        
                    }
                   


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

            }


            if (type_enum == dc_base_storage_capacity_log_enum.导入)
            {
                // 如果是导入库容，则需要扣采购在途、采购临时
            }
        }


        /// <summary>
        /// 批量修改库容
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static batch_dc_base_storage_capacity_log BatchAddLog(batch_dc_base_storage_capacity_log data)
        {
            batch_dc_base_storage_capacity_log r = new batch_dc_base_storage_capacity_log() { datas = new List<api_dc_base_storage_capacity_log> { } };
            foreach (var item in data.datas)
            {
                try
                {
                    AddLog(item.ToJson().ToObj<dc_base_storage_capacity_log>());
                    item.success = true;
                }
                catch (Exception ex)
                {
                    item.success = false;
                    item.message = ex.Message;
                }

                r.datas.Add(item);
            }
            return r;
        }

        /// <summary>
        /// 计算增量值
        /// </summary>
        public static decimal CalculationVal(dc_base_storage_capacity_log_enum type, decimal val)
        {
            if (((int)type) % 2 == 0)
            {
                return (0 - val);
            }
            else
            {
                return (val);
            }
        }


    }
}
