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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 普货周数据缺货明细
    /// </summary>
    public class dc_auto_week_shortage_dao : connectionHelper
    {
        /// <summary>
        /// 分页aims普货周缺货数据明细
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<week_shortage_details_page_output_dto> GetWeekShortagePageList(week_shortage_details_page_input_dto m, int offset, int limit, ref int total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"
                      SELECT t1.*,t2.product_code,t3.warehouse_name 
                      FROM dc_auto_week_shortage_details AS t1
                      LEFT JOIN dc_base_warehouse AS t3 ON t3.warehouse_code = t1.warehouse_code
                      LEFT JOIN dc_base_sku AS t2 ON t2.bailun_sku = t1.bailun_sku
                      WHERE 1=1   
                      ");
            if (!string.IsNullOrWhiteSpace(m.bailun_sku))
            {
                sql.Append($" AND t1.bailun_sku = '{m.bailun_sku}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.warehousecode))
            {
                sql.Append($" AND t1.warehouse_code = '{m.warehousecode}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.start_date))
            {
                sql.Append($" AND t1.stockout_time >= '{m.start_date}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.end_date))
            {
                sql.Append($" AND t1.stockout_time < '{m.end_date}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.platform)&& m.platform!="null")
            {
                sql.Append($" AND t1.platform = '{m.platform}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.simple_category))
            {
                sql.Append($" AND t1.simple_category = '{m.simple_category}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.buyer_name) && m.buyer_name != "null")
            {
                sql.Append($" AND t1.buyer_name = '{m.buyer_name}' ");
            }
            sql.Append(" ORDER BY t1.create_date_time DESC ");
            try
            {
                if (limit > 0)
                {
                    var obj = _connection.Page<week_shortage_details_page_output_dto>(offset, limit, sql.ToString(), ref total);
                    return obj.AsList();
                }
                return _connection.Query<week_shortage_details_page_output_dto>(sql.ToString()).AsList();
            }
            catch (Exception)
            {
                return new List<week_shortage_details_page_output_dto>();
            }
        }

        public static bool Insert(dc_auto_week_shortage_details model)
        {
            try
            {
                var count = _connection.Insert(model);
                return count > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static dc_auto_week_shortage_details GetFirstOrDefault(dc_auto_week_shortage_details model)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * FROM dc_auto_week_shortage_details WHERE bailun_sku = @bailun_sku LIMIT 1;");
                var firstOrDefault = _connection.QueryFirstOrDefault<dc_auto_week_shortage_details>(sql.ToString(), model);
                return firstOrDefault;
            }
            catch (Exception ex)
            {
                return new dc_auto_week_shortage_details();
            }
        }

        /// <summary>
        /// 查询缺货数据
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endtime"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<gmt_out_stock_page_output_dto> GetGmtOutStockPageList(DateTime startTime, DateTime endtime, int offset, int limit, ref int total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($@"
                      SELECT t1.*,t3.first_inbound_date,t4.platform_type AS 'platform',t4.bailun_sku_quantity_ordered,t5.history_sevenday_sales_ebay,t6.bailun_inner_sku,t6.bailun_category_name AS 'simple_category',t6.buyer_name,
                      (( case when t5.average_overdue> 0 then t5.average_overdue else 1 end ) * t5.quantity_safe_inventory ) as 'quantity_safe'
                      FROM(
                      SELECT bailun_sku,warehouse_code,MAX(gmt_out_stock_modified) AS gmt_out_stock_modified FROM dc_mid_transit
                       WHERE gmt_out_stock_modified>= '{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' AND gmt_out_stock_modified< '{endtime.ToString("yyyy-MM-dd HH:mm:ss")}' GROUP BY bailun_sku,warehouse_code
                      ) AS t1
                      LEFT JOIN dc_base_stock AS t3 ON t3.bailun_sku = t1.bailun_sku AND t3.warehouse_code = t1.warehouse_code 
                      LEFT JOIN(
                      SELECT bailun_sku,warehouse_code,platform_type,SUM(bailun_sku_quantity_ordered)bailun_sku_quantity_ordered FROM 
                      dc_base_oms_sku_30 WHERE gmt_create>=  '{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' AND gmt_create< '{endtime.ToString("yyyy-MM-dd HH:mm:ss")}'
                      and has_fba_s = 0 and has_delete = 0 and has_scalp = 0
                      and has_buyer_remark = 0 and has_platsku_remark = 0 and has_innersale = 0 and bailun_order_status !='Canceled'
                      and platform_order_type != '手工单' GROUP BY bailun_sku,warehouse_code,platform_type
                      )AS t4 ON t4.bailun_sku = t1.bailun_sku AND t4.warehouse_code = t1.warehouse_code
                      LEFT JOIN dc_auto_turnover AS t5 ON t5.bailun_sku = t1.bailun_sku AND t5.warehouse_code = t1.warehouse_code
                      LEFT JOIN dc_base_sku AS t6 ON t6.bailun_sku = t1.bailun_sku ");
            try
            {
                var obj = _connection.Page<gmt_out_stock_page_output_dto>(offset, limit, sql.ToString(), ref total);
                return obj.AsList();
            }
            catch (Exception ex)
            {
                Console.WriteLine("GetGmtOutStockPageList异常：" + ex.Message);
                return new List<gmt_out_stock_page_output_dto>();
            }
        }

        /// <summary>
        /// 查询采购数量
        /// </summary>
        /// <param name="skus"></param>
        /// <param name="startTime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public static List<gmt_out_stock_page_output_dto> GetSkuPurchaseList(List<string> skus, DateTime startTime, DateTime endtime)
        {
            StringBuilder sql = new StringBuilder();
            string sku = "'" + string.Join("','", skus) + "'";
            sql.Append(@"
                      SELECT bailun_sku,warehouse_code,SUM(count) purchase_num FROM dc_base_purchase WHERE `status` = 1 
                      AND create_time>=@startTime AND create_time<@endtime AND bailun_sku IN(" + sku + ") GROUP BY bailun_sku,warehouse_code");
            try
            {
                var parameters = new DynamicParameters();
                parameters.Add("startTime", startTime);
                parameters.Add("endtime", endtime);
                var obj = _connection.Query<gmt_out_stock_page_output_dto>(sql.ToString(), parameters);
                return obj.AsList();
            }
            catch (Exception)
            {
                return new List<gmt_out_stock_page_output_dto>();
            }
        }
    }
}
