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.Warehouse;
using Bailun.DC.Models.Api;

namespace Bailun.DC.Services
{
    public class WareHouseServices
    {

        /// <summary>
        /// 获取环球仓库类型
        /// </summary>
        /// <returns></returns>
        public List<string> ListWareHouseType()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                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,int? areaid)
        {
            var param = new DynamicParameters();

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

                var sql = "select * from dc_base_warehouse where 1=1 ";

                if (!string.IsNullOrWhiteSpace(warehousetype))
                {
                    sql += " and hq_type=@type";
                    param.Add("type", warehousetype);
                }
                if (areaid.HasValue)
                {
                    sql += " and area_id="+areaid.Value;
                }

                
                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.hq_type,t1.warehouse_code,t1.warehouse_name,sum(t1.usable_stock) usable_stock,sum(t1.amount_stock) amount_stock,sum(t1.quantity_purchase) quantity_purchase,sum(t1.purchase_amount) purchase_amount,sum(t1.quantity_transfer) quantity_transfer,sum(t1.amount_transit) amount_transit,sum(t1.oneday_total_sales) oneday_total_sales,sum(t1.amount_onedaysale) amount_onedaysale,sum(t1.sevenday_total_sales) sevenday_total_sales,sum(t1.fourteenday_total_sales) fourteenday_total_sales,sum(t1.thirtyday_total_sales) thirtyday_total_sales,sum(quantity_tuneout_onway) quantity_tuneout_onway,sum(t1.quantity_tuneout_30days) quantity_tuneout_30days,sum(t1.quantity_tunein_30days) quantity_tunein_30days,sum(t1.nostockcount) as nostockcount,sum(t1.noshippingcount) as noshippingcount,sum(cash_in_30days) as cash_in_30days,sum(cash_out_30days) as cash_out_30days,sum(cash_net) as cash_net,(sum(thirtyday_total_sales)/sum(usable_stock)) as pin_rate_stock,sum(sevenday_total_sales_amount) as sevenday_total_sales_amount,sum(fourteenday_total_sales_amount) as fourteenday_total_sales_amount,sum(thirtyday_total_sales_amount) as thirtyday_total_sales_amount,sum(amount_tuneout_30days) as amount_tuneout_30days,sum(amount_tunein_30days) as amount_tunein_30days from dc_skuwarehouse_stock_sales t1 ";

            //var sqlcount = "select t1.warehouse_code from dc_skuwarehouse_stock_sales t1 ";

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

            sqlwhere += " group by warehouse_code";

            var sqlorder = "";

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

                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    sqlorder += " " + parameter.order;
                }
            }
            else
            {
                sqlwhere += " order by usable_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.Stock.mLogicWarehouse>(parameter.pageIndex, parameter.limit, sql+ sqlwhere+ sqlorder, ref total, sqlparam);
                return obj.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(string warehousetype, string warehouse)
        {
            var sqlparam = new DynamicParameters();
            var sqlwhere = " where 1=1 ";
            var sql = @"select hq_type,warehouse_code,warehouse_name,sum(usable_stock) usable_stock,sum(amount_stock) amount_stock,sum(quantity_purchase) quantity_purchase,sum(purchase_amount) purchase_amount,sum(quantity_transfer) quantity_transfer,sum(amount_transit) amount_transit,sum(oneday_total_sales) oneday_total_sales,sum(amount_onedaysale) amount_onedaysale,sum(sevenday_total_sales) sevenday_total_sales,sum(fourteenday_total_sales) fourteenday_total_sales,sum(thirtyday_total_sales) thirtyday_total_sales,sum(quantity_tuneout_onway) quantity_tuneout_onway,sum(quantity_tuneout_30days) quantity_tuneout_30days,sum(quantity_tunein_30days) quantity_tunein_30days,sum(nostockcount) as nostockcount,sum(noshippingcount) as noshippingcount,sum(cash_in_30days) as cash_in_30days,sum(cash_out_30days) as cash_out_30days,sum(cash_net) as cash_net from dc_skuwarehouse_stock_sales  ";

            //var sqlcount = "select t1.warehouse_code from dc_skuwarehouse_stock_sales t1 ";

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

            sqlwhere += " group by warehouse_code";
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<Models.Stock.mLogicWarehouse>(sql + sqlwhere,sqlparam);
                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(usable_stock) usable_stock,sum(amount_stock) amount_stock,sum(quantity_purchase) quantity_purchase,sum(purchase_amount) purchase_amount,sum(quantity_transfer) quantity_transfer,sum(amount_transit) amount_transit,sum(oneday_total_sales) oneday_total_sales,sum(amount_onedaysale) amount_onedaysale,sum(sevenday_total_sales) sevenday_total_sales,sum(fourteenday_total_sales) fourteenday_total_sales,sum(thirtyday_total_sales) thirtyday_total_sales,sum(quantity_tuneout_onway) quantity_tuneout_onway,sum(quantity_tuneout_30days) quantity_tuneout_30days,sum(quantity_tunein_30days) quantity_tunein_30days,sum(nostockcount) as nostockcount,sum(noshippingcount) as noshippingcount,sum(cash_in_30days) as cash_in_30days,sum(cash_out_30days) as cash_out_30days,sum(cash_net) as cash_net,(sum(thirtyday_total_sales)/sum(usable_stock)) as pin_rate_stock,sum(sevenday_total_sales_amount) as sevenday_total_sales_amount,sum(fourteenday_total_sales_amount) as fourteenday_total_sales_amount,sum(thirtyday_total_sales_amount) as thirtyday_total_sales_amount,sum(amount_tuneout_30days) as amount_tuneout_30days,sum(amount_tunein_30days) as amount_tunein_30days from dc_skuwarehouse_stock_sales ";

            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sqlwhere += " and hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sqlwhere += " and warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                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 warehousecode, int? categoryid, int? minordercount, int? maxordercount, int? stockmin, int? stockmax, decimal? stockamountmin, decimal? stockamountmax, string skucategoryids,int? push_status,int? available_days_start,int? available_days_end, ref int total)
        {
            var sqlparam = new DynamicParameters();
            //available_days
            var sql = @"select t1.*,t2.category_name,t2.sku_image,t2.sku_title_cn,t2.sku_title_en ";

            var sqlwhere = @" from dc_skuwarehouse_stock_sales t1
                                         left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku
                                        ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                //sqlwhere += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                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 + ")";
                    }
                }
            }

            sqlwhere += " where 1=1 ";

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

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

            if (minordercount.HasValue && minordercount.Value>0)
            {
                sqlwhere += " and (t1.fourteenday_total_sales/14)>=@minordercount";
                sqlparam.Add("minordercount", minordercount.Value);
            }

            if (maxordercount.HasValue && maxordercount.Value > 0)
            {
                sqlwhere += " and (t1.fourteenday_total_sales/14)<@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.amount_stock>=@stockamountmin";
                sqlparam.Add("stockamountmin", stockamountmin.Value);
            }
            if (stockamountmax.HasValue)
            {
                sqlwhere += " and t1.amount_stock<@stockamountmax";
                sqlparam.Add("stockamountmax", stockamountmax.Value);
            }
            
            if (push_status.HasValue)
            {
                sqlwhere += " and t2.push_status="+push_status.Value;
            }

            if(available_days_start.HasValue)
            {
                sqlwhere += " and t1.available_days>="+ available_days_start.Value;
            }

            if (available_days_end.HasValue)
            {
                sqlwhere += " and t1.available_days<"+available_days_end.Value;
            }


            var sqlsort = "";
            
            if (!string.IsNullOrEmpty(parameter.sort))
            {
                sqlsort += " order by " + parameter.sort + " " + parameter.order;
            }
            else
            {
                sqlsort += " order by usable_stock desc";
            }

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

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

                    return obj.AsList();
                }
                else
                {
                    var obj = cn.Query<Models.Stock.mSkuWarehouse>(sql + sqlwhere + sqlsort, sqlparam, null, true, 10 * 60).AsList();
                    return obj;
                }
            }
        }

        /// <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_read))
            {
                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();
            }
        }

        /// <summary>
        /// 获取仓库所在国家
        /// </summary>
        /// <returns></returns>
        public List<mWareHouseArea> ListWarehouseArea()
        {
            var sql = "select area_id as id,area_name as name from dc_base_warehouse where area_id is not null group by area_id ";

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

                var obj = cn.Query<mWareHouseArea>(sql);

                return obj.AsList();
            }
            
        }

        /// <summary>
        /// 数据仓分组列表
        /// </summary>
        /// <returns></returns>
        public List<string> ListGroupName()
        {
            var sql = "select groupname from dc_sku_monitor group by groupname";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<string>(sql);

                return obj.AsList();
            }
        }

        /// <summary>
        /// 获取仓库详情
        /// </summary>
        /// <param name="code">仓库编码</param>
        /// <returns></returns>
        public dc_base_warehouse GetWarehouse(string code)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select * from dc_base_warehouse where warehouse_code=@warehouse_code";
                var sqlparam = new DynamicParameters();
                sqlparam.Add("warehouse_code", code);

                return cn.QueryFirstOrDefault<dc_base_warehouse>(sql, sqlparam);

            }

        }

        /// <summary>
        /// 获取逻辑仓库里面有的仓库
        /// </summary>
        /// <param name="warehousetype"></param>
        /// <returns></returns>
        public List<dc_base_warehouse> ListWarehouseFromStockAndSales(string warehousetype)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select warehouse_code,warehouse_name from dc_skuwarehouse_stock_sales where 1=1 ";
                if (!string.IsNullOrEmpty(warehousetype))
                {
                    sql += " and hq_type=@hq_type";
                    sqlparam.Add("hq_type",warehousetype);
                }

                sql += "  group by warehouse_code";

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

        }

        /// <summary>
        /// 获取仓库 库存分析报表
        /// </summary>
        /// <param name="request">分页信息</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="total">符合条件的总记录数</param>
        /// <returns></returns>
        public List<dc_mid_warehouse_redundancy> ListWarehouseRedundancy(BtTableParameter request, string warehousetype, string warehousecode,DateTime? day,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select t1.* from dc_mid_warehouse_redundancy t1 ";

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

                sql+= " where 1=1 ";

                if (day.HasValue)
                {
                    sql += " and t1.day=@day";
                    sqlparam.Add("day", day.Value.ToString("yyyy-MM-dd"));
                }

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

                if (!string.IsNullOrEmpty(request.sort))
                {
                    sql += " order by t1."+request.sort+" "+request.order;
                }


                var obj = cn.Page<dc_mid_warehouse_redundancy>(request.pageIndex, request.limit, sql, ref total,sqlparam);

                return obj.AsList();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="warehousetype"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public dc_mid_warehouse_redundancy ListWarehouseRedundancyCount(string warehousetype, string warehousecode,DateTime? day)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = @"select sum(skucount) skucount,sum(productcount) productcount,sum(amount_total) amount_total,sum(skusales_count) skusales_count,(sum(skusales_count)/sum(skucount))skusales_rate,sum(daily_productsale_count) daily_productsale_count,(sum(daily_productsale_count)/sum(productcount)) productsale_rate,sum(dailysale_amount) dailysale_amount,(sum(dailysale_amount)/sum(amount_total)) saleamount_rate, sum(redundancy_sku_count) redundancy_sku_count,(sum(redundancy_sku_count)/sum(skucount)) redundancy_sku_rate,sum(redundancy_product_count) redundancy_product_count,(sum(redundancy_product_count)/sum(productcount)) redundancy_product_rate,sum(redundancy_amount) redundancy_amount,(sum(redundancy_amount)/sum(amount_total)) redundancy_amount_rate  from dc_mid_warehouse_redundancy t1 ";

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

                sql += " where 1=1 ";

                if (day.HasValue)
                {
                    sql += " and t1.day=@day";
                    sqlparam.Add("day", day.Value.ToString("yyyy-MM-dd"));
                }

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

                var obj = cn.QueryFirstOrDefault<dc_mid_warehouse_redundancy>(sql,sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取sku冗余前100的数据
        /// </summary>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public List<List<dc_mid_skuwarehouse_redundancy>> GetSkuRedundancy(string warehousecode,DateTime day)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                //前100冗余金额sku分析
                var sql1 = "select bailun_sku,productcount,amount_total,daily_productsale_count,productsale_rate,dailysale_amount,saleamount_rate,redundancy_product_count,redundancy_product_rate,redundancy_amount,redundancy_amount_rate from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode+ "' and day='"+day.ToString("yyyy-MM-dd")+ "' order by redundancy_amount desc limit 100";

                //前100冗余个数sku分析
                var sql2 = "select bailun_sku,productcount,amount_total,daily_productsale_count,productsale_rate,dailysale_amount,saleamount_rate,redundancy_product_count,redundancy_product_rate,redundancy_amount,redundancy_amount_rate from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by redundancy_product_count desc limit 100";

                //前100个数动销最快sku分析
                var sql3 = "select bailun_sku,productcount,amount_total,yesterdaysale_count,yesterdaysale_amount,sale_3d_avg,sale_7d_avg,sale_14d_avg,sale_30d_avg,day_turnover_rate,week_turnover_rate,month_turnover_rate,stockout from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by productsale_rate desc limit 100";

                //前100金额动销最快sku分析
                var sql4 = "select bailun_sku,productcount,amount_total,yesterdaysale_count,yesterdaysale_amount,sale_3d_avg,sale_7d_avg,sale_14d_avg,sale_30d_avg,day_turnover_rate,week_turnover_rate,month_turnover_rate,stockout from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by saleamount_rate desc limit 100";

                var obj1 = cn.Query<dc_mid_skuwarehouse_redundancy>(sql1);
                var obj2 = cn.Query<dc_mid_skuwarehouse_redundancy>(sql2);
                var obj3 = cn.Query<dc_mid_skuwarehouse_redundancy>(sql3);
                var obj4 = cn.Query<dc_mid_skuwarehouse_redundancy>(sql4);

                var list = new List<List<dc_mid_skuwarehouse_redundancy>>();

                list.Add(obj1.AsList());
                list.Add(obj2.AsList());
                list.Add(obj3.AsList());
                list.Add(obj4.AsList());

                return list;
            }
        }

        /// <summary>
        /// 获取sku冗余前100的数据
        /// </summary>
        /// <param name="warehousecode"></param>
        /// <param name="day"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public List<dc_mid_skuwarehouse_redundancy> GetSkuRedundancyByType(string warehousecode, DateTime day, int? t)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                //前100冗余金额sku分析
                var sql1 = "select bailun_sku,productcount,amount_total,daily_productsale_count,productsale_rate,dailysale_amount,saleamount_rate,redundancy_product_count,redundancy_product_rate,redundancy_amount,redundancy_amount_rate,onway_count,sku_product_code,bailun_sku_name,monitorstatus,tortstatus from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by redundancy_amount desc limit 100";

                //前100冗余个数sku分析
                var sql2 = "select bailun_sku,productcount,amount_total,daily_productsale_count,productsale_rate,dailysale_amount,saleamount_rate,redundancy_product_count,redundancy_product_rate,redundancy_amount,redundancy_amount_rate,onway_count,sku_product_code,bailun_sku_name,monitorstatus,tortstatus from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by redundancy_product_count desc limit 100";

                //前100个数动销最快sku分析
                var sql3 = "select bailun_sku,productcount,amount_total,yesterdaysale_count,yesterdaysale_amount,sale_3d_avg,sale_7d_avg,sale_14d_avg,sale_30d_avg,day_turnover_rate,week_turnover_rate,month_turnover_rate,stockout,onway_count,sku_product_code,bailun_sku_name,monitorstatus,tortstatus from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by productsale_rate desc limit 100";

                //前100金额动销最快sku分析
                var sql4 = "select bailun_sku,productcount,amount_total,yesterdaysale_count,yesterdaysale_amount,sale_3d_avg,sale_7d_avg,sale_14d_avg,sale_30d_avg,day_turnover_rate,week_turnover_rate,month_turnover_rate,stockout,onway_count,sku_product_code,bailun_sku_name,monitorstatus,tortstatus from dc_mid_skuwarehouse_redundancy where warehouse_code='" + warehousecode + "' and day='" + day.ToString("yyyy-MM-dd") + "' order by saleamount_rate desc limit 100";

                
                var list = new List<dc_mid_skuwarehouse_redundancy>();

                if (t.Value == 0)
                {
                    list = cn.Query<dc_mid_skuwarehouse_redundancy>(sql1).AsList();
                }
                else if (t.Value == 1)
                {
                    list = cn.Query<dc_mid_skuwarehouse_redundancy>(sql2).AsList();
                }
                else if (t.Value == 2)
                {
                    list = cn.Query<dc_mid_skuwarehouse_redundancy>(sql3).AsList();
                }
                else if (t.Value == 3)
                {
                    list = cn.Query<dc_mid_skuwarehouse_redundancy>(sql4).AsList();
                }

                return list;
            }
        }

        /// <summary>
        /// 获取仓库前两个月的冗余数据
        /// </summary>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_mid_warehouse_redundancy> ListOver60DaysWarehouseRedundancy(string warehousecode, DateTime start, DateTime end)
        {
             using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select * from dc_mid_warehouse_redundancy where warehouse_code=@code and day>=@startday and day<=@endday order by day";

                sqlparam.Add("code", warehousecode);
                sqlparam.Add("startday", start);
                sqlparam.Add("endday", end);

                var obj = cn.Query<dc_mid_warehouse_redundancy>(sql, sqlparam);

                return obj.AsList();
            }
        }

        /// <summary>
        /// 获取仓库利润分析报表
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="skucategoryids">sku分类id</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_mid_sku_profit> ListWarehouseProfit(BtTableParameter parameter, string skucategoryids, string warehousecode,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select t1.warehouse_code,t1.warehouse_name,sum(t1.usable_stock) usable_stock,sum(t1.usable_stock_amount) usable_stock_amount,sum(t1.quantity_purchase) quantity_purchase,sum(t1.quantity_purchase_amount) quantity_purchase_amount,sum(t1.quantity_transfer) quantity_transfer,sum(t1.quantity_transfer_amount) quantity_transfer_amount,sum(t1.yd_sales_amount) yd_sales_amount,sum(t1.yd_sales_count) yd_sales_count,sum(t1.yd_sales_stockamount) yd_sales_stockamount,sum(t1.yd_grossprofit) yd_grossprofit,(sum(t1.yd_grossprofit_rate)/count(t1.id)) yd_grossprofit_rate,sum(t1.pre30_sales_amount) pre30_sales_amount,sum(t1.pre30_sales_count) pre30_sales_count,sum(t1.pre30_stockamount) pre30_stockamount,sum(t1.pre30_grossprofit) pre30_grossprofit,(sum(t1.pre30_grossprofit_rate)/count(t1.id)) pre30_grossprofit_rate,t1.pre30_return_rate,(sum(t1.pre30_stockamount_turn_rate)/count(t1.id)) pre30_stockamount_turn_rate,sum(t1.pre30_stockamount_add) pre30_stockamount_add,(sum(t1.pre30_stockamount__rate)/count(t1.id)) pre30_stockamount__rate,(sum(t1.pre30_stocknum_turn_rate)/count(t1.id)) pre30_stocknum_turn_rate,sum(t1.pre30_stock_add_count) pre30_stock_add_count,(sum(t1.pre30_stocknum_rate)/count(t1.id)) pre30_stocknum_rate,(sum(t1.pre30_stockamount_vendibility_day)/count(t1.id)) pre30_stockamount_vendibility_day,(sum(t1.pre30_stocknum_vendibility_day)/count(t1.id)) pre30_stocknum_vendibility_day  from dc_mid_sku_profit t1 ";

                if (!string.IsNullOrEmpty(skucategoryids))
                {
                    sql += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                    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(",", arr);
                        if (!string.IsNullOrEmpty(s))
                        {
                            sql += " and t4.category_simple_id in (" + s + ") ";
                        }
                    }
                }

                sql += " where 1=1 ";

                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and t1.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }

                sql += " group by t1.warehouse_code";

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

                var obj = cn.Page<dc_mid_sku_profit>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam, "", 2 * 60);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 获取仓库利润分析报表汇总
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="skucategoryids">sku分类id</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public dc_mid_sku_profit ListWarehouseProfitCount(string skucategoryids, string warehousecode)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select sum(t1.usable_stock) usable_stock,sum(t1.usable_stock_amount) usable_stock_amount,sum(t1.quantity_purchase) quantity_purchase,sum(t1.quantity_purchase_amount) quantity_purchase_amount,sum(t1.quantity_transfer) quantity_transfer,sum(t1.quantity_transfer_amount) quantity_transfer_amount,sum(t1.yd_sales_amount) yd_sales_amount,sum(t1.yd_sales_count) yd_sales_count,sum(t1.yd_sales_stockamount) yd_sales_stockamount,sum(t1.yd_grossprofit) yd_grossprofit,(sum(t1.yd_grossprofit_rate)/count(t1.id)) yd_grossprofit_rate,sum(t1.pre30_sales_amount) pre30_sales_amount,sum(t1.pre30_sales_count) pre30_sales_count,sum(t1.pre30_stockamount) pre30_stockamount,sum(t1.pre30_grossprofit) pre30_grossprofit,(sum(t1.pre30_grossprofit_rate)/count(t1.id)) pre30_grossprofit_rate,t1.pre30_return_rate,(sum(t1.pre30_stockamount_turn_rate)/count(t1.id)) pre30_stockamount_turn_rate,sum(t1.pre30_stockamount_add) pre30_stockamount_add,(sum(t1.pre30_stockamount__rate)/count(t1.id)) pre30_stockamount__rate,(sum(t1.pre30_stocknum_turn_rate)/count(t1.id)) pre30_stocknum_turn_rate,sum(t1.pre30_stock_add_count) pre30_stock_add_count,(sum(t1.pre30_stocknum_rate)/count(t1.id)) pre30_stocknum_rate,(sum(t1.pre30_stockamount_vendibility_day)/count(t1.id)) pre30_stockamount_vendibility_day,(sum(t1.pre30_stocknum_vendibility_day)/count(t1.id)) pre30_stocknum_vendibility_day  from dc_mid_sku_profit t1 ";

                if (!string.IsNullOrEmpty(skucategoryids))
                {
                    sql += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                    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(",", arr);
                        if (!string.IsNullOrEmpty(s))
                        {
                            sql += " and t4.category_simple_id in (" + s + ") ";
                        }
                    }
                }

                sql += " where 1=1 ";

                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and t1.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }
                
                var obj = cn.QueryFirstOrDefault<dc_mid_sku_profit>(sql,sqlparam, null,2*60);
                return obj;
            }
        }

        /// <summary>
        /// 获取sku利润分析报表
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="skucategoryids"></param>
        /// <param name="warehousecode"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_mid_sku_profit> ListSkuProfit(BtTableParameter parameter, string skucategoryids, string warehousecode, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select t1.warehouse_code,t1.warehouse_name,t1.usable_stock,t1.usable_stock_amount,t1.quantity_purchase,t1.quantity_purchase_amount,t1.quantity_transfer,t1.quantity_transfer_amount,t1.yd_sales_amount,t1.yd_sales_count,t1.yd_sales_stockamount,t1.yd_grossprofit,t1.yd_grossprofit_rate,t1.pre30_sales_amount,t1.pre30_sales_count,t1.pre30_stockamount,t1.pre30_grossprofit,t1.pre30_grossprofit_rate,t1.pre30_return_rate,t1.pre30_stockamount_turn_rate,t1.pre30_stockamount_add,t1.pre30_stockamount__rate,t1.pre30_stocknum_turn_rate,t1.pre30_stock_add_count,t1.pre30_stocknum_rate,t1.pre30_stockamount_vendibility_day,t1.pre30_stocknum_vendibility_day,t1.bailun_sku,t1.category_name  from dc_mid_sku_profit t1 ";

                if (!string.IsNullOrEmpty(skucategoryids))
                {
                    sql += " join dc_base_sku t4 on t1.bailun_sku=t4.bailun_sku ";
                    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(",", arr);
                        if (!string.IsNullOrEmpty(s))
                        {
                            sql += " and t4.category_simple_id in (" + s + ") ";
                        }
                    }
                }

                sql += " where 1=1 ";

                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and t1.warehouse_code=@warehouse_code";
                    sqlparam.Add("warehouse_code", warehousecode);
                }

                if (!string.IsNullOrEmpty(parameter.sort))
                {
                    sql += " order by t1." + parameter.sort + " " + parameter.order;
                }
                else
                {
                    sql += " order by t1.pre30_sales_amount desc";
                }

                var obj = cn.Page<dc_mid_sku_profit>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam, "", 2 * 60);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 获取sku+仓库每日采购下单数、入库数、发货数和库存变化报表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="isfinish">是否成品，1：成品，0：半成品</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_daily_purchase_sales> ListDailyPurchaseSale(BtTableParameter parameter,string warehousetype , string warehouse_code, DateTime start, DateTime end,string skucategoryids,int isfinish, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = $"select max(t1.day) day,t1.warehouse_code,t1.bailun_sku,t1.warehouse_name,sum(t1.count_sales) count_sales,sum(t1.amount_sales) amount_sales,sum(t1.count_purchase) count_purchase,sum(t1.amount_purchase) amount_purchase,sum(t1.count_putin) count_putin,sum(t1.amount_putin) amount_putin,sum(t1.count_shipping) count_shipping,sum(t1.amount_shipping) amount_shipping,sum(t1.difference_count) difference_count,sum(t1.difference_amount) difference_amount,sum(t1.diff_putin_count) diff_putin_count,sum(t1.diff_putin_amount) diff_putin_amount from dc_daily_purchase_sales t1 ";

            var sqlwhere = "";
            if (!string.IsNullOrEmpty(warehousetype))
            {
                sqlwhere += " join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);
            }

            if (!string.IsNullOrEmpty(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a=>!string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    sqlwhere += " join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku and category_simple_id in ("+(string.Join(",",arr))+")";
                }
            }


            sqlwhere += $" where t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<'{end.AddDays(1).ToString("yyyy-MM-dd")}' and t1.isfinish={isfinish} ";

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

            sqlwhere += " group by t1.warehouse_code,t1.bailun_sku";

            sql += sqlwhere;

            sql = "select t01.*,t03.stock_end,t03.stockamount_end,t02.stockamount_start,t02.stock_start from (" + sql + ") t01 left join dc_daily_purchase_sales t02 on t01.warehouse_code=t02.warehouse_code and t01.bailun_sku=t02.bailun_sku and t02.day='"+start.ToString("yyyy-MM-dd")+ "' and t02.isfinish="+isfinish;
            sql += " left join dc_daily_purchase_sales t03 on t01.warehouse_code=t03.warehouse_code and t01.bailun_sku=t03.bailun_sku and t03.day='" + end.ToString("yyyy-MM-dd") + "' and t03.isfinish="+isfinish;

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

            sql += " limit "+(parameter.pageIndex-1)*parameter.limit+","+parameter.limit;
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                total = cn.QueryFirstOrDefault<int>("select count(*) from (select count(t1.id) from dc_daily_purchase_sales t1 "+sqlwhere+") tt",sqlparam);
                var obj = cn.Query<dc_daily_purchase_sales>(sql, sqlparam,null,true,2*60);
                return obj.AsList();
            }
        }

        /// <summary>
        /// 获取sku+仓库每日采购下单数、入库数、发货数和库存变化报表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public dc_daily_purchase_sales ListDailyPurchaseSaleCount(string warehousetype, string warehouse_code,DateTime start, DateTime end, string skucategoryids,int isfinish)
        {
            var sqlparam = new DynamicParameters();
            var sql = $"select sum(stock_start) stock_start,sum(stockamount_start) stockamount_start,sum(stock_end) stock_end,sum(stockamount_end) stockamount_end,sum(count_sales) count_sales,sum(amount_sales) amount_sales,sum(count_purchase) count_purchase,sum(amount_purchase) amount_purchase,sum(count_putin) count_putin,sum(amount_putin) amount_putin,sum(t1.count_shipping) count_shipping,sum(t1.amount_shipping) amount_shipping,sum(difference_count) difference_count,sum(difference_amount) difference_amount,sum(diff_putin_count) diff_putin_count,sum(diff_putin_amount) diff_putin_amount from dc_daily_purchase_sales t1 ";

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

            if (!string.IsNullOrEmpty(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    sql += " join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku and category_simple_id in (" + (string.Join(",", arr)) + ")";
                }
            }

            var sqlwhere = $" where  t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<'{end.AddDays(1).ToString("yyyy-MM-dd")}' and t1.isfinish="+isfinish;

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

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }
                
                var obj = cn.QueryFirstOrDefault<dc_daily_purchase_sales>(sql + sqlwhere,sqlparam,null,2*60);
                return obj;
            }
        }

        /// <summary>
        /// 按简单分类统计sku每日进销差异  （成品）
        /// </summary>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public List<dc_daily_purchase_sales> DailyPurchaseSaleBySkuCategory(string warehousetype, string warehouse_code, DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select t1.day,t2.category_simple_name,sum(t1.count_sales) count_sales,sum(t1.amount_sales) amount_sales,sum(t1.count_purchase) count_purchase,sum(t1.amount_purchase) amount_purchase,sum(count_putin) count_putin,sum(amount_putin) amount_putin,sum(count_shipping) count_shipping,sum(t1.amount_shipping) amount_shipping  from dc_daily_purchase_sales t1";
            sql += " join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku ";

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

            sql += $" where t1.isfinish=1 and t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<'{end.AddDays(1).ToString("yyyy-MM-dd")}' ";

            if (!string.IsNullOrEmpty(warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse_code);
            }

            sql += " group by t1.day,t2.category_simple_name";

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

                var obj = cn.Query<dc_daily_purchase_sales>(sql, sqlparam, null, true, 2 * 60).AsList();

                return obj;
            }
        }

        /// <summary>
        /// sku每日进销差异  （半成品）
        /// </summary>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public List<dc_daily_purchase_sales> DailyPurchaseSaleBySkuCategory_Semi(string warehousetype, string warehouse_code, DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select t1.day,'原料' category_simple_name,sum(t1.count_sales) count_sales,sum(t1.amount_sales) amount_sales,sum(t1.count_purchase) count_purchase,sum(t1.amount_purchase) amount_purchase,sum(count_putin) count_putin,sum(amount_putin) amount_putin,sum(count_shipping) count_shipping,sum(t1.amount_shipping) amount_shipping  from dc_daily_purchase_sales t1";

            sql += $" where t1.isfinish=0 and t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<'{end.AddDays(1).ToString("yyyy-MM-dd")}' ";

            if (!string.IsNullOrEmpty(warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse_code);
            }

            sql += " group by t1.day";

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

                var obj = cn.Query<dc_daily_purchase_sales>(sql, sqlparam, null, true, 2 * 60).AsList();

                return obj;
            }

        }




        /// <summary>
        /// 按车间获取半成品的每日销售进销比
        /// </summary>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public List<dc_daily_purchase_sales> DailyPurchaseSaleByWH_Semi(DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();

            var sql = $@"select t1.warehouse_name as category_simple_name,sum(t1.count_sales) count_sales,sum(t1.amount_sales) amount_sales,sum(t1.count_purchase) count_purchase,sum(t1.amount_purchase) amount_purchase,sum(count_putin) count_putin,sum(amount_putin) amount_putin,sum(count_shipping) count_shipping,sum(t1.amount_shipping) amount_shipping from dc_daily_purchase_sales t1 
                        where t1.isfinish=0 and warehouse_code>0 and t1.day>='{start.ToString("yyyy-MM-dd")}' and t1.day<'{end.AddDays(1).ToString("yyyy-MM-dd")}'
                        ";

            sql += " group by t1.warehouse_name";
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_daily_purchase_sales>(sql, sqlparam, null, true, 2 * 60).ToList();
                return obj;
            }
        }

        /// <summary>
        /// 半成品仓库列表
        /// </summary>
        /// <returns></returns>
        public List<Tuple<string, string>> ListSemiWareHouse()
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_semi_daily_purchase_sell_stock>("select warehouse_code,warehouse_name from dc_semi_daily_purchase_sell_stock group by warehouse_code");

                return obj.Select(a => new Tuple<string, string>(a.warehouse_code, a.warehouse_name)).ToList();
            }
        }

        /// <summary>
        /// 获取逻辑仓的每日数据，用于显示趋势图
        /// </summary>
        /// <param name="warehouse_code"></param>
        /// <param name="columnname"></param>
        /// <returns></returns>
        public List<string> GetMidLogicWarehouseStatistic(string warehouse_code, string columnname,DateTime start,DateTime end)
        {
            var sql = "select CONCAT(name,',',DATE_FORMAT(day,'%Y-%m-%d')) as name from (";

            if (columnname.Trim() == "pin_rate_stock")
            {
                sql += "select sum(thirtyday_total_sales)*100/sum(usable_stock) as name,day from dc_daily_logic_wh_statistic where warehouse_code='" + warehouse_code + "' ";
            }
            else
            {
                sql += "select sum(" + columnname + ") as name,day from dc_daily_logic_wh_statistic  where warehouse_code='" + warehouse_code + "' ";
            }


            sql += $" and day>='{start.ToString("yyyy-MM-dd")}' and day<='{end.ToString("yyyy-MM-dd")}' group by day";

            sql += " ) t1  ";

            sql += " order by day";

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

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

        /// <summary>
        /// 获取逻辑仓的每日数据，用于显示趋势图(汇总的)
        /// </summary>
        /// <param name="warehouse_code"></param>
        /// <param name="columnname"></param>
        /// <returns></returns>
        public List<string> GetMidLogicWarehouseStatisticCount(string columnname, DateTime start, DateTime end)
        {
            var sql = "select CONCAT(name,',',DATE_FORMAT(day,'%Y-%m-%d')) as name from ("; 
            

            if(columnname.Trim()== "pin_rate_stock")
            {
                sql += "select sum(thirtyday_total_sales)*100/sum(usable_stock) as name,day from dc_daily_logic_wh_statistic where ";
            }
            else
            {
                sql += "select sum(" + columnname + ") as name,day from dc_daily_logic_wh_statistic where ";
            }

            sql += $" day>='{start.ToString("yyyy-MM-dd")}' and day<='{end.ToString("yyyy-MM-dd")}' group by day";

            sql += " ) t1  ";

            sql += " order by day";

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

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



        #region 动态库存分析

        public List<mDynamicStockAnalyse> ListDynamicStockAnalyse(string warehouse_code, int isfinish)
        {
            var sql = $@"select t1.warehouse_code,
                            sum(case when t1.available_days<15 then 1 else 0 end) as less15days_skucount,
                            sum(case when t1.available_days<15 then t1.usable_stock else 0 end) as less15days,
                            sum(case when t1.available_days<15 then t1.amount_stock else 0 end) as less15days_amount,
                            sum(case when t1.available_days>=15 and t1.available_days<30 then 1 else 0 end) as fifteen_30_days_skucount,
                            sum(case when t1.available_days>=15 and t1.available_days<30 then t1.usable_stock else 0 end) as fifteen_30_days,
                            sum(case when t1.available_days>=15 and t1.available_days<30 then t1.amount_stock else 0 end) as fifteen_30_days_amount,
                            sum(case when t1.available_days>=30 and t1.available_days<45 then 1 else 0 end) as thirty_45_days_skucount,
                            sum(case when t1.available_days>=30 and t1.available_days<45 then t1.usable_stock else 0 end) as thirty_45_days,
                            sum(case when t1.available_days>=30 and t1.available_days<45 then t1.amount_stock else 0 end) as thirty_45_days_amount,
                            sum(case when t1.available_days>=45 and t1.available_days<60 then 1 else 0 end) as fortyfive_60_days_skucount,
                            sum(case when t1.available_days>=45 and t1.available_days<60 then t1.usable_stock else 0 end) as fortyfive_60_days,
                            sum(case when t1.available_days>=45 and t1.available_days<60 then t1.amount_stock else 0 end) as fortyfive_60_days_amount,
                            sum(case when t1.available_days>=60 and t1.available_days<90 then 1 else 0 end) as sixty_90_days_skucount,
                            sum(case when t1.available_days>=60 and t1.available_days<90 then t1.usable_stock else 0 end) as sixty_90_days,
                            sum(case when t1.available_days>=60 and t1.available_days<90 then t1.amount_stock else 0 end) as sixty_90_days_amount,
                            sum(case when t1.available_days>=90 and t1.available_days<120 then 1 else 0 end) as ninty_120_days_skucount,
                            sum(case when t1.available_days>=90 and t1.available_days<120 then t1.usable_stock else 0 end) as ninty_120_days,
                            sum(case when t1.available_days>=90 and t1.available_days<120 then t1.amount_stock else 0 end) as ninty_120_days_amount,
                            sum(case when t1.available_days>=120 and t1.available_days<180 then 1 else 0 end) as onetwozero_180_days_skucount,
                            sum(case when t1.available_days>=120 and t1.available_days<180 then t1.usable_stock else 0 end) as onetwozero_180_days,
                            sum(case when t1.available_days>=120 and t1.available_days<180 then t1.amount_stock else 0 end) as onetwozero_180_days_amount,
                            sum(case when t1.available_days>=180 then 1 else 0 end) as morethen_180_days_skucount,
                            sum(case when t1.available_days>=180 then t1.usable_stock else 0 end) as morethen_180_days,
                            sum(case when t1.available_days>=180 then t1.amount_stock else 0 end) as morethen_180_days_amount
                            from dc_skuwarehouse_stock_sales t1
                            where t1.available_days>0 and t1.warehouse_code='{warehouse_code}'
                            group by t1.warehouse_code";

            #region 注释

            //if (isfinish == 1)  //成品
            //{
            //    sql = $@"select t1.warehouse_code,
            //                sum(case when t1.available_days<15 then 1 else 0 end) as less15days_skucount,
            //                sum(case when t1.available_days<15 then t1.usable_stock+t1.occupy_stock else 0 end) as less15days,
            //                sum(case when t1.available_days<15 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as less15days_amount,
            //                sum(case when t1.available_days>=15 and t1.available_days<30 then 1 else 0 end) as fifteen_30_days_skucount,
            //                sum(case when t1.available_days>=15 and t1.available_days<30 then t1.usable_stock+t1.occupy_stock else 0 end) as fifteen_30_days,
            //                sum(case when t1.available_days>=15 and t1.available_days<30 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as fifteen_30_days_amount,
            //                sum(case when t1.available_days>=30 and t1.available_days<45 then 1 else 0 end) as thirty_45_days_skucount,
            //                sum(case when t1.available_days>=30 and t1.available_days<45 then t1.usable_stock+t1.occupy_stock else 0 end) as thirty_45_days,
            //                sum(case when t1.available_days>=30 and t1.available_days<45 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as thirty_45_days_amount,
            //                sum(case when t1.available_days>=45 and t1.available_days<60 then 1 else 0 end) as fortyfive_60_days_skucount,
            //                sum(case when t1.available_days>=45 and t1.available_days<60 then t1.usable_stock+t1.occupy_stock else 0 end) as fortyfive_60_days,
            //                sum(case when t1.available_days>=45 and t1.available_days<60 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as fortyfive_60_days_amount,
            //                sum(case when t1.available_days>=60 and t1.available_days<90 then 1 else 0 end) as sixty_90_days_skucount,
            //                sum(case when t1.available_days>=60 and t1.available_days<90 then t1.usable_stock+t1.occupy_stock else 0 end) as sixty_90_days,
            //                sum(case when t1.available_days>=60 and t1.available_days<90 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as sixty_90_days_amount,
            //                sum(case when t1.available_days>=90 and t1.available_days<120 then 1 else 0 end) as ninty_120_days_skucount,
            //                sum(case when t1.available_days>=90 and t1.available_days<120 then t1.usable_stock+t1.occupy_stock else 0 end) as ninty_120_days,
            //                sum(case when t1.available_days>=90 and t1.available_days<120 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as ninty_120_days_amount,
            //                sum(case when t1.available_days>=120 and t1.available_days<180 then 1 else 0 end) as onetwozero_180_days_skucount,
            //                sum(case when t1.available_days>=120 and t1.available_days<180 then t1.usable_stock+t1.occupy_stock else 0 end) as onetwozero_180_days,
            //                sum(case when t1.available_days>=120 and t1.available_days<180 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as onetwozero_180_days_amount,
            //                sum(case when t1.available_days>=180 then 1 else 0 end) as morethen_180_days_skucount,
            //                sum(case when t1.available_days>=180 then t1.usable_stock+t1.occupy_stock else 0 end) as morethen_180_days,
            //                sum(case when t1.available_days>=180 then (t1.usable_stock+t1.occupy_stock)*t2.unit_price else 0 end) as morethen_180_days_amount
            //                from dc_base_stock t1
            //                left join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
            //                where t1.available_days>0 and t1.warehouse_code='{warehouse_code}'
            //                group by t1.warehouse_code";

            //}
            //else if(isfinish==0)  //半成品
            //{
            //    sql = $@"select t1.warehouse_id as warehouse_code,
            //                sum(case when t1.available_days<15 then 1 else 0 end) as less15days_skucount,
            //                sum(case when t1.available_days<15 then t1.stock else 0 end) as less15days,
            //                sum(case when t1.available_days<15 then (t1.stock)*t1.buyer_price else 0 end) as less15days_amount,
            //                sum(case when t1.available_days>=15 and t1.available_days<30 then 1 else 0 end) as fifteen_30_days_skucount,
            //                sum(case when t1.available_days>=15 and t1.available_days<30 then t1.stock else 0 end) as fifteen_30_days,
            //                sum(case when t1.available_days>=15 and t1.available_days<30 then (t1.stock)*t1.buyer_price else 0 end) as fifteen_30_days_amount,
            //                sum(case when t1.available_days>=30 and t1.available_days<45 then 1 else 0 end) as thirty_45_days_skucount,
            //                sum(case when t1.available_days>=30 and t1.available_days<45 then t1.stock else 0 end) as thirty_45_days,
            //                sum(case when t1.available_days>=30 and t1.available_days<45 then (t1.stock)*t1.buyer_price else 0 end) as thirty_45_days_amount,
            //                sum(case when t1.available_days>=45 and t1.available_days<60 then 1 else 0 end) as fortyfive_60_days_skucount,
            //                sum(case when t1.available_days>=45 and t1.available_days<60 then t1.stock else 0 end) as fortyfive_60_days,
            //                sum(case when t1.available_days>=45 and t1.available_days<60 then (t1.stock)*t1.buyer_price else 0 end) as fortyfive_60_days_amount,
            //                sum(case when t1.available_days>=60 and t1.available_days<90 then 1 else 0 end) as sixty_90_days_skucount,
            //                sum(case when t1.available_days>=60 and t1.available_days<90 then t1.stock else 0 end) as sixty_90_days,
            //                sum(case when t1.available_days>=60 and t1.available_days<90 then (t1.stock)*t1.buyer_price else 0 end) as sixty_90_days_amount,
            //                sum(case when t1.available_days>=90 and t1.available_days<120 then 1 else 0 end) as ninty_120_days_skucount,
            //                sum(case when t1.available_days>=90 and t1.available_days<120 then t1.stock else 0 end) as ninty_120_days,
            //                sum(case when t1.available_days>=90 and t1.available_days<120 then (t1.stock)*t1.buyer_price else 0 end) as ninty_120_days_amount,
            //                sum(case when t1.available_days>=120 and t1.available_days<180 then 1 else 0 end) as onetwozero_180_days_skucount,
            //                sum(case when t1.available_days>=120 and t1.available_days<180 then t1.stock else 0 end) as onetwozero_180_days,
            //                sum(case when t1.available_days>=120 and t1.available_days<180 then (t1.stock)*t1.buyer_price else 0 end) as onetwozero_180_days_amount,
            //                sum(case when t1.available_days>=180 then 1 else 0 end) as morethen_180_days_skucount,
            //                sum(case when t1.available_days>=180 then t1.stock else 0 end) as morethen_180_days,
            //                sum(case when t1.available_days>=180 then (t1.stock)*t1.buyer_price else 0 end) as morethen_180_days_amount
            //                from dc_semi_stock_sales_shipping t1
            //                where t1.available_days>0 and t1.warehouse_id={warehouse_code}
            //                group by t1.warehouse_id";


            //}

            #endregion

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

                var list = cn.Query<mDynamicStockAnalyse>(sql).ToList();

                return list;
            }
        }

        #endregion



        #region Api

        /// <summary>
        /// 获取SKU+仓库的库存和销量信息
        /// </summary>
        /// <param name="skus"></param>
        /// <returns></returns>
        public List<Models.Api.mSkuStock> ListSkuStock(List<string> skus)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $@"select t1.warehouse_code,t2.warehouse_name,t2.hq_type,t1.bailun_sku,t1.usable_stock,(t1.usable_stock*t3.unit_price) amount,t4.quantity_purchase as purchase_transfer,t4.quantity_transfer as allot_transfer,t4.quantity_transfer_plan as allot_plan_transfer,t4.quantity_out_stock,t5.oneday_total_sales,t5.sevenday_average_sales,t5.thirtyday_average_sales from dc_base_stock t1
                            join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                            join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku
                            left join dc_mid_transit t4 on t1.bailun_sku=t4.bailun_sku and t1.warehouse_code=t4.warehouse_code
                            left join dc_daily_sales t5 on t1.bailun_sku=t5.bailun_sku and t1.warehouse_code=t5.warehouse_code and t5.record_date=DATE_SUB(curdate(),INTERVAL 1 DAY)
                            where t1.bailun_sku in ('{string.Join("','",skus)}')";

                var obj = cn.Query<mSkuStock>(sql).ToList();

                return obj;
            }

        }

        #endregion
    }
}
