using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Dapper;
using Bailun.DC.Models;
using Bailun.DC.Common;
using System.Threading;
using Microsoft.Extensions.Hosting;
using System.Threading.Tasks;

namespace Bailun.DC.MonthSaleProfit
{
    public class Services : BackgroundService
    {

        private Timer _timer;

        protected override Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _timer = new Timer(DoWork, null, TimeSpan.Zero, TimeSpan.FromMinutes(1));
            return Task.CompletedTask;
        }

        private void DoWork(object state)
        {
            try
            {
                var now = DateTime.Now;

                if (now.Day==1 && now.Hour==0 && now.Minute==1)  //每个月的1日0点1分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(now.AddMonths(-1).ToShortDateString());

                    Init(start);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// 每月1日统计
        /// </summary>
        /// <param name="day"></param>
        public void Init(DateTime day)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var m = new dc_month_sale_profit {
                    actual_profit = 0,
                    amount_sale = 0,
                    balance_of_profit = 0,
                    business_profit = 0,
                    business_profit_rate = 0,
                    cost_sale = 0,
                    createtime = DateTime.Now,

                    direct_manager_cost = 0,
                    finance_fee = 0,
                    handlingcharges = 0,
                    lastupdatetime = DateTime.Now,
                    lastupdateuserid = 0,
                    lastupdateusername = "admin",

                    ls_count = 0,
                    ls_head_cost = 0,
                    ls_oversea_storage = 0,
                    ls_platform_operation_fee = 0,
                    ls_tail_cost=0,
                    manager_cost_count=0,
                    manager_cost_finish_wh=0,
                    manager_cost_gzbailun = 0,
                    manager_cost_hkbailun = 0,
                    month = day.ToString("yyyy-MM"),

                    nonbusiness_income = 0,
                    nonbusiness_pay = 0,
                    other_incoming = 0,
                    participation_in_profit = 0,
                    payfor_platform_related_fee=0,
                    pt_count = 0,
                    pt_ebay_postingfee=0,
                    pt_incoming=0,
                    pt_paypal_fee=0,
                    pt_platformfee=0,
                    pt_platform_ad_subscribe_fee=0,
                    pt_platform_logistics_fee=0,
                    pt_platform_storage_fee=0,
                    pt_refund_amount=0,
                    sale_fee_count=0,
                    sale_manager_cost=0,
                    sale_profit=0,
                    sale_profit_rate=0
                };

                var sql = $@"select sum(t2.amount_sales*t1.quantity_shipped*t2.seller_order_exchange_rate) as amount_sales,sum(t2.cost_product*t1.quantity_shipped) as cost_product,sum(t2.cost_platform_fee*(if(t2.platform_type='Ebay',t2.seller_other_exchange_rate,t2.seller_order_exchange_rate))*t1.quantity_shipped) as cost_platform_fee,sum(t2.cost_paypal_fee*t2.seller_order_exchange_rate*t1.quantity_shipped) as cost_paypal_fee,sum(t2.cost_first*t1.quantity_shipped) as cost_first,sum(t2.cost_logistics*t1.quantity_shipped) as cost_logistics,sum(t2.cost_handle_platform*t1.quantity_shipped) as cost_handle_platform from dc_base_oms_pick t1
                            join dc_base_oms_sku t2 on t1.bailun_order_id = t2.bailun_order_id  and t1.bailun_sku=t2.bailun_sku  and t2.bailun_order_status != 'Canceled' and t2.has_scalp = 0 and ((t2.platform_type!='FBA' and t2.bailun_order_status!='CantHandle') or t2.platform_type='FBA') and t2.has_scalp = 0 and t2.has_innersale = 0  and t2.has_delete=0 and t2.company_id=1
                            where t1.has_delete=0 and t1.shipping_status = 'TotalShipping' and t1.company_id=1 and t1.shipping_time>='{day.ToString("yyyy-MM-dd")}' and t1.shipping_time<'{day.AddMonths(1).ToString("yyyy-MM-dd")}'";

                var objOrder = cn.QueryFirstOrDefault<Models.Orders.dc_base_oms_order>(sql, null, null, 6 * 60);

                sql = $@"select sum(amount_sales*seller_order_exchange_rate) as amount_sales,sum(cost_product) as cost_product,sum(cost_platform_fee*seller_order_exchange_rate) as cost_platform_fee,sum(cost_first) as cost_first,sum(cost_logistics) as cost_logistics,sum(cost_fba_fee*seller_order_exchange_rate) cost_fba_fee  from dc_base_oms_order tb where tb.company_id=1 and tb.bailun_order_status!='Canceled' and ((tb.platform_type!='FBA' and tb.bailun_order_status!='CantHandle') or tb.platform_type='FBA') and tb.has_scalp=0 and tb.has_innersale=0 and tb.bailun_interception_status in ('None','Failed') and tb.platform_type='FBA' and tb.create_time>='{day.ToString("yyyy-MM-dd")}' and tb.create_time<'{day.AddMonths(1).ToString("yyyy-MM-dd")}'";
                var objFBA = cn.QueryFirstOrDefault<Models.Orders.dc_base_oms_order>(sql, null, null, 6 * 60);

                decimal? obj = 0;

                //销售额
                m.amount_sale = objOrder.amount_sales;

                //FBA
                m.amount_sale += objFBA.amount_sales;

                //发货出库产品成本
                m.cost_sale = objOrder.cost_product;
                //FBA
                m.cost_sale += (objFBA.cost_product);

                //平台费
                m.pt_platformfee = objOrder.cost_platform_fee;
                //FBA
                m.pt_platformfee += (objOrder.cost_platform_fee);

                //ebay刊登费
                sql = $@"select sum(t1.exchange_rate*t1.gross_amount)
                        from dc_base_finance_ebay t1 
                        join dc_base_company_account t2 on t1.company_id=t2.company_id and t1.account_id=t2.account_id
                        where t1.account_entry_type in ('BuyItNowFee','CreditGalleryPlus','CreditInsertion','CreditSMBasicPro','ExtendedDurationFee','ExtendedDurationFeeCredit','FeeAuctionEndEarly','FeeBold','FeeFinalValueShipping','FeeGalleryPlus','FeeLargePicture','FeeSchedule','PrivateListing','SubscriptionSMBasic','SubscriptionSMBasicPro','SubscriptioneBayStores','SubtitleFee','SubtitleFeeCredit','CrediteBayStores') and t1.bj_date>='{day.ToString("yyyy-MM-dd")}' and t1.bj_date<'{day.AddMonths(1).ToString("yyyy-MM-dd")}' and t1.company_id=1";
                obj = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);
                m.pt_ebay_postingfee = obj ?? 0;

                //paypal费用
                m.pt_paypal_fee = objOrder.cost_paypal_fee;

                //平台物流费
                m.pt_platform_logistics_fee = objFBA.cost_fba_fee;


                //广告费
                //ebay
                sql = $@"select sum(t1.exchange_rate*t1.gross_amount) from dc_base_finance_ebay t1 
                        join dc_base_company_account t2 on t1.company_id=t2.company_id and t1.account_id=t2.account_id
                        where t1.account_entry_type in ('FeeAd') and t1.bj_date>='{day.ToString("yyyy-MM-dd")}' and t1.bj_date<'{day.AddMonths(1).ToString("yyyy-MM-dd")}' and t1.company_id=1";
                obj = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);
                m.pt_platform_ad_subscribe_fee = obj ?? 0;

