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 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))
            {
                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.Stock.mSku> ListSku(BtTableParameter parameter,string sku,string skuname,ref int total)
        {
            var sqlparam = new DynamicParameters();
            
            var sql = @"select t1.bailun_sku,t1.sku_image,t1.category_name,t1.sku_title_en,t1.sku_title_cn,sum(t2.usable_stock) usable_stock,(t2.usable_stock*t1.unit_price) amount_stock,sum(t3.quantity_purchase) quantity_purchase,sum(t3.quantity_purchase*t1.unit_price) purchase_amount,sum(t3.quantity_transfer) quantity_transfer,sum(t3.quantity_transfer*t1.unit_price) amount_transit,sum(t5.oneday_total_sales) oneday_total_sales,sum(t5.oneday_total_sales*t1.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 ";
            var sqlwhere = @"from dc_base_sku t1
                                        left join dc_base_stock t2 on t1.bailun_sku=t2.bailun_sku
                                        left join dc_mid_transit t3 on t1.bailun_sku=t3.bailun_sku
                                        left join dc_daily_sales t5 on t1.bailun_sku =t5.bailun_sku and t5.record_date = DATE_SUB(curdate(),INTERVAL 1 DAY)
                                        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 t1.sku_title_cn=@sku_title_cn";
                sqlparam.Add("sku_title_cn", skuname);
            }

            sqlwhere += " group by t1.bailun_sku";

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

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

                var obj = cn.Page<Models.Stock.mSku>(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.Stock.mSku ListSkuCount(string sku, string skuname)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select sum(t2.usable_stock) usable_stock,(t2.usable_stock*t1.unit_price) amount_stock,sum(t3.quantity_purchase) quantity_purchase,sum(t3.quantity_purchase*t1.unit_price) purchase_amount,sum(t3.quantity_transfer) quantity_transfer,sum(t3.quantity_transfer*t1.unit_price) amount_transit,sum(t5.oneday_total_sales) oneday_total_sales,sum(t5.oneday_total_sales*t1.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 ";
            var sqlwhere = @"from dc_base_sku t1
                                        left join dc_base_stock t2 on t1.bailun_sku=t2.bailun_sku
                                        left join dc_mid_transit t3 on t1.bailun_sku=t3.bailun_sku
                                        left join dc_daily_sales t5 on t1.bailun_sku =t5.bailun_sku and t5.record_date = DATE_SUB(curdate(),INTERVAL 1 DAY)
                                        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 t1.sku_title_cn=@sku_title_cn";
                sqlparam.Add("sku_title_cn", skuname);
            }

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

                var obj = cn.QueryFirst<Models.Stock.mSku>(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))
            {
                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))
            {
                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))
            {
                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))
            {
                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);
            }
        }
    }
}
