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

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

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

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

                            var old_cash_flow_list = conn.Query<old_cash_flow_dto>(" select data_type,id from dc_report_cash_flow_log where  bailun_sku=@bailun_sku and item_no=@item_no ", new
                            {
                                bailun_sku = order_item.bailun_sku,
                                item_no = item_no
                            }).ToList();

                            #region PayPal费

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

                            #endregion

                            #region fba 费

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

                            #endregion

                            #region 释放头程费用

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

                            #endregion

                            #region 销售释放成本

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

                            #endregion

                            #region 平台费

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

                            #endregion

                            #region 利润

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

                            #endregion

                            #region 销售数量

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

                            #endregion

                            #region 销售金额

                            dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.销售金额,
                                is_delete = (order_item.has_delete == 1 || order_item.has_scalp == 1) ? 1 : 0,
                                item = order_item.bailun_order_id,
                                update_time = now,
                                no = order_item.bailun_order_id,
                                platform_type = order_item.platform_type,
                                remarks = "",
                                val = order_item.amount_sales * order_item.seller_order_exchange_rate * order_item.bailun_sku_quantity_ordered,
                                occur_time = order_item.paid_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_code,
                                pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                                item_no = order_item.bailun_order_id
                            };
                            item.occur_time_year_month_no = $"{item.occur_time.Year}-{item.occur_time.Month}-{item.bailun_sku}";
                            item.pay_time = CalculationPayTime(configs, item.occur_time, item.data_type, item.platform_type);
                            item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.bailun_sku}";
                            item.id = (old_cash_flow_list.FirstOrDefault(s => s.data_type == item.data_type) ?? new old_cash_flow_dto()).id;
                            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);
                        }

                    }

                    if (addList != null && addList.Count >= 1)
                    {
                        conn.BatchInsert(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 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 ;";
            var res = conn.Execute(sql, datas);
        }

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

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

                            #region 尾程费

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

                            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.bailun_sku}";
                            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.bailun_sku}";
                            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)
                    {
                        conn.BatchInsert(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)
        {
            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.*,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"));
                    sql += $" limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_purchase_details_dto>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            #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 = "",
                                remarks = "",
                                val = 0 - Math.Abs(order_item.amount_paid),
                                occur_time = order_item.create_time ?? new DateTime(1991, 1, 1),
                                warehouse_code = order_item.warehouse_from_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.bailun_sku}";
                            item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.bailun_sku}";
                            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}-异常" + 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.* from dc_base_crm_refund as t1 where gmt_create>=@btime and gmt_create<=@etime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", etime.ToString("yyyy-MM-dd 23:59:59"));
                    sql += $" limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_crm_refund>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            #region 退款费用

                            dc_report_cash_flow_log item = new dc_report_cash_flow_log()
                            {
                                bailun_sku = order_item.bailun_sku,
                                data_type = (int)dc_report_cash_flow_log_data_type_enum.退款,
                                is_delete = (order_item.is_deleted == 1) ? 1 : 0,
                                item = order_item.origin_order_id,
                                update_time = now,
                                no = order_item.origin_order_id,
                                platform_type = "",
                                remarks = "",
                                val = 0 - Math.Abs(order_item.amount_refund_rmb),
                                occur_time = order_item.refund_time ?? new DateTime(1991, 1, 1),
                                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.bailun_sku}";
                            item.pay_time_year_month_no = $"{item.pay_time.Year}-{item.pay_time.Month}-{item.bailun_sku}";
                            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();
                while (true)
                {
                    var now = DateTime.Now;
                    var sql = @" select t1.*,t2.create_time as 'complete_time',ifnull(t3.cost_first,0) as 'cost_first',t4.logisticscode from 
dc_base_transfer_verify as t1 
left join dc_base_transfer_complete as t2 on t1.bailun_sku = t2.bailun_sku and t1.transfer_order_id = t2.transfer_order_id
left join dc_mid_cost_first_order as t3 on t1.bailun_sku = t3.bailun_sku and t1.transfer_order_id = t3.transfer_order_id
left join dc_base_transfer_info as t4 on  t1.transfer_order_id = t4.`code`
where t1.create_time>=@btime and t1.create_time<=@etime ";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("btime", btime.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("etime", etime.ToString("yyyy-MM-dd 23:59:59"));
                    sql += $" limit {(page - 1) * rows},{rows} ";
                    var order_list = conn.Query<dc_base_transfer_verify_dto>(sql, parameters).AsList();
                    if (order_list == null || order_list.Count <= 0)
                    {
                        break;
                    }
                    page++;
                    foreach (var order_item in order_list)
                    {
                        try
                        {
                            #region 调拨支出

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

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

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

        }

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

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

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

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

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

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

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

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

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

        public static Page<dc_report_cash_flow_sku_group_dto> SkuView(dc_report_cash_flow_sku_group_search_dto search)
        {
            if (search.data_type == null || search.data_type <= 0)
            {
                search.data_type = 1;
            }
            var sql = " select t1.*,t2.sku_title_cn as 'sku_name',t2.`status` as 'sku_status',t2.buyer_name from dc_report_cash_flow_sku_group as t1 left join dc_base_sku as t2 on t1.bailun_sku=t2.bailun_sku where t1.type=@type ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("type", search.data_type);

            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku ";
                parameters.Add("bailun_sku", search.bailun_sku);
            }

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