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

namespace AutoTurnOver.DB
{
    public class dc_auto_week_negative_profit_details_dao : connectionHelper
    {
        public static List<week_negative_profit_details_page_output_dto> GetWeekNegativeProfitDetailsPageList(week_negative_profit_details_page_input_dto m, int offset, int limit, ref int total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT t1.*,t2.sku_title_cn FROM dc_auto_week_negative_profit_details AS t1
                          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.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)&&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 && offset > 0)
                {
                    var obj = _connection.Page<week_negative_profit_details_page_output_dto>(offset, limit, sql.ToString(), ref total);
                    return obj.AsList();
                }
                return _connection.Query<week_negative_profit_details_page_output_dto>(sql.ToString()).ToList();
            }
            catch (Exception)
            {
                return new List<week_negative_profit_details_page_output_dto>();
            }
        }

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

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<query_week_negative_profit_dto> GetGmvPageList(DateTime startTime, DateTime endtime, int offset, int limit, ref int total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($@"
                         SELECT t1.bailun_sku,t1.warehouse_code,t5.warehouse_name,t3.bailun_category_name AS 'simple_category',t3.declare_title_cn,SUM(t1.amount_sales_usd_7)AS amount_sales_usd_7,
                         SUM(t1.bailun_sku_quantity_ordered_7)AS bailun_sku_quantity_ordered_7,
                         SUM(t1.bailun_sku_quantity_ordered_7*t3.unit_price)AS weekly_cost_of_sales,
                         CASE WHEN t2.weekly_refunds_num>0 THEN t2.weekly_refunds_num ELSE 0 END AS weekly_refunds_num,
                         CASE WHEN t2.weekly_refund_amount>0 THEN t2.weekly_refund_amount ELSE 0 END AS weekly_refund_amount,
                         CASE WHEN t4.profit_total>0 THEN t4.profit_total ELSE 0 END AS profit_total,
                         CASE WHEN t4.amount_shipping>0 THEN t4.amount_shipping ELSE 0 END AS amount_shipping,
                         CASE WHEN t4.profit_rate>0 THEN t4.profit_rate ELSE 0 END AS profit_rate
                          FROM dc_report_gmv_extend AS t1 
                         LEFT JOIN (
                             SELECT bailun_sku,SUM(bailun_sku_quantity_refund)AS weekly_refunds_num,
                             SUM(bailun_sku_quantity_refund*bailun_sku_unit_price)AS weekly_refund_amount
                             FROM dc_base_crm_refund WHERE refund_time>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
                           AND refund_time<'{endtime.ToString("yyyy-MM-dd HH:mm:ss")}' GROUP BY bailun_sku
                         )AS t2 ON t2.bailun_sku = t1.bailun_sku
                         INNER JOIN dc_base_sku AS t3 ON t3.bailun_sku = t1.bailun_sku
                         LEFT JOIN(SELECT bailun_sku,
                         SUM(profit_total)profit_total,SUM(amount_shipping)amount_shipping,SUM(profit_rate)AS profit_rate 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)AS t4 ON t4.bailun_sku = t1.bailun_sku
                         LEFT JOIN dc_base_warehouse AS t5 ON t5.warehouse_code = t1.warehouse_code
                         WHERE t1.sales_reset_date>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
                         AND t1.sales_reset_date<'{endtime.ToString("yyyy-MM-dd HH:mm:ss")}' AND t1.bailun_sku_quantity_ordered_7>0
                         GROUP BY t1.bailun_sku,t1.warehouse_code ORDER BY t1.bailun_sku ASC
                       ");
            try
            {
                var obj = _connection.Page<query_week_negative_profit_dto>(offset, limit, sql.ToString(), ref total);
                return obj.AsList();
            }
            catch (Exception)
            {
                return new List<query_week_negative_profit_dto>();
            }
        }

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