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

namespace Bailun.DC.Services
{
    /// <summary>
    /// 库存相关服务
    /// </summary>
    public class InventoryServices
    {

        /// <summary>
        /// Sku库存分析报表列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="sku">sku编码</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="month">月份</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Stock.mSkuStockAnalyze> ListSkuStockAnalyze(BtTableParameter parameter,string sku,string warehouse,DateTime? month,ref int total)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select t0.warehouse_code,t0.warehouse_name,t0.bailun_sku,t0.quantity_begin_month_inventory,t0.quantity_purchase_transit,t0.quantity_transfer_transit,t0.quantity_purchase_inbound,t0.quantity_purchase_inbound*t0.purchase_price as purchase_inbound_amount,t0.quantity_transfer_inbound,t0.quantity_transfer_inbound*t0.purchase_price as transfer_inbound_amount,t0.quantity_damaged,t0.quantity_overflow,t0.quantity_sku_changed,t0.quantity_sales,t0.quantity_end_month_inventory,t0.turnover_rate,t0.turnover_date,t0.inv_age_0to1_month,t0.inv_age_1to3_month,t0.inv_age_3to6_month,t0.inv_age_6to12_month,t0.inv_age_1to2_year,t0.inv_age_2to3_year,t0.inv_age_gt3_year,t0.quantity_instant_inventory,t0.quantity_instant_inventory*t0.purchase_price as  instant_inventory_amount,t0.month_total_exports,t0.month_total_exports*t0.purchase_price as exportamount,t0.quantity_inner_purchase,t0.purchase_price ";

            var sqlwhere = @"from (
                                            select* from dc_month_inventory where warehouse_name <> ''  group by warehouse_code,bailun_sku having MAX(record_time)
                                        ) as t0  where 1 = 1 ";


            if (!string.IsNullOrEmpty(sku))
            {
                sqlwhere += (" and t0.bailun_sku=@sku");
                sqlparam.Add("sku", sku);
            }

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

            if (month.HasValue)
            {
                var monthEnd = month.Value.AddMonths(1);

                sqlwhere += (" and t0.record_time>=@monthstart and t0.record_time<@monthend");
                sqlparam.Add("monthstart", month.Value);
                sqlparam.Add("monthend", monthEnd);
            }

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

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

                var obj = cn.Page<Models.Stock.mSkuStockAnalyze>(parameter.pageIndex, parameter.limit, sql + sqlwhere, ref total, sqlparam, "select count(t0.id) "+sqlwhere);

