using AutoTurnOver.Models.Report;
using System;
using System.Collections.Generic;
using System.Text;
using AutoTurnOver.Utility;
using System.Linq;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 财务报表
    /// </summary>
    public class dc_report_finance_dao : connectionHelper
    {
        /// <summary>
        /// 计算出口退税
        /// </summary>
        public static void CalculationTax(DateTime bTime)
        {
            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = thisTime.ToDayHome();
                var e_this_time = thisTime.ToDayEnd();
                dc_report_finance data = new dc_report_finance()
                {
                    data_type = "收回出口退税款",
                    item_name = "收回出口退税款",
                    item_id = "收回出口退税款",
                    date = b_this_time,
                    val = 0,
                    update_date = DateTime.Now

                };

                data.val = _connection.QueryFirstOrDefault<decimal?>("select sum(cashier_paymoneyrmb) from dc_base_finance_cashier where type_name = '出口退税收款' and cashier_status = 1 and cashier_time>=@b_this_time and cashier_time<=@e_this_time ", new
                {
                    b_this_time = b_this_time,
                    e_this_time = e_this_time
                }) ?? 0M;

                data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_name=@item_name and date=@date ", new
                {
                    data_type = data.data_type,
                    item_name = data.item_name,
                    date = data.date
                });
                if (data.id > 0)
                {
                    _connection.Update(data);
                }
                else
                {
                    _connection.Insert(data);
                }

                thisTime = thisTime.AddDays(-1);
            }
        }

        /// <summary>
        /// 计算付现
        /// </summary>
        /// <param name="bTime"></param>
        public static void CalculationCashPayment(DateTime bTime)
        {
            var bodys = ApiUtility.GetFinanceBody().Select(s => s.Name).ToList();
            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = thisTime.ToDayHome();
                var e_this_time = thisTime.ToDayEnd();

                dc_report_finance data = new dc_report_finance()
                {
                    data_type = "采购付现流水",
                    item_name = "采购付现流水",
                    item_id = "采购付现流水",
                    date = b_this_time,
                    val = 0,
                    update_date = DateTime.Now
                };

                data.val =0 - _connection.QueryFirstOrDefault<decimal?>(@"select sum(cashier_paymoneyrmb) from dc_base_finance_cashier where cashier_status = 1 
and (type_name is null or type_name != '') 
and tradeb_bjectname not in @bodys
and ( tradeb_bjectname like '%公司%' or tradeb_bjectname like '%厂%' or tradeb_bjectname like '%经营%' or tradeb_bjectname like '%经销%' or tradeb_bjectname like '%商行%' )
and cashier_type = 1 
and cashier_time>=@b_this_time and cashier_time<=@e_this_time ", new
                {
                    b_this_time = b_this_time,
                    e_this_time = e_this_time,
                    bodys = bodys
                }) ?? 0M;

                data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_name=@item_name and date=@date ", new
                {
                    data_type = data.data_type,
                    item_name = data.item_name,
                    date = data.date
                });
                if (data.id > 0)
                {
                    _connection.Update(data);
                }
                else
                {
                    _connection.Insert(data);
                }



                thisTime = thisTime.AddDays(-1);
            }
        }

        /// <summary>
        /// 计算订单 品类明细
        /// </summary>
        /// <param name="bTime"></param>
        public static void CalculationOrder(DateTime bTime)
        {
            // 查询分类

            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = thisTime.ToDayHome();
                var e_this_time = thisTime.ToDayEnd();

                var group_datas = _connection.Query<oms_order_type_group_dto>(@"select
t2.product_type,
t2.product_type_desc,
sum(t1.bailun_sku_quantity_ordered) as 'bailun_sku_quantity_ordered',
sum(t1.cost_platform_fee * t1.seller_order_exchange_rate * t1.bailun_sku_quantity_ordered) as 'platform_fee',
sum(t1.cost_product * t1.bailun_sku_quantity_ordered) as 'cost_product',
sum(t1.cost_first * t1.bailun_sku_quantity_ordered) as 'cost_first',
sum(t1.profit_total * t1.bailun_sku_quantity_ordered) as 'profit_total'

from dc_base_oms_sku as t1 
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
where t1.paid_time>=@b_this_time and t1.paid_time<=@e_this_time 
and t1.bailun_order_status != 'Canceled'
        and t1.has_fba_s = 0
        and t1.has_delete = 0
        and t1.has_scalp = 0
        and t1.has_buyer_remark = 0
        and t1.has_platsku_remark = 0
GROUP BY t2.product_type,t2.product_type_desc", new
                {
                    b_this_time = b_this_time,
                    e_this_time = e_this_time
                }).ToList();

                if (group_datas != null)
                {
                    List<dc_report_finance> datas = new List<dc_report_finance>();
                    foreach (var item in group_datas)
                    {
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "销售数量",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = item.bailun_sku_quantity_ordered,
                            update_date = DateTime.Now
                        });
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "平台费",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = 0 - item.platform_fee,
                            update_date = DateTime.Now
                        });
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "销售成本",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = 0 - item.cost_product,
                            update_date = DateTime.Now
                        });

                        datas.Add(new dc_report_finance()
                        {
                            data_type = "释放头程费",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = 0 - item.cost_first,
                            update_date = DateTime.Now
                        });
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "销售利润",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = item.profit_total,
                            update_date = DateTime.Now
                        });
                    }

                    foreach (var data in datas)
                    {

                        data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_id=@item_id and date=@date ", new
                        {
                            data_type = data.data_type,
                            item_id = data.item_id,
                            date = data.date
                        });
                        if (data.id > 0)
                        {
                            _connection.Update(data);
                        }
                        else
                        {
                            _connection.Insert(data);
                        }
                    }



                }

                thisTime = thisTime.AddDays(-1);
            }
        }

        /// <summary>
        /// 计算订单 平台明细
        /// </summary>
        /// <param name="bTime"></param>
        public static void CalculationOrderPlatfrom(DateTime bTime)
        {
            // 查询分类

            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = thisTime.ToDayHome();
                var e_this_time = thisTime.ToDayEnd();

                var group_datas = _connection.Query<oms_order_platfrom_group_dto>(@"select
t1.platform_type,
sum(t1.amount_sales * t1.seller_order_exchange_rate * t1.bailun_sku_quantity_ordered) as 'amount_sales'
from dc_base_oms_sku as t1 
where t1.paid_time>=@b_this_time and t1.paid_time<=@e_this_time
and t1.bailun_order_status != 'Canceled'
        and t1.has_fba_s = 0
        and t1.has_delete = 0
        and t1.has_scalp = 0
        and t1.has_buyer_remark = 0
        and t1.has_platsku_remark = 0
GROUP BY t1.platform_type ", new
                {
                    b_this_time = b_this_time,
                    e_this_time = e_this_time
                }).ToList();

                if (group_datas != null)
                {
                    List<dc_report_finance> datas = new List<dc_report_finance>();
                    foreach (var item in group_datas)
                    {
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "销售收入",
                            item_name = item.platform_type ?? "未知",
                            item_id = item.platform_type ?? "未知",
                            date = b_this_time,
                            val = item.amount_sales,
                            update_date = DateTime.Now
                        });
                    }

                    foreach (var data in datas)
                    {

                        data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_id=@item_id and date=@date ", new
                        {
                            data_type = data.data_type,
                            item_id = data.item_id,
                            date = data.date
                        });
                        if (data.id > 0)
                        {
                            _connection.Update(data);
                        }
                        else
                        {
                            _connection.Insert(data);
                        }
                    }



                }

                thisTime = thisTime.AddDays(-1);
            }
        }

        /// <summary>
        /// 计算订单 退款数据
        /// </summary>
        /// <param name="bTime"></param>
        public static void CalculationRefund(DateTime bTime)
        {
            // 查询分类

            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = thisTime.ToDayHome();
                var e_this_time = thisTime.ToDayEnd();

                var group_datas = _connection.Query<oms_order_refund_group_dto>(@"select
t2.product_type,t2.product_type_desc,
sum(t1.amount_refund_rmb) as 'amount_refund_rmb',
sum(t1.bailun_sku_quantity_refund) as 'bailun_sku_quantity_refund'
from dc_base_crm_refund as t1 
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
where t1.refund_time>=@b_this_time and t1.refund_time<=@e_this_time
and t1.is_deleted =0

GROUP BY t2.product_type,t2.product_type_desc ", new
                {
                    b_this_time = b_this_time,
                    e_this_time = e_this_time
                }).ToList();

                if (group_datas != null)
                {
                    List<dc_report_finance> datas = new List<dc_report_finance>();
                    foreach (var item in group_datas)
                    {
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "退款数量",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = item.bailun_sku_quantity_refund,
                            update_date = DateTime.Now
                        });
                        datas.Add(new dc_report_finance()
                        {
                            data_type = "退款金额",
                            item_name = item.product_type_desc ?? "未知",
                            item_id = item.product_type.ToString(),
                            date = b_this_time,
                            val = 0- item.amount_refund_rmb,
                            update_date = DateTime.Now
                        });
                    }

                    foreach (var data in datas)
                    {

                        data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_id=@item_id and date=@date ", new
                        {
                            data_type = data.data_type,
                            item_id = data.item_id,
                            date = data.date
                        });
                        if (data.id > 0)
                        {
                            _connection.Update(data);
                        }
                        else
                        {
                            _connection.Insert(data);
                        }
                    }



                }

                thisTime = thisTime.AddDays(-1);
            }
        }


        /// <summary>
        /// 计算筹资
        /// </summary>
        public static void CalculationRaise(DateTime bTime)
        {
            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = DateTime.Parse(thisTime.ToString("yyyy-MM-01 00:00:00"));
                var e_this_time = thisTime.LastDayOfMonth().ToDayEnd();
                var datas = new List<dc_report_finance>();

                //当月-上月的金额
                var val = (_connection.QueryFirstOrDefault<decimal?>(@" select short_term_borrow from dc_balance_sheet 
where `statistical_time`=@b_this_time ", new
                {
                    b_this_time = b_this_time.LastDayOfMonth().ToString("yyyy-MM-dd")
                }) ?? 0M)
-
 (_connection.QueryFirstOrDefault<decimal?>(@" select short_term_borrow from dc_balance_sheet 
where `statistical_time`>=@b_this_time  ", new
 {
     b_this_time = b_this_time.AddMonths(-1).LastDayOfMonth().ToString("yyyy-MM-dd")
 }) ?? 0M)
;

                datas.Add(new dc_report_finance()
                {
                    data_type = "现金流入(出)",
                    item_name = "筹资现金流入",
                    item_id = "筹资现金流入",
                    date = b_this_time,
                    val = val > 0 ? val : 0,
                    update_date = DateTime.Now
                });
                datas.Add(new dc_report_finance()
                {
                    data_type = "还款现金流出",
                    item_name = "还款现金流出",
                    item_id = "还款现金流出",
                    date = b_this_time,
                    val = 0 - (val < 0 ? val : 0),
                    update_date = DateTime.Now
                });


                foreach (var data in datas)
                {

                    data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_name=@item_name and date=@date ", new
                    {
                        data_type = data.data_type,
                        item_name = data.item_name,
                        date = data.date
                    });
                    if (data.id > 0)
                    {
                        _connection.Update(data);
                    }
                    else
                    {
                        _connection.Insert(data);
                    }
                }


                thisTime = thisTime.AddMonths(-1);
            }
        }

        /// <summary>
        /// 计算其他收入
        /// </summary>
        public static void CalculationOtherIncome(DateTime bTime)
        {
            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = thisTime.ToDayHome();
                var e_this_time = thisTime.ToDayEnd();
                dc_report_finance data = new dc_report_finance()
                {
                    data_type = "其他收入",
                    item_name = "其他收入",
                    item_id = "其他收入",
                    date = b_this_time,
                    val = 0,
                    update_date = DateTime.Now

                };

                data.val = _connection.QueryFirstOrDefault<decimal?>("select other_incoming from dc_month_sale_profit where `month`=@month ", new
                {
                    month = b_this_time.ToString("yyyy-MM")
                }) ?? 0M;

                data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_name=@item_name and date=@date ", new
                {
                    data_type = data.data_type,
                    item_name = data.item_name,
                    date = data.date
                });
                if (data.id > 0)
                {
                    _connection.Update(data);
                }
                else
                {
                    _connection.Insert(data);
                }

                thisTime = thisTime.AddMonths(-1);
            }
        }

        /// <summary>
        /// 计算应收应付账款
        /// </summary>
        public static void CalculationAccounts(DateTime bTime)
        {
            var etime = DateTime.Now;
            var thisTime = etime;
            while (thisTime >= bTime)
            {
                var b_this_time = DateTime.Parse(thisTime.ToString("yyyy-MM-01 00:00:00"));
                var e_this_time = thisTime.LastDayOfMonth().ToDayEnd();
                var datas = new List<dc_report_finance>();


                datas.Add(new dc_report_finance()
                {
                    data_type = "账款余额",
                    item_name = "应收账款余额",
                    item_id = "应收账款余额",
                    date = b_this_time,
                    val = (_connection.QueryFirstOrDefault<decimal?>(@" select accounts_receivable from dc_balance_sheet 
where `statistical_time`>=@b_this_time  ", new
                    {
                        b_this_time = b_this_time.LastDayOfMonth().ToString("yyyy-MM-dd")
                    }) ?? 0M),
                    update_date = DateTime.Now
                });
                datas.Add(new dc_report_finance()
                {
                    data_type = "账款余额",
                    item_name = "应付账款余额",
                    item_id = "应付账款余额",
                    date = b_this_time,
                    val = (_connection.QueryFirstOrDefault<decimal?>(@" select accounts_payable from dc_balance_sheet 
where `statistical_time`>=@b_this_time  ", new
                    {
                        b_this_time = b_this_time.LastDayOfMonth().ToString("yyyy-MM-dd")
                    }) ?? 0M),
                    update_date = DateTime.Now
                });


                foreach (var data in datas)
                {

                    data.id = _connection.QueryFirstOrDefault<int>(" select id from dc_report_finance where data_type=@data_type and item_name=@item_name and date=@date ", new
                    {
                        data_type = data.data_type,
                        item_name = data.item_name,
                        date = data.date
                    });
                    if (data.id > 0)
                    {
                        _connection.Update(data);
                    }
                    else
                    {
                        _connection.Insert(data);
                    }
                }


                thisTime = thisTime.AddMonths(-1);
            }
        }

        public static List<report_stock_week_view_dto> ReportFinanceView(report_stock_week_view_search_dto search)
        {
            var btime = DateTime.Parse(DateTime.Now.ToString("yyyy-01-01"));
            var etime = DateTime.Parse(DateTime.Now.ToString("yyyy-12-31"));
            if (search.btime != null)
            {
                btime = search.btime.Value;
            }
            if (search.etime != null)
            {
                etime = search.etime.Value;
            }

            btime = DateTime.Parse(btime.ToString("yyyy-MM-01")).ToDayHome();
            etime = DateTime.Parse(etime.ToString("yyyy-12-31")).ToDayEnd();

            var datas = _connection.Query<dc_report_finance>(" select * from dc_report_finance where `date`>=@btime and `date` <=@etime ", new { btime, etime });

            List<report_stock_week_view_dto> r_datas = new List<report_stock_week_view_dto>();

            var datas_web_site_group = datas.GroupBy(s => s.data_type).ToList();

            foreach (var websiteGroupItem in datas_web_site_group)
            {

                foreach (var itemData in websiteGroupItem.GroupBy(v => new { v.item_id, v.item_name }))
                {
                    var itemWeekData = new report_stock_week_view_dto
                    {
                        web_site = websiteGroupItem.Key,
                        tag = itemData.Key.item_name,
                        weeks = new List<report_stock_week_view_dto.week> { }
                    };
                    var this_date = btime;
                    while (this_date <= etime)
                    {
                        var b_thie_date = DateTime.Parse(this_date.ToString("yyyy-MM-01")).ToDayHome();
                        var e_thie_date = b_thie_date.LastDayOfMonth().ToDayEnd();

                        var this_datas = itemData.Where(s => s.date >= b_thie_date && s.date <= e_thie_date).ToList();
                        itemWeekData.weeks.Add(new report_stock_week_view_dto.week
                        {
                            val = this_datas.Sum(s => s.val),
                            ratio = 0,
                            tag = this_date.ToString("yyyy-MM")
                        });

                        this_date = this_date.AddMonths(1);
                    }

                    r_datas.Add(itemWeekData);
                }

            }



            return r_datas;
        }

        public static List<string> GetMonthList(report_stock_week_view_search_dto search)
        {
            List<string> months = new List<string>();
            var btime = DateTime.Parse(DateTime.Now.ToString("yyyy-01-01"));
            var etime = DateTime.Parse(DateTime.Now.ToString("yyyy-12-31"));
            if (search.btime != null)
            {
                btime = search.btime.Value;
            }
            if (search.etime != null)
            {
                etime = search.etime.Value;
            }

            btime = DateTime.Parse(btime.ToString("yyyy-MM-01")).ToDayHome();
            etime = DateTime.Parse(etime.ToString("yyyy-12-31")).ToDayEnd();
            var this_date = btime;
            while (this_date <= etime)
            {
                months.Add($"{this_date.ToString("yyyy-MM")}");
                this_date = this_date.AddMonths(1);
            }

            return months;
        }
    }
}
