using System;
using System.Collections.Generic;
using System.Text;
using Bailun.DC.Models;
using Dapper;
using MySql.Data.MySqlClient;
using System.Linq;

namespace Bailun.DC.Services
{
    public class WareHouseServices
    {

        /// <summary>
        /// 获取环球仓库类型
        /// </summary>
        /// <returns></returns>
        public List<string> ListWareHouseType()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select hq_type from dc_base_warehouse group by hq_type";

                return cn.Query<string>(sql).AsList();

            }
        }

        /// <summary>
        /// 获取仓库列表
        /// </summary>
        /// <param name="warehousetype">仓库类型</param>
        /// <returns></returns>
        public List<dc_base_warehouse> List(string warehousetype)
        {
            var param = new DynamicParameters();

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_base_warehouse ";

                if (!string.IsNullOrWhiteSpace(warehousetype))
                {
                    sql += " where hq_type=@type";
                    param.Add("type", warehousetype);
                }


                return cn.Query<dc_base_warehouse>(sql,param).AsList();
            }
        }

        /// <summary>
        /// 获取逻辑仓库库存
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="total">符合条件的记录总数</param>
        /// <returns></returns>
        public List<Models.Stock.mLogicWarehouse> ListLogicWarehouseStock(BtTableParameter parameter, string warehousetype, string warehouse,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sqlwhere = " where 1=1 ";
            var sql = @"select t1.warehouse_code,t4.warehouse_name,sum(t1.usable_stock) usable_stock,sum(t1.usable_stock*t3.unit_price) amount_stock,sum(t2.quantity_purchase) quantity_purchase,sum(t2.quantity_purchase*t3.unit_price) purchase_amount,sum(t2.quantity_transfer) quantity_transfer,sum(t2.quantity_transfer*t3.unit_price) amount_transit,sum(t5.oneday_total_sales) oneday_total_sales,sum(t5.oneday_total_sales*t3.unit_price) amount_onedaysale,sum(t5.sevenday_total_sales) sevenday_total_sales,sum(t5.fourteenday_total_sales) fourteenday_total_sales,sum(t5.thirtyday_total_sales) thirtyday_total_sales
                                from dc_base_stock t1
                                left join dc_mid_transit t2 on t1.warehouse_code=t2.warehouse_code and t1.bailun_sku=t2.bailun_sku
                                left join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku
                                left join dc_daily_sales t5 on t5.record_date = DATE_SUB(curdate(),INTERVAL 1 DAY) and t1.warehouse_code=t5.warehouse_code and t1.bailun_sku =t5.bailun_sku
                                ";

            var sqlcount = "select t1.warehouse_code from dc_base_stock t1 ";

            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " join dc_base_warehouse t4 on t1.warehouse_code=t4.warehouse_code  and t4.hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);

                sqlcount += " join dc_base_warehouse t4 on t1.warehouse_code=t4.warehouse_code and hq_type=@hq_type";
            }
            else
            {
                sql += " left join dc_base_warehouse t4 on t1.warehouse_code=t4.warehouse_code ";
            }
            
            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sqlwhere += "t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);

                sqlcount+= "t1.warehouse_code=@warehouse_code";
            }

            sqlwhere += " group by t1.warehouse_code";

            var sqlorder = "";

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sqlorder += " order by " + parameter.sort;

                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    sqlorder += " " + parameter.order;
                }
            }

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<Models.Stock.mLogicWarehouse>(parameter.pageIndex, parameter.limit, sql+ sqlwhere+ sqlorder, ref total, sqlparam, "select count(*) from (" + sqlcount+ "  group by t1.warehouse_code) tb",2*60);
                return obj.AsList();
            }
        }

        /// <summary>
        /// 逻辑仓库库存 统计
        /// </summary>
        /// <param name="warehousetype"></param>
        /// <param name="warehouse"></param>
        /// <returns></returns>
        public Models.Stock.mLogicWarehouse LogicWarehouseStockCount(string warehousetype, string warehouse)
        {
            var sqlparam = new DynamicParameters();
            var sqlwhere = " where 1=1 ";
            var sql = @"select sum(t1.usable_stock) usable_stock,sum(t1.usable_stock*t3.unit_price) amount_stock,sum(t2.quantity_purchase) quantity_purchase,sum(t2.quantity_purchase*t3.unit_price) purchase_amount,sum(t2.quantity_transfer) quantity_transfer,sum(t2.quantity_transfer*t3.unit_price) amount_transit,sum(t5.oneday_total_sales) oneday_total_sales,sum(t5.oneday_total_sales*t3.unit_price) amount_onedaysale,sum(t5.sevenday_total_sales) sevenday_total_sales,sum(t5.fourteenday_total_sales) fourteenday_total_sales,sum(t5.thirtyday_total_sales) thirtyday_total_sales
                                from dc_base_stock t1
                                left join dc_mid_transit t2 on t1.warehouse_code=t2.warehouse_code and t1.bailun_sku=t2.bailun_sku
                                left join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku
                                left join dc_daily_sales t5 on t5.record_date = DATE_SUB(curdate(),INTERVAL 1 DAY) and t1.warehouse_code=t5.warehouse_code and t1.bailun_sku =t5.bailun_sku
                                ";

            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += "  join dc_base_warehouse t4 on t1.warehouse_code=t4.warehouse_code  and t4.hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sqlwhere += "t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirst<Models.Stock.mLogicWarehouse>(sql + sqlwhere, sqlparam,null,2*60);
                return obj;
            }

        }

        /// <summary>
        /// Sku仓库列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="code">仓库编码</param>
        /// <param name="categoryid">分类id</param>
        /// <param name="minordercount">14日均销量 min值</param>
        /// <param name="maxordercount">14日均销量 max值</param>
        /// <param name="stockmin">库存 min值</param>
        /// <param name="stockmax">库存 max值</param>
        /// <param name="stockamountmin">库存金额 min值</param>
        /// <param name="stockamountmax">库存金额 max值</param>
        /// <param name="total">符合条件的总记录数</param>
        /// <returns></returns>
        public List<Models.Stock.mSkuWarehouse> ListWareHouseSku(BtTableParameter parameter, string code, int? categoryid, int? minordercount, int? maxordercount, int? stockmin, int? stockmax, decimal? stockamountmin, decimal? stockamountmax, string skucategoryids,int? push_status, ref int total)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select t1.bailun_sku,t1.warehouse_code,t5.warehouse_name,t2.sku_image,t2.push_status,t2.category_name,t2.sku_title_en,t2.sku_title_cn,t1.usable_stock,(t1.usable_stock*t2.unit_price) stockamount,(t3.quantity_purchase) quantity_purchase,(t3.quantity_purchase*t2.unit_price) purchase_amount,(t3.quantity_transfer) quantity_transfer,(t3.quantity_transfer*t2.unit_price) amount_transit,(t4.oneday_total_sales) oneday_total_sales,(t4.oneday_total_sales*t2.unit_price) amount_onedaysale,(t4.sevenday_total_sales) sevenday_total_sales,(t4.fourteenday_total_sales) fourteenday_total_sales,(t4.thirtyday_total_sales) thirtyday_total_sales                                ";
            var sqlwhere = @" from dc_base_stock t1
                                        left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                                        left join dc_mid_transit t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
                                        left join dc_daily_sales t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code and t4.record_date = DATE_SUB(curdate(), INTERVAL 0 DAY)
                                        left join dc_base_warehouse t5 on t1.warehouse_code = t5.warehouse_code 
                                        where 1=1 
                                        ";

            if (!string.IsNullOrWhiteSpace(code))
            {
                sqlwhere += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", code);
            }

            if (categoryid.HasValue)
            {
                sqlwhere += " and t2.category_id=@category_id";
                sqlparam.Add("category_id", categoryid.Value);
            }

            if (minordercount.HasValue && minordercount.Value>0)
            {
                sqlwhere += " and t4.fourteenday_average_sales>=@minordercount";
                sqlparam.Add("minordercount", minordercount.Value);
            }

            if (maxordercount.HasValue && maxordercount.Value > 0)
            {
                sqlwhere += " and t4.fourteenday_average_sales<@maxordercount";
                sqlparam.Add("maxordercount", maxordercount.Value);
            }

            if (stockmin.HasValue)
            {
                sqlwhere += " and t1.usable_stock>=@stockmin";
                sqlparam.Add("stockmin", stockmin.Value);
            }
            if (stockmax.HasValue)
            {
                sqlwhere += " and t1.usable_stock<@stockmax";
                sqlparam.Add("stockmax", stockmax.Value);
            }
            if (stockamountmin.HasValue)
            {
                sqlwhere += " and (t1.usable_stock*t2.unit_price)>=@stockamountmin";
                sqlparam.Add("stockamountmin", stockamountmin.Value);
            }
            if (stockamountmax.HasValue)
            {
                sqlwhere += " and (t1.usable_stock*t2.unit_price)<@stockamountmax";
                sqlparam.Add("stockamountmax", stockamountmax.Value);
            }
            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    //获取选中分类下所有的分类
                    var AllCategory = Services.CommonServices.GetCategoryList(0);
                    var listcategory = new List<Models.Common.CategoryDto>();

                    foreach (var item in arr)
                    {
                        listcategory.AddRange(Services.CommonServices.GetCategoryListAll(AllCategory, int.Parse(item)));
                    }

                    var s = string.Join(",", listcategory.Select(a => a.C_ID));
                    if (!string.IsNullOrEmpty(s))
                    {
                        sqlwhere += " and t2.category_id in (" + s + ")";
                    }
                }
            }
            if (push_status.HasValue)
            {
                sqlwhere += " and t2.push_status="+push_status.Value;
            }

            if (!string.IsNullOrEmpty(parameter.sort))
            {
                sqlwhere += " order by "+parameter.sort+" "+parameter.order;
            }
            

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<Models.Stock.mSkuWarehouse>(parameter.pageIndex, parameter.limit, sql + sqlwhere, ref total, sqlparam, "select count(*) from (select t1.id " + sqlwhere + ") tb");

                return obj.AsList();
            }
        }

        /// <summary>
        /// 根据仓库设置属性获取仓库列表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public List<dc_base_warehouse> ListWarehouseByExtType(string type)
        {
            var param = new DynamicParameters();

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_base_warehouse ";

                if (!string.IsNullOrWhiteSpace(type))
                {
                    sql += " where warehouse_type_ex=@type";
                    param.Add("type", type);
                }


                return cn.Query<dc_base_warehouse>(sql, param).AsList();
            }
        }

    }
}
