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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 现金流预测
    /// </summary>
    public class report_cash_flow_forecast_dao : connectionHelper
    {
        public static void Calculation()
        {
            var conn = _connection;
            var task_datas = conn.Query<dc_report_cash_flow_forecast_task>(" select * from dc_report_cash_flow_forecast_task where `status`=0 order by update_time asc  ").ToList();
            foreach (var item in task_datas)
            {
                CalculationSingle(conn, item);
            }
        }

        /// <summary>
        /// 计算现金流
        /// </summary>
        public static void CalculationSingle(MySqlConnection conn, dc_report_cash_flow_forecast_task task_data)
        {
            try
            {
                task_data.status = 1;
                task_data.error_message = "";
                task_data.error_stack_trace = "";
                conn.Update(task_data);

                var now = DateTime.Now;
                conn.Execute(" delete from dc_report_cash_flow_forecast_task_log where task_id=@task_id  ", new { task_id = task_data.id });
                var logisticsList = ApiUtility.RealTimeShipLogisticsList();
                //先清理数据
                List<dc_report_cash_flow_forecast_task_log> logs = new List<dc_report_cash_flow_forecast_task_log>();
                List<dc_report_cash_flow_forecast_task_sale> sale_list = conn.Query<dc_report_cash_flow_forecast_task_sale>(" select * from dc_report_cash_flow_forecast_task_sale where task_id=@task_id ", new { task_id = task_data.id }).AsList();

                if (sale_list == null || sale_list.Count <= 0)
                {
                    throw new Exception(" 未配置销量 ");
                }
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                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_auto_config_stock_up_days> up_days = conn.Query<dc_auto_config_stock_up_days>(" select * from dc_auto_config_stock_up_days ").ToList();
                List<dc_auto_config_safe_inventory> safe_inventorys = conn.Query<dc_auto_config_safe_inventory>(" select * from dc_auto_config_safe_inventory ").ToList();

                task_data.error_stack_trace = "模拟计算周转数据";
                conn.Update(task_data);
                // 模拟跑周转模型
                List<invented_turnover_model> turnover_list = GetTurnoverModels(task_data, sale_list, MatchingUpDays(up_days, task_data.warehouse_code), MatchingSafeInventorys(safe_inventorys, task_data.warehouse_code));
                conn.Execute(" delete from dc_report_cash_flow_forecast_turnover where task_id=@task_id  ", new { task_id = task_data.id });
                if (turnover_list != null)
                {
                    foreach (var item in turnover_list)
                    {
                        conn.Insert(new dc_report_cash_flow_forecast_turnover { date = item.date, type = item.type, val = item.val });
                    }

                }


                task_data.error_stack_trace = "模拟计算销量";
                conn.Update(task_data);

                // 查询平台费
                task_data.platform_fee = ApiUtility.GetPlatformFee(new Models.ApiDto.api_platform_fee_input_dto
                {
                    datas = new List<Models.ApiDto.api_platform_fee_input_dto.data_dto> {
               new Models.ApiDto.api_platform_fee_input_dto.data_dto{ Plat =task_data.platform_type ,PlatCategoryId  =task_data.platform_category_id,Site  =task_data.web_site }
                }
                }).categoryRate;

               

                // 模拟销销售额
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = (int)dc_report_cash_flow_log_data_type_enum.销售金额,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val * task_data.sale_unit_price_cny,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = report_cash_flow_dao.CalculationPayTime(configs, s.date, (int)dc_report_cash_flow_log_data_type_enum.销售金额, task_data.platform_type, task_data.web_site)
                }));

                // 模拟销量
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = (int)dc_report_cash_flow_log_data_type_enum.销售数量,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = report_cash_flow_dao.CalculationPayTime(configs, s.date, (int)dc_report_cash_flow_log_data_type_enum.销售数量, task_data.platform_type, task_data.web_site)
                }));

                // 模拟平台费
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = (int)dc_report_cash_flow_log_data_type_enum.平台费用,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val * task_data.sale_unit_price_cny * task_data.platform_fee.Value,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = report_cash_flow_dao.CalculationPayTime(configs, s.date, (int)dc_report_cash_flow_log_data_type_enum.销售数量, task_data.platform_type, task_data.web_site)
                }));

                if (task_data.refund_ratio > 0)
                {
                    // 模拟退款
                    logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                    {
                        warehouse_code = task_data.warehouse_code,
                        bailun_sku = task_data.bailun_sku,
                        data_type = (int)dc_report_cash_flow_log_data_type_enum.退款,
                        is_delete = 0,
                        item = "",
                        item_no = $"虚拟退款 - {task_data.id}-{s.date_str}-{index}",
                        no = $"虚拟退款 - {task_data.id}-{s.date_str}-{index}",
                        occur_time = s.date.AddDays(7),
                        pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                        remarks = "模拟跑单  - 根据指定退款率，假定付款后7天退款",
                        task_id = task_data.id,
                        platform_type = task_data.platform_type,
                        val = 0 - (s.val * task_data.sale_unit_price_cny * task_data.refund_ratio),
                        update_time = now,
                        web_site = task_data.web_site,
                        pay_time_year_month_no = "",
                        occur_time_year_month_no = "",
                        pay_time = s.date.AddDays(7)
                    }));
                }

                // 模拟 释放销售成本
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = (int)dc_report_cash_flow_log_data_type_enum.释放销售成本,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val * task_data.buy_unit_price_cny,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = report_cash_flow_dao.CalculationPayTime(configs, s.date, (int)dc_report_cash_flow_log_data_type_enum.销售金额, task_data.platform_type, task_data.web_site)
                }));

                if (!"FBA仓".Equals(task_data.warehouse_type, StringComparison.OrdinalIgnoreCase))
                {
                    task_data.tail_logistics_fee = ApiUtility.GetFilterLogisticsAssignLine(new Models.ApiDto.api_logistic_query_input_dto
                    {
                        endCountries = task_data.web_site,
                        high = task_data.high,
                        LineCodes = task_data.tail_logistics_code,
                        Long = task_data.@long,
                        platform = task_data.platform_type,
                        Site = task_data.web_site,
                        startPoint = "国内仓".Equals(task_data.warehouse_type, StringComparison.OrdinalIgnoreCase) ? "1" : "2",
                        warehouseNo = task_data.warehouse_code,
                        weightKg = task_data.weight_kg,
                        width = task_data.width
                    }).TotalPrices;

                    task_data.error_stack_trace = "模拟计算尾程费";
                    conn.Update(task_data);
                    var index = 0;
                    // 模拟尾程费
                    foreach (var s in turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量))
                    {
                        var temp_item = new dc_report_cash_flow_forecast_task_log()
                        {
                            warehouse_code = task_data.warehouse_code,
                            bailun_sku = task_data.bailun_sku,
                            data_type = (int)dc_report_cash_flow_log_data_type_enum.尾程费用,
                            is_delete = 0,
                            item = "",
                            item_no = $"虚拟尾程费用 - {task_data.id}-{s.date_str}-{index}",
                            no = $"虚拟尾程费用 - {task_data.id}-{s.date_str}-{index}",
                            occur_time = s.date,
                            pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                            remarks = "模拟跑单",
                            task_id = task_data.id,
                            platform_type = task_data.platform_type,
                            val = 0 - (s.val * task_data.tail_logistics_fee.Value),
                            update_time = now,
                            web_site = task_data.web_site,
                            pay_time_year_month_no = "",
                            occur_time_year_month_no = ""
                        };
                        string remarks = null;
                        temp_item.pay_time = report_cash_flow_dao.CalculationLogisticsPayTime(logisticsList, logistics_company_list, s.date, task_data.tail_logistics_code, out remarks);
                        temp_item.remarks = remarks;
                        index++;
                        logs.Add(temp_item);
                    }

                }

                task_data.error_stack_trace = "模拟采购";
                conn.Update(task_data);
                // 模拟下采购单
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.最终采购数量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = index == 0 ? (int)dc_report_cash_flow_log_data_type_enum.新增采购费用_首单 : (int)dc_report_cash_flow_log_data_type_enum.新增采购费用,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟采购单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟采购单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = 0 - (s.val * task_data.buy_unit_price_cny),
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = (task_data.buy_pay_type == 2 ? s.date.AddDays(task_data.buy_pay_days + task_data.delivery) : s.date)
                }));


                if (!"国内仓".Equals(task_data.warehouse_type, StringComparison.OrdinalIgnoreCase))
                {
                    task_data.transfer_logistics_fee = ApiUtility.GetFilterLogisticsAssignLine(new Models.ApiDto.api_logistic_query_input_dto
                    {
                        endCountries = task_data.web_site,
                        high = task_data.high,
                        LineCodes = task_data.transfer_logistics_code,
                        Long = task_data.@long,
                        platform = task_data.platform_type,
                        Site = task_data.web_site,
                        startPoint = "3",
                        warehouseNo = task_data.warehouse_code,
                        weightKg = task_data.weight_kg,
                        width = task_data.width
                    }).TotalPrices;

                    task_data.error_stack_trace = "模拟下调拨单";
                    conn.Update(task_data);
                    var index = 0;
                    // 模拟头程费
                    foreach (var s in turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.调拨下单))
                    {
                        var temp_item = new dc_report_cash_flow_forecast_task_log()
                        {
                            warehouse_code = task_data.warehouse_code,
                            bailun_sku = task_data.bailun_sku,
                            data_type = (index == 0 ? (int)dc_report_cash_flow_log_data_type_enum.新增头程费用_首单 : (int)dc_report_cash_flow_log_data_type_enum.新增头程费用),
                            is_delete = 0,
                            item = "",
                            item_no = $"虚拟头程费用 - {task_data.id}-{s.date_str}-{index}",
                            no = $"虚拟头程费用 - {task_data.id}-{s.date_str}-{index}",
                            occur_time = s.date,
                            pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                            remarks = "模拟跑单",
                            task_id = task_data.id,
                            platform_type = task_data.platform_type,
                            val = 0 - (ApiUtility.GetFilterLogisticsAssignLine(new Models.ApiDto.api_logistic_query_input_dto
                            {
                                endCountries = task_data.web_site,
                                high = task_data.high,
                                LineCodes = task_data.transfer_logistics_code,
                                Long = task_data.@long,
                                platform = task_data.platform_type,
                                Site = task_data.web_site,
                                startPoint = "3",
                                warehouseNo = task_data.warehouse_code,
                                weightKg = s.val * task_data.weight_kg,
                                width = task_data.width
                            }).TotalPrices),
                            update_time = now,
                            web_site = task_data.web_site,
                            pay_time_year_month_no = "",
                            occur_time_year_month_no = ""
                        };
                        string remarks = null;
                        temp_item.pay_time = report_cash_flow_dao.CalculationLogisticsPayTime(logisticsList, logistics_company_list, s.date, task_data.transfer_logistics_code, out remarks);
                        temp_item.remarks = remarks;
                        index++;
                        logs.Add(temp_item);
                    }
                }

                if (logs != null && logs.Count >= 1)
                {
                    foreach (var item in logs)
                    {
                        conn.Insert(item);
                    }
                }

                task_data.error_stack_trace = "计算完成";
                task_data.status = 2;
                task_data.error_message = "";
                task_data.error_stack_trace = "";
                conn.Update(task_data);
            }
            catch (Exception ex)
            {
                task_data.status = 3;
                task_data.step = "";
                task_data.error_message = ex.Message;
                task_data.error_stack_trace = ex.StackTrace;
                conn.Update(task_data);
            }
        }

        /// <summary>
        /// 预测周转模型
        /// </summary>
        public static List<invented_turnover_model> GetTurnoverModels(dc_report_cash_flow_forecast_task task_data, List<dc_report_cash_flow_forecast_task_sale> sale_list, dc_auto_config_stock_up_days stock_up_days, dc_auto_config_safe_inventory inventory_data)
        {
            // 最大预测日期，预测销量 - 供应商交期
            var delivery = task_data.delivery;
            if (task_data.warehouse_type != "国内仓")
            {
                delivery += task_data.transfer_days;
            }

            sale_list = sale_list.OrderBy(s => s.date).ToList();
            // 预测周转数据
            List<invented_turnover_model> turnover_list = new List<invented_turnover_model>();
            turnover_list.AddRange(sale_list.Select(s => new invented_turnover_model { date = s.date, val = s.val, type = (int)invented_turnover_model.type_enum.销量 }));
            // 下首单 (采购单)
            // 取最早的预测销量的一天
            var min_date_sale = sale_list.FirstOrDefault();
            var init_purchase_quantity = min_date_sale.val;
            // 考虑备货天数
            var stock_up_day_date = min_date_sale.date.AddDays(stock_up_days.stock_up_days - 1);
            if (stock_up_day_date.ToDayHome() > min_date_sale.date.ToDayHome())
            {
                // 加备货天数
                init_purchase_quantity += sale_list.Where(s => s.date > min_date_sale.date && s.date <= stock_up_day_date.ToDayEnd()).Sum(s => s.val);
            }
            // 安全库存
            var inventory_stock = (inventory_data.param * min_date_sale.val);
            // 考虑安全库存
            init_purchase_quantity += inventory_stock;
            // 考虑moq
            var final_purchase_quantity = Math.Max(init_purchase_quantity, task_data.moq);
            if (task_data.warehouse_type != "国内仓")
            {
                turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.调拨下单, date = min_date_sale.date.AddDays(task_data.delivery) });
            }
            turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.最终采购数量, date = min_date_sale.date.AddDays(0 - (delivery + 1)) });
            // 会在开卖的前一天到货
            turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.入库数量, date = min_date_sale.date.AddDays(-1) });
            turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.可用库存, date = min_date_sale.date.AddDays(-1) });


            var max_forecast_date = sale_list.Max(s => s.date).AddDays(-delivery);

            var this_time = min_date_sale.date;
            while (this_time.ToDayHome() <= max_forecast_date.ToDayHome())
            {
                // 先查询【昨天的可用库存 + 今日入库】
                var yesterday_data_str = this_time.AddDays(-1).ToString("yyyy-MM-dd");
                var today_data_str = this_time.ToString("yyyy-MM-dd");
                decimal yesterday_stock = (turnover_list.Where(s => s.date_str == yesterday_data_str && s.type == (int)invented_turnover_model.type_enum.可用库存).FirstOrDefault() ?? new invented_turnover_model()).val;
                yesterday_stock += (turnover_list.Where(s => s.date_str == today_data_str && s.type == (int)invented_turnover_model.type_enum.入库数量).FirstOrDefault() ?? new invented_turnover_model()).val;

                // 减去销量等于【今日的可用库存】
                var today_sale = (turnover_list.Where(s => s.date_str == today_data_str && s.type == (int)invented_turnover_model.type_enum.销量).FirstOrDefault() ?? new invented_turnover_model()).val;
                var today_stock = yesterday_stock - today_sale;
                turnover_list.Add(new invented_turnover_model { val = today_stock, type = (int)invented_turnover_model.type_enum.可用库存, date = this_time });

                // 供应链长度的累积销量 - 累积入库
                var date_sum_day = this_time.AddDays(delivery).ToDayEnd();
                var sales_sum = turnover_list.Where(s => s.date >= this_time && s.date <= date_sum_day && s.type == (int)invented_turnover_model.type_enum.销量).Sum(s => s.val)
                    - turnover_list.Where(s => s.date >= this_time && s.date <= date_sum_day && s.type == (int)invented_turnover_model.type_enum.入库数量).Sum(s => s.val);

                // 计算跟安全库存的差值
                var init_p = inventory_stock + sales_sum - today_stock;
                if (init_p > 0)
                {
                    //准备采购
                    // 1. 补齐安全库存
                    // 2. 考虑备货天数
                    var this_stock_up_day_date = this_time.AddDays(stock_up_days.stock_up_days - 1);
                    if (this_stock_up_day_date.ToDayHome() > this_time.ToDayHome())
                    {
                        // 加备货天数
                        init_p += sale_list.Where(s => s.date > this_time && s.date <= this_stock_up_day_date.ToDayEnd()).Sum(s => s.val);
                    }
                    // 3. 考虑moq
                    // 考虑moq
                    var final_p = Math.Max(init_p, task_data.moq);
                    turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.最终采购数量, date = this_time });
                    // 加一个交期 等于 到货天数
                    turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.入库数量, date = this_time.AddDays(delivery) });
                    if (task_data.warehouse_type != "国内仓")
                    {
                        turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.调拨下单, date = this_time.AddDays(task_data.delivery) });
                    }
                }

                this_time = this_time.AddDays(1);
            }

            return turnover_list;
        }

        /// <summary>
        /// 匹配备货天数模型
        /// </summary>
        /// <param name="up_days"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static dc_auto_config_stock_up_days MatchingUpDays(List<dc_auto_config_stock_up_days> up_days, string warehouse_code)
        {
            return (up_days.FirstOrDefault(s => s.warehouse_code == warehouse_code) ?? new dc_auto_config_stock_up_days()
            {
                warehouse_code = warehouse_code,
                stock_up_days = 1
            });
        }

        /// <summary>
        /// 匹配安全库存模型
        /// </summary>
        /// <param name="up_days"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static dc_auto_config_safe_inventory MatchingSafeInventorys(List<dc_auto_config_safe_inventory> datas, string warehouse_code)
        {
            return (datas.FirstOrDefault(s => s.warehouse_code == warehouse_code) ?? new dc_auto_config_safe_inventory()
            {
                warehouse_code = warehouse_code,
                param = 1
            });
        }



        #region 配置页面

        public static List<dc_report_cash_flow_forecast_task_dto> Page(dc_report_cash_flow_forecast_task_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_report_cash_flow_forecast_task_dto>();
            try
            {
                var sql = @"select t1.* from dc_report_cash_flow_forecast_task as t1 where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and t1.bailun_sku=" + $"'{m.bailun_sku}'";
                }


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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        public static void Save(dc_report_cash_flow_forecast_task_input_dto input_data)
        {
            var m = input_data.ToJson().ToObj<dc_report_cash_flow_forecast_task>();
            using (var conn = _connection)
            {
                conn.Open();
                using (var t = conn.BeginTransaction())
                {
                    m.update_time = DateTime.Now;
                    if (string.IsNullOrWhiteSpace(m.warehouse_code))
                    {
                        throw new Exception("请选择仓库");
                    }
                    if (m.refund_ratio > 1)
                    {
                        throw new Exception("退款率不可大于1");
                    }
                    var warehouse_data = _connection.QuerySingleOrDefault<dc_base_warehouse>(" select * from dc_base_warehouse where warehouse_code=@warehouse_code limit 1 ", new { warehouse_code = m.warehouse_code });
                    if (warehouse_data == null)
                    {
                        throw new Exception(" 数据异常 ， 仓库不存在 ");
                    }
                    m.warehouse_type = warehouse_data.hq_type;
                    var logisticsList = ApiUtility.RealTimeShipLogisticsList();
                    if ((!"国内仓".Equals(warehouse_data.hq_type, StringComparison.OrdinalIgnoreCase)))
                    {
                        if (string.IsNullOrWhiteSpace(m.transfer_logistics_code))
                        {
                            throw new Exception("请选择物头程物流商");
                        }
                        else
                        {
                            var logisticsData = logisticsList.SingleOrDefault(s => m.transfer_logistics_code.Equals(s.Line_Code));
                            if (logisticsData == null)
                            {
                                throw new Exception("系统异常 供应商不存在");
                            }
                            m.transfer_logistics_name = logisticsData.Line_Name;
                        }

                        if (m.transfer_days <= 0)
                        {
                            throw new Exception("调拨交期必填");
                        }
                    }
                    else
                    {
                        m.transfer_logistics_code = "";
                        m.transfer_logistics_name = "";
                    }

                    if ((!"FBA仓".Equals(warehouse_data.hq_type, StringComparison.OrdinalIgnoreCase)))
                    {
                        if (string.IsNullOrWhiteSpace(m.tail_logistics_code))
                        {
                            throw new Exception("请选择物尾程物流商");
                        }
                        else
                        {
                            var logisticsData = logisticsList.SingleOrDefault(s => m.tail_logistics_code.Equals(s.Line_Code));
                            if (logisticsData == null)
                            {
                                throw new Exception("系统异常 供应商不存在");
                            }
                            m.tail_logistics_name = logisticsData.Line_Name;
                        }
                    }
                    else
                    {
                        m.tail_logistics_name = "";
                        m.tail_logistics_code = "";
                    }


                    m.status = 0;
                    m.step = "待计算";
                    m.error_message = "";
                    m.error_stack_trace = "";
                    var id = 0;
                    if (m.id > 0)
                    {
                        id = m.id;
                        var result = conn.Update<dc_report_cash_flow_forecast_task>(m, t);
                        conn.Execute(" delete from dc_report_cash_flow_forecast_task_sale where task_id=@task_id ", new { task_id = id });
                    }
                    else
                    {
                        id = conn.Insert<dc_report_cash_flow_forecast_task>(m, t) ?? 0;
                    }

                    if (input_data.sales != null)
                    {
                        foreach (var item in input_data.sales)
                        {
                            conn.Insert(new dc_report_cash_flow_forecast_task_sale
                            {
                                date = item.date,
                                task_id = id,
                                val = item.val
                            });
                        }
                    }

                    t.Commit();
                }
            }

        }


        public static dc_report_cash_flow_forecast_task_input_dto GetById(int id)
        {
            var data = _connection.QueryFirstOrDefault<dc_report_cash_flow_forecast_task_input_dto>("select * from dc_report_cash_flow_forecast_task where id=" + id);
            data.sales = _connection.Query<dc_report_cash_flow_forecast_task_input_dto.sale_dto>(" select `date`,`val` from dc_report_cash_flow_forecast_task_sale where task_id=@task_id ", new { task_id = id }).ToList();
            if (data.sales != null && data.sales.Count >= 1)
            {
                data.btime = data.sales.Min(s => s.date).ToString("yyyy-MM-dd");
                data.etime = data.sales.Max(s => s.date).ToString("yyyy-MM-dd");
            }
            return data;
        }


        #endregion

        public static dc_report_cash_flow_forecast_task_info_dto GetInfo(report_cash_flow_view_forecast_task_search_dto search)
        {
            var info_data = new dc_report_cash_flow_forecast_task_info_dto() { views = new List<report_cash_flow_view_dto>() { }, turnovers = new List<dc_report_cash_flow_forecast_task_info_dto.turnover_dto> { } };
            info_data.views.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>() });
            info_data.views.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>() });
            info_data.views.Add(new report_cash_flow_view_dto { date_type = new List<int> { 3 }, date_type_str = "退款", remarks = "预计销售数量 * 销售单价 * 退款率", dates = new List<report_cash_flow_view_dto.date_dto>() });
            info_data.views.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>() });
            info_data.views.Add(new report_cash_flow_view_dto { date_type = new List<int> { 5 }, date_type_str = "平台费用", remarks = "根据平台、站点、产品分类 从利润系统查询的预估平台费率 * 销售数量 * 销售金额", dates = new List<report_cash_flow_view_dto.date_dto>() });

            info_data.views.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>() });
            //info_data.views.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>() });
            info_data.views.Add(new report_cash_flow_view_dto { date_type = new List<int> { 8 }, date_type_str = "尾程费用", remarks = "", dates = new List<report_cash_flow_view_dto.date_dto>() });
            info_data.views.Add(new report_cash_flow_view_dto { date_type = new List<int> { 9 }, date_type_str = "新增采购费用", remarks = "  ", dates = new List<report_cash_flow_view_dto.date_dto>() });
            info_data.views.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>() });
            info_data.views.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>() });
            info_data.views.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>() });
            info_data.views.Add(new report_cash_flow_view_dto { date_type = new List<int> { 2, 4, 3 }, date_type_str = "汇总利润", remarks = " （", dates = new List<report_cash_flow_view_dto.date_dto>() });
            info_data.views.Add(new report_cash_flow_view_dto { date_type = new List<int> { 2, 3, 5, 8, 9, 10, 11, 12, 13, 14 }, date_type_str = "现金流结余", remarks = "  ", dates = new List<report_cash_flow_view_dto.date_dto>() });

            info_data.turnovers.Add(new dc_report_cash_flow_forecast_task_info_dto.turnover_dto { types = new List<int> { (int)invented_turnover_model.type_enum.销量 }, item_title = "销量", dates = new List<dc_report_cash_flow_forecast_task_info_dto.turnover_dto.date_dto> { } });
            info_data.turnovers.Add(new dc_report_cash_flow_forecast_task_info_dto.turnover_dto { types = new List<int> { (int)invented_turnover_model.type_enum.入库数量 }, item_title = "入库", dates = new List<dc_report_cash_flow_forecast_task_info_dto.turnover_dto.date_dto> { } });
            info_data.turnovers.Add(new dc_report_cash_flow_forecast_task_info_dto.turnover_dto { types = new List<int> { (int)invented_turnover_model.type_enum.可用库存 }, item_title = "可用库存", dates = new List<dc_report_cash_flow_forecast_task_info_dto.turnover_dto.date_dto> { } });
            info_data.turnovers.Add(new dc_report_cash_flow_forecast_task_info_dto.turnover_dto { types = new List<int> { (int)invented_turnover_model.type_enum.采购数量 }, item_title = "采购数量", dates = new List<dc_report_cash_flow_forecast_task_info_dto.turnover_dto.date_dto> { } });


            var conn = _connection;
            var 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_forecast_task_log where task_id=@task_id   ";
            var 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_forecast_task_log where task_id=@task_id  ";
            var turnover_sql = " select * from dc_report_cash_flow_forecast_turnover where task_id=@task_id  ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("task_id", search.task_id);
            occur_sql += " GROUP BY data_type,year(occur_time),month(occur_time),day(occur_time) ";
            pay_sql += " GROUP BY data_type,year(pay_time),month(pay_time),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();
            var turnover_datas = conn.Query<dc_report_cash_flow_forecast_turnover>(turnover_sql, parameters, commandTimeout: 0).ToList();


            var min_o_time = occur_datas.Min(s => s.date);
            var min_p_time = pay_datas.Min(s => s.date);
            var max_o_time = occur_datas.Max(s => s.date);
            var max_p_time = pay_datas.Max(s => s.date);
            var btime = min_o_time < min_p_time ? min_o_time : min_p_time;
            var etime = max_o_time > max_p_time ? max_o_time : max_p_time;
            foreach (var itemData in info_data.views)
            {
                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 info_data.views)
                {
                    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)
                    });
                }
                foreach (var itemData in info_data.turnovers)
                {
                    itemData.dates.Add(new dc_report_cash_flow_forecast_task_info_dto.turnover_dto.date_dto
                    {
                        btime = bThisTime,
                        etime = eThisTime,
                        date_title = $"{bThisTime.Month}-{bThisTime.Day}",
                        val = turnover_datas.Where(s => s.date >= bThisTime && s.date <= eThisTime && itemData.types.Contains(s.type)).Sum(s => s.val),
                    });
                }
                thisTime = thisTime.AddDays(1);
            }


            return info_data;
        }

        public static dc_report_cash_flow_forecast_task_date_dto GetDate(int id)
        {
            var data1 = _connection.QueryFirstOrDefault<DateTime?>(" select min(occur_time) from dc_report_cash_flow_forecast_task_log where task_id=@task_id ", new { task_id = id });
            var data2 = _connection.QueryFirstOrDefault<DateTime?>(" select min(pay_time) from dc_report_cash_flow_forecast_task_log where task_id=@task_id ", new { task_id = id });
            var data3 = _connection.QueryFirstOrDefault<DateTime?>(" select max(occur_time) from dc_report_cash_flow_forecast_task_log where task_id=@task_id ", new { task_id = id });
            var data4 = _connection.QueryFirstOrDefault<DateTime?>(" select max(pay_time) from dc_report_cash_flow_forecast_task_log where task_id=@task_id ", new { task_id = id });
            return new dc_report_cash_flow_forecast_task_date_dto
            {
                btime = ((data1 < data2 ? data1 : data2) ?? DateTime.Now.AddDays(-7)).ToString("yyyy-MM-dd"),
                etime = ((data3 > data4 ? data3 : data4) ?? DateTime.Now.AddDays(7)).ToString("yyyy-MM-dd")
            };
        }

        public static List<dc_report_cash_flow_log_dto> Export(dc_report_cash_flow_log_export_forecast_task_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_forecast_task_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.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_forecast_task_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_forecast_task_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_forecast_task_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_forecast_task_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.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);
                }

            }

        }
    }
}
