using AutoTurnOver.Models;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using AutoTurnOver.Models.ApiDto;

namespace AutoTurnOver.DB
{
    public class daily : connectionHelper
    {
        /// <summary>
        /// 查询采购在途数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static IEnumerable<dc_purchase_daily_dto> List(string sku, string warehouse_code)
        {
            var sql = @" 
SELECT
	tb1.bailun_sku AS bailun_sku,
	tb1.warehouse_code AS warehouse_code,
	tb1.purchase_id AS purchase_id,
     tb1.estimated_arrival_time AS estimated_arrival_time,
	-- tb1.has_transfer AS has_transfer,
	sum( ( tb1.count - IFNULL( tb2.count, 0 ) ) ) AS dailyQuantity ,
		(case when t_w.hq_type='fba仓' then t_w.bailun_account else t_w.warehouse_name end)  as warehouse_name
FROM
	(
SELECT
	bailun_sku,
	warehouse_code,
	parent_id,
	purchase_id,
	estimated_arrival_time,
	has_transfer,
	sum( count ) AS count 
FROM
	dc_base_purchase 
WHERE
	buy_status IN ( 0, 1, 2, 3 ) 
	AND has_delete = 0 
	AND bailun_sku = @bailun_sku
	AND warehouse_code = @warehouse_code
GROUP BY
	bailun_sku,
	parent_id 
	) tb1
	LEFT JOIN ( SELECT bailun_sku, warehouse_code, parent_id, sum( count ) AS count FROM dc_base_purchase_inbound GROUP BY bailun_sku, parent_id ) tb2 ON tb1.bailun_sku = tb2.bailun_sku 
	AND tb1.parent_id = tb2.parent_id 
left join dc_base_warehouse as t_w on tb1.warehouse_code = t_w.warehouse_code
GROUP BY
	tb1.bailun_sku,
	tb1.warehouse_code,
	tb1.purchase_id
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_purchase_daily_dto>(sql, parameters);
        }

