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

namespace Bailun.DC.Services
{
    public class SkuMonitorServices
    {
        /// <summary>
        /// 获取数据仓分页数据
        /// </summary>
        /// <param name="page">当前页面</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="start">开发开始时间</param>
        /// <param name="end">开发结束时间</param>
        /// <param name="monitorstatus">监控状态</param>
        /// <param name="cleanstatus">清货状态</param>
        /// <param name="infringestatus">侵权状态</param>
        /// <param name="skutip">sku标签</param>
        /// <param name="storestart">库存区间开始</param>
        /// <param name="storeend">库存区间结束</param>
        /// <param name="skucategory">sku分类</param>
        /// <param name="searchkey">搜索关键字，sku</param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_sku_monitor> List(BtTableParameter request, string warehousetype,int? areaid, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory, string searchkey,string skucategoryids,string groupname, ref int total)
        {
            var sqlparams = new DynamicParameters();
            

            var sql = @"select t2.hq_type as warehousetype,t2.warehouse_name as warehousename,t2.warehouse_code,t1.bailun_sku as sku,t3.category_id,t3.category_name,t3.sku_title_cn as skuname,(case when t4.status=1 then 0 else 1 end) status,t5.clearancestatus,t5.tortstatus,t5.str_skutag,t3.unit_price as buyprice,t1.usable_stock,(t1.usable_stock*t3.unit_price) amount_stock,t6.quantity_purchase,(t6.quantity_purchase*t3.unit_price) as purchase_amount,t6.quantity_transfer,(t6.quantity_transfer*t3.unit_price) amount_transit,t6.quantity_out_stock,t7.oneday_total_sales,(t7.oneday_total_sales*t3.unit_price) as amount_onedaysale,t7.sevenday_total_sales,(t7.sevenday_total_sales*t3.unit_price) amount_sevendaysale,t7.fourteenday_total_sales,t7.thirtyday_total_sales,t7.sevenday_average_sales,t7.fourteenday_average_sales,t7.thirtyday_average_sales,t3.create_time as skucreatetime,t2.area_id,t2.area_name,t5.groupname,t6.quantity_unshipped
                                from dc_base_stock t1
                                left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                                left join dc_base_sku t3 on t1.bailun_sku=t3.bailun_sku
                                left join dc_auto_config_sku_warehouse t4 on t4.bailun_sku=t1.bailun_sku and t4.warehouse_code=t1.warehouse_code
                                left join dc_sku_monitor t5 on t5.sku=t1.bailun_sku and t5.warehousecode=t1.warehouse_code
                                left join dc_mid_transit t6 on t1.bailun_sku=t6.bailun_sku and t1.warehouse_code=t6.warehouse_code
                                left join dc_daily_sales t7 on t7.record_date = DATE_SUB(curdate(),INTERVAL 1 DAY) and t1.bailun_sku=t7.bailun_sku and t7.warehouse_code=t1.warehouse_code
                                where 1=1 ";

            var countsql = @"select count(t1.id) from dc_base_stock t1 ";

            #region 组装联表条件

            if (!string.IsNullOrWhiteSpace(warehousetype) || !string.IsNullOrWhiteSpace(warehousecode)|| areaid.HasValue)
            {
                countsql += " left join dc_base_warehouse t2 on t1.warehouse_code = t2.warehouse_code ";
            }
            if (start.HasValue || end.HasValue || skucategory.HasValue || !string.IsNullOrWhiteSpace(skucategoryids))
            {
                countsql += " left join dc_base_sku t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (monitorstatus.HasValue)
            {
                countsql += " left join dc_auto_config_sku_warehouse t4 on t4.bailun_sku = t1.bailun_sku and t4.warehouse_code = t1.warehouse_code ";
            }
            if(cleanstatus.HasValue|| infringestatus.HasValue|| !string.IsNullOrWhiteSpace(skutip))
            {
                countsql += " left join dc_sku_monitor t5 on t5.sku = t1.bailun_sku and t5.warehousecode = t1.warehouse_code ";
            }

            countsql += " where 1 = 1  ";

            #endregion

            if (!string.IsNullOrWhiteSpace(searchkey))
            {
                var arr = searchkey.Split(',').Where(a=>!string.IsNullOrEmpty(a)).ToList();

                if (arr.Count > 0)
                {
                    sql += " and t1.bailun_sku in @searchkey";
                    countsql += " and t1.bailun_sku in @searchkey";
                    sqlparams.Add("searchkey", arr.ToArray());
                }
            }

            if (areaid.HasValue)
            {
                sql += " and t2.area_id=" + areaid.Value;
                countsql += " and t2.area_id=" + areaid.Value;
            }
            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and t2.hq_type=@hq_type";
                countsql += " and t2.hq_type=@hq_type";
                sqlparams.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t2.warehouse_code=@warehousecode";
                countsql += " and t2.warehouse_code=@warehousecode";
                sqlparams.Add("warehousecode", warehousecode);
            }
            if (start.HasValue)
            {
                sql += " and t3.create_time>=@start";
                countsql += " and t3.create_time>=@start";
                sqlparams.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and t3.create_time<@end";
                countsql += " and t3.create_time<@end";
                sqlparams.Add("end", end.Value);
            }
            if (monitorstatus.HasValue)
            {
                sql += " and t4.status=@monitorstatus";
                countsql += " and t4.status=@monitorstatus";
                sqlparams.Add("monitorstatus", monitorstatus.Value);
            }
            if (cleanstatus.HasValue)
            {
                sql += " and t5.clearancestatus=@clearancestatus";
                countsql += " and t5.clearancestatus=@clearancestatus";
                sqlparams.Add("clearancestatus", cleanstatus.Value);
            }
            if (infringestatus.HasValue)
            {
                sql += " and t5.tortstatus=@tortstatus";
                countsql += " and t5.tortstatus=@tortstatus";
                sqlparams.Add("tortstatus", infringestatus.Value);
            }
            if (!string.IsNullOrWhiteSpace(skutip))
            {
                sql += " and t5.str_skutag like @skutip";
                countsql += " and t5.str_skutag like @skutip";
                sqlparams.Add("skutip", "%"+skutip+"%");
            }
            if (storestart.HasValue && storestart.Value>0)
            {
                sql += " and t1.usable_stock>=@stockstart";
                countsql += " and t1.usable_stock>=@stockstart";
                sqlparams.Add("stockstart", storestart.Value);
            }
            if (storeend.HasValue && storeend.Value > 0)
            {
                sql += " and t1.usable_stock<@stockend";
                countsql += " and t1.usable_stock<@stockend";
                sqlparams.Add("stockend", storeend.Value);
            }
            if (skucategory.HasValue)
            {
                sql += " and t3.category_id=@categoryid";
                countsql += " and t3.category_id=@categoryid";
                sqlparams.Add("categoryid", skucategory.Value);
            }
            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                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))
                    {
                        sql += " and t3.category_id in (" + s + ")";
                        countsql += " and t3.category_id in (" + s + ")";
                    }
                }
            }
            
            if (!string.IsNullOrEmpty(groupname))
            {
                sql += " and t5.groupname=@groupname";
                countsql += " and t5.groupname=@groupname";

                sqlparams.Add("groupname", groupname);
            }

            sql += " ";
            var ordersql = "";
            if (!string.IsNullOrEmpty(request.sort))
            {
                ordersql += " order by " + request.sort + " " + request.order;
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<dc_sku_monitor>(request.pageIndex, request.limit, sql+ordersql, ref total, sqlparams,countsql,3*60);

                return obj.AsList();
            } 
        }

        /// <summary>
        /// 获取数据仓分页数据 （新版）
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="warehousetype"></param>
        /// <param name="areaid"></param>
        /// <param name="warehousecode"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="monitorstatus"></param>
        /// <param name="cleanstatus"></param>
        /// <param name="infringestatus"></param>
        /// <param name="skutip"></param>
        /// <param name="storestart"></param>
        /// <param name="storeend"></param>
        /// <param name="skucategory"></param>
        /// <param name="searchkey"></param>
        /// <param name="skucategoryids"></param>
        /// <param name="groupname"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<dc_datawarehouse> List2(BtTableParameter parameter, string warehousetype, int? areaid, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory, string searchkey, string skucategoryids, string groupname, ref int total)
        {
            var sqlparams = new DynamicParameters();
            var sql = "select t1.* from dc_datawarehouse t1 ";

            sql += " where 1=1 ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        //sql += " and t4.bailun_category_id in (" + s + ") ";
                        sql += " and t1.category_id in (" + s + ") ";
                    }
                }
            }

            if (!string.IsNullOrWhiteSpace(searchkey))
            {
                var arr = searchkey.Split(',').Where(a => !string.IsNullOrEmpty(a)).ToList();

                if (arr.Count > 0)
                {
                    sql += " and t1.sku in ('" + string.Join("','",arr)+"')";
                    //sqlparams.Add("searchkey", arr.ToArray());
                }
            }

            if (areaid.HasValue)
            {
                sql += " and t1.area_id=" + areaid.Value;
            }
            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and t1.warehousetype=@hq_type";
                sqlparams.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t1.warehouse_code=@warehousecode";
                sqlparams.Add("warehousecode", warehousecode);
            }
            if (start.HasValue)
            {
                sql += " and t1.create_time>=@start";
                sqlparams.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and t1.create_time<@end";
                sqlparams.Add("end", end.Value);
            }
            if (monitorstatus.HasValue)
            {
                sql += " and t1.status=@monitorstatus";
                sqlparams.Add("monitorstatus", monitorstatus.Value);
            }
            if (cleanstatus.HasValue)
            {
                sql += " and t1.clearancestatus=@clearancestatus";
                sqlparams.Add("clearancestatus", cleanstatus.Value);
            }
            if (infringestatus.HasValue)
            {
                sql += " and t1.tortstatus=@tortstatus";
                sqlparams.Add("tortstatus", infringestatus.Value);
            }
            if (!string.IsNullOrWhiteSpace(skutip))
            {
                sql += " and t1.str_skutag like @skutip";
                sqlparams.Add("skutip", "%" + skutip + "%");
            }
            if (storestart.HasValue && storestart.Value > 0)
            {
                sql += " and t1.usable_stock>=@stockstart";
                sqlparams.Add("stockstart", storestart.Value);
            }
            if (storeend.HasValue && storeend.Value > 0)
            {
                sql += " and t1.usable_stock<@stockend";
                sqlparams.Add("stockend", storeend.Value);
            }
            if (skucategory.HasValue)
            {
                sql += " and t1.category_id=@categoryid";
                sqlparams.Add("categoryid", skucategory.Value);
            }
            

            if (!string.IsNullOrEmpty(groupname))
            {
                sql += " and t1.groupname=@groupname";

                sqlparams.Add("groupname", groupname);
            }

            if (!string.IsNullOrEmpty(parameter.sort))
            {
                sql += " order by t1." + 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<dc_datawarehouse>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparams, "", 3 * 60);

                return obj.AsList();
            }

        }

        /// <summary>
        /// 数据仓总计
        /// </summary>
        /// <param name="warehousetype"></param>
        /// <param name="warehousecode"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="monitorstatus"></param>
        /// <param name="cleanstatus"></param>
        /// <param name="infringestatus"></param>
        /// <param name="skutip"></param>
        /// <param name="storestart"></param>
        /// <param name="storeend"></param>
        /// <param name="skucategory"></param>
        /// <param name="searchkey"></param>
        /// <returns></returns>
        public dc_sku_monitor ListCount(string warehousetype,int? areaid, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory,string skucategoryids, string searchkey,string groupname)
        {
            var sqlparams = new DynamicParameters();
            
            var sql = @"select sum(t1.usable_stock) usable_stock,sum(t1.amount_stock) amount_stock,sum(t1.quantity_purchase) quantity_purchase,sum(t1.purchase_amount) as purchase_amount,sum(t1.quantity_transfer) quantity_transfer,sum(t1.amount_transit) amount_transit,sum(t1.quantity_out_stock) quantity_out_stock,sum(t1.oneday_total_sales) oneday_total_sales,sum(t1.amount_onedaysale) as amount_onedaysale,sum(t1.sevenday_total_sales) sevenday_total_sales,sum(t1.amount_sevendaysale) amount_sevendaysale,sum(t1.fourteenday_total_sales) fourteenday_total_sales,sum(t1.thirtyday_total_sales) thirtyday_total_sales,sum(t1.sevenday_average_sales) sevenday_average_sales,sum(t1.fourteenday_average_sales) fourteenday_average_sales,sum(t1.thirtyday_average_sales) thirtyday_average_sales from dc_datawarehouse t1 ";

            sql += " where 1=1 ";

            if (!string.IsNullOrWhiteSpace(skucategoryids))
            {
                var arr = skucategoryids.Split('|').Where(a => !string.IsNullOrEmpty(a)).ToList();
                if (arr.Count > 0)
                {
                    var s = string.Join(",", arr);
                    if (!string.IsNullOrEmpty(s))
                    {
                        //sql += " and t4.bailun_category_id in (" + s + ") ";
                        sql += " and t1.category_id in (" + s + ") ";
                    }
                }
            }

            if (!string.IsNullOrWhiteSpace(searchkey))
            {
                var arr = searchkey.Split(',').Where(a => !string.IsNullOrEmpty(a)).ToList();

                if (arr.Count > 0)
                {
                    sql += " and t1.sku in ('" + string.Join("','", arr) + "')";
                    //sqlparams.Add("searchkey", arr.ToArray());
                }
            }

            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and t1.warehousetype=@hq_type";
                sqlparams.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t1.warehouse_code=@warehousecode";
                sqlparams.Add("warehousecode", warehousecode);
            }
            if (start.HasValue)
            {
                sql += " and t1.create_time>=@start";
                sqlparams.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and t1.create_time<@end";
                sqlparams.Add("end", end.Value);
            }
            if (monitorstatus.HasValue)
            {
                sql += " and t1.status=@monitorstatus";
                sqlparams.Add("monitorstatus", monitorstatus.Value);
            }
            if (cleanstatus.HasValue)
            {
                sql += " and t1.clearancestatus=@clearancestatus";
                sqlparams.Add("clearancestatus", cleanstatus.Value);
            }
            if (infringestatus.HasValue)
            {
                sql += " and t1.tortstatus=@tortstatus";
                sqlparams.Add("tortstatus", infringestatus.Value);
            }
            if (!string.IsNullOrWhiteSpace(skutip))
            {
                sql += " and t1.str_skutag like @skutip";
                sqlparams.Add("skutip", "%" + skutip + "%");
            }
            if (storestart.HasValue && storestart.Value > 0)
            {
                sql += " and t1.usable_stock>=@stockstart";
                sqlparams.Add("stockstart", storestart.Value);
            }
            if (storeend.HasValue && storeend.Value > 0)
            {
                sql += " and t1.usable_stock<@stockend";
                sqlparams.Add("stockend", storeend.Value);
            }
            if (skucategory.HasValue)
            {
                sql += " and t1.category_id=@categoryid";
                sqlparams.Add("categoryid", skucategory.Value);
            }

            if (areaid.HasValue)
            {
                sql += " and t1.area_id=" + areaid.Value;
            }
            if (!string.IsNullOrEmpty(groupname))
            {
                sql += " and t1.groupname=@groupname";

                sqlparams.Add("groupname", groupname);
            }

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

                var obj = cn.QueryFirstOrDefault<dc_sku_monitor>(sql,sqlparams,null,3*60);

                return obj;
            }
        }

        /// <summary>
        /// 获取sku分组信息
        /// </summary>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="sku"></param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="country">仓库所在国家（区域）</param>
        /// <param name="groupname">分组名称</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<mSkuGroup> ListSkuGroup(int page,int pagesize,string[] sku,string warehousecode,string country,string[] groupname,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select t1.sku,t1.warehousecode,t1.groupname,t2.area_name from dc_sku_monitor t1
                                join dc_base_warehouse t2 on t1.warehousecode = t2.warehouse_code
                                where groupname is not null ";

            if (sku!=null && sku.Length>0)
            {
                sql += " and t1.sku in @sku";
                sqlparam.Add("sku", sku.ToArray());
            }
            if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " and t1.warehousecode=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }
            if (!string.IsNullOrEmpty(country))
            {
                sql += " and t2.area_name=@country";
                sqlparam.Add("country", country);
            }
            if (groupname!=null && groupname.Length>0)
            {
                sql += " and t1.groupname in @groupname";
                sqlparam.Add("groupname", groupname.ToArray());
            }

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

                var obj = cn.Page<mSkuGroup>(page, pagesize, sql, ref total, sqlparam);

                return obj.AsList();
            }
        }

        /// <summary>
        /// 设置Sku分组信息
        /// </summary>
        /// <param name="listsku"></param>
        /// <param name="groupname"></param>
        /// <returns></returns>
        public string SaveGroupName(List<string> listsku, string groupname,int userid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                foreach (var item in listsku)
                {
                    try
                    {
                        var arr = item.Split('|');
                        if (arr.Length > 1)
                        {

                            var sqls = "select id from dc_sku_monitor where sku=@sku and warehousecode=@warehousecode";
                            var sqlparams = new DynamicParameters();
                            sqlparams.Add("sku", arr[0]);
                            sqlparams.Add("warehousecode", arr[1]);
                            var obj = cn.QueryFirstOrDefault<int?>(sqls,sqlparams);

                            if (!obj.HasValue || obj.Value == 0)
                            {
                                //新增监控信息
                                var objwarehouse = new WareHouseServices().GetWarehouse(arr[1]);
                                if(objwarehouse==null)
                                {
                                    return "找不到该仓库"+arr[1];
                                }

                                var objSkuDevelopTime = new SkuInfoServices().GetSkuDevelopTime(arr[0]);
                                if (!objSkuDevelopTime.HasValue)
                                {
                                    return "找不到该SKU";
                                }

                                var insertsql = @"insert dc_sku_monitor(sku,warehousecode,warehousename,monitorstatus,clearancestatus,tortstatus,str_skutag,createtime,developmenttime,stockcount,groupname,groupupdatetime,groupupdateuser) values(@sku,@warehousecode,@warehousename,@monitorstatus,@clearancestatus,@tortstatus,@str_skutag,@createtime,@developmenttime,@stockcount,@groupname,@groupupdatetime,@groupupdateuser)";
                                var insertparam = new DynamicParameters();
                                insertparam.Add("sku", arr[0]);
                                insertparam.Add("warehousecode", arr[1]);
                                insertparam.Add("warehousename", objwarehouse.warehouse_name);
                                insertparam.Add("monitorstatus", 2);
                                insertparam.Add("clearancestatus", 0);
                                insertparam.Add("tortstatus", 0);
                                insertparam.Add("str_skutag", "");
                                insertparam.Add("createtime", DateTime.Now);
                                insertparam.Add("developmenttime", objSkuDevelopTime.Value);
                                insertparam.Add("stockcount", 0);
                                insertparam.Add("groupname", groupname);
                                insertparam.Add("groupupdatetime", DateTime.Now);
                                insertparam.Add("groupupdateuser", username);

                                cn.Execute(insertsql, insertparam);
                            }
                            else
                            {
                                var sqlparam = new DynamicParameters();
                                var sql = "update dc_sku_monitor set groupname=@groupname,groupupdatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',groupupdateuser='"+username+"' where sku=@sku and warehousecode=@warehousecode";
                                sqlparam.Add("groupname", groupname);
                                sqlparam.Add("sku", arr[0]);
                                sqlparam.Add("warehousecode", arr[1]);
                                cn.Execute(sql, sqlparam);
                            }
                            
                        }

                    }
                    catch (Exception ex)
                    {
                        return "更新" + item + "失败，异常信息：" + ex.Message;
                    }
                }

                return "";
                
            }
            
        }


        /// <summary>
        /// 批量保存SKU分组信息
        /// </summary>
        /// <param name="list"></param>
        /// <param name="userid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string SaveGroupName(List<Tuple<string, string, string, string>> list, int userid, string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                foreach (var item in list)
                {
                    var sqls = "select id from dc_sku_monitor where sku=@sku and warehousecode=@warehousecode";
                    var sqlparams = new DynamicParameters();
                    sqlparams.Add("sku", item.Item2);
                    sqlparams.Add("warehousecode", item.Item1);
                    var obj = cn.QueryFirstOrDefault<int?>(sqls, sqlparams);

                    if (!obj.HasValue || obj.Value == 0)
                    {
                        //新增监控信息
                        var objwarehouse = new WareHouseServices().GetWarehouse(item.Item1);
                        if (objwarehouse == null)
                        {
                            return "找不到该仓库" + item.Item1;
                        }

                        var objSkuDevelopTime = new SkuInfoServices().GetSkuDevelopTime(item.Item2);
                        if (!objSkuDevelopTime.HasValue)
                        {
                            return "找不到该SKU【"+item.Item2+"】";
                        }

                        var insertsql = @"insert dc_sku_monitor(sku,warehousecode,warehousename,monitorstatus,clearancestatus,tortstatus,str_skutag,createtime,developmenttime,stockcount,groupname,groupupdatetime,groupupdateuser) values(@sku,@warehousecode,@warehousename,@monitorstatus,@clearancestatus,@tortstatus,@str_skutag,@createtime,@developmenttime,@stockcount,@groupname,@groupupdatetime,@groupupdateuser)";
                        var insertparam = new DynamicParameters();
                        insertparam.Add("sku", item.Item2);
                        insertparam.Add("warehousecode", item.Item1);
                        insertparam.Add("warehousename", objwarehouse.warehouse_name);
                        insertparam.Add("monitorstatus", 2);
                        insertparam.Add("clearancestatus", 0);
                        insertparam.Add("tortstatus", 0);
                        insertparam.Add("str_skutag", "");
                        insertparam.Add("createtime", DateTime.Now);
                        insertparam.Add("developmenttime", objSkuDevelopTime.Value);
                        insertparam.Add("stockcount", 0);
                        insertparam.Add("groupname", item.Item3);
                        insertparam.Add("groupupdatetime", DateTime.Now);
                        insertparam.Add("groupupdateuser", item.Item4);

                        cn.Execute(insertsql, insertparam);
                    }
                    else
                    {
                        var sqlparam = new DynamicParameters();
                        var sql = "update dc_sku_monitor set groupname=@groupname,groupupdatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',groupupdateuser='" + username + "' where sku=@sku and warehousecode=@warehousecode";
                        sqlparam.Add("groupname", item.Item3);
                        sqlparam.Add("sku", item.Item2);
                        sqlparam.Add("warehousecode", item.Item1);
                        cn.Execute(sql, sqlparam);
                    }
                }

            }

            return "";
        }

        /// <summary>
        /// 获取分组信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<string> ListGroupName(string name, string country, string warehousetype, string warehousecode)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select t1.groupname from dc_sku_monitor t1 join dc_base_warehouse t2 on t1.warehousecode=t2.warehouse_code where 1=1 ";

            if (!string.IsNullOrEmpty(name))
            {
                sql += " and t1.groupname like @groupname";
                sqlparam.Add("groupname", "%"+name+"%");
            }
            if (!string.IsNullOrEmpty(country))
            {
                sql += " and t2.area_name=@area_name";
                sqlparam.Add("area_name", country);
            }
            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " and t2.hq_type=@hq_type";
                sqlparam.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " and t1.warehousecode=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }


            sql += " group by t1.groupname";

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

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

                return obj.Where(a=>!string.IsNullOrEmpty(a)).ToList();
            }
        }

        /// <summary>
        /// 获取淘汰sku列表
        /// </summary>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="start">更新开始时间</param>
        /// <param name="end">更新结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<string> ListWeedOut(int page, int pagesize, DateTime? start, DateTime? end,ref int total)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = "select DISTINCT sku from dc_sku_monitor where is_weedout=1 ";

                if (start.HasValue)
                {
                    sql += " and weedout_updatetime>='"+start.Value.ToString("yyyy-MM-dd HH:mm:ss")+"'";
                }
                if (end.HasValue)
                {
                    sql += " and weedout_updatetime<'"+end.Value.ToString("yyyy-MM-dd HH:mm:ss")+"'";
                }

                var obj = cn.Page<string>(page, pagesize, sql, ref total);

                return obj.AsList();
            }
        }

    }
}
