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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// aims-本周新增冗余明细
    /// </summary>
    public class dc_auto_week_redundancy_details_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_redundancy_details_page_output_dto> GetWeekRedundancyDetailsPageList(week_redundancy_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_redundancy_details AS t1
                          INNER JOIN dc_base_sku AS t2 ON t2.bailun_sku = t1.bailun_sku
                          LEFT JOIN dc_base_warehouse AS t3 ON t3.warehouse_code = t1.warehouse_code
                          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.create_date_time >= '{m.start_date}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.end_date))
            {
                sql.Append($" AND t1.create_date_time < '{m.end_date}' ");
            }
            if (!string.IsNullOrWhiteSpace(m.platform))
            {
                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))
            {
                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_redundancy_details_page_output_dto>(offset, limit, sql.ToString(), ref total);
                    return obj.AsList();
                }
                else
                {
                    return _connection.Query<week_redundancy_details_page_output_dto>(sql.ToString()).AsList();
                }
            }
            catch (Exception)
            {
                return new List<week_redundancy_details_page_output_dto>();
            }
        }

        public static bool Insert(dc_auto_week_redundancy_details model)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO `dc_auto_week_redundancy_details` (`bailun_sku`, `warehouse_code`, `warehousing_time`, ");
            sql.Append("`seven_days_average`, `total_inventory`, `total_inventory_amount`, `redundant_number`,");
            sql.Append("`redundancy_amount`, `redundancy_occurrence_date`, `start_date`, `end_date`, `create_date_time`, `last_synch_time`) ");
            sql.Append(" VALUES (@bailun_sku, @warehouse_code, @warehousing_time,");
            sql.Append(" @seven_days_average, @total_inventory, @total_inventory_amount, @redundant_number,");
            sql.Append(" @redundancy_amount, @redundancy_occurrence_date, @start_date, @end_date, @create_date_time, @last_synch_time);");
            try
            {
                var count = _connection.Execute(sql.ToString(), model);
                return count > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

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

        /// <summary>
        /// 查询库存
        /// </summary>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<SkuRedundancyBaseStockDto> GetSkuRedundancyBaseStockPageList(DateTime startTime, DateTime endtime, int offset, int limit, ref int total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($@"
                      SELECT t1.usable_stock + t1.occupy_stock as sum_usable_stock,
                      ((t1.usable_stock + t1.occupy_stock)*t3.unit_price) AS total_inventory_amount,
                      t3.unit_price,
                      t2.warehouse_code,t3.bailun_sku,t3.product_code, t1.first_inbound_date
                       FROM dc_base_stock AS t1
                      LEFT JOIN dc_base_warehouse AS t2 ON t2.warehouse_code = t1.warehouse_code
                      LEFT JOIN dc_base_sku AS t3 ON t3.bailun_sku = t1.bailun_sku
                      WHERE 1=1 AND t1.gmt_create>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' AND t1.gmt_create<'{endtime.ToString("yyyy-MM-dd HH:mm:ss")}' ORDER BY t1.Id DESC
                      ");
            try
            {
                var obj = _connection.Page<SkuRedundancyBaseStockDto>(offset, limit, sql.ToString(), ref total);
                return obj.AsList();
            }
            catch (Exception)
            {
                return new List<SkuRedundancyBaseStockDto>();
            }
        }
    }
}
