using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using AutoTurnOver.Models;
using Dapper;
using System.Linq;
using AutoTurnOver.Models.Base;
using AutoTurnOver.DB.Base;
using AutoTurnOver.Models.stock;
using AutoTurnOver.Utility;
using AutoTurnOver.Models.Report;
using System.Threading;
using Newtonsoft.Json;
using AutoTurnOver.Models.auto;

namespace AutoTurnOver.DB
{
    public class dc_auto_turnover : connectionHelper
    {
        public dc_auto_turnover()
        {
            Dapper.SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        }

        /// <summary>
        /// 获取自动周转分页列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="total"></param>
        /// <param name="order">排序类型</param>
        /// <param name="sort">排序字段</param>
        /// <returns></returns>
        public static List<Models.dc_auto_turnover_list_dto> List(Condition_AutoTurnOver m, int offset, int limit, ref int total, string order = "", string sort = "", bool isSum = false)
        {
            try
            {
                var sql = "";
                if (isSum)
                {
                    sql = @"select 
sum(dat.quantity_inventory) as 'quantity_inventory',
sum(dat.quantity_out_stock) as 'quantity_out_stock',
sum(dat.quantity_purchase) as 'quantity_purchase',
sum(dat.quantity_transfer) as 'quantity_transfer',
-- sum(t2.oneday_sales) as 'oneday_sales',
-- sum(t2.forecast_oneday_sales) as 'forecast_oneday_sales2',
sum(dat.quantity_final_advise) as 'quantity_final_advise',
sum(dat.quantity_promotion) as 'quantity_promotion',
sum(dat.quantity_safe_inventory) as 'quantity_safe_inventory'
from dc_auto_turnover as dat
left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code
left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code
left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku
where 1=1 ";
                }
                else
                {
                    sql = @"select (t3.warehouse_name)  as 'db_warehouse_name', dat.*,t2.oneday_sales,dat.forecast_oneday_sales as 'forecast_oneday_sales2',
t4.bailun_category_name,
t4.product_type_desc,
t3.hq_type as 'warehouse_type',
( case when t5.`status`=0 or t5.`status` is null then 0 else 1 end ) as 'monitor_status',
( case when t12.`status`=0 or t12.`status` is null then 0 else 1 end ) as 'returngoodspush_state',
t4.suppliers_link,
(t4.supplier_delivery - t6.virtual_delivery_days) as 'delivery_days_error',t4.weight,t4.unit_price,t7.usable_stock,t7.shipped_stock,
t4.develop_time,(case when t8.id is null then '未侵权' else  '侵权' end) as 'has_tort',
( case when t5.`status`=0 or t5.`status` is null then null else t5.gmt_create end ) as 'stop_monitor_create',
t4.category_simple_name,
t4.brand as 'brand',
t7.first_inbound_date,

t3.area_name,
-- ifnull(t15.count,0) as 'not_trans_count',

t13.quantity_transfer_order as 'quantity_transfer_order',
t13.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t13.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage',

(case when t3.hq_type='国内仓' then t4.supplier_delivery else t4.transfer_delivery end  ) as 'supplier_delivery_config',
dat.quantity_final_advise  - t17.stock -  ifnull(t13.quantity_purchase,0) - ifnull(t13.quantity_transfer_temp_schedule,0) - ifnull(t13.quantity_transfer_temporary_storage,0) as 'storage_capacity_lack_quantity',
t17.stock as 'storage_capacity',

t13.quantity_land_purchase as 'quantity_land_purchase',
t13.quantity_ocean_purchase as 'quantity_ocean_purchase',
t13.quantity_air_purchase as 'quantity_air_purchase',
t13.quantity_railway_purchase as 'quantity_railway_purchase',
t13.quantity_land_transfer as 'quantity_land_transfer',
t13.quantity_ocean_transfer as 'quantity_ocean_transfer',
t13.quantity_air_transfer as 'quantity_air_transfer',
t13.quantity_railway_transfer as 'quantity_railway_transfer',
dat.dc_auto_sales_forecast_title,
-- t14.title as 'dc_auto_sales_forecast_title',
t4.`status` as 'sku_status'-- ,
-- t11.label as 'sku_label'
-- ,t10.`name` as 'group_name',



from dc_auto_turnover as dat
left join dc_auto_sales as t2 on t2.bailun_sku=dat.bailun_sku and dat.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code
left join dc_base_sku as t4 on dat.bailun_sku = t4.bailun_sku
left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code
left join dc_average_purchase as t6 on dat.bailun_sku = t6.bailun_sku and dat.warehouse_code  =t6.warehouse_code and t4.suppliers_id = t6.supplier_id
left join dc_base_stock as t7 on dat.bailun_sku = t7.bailun_sku and dat.warehouse_code = t7.warehouse_code
left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku
-- left join dc_auto_stock_up_range as t9 on dat.bailun_sku = t9.bailun_sku and dat.warehouse_code = t9.warehouse_code
-- left join dc_auto_jit_tag_group as t10 on t9.group_id = t10.id
-- left join dc_auto_config_sku_label as t11 on dat.bailun_sku = t11.bailun_sku
left join dc_return_goods_not_push as t12 on dat.bailun_sku = t12.bailun_sku and dat.warehouse_code = t12.warehouse_code
left join dc_mid_transit as t13 on dat.bailun_sku = t13.bailun_sku and dat.warehouse_code = t13.warehouse_code
-- left join dc_auto_sales_forecast_config as t14 on dat.dc_auto_sales_forecast_config_id = t14.id 
-- left join not_trans_view as t15 on dat.bailun_sku = t15.bailun_sku and dat.warehouse_code = t15.warehouse_code
left join dc_base_storage_capacity as t17 on dat.bailun_sku_warehouse_code = t17.bailun_sku_warehouse_code

 ";
                }

                string sqlCount = @"select count(1)
from dc_auto_turnover as dat
";
                DynamicParameters parameters = new DynamicParameters();

                if (string.IsNullOrWhiteSpace(m.sku_label))
                {
                    sqlCount += " left join dc_auto_config_sku_label as t11 on dat.bailun_sku = t11.bailun_sku ";
                }
                if (m.warehousearea > 0)
                {
                    sqlCount += " left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code ";
                }
                if (m.categoryModels != null || (!string.IsNullOrWhiteSpace(m.product_type)))
                {
                    sqlCount += " left join dc_base_sku as t4 on dat.bailun_sku = t4.bailun_sku ";
                }
                if (m.monitor_status != null)
                {
                    sqlCount += " left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code ";
                }
                if (m.storage_capacity_status != null)
                {
                    sqlCount += " left join dc_base_storage_capacity_config as t16 on dat.bailun_sku = t16.bailun_sku and dat.warehouse_code = t16.warehouse_code ";
                    sql += " left join dc_base_storage_capacity_config as t16 on dat.bailun_sku = t16.bailun_sku and dat.warehouse_code = t16.warehouse_code ";
                }
                if (m.returngoodspush_state != null)
                {
                    sqlCount += " left join dc_return_goods_not_push as t12 on dat.bailun_sku = t12.bailun_sku and dat.warehouse_code = t12.warehouse_code ";
                }
                if (m.has_tort != null)
                {
                    sqlCount += " left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku ";
                }
                if (m.oneday_sales_min > 0 || m.oneday_sales_max > 0)
                {
                    sqlCount += " left join dc_auto_sales as t2 on t2.bailun_sku=dat.bailun_sku and dat.warehouse_code = t2.warehouse_code ";
                }
                if (m.not_trans_count_max > 0 || m.not_trans_count_min > 0)
                {
                    sqlCount += " left join not_trans_view as t15 on dat.bailun_sku = t15.bailun_sku and dat.warehouse_code = t15.warehouse_code ";
                }
                if (m.storage_capacity_lack_quantity_max > 0 || m.storage_capacity_lack_quantity_min > 0)
                {
                    sqlCount += " left join dc_mid_transit as t13 on dat.bailun_sku = t13.bailun_sku and dat.warehouse_code = t13.warehouse_code ";
                    sqlCount += " inner join dc_base_storage_capacity as t17 on dat.bailun_sku_warehouse_code = t17.bailun_sku_warehouse_code ";
                }
                sqlCount += " where 1=1 ";
                sql += " where 1=1 ";
                var whereSql = "";
                if (m.has_tort != null)
                {
                    if (m.has_tort == 0)
                    {
                        whereSql += " and t8.id is null ";
                        sqlCount += " and t8.id is null ";
                    }
                    else if (m.has_tort == 1)
                    {
                        whereSql += " and t8.id is not null ";
                        sqlCount += " and t8.id is not null ";
                    }
                }

                if (m.categoryModels != null)
                {
                    whereSql += " and t4.category_simple_id in ( " + string.Join(",", m.categoryModels.Select(s => s.id).Distinct()) + ")";
                    sqlCount += " and t4.category_simple_id in ( " + string.Join(",", m.categoryModels.Select(s => s.id).Distinct()) + ")";
                }
                if (!string.IsNullOrWhiteSpace(m.sku_label))
                {
                    whereSql += " and t11.label = " + $"'{m.sku_label}'";
                    sqlCount += " and t11.label = " + $"'{m.sku_label}'";
                }
                if (!string.IsNullOrWhiteSpace(m.buyer_name))
                {
                    whereSql += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                    sqlCount += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                }
                if (!string.IsNullOrWhiteSpace(m.product_type))
                {
                    whereSql += " and t4.product_type = " + $"'{m.product_type}'";
                    sqlCount += " and t4.product_type = " + $"'{m.product_type}'";
                }
                if (m.monitor_status == 1)
                {
                    whereSql += " and t5.`status`=1 ";
                    sqlCount += " and t5.`status`=1 ";
                }
                if (m.monitor_status == 0)
                {
                    whereSql += " and ( t5.`status`=0 or t5.`status` is null ) ";
                    sqlCount += " and ( t5.`status`=0 or t5.`status` is null ) ";
                }
                if (m.storage_capacity_status == 1)
                {
                    whereSql += " and  t16.`status`=1   ";
                    sqlCount += " and  t16.`status`=1  ";
                }
                if (m.storage_capacity_status == 0)
                {
                    whereSql += " and   t16.`status` is null  ";
                    sqlCount += " and  t16.`status` is null  ";
                }

                if (m.is_mother_sku == 1)
                {
                    whereSql += " and  dat.`is_mother_sku`=1   ";
                    sqlCount += " and  dat.`is_mother_sku`=1  ";
                }
                if (m.is_mother_sku == 0)
                {
                    whereSql += " and   dat.`is_mother_sku` is null  ";
                    sqlCount += " and  dat.`is_mother_sku` is null  ";
                }

                if (m.returngoodspush_state == 1)
                {
                    whereSql += " and t12.`status`=1 ";
                    sqlCount += " and t12.`status`=1 ";
                }
                if (m.returngoodspush_state == 0)
                {
                    whereSql += " and ( t12.`status`=0 or t12.`status` is null ) ";
                    sqlCount += " and ( t12.`status`=0 or t12.`status` is null ) ";
                }

                if (m.hasAadvise == true)
                {
                    whereSql += " and dat.quantity_final_advise>0 ";
                    sqlCount += " and dat.quantity_final_advise>0 ";
                }
                if (m.hasAadvise == false)
                {
                    whereSql += " and dat.quantity_final_advise<=0 ";
                    sqlCount += " and dat.quantity_final_advise<=0 ";
                }
                if (!string.IsNullOrWhiteSpace(m.key_words) && m.searchType > 0)
                {
                    switch (m.searchType.Value)
                    {
                        case 1:
                            whereSql += " and dat.bailun_sku in @bailun_skus ";
                            sqlCount += " and dat.bailun_sku in @bailun_skus ";
                            parameters.Add("bailun_skus", m.key_words.Split(',').ToArray());
                            break;
                        case 2:
                            whereSql += " and dat.bailun_sku like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.bailun_sku =" + $"'%{m.key_words}%'";
                            break;
                        case 3:
                            whereSql += " and dat.product_inner_code in @product_inner_code ";
                            sqlCount += " and dat.product_inner_code in @product_inner_code ";
                            parameters.Add("product_inner_code", m.key_words.Split(',').ToList());
                            break;
                        case 4:
                            whereSql += " and dat.product_inner_code like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.product_inner_code like" + $"'%{m.key_words}%'";
                            break;
                        case 5:
                            whereSql += " and dat.product_code =" + $"'{m.key_words}'";
                            sqlCount += " and dat.product_code =" + $"'{m.key_words}'";
                            break;
                        case 6:
                            whereSql += " and dat.product_code like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.product_code like" + $"'%{m.key_words}%'";
                            break;
                        case 7:
                            whereSql += " and dat.sku_title like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.sku_title like" + $"'%{m.key_words}%'";
                            break;
                        case 8:
                            var mother_sku_warehouse_codes = _connection.Query<string>(@" select mother_sku_warehouse_code from dc_base_mother_sku_map as tm where  tm.bailun_sku = @bailun_sku  ", new { bailun_sku = m.key_words }).ToList();
                            if (mother_sku_warehouse_codes != null && mother_sku_warehouse_codes.Count >= 1)
                            {
                                whereSql += " and dat.bailun_sku_warehouse_code in @bailun_sku_warehouse_codes";
                                sqlCount += " and dat.bailun_sku_warehouse_code in @bailun_sku_warehouse_codes";
                                parameters.Add("bailun_sku_warehouse_codes", mother_sku_warehouse_codes);
                            }
                            else
                            {
                                whereSql += " and 1=2 ";
                                sqlCount += " and 1=2 ";
                            }

                            break;
                        default: break;
                    }

                }

                if (m.hasDefectConfig == true)
                {
                    whereSql += " and ( dat.abroad_inbound_delivery <=0  or dat.transfer_bale_delivery <=0 or dat.transfer_delivery <=0   )  ";
                    sqlCount += " and ( dat.abroad_inbound_delivery <=0  or dat.transfer_bale_delivery <=0 or dat.transfer_delivery <=0   )  ";
                }
                if (m.hasDefectConfig == false)
                {
                    whereSql += " and ( dat.abroad_inbound_delivery >0  and dat.transfer_bale_delivery >0 and dat.transfer_delivery >0   )  ";
                    sqlCount += " and ( dat.abroad_inbound_delivery >0  and dat.transfer_bale_delivery >0 and dat.transfer_delivery >0   )  ";
                }
                if (!string.IsNullOrEmpty(m.warehouse_code))
                {
                    whereSql += " and dat.warehouse_code='" + m.warehouse_code + "'";
                    sqlCount += " and dat.warehouse_code='" + m.warehouse_code + "'";
                }
                else
                {
                    if (!string.IsNullOrWhiteSpace(m.warehousetype))
                    {
                        whereSql += " and dat.hq_type=" + $"'{m.warehousetype}'";
                        sqlCount += " and dat.hq_type=" + $"'{m.warehousetype}'";
                    }
                    if (m.warehousearea > 0)
                    {
                        whereSql += " and t3.area_id=" + m.warehousearea;
                        sqlCount += " and t3.area_id=" + m.warehousearea;
                    }
                }

                if (m.out_of_stock.HasValue)
                {
                    whereSql += " and dat.status=1";
                    sqlCount += " and dat.status=1";
                }
                if (!string.IsNullOrWhiteSpace(m.supplier_name))
                {
                    whereSql += " and dat.suppliers_name = '" + m.supplier_name + "'";
                    sqlCount += " and dat.suppliers_name = '" + m.supplier_name + "'";
                }

                if (m.quantity_inventory_min != null)
                {
                    whereSql += " and dat.quantity_inventory>=@quantity_inventory_min ";
                    sqlCount += " and dat.quantity_inventory>=@quantity_inventory_min ";
                    parameters.Add("quantity_inventory_min", m.quantity_inventory_min);
                }
                if (m.quantity_inventory_max != null)
                {
                    whereSql += " and dat.quantity_inventory<=@quantity_inventory_max ";
                    sqlCount += " and dat.quantity_inventory<=@quantity_inventory_max ";
                    parameters.Add("quantity_inventory_max", m.quantity_inventory_max);
                }
                if (m.not_trans_count_min != null)
                {
                    whereSql += " and t15.count>=@not_trans_count_min ";
                    sqlCount += " and t15.count>=@not_trans_count_min ";
                    parameters.Add("not_trans_count_min", m.not_trans_count_min);
                }

                if (m.quantity_out_stock_max != null)
                {
                    whereSql += " and dat.quantity_out_stock>=@quantity_out_stock_max ";
                    sqlCount += " and dat.quantity_out_stock>=@quantity_out_stock_max ";
                    parameters.Add("quantity_out_stock_max", m.quantity_out_stock_max);
                }
                if (m.oneday_sales_min != null)
                {
                    whereSql += " and t2.oneday_sales>=@oneday_sales_min ";
                    sqlCount += " and t2.oneday_sales>=@oneday_sales_min ";
                    parameters.Add("oneday_sales_min", m.oneday_sales_min);
                }
                if (m.oneday_sales_max != null)
                {
                    whereSql += " and t2.oneday_sales<=@oneday_sales_max ";
                    sqlCount += " and t2.oneday_sales<=@oneday_sales_max ";
                    parameters.Add("oneday_sales_max", m.oneday_sales_max);
                }
                if (m.storage_capacity_lack_quantity_min != null)
                {
                    whereSql += " and (dat.quantity_final_advise  - t17.stock -  ifnull(t13.quantity_purchase,0) - ifnull(t13.quantity_transfer_temp_schedule,0) - ifnull(t13.quantity_transfer_temporary_storage,0)   )>=@storage_capacity_lack_quantity_min ";
                    sqlCount += " and (dat.quantity_final_advise  - t17.stock -  ifnull(t13.quantity_purchase,0) - ifnull(t13.quantity_transfer_temp_schedule,0) - ifnull(t13.quantity_transfer_temporary_storage,0)  )>=@storage_capacity_lack_quantity_min ";
                    parameters.Add("storage_capacity_lack_quantity_min", m.storage_capacity_lack_quantity_min);
                }
                if (m.storage_capacity_lack_quantity_max != null)
                {
                    whereSql += " and (dat.quantity_final_advise  - t17.stock -  ifnull(t13.quantity_purchase,0) - ifnull(t13.quantity_transfer_temp_schedule,0) - ifnull(t13.quantity_transfer_temporary_storage,0)  )<=@storage_capacity_lack_quantity_max ";
                    sqlCount += " and (dat.quantity_final_advise  - t17.stock -  ifnull(t13.quantity_purchase,0) - ifnull(t13.quantity_transfer_temp_schedule,0) - ifnull(t13.quantity_transfer_temporary_storage,0)  )<=@storage_capacity_lack_quantity_max ";
                    parameters.Add("storage_capacity_lack_quantity_max", m.storage_capacity_lack_quantity_max);
                }
                if (m.avg_type > 0)
                {
                    var field_str = "";
                    switch (m.avg_type)
                    {
                        case 1: field_str = "dat.history_sevenday_sales"; break;
                        case 2: field_str = "dat.history_fourteenday_sales"; break;
                        case 3: field_str = "dat.history_thirtyday_sales"; break;
                        case 4: field_str = "dat.forecast_sevenday_sales"; break;
                        case 5: field_str = "dat.forecast_fourteenday_sales"; break;
                        case 7: field_str = "dat.history_sevenday_sales_ebay"; break;
                        case 8: field_str = "dat.history_fourteenday_sales_ebay"; break;
                        case 9: field_str = "dat.history_thirtyday_sales_ebay"; break;
                        case 10: field_str = "dat.history_sevenday_sales_aliexpress"; break;
                        case 11: field_str = "dat.history_fourteenday_sales_aliexpress"; break;
                        case 12: field_str = "dat.history_thirtyday_sales_aliexpress"; break;
                        case 13: field_str = "dat.history_sevenday_sales_amazon"; break;
                        case 14: field_str = "dat.history_fourteenday_sales_amazon"; break;
                        case 15: field_str = "dat.history_thirtyday_sales_amazon"; break;
                        case 16: field_str = "dat.history_sevenday_sales_walmart"; break;
                        case 17: field_str = "dat.history_fourteenday_sales_walmart"; break;
                        case 18: field_str = "dat.history_thirtyday_sales_walmart"; break;

                        default: break;
                    }
                    if (!string.IsNullOrWhiteSpace(field_str))
                    {
                        if (m.avg_sales_min != null)
                        {
                            whereSql += $" and {field_str}>=@avg_sales_min ";
                            sqlCount += $" and {field_str}>=@avg_sales_min ";
                            parameters.Add("avg_sales_min", m.avg_sales_min);
                        }
                        if (m.avg_sales_max != null)
                        {
                            whereSql += $" and {field_str}<=@avg_sales_max ";
                            sqlCount += $" and {field_str}<=@avg_sales_max ";
                            parameters.Add("avg_sales_max", m.avg_sales_max);
                        }
                    }

                }
                var limit_sql = "";
                if (isSum)
                {
                    total = 0;
                }
                else
                {

                    total = _connection.QuerySingleOrDefault<int>(sqlCount, parameters, commandTimeout: 0);
                    //Console.WriteLine(sqlCount);
                    //设置默认排序字段
                    if (string.IsNullOrWhiteSpace(sort)) sort = "dat.id";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        limit_sql += " order by " + sort;

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

                    limit_sql += " limit " + offset + "," + limit;
                }




                var obj = _connection.Query<Models.dc_auto_turnover_list_dto>(sql + whereSql + limit_sql, parameters, commandTimeout: 0).AsList();
                //Console.WriteLine(sql);
                if (obj != null && obj.Count >= 1)
                {
                    var storage_capacity_configs = _connection.Query<dc_base_storage_capacity_config>(" select * from dc_base_storage_capacity_config where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
                    {
                        bailun_sku_warehouse_codes = obj.Select(s => $"{s.bailun_sku}{s.warehouse_code}").ToList()
                    }).ToList();

                    foreach (var item in obj)
                    {
                        item.history_sevenday_sales = item.history_sevenday_sales.Round1();
                        item.history_fourteenday_sales = item.history_fourteenday_sales.Round1();
                        item.history_thirtyday_sales = item.history_thirtyday_sales.Round1();
                        item.history_sevenday_sales_ebay = item.history_sevenday_sales_ebay.Round1();
                        item.history_fourteenday_sales_ebay = item.history_fourteenday_sales_ebay.Round1();
                        item.history_thirtyday_sales_ebay = item.history_thirtyday_sales_ebay.Round1();
                        item.history_sevenday_sales_aliexpress = item.history_sevenday_sales_aliexpress.Round1();
                        item.history_fourteenday_sales_aliexpress = item.history_fourteenday_sales_aliexpress.Round1();
                        item.history_thirtyday_sales_aliexpress = item.history_thirtyday_sales_aliexpress.Round1();
                        item.history_sevenday_sales_amazon = item.history_sevenday_sales_amazon.Round1();
                        item.history_fourteenday_sales_amazon = item.history_fourteenday_sales_amazon.Round1();
                        item.history_thirtyday_sales_amazon = item.history_thirtyday_sales_amazon.Round1();

                        var storage_capacity_config = storage_capacity_configs.FirstOrDefault(s => s.bailun_sku_warehouse_code == $"{item.bailun_sku}{item.warehouse_code}") ?? new dc_base_storage_capacity_config { };
                        item.storage_capacity_status = storage_capacity_config.status;
                    }

                    //太大了可能是导出，就不查了
                    if (obj.Count <= 200)
                    {
                        // 查询所有映射
                        var map_datas = _connection.Query<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where mother_sku_warehouse_code in @mother_sku_warehouse_codes ", new
                        {
                            mother_sku_warehouse_codes = obj.Select(s => s.bailun_sku_warehouse_code).ToList()
                        }).ToList();
                        if (map_datas != null && map_datas.Count >= 1)
                        {
                            var map_turn_datas = _connection.Query<dc_auto_turnover_list_dto>(sql + " and dat.bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new { bailun_sku_warehouse_codes = map_datas.Select(s => s.bailun_sku_warehouse_code).ToList() });
                            foreach (var item in obj)
                            {
                                item.maps = map_turn_datas.Where(s => map_datas.Any(v => v.mother_sku_warehouse_code == item.bailun_sku_warehouse_code && s.bailun_sku_warehouse_code == v.bailun_sku_warehouse_code)).ToList();
                            }
                        }

                    }
                }

                return obj;
            }
            catch (Exception ex)
            {

                throw;
            }

        }

