using Bailun.DC.Models.Orders;
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Dapper;

namespace Bailun.DC.Services
{
    public class SkuOrderServices
    {
        /// <summary>
        /// 获取sku的销量、销售额、利润数据
        /// </summary>
        /// <param name="skus"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="platform"></param>
        /// <returns></returns>
        public List<dc_base_oms_sku> ListSkuSaleCount(List<string> skus, DateTime start, DateTime? end, string platform)
        {
            var sql = $@"select t1.platform_type,t1.bailun_sku,sum(t1.bailun_sku_quantity_ordered) as bailun_sku_quantity_ordered,sum(t1.amount_sales*t1.seller_order_exchange_rate*t1.bailun_sku_quantity_ordered) as amount_sales,sum(t1.profit_total*t1.bailun_sku_quantity_ordered) as profit_total from dc_base_oms_sku t1 
            where t1.has_delete=0 and t1.company_id=1 and t1.has_scalp=0 and t1.has_innersale=0 and t1.bailun_interception_status in ('None','Failed')  and t1.bailun_order_status!='Canceled' and ((t1.platform_type!='FBA' and t1.bailun_order_status!='CantHandle') or t1.platform_type='FBA') and t1.paid_time>='{start.ToString("yyyy-MM-dd")}'
            ";

            sql += $" and t1.bailun_sku in ('{string.Join("','",skus)}')";

            if (end.HasValue)
            {
                sql += $" and t1.paid_time<'{end.Value.AddDays(1).ToString("yyyy-MM-dd")}'";
            }

            if (!string.IsNullOrWhiteSpace(platform))
            {
                sql += $" and t1.platform_type='{platform}'";
            }

            sql += " group by t1.platform_type,t1.bailun_sku";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_base_oms_sku>(sql);

                return obj.AsList();
            }
        }

    }
}
