using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoTurnOver.Models;
using AutoTurnOver.Models.ApiDto;
using AutoTurnOver.Models.Report;
using Dapper;
using AutoTurnOver.Utility;
using AutoTurnOver.DB.Base;
using AutoTurnOver.Models.Base;
using MySql.Data.MySqlClient;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 现金流计算
    /// </summary>
    public class report_cash_flow_dao : connectionHelper
    {
        /// <summary>
        /// 计算订单 销售字段
        /// </summary>
        public static void CalculationOrder(DateTime btime, DateTime? etime)
        {
            try
            {
                var conn = _connection;
                conn.Open();
                int page = 1;
                int rows = 10000;
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                while (true)
                {
                    var now = DateTime.Now;
                    var sql = " select * from dc_base_oms_sku where gmt_create>=@btime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    if (etime != null)
                    {
                        sql += " and gmt_create<=@etime ";
                        parameters.Add("etime", etime.Value.ToString("yyyy-MM-dd 23:59:59"));
                    }
                    sql += $" order by id desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_oms_sku>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;

                    var addList = new List<dc_report_cash_flow_log>();
                    var updateList = new List<dc_report_cash_flow_log>();

                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            var item_no = order_item.bailun_order_id;

                            #region 销售释放成本

                            dc_report_cash_flow_log item5 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.释放销售成本,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = order_item.cost_product * order_item.bailun_sku_quantity_ordered,
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后收,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item5.occur_time_year_month_no = $"{item5.occur_time.Year}-{item5.occur_time.Month}-{item5.data_type}-{item5.platform_type}-{item5.web_site}";
                            item5.pay_time = CalculationPayTime(configs, item5.occur_time, item5.data_type, item5.platform_type,item5.web_site);
                            item5.pay_time_year_month_no = $"{item5.pay_time.Year}-{item5.pay_time.Month}-{item5.data_type}-{item5.platform_type}-{item5.web_site}";
                            item5.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item5.data_type,
                                bailun_sku = item5.bailun_sku,
                                item_no = item5.item_no
                            }) ?? 0;
                            if (item5.id > 0)
                            {
                                updateList.Add(item5);
                                //conn.Update(item5);
                            }
                            else
                            {
                                addList.Add(item5);
                                //conn.Insert(item5);
                            }

                            #endregion

                            #region PayPal费

                            dc_report_cash_flow_log item8 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.PayPal费,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = 0 - Math.Abs(order_item.cost_paypal_fee * order_item.seller_order_exchange_rate * order_item.bailun_sku_quantity_ordered),
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item8.occur_time_year_month_no = $"{item8.occur_time.Year}-{item8.occur_time.Month}-{item8.data_type}-{item8.platform_type}-{item8.web_site}";
                            item8.pay_time = CalculationPayTime(configs, item8.occur_time, item8.data_type, item8.platform_type, item8.web_site);
                            item8.pay_time_year_month_no = $"{item8.pay_time.Year}-{item8.pay_time.Month}-{item8.data_type}-{item8.platform_type}-{item8.web_site}";
                            item8.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item8.data_type,
                                bailun_sku = item8.bailun_sku,
                                item_no = item8.item_no
                            }) ?? 0;
                            if (item8.id > 0)
                            {
                                updateList.Add(item8);
                                //conn.Update(item8);
                            }
                            else
                            {
                                addList.Add(item8);
                                //conn.Insert(item8);
                            }

                            #endregion

                            #region fba 费

                            dc_report_cash_flow_log item7 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.FBA费,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = 0 - Math.Abs(order_item.cost_fba_fee * order_item.seller_order_exchange_rate * order_item.bailun_sku_quantity_ordered),
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item7.occur_time_year_month_no = $"{item7.occur_time.Year}-{item7.occur_time.Month}-{item7.data_type}-{item7.platform_type}-{item7.web_site}";
                            item7.pay_time = CalculationPayTime(configs, item7.occur_time, item7.data_type, item7.platform_type,item7.web_site);
                            item7.pay_time_year_month_no = $"{item7.pay_time.Year}-{item7.pay_time.Month}-{item7.data_type}-{item7.platform_type}-{item7.web_site}";
                            item7.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item7.data_type,
                                bailun_sku = item7.bailun_sku,
                                item_no = item7.item_no
                            }) ?? 0;
                            if (item7.id > 0)
                            {
                                updateList.Add(item7);
                                //conn.Update(item7);
                            }
                            else
                            {
                                addList.Add(item7);
                                //conn.Insert(item7);
                            }

                            #endregion

                            #region 释放头程费用

                            dc_report_cash_flow_log item6 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.释放头程费用,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = order_item.cost_first * order_item.bailun_sku_quantity_ordered,
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后收,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item6.occur_time_year_month_no = $"{item6.occur_time.Year}-{item6.occur_time.Month}-{item6.data_type}-{item6.platform_type}-{item6.web_site}";
                            item6.pay_time = CalculationPayTime(configs, item6.occur_time, item6.data_type, item6.platform_type,item6.web_site);
                            item6.pay_time_year_month_no = $"{item6.pay_time.Year}-{item6.pay_time.Month}-{item6.data_type}-{item6.platform_type}-{item6.web_site}";
                            item6.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item6.data_type,
                                bailun_sku = item6.bailun_sku,
                                item_no = item6.item_no
                            }) ?? 0;
                            if (item6.id > 0)
                            {
                                updateList.Add(item6);
                                //conn.Update(item6);
                            }
                            else
                            {
                                addList.Add(item6);
                                //conn.Insert(item6);
                            }

                            #endregion

                            #region 平台费

                            dc_report_cash_flow_log item3 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.平台费用,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = 0 - Math.Abs(order_item.cost_platform_fee * order_item.seller_order_exchange_rate * order_item.bailun_sku_quantity_ordered),
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item3.occur_time_year_month_no = $"{item3.occur_time.Year}-{item3.occur_time.Month}-{item3.data_type}-{item3.platform_type}-{item3.web_site}";
                            item3.pay_time = CalculationPayTime(configs, item3.occur_time, item3.data_type, item3.platform_type,item3.web_site);
                            item3.pay_time_year_month_no = $"{item3.pay_time.Year}-{item3.pay_time.Month}-{item3.data_type}-{item3.platform_type}-{item3.web_site}";
                            item3.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item3.data_type,
                                bailun_sku = item3.bailun_sku,
                                item_no = item3.item_no
                            }) ?? 0;
                            if (item3.id > 0)
                            {
                                updateList.Add(item3);
                                //conn.Update(item3);
                            }
                            else
                            {
                                addList.Add(item3);
                                //conn.Insert(item3);
                            }

                            #endregion

                            #region 利润

                            dc_report_cash_flow_log item2 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.利润,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = order_item.profit_total * order_item.bailun_sku_quantity_ordered,
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item2.occur_time_year_month_no = $"{item2.occur_time.Year}-{item2.occur_time.Month}-{item2.data_type}-{item2.platform_type}-{item2.web_site}";
                            item2.pay_time = CalculationPayTime(configs, item2.occur_time, item2.data_type, item2.platform_type,item2.web_site);
                            item2.pay_time_year_month_no = $"{item2.pay_time.Year}-{item2.pay_time.Month}-{item2.data_type}-{item2.platform_type}-{item2.web_site}";
                            item2.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item2.data_type,
                                bailun_sku = item2.bailun_sku,
                                item_no = item2.item_no
                            }) ?? 0;
                            if (item2.id > 0)
                            {
                                updateList.Add(item2);
                                //conn.Update(item2);
                            }
                            else
                            {
                                addList.Add(item2);
                                //conn.Insert(item2);
                            }

                            #endregion

                            #region 销售数量

                            dc_report_cash_flow_log item1 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.销售数量,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = order_item.bailun_sku_quantity_ordered,
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item1.occur_time_year_month_no = $"{item1.occur_time.Year}-{item1.occur_time.Month}-{item1.data_type}-{item1.platform_type}-{item1.web_site}";
                            item1.pay_time = CalculationPayTime(configs, item1.occur_time, item1.data_type, item1.platform_type,item1.web_site);
                            item1.pay_time_year_month_no = $"{item1.pay_time.Year}-{item1.pay_time.Month}-{item1.data_type}-{item1.platform_type}-{item1.web_site}";
                            item1.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item1.data_type,
                                bailun_sku = item1.bailun_sku,
                                item_no = item1.item_no
                            }) ?? 0;
                            if (item1.id > 0)
                            {
                                updateList.Add(item1);
                                //conn.Update(item1);
                            }
                            else
                            {
                                addList.Add(item1);
                                //conn.Insert(item1);
                            }

                            #endregion

                            #region 销售金额

                            dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.销售金额,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = order_item.amount_sales * order_item.seller_order_exchange_rate * order_item.bailun_sku_quantity_ordered,
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };
                            item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                            item.pay_time = CalculationPayTime(configs, item.occur_time, item.data_type, item.platform_type,item.web_site);
                            item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                            item.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item.data_type,
                                bailun_sku = item.bailun_sku,
                                item_no = item.item_no
                            }) ?? 0;
                            if (item.id > 0)
                            {
                                updateList.Add(item);
                                //conn.Update(item);
                            }
                            else
                            {
                                addList.Add(item);
                                //conn.Insert(item);
                            }

                            #endregion
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"现金流-订单-{order_item.bailun_order_id}-异常" + ex.Message);
                            Console.WriteLine("现金流-订单-异常" + ex.StackTrace);
                        }

                    }

                    Console.WriteLine($" 开始写入第 {page} 页数据 ， 预计 写入 {addList.Count} 条 ， 更新 {updateList.Count} 条, 最大id {updateList.Max(s=>s.item_no)}, 最小id {updateList.Min(s => s.item_no)} ");
                    if (addList != null && addList.Count >= 1)
                    {
                        BatchInsertDatas(conn,addList);
                    }
                    if (updateList != null && updateList.Count >= 1)
                    {
                        UpdateUsersTest(conn, updateList);
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-订单-异常" + ex.Message);
                Console.WriteLine("现金流-订单-异常" + ex.StackTrace);
            }


        }

        public static void UpdateUsersTest(MySqlConnection conn, List<dc_report_cash_flow_log> datas)
        {
            var sql = @"UPDATE dc_report_cash_flow_log SET web_site=@web_site,warehouse_code=@warehouse_code,platform_type=@platform_type,bailun_sku=@bailun_sku,data_type=@data_type,pay_type=@pay_type,val=@val,occur_time=@occur_time,pay_time=@pay_time,no=@no,item=@item,item_no=@item_no,remarks=@remarks,is_delete=@is_delete,update_time=@update_time,occur_time_year_month_no=@occur_time_year_month_no,pay_time_year_month_no=@pay_time_year_month_no WHERE id = @id ;";
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时
            var res = conn.Execute(sql, datas);
            Console.WriteLine($" 更新 {datas.Count} 条数据，耗时 {watch.ElapsedMilliseconds} 毫秒 ");
        }

        public static void BatchInsertDatas(MySqlConnection conn, List<dc_report_cash_flow_log> datas)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时
            StringBuilder sqlSb =  new StringBuilder(@" insert into dc_report_cash_flow_log(`web_site`,`warehouse_code`,`platform_type`,`bailun_sku`,`data_type`,`pay_type`,`val`,`occur_time`,`pay_time`,`no`,`item`,`item_no`,`remarks`,`is_delete`,`update_time`,`occur_time_year_month_no`,`pay_time_year_month_no`)  value ");
            foreach (var item in datas)
            {
                sqlSb.Append($"('{item.web_site}','{item.warehouse_code}','{item.platform_type}','{item.bailun_sku}',{item.data_type},{item.pay_type},{item.val},'{item.occur_time.ToString("yyyy-MM-dd HH:mm:ss")}','{item.pay_time.ToString("yyyy-MM-dd HH:mm:ss")}','{item.no}','{item.item}','{item.item_no}','{item.remarks}',{(item.is_delete)},'{item.update_time.ToString("yyyy-MM-dd HH:mm:ss")}','{item.occur_time_year_month_no}','{item.pay_time_year_month_no}' ),");
            }
            var sql = sqlSb.ToString();
            sql = sql.Substring(0, sql.Length-1);
            var res = conn.Execute(sql.ToString());
            Console.WriteLine($" 新增 {datas.Count} 条数据，耗时 {watch.ElapsedMilliseconds} 毫秒 ");
        }

        /// <summary>
        /// 计算订单尾程费
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationOrderCostLogistics(DateTime btime, DateTime? etime)
        {
            try
            {
                var conn = _connection;
                conn.Open();
                int page = 1;
                int rows = 5000;
                var logisticsList = ApiUtility.RealTimeShipLogisticsList();
                List<dc_report_logistics_company_config_dto> logistics_company_list = conn.Query<dc_report_logistics_company_config_dto>(" select * from dc_report_logistics_company_config ").AsList();
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                while (true)
                {
                    var now = DateTime.Now;
                    var sql = " select * from dc_base_oms_sku where gmt_create>=@btime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    if (etime != null)
                    {
                        sql += " and gmt_create<=@etime ";
                        parameters.Add("etime", etime.Value.ToString("yyyy-MM-dd 23:59:59"));
                    }
                    sql += $" order by id desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_oms_sku>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;

                    var addList = new List<dc_report_cash_flow_log>();
                    var updateList = new List<dc_report_cash_flow_log>();
                    foreach (var order_item in order_list)
                    {
                        try
                        {

                            #region 尾程费

                            dc_report_cash_flow_log item4 = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.尾程费用,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = 0 - Math.Abs(order_item.cost_logistics * order_item.bailun_sku_quantity_ordered),
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                item_no = order_item.bailun_order_id,
                                web_site = order_item.website
                            };

                            var pick_data_logistics_order_id = conn.QueryFirstOrDefault<string>(" select logistics_order_id from dc_base_oms_pick where bailun_order_id=@bailun_order_id and bailun_sku=@bailun_sku limit 1 ", new
                            {
                                bailun_order_id = order_item.bailun_order_id,
                                bailun_sku = order_item.bailun_sku
                            });
                            var remarks = "";
                            item4.occur_time_year_month_no = $"{item4.occur_time.Year}-{item4.occur_time.Month}-{item4.data_type}-{item4.platform_type}-{item4.web_site}";
                            item4.pay_time = CalculationLogisticsPayTime(logisticsList, logistics_company_list, item4.occur_time, pick_data_logistics_order_id, out remarks);
                            item4.pay_time_year_month_no = $"{item4.pay_time.Year}-{item4.pay_time.Month}-{item4.data_type}-{item4.platform_type}-{item4.web_site}";
                            item4.remarks = remarks;
                            item4.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item4.data_type,
                                bailun_sku = item4.bailun_sku,
                                item_no = item4.item_no
                            }) ?? 0;
                            if (item4.id > 0)
                            {
                                updateList.Add(item4);
                                //conn.Update(item4);
                            }
                            else
                            {
                                addList.Add(item4);
                                //conn.Insert(item4);
                            }

                            #endregion
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"现金流-订单-{order_item.bailun_order_id}-异常" + ex.Message);
                            Console.WriteLine("现金流-订单-异常" + ex.StackTrace);
                        }

                    }

                    if (addList != null && addList.Count >= 1)
                    {
                        BatchInsertDatas(conn, addList);
                    }
                    if (updateList != null && updateList.Count >= 1)
                    {
                        UpdateUsersTest(conn, updateList);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-订单-异常" + ex.Message);
                Console.WriteLine("现金流-订单-异常" + ex.StackTrace);
            }


        }

        /// <summary>
        /// 计算采购订单
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationPurchaseOrder(DateTime btime, DateTime etime, string purchase_id = null, string bailun_sku = null)
       {
            try
            {
                var conn = _connection;
                int page = 1;
                int rows = 1000;
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                List<dc_auto_config_safe_inventory> inventoryConfigs = conn.Query<dc_auto_config_safe_inventory>(" select * from dc_auto_config_safe_inventory ").AsList();
                while (true)
                {
                    var now = DateTime.Now;
                    var sql = " select t1.*,t2.pay_time from dc_base_purchase_details as t1 left join dc_base_purchase as t2 on t1.purchase_id = t2.purchase_id and t1.bailun_sku = t2.bailun_sku where t1.gmt_create>=@btime and t1.gmt_create<=@etime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", etime.ToString("yyyy-MM-dd 23:59:59"));
                    if (!string.IsNullOrWhiteSpace(purchase_id))
                    {
                        sql += " and t1.purchase_id=@purchase_id ";
                        parameters.Add("purchase_id", purchase_id);
                    }if (!string.IsNullOrWhiteSpace(bailun_sku))
                    {
                        sql += " and t1.bailun_sku=@bailun_sku ";
                        parameters.Add("bailun_sku", bailun_sku);
                    }
                    sql += $" limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_purchase_details_dto>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            var occur_time = order_item.create_time ?? new DateTime(1991, 1, 1);
                            var warehouse_code = order_item.isallot == 1 ? order_item.warehouse_into_code : order_item.warehouse_from_code;
                            var platforms =  GetPlatformShare(occur_time, conn, inventoryConfigs, warehouse_code,order_item.bailun_sku);
                            if(platforms!=null && platforms.Count >= 1)
                            {
                                // 把原本未挂载平台的数据删除
                                conn.Execute(" delete from dc_report_cash_flow_log where  data_type=@data_type and bailun_sku=@bailun_sku and  `no`=@purchase_id and platform_type='' ", new {
                                    bailun_sku = order_item.bailun_sku,
                                    purchase_id = order_item.purchase_id,
                                    data_type = (int)dc_report_cash_flow_log_data_type_enum.新增采购费用
                                });

                                foreach (var platformItem in platforms)
                                {
                                    #region 采购支出

                                    dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                                    {
                                        bailun_sku = order_item.bailun_sku,
                                        data_type = (int)dc_report_cash_flow_log_data_type_enum.新增采购费用,
                                        is_delete = (order_item.status == -1) ? 1 : 0,
                                        item = order_item.purchase_id,
                                        update_time = now,
                                        no = order_item.purchase_id,
                                        platform_type = platformItem.platform_type,
                                        web_site = platformItem.web_stie,
                                        remarks = $" 原采购费用 {order_item.amount_paid} ， 该平台-站点 日均加权值 {platformItem.sales_weight} 占比 {platformItem.ratio} ",
                                        val = 0 - (Math.Abs(order_item.amount_paid) * platformItem.ratio),
                                        occur_time = occur_time,
                                        warehouse_code = warehouse_code,
                                        pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                        item_no = $"{order_item.purchase_id}-{platformItem.platform_type}-{platformItem.web_stie}" 
                                    };
                                    if (order_item.pay_type == 3) // 无需付款
                                    {
                                        item.pay_time = order_item.create_time ?? new DateTime(1991, 1, 1);
                                    }
                                    else
                                    {
                                        item.pay_time = order_item.pay_time ?? new DateTime(1991, 1, 1);
                                    }
                                    item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                    item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                    item.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                                    {
                                        data_type = item.data_type,
                                        bailun_sku = item.bailun_sku,
                                        item_no = item.item_no
                                    }) ?? 0;
                                    if (item.id > 0)
                                    {
                                        conn.Update(item);
                                    }
                                    else
                                    {
                                        conn.Insert(item);
                                    }

                                    #endregion
                                }
                            }
                            else
                            {
                                #region 采购支出

                                dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                                {
                                    bailun_sku = order_item.bailun_sku,
                                    data_type = (int)dc_report_cash_flow_log_data_type_enum.新增采购费用,
                                    is_delete = (order_item.status == -1) ? 1 : 0,
                                    item = order_item.purchase_id,
                                    update_time = now,
                                    no = order_item.purchase_id,
                                    platform_type = "",
                                    web_site = "",
                                    remarks = $" 最新30天无订单，暂时不挂载平台 ",
                                    val = 0 - (Math.Abs(order_item.amount_paid) ),
                                    occur_time = order_item.create_time ?? new DateTime(1991, 1, 1),
                                    warehouse_code = warehouse_code,
                                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                    item_no = $"{order_item.purchase_id}"
                                };
                                if (order_item.pay_type == 3) // 无需付款
                                {
                                    item.pay_time = order_item.create_time ?? new DateTime(1991, 1, 1);
                                }
                                else
                                {
                                    item.pay_time = order_item.pay_time ?? new DateTime(1991, 1, 1);
                                }
                                item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                item.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                                {
                                    data_type = item.data_type,
                                    bailun_sku = item.bailun_sku,
                                    item_no = item.item_no
                                }) ?? 0;
                                if (item.id > 0)
                                {
                                    conn.Update(item);
                                }
                                else
                                {
                                    conn.Insert(item);
                                }

                                #endregion
                            }

                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"现金流-采购单-{order_item.purchase_id}-{order_item.bailun_sku}-异常" + ex.Message);
                            Console.WriteLine("现金流-采购单-异常" + ex.StackTrace);
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-采购单-异常" + ex.Message);
                Console.WriteLine("现金流-采购单-异常" + ex.StackTrace);
            }

        }

        /// <summary>
        /// 计算退款数据
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationRefundOrder(DateTime btime, DateTime etime)
        {
            try
            {
                var conn = _connection;
                int page = 1;
                int rows = 1000;
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                while (true)
                {
                    var now = DateTime.Now;
                    var sql = @" select t1.*,(case when t1.platform_type='ebay' then t3.warehouse_code else  t2.warehouse_code end) as 'warehouse_code' from dc_base_crm_refund as t1 
left join dc_base_oms_sku as t2 on t1.origin_order_id = t2.origin_order_id and t1.bailun_sku = t2.bailun_sku
left join dc_base_oms_sku as t3 on t1.origin_order_id = t3.transaction_id and t1.bailun_sku = t3.bailun_sku
where t1.gmt_create>=@btime and t1.gmt_create<=@etime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", etime.ToString("yyyy-MM-dd 23:59:59"));
                    sql += $" limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_crm_refund_dto>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            #region 退款费用

                            dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.退款,
                                is_delete = (order_item.is_deleted == 1) ? 1 : 0,
                                item = order_item.origin_order_id,
                                update_time = now,
                                no = order_item.origin_order_id,
                                platform_type = order_item.platform_type,
                                web_site = order_item.website,
                                remarks = "",
                                val = 0 - Math.Abs(order_item.amount_refund_rmb),
                                occur_time = order_item.refund_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = order_item.crm_id.ToString()
                            };

                            item.pay_time = order_item.refund_time ?? new DateTime(1991, 1, 1);
                            item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                            item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                            item.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                data_type = item.data_type,
                                bailun_sku = item.bailun_sku,
                                item_no = item.item_no
                            }) ?? 0;
                            if (item.id > 0)
                            {
                                conn.Update(item);
                            }
                            else
                            {
                                conn.Insert(item);
                            }

                            #endregion
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"现金流-退款-{order_item.origin_order_id}-异常" + ex.Message);
                            Console.WriteLine($"现金流-退款-异常" + ex.StackTrace);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-退款-异常" + ex.Message);
                Console.WriteLine("现金流-退款-异常" + ex.StackTrace);
            }

        }

        /// <summary>
        /// 计算调拨订单
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationTransferOrder(DateTime btime, DateTime etime)
        {
            try
            {
                var conn = _connection;
                int page = 1;
                int rows = 1000;
                var logisticsList = ApiUtility.RealTimeShipLogisticsList();
                List<dc_report_logistics_company_config_dto> logistics_company_list = conn.Query<dc_report_logistics_company_config_dto>(" select * from dc_report_logistics_company_config ").AsList();
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                List<dc_auto_config_safe_inventory> inventoryConfigs = conn.Query<dc_auto_config_safe_inventory>(" select * from dc_auto_config_safe_inventory ").AsList();
                while (true)
                {
                    var now = DateTime.Now;
                    var sql = @" select t1.*,t2.create_time as 'complete_time',ifnull(t3.cost_first,0) as 'cost_first',t4.logisticscode from 
dc_base_transfer_verify as t1 
left join dc_base_transfer_complete as t2 on t1.bailun_sku = t2.bailun_sku and t1.transfer_order_id = t2.transfer_order_id
left join dc_mid_cost_first_order as t3 on t1.bailun_sku = t3.bailun_sku and t1.transfer_order_id = t3.transfer_order_id
left join dc_base_transfer_info as t4 on  t1.transfer_order_id = t4.`code`
where t1.create_time>=@btime and t1.create_time<=@etime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", etime.ToString("yyyy-MM-dd 23:59:59"));
                    sql += $" limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_transfer_verify_dto>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            var occur_time = order_item.create_time;
                            var platforms = GetPlatformShare(occur_time, conn, inventoryConfigs, order_item.warehouse_code, order_item.bailun_sku);
                            if(platforms!=null && platforms.Count >= 1)
                            {
                                // 把原本未挂载平台的数据删除
                                conn.Execute(" delete from dc_report_cash_flow_log where  data_type=@data_type and  bailun_sku=@bailun_sku and  `no`=@transfer_order_id and platform_type='' ", new
                                {
                                    bailun_sku = order_item.bailun_sku,
                                    transfer_order_id = order_item.transfer_order_id,
                                    data_type = (int)dc_report_cash_flow_log_data_type_enum.新增头程费用
                                });

                                foreach (var platformItem in platforms)
                                {
                                    #region 调拨支出

                                    dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                                    {
                                        bailun_sku = order_item.bailun_sku,
                                        data_type = (int)dc_report_cash_flow_log_data_type_enum.新增头程费用,
                                        is_delete = (order_item.deleted == 1) ? 1 : 0,
                                        item = order_item.transfer_order_id,
                                        update_time = now,
                                        no = order_item.transfer_order_id,
                                        platform_type = platformItem.platform_type,
                                        web_site = platformItem.web_stie,
                                        remarks = $" 原本调拨费用 {order_item.cost_first} 该平台站点 日均加权销量 {platformItem.sales_weight} 占比 {platformItem.ratio} ",
                                        val = 0 - (Math.Abs(order_item.cost_first) * platformItem.ratio),
                                        occur_time = occur_time,
                                        warehouse_code = order_item.warehouse_code,
                                        pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                        item_no = $"{order_item.transfer_order_id}-{platformItem.platform_type}-{platformItem.web_stie}"
                                    };
                                    var remarks = ""; 
                                    item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                    item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                    item.pay_time = CalculationLogisticsPayTime(logisticsList, logistics_company_list, item.occur_time, order_item.logisticscode, out remarks);
                                    item.remarks += remarks;
                                    item.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                                    {
                                        data_type = item.data_type,
                                        bailun_sku = item.bailun_sku,
                                        item_no = item.item_no
                                    }) ?? 0;
                                    if (item.id > 0)
                                    {
                                        conn.Update(item);
                                    }
                                    else
                                    {
                                        conn.Insert(item);
                                    }

                                    #endregion
                                }
                            }
                            else
                            {
                                #region 调拨支出

                                dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                                {
                                    bailun_sku = order_item.bailun_sku,
                                    data_type = (int)dc_report_cash_flow_log_data_type_enum.新增头程费用,
                                    is_delete = (order_item.deleted == 1) ? 1 : 0,
                                    item = order_item.transfer_order_id,
                                    update_time = now,
                                    no = order_item.transfer_order_id,
                                    platform_type = "",
                                    web_site = "",
                                    remarks = "",
                                    val = 0 - Math.Abs(order_item.cost_first),
                                    occur_time = order_item.create_time,
                                    warehouse_code = order_item.warehouse_code,
                                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                    item_no = order_item.transfer_order_id
                                };
                                var remarks = "";
                                item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.data_type}-{item.platform_type}-{item.web_site}";
                                item.pay_time = CalculationLogisticsPayTime(logisticsList, logistics_company_list, item.occur_time, order_item.logisticscode, out remarks);
                                item.remarks = remarks;
                                item.id = conn.QuerySingleOrDefault<int?>(" select id from dc_report_cash_flow_log where data_type=@data_type and  bailun_sku=@bailun_sku and item_no=@item_no ", new
                                {
                                    data_type = item.data_type,
                                    bailun_sku = item.bailun_sku,
                                    item_no = item.item_no
                                }) ?? 0;
                                if (item.id > 0)
                                {
                                    conn.Update(item);
                                }
                                else
                                {
                                    conn.Insert(item);
                                }

                                #endregion
                            }

                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"现金流-调拨单-{order_item.transfer_order_id}-异常" + ex.Message);
                            Console.WriteLine("现金流-调拨单-异常" + ex.StackTrace);
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-调拨单-异常" + ex.Message);
                Console.WriteLine("现金流-调拨单-异常" + ex.StackTrace);
            }

        }

        /// <summary>
        /// 计算支付时间
        /// </summary>
        public static DateTime CalculationPayTime(List<dc_report_cash_flow_config> configs, DateTime occur_time, int data_type, string platform_type,string web_stite)
        {
            var item_config = configs.Where(s => s.data_type == data_type
            && (s.platform_type == "" || s.platform_type.Equals(platform_type))
            && (s.web_stite == "" || s.web_stite.Equals(web_stite))
            ).OrderByDescending(s=>s.web_stite).FirstOrDefault(); 
            if (item_config == null)
            {
                return occur_time;
            }
            else
            {
                if (item_config.settlement_type == (int)dc_report_cash_flow_settlement_type_enum.等长顺延)
                {
                    return occur_time.AddDays(item_config.settlement_val);
                }
                else if (item_config.settlement_type == (int)dc_report_cash_flow_settlement_type_enum.月度固定)
                {
                    if (occur_time.Day < item_config.settlement_val)
                    {
                        return DateTime.Parse(occur_time.ToString($"yyyy-MM-{item_config.settlement_val} HH:mm:ss"));
                    }
                    else
                    {
                        return DateTime.Parse(occur_time.AddMonths(1).ToString($"yyyy-MM-{item_config.settlement_val} HH:mm:ss"));
                    }
                }
                else
                {
                    return occur_time;
                }
            }
        }

        /// <summary>
        /// 获取物流商结款时间
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public static DateTime CalculationLogisticsPayTime(List<LmsShipLogisticsDto> logistics, List<dc_report_logistics_company_config_dto> logistics_company_list, DateTime occur_time, string line_code, out string remarks)
        {
            if (string.IsNullOrWhiteSpace(line_code))
            {
                remarks = "未获取到订单渠道，取发生时间";
                return occur_time;
            }
            else
            {
                var logistics_item = logistics.FirstOrDefault(s => line_code.Equals(s.Line_Code, StringComparison.OrdinalIgnoreCase));
                if (logistics_item == null)
                {
                    remarks = $"lms 系统中 未查询到该渠道({line_code})，取发生时间";
                    return occur_time;
                }
                else
                {
                    if (string.IsNullOrWhiteSpace(logistics_item.Company_Code))
                    {
                        remarks = $"lms 系统中 该渠道({logistics_item.Line_Name})未配置供应商，取发生时间";
                        return occur_time;
                    }
                    else
                    {
                        var item_logistics_company = logistics_company_list.FirstOrDefault(s => logistics_item.Company_Code == s.company_code);
                        if (item_logistics_company == null)
                        {
                            remarks = $"Aims 系统中未读取到该供应商（{logistics_item.Company_Name}）的账期配置，取发生时间";
                            return occur_time;
                        }
                        else
                        {
                            switch (item_logistics_company.payment_days_type)
                            {
                                case ((int)dc_report_logistics_company_config_payment_days_type.周结):
                                    remarks = $" 供应商 {item_logistics_company.company_name} 【{item_logistics_company.payment_days_type_str}】 ";
                                    return occur_time.GetWeekLastDaySun();
                                case ((int)dc_report_logistics_company_config_payment_days_type.月结):
                                    remarks = $" 供应商 {item_logistics_company.company_name} 【{item_logistics_company.payment_days_type_str}】 ";
                                    return occur_time.LastDayOfMonth();
                                case ((int)dc_report_logistics_company_config_payment_days_type.半月结):
                                    remarks = $" 供应商 {item_logistics_company.company_name} 【{item_logistics_company.payment_days_type_str}】 ";
                                    if (occur_time.Day <= 15)
                                    {
                                        return DateTime.Parse(occur_time.ToString("yyyy-MM-15 HH:mm:ss"));
                                    }
                                    else
                                    {
                                        return occur_time.LastDayOfMonth();
                                    }
                                case ((int)dc_report_logistics_company_config_payment_days_type.固定天数):
                                    remarks = $" 供应商 {item_logistics_company.company_name} 【{item_logistics_company.payment_days_type_str}】【{item_logistics_company.days}天】 ";
                                    return occur_time.AddDays(item_logistics_company.days);
                                case ((int)dc_report_logistics_company_config_payment_days_type.现结):
                                case ((int)dc_report_logistics_company_config_payment_days_type.预付):
                                    remarks = $" 供应商 {item_logistics_company.company_name} 【{item_logistics_company.payment_days_type_str}】 ";
                                    return occur_time;
                                default: throw new Exception("程序异常，aims 配置了算法不支持的账期");
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 取安全库存规则
        /// </summary>
        /// <returns></returns>
        public static dc_auto_config_safe_inventory GetInventoryRule(List<dc_auto_config_safe_inventory> configs, string warehouse_code)
        {
            return (configs.Where(s => s.warehouse_code == warehouse_code).FirstOrDefault() ?? new dc_auto_config_safe_inventory { weighting_coefficient_seven = 0.5M, weighting_coefficient_fourteen = 0.25M, weighting_coefficient_thirty = 0.25M });
        }

        /// <summary>
        /// 获取平台分摊比例
        /// </summary>
        /// <returns></returns>
        public static List<dc_report_cash_flow_platform_share_dto> GetPlatformShare(DateTime occurDate, MySqlConnection conn, List<dc_auto_config_safe_inventory> configs, string warehouse_code, string bailun_sku)
        {
            var rule = GetInventoryRule(configs, warehouse_code);
            var sql = @"SELECT platform_type,website as 'web_stie',
sum( case	when  paid_time >= @btime7 then bailun_sku_quantity_ordered else 0 end)  as 'sales_7',
sum( case	when  paid_time >= @btime14 then bailun_sku_quantity_ordered else 0 end)  as 'sales_14',
sum(bailun_sku_quantity_ordered) as 'sales_30'
FROM `dc_base_oms_sku` where bailun_sku =@bailun_sku and warehouse_code = @warehouse_code and paid_time>= @btime30 and paid_time<=@ctime
GROUP BY platform_type,website";
            var datas = conn.Query<dc_report_cash_flow_platform_share_dto>(sql, new
            {
                btime7 = occurDate.AddDays(-7).ToDayHome(),
                btime14 = occurDate.AddDays(-14).ToDayHome(),
                btime30 = occurDate.AddDays(-30).ToDayHome(),
                ctime = occurDate.ToDayEnd(),
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code
            },commandTimeout:0).ToList();

            if (datas == null || datas.Count <= 0)
            {
                var now = occurDate.AddMonths(1);
                datas = conn.Query<dc_report_cash_flow_platform_share_dto>(sql, new
                {
                    btime7 = now.AddDays(-7).ToDayHome(),
                    btime14 = now.AddDays(-14).ToDayHome(),
                    btime30 = now.AddDays(-30).ToDayHome(),
                    ctime = now.ToDayEnd(),
                    bailun_sku = bailun_sku,
                    warehouse_code = warehouse_code
                }, commandTimeout: 0).ToList();
            }
            if (datas != null && datas.Count >= 1)
            {
                foreach (var item in datas)
                {
                    item.sales_weight = Math.Round(((item.sales_7 * rule.weighting_coefficient_seven) + (item.sales_14 * rule.weighting_coefficient_fourteen) + (item.sales_30 * rule.weighting_coefficient_thirty)), 2);
                }
                // 总加权销量
                var sales_weight_sum = datas.Sum(s => s.sales_weight);
                foreach (var item in datas)
                {
                    item.ratio = item.sales_weight / sales_weight_sum;
                }
                // 把精度导致的误差全部算给最高的平台
                var sumRatio = datas.Sum(s => s.ratio);
                datas.OrderByDescending(s => s.ratio).FirstOrDefault().ratio += (1 - sumRatio);
            }

            return datas;
        }

        public static List<report_cash_flow_view_dto> GetView(report_cash_flow_view_search_dto search)
        {
            List<report_cash_flow_view_dto> datas = new List<report_cash_flow_view_dto>();
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 1 }, date_type_str = "销售数量", remarks = "", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 2 }, date_type_str = "销售金额", remarks = "过滤掉刷单的订单 ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 3 }, date_type_str = "退款", remarks = "crm 抓取的退款数据，未区分仓库", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 4 }, date_type_str = "利润", remarks = "oms 中的订单，分摊到sku的利润，不包含广告费，上架费，退款", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 5 }, date_type_str = "平台费用", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 6 }, date_type_str = "释放销售成本", remarks = "采购成本，利润中已经计算了", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 7 }, date_type_str = "释放头程费用", remarks = "", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 8 }, date_type_str = "尾程费用", remarks = "oms 订单中海外仓发货的，支付时间 已 读取对应物流商的账期", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 9 }, date_type_str = "新增采购费用", remarks = " 1、采购系统的数据，外采订单直接读取订单支付时间。2、向自己工厂采购的（无需付款）的单，支付时间=下单时间 ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 10 }, date_type_str = "新增头程费用", remarks = " 调拨单的数据，支付时间 已 读取对应物流商的账期，注：调拨单需要付款之后才能获取到金额 ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 11 }, date_type_str = "fba费", remarks = "  ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 12 }, date_type_str = "paypal费", remarks = "  ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 4, 3 }, date_type_str = "汇总利润", remarks = " oms 中的订单，销售额 - 销售成本 - 尾程费 -  头程费 - 退款 - 平台费 - fba费 -paypal 费 （不包含广告费、上架费）", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 2, 3, 5, 8, 9, 10, 11, 12 }, date_type_str = "现金流结余", remarks = " 销售金额 - 新增采购费用 - 新增头程费用 - 平台费用 - 尾程费用  ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            var btime = DateTime.Now.AddDays(-8).ToDayHome();
            var etime = DateTime.Now.AddDays(-1).ToDayEnd();
            if (search.btime != null)
            {
                btime = search.btime.Value.ToDayHome();
            }
            if (search.etime != null)
            {
                etime = search.etime.Value.ToDayEnd();
            }

            var conn = _connection;
            var occur_sql = " select * from dc_report_cash_flow_group_day where `type`=1 and  `date`>=@btime and `date`<=@etime ";
            var pay_sql = " select * from dc_report_cash_flow_group_day where `type`=2  and `date`>=@btime and `date`<=@etime ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                occur_sql = " select occur_time as 'date',sum(val) as 'val',data_type,1 as 'type',platform_type,web_site from dc_report_cash_flow_log where is_delete=0 and occur_time >=@btime and occur_time<=@etime and bailun_sku=@bailun_sku and warehouse_code = @warehouse_code ";
                pay_sql = " select pay_time as 'date',sum(val) as 'val',data_type,1 as 'type',platform_type,web_site from dc_report_cash_flow_log where is_delete=0 and pay_time >=@btime and pay_time<=@etime and bailun_sku=@bailun_sku and warehouse_code = @warehouse_code ";

                parameters.Add("bailun_sku",search.bailun_sku);
                parameters.Add("warehouse_code", search.warehouse_code);
            }
           
            parameters.Add("btime", btime);
            parameters.Add("etime", etime);

            if(!string.IsNullOrWhiteSpace(search.platform_type))
            {
                if ("fba".Equals(search.platform_type,StringComparison.OrdinalIgnoreCase) || "amazon".Equals(search.platform_type, StringComparison.OrdinalIgnoreCase))
                {
                    occur_sql += " and platform_type in @platform_types ";
                    pay_sql += " and platform_type in @platform_types ";
                    parameters.Add("platform_types", new List<string> { "amazon","fba"});
                }
                else
                {
                    occur_sql += " and platform_type=@platform_type ";
                    pay_sql += " and platform_type=@platform_type ";
                    parameters.Add("platform_type", search.platform_type);
                }
                
            } 
            
            if(!string.IsNullOrWhiteSpace(search.web_site))
            {
                occur_sql += " and web_site=@web_site ";
                pay_sql += " and web_site=@web_site ";
                parameters.Add("web_site", search.web_site);
            }

            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                occur_sql += " GROUP BY occur_time_year_month_no,day(occur_time) ";
                pay_sql += " GROUP BY pay_time_year_month_no,day(pay_time) ";
            }

            var occur_datas = conn.Query<dc_report_cash_flow_group_day>(occur_sql, parameters, commandTimeout: 0).ToList();
            var pay_datas = conn.Query<dc_report_cash_flow_group_day>(pay_sql, parameters, commandTimeout: 0).ToList();

            foreach (var itemData in datas)
            {
                itemData.dates.Add(new report_cash_flow_view_dto.date_dto
                {
                    btime = btime,
                    etime = etime,
                    date_title = $"汇总",
                    occur_val = occur_datas.Where(s => itemData.date_type.Contains(s.data_type)).Sum(s => s.val),
                    pay_val = pay_datas.Where(s => itemData.date_type.Contains(s.data_type)).Sum(s => s.val)
                });
            }

            var thisTime = btime;
            while (thisTime <= etime)
            {
                var bThisTime = thisTime.ToDayHome();
                var eThisTime = thisTime.ToDayEnd();
                foreach (var itemData in datas)
                {
                    itemData.dates.Add(new report_cash_flow_view_dto.date_dto
                    {
                        btime = bThisTime,
                        etime = eThisTime,
                        date_title = $"{bThisTime.Month}-{bThisTime.Day}",
                        occur_val = occur_datas.Where(s => s.date >= bThisTime && s.date <= eThisTime && itemData.date_type.Contains(s.data_type)).Sum(s => s.val),
                        pay_val = pay_datas.Where(s => s.date >= bThisTime && s.date <= eThisTime && itemData.date_type.Contains(s.data_type)).Sum(s => s.val)
                    });
                }

                thisTime = thisTime.AddDays(1);
            }
            return datas;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        public static List<dc_report_cash_flow_log_dto> Export(dc_report_cash_flow_log_export_search_dto search)
        {
            if (search.btime == null || search.etime == null)
            {
                throw new Exception("时间范围必选");
            }
            if (search.type == null)
            {
                throw new Exception("时间类型必选");
            }
            var sql = " select t1.*,t2.warehouse_name from dc_report_cash_flow_log as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where t1.is_delete=0 and `val`!=0 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search.type == 1)
            {
                sql += " and t1.occur_time>=@btime ";
                sql += " and t1.occur_time<=@etime ";
            }
            else
            {
                sql += " and t1.pay_time>=@btime ";
                sql += " and t1.pay_time<=@etime ";
            }
            parameters.Add("btime", search.btime.Value.ToDayHome());
            parameters.Add("etime", search.etime.Value.ToDayEnd());

            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                sql += " and t1.bailun_sku = @bailun_sku ";
                parameters.Add("bailun_sku", search.bailun_sku);
            }
            if (search.data_type != null && search.data_type.Count >= 0)
            {
                sql += " and t1.data_type in @data_type ";
                parameters.Add("data_type", search.data_type);
            }
            return _connection.Query<dc_report_cash_flow_log_dto>(sql, parameters).AsList();
        }



        public static Page<dc_report_cash_flow_sku_group_dto> SkuView(dc_report_cash_flow_sku_group_search_dto search)
        {
            if (search.data_type == null || search.data_type <= 0)
            {
                search.data_type = 1;
            }
            var sql = @" select t1.*,t2.sku_title_cn as 'sku_name',t2.`status` as 'sku_status',t2.buyer_name,ifnull(t3.warehouse_name,t3.warehouse_extend_name) as  'warehouse_name',ifnull(t4.usable_stock,0) as 'usable_stock',(ifnull(t5.quantity_transfer,0)+ifnull(t5.quantity_purchase,0)) as 'on_the_way',
(ifnull(t4.usable_stock,0) * t2.unit_price) as 'stock_amount',(ifnull(t5.quantity_transfer,0)+ifnull(t5.quantity_purchase,0)) as 'on_the_way_amount',ifnull(t8.history_sevenday_sales,0) as 'history_sevenday_sales',ifnull(t8.history_fourteenday_sales,0) as 'history_fourteenday_sales',ifnull(t8.history_thirtyday_sales,0) as 'history_thirtyday_sales'
from dc_report_cash_flow_sku_group as t1 
left join dc_base_sku as t2 on t1.bailun_sku=t2.bailun_sku 
left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code
left join dc_base_stock as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
left join dc_mid_transit as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
left join dc_auto_config_sku_label as t6 on t1.bailun_sku = t6.bailun_sku
left join dc_auto_config_sku_warehouse as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code 
left join dc_auto_turnover as t8 on t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.warehouse_code 
where t1.type=@type ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("type", search.data_type);

            if (!string.IsNullOrWhiteSpace(search.product_type))
            {
                sql += " and t2.product_type=@product_type ";
                parameters.Add("product_type", search.product_type);
            }
            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku ";
                parameters.Add("bailun_sku", search.bailun_sku);
            }
            if (!string.IsNullOrWhiteSpace(search.sku_label))
            {
                sql += " and t6.label=@sku_label ";
                parameters.Add("sku_label", search.sku_label);
            }
            if (!string.IsNullOrWhiteSpace(search.buyer_name))
            {
                sql += " and t2.buyer_name = @buyer_name";
                parameters.Add("buyer_name", search.buyer_name);
            }
            if (search.monitor_status == 1)
            {
                sql += " and t7.`status`=1 ";
            }
            if (search.monitor_status == 0)
            {
                sql += " and ( t7.`status`=0 or t7.`status` is null ) ";
            }
            if (!string.IsNullOrWhiteSpace(search.supplier_name))
            {
                sql += " and t2.suppliers_name =@suppliers_name ";
                parameters.Add("suppliers_name", search.supplier_name);
            }
            if (search.warehouse_code != null)
            {
                sql += $" and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search.warehouse_code);
            }
            else
            {
                if (!string.IsNullOrWhiteSpace(search.warehousetype))
                {
                    sql += " and t3.hq_type=" + $"'{search.warehousetype}'";
                }
                if (search.warehousearea > 0)
                {
                    sql += " and t3.area_id=" + search.warehousearea;
                }
            }



            return _connection.Page<dc_report_cash_flow_sku_group_dto>(sql, search, parameters);
        }

    }
}