        /// <summary>
        /// 查询历史节点趋势图
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static charts_line_dto GetHistoryDataNodes(string sku, string warehousecode)
        {
            var btime = DateTime.Now.AddDays(-30).ToDayHome();
            var base_datas = _connection.Query<dc_base_forecast_history>(" select * from dc_base_forecast_history where `date`>=@btime and bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
            {
                bailun_sku_warehouse_code = $"{sku}{warehousecode}",
                btime = btime
            }).ToList();

            List<string> date_list = new List<string>();
            List<charts_line_dto.data_dto> datas = new List<charts_line_dto.data_dto>();
            var names = new List<string> { "供应链长度", "供应商交期", "质检入库天数", "调拨头程天数", "调拨打包天数", "海外仓入库天数" };
            foreach (var itemName in names)
            {
                datas.Add(new charts_line_dto.data_dto
                {
                    name = itemName,
                    type = "line",
                    data = new List<decimal> { }
                });
            }

            var nowDate = DateTime.Now;
            var this_date = btime;
            while (this_date.Date.ToDayHome() <= nowDate)
            {
                date_list.Add(this_date.ToString("yyyy-MM-dd"));
                var this_date_item = (base_datas.FirstOrDefault(s => s.date_str == this_date.ToString("yyyy-MM-dd")) ?? new dc_base_forecast_history());
                datas.FirstOrDefault(s => s.name == "供应链长度").data.Add(this_date_item.turnover_days);
                datas.FirstOrDefault(s => s.name == "供应商交期").data.Add(this_date_item.supplier_delivery);
                datas.FirstOrDefault(s => s.name == "质检入库天数").data.Add(this_date_item.inspection_delivery);
                datas.FirstOrDefault(s => s.name == "调拨头程天数").data.Add(this_date_item.transfer_delivery);
                datas.FirstOrDefault(s => s.name == "调拨打包天数").data.Add(this_date_item.transfer_bale_delivery);
                datas.FirstOrDefault(s => s.name == "海外仓入库天数").data.Add(this_date_item.abroad_inbound_delivery);
                this_date = this_date.AddDays(1);
            }

            return new charts_line_dto
            {
                y = datas,
                x = date_list
            };

        }

        /// <summary>
        /// 刷新历史数据
        /// </summary>
        public static void ResetHistory()
        {
            var now = DateTime.Now;
            var init_date = DateTime.Parse(now.ToString("yyyy-MM-dd 05:00:00"));
            if (now > init_date)
            {
                var init_now = DateTime.Now.ToDayHome();
                var end_date = DateTime.Parse(now.ToString("yyyy-MM-dd 23:00:00"));
                var total = _connection.QueryFirstOrDefault<int>(" select count(1) from dc_auto_turnover where gmt_modified<@init_now ", new { init_now });
                var rows = (int)Math.Floor(total / ((end_date - now).TotalMinutes / 11));
                var datas = _connection.Query<ResetHistoryDto>(" select bailun_sku,warehouse_code from dc_auto_turnover where gmt_modified<@init_now limit @rows ", new { init_now, rows }).ToList();
                if (datas != null)
                {
                    foreach (var item in datas)
                    {
                        _connection.Insert(new dc_base_queue
                        {
                            submit_date = DateTime.Now,
                            error_message = "",
                            consume_date = null,
                            error_stack_trace = "",
                            message = new { bailun_sku = item.bailun_sku, warehouse_code = item.warehouse_code }.ToJson(),
                            type = "刷新周转表",
                            remarks = "刷新已停止监控的历史数据"
                        });
                    }
                }
            }

        }
        public class ResetHistoryDto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
        }


        public static IEnumerable<string> GetLabelList()
        {
            return _connection.Query<string>(" select DISTINCT label from dc_auto_config_sku_label  ");
        }

        #region 特殊销售设置

        /// <summary>
        /// 获取特殊销售设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_auto_config_promotion> ListConfigPromotion(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_promotion>();

            try
            {
                var sql = "select dacp.id,dacp.bailun_sku,dacp.warehouse_code,dbw.warehouse_name,dacp.promotion_time,dacp.count,dacp.bl_auditor,dacp.remark,dacp.status from dc_auto_config_promotion dacp " +
                            "left join dc_base_warehouse as dbw on dacp.warehouse_code=dbw.warehouse_code where 1 =1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and dbw.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and dbw.area_id=" + m.warehousearea;
                }
                if (m.IsEffective == true)
                {
                    sql += $" and dacp.promotion_time>= '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.sku + "%'";
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " and dacp.warehouse_code='" + m.warehousecode + "'";
                }

