using AutoTurnOver.Models;
using Dapper;
using System;
using System.Collections.Generic;
using System.Text;

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
	tb5.bailun_sku bailun_sku,
	tb5.warehouse_code warehouse_code,
	tb5.transfer_order_id transfer_order_id,
	tb5.estimated_arrival_time estimated_arrival_time,
	tb5.create_time create_time,
	( tb5.count - IFNULL( tb4.count, 0 ) ) AS count ,
	(case when t_w.hq_type='fba仓' then t_w.bailun_account else t_w.warehouse_name end)  as warehouse_name
FROM
	(
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.deleted = tb2.deleted 
	AND tb1.bailun_sku = tb2.bailun_sku 
	AND tb1.warehouse_code = tb2.warehouse_code 
	AND 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
LEFT JOIN (
	SELECT
		bailun_sku,
		warehouse_code,
		transfer_order_id,
		sum( count ) count 
	FROM
		(
		SELECT
			tb1.bailun_sku,
			tb1.warehouse_code,
			max( tb1.count ) count,
			tb1.transfer_order_id 
		FROM
			dc_base_transfer_inbound tb1
			LEFT JOIN dc_base_transfer_complete tb2 ON tb2.deleted = 0 
			AND tb1.bailun_sku = tb2.bailun_sku 
			AND tb1.warehouse_code = tb2.warehouse_code 
			AND tb1.transfer_order_id = tb2.transfer_order_id 
		WHERE
			tb2.bailun_sku IS NULL 
			AND tb1.deleted = 0 
		GROUP BY
			bailun_sku,
			warehouse_code,
			transfer_order_id 
		) 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 
left join dc_base_warehouse as t_w on tb5.warehouse_code = t_w.warehouse_code
WHERE
( tb5.count - IFNULL( tb4.count, 0 ) ) > 0
";
            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)
        {
            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
where 1=1";
            }
            else
            {
                sql = @" 
 select
(case when dbw.hq_type='fba仓' then dbw.bailun_account else dbw.warehouse_name end)  as warehouse_name,
t1.sku_title_cn as 'sku_name',
t4.quantity_purchase as 'purchase_daily_quantity',
t3.buyer_name,t3.sku_title_en,t3.product_inner_code,
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,
t1.*
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
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
left join dc_mid_transit as t4  on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
where 1=1
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("time", DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));

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