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

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;
            }
        }

    }
}
