using AutoTurnOver.DB.Base;
using AutoTurnOver.Models;
using AutoTurnOver.Models.ApiDto;
using AutoTurnOver.Models.ApiDto.Stock;
using AutoTurnOver.Models.Base;
using AutoTurnOver.Utility;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoTurnOver.DB
{
    public class api_dao : connectionHelper
    {
        /// <summary>
        /// 获取缺货数据
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static Page<dc_quantity_out_stock_dto> GetOutStockPage(dc_quantity_out_stock_search_dto search)
        {
            string sql = " select  *  from dc_mid_transit as t1 where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if (search.btime != null)
                {
                    sql += " and t1.gmt_out_stock_modified>=@btime ";
                    parameters.Add("btime", search.btime);
                }
                if (search.etime != null)
                {
                    sql += " and t1.gmt_out_stock_modified<=@etime ";
                    parameters.Add("etime", search.etime);
                }
            }
            return _connection.Page<dc_quantity_out_stock_dto>(sql, search, parameters);
        }

        public static async Task<IEnumerable<dc_base_oms_sku_full_dto>> GetOmsSkuFullPage(dc_base_oms_sku_full_search_dto search)
        {
            if (search.page <= 0)
            {
                search.page = 1;
            }
            var sql = " select t1.* from dc_base_oms_sku as t1 where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(search.warehouse_codes))
            {
                sql += " and t1.warehouse_code in @warehouse_codes ";
                parameters.Add("warehouse_codes", search.warehouse_codes.Split(',').ToList());
            }
            if (search.btime != null)
            {
                sql += " and t1.gmt_modified >=@btime ";
                parameters.Add("btime", search.btime);
            }
            if (search.etime != null)
            {
                sql += " and t1.gmt_modified <=@etime ";
                parameters.Add("etime", search.etime);
            }
            return await _connection.QueryAsync<dc_base_oms_sku_full_dto>(sql + $" limit {(search.page - 1) * search.rows},{search.rows} ", parameters);
        }

        // <summary>
        /// 查询sku销量
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static Page<api_sku_sales_volume_dto> GetSkuSalesVolume(api_sku_sales_volume_search_dto search)
        {
            string sql = @" select  t1.bailun_sku,t1.warehouse_code,t1.history_sevenday_sales,t2.oneday_sales 
from dc_auto_turnover as t1
left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code  =t2.warehouse_code where 1=1";
            string count_sql = " select count(1) from dc_auto_turnover as t1 where 1=1  ";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if (search.bailun_sku_list != null && search.bailun_sku_list.Count >= 1)
                {
                    sql += " and t1.bailun_sku in @bailun_skus ";
                    count_sql += " and t1.bailun_sku in @bailun_skus ";
                    parameters.Add("bailun_skus", search.bailun_sku_list);
                }
            }
            var items = _connection.Query<api_sku_sales_volume_dto>(sql, parameters).ToList();
            var totalItems = _connection.QuerySingleOrDefault<int?>(count_sql, parameters) ?? 0;
            Page<api_sku_sales_volume_dto> page_data = new Page<api_sku_sales_volume_dto>
            {
                Items = items,
                TotalItems = totalItems,
                TotalPages = (long)Math.Ceiling(totalItems * 1.0 / search.rows)
            };
            return page_data;
        }

        /// <summary>
        /// 获取sku 的侵权状态
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static Page<api_sku_tort_dto> GetSkuTort(api_sku_tort_search_dto search)
        {
            search.page = search.page <= 0 ? 1 : search.page;
            search.rows = search.rows <= 0 ? 100 : search.rows;
            var sql = @" select t1.bailun_sku,t1.gmt_modified,(case when t8.id is null then '未侵权' else  '侵权' end) as 'tort_status' from dc_base_sku as t1 left join dc_base_tort as t8  on t1.bailun_sku = t8.bailun_sku where 1=1";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if (!string.IsNullOrWhiteSpace(search.bailun_sku))
                {
                    sql += " and t1.bailun_sku = @bailun_sku ";
                    parameters.Add("bailun_sku", search.bailun_sku);
                }
                if (search.btime != null)
                {
                    sql += " and t1.gmt_modified >= @btime ";
                    parameters.Add("btime", search.btime);
                }
                if (search.etime != null)
                {
                    sql += " and t1.gmt_modified <= @etime ";
                    parameters.Add("etime", search.etime);
                }
            }
            return _connection.Page<api_sku_tort_dto>(sql, search, parameters);
        }

        /// <summary>
        /// 查询库存金额
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static async Task<List<api_base_stock_amount_info_output_dto>> GetBaseStockAmountInfo(api_base_stock_amount_info_input_dto search)
        {
            StringBuilder sqlText = new StringBuilder();
            sqlText.Append(@"
                            SELECT t1.bailun_sku,t1.warehouse_code,t3.warehouse_name,t1.usable_stock + t1.occupy_stock as 'sum_usable_stock',
                            ((t1.usable_stock + t1.occupy_stock) * t2.unit_price) as 'sum_usable_stock_amount'
                             FROM dc_base_stock as t1
                            left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
                            LEFT JOIN dc_base_warehouse AS t3 ON t3.warehouse_code = t1.warehouse_code
                            WHERE t1.bailun_sku IN @bailun_skus 
                           ");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_skus", search.bailun_skus);
            if (!string.IsNullOrWhiteSpace(search.warehouse_code))
            {
                sqlText.Append(" AND t1.warehouse_code = @warehouse_code ");
                parameters.Add("warehouse_code", search.warehouse_code);
            }
            var data = await _connection.QueryAsync<api_base_stock_amount_info_output_dto>(sqlText.ToString(), parameters);
            return data.ToList();
        }

        /// <summary>
        /// 查询sku是否缺货
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static async Task<List<api_oms_sku_outofstock_output_dto>> GetSkuOutOfStockInfo(api_oms_sku_outofstock_input_dto search)
        {
            try
            {
                StringBuilder sqlText = new StringBuilder();
                sqlText.Append(@"
                            SELECT t1.bailun_sku,MAX(t2.gmt_out_stock_modified)AS gmt_out_stock_modified,
                            (case when t1.bailun_sku_quantity_ordered>t1.bailun_sku_quantity_picked then 1 else 0 end) as 'is_out_stock'
                            FROM dc_base_oms_sku_outofstock AS t1
                            LEFT JOIN dc_mid_transit AS t2 ON t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
                            LEFT JOIN dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
                            WHERE t1.bailun_sku IN @bailun_skus 
                           ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("bailun_skus", search.bailun_skus);
                if (search.start_time.HasValue && search.start_time.Value != DateTime.MinValue &&
                    search.end_time.HasValue && search.end_time.Value != DateTime.MinValue)
                {
                    sqlText.Append(" AND t2.gmt_out_stock_modified>= @start_time AND t2.gmt_out_stock_modified<= @end_time ");
                    parameters.Add("start_time", search.start_time.Value);
                    parameters.Add("end_time", search.end_time.Value);
                }
                sqlText.Append(" GROUP BY t1.bailun_sku; ");
                var data = await _connection.QueryAsync<api_oms_sku_outofstock_output_dto>(sqlText.ToString(), parameters);
                return data.ToList();
            }
            catch (Exception ex)
            {
                return new List<api_oms_sku_outofstock_output_dto>();
            }
        }

        public static Page<api_stock_dto> GetStockPage(api_stock_search_dto search)
        {
            string sql = $@" select 
t1.bailun_sku as 'sku', 
t1.usable_stock, 
t1.shipped_stock, 
t2.area_name,
t2.warehouse_name,
t1.warehouse_code,
t1.gmt_modified,
ifnull(t3.quantity_transfer,0) as 'quantity_transfer',
ifnull(t3.quantity_purchase,0) as 'quantity_purchase',

t16.lately_transfer_expect_arrival_time,
t16.lately_transfer_order_no,
t16.lately_transfer_count,
t16.lately_transfer_trackingno,
t16.lately_transfer_transporttype,
t16.lately_transfer_logisticsname
from dc_base_stock as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_mid_transit as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
left join dc_base_transfer_extend as t16 on t1.bailun_sku=t16.bailun_sku and t1.warehouse_code = t16.warehouse_code
where t1.warehouse_code in @warehouse_codes and t1.bailun_sku in @skus  ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("skus", search.skus);
            if (search.warehouse_codes != null && search.warehouse_codes.Count >= 1) {
                parameters.Add("warehouse_codes", search.warehouse_codes);
            }
            else
            {
                parameters.Add("warehouse_codes", new List<string> { "US4PXBL", "AU4PXBL", "GZBLLM" });
            }
            return _connection.Page<api_stock_dto>(sql, search, parameters);
        }

        public static Page<api_stock_dto> GetStockPageBySkus(api_stock_search_dto search)
        {
            string sql = $@" select 
                             t1.bailun_sku as 'sku', 
                             t1.usable_stock, 
                             t1.shipped_stock, 
                             t2.area_name,
                             t2.warehouse_name,
                             t1.warehouse_code,
                             t1.gmt_modified,
                             ifnull(t3.quantity_transfer,0) as 'quantity_transfer',
                             ifnull(t3.quantity_purchase,0) as 'quantity_purchase'
                             from dc_base_stock as t1 
                             left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
                             left join dc_mid_transit as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
                             where t1.bailun_sku in @skus  ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("skus", search.skus);

            return _connection.Page<api_stock_dto>(sql, search, parameters);
        }
    }
}