                //amazon
                sql = $@"select sum(t1.cost*t1.exchange_rate) from dc_base_finance_amazon_ad_product t1 
                        join dc_base_company_account t2 on t1.account_id=t2.account_id
                        where t1.report_date>='{day.ToString("yyyy-MM-dd")}' and t1.report_date<'{day.AddMonths(1).ToString("yyyy-MM-dd")}' and t1.company_id=1";
                obj = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);
                m.pt_platform_ad_subscribe_fee += (obj ?? 0);

                //退款
                sql = $@"select sum(t1.amount_refund_rmb) from dc_base_crm_refund t1 where t1.shipping_status='TotalShipping' and t1.is_deleted=0 and is_freeze=0 and t1.company_id=1 and t1.refund_time>='{day.ToString("yyyy-MM-dd")}' and t1.refund_time<'{day.AddMonths(1).ToString("yyyy-MM-dd")}' and t1.shipping_status!='UnShipping'";
                obj = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);
                m.pt_refund_amount = obj ?? 0;


                //头程费
                m.ls_head_cost = objOrder.cost_first;
                //FBA
                m.ls_head_cost += (objFBA.cost_first);

                //尾程费
                m.ls_tail_cost = objOrder.cost_logistics;
                //FBA
                m.ls_tail_cost += (objFBA.cost_logistics);

                //平台操作费
                //cost_handle_platform
                m.ls_platform_operation_fee = objOrder.cost_handle_platform;


                //平台扣费及退款=平台费+ebay刊登费+paypal费用+平台物流费+广告及宣传费|订阅费+平台仓储费+退款
                m.pt_count = m.pt_platformfee + m.pt_ebay_postingfee + m.pt_paypal_fee + (m.pt_platform_logistics_fee + m.pt_platform_logistics_fee_) + (m.pt_platform_ad_subscribe_fee + m.pt_platform_ad_subscribe_fee_) + m.pt_platform_storage_fee + m.pt_refund_amount;

                //回款=销售额-平台扣费及退款
                m.pt_incoming = m.amount_sale - m.pt_count;

                //物流仓储费用=头程运输+尾程物流+海外仓的仓储费+平台操作费
                m.ls_count = (m.ls_head_cost + m.ls_head_cost_) + (m.ls_tail_cost + m.ls_tail_cost_) + m.ls_oversea_storage + m.ls_platform_operation_fee;

                //销售费用合计=平台扣费及退款+物流仓储费用
                m.sale_fee_count = m.pt_count + m.ls_count;

                //销售利润=销售额-成本-销售费用合计
                m.sale_profit = m.amount_sale - (m.cost_sale + m.cost_sale_) - m.sale_fee_count;

                //销售毛利率 = 销售利润/销售额
                m.sale_profit_rate = m.amount_sale>0?m.sale_profit / m.amount_sale:0;

                //销售相关的管理成本=处理费+直接支付的与销售平台相关费用
                m.sale_manager_cost = m.handlingcharges + m.payfor_platform_related_fee;

                //直接管理成本=管理成本香港百伦+管理成本广州百伦+管理成本成品仓+财务费用
                m.direct_manager_cost = m.manager_cost_hkbailun + m.manager_cost_gzbailun + m.manager_cost_finish_wh + m.finance_fee;

                //管理成本合计=直接管理成本+销售相关的管理成本
                m.manager_cost_count = m.direct_manager_cost + m.sale_manager_cost;

                //营业利润=销售利润-管理成本合计
                m.business_profit = m.sale_profit - m.manager_cost_count;

                //营业毛利率=营业利润/销售额
                m.business_profit_rate = m.amount_sale>0? m.business_profit / m.amount_sale:0;

                //实际利润=营业利润+其他收入+营业外收入-营业外支出
                m.actual_profit = m.business_profit + m.other_incoming + m.nonbusiness_income - m.nonbusiness_pay;

                //利润结余=实际利润-分红
                m.balance_of_profit = m.actual_profit - m.participation_in_profit;



                cn.Execute("delete from dc_month_sale_profit where month='" + m.month + "'");
                sql = $@"insert dc_month_sale_profit (actual_profit,amount_sale,balance_of_profit,business_profit,business_profit_rate,cost_sale,createtime,direct_manager_cost,finance_fee,handlingcharges,lastupdatetime,lastupdateuserid,lastupdateusername,ls_count,ls_head_cost,ls_oversea_storage,ls_platform_operation_fee,ls_tail_cost,manager_cost_count,manager_cost_finish_wh,manager_cost_gzbailun,manager_cost_hkbailun,month,nonbusiness_income,nonbusiness_pay,other_incoming,participation_in_profit,payfor_platform_related_fee,pt_count,pt_ebay_postingfee,pt_incoming,pt_paypal_fee,pt_platformfee,pt_platform_ad_subscribe_fee,pt_platform_logistics_fee,pt_platform_storage_fee,pt_refund_amount,sale_fee_count,sale_manager_cost,sale_profit,sale_profit_rate) values 
                        ({m.actual_profit},{m.amount_sale},{m.balance_of_profit},{m.business_profit},{m.business_profit_rate},{m.cost_sale},'{m.createtime.ToString("yyyy-MM-dd HH:mm:ss")}',{m.direct_manager_cost},{m.finance_fee},{m.handlingcharges},'{m.lastupdatetime.ToString("yyyy-MM-dd HH:mm:ss")}',{m.lastupdateuserid},'{m.lastupdateusername}',{m.ls_count},{m.ls_head_cost},{m.ls_oversea_storage},{m.ls_platform_operation_fee},{m.ls_tail_cost},{m.manager_cost_count},{m.manager_cost_finish_wh},{m.manager_cost_gzbailun},{m.manager_cost_hkbailun},'{m.month}',{m.nonbusiness_income},{m.nonbusiness_pay},{m.other_incoming},{m.participation_in_profit},{m.payfor_platform_related_fee},{m.pt_count},{m.pt_ebay_postingfee},{m.pt_incoming},{m.pt_paypal_fee},{m.pt_platformfee},{m.pt_platform_ad_subscribe_fee},{m.pt_platform_logistics_fee},{m.pt_platform_storage_fee},{m.pt_refund_amount},{m.sale_fee_count},{m.sale_manager_cost},{m.sale_profit},{m.sale_profit_rate})";

                cn.Execute(sql);
            }
        }
    }
}
