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

namespace Bailun.DC.Services
{
    public class SkuInfoServices
    {

        /// <summary>
        /// 分类列表
        /// </summary>
        /// <returns></returns>
        public List<SkuCategory> ListSkuCategory()
        {
            var sql = "select category_id as id,category_name as name from dc_base_sku where category_id>0 group by category_id";

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

                return cn.Query<SkuCategory>(sql).AsList();
            }
        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="sku"></param>
        /// <param name="skuname"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<Models.dc_mid_sku_statistics> ListSku(BtTableParameter parameter,string sku,string skuname,ref int total)
        {
            var sqlparam = new DynamicParameters();
            
            var sql = @"select t1.bailun_sku,t2.sku_image,t2.category_name,t2.sku_title_en,t2.sku_title_cn,t1.count_stock,t1.amount_stock,t1.count_onway_purchase,t1.amount_onway_purchase,t1.count_onway_allot,t1.amount_onway_allot,t1.one_day_sale_count,t1.one_day_sale_amount,t1.seven_day_sale_count,t1.fourteen_day_sale_count,t1.thirty_day_sale_count,t1.count_unshipping,t1.count_outstock";

            var sqlwhere = "  from dc_mid_sku_statistics t1 join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sqlwhere += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }
            if (!string.IsNullOrWhiteSpace(skuname))
            {
                sqlwhere += " and t2.sku_title_cn=@sku_title_cn";
                sqlparam.Add("sku_title_cn", skuname);
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sqlwhere += " order by " + parameter.sort + " " + parameter.order;
            }
            else
            {
                sqlwhere += " order by t1.count_stock desc";
            }
            

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

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

                return obj.AsList();
            }

        }

        /// <summary>
        /// Sku统计
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="skuname"></param>
        /// <returns></returns>
        public Models.dc_mid_sku_statistics ListSkuCount(string sku, string skuname)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select sum(t1.count_stock) as count_stock,sum(t1.amount_stock) as amount_stock,sum(t1.count_onway_purchase) as count_onway_purchase,sum(t1.amount_onway_purchase) as amount_onway_purchase,sum(t1.count_onway_allot) as count_onway_allot,sum(t1.amount_onway_allot) as amount_onway_allot,sum(t1.one_day_sale_count) as one_day_sale_count,sum(t1.one_day_sale_amount) as one_day_sale_amount,sum(t1.seven_day_sale_count) as seven_day_sale_count,sum(t1.fourteen_day_sale_count) as fourteen_day_sale_count,sum(t1.thirty_day_sale_count) as thirty_day_sale_count from dc_mid_sku_statistics t1
join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku ";

