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 = MyMySqlConnection._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_30 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, commandTimeout: 0).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>();
                    // 未拦截订单
                    var bailun_interception_status = new List<string>() { "None", "Failed" };
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            var item_no = order_item.bailun_order_id;
                            var is_delete = (
                                (!bailun_interception_status.Any(v => v.Equals(order_item.bailun_interception_status, StringComparison.OrdinalIgnoreCase))) // 拦截单
                                ||
                                order_item.has_innersale == 1 // 内购单
                                ||
                                "Canceled".Equals(order_item.bailun_order_status, StringComparison.OrdinalIgnoreCase) // 取消单
                                ||
                                ((!"FBA".Equals(order_item.platform_type, StringComparison.OrdinalIgnoreCase)) && "CantHandle".Equals(order_item.bailun_order_status, StringComparison.OrdinalIgnoreCase)) // 非fba 的订单状态为取消
                                ||
                                order_item.has_scalp == 1 // 刷单
                                ||
                                order_item.has_delete == 1
                                ||
                                order_item.company_id != 1
                                ) ? 1 : 0;

                            #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 = is_delete,
                                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 = is_delete,
                                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 = is_delete,
                                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 = is_delete,
                                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 = is_delete,
                                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 = is_delete,
                                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 = is_delete,
                                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 = is_delete,
                                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 = MyMySqlConnection._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_30 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, commandTimeout: 0).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>();
                    // 未拦截订单
                    var bailun_interception_status = new List<string>() { "None", "Failed" };
                    foreach (var order_item in order_list)
                    {
                        try
                        {

                            var is_delete = (
                                (!bailun_interception_status.Any(v => v.Equals(order_item.bailun_interception_status, StringComparison.OrdinalIgnoreCase))) // 拦截单
                                ||
                                order_item.has_innersale == 1 // 内购单
                                ||
                                "Canceled".Equals(order_item.bailun_order_status, StringComparison.OrdinalIgnoreCase) // 取消单
                                ||
                                ((!"FBA".Equals(order_item.platform_type, StringComparison.OrdinalIgnoreCase)) && "CantHandle".Equals(order_item.bailun_order_status, StringComparison.OrdinalIgnoreCase)) // 非fba 的订单状态为取消
                                ||
                                order_item.has_scalp == 1 // 刷单
                                ||
                                order_item.has_delete == 1
                                ||
                                order_item.company_id != 1
                                ) ? 1 : 0;

                            #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 = is_delete,
                                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 += $" order by t1.gmt_create desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_purchase_details_dto>(sql, parameters, commandTimeout: 0).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 ", 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_30 as t2 on t1.origin_order_id = t2.origin_order_id and t1.bailun_sku = t2.bailun_sku
left join dc_base_oms_sku_30 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 += $" order by t1.id desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_crm_refund_dto>(sql, parameters, commandTimeout: 0).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 = @"SET SESSION group_concat_max_len = 102400000;
select * from (
select t2.creationtime,t2.logisticscode,t2.isdeleted,t1.transfer_order_id,t1.box_id,GROUP_CONCAT(t1.bailun_sku,':',t3.weight) as 'bailun_skus',t1.warehouse_code,cost_first as 'sum_cost_first' from dc_base_cost_first as t1 
left join dc_base_transfer_info as t2 on t1.transfer_order_id = t2.`code`
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
where t2.creationtime>=@btime and t2.creationtime<=@etime
group by t1.transfer_order_id,t1.box_id
) as t1
 ";
                    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 += $" order by t1.creationtime desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<transfer_box_dto>(sql, parameters, commandTimeout: 0).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        foreach (var order_item_sku in order_item.skus)
                        {
                            try
                            {
                                var occur_time = order_item.creationtime;
                                var platforms = GetPlatformShare(occur_time, conn, inventoryConfigs, order_item.warehouse_code, order_item_sku.bailun_sku);

                                // 查询是否有真实费用
                                var is_real = 0;
                                var real_cost = order_item_sku.cost_first;
                                var real_data = conn.QuerySingleOrDefault<dc_base_transfer_cost_real>(" select * from dc_base_transfer_cost_real where bailun_sku=@bailun_sku and transfer_order_id=@transfer_order_id and box_id=@box_id ",new {
                                    bailun_sku = order_item_sku.bailun_sku,
                                    transfer_order_id = order_item.transfer_order_id,
                                    box_id = order_item.box_id
                                });
                                if (real_data != null)
                                {
                                    is_real = 1;
                                    real_cost = real_data.sku_avg_cost;
                                }

                                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 ", new
                                    {
                                        bailun_sku = order_item_sku.bailun_sku,
                                        transfer_order_id = order_item.unique_no,
                                        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_sku.bailun_sku,
                                            data_type = (int)dc_report_cash_flow_log_data_type_enum.新增头程费用,
                                            is_delete = (order_item.isdeleted == 1) ? 1 : 0,
                                            item = order_item.transfer_order_id,
                                            update_time = now,
                                            no = order_item.unique_no,
                                            platform_type = platformItem.platform_type,
                                            web_site = platformItem.web_stie,
                                            remarks = $" 原本调拨费用 {order_item_sku.cost_first} 该平台站点 日均加权销量 {platformItem.sales_weight} 占比 {platformItem.ratio} ",
                                            val = 0 - (Math.Abs(real_cost) * 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.unique_no}-{platformItem.platform_type}-{platformItem.web_stie}",
                                            is_real =is_real
                                        };
                                        var remarks = "";
                                        item.pay_time = CalculationLogisticsPayTime(logisticsList, logistics_company_list, item.occur_time, order_item.logisticscode, out 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.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_sku.bailun_sku,
                                        data_type = (int)dc_report_cash_flow_log_data_type_enum.新增头程费用_首单,
                                        is_delete = (order_item.isdeleted == 1) ? 1 : 0,
                                        item = order_item.unique_no,
                                        update_time = now,
                                        no = order_item.unique_no,
                                        platform_type = "",
                                        web_site = "",
                                        remarks = "",
                                        val = 0 - Math.Abs(real_cost),
                                        occur_time = order_item.creationtime,
                                        warehouse_code = order_item.warehouse_code,
                                        pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                                        item_no = order_item.unique_no,
                                        is_real  =is_real
                                    };
                                    var remarks = "";
                                    item.pay_time = CalculationLogisticsPayTime(logisticsList, logistics_company_list, item.occur_time, order_item.logisticscode, out 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.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>
        /// 计算ebay的广告费
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationAdFeeEbay(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.* from dc_base_finance_ebay as t1 where account_entry_type='FeeAd'
and t1.bj_date>=@btime and t1.bj_date<=@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 += $" order by t1.bj_date desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_finance_ebay>(sql, parameters, commandTimeout: 0).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 = "",
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.广告费,
                                is_delete = 0,
                                item = order_item.id.ToString(),
                                update_time = now,
                                no = order_item.id.ToString(),
                                platform_type = "Ebay",
                                web_site = "",
                                remarks = "",
                                val = 0 - Math.Abs(order_item.gross_amount * order_item.exchange_rate),
                                occur_time = order_item.bj_date ?? new DateTime(1991, 1, 1),
                                warehouse_code = "",
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = "Ebay-" + order_item.id.ToString()
                            };

                            item.pay_time = order_item.bj_date ?? 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($"现金流-广告费 Ebay-{order_item.id}-异常" + ex.Message);
                            Console.WriteLine($"现金流-广告费 Ebay-异常" + ex.StackTrace);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-广告费 Ebay-异常" + ex.Message);
                Console.WriteLine("现金流-广告费 Ebay-异常" + ex.StackTrace);
            }

        }

        /// <summary>
        /// 计算ebay的刊登费
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationPublishFeeEbay(DateTime btime, DateTime etime)
        {
            try
            {
                List<string> feeFields = new List<string>() {
                    "BuyItNowFee",
                    "CreditGalleryPlus",
                    "CreditInsertion",
                    "CreditSMBasicPro",
                    "ExtendedDurationFee",
                    "ExtendedDurationFeeCredit",
                    "FeeAuctionEndEarly",
                    "FeeBold",
                    "FeeFinalValueShipping",
                    "FeeGalleryPlus",
                    "FeeLargePicture",
                    "FeeSchedule",
                    "PrivateListing",
                    "SubscriptionSMBasic",
                    "SubscriptionSMBasicPro",
                    "SubscriptioneBayStores",
                    "SubtitleFee",
                    "SubtitleFeeCredit",
                    "CrediteBayStores"
                };
                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.* from dc_base_finance_ebay as t1 where account_entry_type in @feeFields
and t1.bj_date>=@btime and t1.bj_date<=@etime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("feeFields", feeFields);
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", etime.ToString("yyyy-MM-dd 23:59:59"));
                    sql += $" order by t1.id desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_finance_ebay>(sql, parameters, commandTimeout: 0).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 = "",
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.上架费,
                                is_delete = 0,
                                item = order_item.id.ToString(),
                                update_time = now,
                                no = order_item.id.ToString(),
                                platform_type = "Ebay",
                                web_site = "",
                                remarks = "",
                                val = 0 - Math.Abs(order_item.gross_amount * order_item.exchange_rate),
                                occur_time = order_item.bj_date ?? new DateTime(1991, 1, 1),
                                warehouse_code = "",
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = "Ebay-" + order_item.id.ToString()
                            };

                            item.pay_time = order_item.bj_date ?? 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($"现金流-广告费 Ebay-{order_item.id}-异常" + ex.Message);
                            Console.WriteLine($"现金流-广告费 Ebay-异常" + ex.StackTrace);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-广告费 Ebay-异常" + ex.Message);
                Console.WriteLine("现金流-广告费 Ebay-异常" + ex.StackTrace);
            }

        }

        /// <summary>
        /// 计算 amazon 的广告费
        /// </summary>
        /// <param name="btime"></param>
        /// <param name="etime"></param>
        public static void CalculationAdFeeAmazon(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.* from dc_base_finance_amazon_ad_product as t1 
where t1.report_date>=@btime and t1.report_date<=@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 += $" order by t1.id desc limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_finance_amazon_ad_product>(sql, parameters, commandTimeout: 0).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 = "",
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.广告费,
                                is_delete = 0,
                                item = order_item.id.ToString(),
                                update_time = now,
                                no = order_item.id.ToString(),
                                platform_type = "Amazon",
                                web_site = "",
                                remarks = "",
                                val = 0 - Math.Abs(order_item.cost * order_item.exchange_rate),
                                occur_time = order_item.report_date,
                                warehouse_code = "",
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = "amazon-" + order_item.id.ToString()
                            };

                            item.pay_time = order_item.report_date;
                            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($"现金流-广告费 Amazon-{order_item.id}-异常" + ex.Message);
                            Console.WriteLine($"现金流-广告费 Amazon-异常" + ex.StackTrace);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("现金流-广告费 Amazon-异常" + ex.Message);
                Console.WriteLine("现金流-广告费 Amazon-异常" + 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)
        {

            if (
                data_type == (int)dc_report_cash_flow_log_data_type_enum.利润
                || data_type == (int)dc_report_cash_flow_log_data_type_enum.释放头程费用
                || data_type == (int)dc_report_cash_flow_log_data_type_enum.释放销售成本
                || data_type == (int)dc_report_cash_flow_log_data_type_enum.销售数量
                )
            {
                data_type = (int)dc_report_cash_flow_log_data_type_enum.销售金额;
            }
            var item_configs = 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).ToList();
            if (item_configs == null || item_configs.Count <= 0)
            {
                return occur_time;
            }
            else
            {
                dc_report_cash_flow_config item_config = item_configs[0];
                if (item_configs.Where(v => v.settlement_type == (int)dc_report_cash_flow_settlement_type_enum.月度固定).Count() > 1)
                {
                    // 取发生时间后，最早的一个付款日期
                    item_config = item_configs.Where(s => s.settlement_type > occur_time.Day).OrderBy(s => s.settlement_type).FirstOrDefault();
                    if (item_config == null)
                    {
                        // 如果没取到，则有可能是最后一天了，要下个月付款了，。则取最早的一天
                        item_config = item_configs.OrderBy(s => s.settlement_type).FirstOrDefault();
                    }
                }
                if (item_configs.Where(v => v.settlement_type == (int)dc_report_cash_flow_settlement_type_enum.月度固定_下期付款).Count() > 1)
                {
                    // 取发生时间后，最早的一个付款日期
                    var temp_item_configs = item_configs.Where(s => s.settlement_type > occur_time.Day).OrderBy(s => s.settlement_type).ToList();
                    if (temp_item_configs.Count > 1)
                    {
                        item_config = temp_item_configs[1];
                    }
                    else if (temp_item_configs.Count == 1)
                    {
                        // 只有一条，取下期
                        item_config = item_configs.OrderBy(s => s.settlement_type).FirstOrDefault();
                    }
                    else if (temp_item_configs.Count == 0)
                    {
                        // 一条都没有，取整个月第二期
                        item_config = item_configs.OrderBy(s => s.settlement_type).ToList()[1];
                    }
                }

                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.AddMonths(1).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.AddMonths(1).ToString("yyyy-MM-15 HH:mm:ss"));
                                    }
                                    else
                                    {
                                        return occur_time.AddMonths(1).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, MyMySqlConnection 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> { 15 }, date_type_str = "广告费", remarks = "未区分 sku , 仓库", dates = new List<report_cash_flow_view_dto.date_dto>() });
            datas.Add(new report_cash_flow_view_dto { date_type = new List<int> { 16 }, date_type_str = "上架费", remarks = "未区分 sku , 仓库", 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, 15, 16 }, 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> { 13 }, 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> { 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> { 14 }, 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, 15, 16 }, 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, 15, 16, 5, 8, 9, 13, 10, 14, 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_log_dto> LogPage(dc_report_cash_flow_log_export_page_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 ,{search.type ?? 2} as 'type' 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 ";
            if (search.issum)
            {
                sql = $" select  sum(`val`) as 'val' from dc_report_cash_flow_log as t1 where t1.is_delete=0 and `val`!=0 ";
            }
            else
            {
                if (search.view_type == 2)
                {
                    sql = $@" select  t1.data_type,t1.pay_type,sum(`val`) as 'val' 
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 (search.web_site != null)
            {
                sql += " and t1.web_site = @web_site ";
                parameters.Add("web_site", search.web_site);
            }
            if (!string.IsNullOrWhiteSpace(search.warehouse_code))
            {
                sql += " and t1.warehouse_code = @warehouse_code ";
                parameters.Add("warehouse_code", search.warehouse_code);
            }
            if (!string.IsNullOrWhiteSpace(search.platform_type))
            {
                sql += " and t1.platform_type = @platform_type ";
                parameters.Add("platform_type", search.platform_type);
            }
            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);
            }
            if (search.view_type == 2)
            {
                sql += "group by t1.data_type ";
            }
            if (search.issum)
            {
                return new Page<dc_report_cash_flow_log_dto>()
                {
                    Items = new List<dc_report_cash_flow_log_dto> { _connection.QuerySingleOrDefault<dc_report_cash_flow_log_dto>(sql, parameters) }
                };
            }
            else
            {
                if (search.view_type == 2)
                {
                    return new Page<dc_report_cash_flow_log_dto>()
                    {
                        Items = _connection.Query<dc_report_cash_flow_log_dto>(sql, parameters).ToList()
                    };
                }
                else
                {
                    return _connection.Page<dc_report_cash_flow_log_dto>(sql, search, parameters);
                }

            }

        }

        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',
(0-ifnull(t4.pay_amount_30,0)) as 'pay_amount_30',ifnull(t4.income_amount_30,0) as 'income_amount_30'
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 ";
            if (search.is_sum == true)
            {
                sql = @" select 
sum(t1.balance) as 'balance',
sum(t1.current_balance) as 'current_balance',
sum(t1.current_income) as 'current_income',
sum(t1.current_expend) as 'current_expend',
sum(t1.last_balance) as 'last_balance',
sum(t1.last_income) as 'last_income',
sum(t1.last_expend) as 'last_expend',
sum(ifnull(t4.usable_stock,0)) as 'usable_stock',
sum(ifnull(t5.quantity_transfer,0)+ifnull(t5.quantity_purchase,0)) as 'on_the_way',
sum(ifnull(t4.usable_stock,0) * t2.unit_price) as 'stock_amount',
sum(ifnull(t5.quantity_transfer,0)+ifnull(t5.quantity_purchase,0)) as 'on_the_way_amount',
sum(0-ifnull(t4.pay_amount_30,0)) as 'pay_amount_30',
sum(ifnull(t4.income_amount_30,0)) as 'income_amount_30'

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;
                }
            }

            if (search.is_sum)
            {
                return new Page<dc_report_cash_flow_sku_group_dto>() { 
                Items = new List<dc_report_cash_flow_sku_group_dto> { _connection.QuerySingleOrDefault<dc_report_cash_flow_sku_group_dto>(sql, parameters) }
            };
            }
            else
            {
                return _connection.Page<dc_report_cash_flow_sku_group_dto>(sql, search, parameters);
            }
          

        }

        /// <summary>
        /// 同步调拨系统的真实费用
        /// </summary>
        public static void SynchroTransferCost()
        {
            var conn = _connection;
            var page = 1;
            var rows = 20;
            while (true)
            {
                var items = ApiUtility.GetTransferCost(new api_transfer_cost_real_input_dto { data = new api_transfer_cost_real_input_dto.data_dto { page = new api_transfer_cost_real_input_dto.data_dto.page_dto { page = page,rows = rows},} });
                if(items==null || items.Count <= 0)
                {
                    break;
                }
                page++;

                foreach (var item in items)
                {
                    foreach (var skuItem in item.skus)
                    {
                        dc_base_transfer_cost_real db_data = new dc_base_transfer_cost_real()
                        {
                            bailun_sku = skuItem.sku,
                            sku_avg_cost  = skuItem.skuAvgCost,
                            transfer_order_id = item.orderCode,
                            box_id = item.boxCode
                        };
                        db_data.id = conn.QuerySingleOrDefault<int?>(" select id from dc_base_transfer_cost_real where bailun_sku=@bailun_sku and transfer_order_id=@transfer_order_id and box_id=@box_id limit 1 ", new {
                            bailun_sku = db_data.bailun_sku,
                            transfer_order_id = db_data.transfer_order_id,
                            box_id = item.boxCode
                        })??0;
                        if (db_data.id > 0)
                        {
                            conn.Update(db_data);
                        }
                        else
                        {
                            conn.Insert(db_data);
                        }
                    }
                    
                }
            }
        }

    }
}