                if (m.status.HasValue)
                {
                    sql += " dacp.and status=" + m.status.Value;
                }

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 查询未来指定时间范围内的特殊销量数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static IEnumerable<dc_auto_config_promotion> ListConfigPromotion(string sku, string warehousecode, DateTime btime, DateTime etime)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehousecode);
            parameters.Add("b_promotion_time", btime.ToString("yyyy-MM-dd 00:00:00"));
            parameters.Add("e_promotion_time", etime.ToString("yyyy-MM-dd 23:59:59"));
            return _connection.Query<Models.dc_auto_config_promotion>(@"select * from dc_auto_config_promotion where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and promotion_time>=@b_promotion_time and promotion_time<=@e_promotion_time ", parameters);
        }

        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveSaleSetting(Models.dc_auto_config_promotion m, string username)
        {
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_config_promotion>("select * from dc_auto_config_promotion where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_config_promotion
                {
                    gmt_create = DateTime.Now,
                };
            }
            else
            {
                if (obj.status != 0)
                {
                    return "该销售设置不能修改，目前状态为：" + Enum.GetName(typeof(EnumSaleSettingStatus), obj.status);
                }
            }

            obj.id = m.id;
            obj.bailun_sku = m.bailun_sku;
            obj.bl_operator = username ?? "";
            obj.bl_auditor = "";
            obj.gmt_modified = DateTime.Now;
            obj.promotion_time = m.promotion_time;
            obj.remark = m.remark;
            obj.status = m.status;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.count = m.count;

            try
            {
                if (obj.id > 0)
                {
                    var result = _connection.Update<Models.dc_auto_config_promotion>(obj);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_promotion>(obj);

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 批量追加
        /// </summary>
        /// <param name="datas"></param>
        public static void AddsSaleSetting(List<dc_auto_config_promotion_input> datas)
        {
            var _con = MyMySqlConnection._connection;
            _con.Open();
            IDbTransaction transaction = _con.BeginTransaction();
            try
            {
                foreach (var item in datas)
                {
                    var obj = new dc_auto_config_promotion();
                    obj.bailun_sku = item.sku;
                    obj.gmt_modified = DateTime.Now;
                    obj.promotion_time = item.time;
                    obj.bl_auditor = "";
                    obj.bl_operator = "";
                    obj.remark = item.note;
                    obj.status = 0;
                    obj.warehouse_code = item.warehousecode;
                    obj.warehouse_name = item.warehouse_name;
                    obj.count = item.count;

                    _con.Insert(obj, transaction);
                }

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                _con.Close();
                throw;
            }
        }

        /// <summary>
        /// 获取特殊销售设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_auto_config_promotion GetSaleSettingById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_promotion>("select * from dc_auto_config_promotion where id=" + id);
        }

        #endregion

        #region 预估库存

        /// <summary>
        /// 获取预估库存详情
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_auto_inventory GetAutoInventoryBySkuWH(string sku, string warehousecode)
        {
            var sql = "select * from dc_auto_inventory where warehouse_code='" + warehousecode + "' and bailun_sku='" + sku + "'";

            return _connection.QueryFirstOrDefault<dc_auto_inventory>(sql);
        }

        /// <summary>
        /// 历史库存
        /// </summary>
        /// <returns></returns>
        public static List<dc_daily_stock_dto> GetDailyStock(string bailun_sku, string warehouse_code, DateTime date)
        {
            return _connection.Query<dc_daily_stock_dto>(" select * from dc_daily_stock where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and record_time>=@record_time ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code,
                record_time = date
            }).ToList();
        }

        #endregion

        #region 预测入库

        /// <summary>
        /// 获取预测入库数详情
        /// </summary>
        /// <param name="sku">sku编码</param>
        /// <param name="warehousecode">库存编码</param>
        /// <param name="type">入库类型，1是采购入库, 2是调拨入库 </param>
        /// <returns></returns>
        public static dc_auto_inbound GetAutoInboundBySkuWH(string sku, string warehousecode, int type)
        {
            var sql = "select * from dc_auto_inbound where bailun_sku='" + sku + "' and warehouse_code='" + warehousecode + "'";
            if (type > 0)
            {
                sql += $" and type={type} ";
            }
            return _connection.QueryFirstOrDefault<dc_auto_inbound>(sql);
        }

        #endregion

        #region 预测断货

        /// <summary>
        /// 获取预测断货数详情
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_auto_short_supply GetAutoShortSupplyBySkuWH(string sku, string warehousecode)
        {
            var sql = "select * from dc_auto_short_supply where bailun_sku='" + sku + "' and warehouse_code='" + warehousecode + "'";

            return _connection.QueryFirstOrDefault<dc_auto_short_supply>(sql);
        }

        #endregion

        /// <summary>
        /// 查询周转数据
        /// </summary>
        /// <param name="sku">产品SKU</param>
        /// <param name="wCode">仓库编码</param>
        /// <returns></returns>
        public static Models.dc_auto_turnover GetModel(string sku, string wCode)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", wCode);
            return _connection.QueryFirst<Models.dc_auto_turnover>(" select * from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", parameters);
        }
        public static Models.dc_auto_turnover GetModelAir(string sku, string wCode)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", wCode);
            return _connection.QueryFirst<Models.dc_auto_turnover>(" select * from dc_auto_turnover_air where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", parameters);
        }
        public static Models.dc_auto_turnover GetModelOcean(string sku, string wCode)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", wCode);
            return _connection.QueryFirst<Models.dc_auto_turnover>(" select * from dc_auto_turnover_ocean where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", parameters);
        }


        #region 市场因素设置

        /// <summary>
        /// 获取市场因素设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_auto_config_correction> ListCorrection(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_correction>();

            try
            {
                var sql = @"select dacp.id,dacp.bailun_sku,dacp.warehouse_code,dbw.warehouse_name,dacp.start_time,dacp.end_time,dacp.param,dacp.remark from dc_auto_config_correction dacp 
                            left join dc_base_warehouse dbw on dacp.warehouse_code = dbw.warehouse_code where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and dbw.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and dbw.area_id=" + m.warehousearea;
                }
                if (m.IsEffective == true)
                {
                    var nowStr = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    sql += $" and dacp.start_time<= '{nowStr}' and dacp.end_time>={nowStr} ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.sku + "%'";
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " and dacp.warehouse_code='" + m.warehousecode + "'";
                }

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 查询未来指定时间范围内的市场因素数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static IEnumerable<dc_auto_config_correction> ListCorrection(string sku, string warehousecode, DateTime btime, DateTime etime)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehousecode);
            parameters.Add("b_time", btime.ToString("yyyy-MM-dd 00:00:00"));
            parameters.Add("e_time", etime.ToString("yyyy-MM-dd 23:59:59"));
            return _connection.Query<Models.dc_auto_config_correction>(@"select * from dc_auto_config_correction where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and ( end_time>=@b_time or start_time<=@e_time ) ", parameters);
        }

        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveCorrection(Models.dc_auto_config_correction m, string username)
        {
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_config_correction>("select * from dc_auto_config_correction where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_config_correction
                {
                    gmt_create = DateTime.Now,
                };
            }

            obj.id = m.id;
            obj.bailun_sku = m.bailun_sku;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.end_time = m.end_time;
            obj.start_time = m.start_time;
            obj.remark = m.remark;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.param = m.param;

            // 检查该数据在时间段上是否有交叉重复
            var oldDataList = ListCorrection(m.bailun_sku, m.warehouse_code, obj.start_time, obj.end_time);
            if (oldDataList != null && oldDataList.Any(s => s.id != obj.id))
            {
                return $" SKU:【{m.bailun_sku}】,仓库:【{m.warehouse_code}】 在在所选时间范围已经有配置了数据，请勿重复配置 ";
            }

            try
            {
                if (obj.id > 0)
                {
                    var result = _connection.Update<Models.dc_auto_config_correction>(obj);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_correction>(obj);

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 获取特殊销售设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_auto_config_correction GetCorrectionById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_correction>("select * from dc_auto_config_correction where id=" + id);
        }

        #endregion

        /// <summary>
        /// 查询SKU基础配置数据
        /// </summary>
        /// <returns></returns>
        public static List<dc_sku_config_dto> SkuConfigList()
        {
            var sql = @"
-- select * from view_estimated_arrival_time

select
 dbw.hq_type as 'warehouse_type',
 t_base.warehouse_code as 'warehouse_code',
 (case when dbw.hq_type='fba仓' then dbw.bailun_account else dbw.warehouse_name end)  as warehouse_name,
 t3.bailun_sku as 'bailun_sku',
 t3.sku_title_cn as 'sku_title',
 t3.product_code as 'product_code',
 t3.product_inner_code as 'product_inner_code',
 t3.buyer_name as 'buyer_name',

 t5.transfer_config_delivery as 'transfer_config_delivery', -- 头程调拨初始值
 t6.transfer_delivery as 'transfer_delivery', -- 头程调拨平均值
 t5.abroad_inbound_config_delivery as 'abroad_inbound_config_delivery', -- 海外仓入库天数初始值
 t6.abroad_inbound_delivery as 'abroad_inbound_delivery', -- 海外仓入库天数平均值
 t5.transfer_bale_config_delivery as 'transfer_bale_config_delivery', -- 海外仓打包天数初始
 t6.transfer_bale_delivery as 'transfer_bale_delivery', -- 海外仓打包天数平均值
 
 t3.supplier_delivery as 'supplier_delivery', -- 初始采购交期
 (case when t7.new_supplier_delivery >0 then t7.new_supplier_delivery else 0 end  ) as 'new_supplier_delivery', -- 最近一个单的预计到货时间
 
 ( case when t5.average_overdue> 0 then t5.average_overdue else null end ) as 'average_overdue', -- 安全库存波峰天数(平均逾期天数),
 t3.moq as 'moq',
 (( case when t5.average_overdue> 0 then t5.average_overdue else 1 end ) * t5.quantity_safe_inventory ) as 'quantity_safe', -- 安全库存数量 
 t9.delivery_days as 'average_arrival' ,-- '实际到货平均值',
 t1.usable_stock as 'stock', -- 实际库存
 t5.quantity_safe_inventory as 'quantity_safe_inventory', -- 日均加权销量
 t5.forecast_sevenday_sales as 'forecast_sevenday_sales', -- 未来7天日均
 t5.turnover_days as 'turnover_days', -- 周转天数（供应商长度）
 t4.quantity_purchase as 'realtime_quantity_purchase', -- 采购在途
 t4.quantity_transfer as 'realtime_quantity_transfer', -- 调拨在途
 t5.quantity_out_stock as 'quantity_out_stock', -- 缺货数量
 ( case when t8.`status`=0 or t8.`status` is null then '正常监控' else '停止监控' end ) as 'monitor_status'
from 
(SELECT
 t1.warehouse_code,t1.bailun_sku 
FROM
 dc_mid_transit AS t1
 
union

SELECT
  t1.warehouse_code,t1.bailun_sku 
FROM
 dc_base_stock AS t1
 ) as t_base
left join dc_base_stock as t1 on t_base.bailun_sku = t1.bailun_sku and t_base.warehouse_code = t1.warehouse_code
left join dc_base_sku as t3 on t_base.bailun_sku = t3.bailun_sku
left join dc_base_warehouse as dbw on t_base.warehouse_code = dbw.warehouse_code
left join dc_mid_transit as t4  on t_base.bailun_sku = t4.bailun_sku and t_base.warehouse_code = t4.warehouse_code
left join dc_auto_turnover as t5 on t_base.bailun_sku = t5.bailun_sku and t_base.warehouse_code = t5.warehouse_code
left join dc_average_warehouse as t6 on t_base.bailun_sku = t6.bailun_sku and t_base.warehouse_code = t6.warehouse_code
left join view_estimated_arrival_time as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_auto_config_sku_warehouse as t8 on t_base.bailun_sku = t8.bailun_sku and t_base.warehouse_code = t8.warehouse_code
left join dc_average_purchase as t9 on t1.bailun_sku  = t9.bailun_sku and t3.suppliers_id = t9.supplier_id
";

            var datas = _connection.Query<dc_sku_config_dto>(sql, commandTimeout: 0).ToList();

            return datas;
        }

        /// <summary>
        /// 查询SKU最近一个单的供应商交期
        /// </summary>
        /// <returns></returns>
        public static List<new_supplier_delivery_dto> SkuNewSupplierDeliveryList()
        {


            var sql = @"
select t7_1.warehouse_code,t7_1.bailun_sku,(to_days(t7_1.estimated_arrival_time) - ( case when t7_1.pay_type=1 then to_days(t7_1.pay_time) else to_days(t7_1.confirm_time) end ) ) as 'new_supplier_delivery' from dc_base_purchase  as t7_1
 	where t7_1.id in (
 		select max(id) from dc_base_purchase as t7_2 where t7_2.estimated_arrival_time is not null GROUP BY t7_2.warehouse_code,t7_2.bailun_sku
 	)
";
            var datas = _connection.Query<new_supplier_delivery_dto>(sql).ToList();


            return datas;
        }

        /// <summary>
        /// 备份预测值
        /// </summary>
        public static void CopyForecast()
        {
            _connection.Execute(@"
set session transaction isolation level read uncommitted;
REPLACE INTO dc_base_forecast_history(`bailun_sku`,`warehouse_code`,`bailun_sku_warehouse_code`,`date`,`date_str`,`create_date`,`sales`,`turnover_days`,`quantity_safe_inventory`,`supplier_delivery`,`transfer_config_delivery`,`transfer_bale_config_delivery`,`abroad_inbound_config_delivery`,`inspection_config_delivery`,`inspection_delivery`,`transfer_delivery`,`daily_weighted_sales`,`transfer_bale_delivery`,`abroad_inbound_delivery`,`quantity_final_advise`,`turnover_sales`)
(
select 
t1.bailun_sku,
t1.warehouse_code,
CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code',
t1.gmt_modified as 'date',
DATE_FORMAT(t1.gmt_modified,'%Y-%m-%d') as 'date_str',
now() as 'create_date',
t1.forecast_oneday_sales as 'sales',
t1.turnover_days as 'turnover_days',
t1.quantity_safe_inventory as 'quantity_safe_inventory',
t1.supplier_delivery as 'supplier_delivery',
t1.transfer_config_delivery as 'transfer_config_delivery',
t1.transfer_bale_config_delivery as 'transfer_bale_config_delivery',
t1.abroad_inbound_config_delivery as 'abroad_inbound_config_delivery',
t1.inspection_config_delivery as 'inspection_config_delivery',
t1.inspection_delivery as 'inspection_delivery',
t1.transfer_delivery as 'transfer_delivery',
t1.daily_weighted_sales as 'daily_weighted_sales',
t1.transfer_bale_delivery as 'transfer_bale_delivery',
t1.abroad_inbound_delivery as 'abroad_inbound_delivery',
t1.quantity_final_advise as 'quantity_final_advise',
t1.turnover_sales as 'turnover_sales'
from dc_auto_turnover as t1 
where t1.gmt_modified>=@btime and t1.gmt_modified<=@etime
)
", new { btime = DateTime.Now.ToDayHome(), etime = DateTime.Now.ToDayEnd() }, commandTimeout: 0);

            _connection.Execute(@" set session transaction isolation level read uncommitted;
update dc_base_forecast_history as t1
left join dc_base_stock as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
set t1.usable_stock = t2.usable_stock
where t1.date>=@btime ", new { btime = DateTime.Now.ToDayHome() }, commandTimeout: 0);
            _connection.Execute(@" set session transaction isolation level read uncommitted;
update dc_base_forecast_history as t1
left join dc_mid_transit as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
set t1.quantity_transfer = t2.quantity_transfer,t1.quantity_purchase = t2.quantity_purchase
where t1.date>=@btime ", new { btime = DateTime.Now.ToDayHome() }, commandTimeout: 0);


            _connection.Execute(@"
set session transaction isolation level read uncommitted;
REPLACE INTO dc_base_forecast_history_ocean(`bailun_sku`,`warehouse_code`,`bailun_sku_warehouse_code`,`date`,`date_str`,`create_date`,`sales`,`turnover_days`,`quantity_safe_inventory`,`supplier_delivery`,`transfer_config_delivery`,`transfer_bale_config_delivery`,`abroad_inbound_config_delivery`,`inspection_config_delivery`,`inspection_delivery`,`transfer_delivery`,`daily_weighted_sales`,`transfer_bale_delivery`,`abroad_inbound_delivery`,`quantity_final_advise`,`turnover_sales`)
(
select 
t1.bailun_sku,
t1.warehouse_code,
CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code',
t1.gmt_modified as 'date',
DATE_FORMAT(t1.gmt_modified,'%Y-%m-%d') as 'date_str',
now() as 'create_date',
t1.forecast_oneday_sales as 'sales',
t1.turnover_days as 'turnover_days',
t1.quantity_safe_inventory as 'quantity_safe_inventory',
t1.supplier_delivery as 'supplier_delivery',
t1.transfer_config_delivery as 'transfer_config_delivery',
t1.transfer_bale_config_delivery as 'transfer_bale_config_delivery',
t1.abroad_inbound_config_delivery as 'abroad_inbound_config_delivery',
t1.inspection_config_delivery as 'inspection_config_delivery',
t1.inspection_delivery as 'inspection_delivery',
t1.transfer_delivery as 'transfer_delivery',
t1.daily_weighted_sales as 'daily_weighted_sales',
t1.transfer_bale_delivery as 'transfer_bale_delivery',
t1.abroad_inbound_delivery as 'abroad_inbound_delivery',
t1.quantity_final_advise as 'quantity_final_advise',
t1.turnover_sales as 'turnover_sales'
from dc_auto_turnover_ocean as t1 
where t1.gmt_modified>=@btime and t1.gmt_modified<=@etime
)
", new { btime = DateTime.Now.ToDayHome(), etime = DateTime.Now.ToDayEnd() }, commandTimeout: 0);

            _connection.Execute(@"
set session transaction isolation level read uncommitted;
REPLACE INTO dc_base_forecast_history_air (`bailun_sku`,`warehouse_code`,`bailun_sku_warehouse_code`,`date`,`date_str`,`create_date`,`sales`,`turnover_days`,`quantity_safe_inventory`,`supplier_delivery`,`transfer_config_delivery`,`transfer_bale_config_delivery`,`abroad_inbound_config_delivery`,`inspection_config_delivery`,`inspection_delivery`,`transfer_delivery`,`daily_weighted_sales`,`transfer_bale_delivery`,`abroad_inbound_delivery`,`quantity_final_advise`,`turnover_sales`)
(
select 
t1.bailun_sku,
t1.warehouse_code,
CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code',
t1.gmt_modified as 'date',
DATE_FORMAT(t1.gmt_modified,'%Y-%m-%d') as 'date_str',
now() as 'create_date',
t1.forecast_oneday_sales as 'sales',
t1.turnover_days as 'turnover_days',
t1.quantity_safe_inventory as 'quantity_safe_inventory',
t1.supplier_delivery as 'supplier_delivery',
t1.transfer_config_delivery as 'transfer_config_delivery',
t1.transfer_bale_config_delivery as 'transfer_bale_config_delivery',
t1.abroad_inbound_config_delivery as 'abroad_inbound_config_delivery',
t1.inspection_config_delivery as 'inspection_config_delivery',
t1.inspection_delivery as 'inspection_delivery',
t1.transfer_delivery as 'transfer_delivery',
t1.daily_weighted_sales as 'daily_weighted_sales',
t1.transfer_bale_delivery as 'transfer_bale_delivery',
t1.abroad_inbound_delivery as 'abroad_inbound_delivery',
t1.quantity_final_advise as 'quantity_final_advise',
t1.turnover_sales
from dc_auto_turnover_air as t1 
where t1.gmt_modified>=@btime and t1.gmt_modified<=@etime
)
", new { btime = DateTime.Now.ToDayHome(), etime = DateTime.Now.ToDayEnd() }, commandTimeout: 0);
        }

        /// <summary>
        /// 获取数据来源
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_order_data_source_dto> GetOrderDataSource(dc_base_order_data_source_search_dto searchData, int offset, int limit, ref int total)
        {
            var sql = " select * from dc_base_order_data_source as t1 where 1=1 ";
            var countSql = " select count(1) from dc_base_order_data_source as t1 where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(searchData.bailun_sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku ";
                countSql += " and t1.bailun_sku=@bailun_sku ";
                parameters.Add("bailun_sku", searchData.bailun_sku);
            }
            if (!string.IsNullOrWhiteSpace(searchData.warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", searchData.warehouse_code);
            }
            if (!string.IsNullOrWhiteSpace(searchData.order_type_str))
            {
                sql += " and t1.order_type=@order_type ";
                countSql += " and t1.order_type=@order_type ";
                parameters.Add("order_type", searchData.order_type_str);
            }
            if (searchData.order_type == 24)
            {
                sql += " and t1.sail_days>0 ";
                countSql += " and t1.sail_days>0 ";
            }
            sql += " limit " + offset + "," + limit;
            total = _connection.QueryFirstOrDefault<int>(countSql, parameters);
            return _connection.Query<dc_base_order_data_source_dto>(sql, parameters).AsList();
        }

        /// <summary>
        /// 更新采购建议
        /// </summary>
        public static void ResetPurchaseAdviseSingle(string bailun_sku, string warehouse_code, string remarks)
        {
            _connection.Insert(new dc_base_queue
            {
                submit_date = DateTime.Now,
                error_message = "",
                consume_date = null,
                error_stack_trace = "",
                message = new { bailun_sku = bailun_sku, warehouse_code = warehouse_code }.ToJson(),
                type = "刷新周转表",
                remarks = remarks
            });
        }

        public static void WaitTurnoverQueueTask()
        {
            // 每秒检查一次
            Thread.Sleep(10 * 1000);
            var count = _connection.QueryFirstOrDefault<int>(" select count(1) from dc_base_queue where consume_date is null and queue_type=1 ", commandTimeout: 0);
            if (count > 0)
            {
                WaitTurnoverQueueTask();
            }
        }


        /// <summary>
        /// 调拨利润
        /// </summary>
        /// <returns></returns>
        public static transfer_profit_result_dto TransferProfitList(string bailun_sku, string warehouse_code, DateTime? btime = null)
        {
            transfer_profit_result_dto result_data = new transfer_profit_result_dto();
            List<transfer_profit_dto> datas = new List<transfer_profit_dto>() {
            new transfer_profit_dto{ field_name ="unit_price", name ="采购单价",remarks = "历史值：历史采购订单的价格，预测值：当前sku资料的采购价格",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_advise",  name ="实际建议周转数",remarks = "历史值：历史跑出来的的周转数，预测值：预测建议数",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_advise_amount",  name ="实际建议周转金额",remarks="历史值：当天的历史采购金额，预测值：预测周转数*采购单价",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="turnover",  name ="实际周转数",remarks = "（周转天数 * 日均销量）+ 安全库存 ",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="turnover_amount",  name ="实际周转金额",remarks = "实际周转数 * 采购单价",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price",  name ="实际周转运费",remarks = "周转数 * 实际运费单价",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_unit_price",  name ="实际运费单价",remarks ="移动平均数算法算出来的历史平均调拨费用",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_advise_ocean",  name ="海运周转数（推荐海运）",remarks = "（海运周转天数 * 日均销量）+ 安全库存",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_advise_ocean_amount",  name ="海运周转金额",remarks = "海运周转数 * 采购价 ",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_ocean",  name ="海运周转运费",remarks = "海运周转数 * 最便宜的海运物流跑出来的运费单价 ",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_advise_air",  name ="空运周转数（推荐空运）",remarks = "（空运运周转天数 * 日均销量）+ 安全库存",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_advise_air_amount",  name ="空运周转金额",remarks = "空运周转数 * 采购价",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_air",  name ="空运周转运费",remarks="空运周转数 * 最便宜的空运物流跑出来的运费单价 ",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_ocean_difference_amount",  name ="实际-海运运费差值",remarks = "【实际周转运费】-【海运周转运费】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_ocean_difference_amount",  name ="实际-海运周转采购金额差值",remarks = "【实际周转金额】-【海运周转金额】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_ocean_difference_amount_radio",  name ="差值(海运)-实际周转金额占比",remarks = "【实际-海运运费差值】/ 【实际周转运费】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_air_difference_amount",  name ="实际-空运运费差值",remarks="【实际周转运费】-【空运周转运费】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final_air_difference_amount",  name ="实际-空运周转采购金额差值",remarks = "【实际周转金额】-【空运周转金额】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_air_difference_amount_radio",  name ="差值(空运)-实际周转金额占比",remarks = "【实际-空运运费差值】/ 【实际周转运费】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_ocean_air_difference",  name ="海运-空运运费差值",remarks="【海运周转运费】-【空运运周转运费】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_final__ocean_air_difference_amount",  name ="海运-空运周转采购金额差值",remarks="【海运周转金额】-【空运周转金额】",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_ocean_air_difference_radio",  name ="差值(海运-空运)-实际周转金额占比",remarks ="【海运-空运周转运费】/【实际周转运费】 ",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="quantity_safe_inventory",remarks="安全库存数量"  ,name ="安全库存数量",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="turnover_days_air", remarks ="空运周转天数", name ="空运周转天数",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="turnover_days_ocean", remarks="海运周转天数", name ="海运周转天数",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_ocean_30_difference", remarks = "30天预测销量实际-海运金额汇总", name ="30天预测销量实际-海运金额汇总",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_air_30_difference",remarks ="30天预测销量空运-实际金额汇总"  ,name ="30天预测销量空运-实际金额汇总",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="freight_price_air_ocean_30_difference", remarks = "30天预测销量空运-海运金额汇总", name ="30天预测销量空运-海运金额汇总",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="order_count_3_radio", remarks = "空运调拨票数占比", name ="空运调拨票数占比",days = new List<transfer_profit_dto.day_dto>()},
            new transfer_profit_dto{field_name ="product_count_3_radio", remarks = "空运调拨数量占比", name ="空运调拨数量占比",days = new List<transfer_profit_dto.day_dto>()},
            };


            var result = GetModel(bailun_sku, warehouse_code);
            if (result.is_mother_sku == 1)
            {
                return null;
            }
            var result_air = GetModelAir(bailun_sku, warehouse_code);
            var result_ocean = GetModelOcean(bailun_sku, warehouse_code);
            var base_wa = _connection.QueryFirstOrDefault<string>(" select t2.`code` from dc_base_warehouse as t1 left join dc_base_country as t2 on t1.area_name = t2.`name` where warehouse_code=@warehouse_code ", new { warehouse_code });
            var quantity_transfer = _connection.QueryFirstOrDefault<int?>(" select quantity_transfer from dc_mid_transit as t1 where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new { bailun_sku, warehouse_code }) ?? 0;
            var quantity_purchase = _connection.QueryFirstOrDefault<int?>(" select quantity_purchase from dc_mid_transit as t1 where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new { bailun_sku, warehouse_code }) ?? 0;
            var dc_stock_data = _connection.QueryFirstOrDefault<int?>(" select usable_stock from dc_base_stock as t1 where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new { bailun_sku, warehouse_code }) ?? 0;
            var dc_base_sku_data = _connection.QueryFirstOrDefault<dc_base_sku>(" select * from dc_base_sku where bailun_sku=@bailun_sku ", new { bailun_sku });


            transfer_profit_modal_dto modalData = new transfer_profit_modal_dto();
            // 查询物流方案
            var logisticsDatas = ApiUtility.RealTimeShipLogisticsList(new Models.ApiDto.LmsShipLogisticsRequstDto
            {
                endCountries = base_wa,
                character_skus = bailun_sku,
                startPoint = "3",
                has_min_freight_unit_price = true,
                has_history_order = 90
            }).Where(s => s.min_freight_unit_price > 1);
            var logoCeanData = logisticsDatas.Where(s => s.TransportType == 2).OrderBy(s => s.min_freight_unit_price).FirstOrDefault();
            modalData.freight_unit_price_ocean = Math.Round(logoCeanData.min_freight_unit_price.Value / (logoCeanData.min_freight_unit_weight ?? 1) * (dc_base_sku_data.weight / 1000), 2);

            var logoAirData = logisticsDatas.Where(s => s.TransportType == 3).OrderBy(s => s.min_freight_unit_price).FirstOrDefault();
            modalData.freight_unit_price_air = Math.Round(logoAirData.min_freight_unit_price.Value / (logoAirData.min_freight_unit_weight ?? 1) * (dc_base_sku_data.weight / 1000), 2);

            var sales_details = result.sales_details.ToObj<List<decimal>>().Select(s => (int)Math.Ceiling(s)).ToList();
            var days30 = 30;
            var sales30 = 0;
            for (int i = 7; i < days30 + 7; i++)
            {
                if (sales_details.Count - 1 >= i)
                {
                    sales30 += sales_details[i];
                }
            }
            //var sales_details = result.sales_details.ToObj<List<decimal>>();
            var sales_details_air = result_air.sales_details.ToObj<List<decimal>>();
            var sales_details_ocean = result_ocean.sales_details.ToObj<List<decimal>>();
            if (btime == null)
            {
                btime = DateTime.Now.AddDays(-30);
            }

            var etime = DateTime.Now;
            var this_time = etime;
            var now = DateTime.Now.ToDayHome();
            var index = -23;
            while (btime.Value.ToDayHome() <= this_time.ToDayHome())
            {
                var this_time_end = this_time.ToDayEnd();
                // 预测未来
                if (this_time_end >= now)
                {
                    var average_warehouse_data = _connection.QueryFirstOrDefault<dc_average_warehouse>(" select * from dc_average_warehouse where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku limit 1 ", new
                    {
                        warehouse_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku
                    }) ?? new dc_average_warehouse { };
                    modalData.product_count = average_warehouse_data.product_count;
                    modalData.product_count_3 = average_warehouse_data.product_count_3;
                    modalData.order_count = average_warehouse_data.order_count;
                    modalData.order_count_3 = average_warehouse_data.order_count_3;
                    modalData.order_count_3_radio = modalData.order_count <= 0 ? 0 : modalData.order_count_3 / modalData.order_count;
                    modalData.product_count_3_radio = modalData.product_count <= 0 ? 0 : modalData.product_count_3 / modalData.product_count;

                    datas.FirstOrDefault(s => s.name == "空运调拨票数占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.order_count_3_radio,
                        formula = $" 空运调拨票数占比 "
                    });
                    datas.FirstOrDefault(s => s.name == "空运调拨数量占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.product_count_3_radio,
                        formula = $" 空运调拨票数占比 "
                    });

                    var dc_base_transfer_freight_data = _connection.QueryFirstOrDefault<dc_base_transfer_freight>(" select * from dc_base_transfer_freight where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku limit 1 ", new
                    {
                        warehouse_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku
                    }) ?? new dc_base_transfer_freight { };
                    modalData.freight_unit_price = dc_base_transfer_freight_data.freight_unit_price;
                    modalData.freight_price = dc_base_transfer_freight_data.freight;
                    modalData.freight_unit_price_formula = dc_base_transfer_freight_data.formula;
                    datas.FirstOrDefault(s => s.name == "实际运费单价").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_unit_price,
                        formula = $" 历史调拨单费用平均值 "
                    });

                    modalData.transfer_days_air = result_air.transfer_delivery + (result_air.abroad_inbound_delivery) + (result_air.transfer_bale_delivery);
                    modalData.transfer_days = result.transfer_delivery + (result.abroad_inbound_delivery) + (result.transfer_bale_delivery);
                    modalData.transfer_days_ocean = result_ocean.transfer_delivery + (result_ocean.abroad_inbound_delivery) + (result_ocean.transfer_bale_delivery);

                    modalData.transfer_sales = sales_details.Where((s, a_index) => a_index > 7 && a_index <= (7 + modalData.transfer_days)).Sum();
                    modalData.transfer_sales_air = sales_details_air.Where((s, a_index) => a_index > 7 && a_index <= (7 + modalData.transfer_days_air)).Sum();
                    modalData.transfer_sales_ocean = sales_details_ocean.Where((s, a_index) => a_index > 7 && a_index <= (7 + modalData.transfer_days_ocean)).Sum();

                    //30天周转数
                    modalData.quantity_final_advise_30 = sales30;
                    modalData.freight_price_30 = sales30 * modalData.freight_unit_price;
                    modalData.freight_price_ocean_30 = sales30 * modalData.freight_unit_price_ocean;
                    modalData.freight_price_air_30 = sales30 * modalData.freight_unit_price_air;
                    modalData.freight_price_ocean_30_difference = modalData.freight_price_30 - modalData.freight_price_ocean_30;
                    modalData.freight_price_air_30_difference = modalData.freight_price_air_30 - modalData.freight_price_30;
                    modalData.freight_price_air_ocean_30_difference = modalData.freight_price_air_30 - modalData.freight_price_ocean_30;

                    datas.FirstOrDefault(s => s.name == "30天预测销量实际-海运金额汇总").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_30_difference,
                        formula = $@" 【30天预测销量海运-实际金额汇总】 = 预测30日销量( {sales30}）* ( 海运运费单价({modalData.freight_unit_price_ocean}) - 历史平均运费单价（{modalData.freight_unit_price}）)
<br/>海运运费单价({modalData.freight_unit_price_ocean}) = （最近90天内发过的最便宜的海运物流 {logoCeanData.Line_Name} 运费单价 {logoCeanData.min_freight_unit_price / (logoCeanData.min_freight_unit_weight ?? 1) }/kg ）* 产品重量 （{dc_base_sku_data.weight / 1000} kg ）
"
                    });

                    datas.FirstOrDefault(s => s.name == "30天预测销量空运-实际金额汇总").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air_30_difference,
                        formula = $@" 【30天预测销量空运-实际金额汇总】 = 预测30日销量( {sales30}）* ( 空运运费单价({modalData.freight_unit_price_air}) - 历史平均运费单价（{modalData.freight_unit_price}）)
<br/>空运运费单价({modalData.freight_unit_price_air}) = （最近90天内发过的最便宜的空运物流 {logoAirData.Line_Name} 运费单价 {logoAirData.min_freight_unit_price / (logoAirData.min_freight_unit_weight ?? 1) }/kg ）* 产品重量 （{dc_base_sku_data.weight / 1000} kg ）
"
                    });
                    datas.FirstOrDefault(s => s.name == "30天预测销量空运-海运金额汇总").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air_ocean_30_difference
                    });

                    modalData.quantity_safe_inventory = result.quantity_safe_inventory;
                    modalData.daily_weighted_sales = result.daily_weighted_sales;
                    modalData.quantity_safe_inventory_days = result.daily_weighted_sales <= 0 ? 14 : (Math.Ceiling(result.quantity_safe_inventory * 1.0M / result.daily_weighted_sales));
                    datas.FirstOrDefault(s => s.name == "安全库存数量").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_safe_inventory,
                        formula = " 安全库存数量 = aims 系统配置天数 * 日均销量 "
                    });


                    modalData.turnover_days = result.turnover_days;
                    modalData.purchase_create_order_days = result.purchase_create_order_days;
                    modalData.payment_before_delivery = result.payment_before_delivery;
                    modalData.supplier_delivery = result.supplier_delivery;
                    modalData.inspection_delivery_days = result.inspection_delivery;
                    modalData.transfer_delivery = result.transfer_delivery;
                    modalData.abroad_inbound_delivery = result.abroad_inbound_delivery;
                    modalData.transfer_bale_delivery = result.transfer_bale_delivery;
                    modalData.transfer_order_days = result.transfer_order_days;
                    modalData.sail_days = result.transfer_sail_days;




                    modalData.turnover_days_air = result_air.turnover_days;
                    modalData.purchase_create_order_days_air = result_air.purchase_create_order_days;
                    modalData.payment_before_delivery_air = result_air.payment_before_delivery;
                    modalData.supplier_delivery_air = result_air.supplier_delivery;
                    modalData.inspection_delivery_days_air = result_air.inspection_delivery;
                    modalData.transfer_delivery_air = result_air.transfer_delivery;
                    modalData.abroad_inbound_delivery_air = result_air.abroad_inbound_delivery;
                    modalData.transfer_bale_delivery_air = result_air.transfer_bale_delivery;
                    modalData.sail_days_air = result_air.transfer_sail_days;
                    modalData.transfer_order_days_air = result_air.transfer_order_days;
                    datas.FirstOrDefault(s => s.name == "空运周转天数").days.Add(new transfer_profit_dto.day_dto
                    {
                        has_int = true,
                        date = this_time,
                        val = result_air.turnover_days,
                        formula = $" 【空运周转天数】 = 供应链长度( {result_air.turnover_days} ) = 下单天数( {result_air.purchase_create_order_days} ) + 财务付款时间( {result_air.payment_before_delivery} ) + 供应商交期( {result_air.supplier_delivery} ) + 质检入库天数( {result_air.inspection_delivery} ) + 调拨打包天数( {result_air.transfer_bale_delivery}  + 调拨下单天数( {result_air.transfer_bale_delivery} + 调拨启航等待天数(  {result_air.transfer_sail_days} ) + 调拨头程天数( {result_air.transfer_order_days} ) + 海外仓入库天数( {result_air.abroad_inbound_delivery} )  )"
                    });

                    modalData.turnover_days_ocean = result_ocean.turnover_days;
                    modalData.purchase_create_order_days_ocean = result_ocean.purchase_create_order_days;
                    modalData.payment_before_delivery_ocean = result_ocean.payment_before_delivery;
                    modalData.supplier_delivery_ocean = result_ocean.supplier_delivery;
                    modalData.inspection_delivery_days_ocean = result_ocean.inspection_delivery;
                    modalData.transfer_delivery_ocean = result_ocean.transfer_delivery;
                    modalData.abroad_inbound_delivery_ocean = result_ocean.abroad_inbound_delivery;
                    modalData.transfer_bale_delivery_ocean = result_ocean.transfer_bale_delivery;
                    modalData.transfer_order_days_ocean = result_ocean.transfer_order_days;
                    modalData.sail_days_ocean = result_ocean.transfer_sail_days;
                    datas.FirstOrDefault(s => s.name == "海运周转天数").days.Add(new transfer_profit_dto.day_dto
                    {
                        has_int = true,
                        date = this_time,
                        val = result_ocean.turnover_days,
                        formula = $" 【海运周转天数】 = 供应链长度( {result_ocean.turnover_days} ) = 下单天数( {result_ocean.purchase_create_order_days} ) + 财务付款时间( {result_ocean.payment_before_delivery} ) + 供应商交期( {result_ocean.supplier_delivery} ) + 质检入库天数( {result_ocean.inspection_delivery} ) + 调拨启航等待天数( 0 ) + 调拨头程天数( {result_ocean.transfer_delivery} ) + 海外仓入库天数( {result_ocean.abroad_inbound_delivery} ) + 调拨打包天数( {result_ocean.transfer_bale_delivery} )"
                    });

                    modalData.unit_price = dc_base_sku_data.unit_price;
                    datas.FirstOrDefault(s => s.name == "采购单价").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = Math.Round(modalData.unit_price, 1),
                        formula = " 【采购单价】从产品库读取配置"
                    });

                    // 周转天数 * 加权日均
                    modalData.quantity_final_advise = 0;
                    modalData.turnover_sales = result.turnover_sales;
                    if (result.turnover_sales > 0)
                    {
                        modalData.quantity_final_advise = (int)Math.Round(result.turnover_sales + result.quantity_safe_inventory);
                    }
                    datas.FirstOrDefault(s => s.name == "实际建议周转数").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise,
                        formula = $" 【实际建议周转数】= 供应链长度预测销量（{result.turnover_sales}） + 安全库存（{ result.quantity_safe_inventory}） "
                    });
                    modalData.quantity_final_advise_amount = Math.Round((modalData.quantity_final_advise) * (modalData.unit_price), 2);

                    datas.FirstOrDefault(s => s.name == "实际建议周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_amount,
                        formula = $" 【实际建议周转金额】 = 【实际建议周转数 {modalData.quantity_final_advise}】 * 【采购单价 {Math.Round(modalData.unit_price, 1)}】"
                    });

                    // 在库+在途
                    modalData.turnover = quantity_transfer + quantity_purchase + dc_stock_data;
                    modalData.dc_stock_data = dc_stock_data;
                    modalData.quantity_transfer = quantity_transfer;
                    modalData.quantity_purchase = quantity_purchase;
                    datas.FirstOrDefault(s => s.name == "实际周转数").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.turnover,
                        formula = $" 【实际周转数】 = 采购+调拨在途 （{quantity_transfer + quantity_purchase}） + 在库 （{dc_stock_data}） "
                    });

                    modalData.freight_price = dc_base_transfer_freight_data.freight;
                    datas.FirstOrDefault(s => s.name == "实际周转运费").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price,
                        formula = dc_base_transfer_freight_data.formula
                    });

                    // 实际建议周转运费
                    //modalData.quantity_final_advise_freight = Math.Round((modalData.quantity_final_advise ?? 0) * (modalData.freight_unit_price ?? 0), 2); ;

                    //实际周转金额
                    modalData.turnover_amount = modalData.turnover * modalData.unit_price;
                    datas.FirstOrDefault(s => s.name == "实际周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.turnover_amount,
                        formula = $"  "
                    });

                    modalData.turnover_sales_ocean = result_ocean.turnover_sales;
                    modalData.quantity_final_advise_ocean = (int)Math.Round(result_ocean.turnover_sales + result_ocean.quantity_safe_inventory);
                    datas.FirstOrDefault(s => s.name == "海运周转数（推荐海运）").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_ocean,
                        formula = $" 【海运周转数（推荐海运）】 = 海运供应链长度预测销量（{result_ocean.turnover_sales}） + 安全库存（{ result.quantity_safe_inventory}） "
                    });
                    modalData.quantity_final_advise_ocean_amount = Math.Round((modalData.quantity_final_advise_ocean) * (modalData.unit_price), 2);
                    datas.FirstOrDefault(s => s.name == "海运周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_ocean_amount,
                        formula = $" 【海运周转金额】 =  【海运周转数（推荐海运）{modalData.quantity_final_advise_ocean}】* 【采购单价 {modalData.unit_price}】"
                    });
                    modalData.freight_price_ocean = Math.Round((modalData.transfer_sales_ocean) * modalData.freight_unit_price_ocean, 2);
                    modalData.cean_line_name = logoCeanData.Line_Name;
                    modalData.cean_line_freight_unit_price = (logoCeanData.min_freight_unit_price ?? 0) / (logoCeanData.min_freight_unit_weight ?? 1);
                    modalData.weight = dc_base_sku_data.weight / 1000;
                    datas.FirstOrDefault(s => s.name == "海运周转运费").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean,
                        formula = $"  【海运周转运费】= 【海运周转数（推荐海运）{modalData.transfer_sales_ocean}】* 【（最近90天内发过的最便宜的海运物流 {logoCeanData.Line_Name} 运费单价 {logoCeanData.min_freight_unit_price / (logoCeanData.min_freight_unit_weight ?? 1) }/kg ）* 产品重量 （{dc_base_sku_data.weight / 1000} kg ）】 "
                    });

                    modalData.quantity_final_advise_air = (int)Math.Round(result_air.turnover_sales + result_air.quantity_safe_inventory);
                    modalData.turnover_sales_air = result_air.turnover_sales;
                    datas.FirstOrDefault(s => s.name == "空运周转数（推荐空运）").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_air,
                        formula = $" 【空运周转数（推荐空运）】  = 空运供应链长度预测销量（{result_air.turnover_sales}） + 安全库存（{ result_air.quantity_safe_inventory}）"
                    });
                    modalData.quantity_final_advise_air_amount = Math.Round(modalData.quantity_final_advise_air * modalData.unit_price, 2);
                    datas.FirstOrDefault(s => s.name == "空运周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_air_amount,
                        formula = $" 【空运周转金额】 =  【空运周转数（推荐空运）{modalData.quantity_final_advise_air}】* 【采购单价 {modalData.unit_price}】"
                    });
                    modalData.freight_price_air = Math.Round((modalData.transfer_sales_air) * modalData.freight_unit_price_air, 2);
                    modalData.air_line_name = logoAirData.Line_Name;
                    modalData.air_line_freight_unit_price = (logoAirData.min_freight_unit_price ?? 0) / (logoAirData.min_freight_unit_weight ?? 1);
                    datas.FirstOrDefault(s => s.name == "空运周转运费").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air,
                        formula = $"  【空运周转运费】= 【空运周转数（推荐空运）{modalData.quantity_final_advise_air}】* 【（最近90天内发过的最便宜的海运物流 {logoAirData.Line_Name} 运费单价 {logoAirData.min_freight_unit_price / (logoAirData.min_freight_unit_weight ?? 1) }/kg ）* 产品重量 （{dc_base_sku_data.weight / 1000} kg ）】 "

                    });

                    modalData.freight_price_ocean_difference_amount = modalData.freight_price - modalData.freight_price_ocean;
                    datas.FirstOrDefault(s => s.name == "实际-海运运费差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_difference_amount,
                        formula = $"【实际-海运运费差值】= 【实际周转运费 {modalData.freight_price }】 - 【海运周转运费 {modalData.freight_price_ocean}】"
                    });

                    modalData.quantity_final_ocean_difference_amount = modalData.turnover_amount - modalData.quantity_final_advise_ocean_amount;
                    datas.FirstOrDefault(s => s.name == "实际-海运周转采购金额差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_ocean_difference_amount,
                        formula = $"【实际-海运周转采购金额差值】= 【实际周转金额 {modalData.turnover_amount }】 - 【海运周转金额 {modalData.quantity_final_advise_ocean_amount}】"
                    });

                    modalData.freight_price_ocean_difference_amount_radio = (modalData.turnover_amount) <= 0 ? 0M : (Math.Round((modalData.quantity_final_ocean_difference_amount) / modalData.turnover_amount, 3));
                    datas.FirstOrDefault(s => s.name == "差值(海运)-实际周转金额占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_difference_amount_radio,
                        formula = $"【差值(海运)-实际周转金额占比】 = 【实际-海运周转金额差值 {modalData.quantity_final_ocean_difference_amount}】/ 【实际周转金额 {modalData.turnover_amount}】 "
                    });


                    modalData.freight_price_air_difference_amount = modalData.freight_price - modalData.freight_price_air;
                    datas.FirstOrDefault(s => s.name == "实际-空运运费差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air_difference_amount,
                        formula = $"【实际-空运运费差值】= 【实际周转运费 {modalData.freight_price }】 - 【空运运周转运费 {modalData.freight_price_air}】"
                    });

                    modalData.quantity_final_air_difference_amount = modalData.turnover_amount - modalData.quantity_final_advise_air_amount;
                    datas.FirstOrDefault(s => s.name == "实际-空运周转采购金额差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_air_difference_amount,
                        formula = $"【实际-空运运周转采购金额差值】= 【实际建议周转金额 {modalData.turnover_amount }】 - 【空运周转金额 {modalData.quantity_final_advise_air_amount}】"

                    });
                    modalData.freight_price_air_difference_amount_radio = (modalData.turnover_amount) <= 0 ? 0M : (Math.Round((modalData.quantity_final_air_difference_amount) / modalData.turnover_amount, 3));
                    datas.FirstOrDefault(s => s.name == "差值(空运)-实际周转金额占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air_difference_amount_radio,
                        formula = $"【差值(空运)-实际周转金额占比】 = 【实际-空运周转金额差值 {modalData.quantity_final_air_difference_amount}】/ 【实际建议周转金额 {modalData.turnover_amount}】 "

                    });
                    modalData.freight_price_ocean_air_difference = modalData.freight_price_ocean - modalData.freight_price_air;
                    datas.FirstOrDefault(s => s.name == "海运-空运运费差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_air_difference,
                        formula = $"【海运-空运运费差值】= 【海运周转运费 {modalData.freight_price_ocean }】 - 【空运运周转运费 {modalData.freight_price_air}】"
                    });

                    modalData.quantity_final__ocean_air_difference_amount = modalData.quantity_final_advise_ocean_amount - modalData.quantity_final_advise_air_amount;
                    datas.FirstOrDefault(s => s.name == "海运-空运周转采购金额差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final__ocean_air_difference_amount,
                        formula = $"【海运-空运周转采购金额差值】= 【海运周转采购金额 {modalData.quantity_final_advise_ocean_amount }】 - 【空运运周转采购金额 {modalData.quantity_final_advise_air_amount}】"
                    });

                    modalData.freight_price_ocean_air_difference_radio = (modalData.turnover_amount) <= 0 ? 0M : (Math.Round((modalData.quantity_final__ocean_air_difference_amount) / modalData.turnover_amount, 3));
                    datas.FirstOrDefault(s => s.name == "差值(海运-空运)-实际周转金额占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_air_difference_radio,
                        formula = $"【差值(海运-空运)-实际周转金额占比】 = 【海运-空运周转金额差值 {modalData.quantity_final__ocean_air_difference_amount}】/ 【实际周转运费 {modalData.turnover_amount}】 "

                    });
                    result_data.today_data = modalData.ToJson().ToObj<transfer_profit_modal_dto>();
                }
                else // 过去
                {
                    //var average_warehouse_data = _connection.QueryFirstOrDefault<dc_average_warehouse>(" select * from dc_average_warehouse where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku limit 1 ", new
                    //{
                    //    warehouse_code = result.warehouse_code,
                    //    bailun_sku = result.bailun_sku
                    //}) ?? new dc_average_warehouse { };
                    //modalData.product_count = average_warehouse_data.product_count;
                    //modalData.product_count_3 = average_warehouse_data.product_count_3;
                    //modalData.order_count = average_warehouse_data.order_count;
                    //modalData.order_count_3 = average_warehouse_data.order_count_3;
                    //modalData.order_count_3_radio = modalData.order_count <= 0 ? 0 : modalData.order_count_3 / modalData.order_count;
                    //modalData.product_count_3_radio = modalData.product_count <= 0 ? 0 : modalData.product_count_3 / modalData.product_count;

                    datas.FirstOrDefault(s => s.name == "空运调拨票数占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = null,
                        formula = $" 空运调拨票数占比 "
                    });
                    datas.FirstOrDefault(s => s.name == "空运调拨数量占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = null,
                        formula = $" 空运调拨票数占比 "
                    });

                    datas.FirstOrDefault(s => s.name == "30天预测销量实际-海运金额汇总").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = null
                    });

                    datas.FirstOrDefault(s => s.name == "30天预测销量空运-实际金额汇总").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = null
                    });
                    datas.FirstOrDefault(s => s.name == "30天预测销量空运-海运金额汇总").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = null
                    });

                    #region MyRegion
                    var dc_base_forecast_historydata = _connection.QueryFirstOrDefault<dc_base_forecast_history>(" select * from dc_base_forecast_history where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and date_str=@date_str limit 1 ", new
                    {
                        warehouse_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku,
                        date_str = this_time_end.ToString("yyyy-MM-dd")
                    }) ?? new dc_base_forecast_history();
                    modalData.quantity_safe_inventory = dc_base_forecast_historydata.quantity_safe_inventory;
                    datas.FirstOrDefault(s => s.name == "安全库存数量").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_safe_inventory
                    });

                    datas.FirstOrDefault(s => s.name == "空运周转天数").days.Add(new transfer_profit_dto.day_dto
                    {
                        has_int = true,
                        date = this_time,
                        val = _connection.QueryFirstOrDefault<int?>(" select turnover_days from dc_base_forecast_history_air where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and date_str=@date_str limit 1 ", new
                        {
                            warehouse_code = result.warehouse_code,
                            bailun_sku = result.bailun_sku,
                            date_str = this_time_end.ToString("yyyy-MM-dd")
                        })
                    });

                    datas.FirstOrDefault(s => s.name == "海运周转天数").days.Add(new transfer_profit_dto.day_dto
                    {
                        has_int = true,
                        date = this_time,
                        val = _connection.QueryFirstOrDefault<int?>(" select turnover_days from dc_base_forecast_history_ocean where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and date_str=@date_str limit 1 ", new
                        {
                            warehouse_code = result.warehouse_code,
                            bailun_sku = result.bailun_sku,
                            date_str = this_time_end.ToString("yyyy-MM-dd")
                        })
                    });

                    modalData.unit_price = _connection.QueryFirstOrDefault<decimal?>(" select unit_price from dc_base_purchase_details where warehouse_into_code=@warehouse_into_code and bailun_sku=@bailun_sku and status!=-1 and create_time<=@btime order by create_time desc limit 1 ", new
                    {
                        warehouse_into_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku,
                        btime = this_time_end
                    }) ?? 0;
                    datas.FirstOrDefault(s => s.name == "采购单价").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = Math.Round(modalData.unit_price, 1)
                    });
                    modalData.quantity_final_advise = 0;
                    if (dc_base_forecast_historydata.turnover_sales > 0)
                    {
                        modalData.quantity_final_advise = (int)Math.Round(dc_base_forecast_historydata.turnover_sales + dc_base_forecast_historydata.quantity_safe_inventory);
                    }

                    datas.FirstOrDefault(s => s.name == "实际建议周转数").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise
                    });
                    modalData.turnover_amount = modalData.quantity_final_advise * modalData.unit_price;
                    datas.FirstOrDefault(s => s.name == "实际周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.turnover_amount,
                        formula = $"  "
                    });
                    modalData.quantity_final_advise_amount = modalData.quantity_final_advise * modalData.unit_price;
                    datas.FirstOrDefault(s => s.name == "实际建议周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_amount
                    });
                    modalData.purchase_quantity = (dc_base_forecast_historydata.usable_stock + dc_base_forecast_historydata.quantity_purchase + dc_base_forecast_historydata.quantity_transfer);
                    datas.FirstOrDefault(s => s.name == "实际周转数").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.purchase_quantity
                    });
                    modalData.freight_unit_price = _connection.QueryFirstOrDefault<decimal?>(" select freight_unit_price from dc_base_transfer_freight_log where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and action_date<=@action_date limit 1 ", new
                    {
                        warehouse_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku,
                        action_date = this_time_end
                    }) ?? 0;
                    datas.FirstOrDefault(s => s.name == "实际运费单价").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_unit_price
                    });

                    //modalData.quantity_final_advise_freight = modalData.quantity_final_advise * modalData.freight_unit_price;
                    //modalData.freight_price = modalData.purchase_quantity * modalData.freight_unit_price;
                    datas.FirstOrDefault(s => s.name == "实际周转运费").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price
                    });

                    var dc_base_forecast_history_ocean_data = _connection.QueryFirstOrDefault<dc_base_forecast_history>(" select * from dc_base_forecast_history_ocean where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and `date`<=@date_str order by `date` desc limit 1 ", new
                    {
                        warehouse_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku,
                        date_str = this_time_end.ToString("yyyy-MM-dd")
                    }) ?? new dc_base_forecast_history() { };
                    modalData.quantity_final_advise_ocean = 0;
                    if (dc_base_forecast_history_ocean_data.turnover_sales > 0)
                    {
                        modalData.quantity_final_advise_ocean = (int)Math.Round((dc_base_forecast_history_ocean_data.turnover_sales + dc_base_forecast_history_ocean_data.quantity_safe_inventory));
                    }

                    datas.FirstOrDefault(s => s.name == "海运周转数（推荐海运）").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_ocean
                    });
                    modalData.quantity_final_advise_ocean_amount = modalData.quantity_final_advise_ocean * modalData.unit_price;
                    datas.FirstOrDefault(s => s.name == "海运周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_ocean_amount
                    });
                    modalData.freight_price_ocean = modalData.quantity_final_advise_ocean * modalData.freight_unit_price_ocean;
                    datas.FirstOrDefault(s => s.name == "海运周转运费").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean
                    });

                    var dc_base_forecast_history_air_data = _connection.QueryFirstOrDefault<dc_base_forecast_history>(" select * from dc_base_forecast_history_air where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and `date`<=@date_str order by `date` desc limit 1 ", new
                    {
                        warehouse_code = result.warehouse_code,
                        bailun_sku = result.bailun_sku,
                        date_str = this_time_end.ToString("yyyy-MM-dd")
                    }) ?? new dc_base_forecast_history() { };
                    modalData.quantity_final_advise_air = 0;
                    if (dc_base_forecast_history_air_data.turnover_sales > 0)
                    {
                        modalData.quantity_final_advise_air = (dc_base_forecast_history_air_data.turnover_sales + dc_base_forecast_history_air_data.quantity_safe_inventory);
                    }

                    datas.FirstOrDefault(s => s.name == "空运周转数（推荐空运）").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_air
                    });
                    modalData.quantity_final_advise_air_amount = modalData.quantity_final_advise_air * modalData.unit_price;
                    datas.FirstOrDefault(s => s.name == "空运周转金额").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_advise_air_amount
                    });
                    modalData.freight_price_air = modalData.quantity_final_advise_air * modalData.freight_unit_price_air;
                    datas.FirstOrDefault(s => s.name == "空运周转运费").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air
                    });
                    modalData.freight_price_ocean_difference_amount = modalData.freight_price - modalData.freight_price_ocean;
                    datas.FirstOrDefault(s => s.name == "实际-海运运费差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_difference_amount
                    });
                    modalData.quantity_final_ocean_difference_amount = modalData.quantity_final_advise_amount - modalData.quantity_final_advise_ocean_amount;
                    datas.FirstOrDefault(s => s.name == "实际-海运周转采购金额差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_ocean_difference_amount
                    });

                    datas.FirstOrDefault(s => s.name == "差值(海运)-实际周转金额占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = (modalData.turnover_amount) <= 0 ? 0M : (Math.Round(modalData.quantity_final_ocean_difference_amount / modalData.turnover_amount, 3))
                    });

                    modalData.freight_price_air_difference_amount = modalData.freight_price - modalData.freight_price_air;
                    datas.FirstOrDefault(s => s.name == "实际-空运运费差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_air_difference_amount
                    });
                    modalData.quantity_final_air_difference_amount = modalData.quantity_final_advise_amount - modalData.quantity_final_advise_air_amount;
                    datas.FirstOrDefault(s => s.name == "实际-空运周转采购金额差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final_air_difference_amount
                    });

                    datas.FirstOrDefault(s => s.name == "差值(空运)-实际周转金额占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = (modalData.turnover_amount) <= 0 ? 0M : (Math.Round(modalData.freight_price_air_difference_amount / modalData.turnover_amount, 3))
                    });

                    #endregion

                    modalData.freight_price_ocean_air_difference = modalData.freight_price_ocean - modalData.freight_price_air;
                    datas.FirstOrDefault(s => s.name == "海运-空运运费差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_air_difference,
                        formula = $"【海运-空运运费差值】= 【海运周转运费 {modalData.freight_price_ocean }】 - 【空运运周转运费 {modalData.freight_price_air}】"
                    });

                    modalData.quantity_final__ocean_air_difference_amount = modalData.quantity_final_advise_ocean_amount - modalData.quantity_final_advise_air_amount;
                    datas.FirstOrDefault(s => s.name == "海运-空运周转采购金额差值").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.quantity_final__ocean_air_difference_amount,
                        formula = $"【海运-空运周转采购金额差值】= 【海运周转采购金额 {modalData.quantity_final_advise_ocean_amount }】 - 【空运运周转采购金额 {modalData.quantity_final_advise_air_amount}】"
                    });

                    modalData.freight_price_ocean_air_difference_radio = (modalData.turnover_amount) <= 0 ? 0M : (Math.Round((modalData.quantity_final__ocean_air_difference_amount) / modalData.turnover_amount, 3));
                    datas.FirstOrDefault(s => s.name == "差值(海运-空运)-实际周转金额占比").days.Add(new transfer_profit_dto.day_dto
                    {
                        date = this_time,
                        val = modalData.freight_price_ocean_air_difference_radio,
                        formula = $"【差值(海运-空运)-实际周转金额占比】 = 【海运-空运周转金额差值 {modalData.quantity_final__ocean_air_difference_amount}】/ 【实际周转运费 {modalData.turnover_amount}】 "

                    });

                }




                index++;
                this_time = this_time.AddDays(-1);
            }

            result_data.datas = datas;
            return result_data;
        }

        /// <summary>
        /// 查询过去30日的销量
        /// </summary>
        /// <returns></returns>
        public static List<decimal> GetHistorySales30(int turnId, string bailun_sku, string warehouse_code)
        {
            var sales_details = new List<decimal>();
            var trun_sales = DB.SaleVolume.GetByTurnOverId(turnId, bailun_sku, warehouse_code);
            sales_details.Add(trun_sales.thirtyday_sales);
            sales_details.Add(trun_sales.twenty_nineday_sales);
            sales_details.Add(trun_sales.twenty_eightday_sales);
            sales_details.Add(trun_sales.twenty_seveneday_sales);
            sales_details.Add(trun_sales.twenty_sixday_sales);
            sales_details.Add(trun_sales.twenty_fiveday_sales);
            sales_details.Add(trun_sales.twenty_fourthday_sales);
            sales_details.Add(trun_sales.twenty_threeday_sales);
            sales_details.Add(trun_sales.twenty_twoday_sales);
            sales_details.Add(trun_sales.twenty_oneday_sales);
            sales_details.Add(trun_sales.twentyday_sales);
            sales_details.Add(trun_sales.nineteenday_sales);
            sales_details.Add(trun_sales.eighteenday_sales);
            sales_details.Add(trun_sales.seventeenday_sales);
            sales_details.Add(trun_sales.sixteenday_sales);
            sales_details.Add(trun_sales.fifteenday_sales);
            sales_details.Add(trun_sales.fourteenday_sales);
            sales_details.Add(trun_sales.thridteenday_sales);
            sales_details.Add(trun_sales.twelveday_sales);
            sales_details.Add(trun_sales.elevenday_sales);
            sales_details.Add(trun_sales.tenday_sales);
            sales_details.Add(trun_sales.nineday_sales);
            sales_details.Add(trun_sales.eightday_sales);
            sales_details.Add(trun_sales.sevenday_sales);
            sales_details.Add(trun_sales.sixday_sales);
            sales_details.Add(trun_sales.fiveday_sales);
            sales_details.Add(trun_sales.fourthday_sales);
            sales_details.Add(trun_sales.threeday_sales);
            sales_details.Add(trun_sales.twoday_sales);
            sales_details.Add(trun_sales.oneday_sales);
            return sales_details;
        }

        /// <summary>
        /// 计算利润分析数据
        /// </summary>
        public static void CalculationProfitAnalysis(string bailun_sku, string warehouse_code)
        {
            dc_base_transfer_freight_dao.CalculationSingle(bailun_sku, warehouse_code);

            var warehouse_data = _connection.QueryFirstOrDefault<dc_base_warehouse>(" select * from dc_base_warehouse where  warehouse_code=@warehouse_code ", new { warehouse_code = warehouse_code });
            if (warehouse_data.hq_type == "国内仓")
            {
                return;
            }


            // 查询利润数据
            var profit_dataData = TransferProfitList(bailun_sku, warehouse_code, DateTime.Now);
            if (profit_dataData == null)
            {
                return;
            }
            var profit_data = profit_dataData.today_data;
            if (profit_data == null)
            {
                return;
            }
            profit_data.ocean_purchase = Math.Max(0, profit_data.quantity_final_advise_ocean - profit_data.turnover);
            profit_data.ocean_purchase_amount = profit_data.ocean_purchase * profit_data.unit_price;
            profit_data.freight_stock_amount_ocean = profit_data.quantity_final_ocean_difference_amount <= 0 ? 0 : ((profit_data.freight_price_ocean_difference_amount) / (profit_data.quantity_final_ocean_difference_amount));
            profit_data.freight_stock_amount_air = profit_data.quantity_final_air_difference_amount <= 0 ? 0 : ((profit_data.freight_price_air_difference_amount) / (profit_data.quantity_final_air_difference_amount));
            profit_data.freight_stock_amount_ocean_30 = profit_data.turnover_amount <= 0 ? 0 : (Math.Abs(profit_data.freight_price_ocean_30_difference) / profit_data.turnover_amount);
            profit_data.freight_stock_amount_air_30 = profit_data.turnover_amount <= 0 ? 0 : (Math.Abs(profit_data.freight_price_air_30_difference) / profit_data.turnover_amount);
            profit_data.forecast_daily_weighted_sales = profit_data.turnover_days <= 0 ? 0 : Math.Round(profit_data.turnover_sales / profit_data.turnover_days);
            profit_data.forecast_daily_weighted_sales_ocean = profit_data.turnover_days_ocean <= 0 ? 0 : Math.Round(profit_data.turnover_sales_ocean / profit_data.turnover_days_ocean);
            profit_data.forecast_daily_weighted_sales_air = profit_data.turnover_days_air <= 0 ? 0 : Math.Round(profit_data.turnover_sales_air / profit_data.turnover_days_air);
            var formula_json = profit_data.ToJson();
            dc_report_profit_analysis data = formula_json.ToObj<dc_report_profit_analysis>();

            data.bailun_sku = bailun_sku;
            data.formula_json = formula_json;
            data.warehouse_code = warehouse_code;
            data.warehouse_type = warehouse_data.hq_type;
            data.country = warehouse_data.area_name;
            data.warehouse_name = warehouse_data.o_warehouse_name;
            // 查询是否有默认渠道
            var dc_base_head_transport_config_data = _connection.QuerySingleOrDefault<dc_base_head_transport_config>(" select * from dc_base_head_transport_config  where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and is_delete=0 ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code
            }) ?? new dc_base_head_transport_config { };
            data.transport_type = dc_base_head_transport_config_data.head_transport ?? "";
            dc_report_profit_analysis sales_data = _connection.QueryFirstOrDefault<dc_report_profit_analysis>(@" select 
sum( case when paid_time>=@btime2 and paid_time<=@etime1 then bailun_sku_quantity_ordered else 0 end ) as 'sales_avg_7', 
sum( case when paid_time>=@btime3 and paid_time<=@etime1 then bailun_sku_quantity_ordered else 0 end ) as 'sales_avg_30', 
sum( bailun_sku_quantity_ordered ) as 'sales_avg_90'
from dc_base_oms_sku_30 where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and paid_time>=@btime1 and paid_time<=@etime1  ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code,
                btime1 = DateTime.Now.AddDays(-91).ToDayHome(),
                btime2 = DateTime.Now.AddDays(-8).ToDayHome(),
                btime3 = DateTime.Now.AddDays(-30).ToDayHome(),
                etime1 = DateTime.Now.AddDays(-1).ToDayEnd(),
            }, commandTimeout: 0) ?? new dc_report_profit_analysis { };
            data.sales_avg_7 = sales_data.sales_avg_7 / 7;
            data.sales_avg_30 = sales_data.sales_avg_30 / 30;
            data.sales_avg_90 = sales_data.sales_avg_90 / 90;
            data.sales_avg_180 = (_connection.QueryFirstOrDefault<int?>(@" select 
sum( bailun_sku_quantity_ordered ) as 'sales_avg_180'
from dc_base_oms_sku where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and paid_time>=@btime1 and paid_time<=@etime1  ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code,
                btime1 = DateTime.Now.AddDays(-181).ToDayHome(),
                etime1 = DateTime.Now.AddDays(-1).ToDayEnd(),
            }, commandTimeout: 0) ?? 0) / 180;



            data.id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_profit_analysis where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code
            }) ?? 0;

            data.update_time = DateTime.Now;
            if (data.id > 0)
            {
                _connection.Update(data);
            }
            else
            {
                _connection.Insert(data);
            }
        }

        /// <summary>
        /// 刷新 利润分析数据
        /// </summary>
        public static void SynchroCalculationProfitAnalysis()
        {
            if (DateTime.Now.Hour < 8)
            {
                return;
            }
            var task_name = $"SynchroCalculationProfitAnalysis_v19";
            var conn = MyMySqlConnection._connection; ;
            // 查询最后一次成功抓取的记录
            var last_task_synchro_log = conn.QuerySingleOrDefault<task_synchro_log>(" select  * from task_synchro_log where task_name=@task_name and status=1 order by end_time desc limit 1 ", new { task_name = task_name });

            var new_task_synchro_log = new task_synchro_log
            {
                create_date = DateTime.Now,
                end_time = DateTime.Now.AddMinutes(-2),
                status = 0,
                task_name = task_name
            };

            if (last_task_synchro_log != null)
            {
                new_task_synchro_log.start_time = last_task_synchro_log.end_time.AddMinutes(-1);
            }
            else
            {
                new_task_synchro_log.start_time = DateTime.Now.AddYears(-1);
            }

            new_task_synchro_log.id = conn.Insert(new_task_synchro_log) ?? 0;
            int count = 0;
            int page = 1;
            int rows = 1000;

            var now = DateTime.Now;
            while (true)
            {
                var datas = conn.Query<sku_warehouse_code_dto>($@" select t1.bailun_sku,t1.warehouse_code from dc_auto_turnover as t1 left join dc_base_warehouse as t2 on t1.warehouse_code =t2.warehouse_code 
where t2.hq_type not in ('国内仓') and t1.gmt_modified>=@btime and t1.gmt_modified <=@etime and t1.daily_weighted_sales>1
order by t1.gmt_modified asc 
 limit {(page - 1) * rows},{rows}  ", new { btime = new_task_synchro_log.start_time, etime = new_task_synchro_log.end_time }, commandTimeout: 0).ToList();
                if (datas.Count <= 0) { break; }
                page++;
                foreach (var orderItem in datas)
                {
                    try
                    {
                        CalculationProfitAnalysis(orderItem.bailun_sku, orderItem.warehouse_code);
                        count++;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }


                }

            }


            new_task_synchro_log.count = count;
            new_task_synchro_log.status = 1;
            conn.Update(new_task_synchro_log);
        }

        /// <summary>
        /// 查询供应链调拨在途
        /// </summary>
        /// <param name="sku">sku</param>
        /// <param name="warehouse_code">仓库编码</param>
        /// <returns></returns>
        public static List<dc_report_profit_analysis_dto> ProfitAnalysis(Condition_AutoTurnOver m, int offset, int limit, ref int total, string order = "", string sort = "", bool isSum = false)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = "";
            if (false && m.isSum)
            {
                sql = @"

";

            }
            else
            {
                sql = @" select ta.*,t4.product_code,t4.weight,t4.sku_title_cn as 'product_name',t4.brand as 'brand',t5.`stock` as 'fba_storage_capacity',
t5.`stock` - (ifnull(t6.quantity_transfer_temp_schedule,0) + ifnull(t6.quantity_transfer_temporary_storage,0) + ifnull(t6.quantity_transfer_plan,0) + ifnull(t6.quantity_purchase,0) ) as 'storage_capacity'

from dc_report_profit_analysis as ta 
left join dc_auto_turnover as dat on dat.warehouse_code = ta.warehouse_code and dat.bailun_sku = ta.bailun_sku
left join dc_base_sku as t4 on dat.bailun_sku = t4.bailun_sku
left join dc_base_storage_capacity as t5 on ta.bailun_sku = t5.bailun_sku and ta.warehouse_code = t5.warehouse_code
left join dc_mid_transit as t6 on ta.bailun_sku = t6.bailun_sku and ta.warehouse_code = t6.warehouse_code
";
            }

            var sqlCount = @" 
 select
count(1)
from dc_report_profit_analysis as ta
left join dc_auto_turnover as dat on dat.warehouse_code = ta.warehouse_code and dat.bailun_sku = ta.bailun_sku
";

            if (string.IsNullOrWhiteSpace(m.sku_label))
            {
                sqlCount += " left join dc_auto_config_sku_label as t11 on dat.bailun_sku = t11.bailun_sku ";
                sql += " left join dc_auto_config_sku_label as t11 on dat.bailun_sku = t11.bailun_sku ";
            }
            if (m.warehousearea > 0)
            {
                sqlCount += " left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code ";
                sql += " left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code ";
            }
            if (m.categoryModels != null || (!string.IsNullOrWhiteSpace(m.product_type)))
            {
                sqlCount += " left join dc_base_sku as t4 on dat.bailun_sku = t4.bailun_sku ";
            }
            if (m.monitor_status != null)
            {
                sqlCount += " left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code ";
                sql += " left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code ";
            }
            if (m.returngoodspush_state != null)
            {
                sqlCount += " left join dc_return_goods_not_push as t12 on dat.bailun_sku = t12.bailun_sku and dat.warehouse_code = t12.warehouse_code ";
                sql += " left join dc_return_goods_not_push as t12 on dat.bailun_sku = t12.bailun_sku and dat.warehouse_code = t12.warehouse_code ";
            }
            if (m.has_tort != null)
            {
                sqlCount += " left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku ";
                sql += " left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku ";
            }
            if (m.oneday_sales_min > 0 || m.oneday_sales_max > 0)
            {
                sqlCount += " left join dc_auto_sales as t2 on t2.bailun_sku=dat.bailun_sku and dat.warehouse_code = t2.warehouse_code ";
                sql += " left join dc_auto_sales as t2 on t2.bailun_sku=dat.bailun_sku and dat.warehouse_code = t2.warehouse_code ";
            }
            if (m.not_trans_count_max > 0 || m.not_trans_count_min > 0)
            {
                sqlCount += " left join not_trans_view as t15 on dat.bailun_sku = t15.bailun_sku and dat.warehouse_code = t15.warehouse_code ";
                sql += " left join not_trans_view as t15 on dat.bailun_sku = t15.bailun_sku and dat.warehouse_code = t15.warehouse_code ";
            }
            sqlCount += " where 1=1 ";
            sql += " where 1=1 ";

            if (m.has_tort != null)
            {
                if (m.has_tort == 0)
                {
                    sql += " and t8.id is null ";
                    sqlCount += " and t8.id is null ";
                }
                else if (m.has_tort == 1)
                {
                    sql += " and t8.id is not null ";
                    sqlCount += " and t8.id is not null ";
                }
            }

            if (m.categoryModels != null)
            {
                sql += " and t4.category_simple_id in ( " + string.Join(",", m.categoryModels.Select(s => s.id).Distinct()) + ")";
                sqlCount += " and t4.category_simple_id in ( " + string.Join(",", m.categoryModels.Select(s => s.id).Distinct()) + ")";
            }
            if (!string.IsNullOrWhiteSpace(m.sku_label))
            {
                sql += " and t11.label = " + $"'{m.sku_label}'";
                sqlCount += " and t11.label = " + $"'{m.sku_label}'";
            }
            if (!string.IsNullOrWhiteSpace(m.buyer_name))
            {
                sql += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                sqlCount += " and dat.buyer_name = " + $"'{m.buyer_name}'";
            }
            if (!string.IsNullOrWhiteSpace(m.product_type))
            {
                sql += " and t4.product_type = " + $"'{m.product_type}'";
                sqlCount += " and t4.product_type = " + $"'{m.product_type}'";
            }
            if (m.monitor_status == 1)
            {
                sql += " and t5.`status`=1 ";
                sqlCount += " and t5.`status`=1 ";
            }
            if (m.monitor_status == 0)
            {
                sql += " and ( t5.`status`=0 or t5.`status` is null ) ";
                sqlCount += " and ( t5.`status`=0 or t5.`status` is null ) ";
            }

            if (m.returngoodspush_state == 1)
            {
                sql += " and t12.`status`=1 ";
                sqlCount += " and t12.`status`=1 ";
            }
            if (m.returngoodspush_state == 0)
            {
                sql += " and ( t12.`status`=0 or t12.`status` is null ) ";
                sqlCount += " and ( t12.`status`=0 or t12.`status` is null ) ";
            }

            if (m.hasAadvise == true)
            {
                sql += " and dat.quantity_final_advise>0 ";
                sqlCount += " and dat.quantity_final_advise>0 ";
            }
            if (m.hasAadvise == false)
            {
                sql += " and dat.quantity_final_advise<=0 ";
                sqlCount += " and dat.quantity_final_advise<=0 ";
            }
            if (!string.IsNullOrWhiteSpace(m.key_words) && m.searchType > 0)
            {
                switch (m.searchType.Value)
                {
                    case 1:
                        sql += " and dat.bailun_sku in @bailun_skus ";
                        sqlCount += " and dat.bailun_sku in @bailun_skus ";
                        parameters.Add("bailun_skus", m.key_words.Split(',').ToArray());
                        break;
                    case 2:
                        sql += " and dat.bailun_sku like " + $"'%{m.key_words}%'";
                        sqlCount += " and dat.bailun_sku =" + $"'%{m.key_words}%'";
                        break;
                    case 3:
                        sql += " and dat.product_inner_code in @product_inner_code ";
                        sqlCount += " and dat.product_inner_code in @product_inner_code ";
                        parameters.Add("product_inner_code", m.key_words.Split(',').ToList());
                        break;
                    case 4:
                        sql += " and dat.product_inner_code like " + $"'%{m.key_words}%'";
                        sqlCount += " and dat.product_inner_code like" + $"'%{m.key_words}%'";
                        break;
                    case 5:
                        sql += " and dat.product_code =" + $"'{m.key_words}'";
                        sqlCount += " and dat.product_code =" + $"'{m.key_words}'";
                        break;
                    case 6:
                        sql += " and dat.product_code like " + $"'%{m.key_words}%'";
                        sqlCount += " and dat.product_code like" + $"'%{m.key_words}%'";
                        break;
                    case 7:
                        sql += " and dat.sku_title like " + $"'%{m.key_words}%'";
                        sqlCount += " and dat.sku_title like" + $"'%{m.key_words}%'";
                        break;
                    default: break;
                }

            }

            if (m.hasDefectConfig == true)
            {
                sql += " and ( dat.abroad_inbound_delivery <=0  or dat.transfer_bale_delivery <=0 or dat.transfer_delivery <=0   )  ";
                sqlCount += " and ( dat.abroad_inbound_delivery <=0  or dat.transfer_bale_delivery <=0 or dat.transfer_delivery <=0   )  ";
            }
            if (m.hasDefectConfig == false)
            {
                sql += " and ( dat.abroad_inbound_delivery >0  and dat.transfer_bale_delivery >0 and dat.transfer_delivery >0   )  ";
                sqlCount += " and ( dat.abroad_inbound_delivery >0  and dat.transfer_bale_delivery >0 and dat.transfer_delivery >0   )  ";
            }
            if (!string.IsNullOrEmpty(m.warehouse_code))
            {
                sql += " and dat.warehouse_code='" + m.warehouse_code + "'";
                sqlCount += " and dat.warehouse_code='" + m.warehouse_code + "'";
            }
            else
            {
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and dat.hq_type=" + $"'{m.warehousetype}'";
                    sqlCount += " and dat.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t3.area_id=" + m.warehousearea;
                    sqlCount += " and t3.area_id=" + m.warehousearea;
                }
            }

            if (m.out_of_stock.HasValue)
            {
                sql += " and dat.status=1";
                sqlCount += " and dat.status=1";
            }
            if (!string.IsNullOrWhiteSpace(m.supplier_name))
            {
                sql += " and dat.suppliers_name = '" + m.supplier_name + "'";
                sqlCount += " and dat.suppliers_name = '" + m.supplier_name + "'";
            }

            if (m.quantity_inventory_min != null)
            {
                sql += " and dat.quantity_inventory>=@quantity_inventory_min ";
                sqlCount += " and dat.quantity_inventory>=@quantity_inventory_min ";
                parameters.Add("quantity_inventory_min", m.quantity_inventory_min);
            }
            if (m.quantity_inventory_max != null)
            {
                sql += " and dat.quantity_inventory<=@quantity_inventory_max ";
                sqlCount += " and dat.quantity_inventory<=@quantity_inventory_max ";
                parameters.Add("quantity_inventory_max", m.quantity_inventory_max);
            }
            if (m.not_trans_count_min != null)
            {
                sql += " and t15.count>=@not_trans_count_min ";
                sqlCount += " and t15.count>=@not_trans_count_min ";
                parameters.Add("not_trans_count_min", m.not_trans_count_min);
            }

            if (m.quantity_out_stock_max != null)
            {
                sql += " and dat.quantity_out_stock>=@quantity_out_stock_max ";
                sqlCount += " and dat.quantity_out_stock>=@quantity_out_stock_max ";
                parameters.Add("quantity_out_stock_max", m.quantity_out_stock_max);
            }
            if (m.oneday_sales_min != null)
            {
                sql += " and t2.oneday_sales>=@oneday_sales_min ";
                sqlCount += " and t2.oneday_sales>=@oneday_sales_min ";
                parameters.Add("oneday_sales_min", m.oneday_sales_min);
            }
            if (m.oneday_sales_max != null)
            {
                sql += " and t2.oneday_sales<=@oneday_sales_max ";
                sqlCount += " and t2.oneday_sales<=@oneday_sales_max ";
                parameters.Add("oneday_sales_max", m.oneday_sales_max);
            }
            if (m.avg_type > 0)
            {
                var field_str = "";
                switch (m.avg_type)
                {
                    case 1: field_str = "dat.history_sevenday_sales"; break;
                    case 2: field_str = "dat.history_fourteenday_sales"; break;
                    case 3: field_str = "dat.history_thirtyday_sales"; break;
                    case 4: field_str = "dat.forecast_sevenday_sales"; break;
                    case 5: field_str = "dat.forecast_fourteenday_sales"; break;
                    case 7: field_str = "dat.history_sevenday_sales_ebay"; break;
                    case 8: field_str = "dat.history_fourteenday_sales_ebay"; break;
                    case 9: field_str = "dat.history_thirtyday_sales_ebay"; break;
                    case 10: field_str = "dat.history_sevenday_sales_aliexpress"; break;
                    case 11: field_str = "dat.history_fourteenday_sales_aliexpress"; break;
                    case 12: field_str = "dat.history_thirtyday_sales_aliexpress"; break;
                    case 13: field_str = "dat.history_sevenday_sales_amazon"; break;
                    case 14: field_str = "dat.history_fourteenday_sales_amazon"; break;
                    case 15: field_str = "dat.history_thirtyday_sales_amazon"; break;

                    default: break;
                }
                if (!string.IsNullOrWhiteSpace(field_str))
                {
                    if (m.avg_sales_min != null)
                    {
                        sql += $" and {field_str}>=@avg_sales_min ";
                        sqlCount += $" and {field_str}>=@avg_sales_min ";
                        parameters.Add("avg_sales_min", m.avg_sales_min);
                    }
                    if (m.avg_sales_max != null)
                    {
                        sql += $" and {field_str}<=@avg_sales_max ";
                        sqlCount += $" and {field_str}<=@avg_sales_max ";
                        parameters.Add("avg_sales_max", m.avg_sales_max);
                    }
                }

            }

            if (limit > 1000 || m.isSum)
            {
                total = 0;
            }
            else
            {
                total = _connection.QueryFirstOrDefault<int>(sqlCount, parameters, commandTimeout: 0);
            }



            if (string.IsNullOrWhiteSpace(sort))
            {
                sort = " ta.`ocean_purchase_amount` ";
                order = "desc";
            }
            if (!string.IsNullOrEmpty(sort) && !string.IsNullOrEmpty(order))
            {
                sql += " order by " + sort;

                if (!string.IsNullOrEmpty(order))
                {
                    sql += " " + order;
                }
                else
                {
                    sql += " asc";
                }
            }
            sql += " limit " + offset + "," + limit;
            var obj = _connection.Query<dc_report_profit_analysis_dto>(sql, parameters, buffered: false, commandTimeout: 0).AsList();
            if (m.isSum)
            {
                return new List<dc_report_profit_analysis_dto>() {
                new dc_report_profit_analysis_dto {
                sales_avg_180  =obj.Sum(s=>s.sales_avg_180),
                sales_avg_90  =obj.Sum(s=>s.sales_avg_90),
                sales_avg_30  =obj.Sum(s=>s.sales_avg_30),
                sales_avg_7  =obj.Sum(s=>s.sales_avg_7),
                quantity_final_advise  =obj.Sum(s=>s.quantity_final_advise),
                turnover_amount  =obj.Sum(s=>s.turnover_amount),
                quantity_final_advise_amount  =obj.Sum(s=>s.quantity_final_advise_amount),
                turnover  =obj.Sum(s=>s.turnover),
                freight_price  =obj.Sum(s=>s.freight_price),
                quantity_final_advise_ocean  =obj.Sum(s=>s.quantity_final_advise_ocean),
                quantity_final_advise_ocean_amount  =obj.Sum(s=>s.quantity_final_advise_ocean_amount),
                freight_price_ocean  =obj.Sum(s=>s.freight_price_ocean),
                quantity_final_advise_air  =obj.Sum(s=>s.quantity_final_advise_air),
                quantity_final_advise_air_amount  =obj.Sum(s=>s.quantity_final_advise_air_amount),
                freight_price_air  =obj.Sum(s=>s.freight_price_air),
                freight_price_ocean_difference_amount  =obj.Sum(s=>s.freight_price_ocean_difference_amount),
                quantity_safe_inventory  =obj.Sum(s=>s.quantity_safe_inventory),
                ocean_purchase  =obj.Sum(s=>s.ocean_purchase),
                ocean_purchase_amount  =obj.Sum(s=>s.ocean_purchase_amount),
                quantity_final_ocean_difference_amount  =obj.Sum(s=>s.quantity_final_ocean_difference_amount),
                freight_price_ocean_30_difference  =obj.Sum(s=>s.freight_price_ocean_30_difference),
                freight_price_air_30_difference  =obj.Sum(s=>s.freight_price_air_30_difference),
                freight_price_air_ocean_30_difference  =obj.Sum(s=>s.freight_price_air_ocean_30_difference),
                }
                };
            }
            else
            {
                return obj;
            }

        }


        /// <summary>
        /// 生成采购建议
        /// </summary>
        public static void BatchPushPurchaseAdvise(string ids, UserData user)
        {
            var idInts = ids.Split(',').Select(s => int.Parse(s)).ToList();
            var anas = _connection.Query<dc_report_profit_analysis>(" select * from dc_report_profit_analysis where id in @ids ", new
            {
                ids = idInts
            }).ToList();
            if (anas != null && anas.Count >= 1)
            {
                var mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = DateTime.Now, no = purchase_advise.GenerateOrderNo(), buy_sys_plan_no = "补海运", title = $" {user.UserName} 补海运 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;

                var datas = new List<dc_auto_purchase_advise_detailed>();
                foreach (var item in anas)
                {
                    if (item.ocean_purchase > 0)
                    {
                        var item_data = new dc_auto_purchase_advise_detailed
                        {
                            main_id = mainID,
                            auto_replace_logistics_task_id = null,
                            bailun_order_ids = "",
                            bailun_sku = item.bailun_sku,
                            bailun_sku_warehouse_code = $"{item.bailun_sku}{item.warehouse_code}",
                            warehouse_code = item.warehouse_code,
                            create_time = DateTime.Now,
                            push_date = null,
                            fit_forecast_formula = "",
                            fixed_daily_weighted_sales = null,
                            fixed_dc_auto_sales_forecast_title = "",
                            quantity_init_advise = item.ocean_purchase,
                            quantity_actual = item.ocean_purchase,
                            turnover_days = (int)Math.Ceiling(item.turnover_days_ocean),
                            supplier_delivery = 0,
                            inspection_delivery = 0,
                            type = (int)purchase_type_jit_enum.补海运,
                            product_inner_code = "",
                            goods_quantity_init_advise = item.ocean_purchase,
                            good_sku_codes = item.bailun_sku,
                            purchase_type_jit = (int)purchase_advise_type_enum.补海运
                        };
                        datas.Add(item_data);
                        _connection.Insert(item_data);
                    }

                }
            }
        }

        /// <summary>
        /// 刷新 每日配置销量
        /// </summary>
        public static void Synchrofull_stock_use_days_turnover_sales()
        {
            if (DateTime.Now.Hour < 8)
            {
                return;
            }
            var task_name = $"Synchrofull_stock_use_days_turnover_sales_v2";
            var conn = MyMySqlConnection._connection; ;
            // 查询最后一次成功抓取的记录
            var last_task_synchro_log = conn.QuerySingleOrDefault<task_synchro_log>(" select  * from task_synchro_log where task_name=@task_name and status=1 order by end_time desc limit 1 ", new { task_name = task_name });

            var new_task_synchro_log = new task_synchro_log
            {
                create_date = DateTime.Now,
                end_time = DateTime.Now.AddMinutes(-2),
                status = 0,
                task_name = task_name
            };

            if (last_task_synchro_log != null)
            {
                new_task_synchro_log.start_time = last_task_synchro_log.end_time.AddMinutes(-1);
            }
            else
            {
                new_task_synchro_log.start_time = DateTime.Now.AddYears(-1);
            }

            new_task_synchro_log.id = conn.Insert(new_task_synchro_log) ?? 0;
            int count = 0;
            int page = 1;
            int rows = 1000;

            var now = DateTime.Now;
            while (true)
            {
                var datas = conn.Query<Models.dc_auto_turnover>($@" select t1.* from dc_auto_turnover as t1 
where  t1.gmt_modified>=@btime and t1.gmt_modified <=@etime 
order by t1.gmt_modified asc 
 limit {(page - 1) * rows},{rows}  ", new { btime = new_task_synchro_log.start_time, etime = new_task_synchro_log.end_time }, commandTimeout: 0).ToList();
                if (datas.Count <= 0) { break; }
                page++;
                foreach (var orderItem in datas)
                {
                    try
                    {
                        set_full_stock_use_days_turnover_sales(orderItem);
                        count++;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }


                }

            }


            new_task_synchro_log.count = count;
            new_task_synchro_log.status = 1;
            conn.Update(new_task_synchro_log);
        }

        /// <summary>
        /// 设置 在途加在库可用天数的总销量
        /// </summary>
        public static void set_full_stock_use_days_turnover_sales(Models.dc_auto_turnover data)
        {
            var sales_day_configs = _connection.Query<sales_day_config>(" select * from sales_day_config where bailun_sku_warehouse_code =@bailun_sku_warehouse_code ", new
            {
                bailun_sku_warehouse_code = data.bailun_sku_warehouse_code
            }).ToList();
            var sales_day_config_details_list = new List<decimal>();
            var sales_details_list = data.sales_details.Replace("[","").Replace("]","").Split(',').Select(s=>decimal.Parse(s)).ToList();
            var thisDate = DateTime.Now.ToDayHome();
            var thisEnd = thisDate.AddDays(data.turnover_days).ToDayEnd();
            var index = 7;
            while (thisDate <= thisEnd)
            {
                var date_str = thisDate.ToString("yyyy-MM-dd");
                sales_day_config_details_list.Add((sales_day_configs.FirstOrDefault(s => s.date_str == date_str) ?? new sales_day_config()
                {
                    //sales = sales_details_list.Count - 1 >= index ? sales_details_list[index] : 0
                    sales = 0
                }).sales);
                thisDate = thisDate.AddDays(1);
                index++;
            }

            data.sales_day_config_details = sales_day_config_details_list.ToJson();
            _connection.Execute("  update dc_auto_turnover set sales_day_config_details=@sales_day_config_details where id=@id ", new
            {
                id = data.id,
                sales_day_config_details = data.sales_day_config_details
            });
        }
    }

}