using AutoTurnOver.DB.Base;
using AutoTurnOver.Models;
using AutoTurnOver.Models.ApiDto;
using AutoTurnOver.Models.Base;
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);
        }


    }
}