        // <summary>
        /// 查询调拨在途数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static IEnumerable<dc_transfer_daily_dto> TransferList(string sku, string warehouse_code)
        {
            var sql = @" 
SELECT
 tb11.bailun_sku bailun_sku,
 tb11.warehouse_code warehouse_code,
 tb11.transfer_order_id transfer_order_id,
 tb5.estimated_arrival_time estimated_arrival_time,
 tb11.create_time create_time,
 (
  tb11.count - IFNULL(tb4.count, 0)
 ) AS count
FROM
 (
  SELECT
   tb1.bailun_sku,
   tb1.warehouse_code,
   tb1.transfer_order_id,
   tb1.create_time,
   sum(tb1.count) count
  FROM
   dc_base_transfer_bale tb1
  LEFT JOIN dc_base_transfer_complete tb2 ON tb1.transfer_order_id = tb2.transfer_order_id
  WHERE
   tb2.bailun_sku IS NULL
  AND tb1.bailun_sku = @bailun_sku
  AND tb1.warehouse_code = @warehouse_code
  AND tb1.deleted = 0
  GROUP BY
   bailun_sku,
   warehouse_code,
   transfer_order_id
 ) tb11
LEFT JOIN (
 SELECT
  tb1.bailun_sku,
  tb1.warehouse_code,
  tb1.transfer_order_id,
  tb1.estimated_arrival_time,
  tb1.create_time,
  sum(tb1.count) count
 FROM
  dc_base_transfer_verify tb1
 LEFT JOIN dc_base_transfer_complete tb2 ON tb1.transfer_order_id = tb2.transfer_order_id
 WHERE
  tb2.bailun_sku IS NULL
 AND tb1.bailun_sku = @bailun_sku
 AND tb1.warehouse_code = @warehouse_code
 AND tb1.deleted = 0
 GROUP BY
  bailun_sku,
  warehouse_code,
  transfer_order_id
) tb5 ON tb5.bailun_sku = tb11.bailun_sku
AND tb5.warehouse_code = tb11.warehouse_code
AND tb5.transfer_order_id = tb11.transfer_order_id
LEFT JOIN (
 SELECT
  bailun_sku,
  warehouse_code,
  transfer_order_id,
  sum(count) count
 FROM
  (
   SELECT
    t1.bailun_sku,
    t1.warehouse_code,
    t2.count,
    t1.transfer_order_id
   FROM
    (
     SELECT
      max(create_time) AS create_time,
      bailun_sku,
      warehouse_code,
      transfer_order_id
     FROM
      dc_base_transfer_inbound
     WHERE
      deleted = 0
     AND bailun_sku = @bailun_sku
     AND warehouse_code = @warehouse_code
     GROUP BY
      bailun_sku,
      warehouse_code,
      transfer_order_id
    ) t1
   INNER JOIN dc_base_transfer_inbound t2 ON t1.bailun_sku = t2.bailun_sku
   AND t1.warehouse_code = t2.warehouse_code
   AND t1.transfer_order_id = t2.transfer_order_id
   AND t1.create_time = t2.create_time
   LEFT JOIN dc_base_transfer_complete tb2 ON t1.transfer_order_id = tb2.transfer_order_id
   WHERE
    tb2.bailun_sku IS NULL
  ) tb3
 GROUP BY
  bailun_sku,
  warehouse_code,
  transfer_order_id
) tb4 ON tb5.bailun_sku = tb4.bailun_sku
AND tb5.warehouse_code = tb4.warehouse_code
AND tb5.transfer_order_id = tb4.transfer_order_id
WHERE
 (
  tb11.count - IFNULL(tb4.count, 0)
 ) > 0
UNION ALL
 SELECT
  bailun_sku,
  warehouse_code,
  transfer_plan_order_id,
  NULL,
  update_time,
  count
 FROM
  dc_base_transfer_plan
 WHERE
  STATUS = 1
 AND deleted = 0
 AND bailun_sku = @bailun_sku
 AND warehouse_code = @warehouse_code ;
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_transfer_daily_dto>(sql, parameters);
        }

