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(int page, int pagesize, string warehousetype, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory, string searchkey,string skucategoryids, ref int total)
        {
            var sqlparams = new DynamicParameters();
            

            var sql = @"select t4.hq_type as warehousetype,t4.warehouse_name as warehousename,t0.bailun_sku as sku,t2.category_name as categoryname,t2.sku_title_cn as skuname,t1.monitorstatus,t1.clearancestatus,t1.tortstatus,t1.str_skutag,t2.unit_price as buyprice,t3.usable_stock as inventorycount,t2.unit_price*t3.usable_stock as inventoryamount,t5.quantity_purchase as purchaseonwaycount,t5.quantity_transfer as allotcount 
                                from (SELECT t1.warehouse_code,t1.bailun_sku FROM dc_mid_transit AS t1 union SELECT t1.warehouse_code,t1.bailun_sku FROM dc_base_stock AS t1) t0
                                left join dc_base_stock t3 on t0.bailun_sku=t3.bailun_sku and t0.warehouse_code=t3.warehouse_code
                                left join dc_sku_monitor t1 on t1.sku=t0.bailun_sku and t1.warehousecode=t0.warehouse_code 
                                left join dc_base_sku t2 on t0.bailun_sku=t2.bailun_sku 
                                left join dc_base_warehouse t4 on t0.warehouse_code=t4.warehouse_code 
                                left join dc_mid_transit t5 on t0.bailun_sku=t5.bailun_sku and t0.warehouse_code=t5.warehouse_code where 1=1";


            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and t4.hq_type=@hq_type";
                sqlparams.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t0.warehousecode=@warehousecode";
                sqlparams.Add("warehousecode", warehousecode);
            }
            if (start.HasValue)
            {
                sql += " and t2.create_time>=@start";
                sqlparams.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and t2.create_time<@end";
                sqlparams.Add("end", end.Value);
            }
            if (monitorstatus.HasValue)
            {
                sql += " and t1.monitorstatus=@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 t3.usable_stock>=@stockstart";
                sqlparams.Add("stockstart", storestart.Value);
            }
            if (storeend.HasValue && storeend.Value > 0)
            {
                sql += " and t3.usable_stock<@stockend";
                sqlparams.Add("stockend", storeend.Value);
            }
            if (skucategory.HasValue)
            {
                sql += " and t2.category_id=@categoryid";
                sqlparams.Add("categoryid", skucategory.Value);
            }
            if (!string.IsNullOrWhiteSpace(searchkey))
            {
                sql += " and t0.bailun_sku like @searchkey";
                sqlparams.Add("searchkey", "%"+searchkey+"%");
            }
            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 t2.category_id in (" + s + ")";
                    }
                }
            }


            sql += " order by t1.monitorstatus desc,t3.usable_stock desc";

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

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

                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, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory,string skucategoryids, string searchkey)
        {
            var sqlparams = new DynamicParameters();

            var sql = @"select sum(t3.usable_stock) as inventorycount,sum(t2.unit_price*t3.usable_stock) as inventoryamount,sum(t5.quantity_purchase) as purchaseonwaycount,sum(t5.quantity_purchase*t2.unit_price) purchaseonwayamount,sum(t5.quantity_transfer) as allotcount,sum(t5.quantity_transfer*t2.unit_price) allotamount 
                                from (SELECT t1.warehouse_code,t1.bailun_sku FROM dc_mid_transit AS t1 union SELECT t1.warehouse_code,t1.bailun_sku FROM dc_base_stock AS t1) t0
                                left join dc_base_stock t3 on t0.bailun_sku=t3.bailun_sku and t0.warehouse_code=t3.warehouse_code
                                left join dc_sku_monitor t1 on t1.sku=t0.bailun_sku and t1.warehousecode=t0.warehouse_code 
                                left join dc_base_sku t2 on t0.bailun_sku=t2.bailun_sku 
                                left join dc_base_warehouse t4 on t0.warehouse_code=t4.warehouse_code 
                                left join dc_mid_transit t5 on t0.bailun_sku=t5.bailun_sku and t0.warehouse_code=t5.warehouse_code where 1=1";


            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and t4.hq_type=@hq_type";
                sqlparams.Add("hq_type", warehousetype);
            }
            if (!string.IsNullOrWhiteSpace(warehousecode))
            {
                sql += " and t0.warehousecode=@warehousecode";
                sqlparams.Add("warehousecode", warehousecode);
            }
            if (start.HasValue)
            {
                sql += " and t2.create_time>=@start";
                sqlparams.Add("start", start.Value);
            }
            if (end.HasValue)
            {
                sql += " and t2.create_time<@end";
                sqlparams.Add("end", end.Value);
            }
            if (monitorstatus.HasValue)
            {
                sql += " and t1.monitorstatus=@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 t3.usable_stock>=@stockstart";
                sqlparams.Add("stockstart", storestart.Value);
            }
            if (storeend.HasValue && storeend.Value > 0)
            {
                sql += " and t3.usable_stock<@stockend";
                sqlparams.Add("stockend", storeend.Value);
            }
            if (skucategory.HasValue)
            {
                sql += " and t2.category_id=@categoryid";
                sqlparams.Add("categoryid", skucategory.Value);
            }
            if (!string.IsNullOrWhiteSpace(searchkey))
            {
                sql += " and t0.bailun_sku like @searchkey";
                sqlparams.Add("searchkey", "%" + searchkey + "%");
            }

            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 t2.category_id in (" + s + ")";
                    }
                }
            }

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

                return obj;
            }
        }
    }
}