            var sqlwhere = " where 1=1 ";

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sqlwhere += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }
            if (!string.IsNullOrWhiteSpace(skuname))
            {
                sqlwhere += " and t2.sku_title_cn=@sku_title_cn";
                sqlparam.Add("sku_title_cn", skuname);
            }

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

                var obj = cn.QueryFirst<Models.dc_mid_sku_statistics>(sql + sqlwhere, sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取Sku的开发时间
        /// </summary>
        /// <param name="sku"></param>
        /// <returns></returns>
        public DateTime? GetSkuDevelopTime(string sku)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select create_time from dc_base_sku where bailun_sku=@sku";
                var sqlparam = new DynamicParameters();
                sqlparam.Add("sku", sku);

                return cn.QueryFirstOrDefault<DateTime?>(sql, sqlparam);
            }
        }

        /// <summary>
        /// 获取百伦简单分类一二级
        /// </summary>
        /// <returns></returns>
        public List<Models.Api.mSkuCategoryTree> ListSimpleCategory12(int? companyid)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select id,parent_id,name,full_name,level,skums_id from dc_base_sku_simple_category ";
                var obj = cn.Query<dc_base_sku_simple_category>(sql).AsList();

                var list = new List<Models.Api.mSkuCategoryTree>();

                var onelevel = obj.Where(a => a.level == 1);
                foreach (var item in onelevel)
                {
                    var chidren = obj.Where(a => a.parent_id == item.skums_id);
                    var m = new Models.Api.mSkuCategoryTree {
                        id = item.skums_id,
                        name = item.full_name,
                        open = false,
                        children = chidren.Select(a=>new Models.Api.mSkuCategoryTree {
                            id = a.skums_id,
                            name = a.full_name,
                            open = false
                        }).ToList()
                    };

                    list.Add(m);
                }

                return list;
            }

        }

        /// <summary>
        /// 获取百伦简单分类详情信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public dc_base_sku_simple_category GetSimpleCategory(string full_name)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                sqlparam.Add("full_name", full_name);

                return cn.QueryFirstOrDefault<dc_base_sku_simple_category>("select * from dc_base_sku_simple_category where full_name=@full_name", sqlparam);
            }
        }

        /// <summary>
        /// 获取百伦简单分类详情信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public dc_base_sku_simple_category GetSimpleCategory(int id)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }
                
                return cn.QueryFirstOrDefault<dc_base_sku_simple_category>("select * from dc_base_sku_simple_category where id="+id);
            }
        }

        /// <summary>
        /// sku产品类型
        /// </summary>
        /// <returns></returns>
        public List<mSku_ProductType> ListSkuProductType()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<mSku_ProductType>("select product_type,product_type_desc from dc_base_sku group by product_type");

                return obj.ToList();
            }
        }

        /// <summary>
        /// sku开发来源
        /// </summary>
        /// <returns></returns>
        public List<int> ListSkuSource()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<int>("select Source from dc_base_sku group by Source");

                return obj.ToList();

            }
        }

        /// <summary>
        /// 获取平台SKU的百伦SKU
        /// </summary>
        /// <param name="platformsku">平台SKU列表</param>
        /// <returns></returns>
        public List<dc_base_sku_mapping> GetBailunSkuByPlatformSku(List<string> platformsku)
        {
            for(var i=0;i<platformsku.Count;i++)
            {
                if (platformsku[i].IndexOf('\'') >= 0)
                {
                    platformsku[i] = platformsku[i].Replace("'", "''");
                }
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $"select t1.bailun_sku,t1.platform_sku,t1.count,t1.platform,t1.site from dc_base_sku_mapping t1 where t1.platform_sku in ('{string.Join("','", platformsku)}') and t1.has_deleted=0 ";

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

            }
        }

        /// <summary>
        /// 获取SKU的财务分类
        /// </summary>
        /// <param name="sku"></param>
        /// <returns></returns>
        public List<dc_base_sku_finance_category> GetSkuFinanceCategory(List<string> sku)
        {
            var sql = $@"select t1.bailuncategoryname,t1.financecategoryname,t2.bailun_sku from dc_base_sku_finance_category t1 
join dc_base_sku t2 on t1.bailuncategoryid = t2.bailun_category_id and t2.bailun_sku in ('{string.Join("','",sku)}')";

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

                return cn.Query<dc_base_sku_finance_category>(sql).ToList();

            }
            
        }

        /// <summary>
        /// 获取财务分类列表
        /// </summary>
        /// <returns></returns>
        public List<dc_base_sku_finance_category> ListFinanceCategory()
        {
            var sql = $@"select financecategoryid,financecategoryname from dc_base_sku_finance_category t1 group by t1.financecategoryname";

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

                return cn.Query<dc_base_sku_finance_category>(sql).ToList();

            }
        }

        /// <summary>
        /// 获取财务分类列表
        /// </summary>
        /// <returns></returns>
        public List<dc_semi_stock_log> ListSemiSkuCategory()
        {
            var sql = $@"select product_category_id,product_category_name from dc_semi_stock_log where product_category_name!='缺失' group by product_category_name";

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

                return cn.Query<dc_semi_stock_log>(sql).ToList();

            }
        }


        /// <summary>
        /// 获取SKU的所有品牌列表
        /// </summary>
        /// <returns></returns>
        public List<string> ListBrand()
        {
            var sql = "select brand from dc_base_sku group by brand";

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

                return cn.Query<string>(sql).ToList();
            }

        }

    }
}