        /// <summary>
        /// 指定日期到货的在途数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse_code"></param>
        /// <param name="index">dc_auto_inbound details 字段中的索引 索引</param>
        /// <returns></returns>
        public static List<dc_estimated_arrival_dto> EstimatedArrivalList(string sku, string warehouse_code, int index)
        {
            var inbound_data = _connection.QueryFirstOrDefault<dc_auto_inbound>(" select * from dc_auto_inbound where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = sku, warehouse_code = warehouse_code });
            if (inbound_data == null) return new List<dc_estimated_arrival_dto>();
            else
            {
                // 解析 relation 字段
                var relation = inbound_data.relation;
                if (string.IsNullOrWhiteSpace(relation))
                {
                    return new List<dc_estimated_arrival_dto>();
                }
                else
                {
                    var nos = relation.Replace("[", "").Replace("]", "").Split(',')[index];
                    var datas = new List<dc_estimated_arrival_dto>();
                    if (nos != "N/A")
                    {
                        foreach (var no_str in nos.Split('*'))
                        {
                            var no_datas = no_str.Split('_');
                            dc_estimated_arrival_dto data = new dc_estimated_arrival_dto();
                            data.type = no_datas[0].Contains("p") ? 1 : no_datas[0].Contains("t") ? 2 : 0;
                            data.no = no_datas[1];
                            data.quantity = decimal.Parse(no_datas[2]);
                            datas.Add(data);

                        }

                        return datas;
                    }


                    return datas;
                }
            }
        }

        /// <summary>
        /// 查询时实库存数据
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static List<dc_base_stock_dto> RealtimeList(string sku,string bailun_sku, string warehouse_code, string product_inner_code, string sku_title_cn,string supplier_name, int offset, int limit, ref int total, string warehousetype, int? warehousearea, bool isSum = false, string order = null, string sort = null,int? has_tort = null, List<bailun_category_new_dto> categoryModels = null)
        {
            var sql = "";
            if (isSum)
            {
                sql = @"
select
sum(t1.quantity_reservation) as 'quantity_reservation',
sum(t1.quantity_transport) as 'quantity_transport',
sum(t1.quantity_transfer) as 'quantity_transfer',
sum(t1.quantity_freeze) as 'quantity_freeze',
sum(t1.quantity_supplier) as 'quantity_supplier',
sum(t1.quantity_presell) as 'quantity_presell',
sum(t1.quantity_defective) as 'quantity_defective',
sum(t1.quantity_transit) as 'quantity_transit',
sum(t1.wait_check) as 'wait_check',
sum(t1.wait_putaway) as 'wait_putaway',
sum(t1.usable_stock) as 'usable_stock',
sum(t1.usable_sales) as 'usable_sales',
sum(t1.usable_sales_private) as 'usable_sales_private',
sum(t1.usable_sales_share) as 'usable_sales_share',
sum(t1.in_warehouse_share) as 'in_warehouse_share',
sum(t1.in_warehouse_private) as 'in_warehouse_private',
sum(t1.in_warehouse) as 'in_warehouse',
sum(t1.occupy_stock_share) as 'occupy_stock_share',
sum(t1.occupy_stock_private) as 'occupy_stock_private',
sum(t1.occupy_stock) as 'occupy_stock',
sum(t1.occupy_activity) as 'occupy_activity',
sum(t1.occupy_order_private) as 'occupy_order_private',
sum(t1.occupy_order_share) as 'occupy_order_share',
sum(t1.occupy_order) as 'occupy_order',

sum(t4.quantity_purchase) as 'purchase_daily_quantity',
sum(t4.quantity_purchase) as 'realtime_quantity_purchase',
sum(t4.quantity_transfer) as 'realtime_quantity_transfer',
sum(t4.quantity_out_stock) as 'quantity_out_stock'
from 
dc_base_stock as t1
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
left join dc_mid_transit as t4  on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
";
                if (has_tort != null)
                {
                    sql += " left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku ";
                }
                sql += " where 1=1 ";
            }
            else
            {
                sql = @" 
select
t1.*,
t3.develop_time as 'develop_time',
(case when dbw.hq_type='fba仓' then dbw.bailun_account else dbw.warehouse_name end)  as warehouse_name,
t3.sku_title_cn as 'base_sku_name',
t4.quantity_purchase as 'purchase_daily_quantity',
t3.buyer_name,t3.sku_title_en,t3.product_inner_code as 'sku_product_inner_code',
t3.category_simple_name,
t4.quantity_purchase as 'realtime_quantity_purchase',t4.quantity_transfer as 'realtime_quantity_transfer',
t1.bailun_sku,t1.warehouse_code,t4.quantity_out_stock,t4.gmt_out_stock_modified,t3.weight,t3.unit_price,
(case when t5.id is null then '未侵权' else  '侵权' end) as 'has_tort',
( case when t7.`status`=0 or t7.`status` is null then 0 else 1 end ) as 'monitor_status',
t6.history_sevenday_sales,t6.history_fourteenday_sales,t6.history_thirtyday_sales,
t6.history_sevenday_sales_ebay,t6.history_fourteenday_sales_ebay,t6.history_thirtyday_sales_ebay,
t6.history_sevenday_sales_aliexpress,t6.history_fourteenday_sales_aliexpress,t6.history_thirtyday_sales_aliexpress,
t8.oneday_sales,
t3.`status` as 'sku_status'
from 
dc_base_stock as t1
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
left join dc_mid_transit as t4  on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku
left join dc_auto_turnover as t6 on t1.bailun_sku = t6.bailun_sku and t1.warehouse_code = t6.warehouse_code
left join dc_auto_config_sku_warehouse as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_auto_sales as t8 on t1.bailun_sku=t8.bailun_sku and t1.warehouse_code = t8.warehouse_code
where 1=1
";
            }
            var countSql = @" 
 select
count(1)
from 
dc_base_stock as t1
left join dc_base_warehouse as dbw on t1.warehouse_code = dbw.warehouse_code
";
            if (categoryModels != null && categoryModels.Count >= 1)
            {
                countSql += " left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku ";
            }
            if (has_tort != null)
            {
                countSql += " left join dc_base_tort as t5  on t1.bailun_sku = t5.bailun_sku ";
            }
            
            countSql += "where 1=1 ";


            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("time", DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));

            if (categoryModels != null && categoryModels.Count>=1)
            {
                sql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
                countSql += " and t3.category_simple_id in ( " + string.Join(",", categoryModels.Select(s => s.id).Distinct()) + ")";
            }

            if (has_tort != null)
            {
                if (has_tort == 0)
                {
                    sql += " and t5.id is null ";
                    countSql += " and t5.id is null ";
                }
                else if (has_tort == 1)
                {
                    sql += " and t5.id is not null ";
                    countSql += " and t5.id is not null ";
                }
            }
            if (!string.IsNullOrWhiteSpace(warehousetype))
            {
                sql += " and dbw.hq_type = @hq_type ";
                countSql += " and dbw.hq_type = @hq_type ";

                parameters.Add("hq_type", warehousetype);
            }
            if (warehousearea > 0)
            {
                sql += " and dbw.area_id = @area_id ";
                countSql += " and dbw.area_id = @area_id ";

                parameters.Add("area_id", warehousearea);
            }

            if (!string.IsNullOrWhiteSpace(product_inner_code))
            {
                sql += " and t1.product_inner_code like @product_inner_code ";
                countSql += " and t1.product_inner_code like @product_inner_code ";

                parameters.Add("product_inner_code", $"%{product_inner_code}%");
            }
            if (!string.IsNullOrWhiteSpace(sku_title_cn))
            {
                sql += " and t1.sku_title_cn like @sku_title_cn ";
                countSql += " and t1.sku_title_cn like @sku_title_cn ";

                parameters.Add("sku_title_cn", $"%{sku_title_cn}%");
            }
            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku in @bailun_skus ";
                countSql += " and t1.bailun_sku in @bailun_skus ";

                parameters.Add("bailun_skus", sku.Split(','));
            }
            if (!string.IsNullOrWhiteSpace(bailun_sku))
            {
                sql += " and t1.bailun_sku like @bailun_sku ";
                countSql += " and t1.bailun_sku like @bailun_sku ";

                parameters.Add("bailun_sku", $"%{bailun_sku}%");
            }
            if (!string.IsNullOrWhiteSpace(warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", warehouse_code);
            }
            if (!string.IsNullOrWhiteSpace(supplier_name))
            {
                sql += " and t1.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", supplier_name);
            }