                return obj.AsList();

            }
        }

        /// <summary>
        /// Sku库存分析报表 统计
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public Models.Stock.mSkuStockAnalyze ListSkuStockAnalyzeCount(string sku,string warehouse,DateTime? month)
        {
            var sqlparam = new DynamicParameters();

            var sql = @"select sum(t0.quantity_purchase_transit) quantity_purchase_transit,sum(t0.quantity_transfer_transit) quantity_transfer_transit,sum(t0.quantity_purchase_inbound) quantity_purchase_inbound,sum(t0.quantity_purchase_inbound*t0.purchase_price) as purchase_inbound_amount,sum(t0.quantity_transfer_inbound) quantity_transfer_inbound,sum(t0.quantity_transfer_inbound*t0.purchase_price) as transfer_inbound_amount,sum(t0.quantity_damaged) as quantity_damaged,sum(t0.quantity_overflow) quantity_overflow,sum(t0.quantity_sku_changed) quantity_sku_changed,sum(t0.quantity_sales) quantity_sales,t0.turnover_rate,t0.turnover_date,sum(t0.inv_age_0to1_month) inv_age_0to1_month,(t0.inv_age_1to3_month) inv_age_1to3_month,sum(t0.inv_age_3to6_month) inv_age_3to6_month,sum(t0.inv_age_6to12_month) inv_age_6to12_month,sum(t0.inv_age_1to2_year) inv_age_1to2_year,sum(t0.inv_age_2to3_year),sum(t0.inv_age_gt3_year) inv_age_gt3_year,sum(t0.quantity_instant_inventory) quantity_instant_inventory,sum(t0.quantity_instant_inventory*t0.purchase_price) as  instant_inventory_amount,sum(t0.month_total_exports) month_total_exports,sum(t0.month_total_exports*t0.purchase_price) as exportamount,sum(t0.quantity_inner_purchase) quantity_inner_purchase ";

            var sqlwhere = @"from (
                                            select* from dc_month_inventory where warehouse_name <> ''  group by warehouse_code,bailun_sku having MAX(record_time)
                                        ) as t0  where 1 = 1 ";


            if (!string.IsNullOrEmpty(sku))
            {
                sqlwhere += (" and t0.bailun_sku=@sku");
                sqlparam.Add("sku", sku);
            }

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

            if (month.HasValue)
            {
                var monthEnd = month.Value.AddMonths(1);

                sqlwhere += (" and t0.record_time>=@monthstart and t0.record_time<@monthend");
                sqlparam.Add("monthstart", month.Value);
                sqlparam.Add("monthend", monthEnd);
            }

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

                try
                {
                    var obj = cn.QueryFirst<Models.Stock.mSkuStockAnalyze>(sql + sqlwhere, sqlparam, null, 20);

                    return obj;
                }
                catch (Exception ex)
                {

                    return new mSkuStockAnalyze();
                }
                

            }

        }


        /// <summary>
        /// 保存仓库冗余数据 （Sku+仓库维度）
        /// </summary>
        /// <returns></returns>
        public string SaveSkuWarehouseRedundancy_backup(string filepath)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var liststock = cn.Query<Models.Warehouse.mStock>("select bailun_sku,warehouse_code,usable_stock from dc_base_stock");
                var tb = new System.Data.DataTable();
                tb.Columns.Add("id", typeof(int));
                tb.Columns.Add("bailun_sku");
                tb.Columns.Add("bailun_sku_name");
                tb.Columns.Add("warehouse_code");
                tb.Columns.Add("warehouse_name");
                tb.Columns.Add("productcount");
                tb.Columns.Add("amount_total");
                tb.Columns.Add("daily_productsale_count");
                tb.Columns.Add("dailysale_amount");
                tb.Columns.Add("productsale_rate");
                tb.Columns.Add("saleamount_rate");
                tb.Columns.Add("redundancy_product_count");
                tb.Columns.Add("redundancy_product_rate");
                tb.Columns.Add("redundancy_amount");
                tb.Columns.Add("redundancy_amount_rate");
                tb.Columns.Add("yesterdaysale_amount");
                tb.Columns.Add("yesterdaysale_count");

                tb.Columns.Add("sale_3d_avg");
                tb.Columns.Add("sale_7d_avg");
                tb.Columns.Add("sale_14d_avg");
                tb.Columns.Add("sale_30d_avg");
                tb.Columns.Add("day_turnover_rate");
                tb.Columns.Add("week_turnover_rate");
                tb.Columns.Add("month_turnover_rate");
                tb.Columns.Add("stockout");
                tb.Columns.Add("createtime");
                tb.Columns.Add("day");

                foreach (var item in liststock)
                {
                    try
                    {
                        if (cn.State == System.Data.ConnectionState.Closed)
                        {
                            cn.Open();
                        }

                        var objsku = cn.QueryFirstOrDefault<Models.Warehouse.mSkuInfo>("select id,bailun_sku,unit_price,sku_title_cn from dc_base_sku where bailun_sku='" + item.bailun_sku + "'");
                        if (objsku == null)
                        {
                            continue;
                        }

                        var objWarehouse = cn.QueryFirstOrDefault<Models.dc_base_warehouse>("select id,warehouse_name from dc_base_warehouse where warehouse_code='" + item.warehouse_code + "'");
                        if (objWarehouse == null)
                        {
                            continue;
                        }

                        var date = DateTime.Parse(DateTime.Now.AddDays(-7).ToShortDateString());
                        var obj7SaleAvg = cn.QueryFirstOrDefault<decimal?>("select sum(bailun_sku_quantity_ordered) from dc_base_oms_sku where bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "' and bailun_order_status!='Canceled' and paid_time>='" + date.ToString("yyyy-MM-dd") + "' and paid_time<'" + date.AddDays(7).ToString("yyyy-MM-dd") + "'");
                        var objRedundancy = cn.QueryFirstOrDefault<int?>("select quantity from dc_auto_forecast_fluctuation where type=3 and bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "'");
                        var objYesterdaySale = cn.QueryFirstOrDefault<int?>("select sum(bailun_sku_quantity_ordered) from dc_base_oms_sku where bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "' and bailun_order_status!='Canceled' and paid_time>='" + date.AddDays(5).ToString("yyyy-MM-dd") + "' and paid_time<'" + date.AddDays(6).ToString("yyyy-MM-dd") + "'");
                        var obj3SaleAvg = cn.QueryFirstOrDefault<decimal?>("select sum(bailun_sku_quantity_ordered) from dc_base_oms_sku where bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "' and bailun_order_status!='Canceled' and paid_time>='" + date.AddDays(4).ToString("yyyy-MM-dd") + "' and paid_time<'" + date.AddDays(7).ToString("yyyy-MM-dd") + "'");
                        var obj14SaleAvg = cn.QueryFirstOrDefault<decimal?>("select sum(bailun_sku_quantity_ordered) from dc_base_oms_sku where bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "' and bailun_order_status!='Canceled' and paid_time>='" + date.AddDays(-7).ToString("yyyy-MM-dd") + "' and paid_time<'" + date.AddDays(7).ToString("yyyy-MM-dd") + "'");
                        var obj30SaleAvg = cn.QueryFirstOrDefault<decimal?>("select sum(bailun_sku_quantity_ordered) from dc_base_oms_sku where bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "' and bailun_order_status!='Canceled' and paid_time>='" + date.AddDays(-23).ToString("yyyy-MM-dd") + "' and paid_time<'" + date.AddDays(7).ToString("yyyy-MM-dd") + "'");
                        var objStockout = cn.QueryFirstOrDefault<int?>("select quantity_out_stock from dc_mid_transit where bailun_sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "'");

                        var m = new Models.Warehouse.dc_mid_skuwarehouse_redundancy();
                        m.bailun_sku = item.bailun_sku;
                        m.bailun_sku_name = objsku.sku_title_cn;
                        m.warehouse_code = item.warehouse_code;
                        m.warehouse_name = objWarehouse.warehouse_name;
                        m.productcount = item.usable_stock;
                        m.amount_total = item.usable_stock * objsku.unit_price;

                        m.daily_productsale_count = obj7SaleAvg ?? 0;
                        m.dailysale_amount = (obj7SaleAvg ?? 0) * objsku.unit_price;
                        m.productsale_rate = item.usable_stock > 0 ? (obj7SaleAvg ?? 0) / item.usable_stock * 7 : 0;
                        m.saleamount_rate = item.usable_stock > 0 ? (obj7SaleAvg ?? 0) / item.usable_stock * 7 : 0;

                        m.redundancy_product_count = objRedundancy ?? 0;
                        m.redundancy_product_rate = item.usable_stock > 0 ? (objRedundancy ?? 0) / item.usable_stock : 0;
                        m.redundancy_amount = (objRedundancy ?? 0) * objsku.unit_price;
                        m.redundancy_amount_rate = item.usable_stock > 0 ? (objRedundancy ?? 0) / item.usable_stock : 0;

                        m.yesterdaysale_amount = (objYesterdaySale ?? 0) * objsku.unit_price;
                        m.yesterdaysale_count = (objYesterdaySale ?? 0);

                        m.sale_3d_avg = (obj3SaleAvg ?? 0) / 3;
                        m.sale_7d_avg = (obj7SaleAvg ?? 0) / 7;
                        m.sale_14d_avg = (obj14SaleAvg ?? 0) / 14;
                        m.sale_30d_avg = (obj30SaleAvg ?? 0) / 30;

                        m.day_turnover_rate = item.usable_stock > 0 ? objYesterdaySale ?? 0 / item.usable_stock : 0;
                        m.week_turnover_rate = item.usable_stock > 0 ? (obj7SaleAvg ?? 0) / item.usable_stock * 7 : 0;
                        m.month_turnover_rate = item.usable_stock > 0 ? (obj30SaleAvg ?? 0) / item.usable_stock * 30 : 0;
                        m.stockout = objStockout ?? 0;

                        m.createtime = DateTime.Now;
                        m.day = DateTime.Now.AddDays(-1);

                        //MySqlBulkLoader loader = new MySqlBulkLoader(cn);

                        var row = tb.NewRow();
                        row["id"] = 0;
                        row["bailun_sku"] = item.bailun_sku;
                        row["bailun_sku_name"] = objsku.sku_title_cn;
                        row["warehouse_code"] = item.warehouse_code;
                        row["warehouse_name"] = objWarehouse.warehouse_name;
                        row["productcount"] = item.usable_stock;
                        row["amount_total"] = item.usable_stock * objsku.unit_price;

                        row["daily_productsale_count"] = obj7SaleAvg ?? 0;
                        row["dailysale_amount"] = (obj7SaleAvg ?? 0) * objsku.unit_price;
                        row["productsale_rate"] = item.usable_stock > 0 ? (obj7SaleAvg ?? 0) / item.usable_stock * 7 : 0;
                        row["saleamount_rate"] = item.usable_stock > 0 ? (obj7SaleAvg ?? 0) / item.usable_stock * 7 : 0;

                        row["redundancy_product_count"] = objRedundancy ?? 0;
                        row["redundancy_product_rate"] = item.usable_stock > 0 ? (objRedundancy ?? 0) / item.usable_stock : 0;
                        row["redundancy_amount"] = (objRedundancy ?? 0) * objsku.unit_price;
                        row["redundancy_amount_rate"] = item.usable_stock > 0 ? (objRedundancy ?? 0) / item.usable_stock : 0;

                        row["yesterdaysale_amount"] = (objYesterdaySale ?? 0) * objsku.unit_price;
                        row["yesterdaysale_count"] = (objYesterdaySale ?? 0);

                        row["sale_3d_avg"] = (obj3SaleAvg ?? 0) / 3;
                        row["sale_7d_avg"] = (obj7SaleAvg ?? 0) / 7;
                        row["sale_14d_avg"] = (obj14SaleAvg ?? 0) / 14;
                        row["sale_30d_avg"] = (obj30SaleAvg ?? 0) / 30;

                        row["day_turnover_rate"] = item.usable_stock > 0 ? objYesterdaySale ?? 0 / item.usable_stock : 0;
                        row["week_turnover_rate"] = item.usable_stock > 0 ? (obj7SaleAvg ?? 0) / item.usable_stock * 7 : 0;
                        row["month_turnover_rate"] = item.usable_stock > 0 ? (obj30SaleAvg ?? 0) / item.usable_stock * 30 : 0;
                        row["stockout"] = objStockout ?? 0;

                        row["createtime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        row["day"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");

                        tb.Rows.Add(row);
                    }
                    catch (Exception ex)
                    {

                    }
                }

                var str = DataTableToCsv(tb);
                System.IO.File.WriteAllText(filepath, str);

                MySqlBulkLoader loader = new MySqlBulkLoader(cn)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    FileName = filepath,
                    NumberOfLinesToSkip = 0,
                    TableName = "dc_mid_skuwarehouse_redundancy",
                };

                var count =loader.Load();

                System.IO.File.Delete(filepath);
            }

            return "";
        }

        /// <summary>
        /// 保存仓库冗余数据 （Sku+仓库维度）
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public string SaveSkuWarehouseRedundancy(string filepath)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }
                
                var tb = new System.Data.DataTable();
                tb.Columns.Add("id", typeof(int));
                tb.Columns.Add("bailun_sku");
                tb.Columns.Add("bailun_sku_name");
                tb.Columns.Add("warehouse_code");
                tb.Columns.Add("warehouse_name");
                tb.Columns.Add("productcount");
                tb.Columns.Add("amount_total");
                tb.Columns.Add("daily_productsale_count");
                tb.Columns.Add("dailysale_amount");
                tb.Columns.Add("productsale_rate");
                tb.Columns.Add("saleamount_rate");
                tb.Columns.Add("redundancy_product_count");
                tb.Columns.Add("redundancy_product_rate");
                tb.Columns.Add("redundancy_amount");
                tb.Columns.Add("redundancy_amount_rate");
                tb.Columns.Add("yesterdaysale_amount");
                tb.Columns.Add("yesterdaysale_count");

                tb.Columns.Add("sale_3d_avg");
                tb.Columns.Add("sale_7d_avg");
                tb.Columns.Add("sale_14d_avg");
                tb.Columns.Add("sale_30d_avg");
                tb.Columns.Add("day_turnover_rate");
                tb.Columns.Add("week_turnover_rate");
                tb.Columns.Add("month_turnover_rate");
                tb.Columns.Add("stockout");
                tb.Columns.Add("day");
                tb.Columns.Add("createtime");
                tb.Columns.Add("onway_count");
                tb.Columns.Add("sku_product_code");
                tb.Columns.Add("bailun_category_id");
                tb.Columns.Add("bailun_category_name");
                tb.Columns.Add("monitorstatus");
                tb.Columns.Add("tortstatus");
                var page = 1;
                var pagesize = 10000;
                var listcount = cn.QueryFirstOrDefault<int>("select count(id) from dc_base_stock");
                var resultcount = 0;

                while (page == 1 || (resultcount == pagesize && resultcount != 0))
                {
                    var strlimit = (((page - 1) * pagesize) + "," + pagesize); 
                    var liststock = cn.Query<Models.Warehouse.mWarehouseRedundancy>($@"select t1.bailun_sku,t1.warehouse_code,t1.usable_stock,t2.sku_title_cn,t2.unit_price,t3.warehouse_name,t4.quantity,t5.quantity_out_stock,t6.oneday_total_sales,t6.threeday_total_sales,t6.sevenday_total_sales,t6.fourteenday_total_sales,t6.thirtyday_total_sales,t6.threeday_average_sales,t6.sevenday_average_sales,t6.fourteenday_average_sales,t6.thirtyday_average_sales,t5.quantity_purchase,t5.quantity_transfer,t2.product_code,t2.bailun_category_id,t2.bailun_category_name,t7.status as 'monitorstatus',t8.tortstatus 
                                                                                                                                    from dc_base_stock t1
                                                                                                                                    join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                                                                                                                                    join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                                                                                                                                    left join dc_auto_daily_redundance t4 on t4.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.bailun_sku=t4.bailun_sku and t1.warehouse_code=t4.warehouse_code -- 仓库冗余
                                                                                                                                    left join dc_mid_transit t5 on t1.bailun_sku=t5.bailun_sku and t1.warehouse_code=t5.warehouse_code
                                                                                                                                    left join dc_daily_sales t6 on t1.bailun_sku=t6.bailun_sku and t6.record_date='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.warehouse_code=t6.warehouse_code
                                                                                                                                    left join dc_auto_config_sku_warehouse t7 on t7.bailun_sku=t1.bailun_sku and t7.warehouse_code=t1.warehouse_code
                                                                                                                                    left join dc_sku_monitor t8 on t8.sku=t1.bailun_sku and t8.warehousecode=t1.warehouse_code
                                                                                                                                    limit {strlimit}", null, null, true, 30 * 60);

                    resultcount = liststock.AsList().Count;
                    page++;

                    foreach (var item in liststock)
                    {
                        var onwaycount = (item.quantity_purchase ?? 0 + item.quantity_transfer ?? 0);
                        var stockcount = (item.usable_stock + onwaycount);  //可用库存+在途数

                        var row = tb.NewRow();
                        row["id"] = 0;
                        row["bailun_sku"] = item.bailun_sku;
                        row["bailun_sku_name"] = item.sku_title_cn;
                        row["warehouse_code"] = item.warehouse_code;
                        row["warehouse_name"] = item.warehouse_name;
                        row["productcount"] = stockcount;
                        row["amount_total"] = stockcount * item.unit_price;

                        row["daily_productsale_count"] = item.sevenday_average_sales ?? 0;
                        row["dailysale_amount"] = (item.sevenday_total_sales ?? 0) * item.unit_price;
                        row["productsale_rate"] = stockcount > 0 ? (item.sevenday_average_sales ?? 0) / (decimal)stockcount : 0;
                        row["saleamount_rate"] = stockcount > 0 ? (item.sevenday_average_sales ?? 0) / (decimal)stockcount : 0;

                        row["redundancy_product_count"] = item.quantity ?? 0;
                        row["redundancy_product_rate"] = stockcount > 0 ? (decimal)(item.quantity ?? 0) / (decimal)stockcount : 0;
                        row["redundancy_amount"] = (item.quantity ?? 0) * item.unit_price;
                        row["redundancy_amount_rate"] = stockcount > 0 ? (decimal)(item.quantity ?? 0) / (decimal)stockcount : 0;

                        row["yesterdaysale_amount"] = (item.oneday_total_sales ?? 0) * item.unit_price;
                        row["yesterdaysale_count"] = (item.oneday_total_sales ?? 0);

                        row["sale_3d_avg"] = (item.threeday_average_sales ?? 0) / 3;
                        row["sale_7d_avg"] = (item.sevenday_average_sales ?? 0) / 7;
                        row["sale_14d_avg"] = (item.fourteenday_average_sales ?? 0) / 14;
                        row["sale_30d_avg"] = (item.thirtyday_average_sales ?? 0) / 30;

                        row["day_turnover_rate"] = stockcount > 0 ? item.oneday_total_sales ?? 0 / (decimal)stockcount : 0;
                        row["week_turnover_rate"] = stockcount > 0 ? (item.sevenday_average_sales ?? 0) / (decimal)stockcount * 7 : 0;
                        row["month_turnover_rate"] = stockcount > 0 ? (item.thirtyday_average_sales ?? 0) / (decimal)stockcount * 30 : 0;
                        row["stockout"] = item.quantity_out_stock ?? 0;

                        row["day"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                        row["createtime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        row["onway_count"] = onwaycount;
                        row["sku_product_code"] = item.product_code;
                        row["bailun_category_id"] = item.bailun_category_id;
                        row["bailun_category_name"] = item.bailun_category_name;

                        row["monitorstatus"] = item.monitorstatus ?? 0;
                        row["tortstatus"] = item.tortstatus ?? 0;
                        tb.Rows.Add(row);
                    }

                    try
                    {
                        var str = DataTableToCsv(tb);

                        if (!System.IO.Directory.Exists(filepath))
                        {
                            System.IO.Directory.CreateDirectory(filepath);
                        }

                        var filename = filepath+"redundancy.csv";

                        if (System.IO.File.Exists(filename))
                        {
                            System.IO.File.Delete(filename);
                        }


                        System.IO.File.WriteAllText(filename, str);

                        MySqlBulkLoader loader = new MySqlBulkLoader(cn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = filename,
                            NumberOfLinesToSkip = 0,
                            TableName = "dc_mid_skuwarehouse_redundancy",
                        };

                        var count = loader.Load();

                        System.Threading.Thread.Sleep(2 * 1000);

                    }
                    catch (Exception ex)
                    {
                        return ex.Message;
                    }
                }

                try
                {
                    //生成仓库维度的冗余报告
                    cn.Execute("call Proc_SaveWarehouseRedundancy",null,null,2*60);
                }
                catch(Exception ex)
                {
                    Common.LogHelper.Info("SaveSkuWarehouseRedundancy", ex.Message);
                    cn.Execute("call Proc_SaveWarehouseRedundancy", null, null, 2 * 60);
                }
            }

            return "";
        }

        public string SaveSkuWarehouseSales(string filepath)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var tb = new System.Data.DataTable();
                tb.Columns.Add("id", typeof(int));
                tb.Columns.Add("bailun_sku");
                tb.Columns.Add("bailun_sku_name");
                tb.Columns.Add("warehouse_code");
                tb.Columns.Add("warehouse_name");
                tb.Columns.Add("warehouse_type");
                tb.Columns.Add("stock_count");
                tb.Columns.Add("stock_amount");
                tb.Columns.Add("sku_developtime");
                tb.Columns.Add("aliexpress_salecount");
                tb.Columns.Add("aliexpress_saleamount");
                tb.Columns.Add("amazon_salecount");
                tb.Columns.Add("amazon_saleamount");
                tb.Columns.Add("fba_salecount");
                tb.Columns.Add("fba_saleamount");
                tb.Columns.Add("ebay_salecount");
                tb.Columns.Add("ebay_saleamount");

                tb.Columns.Add("amazon2B_salecount");
                tb.Columns.Add("amazon2B_saleamount");
                tb.Columns.Add("jollychic_salecount");
                tb.Columns.Add("jollychic_saleamount");
                tb.Columns.Add("joom_salecount");
                tb.Columns.Add("joom_saleamount");
                tb.Columns.Add("marketing_salecount");
                tb.Columns.Add("marketing_saleamount");
                tb.Columns.Add("mymall_salecount");
                tb.Columns.Add("mymall_saleamount");

                tb.Columns.Add("opensky_salecount");
                tb.Columns.Add("opensky_saleamount");
                tb.Columns.Add("priceminister_salecount");
                tb.Columns.Add("priceminister_saleamount");
                tb.Columns.Add("qoo10_salecount");
                tb.Columns.Add("qoo10_saleamount");
                tb.Columns.Add("shopee_salecount");
                tb.Columns.Add("shopee_saleamount");
                tb.Columns.Add("shopify_salecount");
                tb.Columns.Add("shopify_saleamount");
                tb.Columns.Add("walmart_salecount");
                tb.Columns.Add("walmart_saleamount");

                tb.Columns.Add("wish_salecount");
                tb.Columns.Add("wish_saleamount");
                tb.Columns.Add("day");
                tb.Columns.Add("createtime");
                tb.Columns.Add("salecount");

                var page = 1;
                var pagesize = 100000;
                var listcount = cn.QueryFirstOrDefault<int>("select count(id) from dc_base_stock");
                var resultcount = 0;

                while (page == 1 || (resultcount <= pagesize && resultcount != 0))
                {
                    var strlimit = (((page - 1) * pagesize) + "," + pagesize);

                    var sql = @"select t1.bailun_sku,t2.sku_title_cn 'bailun_sku_name',t1.warehouse_code,t3.warehouse_name,t3.hq_type 'warehouse_type',t1.usable_sales 'stock_count',t1.usable_sales*t2.unit_price 'stock_amount',t2.create_time 'sku_developtime',
                                    t4.amount_sales 'aliexpress_saleamount',t4.sales_count 'aliexpress_salecount',t5.amount_sales 'amazon_saleamount',t5.sales_count 'amazon_salecount',t6.amount_sales 'fba_saleamount',t6.sales_count 'fba_salecount',t7.amount_sales 'ebay_saleamount',t7.sales_count 'ebay_salecount',t8.amount_sales 'amazon2B_saleamount',t8.sales_count 'amazon2B_salecount',t9.amount_sales 'jollychic_saleamount',t9.sales_count 'jollychic_salecount',t10.amount_sales 'joom_saleamount',t10.sales_count 'joom_salecount',t11.amount_sales 'marketing_saleamount',t11.sales_count 'marketing_salecount',t12.amount_sales 'mymall_saleamount',t12.sales_count 'mymall_salecount',t13.amount_sales 'opensky_saleamount',t13.sales_count 'opensky_salecount',t14.amount_sales 'priceminister_saleamount',t14.sales_count 'priceminister_salecount',t15.amount_sales 'qoo10_saleamount',t15.sales_count 'qoo10_salecount',t16.amount_sales 'shopee_saleamount',t16.sales_count 'shopee_salecount',t17.amount_sales 'shopify_saleamount',t17.sales_count 'shopify_salecount',t18.amount_sales 'walmart_saleamount',t18.sales_count 'walmart_salecount',t19.amount_sales 'wish_saleamount',t19.sales_count 'wish_salecount'
                                    from dc_base_stock t1
                                    join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku 
                                    join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                                    left join view_sku_sales t4 on t4.platform_type='Aliexpress' and t1.bailun_sku=t4.bailun_sku and t1.warehouse_code=t4.warehouse_code
                                    left join view_sku_sales t5 on t5.platform_type='Amazon' and t1.bailun_sku=t5.bailun_sku and t1.warehouse_code=t5.warehouse_code
                                    left join view_sku_sales t6 on t6.platform_type='FBA' and t1.bailun_sku=t6.bailun_sku and t1.warehouse_code=t6.warehouse_code
                                    left join view_sku_sales t7 on t7.platform_type='Ebay' and t1.bailun_sku=t7.bailun_sku and t1.warehouse_code=t7.warehouse_code 
                                    left join view_sku_sales t8 on t8.platform_type='Amazon2B' and t1.bailun_sku=t8.bailun_sku and t1.warehouse_code=t8.warehouse_code 
                                    left join view_sku_sales t9 on t9.platform_type='Jollychic' and t1.bailun_sku=t9.bailun_sku and t1.warehouse_code=t9.warehouse_code
                                    left join view_sku_sales t10 on t10.platform_type='Joom' and t1.bailun_sku=t10.bailun_sku and t1.warehouse_code=t10.warehouse_code
                                    left join view_sku_sales t11 on t11.platform_type='MARKETING' and t1.bailun_sku=t11.bailun_sku and t1.warehouse_code=t11.warehouse_code
                                    left join view_sku_sales t12 on t12.platform_type='MyMall' and t1.bailun_sku=t12.bailun_sku and t1.warehouse_code=t12.warehouse_code
                                    left join view_sku_sales t13 on t13.platform_type='OpenSky' and t1.bailun_sku=t13.bailun_sku and t1.warehouse_code=t13.warehouse_code
                                    left join view_sku_sales t14 on t14.platform_type='PriceMinister' and t1.bailun_sku=t14.bailun_sku and t1.warehouse_code=t14.warehouse_code
                                    left join view_sku_sales t15 on t15.platform_type='Qoo10' and t1.bailun_sku=t15.bailun_sku and t1.warehouse_code=t15.warehouse_code
                                    left join view_sku_sales t16 on t16.platform_type='Shopee' and t1.bailun_sku=t16.bailun_sku and t1.warehouse_code=t16.warehouse_code
                                    left join view_sku_sales t17 on t17.platform_type='shopify' and t1.bailun_sku=t17.bailun_sku and t1.warehouse_code=t17.warehouse_code
                                    left join view_sku_sales t18 on t18.platform_type='Walmart' and t1.bailun_sku=t18.bailun_sku and t1.warehouse_code=t18.warehouse_code
                                    left join view_sku_sales t19 on t19.platform_type='Wish' and t1.bailun_sku=t19.bailun_sku and t1.warehouse_code=t19.warehouse_code";
                    sql += " limit " + strlimit;

                    var liststock = cn.Query<Models.Warehouse.dc_mid_skuwarehouse_sales>(sql, null, null, true, 30 * 60);

                    resultcount = liststock.AsList().Count;
                    page++;

                    foreach (var item in liststock)
                    {
                        var row = tb.NewRow();
                        row["id"]= 0 ;
                        row["bailun_sku"]=item.bailun_sku;
                        row["bailun_sku_name"]=item.bailun_sku_name;
                        row["warehouse_code"]=item.warehouse_code;
                        row["warehouse_name"]=item.warehouse_name;
                        row["warehouse_type"] = item.warehouse_type;
                        row["stock_count"]=item.stock_count;
                        row["stock_amount"]=item.stock_amount??0;
                        row["sku_developtime"]=item.sku_developtime;
                        row["aliexpress_salecount"]=item.aliexpress_salecount??0;
                        row["aliexpress_saleamount"]=item.aliexpress_saleamount??0;
                        row["amazon_salecount"]=item.amazon_salecount??0;
                        row["amazon_saleamount"]=item.amazon_saleamount??0;
                        row["fba_salecount"]=item.fba_salecount??0;
                        row["fba_saleamount"]=item.fba_saleamount??0;
                        row["ebay_salecount"] = item.ebay_salecount??0;
                        row["ebay_saleamount"]=item.ebay_saleamount??0;

                        row["amazon2B_salecount"]=item.amazon2B_salecount??0;
                        row["amazon2B_saleamount"] = item.amazon2B_saleamount??0;
                        row["jollychic_salecount"] = item.jollychic_salecount??0;
                        row["jollychic_saleamount"] = item.jollychic_saleamount??0;
                        row["joom_salecount"] = item.joom_salecount??0;
                        row["joom_saleamount"] = item.joom_saleamount??0;
                        row["marketing_salecount"] = item.marketing_salecount??0;
                        row["marketing_saleamount"]=item.marketing_saleamount??0;
                        row["mymall_salecount"]=item.mymall_salecount??0;
                        row["mymall_saleamount"] = item.mymall_saleamount??0;

                        row["opensky_salecount"] = item.opensky_salecount??0;
                        row["opensky_saleamount"] = item.opensky_saleamount??0;
                        row["priceminister_salecount"] = item.priceminister_salecount??0;
                        row["priceminister_saleamount"] = item.priceminister_saleamount??0;
                        row["qoo10_salecount"] = item.qoo10_salecount??0;
                        row["qoo10_saleamount"] = item.qoo10_saleamount??0;
                        row["shopee_salecount"] = item.shopee_salecount??0;
                        row["shopee_saleamount"] = item.shopee_saleamount??0;
                        row["shopify_salecount"] = item.shopify_salecount??0;
                        row["shopify_saleamount"] = item.shopify_saleamount??0;
                        row["walmart_salecount"] = item.walmart_salecount??0;
                        row["walmart_saleamount"] = item.walmart_saleamount??0;

                        row["wish_salecount"] = item.wish_salecount??0;
                        row["wish_saleamount"] = item.wish_saleamount??0;
                        row["day"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                        row["createtime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        row["salecount"] = (item.aliexpress_salecount ?? 0) + (item.amazon_salecount ?? 0) + (item.fba_salecount ?? 0) + (item.ebay_salecount ?? 0) + (item.amazon2B_salecount ?? 0) + (item.jollychic_salecount ?? 0) + (item.joom_salecount ?? 0) + (item.marketing_salecount ?? 0) + (item.mymall_salecount ?? 0) + (item.opensky_salecount ?? 0) + (item.priceminister_salecount ?? 0) + (item.qoo10_salecount ?? 0) + (item.shopee_salecount ?? 0) + (item.shopify_salecount ?? 0) + (item.walmart_salecount ?? 0) + (item.wish_salecount ?? 0);

                        tb.Rows.Add(row);
                    }

                    try
                    {
                        var str = DataTableToCsv(tb);
                        System.IO.File.Delete(filepath);
                        System.IO.File.WriteAllText(filepath, str);

                        MySqlBulkLoader loader = new MySqlBulkLoader(cn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = filepath,
                            NumberOfLinesToSkip = 0,
                            TableName = "dc_mid_skuwarehouse_sales",
                        };

                        var count = loader.Load();

                        System.Threading.Thread.Sleep(5 * 1000);

                    }
                    catch (Exception ex)
                    {

                    }

                }

            }

            return "";
        }

        private static string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。  
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。  
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。  
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }


            return sb.ToString();
        }

        #region 数据仓sku销售分析

        /// <summary>
        /// 数据仓 sku销售分析 仓库维度
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="startcount">销量开始</param>
        /// <param name="endcount">销量结束</param>
        /// <param name="devstart">开发开始时间</param>
        /// <param name="devend">开发结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Warehouse.dc_mid_skuwarehouse_sales> ListWarehouseSales(Models.BtTableParameter parameter,string warehousetype,string warehousecode,int? startcount,int? endcount,DateTime? devstart,DateTime? devend,DateTime? start,DateTime? end, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select warehouse_code,warehouse_name,warehouse_type,sum(stock_count) stock_count,sum(stock_amount) stock_amount,sum(aliexpress_salecount) aliexpress_salecount,sum(aliexpress_saleamount) aliexpress_saleamount,sum(amazon_salecount) amazon_salecount, sum(amazon_saleamount) amazon_saleamount,sum(fba_salecount) fba_salecount,sum(fba_saleamount) fba_saleamount,sum(ebay_salecount) ebay_salecount,sum(ebay_saleamount) ebay_saleamount,sum(amazon2B_salecount) amazon2B_salecount,sum(amazon2B_saleamount) amazon2B_saleamount,sum(jollychic_salecount) jollychic_salecount,sum(jollychic_saleamount) jollychic_saleamount, sum(joom_salecount) joom_salecount,sum(joom_saleamount) joom_saleamount,sum(marketing_salecount) marketing_salecount,sum(marketing_saleamount) marketing_saleamount,sum(mymall_salecount) mymall_salecount,sum(mymall_saleamount) mymall_saleamount,sum(opensky_salecount) opensky_salecount,sum(opensky_saleamount) opensky_saleamount,sum(priceminister_salecount) priceminister_salecount,sum(priceminister_saleamount) priceminister_saleamount,sum(qoo10_salecount) qoo10_salecount,sum(qoo10_saleamount) qoo10_saleamount,sum(shopee_salecount) shopee_salecount,sum(shopee_saleamount) shopee_saleamount,sum(shopify_salecount) shopify_salecount,sum(shopify_saleamount) shopify_saleamount,sum(walmart_salecount) walmart_salecount,sum(walmart_saleamount) walmart_saleamount,sum(wish_salecount) wish_salecount,sum(wish_saleamount) wish_saleamount,sum(salecount) salecount from dc_mid_skuwarehouse_sales where 1=1 ";

                if (!string.IsNullOrEmpty(warehousetype))
                {
                    sql += " and warehouse_type=@warehouse_type";
                    sqlparam.Add("warehouse_type", warehousetype);
                }
                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and warehouse_code=@warehousecode";
                    sqlparam.Add("warehousecode", warehousecode);
                }
                if(devstart.HasValue)
                {
                    sql += " and sku_developtime>=@devstart";
                    sqlparam.Add("devstart", devstart.Value);
                }
                if (devend.HasValue)
                {
                    sql += " and sku_developtime<@devend";
                    sqlparam.Add("devend", devend.Value.AddDays(1));
                }

                if (startcount.HasValue)
                {
                    sql += " and salecount>=@startcount";
                    sqlparam.Add("startcount", startcount.Value);
                }
                if (endcount.HasValue)
                {
                    sql += " and salecount<@endcount";
                    sqlparam.Add("endcount", endcount.Value);
                }

                if (start.HasValue)
                {
                    sql += " and day>=@start";
                    sqlparam.Add("start", start.Value);
                }
                if (end.HasValue)
                {
                    sql += " and day<@end";
                    sqlparam.Add("end", end.Value.AddDays(1));
                }


                sql += " group by warehouse_code ";

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

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

                return obj.AsList();
            }
        }

        /// <summary>
        /// 数据仓 sku销售分析 仓库维度  统计
        /// </summary>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="startcount">销量开始</param>
        /// <param name="endcount">销量结束</param>
        /// <param name="devstart">开发开始时间</param>
        /// <param name="devend">开发结束时间</param>
        /// <returns></returns>
        public dc_mid_skuwarehouse_sales ListWarehouseSalesCount(string warehousetype, string warehousecode, int? startcount, int? endcount, DateTime? devstart, DateTime? devend,DateTime? start, DateTime? end)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select sum(stock_count) stock_count,sum(stock_amount) stock_amount,sum(aliexpress_salecount) aliexpress_salecount,sum(aliexpress_saleamount) aliexpress_saleamount,sum(amazon_salecount) amazon_salecount, sum(amazon_saleamount) amazon_saleamount,sum(fba_salecount) fba_salecount,sum(fba_saleamount) fba_saleamount,sum(ebay_salecount) ebay_salecount,sum(ebay_saleamount) ebay_saleamount,sum(amazon2B_salecount) amazon2B_salecount,sum(amazon2B_saleamount) amazon2B_saleamount,sum(jollychic_salecount) jollychic_salecount,sum(jollychic_saleamount) jollychic_saleamount, sum(joom_salecount) joom_salecount,sum(joom_saleamount) joom_saleamount,sum(marketing_salecount) marketing_salecount,sum(marketing_saleamount) marketing_saleamount,sum(mymall_salecount) mymall_salecount,sum(mymall_saleamount) mymall_saleamount,sum(opensky_salecount) opensky_salecount,sum(opensky_saleamount) opensky_saleamount,sum(priceminister_salecount) priceminister_salecount,sum(priceminister_saleamount) priceminister_saleamount,sum(qoo10_salecount) qoo10_salecount,sum(qoo10_saleamount) qoo10_saleamount,sum(shopee_salecount) shopee_salecount,sum(shopee_saleamount) shopee_saleamount,sum(shopify_salecount) shopify_salecount,sum(shopify_saleamount) shopify_saleamount,sum(walmart_salecount) walmart_salecount,sum(walmart_saleamount) walmart_saleamount,sum(wish_salecount) wish_salecount,sum(wish_saleamount) wish_saleamount,sum(salecount) salecount from dc_mid_skuwarehouse_sales where 1=1 ";

                if (!string.IsNullOrEmpty(warehousetype))
                {
                    sql += " and warehouse_type=@warehouse_type";
                    sqlparam.Add("warehouse_type", warehousetype);
                }
                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and warehouse_code=@warehousecode";
                    sqlparam.Add("warehousecode", warehousecode);
                }
                if (devstart.HasValue)
                {
                    sql += " and sku_developtime>=@devstart";
                    sqlparam.Add("devstart", devstart.Value);
                }
                if (devend.HasValue)
                {
                    sql += " and sku_developtime<@devend";
                    sqlparam.Add("devend", devend.Value.AddDays(1));
                }

                if (startcount.HasValue)
                {
                    sql += " and salecount>=@startcount";
                    sqlparam.Add("startcount", startcount.Value);
                }
                if (endcount.HasValue)
                {
                    sql += " and salecount<@endcount";
                    sqlparam.Add("endcount", endcount.Value);
                }

                if (start.HasValue)
                {
                    sql += " and day>=@start";
                    sqlparam.Add("start", start.Value);
                }
                if (end.HasValue)
                {
                    sql += " and day<@end";
                    sqlparam.Add("end", end.Value.AddDays(1));
                }

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

                return obj;
            }
        }

        /// <summary>
        /// 数据仓 销量分析报表 sku维度
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="startcount">销量开始</param>
        /// <param name="endcount">销量结束</param>
        /// <param name="devstart">开发开始时间</param>
        /// <param name="devend">开发结束时间</param>
        /// <param name="start">统计开始时间</param>
        /// <param name="end">统计结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_mid_skuwarehouse_sales> ListSkuWarehouseSales(BtTableParameter parameter, string warehousecode, int? startcount, int? endcount, DateTime? devstart, DateTime? devend, DateTime? start, DateTime? end,string bailun_sku, ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select * from dc_mid_skuwarehouse_sales where 1=1 ";

                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and warehouse_code=@warehousecode";
                    sqlparam.Add("warehousecode", warehousecode);
                }
                if (devstart.HasValue)
                {
                    sql += " and sku_developtime>=@devstart";
                    sqlparam.Add("devstart", devstart.Value);
                }
                if (devend.HasValue)
                {
                    sql += " and sku_developtime<@devend";
                    sqlparam.Add("devend", devend.Value.AddDays(1));
                }

                if (startcount.HasValue)
                {
                    sql += " and salecount>=@startcount";
                    sqlparam.Add("startcount", startcount.Value);
                }
                if (endcount.HasValue)
                {
                    sql += " and salecount<@endcount";
                    sqlparam.Add("endcount", endcount.Value);
                }

                if (start.HasValue)
                {
                    sql += " and day>=@start";
                    sqlparam.Add("start", start.Value);
                }
                if (end.HasValue)
                {
                    sql += " and day<@end";
                    sqlparam.Add("end", end.Value.AddDays(1));
                }

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

                if (parameter.limit > 0)
                {
                    if (!string.IsNullOrEmpty(parameter.sort))
                    {
                        sql += " order by " + parameter.sort + " " + parameter.order;
                    }
                    else
                    {
                        sql += " order by stock_count desc";
                    }

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

                    return obj.AsList();
                }
                else
                {
                    var obj = cn.Query<dc_mid_skuwarehouse_sales>(sql, sqlparam, null, true, 10 * 60).AsList();
                    return obj;
                }
            }
        }

        /// <summary>
        /// 数据仓 销量分析报表 sku维度 统计
        /// </summary>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="startcount">销量开始</param>
        /// <param name="endcount">销量结束</param>
        /// <param name="devstart">开发开始时间</param>
        /// <param name="devend">开发结束时间</param>
        /// <param name="start">统计开始时间</param>
        /// <param name="end">统计结束时间</param>
        /// <returns></returns>
        public dc_mid_skuwarehouse_sales ListSkuWarehouseSalesCount(string warehousecode, int? startcount, int? endcount, DateTime? devstart, DateTime? devend, DateTime? start, DateTime? end, string bailun_sku)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sqlparam = new DynamicParameters();
                var sql = "select sum(stock_count) stock_count,sum(stock_amount) stock_amount,sum(aliexpress_salecount) aliexpress_salecount,sum(aliexpress_saleamount) aliexpress_saleamount,sum(amazon_salecount) amazon_salecount, sum(amazon_saleamount) amazon_saleamount,sum(fba_salecount) fba_salecount,sum(fba_saleamount) fba_saleamount,sum(ebay_salecount) ebay_salecount,sum(ebay_saleamount) ebay_saleamount,sum(amazon2B_salecount) amazon2B_salecount,sum(amazon2B_saleamount) amazon2B_saleamount,sum(jollychic_salecount) jollychic_salecount,sum(jollychic_saleamount) jollychic_saleamount, sum(joom_salecount) joom_salecount,sum(joom_saleamount) joom_saleamount,sum(marketing_salecount) marketing_salecount,sum(marketing_saleamount) marketing_saleamount,sum(mymall_salecount) mymall_salecount,sum(mymall_saleamount) mymall_saleamount,sum(opensky_salecount) opensky_salecount,sum(opensky_saleamount) opensky_saleamount,sum(priceminister_salecount) priceminister_salecount,sum(priceminister_saleamount) priceminister_saleamount,sum(qoo10_salecount) qoo10_salecount,sum(qoo10_saleamount) qoo10_saleamount,sum(shopee_salecount) shopee_salecount,sum(shopee_saleamount) shopee_saleamount,sum(shopify_salecount) shopify_salecount,sum(shopify_saleamount) shopify_saleamount,sum(walmart_salecount) walmart_salecount,sum(walmart_saleamount) walmart_saleamount,sum(wish_salecount) wish_salecount,sum(wish_saleamount) wish_saleamount,sum(salecount) salecount from dc_mid_skuwarehouse_sales where 1=1 ";

                if (!string.IsNullOrEmpty(warehousecode))
                {
                    sql += " and warehouse_code=@warehousecode";
                    sqlparam.Add("warehousecode", warehousecode);
                }
                if (devstart.HasValue)
                {
                    sql += " and sku_developtime>=@devstart";
                    sqlparam.Add("devstart", devstart.Value);
                }
                if (devend.HasValue)
                {
                    sql += " and sku_developtime<@devend";
                    sqlparam.Add("devend", devend.Value.AddDays(1));
                }

                if (startcount.HasValue)
                {
                    sql += " and salecount>=@startcount";
                    sqlparam.Add("startcount", startcount.Value);
                }
                if (endcount.HasValue)
                {
                    sql += " and salecount<@endcount";
                    sqlparam.Add("endcount", endcount.Value);
                }

                if (start.HasValue)
                {
                    sql += " and day>=@start";
                    sqlparam.Add("start", start.Value);
                }
                if (end.HasValue)
                {
                    sql += " and day<@end";
                    sqlparam.Add("end", end.Value.AddDays(1));
                }

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

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

                return obj;
            }
        }

        #endregion
        
        #region 库龄

        /// <summary>
        /// sku库龄列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="sku">sku编码</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="isfinish">1：成品，2：半成品</param>
        /// <param name="category">Sku产品分类</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_mid_stock_age> ListStockDate(BtTableParameter parameter, string sku, string warehousetype, string warehouse,DateTime? start,DateTime? end,string buyername,int? isfinish,string category, ref int total)
        {
            var sqlparam = new DynamicParameters();
            //var sql = "select t1.bailun_sku,t1.warehouse_code,t2.warehouse_name,sum(t1.quantity_stock) as quantity_stock,sum(t1.quantity_stock*t1.unit_price) as amount,stock_time from dc_mid_stock_date t1 ";
            var sql = "select t1.id,t1.bailun_sku,t3.sku_title_cn as sku_name,t1.warehouse_code,t1.warehouse_name,t1.stock,t3.unit_price,(t1.stock*t3.unit_price) as amount,t1.`day`,t1.warehouse_type,t1.sku_category from dc_mid_stock_age t1";
            var sqlwhere = " ";
            sqlwhere += " left join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku ";

            sqlwhere += " where t1.stock>0 ";

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

            if (!string.IsNullOrEmpty(buyername))
            {
                sqlwhere += " and t3.buyer_name=@";
                sqlparam.Add("buyer_name", buyername);
            }

            
            if (!string.IsNullOrEmpty(sku))
            {
                sqlwhere += " and t1.bailun_sku = @sku";
                sqlparam.Add("sku", sku);
            }
            if (!string.IsNullOrEmpty(warehouse))
            {
                sqlwhere += " and t1.warehouse_code=@warehouse";
                sqlparam.Add("warehouse", warehouse);
            }
            if (start.HasValue)
            {
                sqlwhere += " and t1.day>=@start";
                sqlparam.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sqlwhere += " and t1.day<@end";
                sqlparam.Add("end", end.Value.AddDays(1));
            }


            if (isfinish.HasValue)
            {
                if (isfinish.Value == 1) //成品
                {
                    sqlwhere += " and t1.warehouse_type!='半成品仓'";
                }
                else if (isfinish.Value == 2) //原料
                {
                    sqlwhere += " and t1.warehouse_type='半成品仓'";
                }
            }

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


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

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

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

                if (parameter.limit == 0)
                {
                    var obj = cn.Query<dc_mid_stock_age>(sql + sqlwhere + sqlsort, sqlparam, null, true, 2 * 60).AsList();
                    return obj;
                }
                else
                {
                    var obj = cn.Page<dc_mid_stock_age>(parameter.pageIndex, parameter.limit, sql + sqlwhere + sqlsort, ref total, sqlparam, "select count(*) from (select t1.bailun_sku,t1.warehouse_code from dc_mid_stock_age t1 " + sqlwhere + ") tb", 2 * 60).AsList();

                    return obj;
                }
                
            }
        }

        /// <summary>
        /// sku库龄列表汇总
        /// </summary>
        /// <param name="sku">sku编码</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public dc_mid_stock_age ListStockDateCount(string sku, string warehousetype, string warehouse, DateTime? start, DateTime? end,string buyername, int? isfinish, string category)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select sum(t1.stock) as stock,sum(t1.stock*t3.unit_price) as amount from dc_mid_stock_age t1 ";

            var sqlwhere = " ";
            sqlwhere += " left join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku ";

            sqlwhere += " where t1.stock>0 ";

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

            if (!string.IsNullOrEmpty(buyername))
            {
                sqlwhere += " and t3.buyer_name=@";
                sqlparam.Add("buyer_name", buyername);
            }



            if (!string.IsNullOrEmpty(sku))
            {
                sqlwhere += " and t1.bailun_sku = @sku";
                sqlparam.Add("sku", sku);
            }
            if (!string.IsNullOrEmpty(warehouse))
            {
                sqlwhere += " and t1.warehouse_code=@warehouse";
                sqlparam.Add("warehouse", warehouse);
            }
            if (start.HasValue)
            {
                sqlwhere += " and t1.day>=@start";
                sqlparam.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sqlwhere += " and t1.day<@end";
                sqlparam.Add("end", end.Value.AddDays(1));
            }


            if (isfinish.HasValue)
            {
                if (isfinish.Value == 1) //成品
                {
                    sqlwhere += " and t1.warehouse_type!='半成品仓'";
                }
                else if (isfinish.Value == 2) //原料
                {
                    sqlwhere += " and t1.warehouse_type='半成品仓'";
                }
            }

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

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

                var obj = cn.QueryFirstOrDefault<dc_mid_stock_age>(sql+sqlwhere,sqlparam,null,2 * 60);

                return obj;
            }
        }

        /// <summary>
        /// 获取库龄详情数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public dc_mid_stock_age GetStockAge(int id)
        {
            var sql = "select * from dc_mid_stock_age where id="+id;
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                return cn.QueryFirstOrDefault<dc_mid_stock_age>(sql);
            }
        }

        /// <summary>
        /// 根据sku+仓库获取库龄信息
        /// </summary>
        /// <param name="skus">sku集合</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <returns></returns>
        public List<dc_mid_stock_age> GetSkuStockAge(List<string> skus, string warehousecode)
        {
            var sql = $"select * from dc_mid_stock_age where 1=1 ";

            if(skus.Count>0)
            {
                sql += $" and bailun_sku in ('{string.Join("','", skus)}')";
            }

            if(string.IsNullOrEmpty(warehousecode))
            {
                sql += "and warehouse_code='{warehousecode}' ";
            }


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

                var list = cn.Query<dc_mid_stock_age>(sql).AsList();

                return list;
            }

        }


        #endregion

        #region 进销存

        public List<mStockSellStorage> ListStockSellWarehouse(BtTableParameter parameter, string warehousetype, string warehouse, DateTime start, DateTime end,ref int total)
        {
            var sql = "";

            return new List<mStockSellStorage>();
        }

        #endregion

        #region 库存

        /// <summary>
        /// 获取sku的实时总库存
        /// </summary>
        /// <param name="skus"></param>
        /// <returns></returns>
        public List<mSkuStock> ListStock(List<string> skus)
        {
            var sql = $"select t1.bailun_sku,sum(t1.usable_stock) usable_stock,sum(t1.occupy_stock) occupy_stock,t2.unit_price from dc_base_stock t1 left join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku where t1.bailun_sku in ('{string.Join("','",skus)}') group by t1.bailun_sku";

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

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

                return obj;
            }
        }

        #endregion
    }

}
