using AutoTurnOver.Models;
using AutoTurnOver.Models.Report;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoTurnOver.Utility;
using Dapper;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 投资回报分析
    /// </summary>
    public class report_invest_return_dao : connectionHelper
    {
        public static List<dc_report_invest_return_analysis> AnaList(Condition_ConfigPromotion m, int offset, int limit,string sort,string order, ref int total)
        {
            var list = new List<dc_report_invest_return_analysis>();

            try
            {
                var sql = @"select t1.* from dc_report_invest_return_analysis as t1
 where 1 = 1 ";

                

                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1");

                if (!string.IsNullOrEmpty(sort) && !string.IsNullOrEmpty(order))
                {
                    sql += " order by " + sort;

                    if (!string.IsNullOrEmpty(order))
                    {
                        sql += " " + order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }

                var obj = _connection.Query<dc_report_invest_return_analysis>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 同步pps刊登过的sku
        /// </summary>
        public static void SynchPpsSku()
        {
            var skus = ApiUtility.GetPPSSkus();
            foreach (var item in skus)
            {
                var dbSku = new dc_report_invest_return_sku
                {
                    create_user = "pps",
                    gmt_create_time = DateTime.Now,
                    gmt_update_time = DateTime.Now,
                    project = "DE",
                    sku = item.skuCode,
                    sku_name = "",
                    warehouse_type = "海外仓,第三方仓库",
                    id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_invest_return_sku where sku=@sku and project=@project limit 1  ", new { sku = item.skuCode, project = "DE" }) ?? 0
                };
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                } 
                
                //var dbSkuFba = new dc_report_invest_return_sku
                //{
                //    create_user = "pps",
                //    gmt_create_time = DateTime.Now,
                //    gmt_update_time = DateTime.Now,
                //    project = "BP代运营",
                //    sku = item.skuCode,
                //    sku_name = "",
                //    warehouse_type = "FBA仓",
                //    id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_invest_return_sku where sku=@sku and project=@project limit 1  ", new { sku = item.skuCode, project = "BP代运营" }) ?? 0
                //};
                //if (dbSkuFba.id <= 0)
                //{
                //    _connection.Insert(dbSkuFba);
                //}
            }
        }

        public class yinjusku_dto
        {
            public string bailun_sku { get; set; }
            public string sku_title_cn { get; set; }
        }
        // 同步隐矩的sku
        public static void SynchYinjuSku()
        {
            var skus = _connection.Query<yinjusku_dto>($@" select bailun_sku,sku_title_cn from dc_base_stock where usable_stock>0  and warehouse_code in ('GZBLWH','BLGZ03') and bailun_sku not in (
select sku from dc_report_invest_return_sku 
) ").ToList() ;
            foreach (var item in skus)
            {
                var dbSku = new dc_report_invest_return_sku
                {
                    create_user = "aims",
                    gmt_create_time = DateTime.Now,
                    gmt_update_time = DateTime.Now,
                    project = "隐矩",
                    sku = item.bailun_sku,
                    sku_name = item.sku_title_cn,
                    warehouse_type = "国内仓"
                };
                if(item.sku_title_cn!=null && (item.sku_title_cn.IndexOf("DE-")==0 || item.sku_title_cn.IndexOf("DE ")==0))
                {
                    dbSku.project = "DE";
                }
                dbSku.id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_invest_return_sku where sku=@sku and project=@project limit 1  ", new { sku = item.bailun_sku, project = dbSku.project }) ?? 0;
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                }
            }
        }
        public static void SynchBtmAdFees()
        {
            var fees = ApiUtility.GetBtmAdFees(DateTime.Now.AddDays(-60), DateTime.Now);
            foreach (var item in fees)
            {
                var meidongDate = DateTime.Parse(item.date_str);
                var dbSku = new t_base_shopify_adfee
                {
                    data_id = item.data_id,
                    account_id = item.account_id,
                    date_str = item.date_str,
                    fee = item.fee,
                    fee_cny = item.fee_cny,
                    bdate = meidongDate.GetMonthFirstDay().ToDayHome().AMESTimeToBeijinTime(),
                    edate = meidongDate.LastDayOfMonth().ToDayEnd().AMESTimeToBeijinTime(),
                    gmt_create_time = DateTime.Now,
                    share_time = null,
                    id = _connection.QueryFirstOrDefault<int?>(" select id from t_base_shopify_adfee where data_id=@data_id ", new { data_id = item.data_id }) ?? 0
                };
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                }
                else
                {
                    _connection.Update(dbSku);
                }
            }
        }
        public static void SynchBtmOrderRefund()
        {
            var fees = ApiUtility.GetBtmOrderRefunds(DateTime.Now.AddDays(-60), DateTime.Now);
            foreach (var item in fees)
            {
                var dbSku = new t_base_shopify_refund
                {
                    bailun_order_id = item.bailun_order_id,
                    warehouse_code = item.warehouse_code,
                    bailun_sku = item.bailun_sku,
                    order_pay_time = item.order_pay_time,
                    origin_order_id = item.origin_order_id,
                    refund_amount = item.refund_amount,
                    id = _connection.QueryFirstOrDefault<int?>(" select id from t_base_shopify_refund where bailun_order_id=@bailun_order_id and bailun_sku=@bailun_sku ", new { bailun_order_id = item.bailun_order_id, bailun_sku = item.bailun_sku }) ?? 0
                };
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                }
                else
                {
                    _connection.Update(dbSku);
                }
            }
        }

        public static void CalculationStockScore(string sku=null)
        {
            var skus = _connection.Query<dc_report_invest_return_sku>(" select * from dc_report_invest_return_sku order by gmt_update_time  ").ToList() ;
            if (!string.IsNullOrWhiteSpace(sku))
            {
                skus = skus.Where(s => sku.Equals( s.sku,StringComparison.InvariantCultureIgnoreCase)).ToList();
            }
            foreach (var skuItem in skus)
            {
                CalculationStockScoreSingle(skuItem);

                _connection.Execute(" update dc_report_invest_return_sku set gmt_update_time=now() where sku=@sku ",new { sku = skuItem.sku});
            }
        }
        /// <summary>
        /// 计算仓库分数
        /// </summary>
        /// <param name="sku"></param>
        public static void CalculationStockScoreSingle(dc_report_invest_return_sku sku)
        {
            var day30Btime = DateTime.Now.AddDays(-31).ToDayHome();
            var day7Btime = DateTime.Now.AddDays(-8).ToDayHome();
            var dayEtime = DateTime.Now.AddDays(-1).ToDayEnd();
            // 查询这个sku涉及的所有仓库 
            var warehouse_types = (sku.warehouse_type ?? "").Split(',').ToList();
            var stocks = _connection.Query<dc_base_stock>(" select * from dc_base_stock where bailun_sku=@sku and warehouse_code in ( select warehouse_code from dc_base_warehouse where hq_type in @warehouse_types ) ", new { sku = sku.sku, warehouse_types= warehouse_types }).ToList();
            // 国内仓先去除中转仓
            stocks = stocks.Where(s => s.warehouse_code != "GZBLZZG").ToList();
            // 如果是海外仓或者fba，就再加上中转仓
            var haiwaiTypes = new List<string> { "海外仓", "第三方仓库","FBA仓" };
            // 需要忽略的仓库
            var ignoreWarehouseCodes = new List<string> { "DEBLDG", "FPLLA", "XXHWCATL" };
            stocks = stocks.Where(s => !ignoreWarehouseCodes.Contains( s.warehouse_code) ).ToList();
            if (warehouse_types.Any(s=> haiwaiTypes.Any(s1=>s==s1)))
            {
                var transferWarehouse = _connection.QueryFirstOrDefault<dc_base_stock>(" select * from dc_base_stock where bailun_sku=@sku and warehouse_code =@warehouse_code ", new { sku = sku.sku, warehouse_code = "GZBLZZG" });
                if (transferWarehouse != null)
                {
                    stocks.Add(transferWarehouse);
                }
            }

            // 查询sku 基础表
            var baseSku = _connection.QueryFirstOrDefault<dc_base_sku>(" select * from dc_base_sku where     bailun_sku=@bailun_sku ", new
            {
                bailun_sku = sku.sku
            });
            var chinaWarehouseCodes = new List<string> { "GZBLWH", "BLGZ03", "GZBLZZG" }; 
            foreach (var stockItem in stocks)
            {
                
                var warehouseData = _connection.QueryFirstOrDefault<dc_base_warehouse>(" select * from dc_base_warehouse where warehouse_code=@warehouse_code  ", new
                {
                    warehouse_code = stockItem.warehouse_code
                })??new dc_base_warehouse { warehouse_code= stockItem.warehouse_code, warehouse_name  = "-",hq_type = ""};

                // 国内仓只监听01和03仓
                if ("国内仓".Equals(warehouseData.hq_type, StringComparison.InvariantCultureIgnoreCase))
                {
                    if (!chinaWarehouseCodes.Contains(stockItem.warehouse_code))
                    {
                        continue;
                    }
                }

                // 查询在途数据
                var stock_transit = _connection.QueryFirstOrDefault<dc_mid_transit>(" select * from dc_mid_transit where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ", new
                {
                    warehouse_code = stockItem.warehouse_code,
                    bailun_sku = sku.sku
                })??new dc_mid_transit { };
                // 查询历史订单
                var orders30 = _connection.Query<dc_base_oms_sku>(" select * from dc_base_oms_sku where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and paid_time<=@etime and paid_time>=@btime ", new
                {
                    warehouse_code = stockItem.warehouse_code,
                    bailun_sku = sku.sku,
                    etime = dayEtime,
                    btime = day30Btime
                },commandTimeout:0).ToList();


                dc_report_invest_return_analysis ana = new dc_report_invest_return_analysis
                {
                    sku = sku.sku,
                    stock = stockItem.usable_stock + stock_transit.quantity_purchase + stock_transit.quantity_transfer,
                    project = sku.project,
                    sku_name = baseSku.sku_title_cn,
                    warehouse_type = warehouseData.hq_type=="第三方仓库"?"海外仓": warehouseData.hq_type,
                    quantity_transfer = stock_transit.quantity_transfer,
                    quantity_purchase = stock_transit.quantity_purchase,
                    create_user = "system",
                    first_order_date = null,
                    last_order_date = null,
                    gmt_create_time = sku.gmt_create_time,
                    gmt_update_time = DateTime.Now,
                    order_days = _connection.QueryFirstOrDefault<decimal?>("  select count(DISTINCT DATE_FORMAT(paid_time,'%Y-%m-%d')) from dc_base_oms_sku where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and paid_time<=@etime ", new
                    {
                        warehouse_code = stockItem.warehouse_code,
                        bailun_sku = sku.sku,
                        etime = dayEtime
                    }, commandTimeout: 0)??0,
                    library_stock = stockItem.usable_stock,
                    price = baseSku.unit_price,
                    quantity_purchase_amount = 0,
                    quantity_transfer_amount = 0,
                    sales_day_30 = orders30.Select(s => s.bailun_order_id).Distinct().Count(),
                    sales_day_7 = orders30.Where(s => s.paid_time >= day7Btime).Select(s => s.bailun_order_id).Distinct().Count(),
                    consume_days_30 = orders30.Where(s => s.paid_time >= day7Btime).Select(s => s.paid_time.Value.ToString("yyyy-MM-dd")).Distinct().Count(),
                    stock_amount = 0,
                    warehouse_code = stockItem.warehouse_code,
                    library_stock_amount = 0,
                    warehouse_name = warehouseData.warehouse_name,
                    sales_day_average = 0,
                    profit_30_forecast = 0,
                    vendibility_days = 0,
                    seller_order_exchange_rate = null,
                };

                //如果是中转仓
                if(stockItem.warehouse_code== "GZBLZZG")
                {
                    ana.quantity_transfer = ana.library_stock;
                }

                if (orders30.Any(s => s.seller_order_exchange_rate > 0))
                {
                   // ana.seller_order_exchange_rate = orders.Where(s => s.paid_time >= day30Btime).Where(s => s.seller_order_exchange_rate > 0).Average(s => s.seller_order_exchange_rate);
                }
                ana.quantity_purchase_amount = ana.quantity_purchase * ana.price;
                ana.quantity_transfer_amount = ana.quantity_transfer * ana.price;
                ana.stock_amount = ana.stock * ana.price;
                ana.library_stock_amount = ana.library_stock * ana.price;
                ana.sales_day_average =Math.Round( (ana.sales_day_30 .Division( 30M) * 0.5M) + (ana.sales_day_7.Division( 7M) * 0.5M),0);
                ana.vendibility_days = Math.Round( ana.stock.Division( ana.sales_day_average),0);
                /// 30天利润
                ///预计30天内销量，可售天数大于30天的，则为加权日均销量*30，可售天数小于30天的，则为加权日均销量* 可售天数
                ///预计30天内销售额=预计30天内销量* 单位售价
                ///预计30天营业利润=预计30天内销量* 单位售价*(1-最近1个月的退款率-最近1个月的平台费率最近1个月的广告费率)-预计30天内销量* 单位采购成本
                ana.profit_30_forecast = 0;
                //预计30天内销量
                ana.sales_day_30_forecast = ana.sales_day_average * ana.vendibility_days;
                if (ana.vendibility_days > 30)
                {
                    //预计30天内销量
                    ana.sales_day_30_forecast = ana.sales_day_average * 30;
                }
                ana.gmv_day30_avg_unit_price_cny = 0;
                if (orders30.Any(s => s.amount_sales > 0 && s.seller_order_exchange_rate > 0))
                {
                    //过去30天 单位售价
                    ana.gmv_day30_avg_unit_price_cny = 0;
                    var unit_price_orders = orders30.Where(s => s.amount_sales > 0 && s.seller_order_exchange_rate > 0);
                    if (unit_price_orders.Count() >= 1)
                    {
                        ana.gmv_day30_avg_unit_price_cny = unit_price_orders.Average(s => s.amount_sales * s.seller_order_exchange_rate);
                    }
                   
                }
                //预计30天内销售额
                ana.gmv_day_30_forecast_cny = ana.sales_day_30_forecast * ana.gmv_day30_avg_unit_price_cny;
                // 计算最近1个月的广告费率
                var adFees = _connection.Query<t_base_shopify_adfee_order>(" select * from t_base_shopify_adfee_order where is_delete=0 and bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and order_pay_time>=@order_pay_btime and order_pay_time<=@order_pay_etime ", new
                {
                    bailun_sku = ana.sku,
                    warehouse_code = ana.warehouse_code,
                    order_pay_btime = day30Btime,
                    order_pay_etime = dayEtime,
                }).ToList();

                ana.adfee_day_30 = adFees.Sum(s => s.order_fee);
                ana.amount_total_day_30 = orders30.Sum(s => s.amount_total * s.order_to_usd_exchange_rate);
                ana.adfee_rate = Math.Round(ana.adfee_day_30 .Division( ana.amount_total_day_30), 4);

                // 计算最近1个月的平台费率
                ana.platform_fee_day_30 = orders30.Where(s=>s.cost_platform_fee>0).Sum(s => s.cost_platform_fee * s.order_to_usd_exchange_rate);
                ana.platform_fee_rate = Math.Round(ana.platform_fee_day_30 .Division( ana.amount_total_day_30), 4);



                // 计算最近1个月的退款费率 
                // de的读btm的，其他的读oms的
                if ("de".Equals(ana.project, StringComparison.InvariantCultureIgnoreCase))
                {
                    // 计算最近1个月的广告费率
                    ana.refund_amount_day_30 = _connection.QueryFirstOrDefault<decimal?>(" select * from t_base_shopify_refund where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and order_pay_time>=@order_pay_btime and order_pay_time<=@order_pay_etime ", new
                    {
                        bailun_sku = ana.sku,
                        warehouse_code = ana.warehouse_code,
                        order_pay_btime = day30Btime,
                        order_pay_etime = dayEtime,
                    }) ?? 0M;

                }
                else
                {
                    ana.refund_amount_day_30 = 0;
                }
                ana.refund_rate = Math.Round(ana.refund_amount_day_30 .Division( ana.amount_total_day_30), 4); ;

                // 预计30天利润
                ana.profit_30_forecast = ana.gmv_day_30_forecast_cny * (1 - ana.refund_rate - ana.adfee_rate - ana.platform_fee_rate);

                ana.first_order_date = _connection.QueryFirstOrDefault<DateTime?>("  select min(paid_time) from dc_base_oms_sku where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and paid_time<=@etime ", new
                {
                    warehouse_code = stockItem.warehouse_code,
                    bailun_sku = sku.sku,
                    etime = dayEtime
                }, commandTimeout: 0);
                ana.last_order_date = _connection.QueryFirstOrDefault<DateTime?>("  select max(paid_time) from dc_base_oms_sku where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and paid_time<=@etime ", new
                {
                    warehouse_code = stockItem.warehouse_code,
                    bailun_sku = sku.sku,
                    etime = dayEtime
                }, commandTimeout: 0);

                //计算单位运费
                ana.freight_unit_price = CalculationFreight(ana.sku, ana.warehouse_code, ana.warehouse_type);
                
                //预估运费，库存 * 单位运费
                ana.estimated_freight = ana.freight_unit_price * ana.stock;
                ana.stock_score = (ana.sales_day_average * 90).Division(ana.stock) * 100;
                ana.id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_invest_return_analysis where sku=@sku and warehouse_code=@warehouse_code ", new
                {
                    sku = ana.sku,
                    warehouse_code = ana.warehouse_code
                }) ?? 0;
                if (ana.id <= 0)
                {
                    _connection.Insert(ana);
                }
                else
                {
                    _connection.Update(ana);
                }
            }
        }

        /// <summary>
        /// 分摊广告费算法
        /// </summary>
        public static void ShareAdFee()
        {

            // 查询100条未分摊过的费用
            var fees = _connection.Query<t_base_shopify_adfee>(" select * from t_base_shopify_adfee where share_time is null limit 100 ").ToList();
            foreach (var feeItem in fees)
            {
                // 查询这个账号，该时间范围，所有有效订单
                var orders = _connection.Query<dc_base_oms_sku>(" select * from dc_base_oms_sku where bailun_account_id=@bailun_account_id  and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and paid_time>=@btime and paid_time<=@etime ", new
                {
                    bailun_account_id = feeItem.account_id,
                    btime = feeItem.bdate,
                    etime = feeItem.edate
                }).Where(s => s.order_to_usd_exchange_rate > 0 && s.amount_total > 0).ToList();
                if (orders != null && orders.Count > 0)
                {
                    List<t_base_shopify_adfee_order> adfee_orders = new List<t_base_shopify_adfee_order>();
                    // 计算这个范围内的总销售额
                    var amount_total_usd = orders.Sum(s => s.order_to_usd_exchange_rate * s.amount_total);
                    var useFeeCny = 0M;
                    var useFeeUsd = 0M;
                    foreach (var itemOrder in orders)
                    {
                        var order_amount_total = itemOrder.order_to_usd_exchange_rate * itemOrder.amount_total;
                        var ratio = order_amount_total / amount_total_usd;
                        t_base_shopify_adfee_order addfee_order = new t_base_shopify_adfee_order
                        {
                            amount_total_usd = amount_total_usd,
                            bailun_account_id = itemOrder.bailun_account_id,
                            bailun_sku = itemOrder.bailun_sku,
                            warehouse_code = itemOrder.warehouse_code,
                            calculation_btime = feeItem.bdate,
                            is_delete = 0,
                            order_pay_time = itemOrder.paid_time.Value,
                            calculation_etime = feeItem.edate,
                            date_str = feeItem.date_str,
                            fee_data_id = feeItem.data_id,
                            order_amount_total_usd = order_amount_total,
                            order_unique = $"{itemOrder.origin_order_id}-{itemOrder.bailun_account_id}-{itemOrder.bailun_sku}",
                            origin_order_id = itemOrder.origin_order_id,
                            origin_fee = feeItem.fee,
                            origin_fee_cny = feeItem.fee_cny,
                            order_fee = Math.Round(feeItem.fee * ratio, 2),
                            order_fee_cny = Math.Round(feeItem.fee_cny * ratio, 4),
                        };
                        addfee_order.unique_id = $"{addfee_order.fee_data_id}-{addfee_order.order_unique}";
                        useFeeCny += addfee_order.order_fee_cny;
                        useFeeUsd += addfee_order.order_fee;
                        adfee_orders.Add(addfee_order);
                    }

                    // 如果分摊之后，还有小数没有分摊均匀。就把多余的全部给最大的那笔
                    if (feeItem.fee_cny != useFeeCny || feeItem.fee != useFeeUsd)
                    {
                        var topOrder = adfee_orders.OrderByDescending(s => s.order_amount_total_usd).FirstOrDefault();
                        topOrder.order_fee += (feeItem.fee - useFeeUsd);
                        topOrder.order_fee_cny += (feeItem.fee_cny - useFeeCny);
                    }

                    foreach (var item in adfee_orders)
                    {
                        try
                        {
                            item.id = _connection.QueryFirstOrDefault<int?>(" select id from t_base_shopify_adfee_order where unique_id=@unique_id ", new { unique_id = item.unique_id }) ?? 0;
                            if (item.id <= 0)
                            {
                                _connection.Insert(item);
                            }
                            else
                            {
                                _connection.Update(item);
                            }
                        }
                        catch (Exception ex)
                        {

                            throw;
                        }

                    }

                    // 清理多余的数据
                    _connection.Execute(" update t_base_shopify_adfee_order set is_delete=1 where fee_data_id=@fee_data_id and unique_id not in @unique_ids ", new
                    {
                        fee_data_id = feeItem.data_id,
                        unique_ids = adfee_orders.Select(s => s.unique_id).ToList()
                    });

                }

                // 标记为已分摊
                _connection.Execute(" update t_base_shopify_adfee set share_time=now() where id=@id ", new { id = feeItem.id });
            }
        }

        /// <summary>
        /// 计算单位运费 
        /// </summary>
        public static decimal CalculationFreight(string bailun_sku, string warehouse_code, string warehouse_type )
        {
            if ("国内仓".Equals(warehouse_type, StringComparison.InvariantCultureIgnoreCase))
            {
                return 0;
            }
            // 清理运费计算日志
            _connection.Execute(" delete from dc_base_transfer_freight_new_order where main_bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code
            });
            var top5DataIds = _connection.Query<string>($@" select dataid from dc_base_transfer_info where
dataid in ( select dataid from dc_base_transfer_info_skus where sku=@sku ) 
and isdeleted=0 
and isexception = 0
and estimatedcost>0 
and targetwareno=@warehouse_code
order by creationtime desc limit 5 ",new { sku = bailun_sku, warehouse_code  = warehouse_code }).ToList();
           
            if(top5DataIds!=null && top5DataIds.Count >= 1)
            {
                // 查询这个sku 时间范围内所有的调拨单明细
                var tempDatas = _connection.Query<CalculationFreightDto>($@"select t2.sku ,
t1.`code`,
t2.deliverycount,
t1.estimatedcost as 'order_estimatedcost',
t3.deliverycount as 'order_deliverycount',
t4.weight
from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid = t2.dataid
left join ( select dataid,sum(deliverycount) as 'deliverycount' from dc_base_transfer_info_skus GROUP BY dataid ) as t3 on  t1.dataid = t3.dataid
left join dc_base_sku as t4 on t2.sku = t4.bailun_sku
where  t1.dataid in @dataIds", new
                {
                    dataIds = top5DataIds
                }, commandTimeout: 0).ToList();

                if (tempDatas != null && tempDatas.Count >= 1)
                {
                    List<dc_base_transfer_freight_new_order> orders = new List<dc_base_transfer_freight_new_order>();
                    foreach (var itemGroup in tempDatas.GroupBy(s => s.code))
                    {
                        //计算该订单总sku重量
                        var sumWeight = itemGroup.Sum(s => s.weight * s.deliverycount);
                        var sumSkuCount = itemGroup.Sum(s => s.deliverycount);
                        var sumCost = itemGroup.FirstOrDefault().order_estimatedcost;
                        List<dc_base_transfer_freight_new_order> groupOrders = new List<dc_base_transfer_freight_new_order>();
                        foreach (var item in itemGroup.AsQueryable())
                        {
                            dc_base_transfer_freight_new_order new_order = new dc_base_transfer_freight_new_order()
                            {
                                main_bailun_sku = bailun_sku,
                                bailun_sku = item.sku,
                                gmt_update_time = DateTime.Now,
                                sku_count = item.deliverycount,
                                transfer_order_id = itemGroup.Key,
                                sku_weight = item.weight * item.deliverycount,
                                warehouse_code = warehouse_code,
                                order_cost = item.order_estimatedcost,
                                order_sku_count = sumSkuCount,
                                order_weight = sumWeight,
                                sku_cost = Math.Round((item.weight * item.deliverycount).Division(sumWeight) * item.order_estimatedcost, 2)
                            };

                            groupOrders.Add(new_order);
                        }
                        var cha = (sumCost - groupOrders.Sum(s => s.sku_cost));
                        if (cha > 0)
                        {
                            var topSkuOrder = groupOrders.OrderByDescending(s => s.order_cost).FirstOrDefault();

                            topSkuOrder.order_cost += cha;
                        }
                        orders.AddRange(groupOrders);
                        foreach (var itemOrder in orders)
                        {
                            //_connection.Insert(itemOrder);
                        }


                    }

                    var thisOrders = orders.Where(s => s.bailun_sku == bailun_sku).ToList();
                    dc_base_transfer_freight_new newData = new dc_base_transfer_freight_new()
                    {
                        bailun_sku = bailun_sku,
                        count = thisOrders.Sum(s => s.sku_count),
                        freight = thisOrders.Sum(s => s.sku_cost),
                        update_time = DateTime.Now,
                        warehouse_code = warehouse_code,
                        id = _connection.QueryFirstOrDefault<int?>(" select id from dc_base_transfer_freight_new where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = bailun_sku, warehouse_code = warehouse_code }) ?? 0
                    };
                    newData.freight_unit_price = newData.freight.Division(newData.count);
                    if (newData.id <= 0)
                    {
                        _connection.Insert(newData);
                    }
                    else
                    {
                        _connection.Update(newData);
                    }
                    return newData.freight_unit_price;
                }
                else
                {
                    // 清空运费
                    _connection.Execute(" delete from dc_base_transfer_freight_new where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                    {
                        bailun_sku = bailun_sku,
                        warehouse_code = warehouse_code,
                    });
                    return 0;
                }
            }
            else
            {
                return 0;
            }
           
        }

        public class CalculationFreightDto
        {
            public string sku { get; set; }
            public string code { get; set; }
            /// <summary>
            /// 发货数量
            /// </summary>
            public decimal deliverycount { get; set; }
            /// <summary>
            /// 调拨单总运费
            /// </summary>
            public decimal order_estimatedcost { get; set; }
            public string order_deliverycount { get; set; }

            /// <summary>
            /// sku 单位重量
            /// </summary>
            public decimal weight { get; set; }


        }
    }
}