            if (isSum)
            {
                total = 0;
            }
            else
            {
                if (limit > 1000)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
                }
               

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

                    if (!string.IsNullOrEmpty(order))
                    {
                        sql += " " + order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                sql += " limit " + offset + "," + limit;
            }
            var obj = _connection.Query<dc_base_stock_dto>(sql, parameters, buffered: false, commandTimeout: 0);
            return obj.AsList();
        }

        public  static string MapField(string sort)
        {
            sort = sort.Replace("-","");
            switch (sort)
            {
                case "quantity_out_stock":return "t4.quantity_out_stock";
                default:return sort;
            }
        }

        /// <summary>
        /// 查询缺货订单
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static IEnumerable<dc_base_oms_sku> OutOfStockOrderList(string sku, string warehouse_code)
        {
            var sql = @" 
  SELECT
    *
    FROM
    dc_base_oms_sku
    WHERE
        bailun_order_status not in ('Canceled')
        and bailun_interception_status in('None','Failed')
  and warehouse_code = @warehouse_code and bailun_sku=@bailun_sku
  and platform_type != 'FBA'
    AND paid_time >= '2019-03-01 12:00:00'
   and bailun_sku_quantity_ordered > bailun_sku_quantity_picked
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_base_oms_sku>(sql, parameters);
        }
    }
}
