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;
using System.Linq;

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"));
                }
                else if (now.Hour%6==0 && now.Minute == 59)
                {
                    Console.WriteLine("开始启动更新服务" + DateTime.Now);
                    Update(DateTime.Now);
                    Console.WriteLine("完成更新服务" + DateTime.Now);
                }

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

        /// <summary>
        /// 每月1日统计
        /// </summary>
        /// <param name="day"></param>
        public void Init(DateTime day)
        {
            var objOrder = new Models.Orders.dc_base_oms_order();
            var objFBA = new Models.Orders.dc_base_oms_order();
            var sql = "";

            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,
                manager_cost_meijiadian = 0,
                manager_cost_yangshan = 0,
                manager_cost_xinhuilan = 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
            };

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                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")}'";

                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")}'";
                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','CreditInsertion','FeeGalleryPlus','PrivateListing','SubscriptioneBayStores','SubscriptionSMBasic','SubscriptionSMBasicPro','SubtitleFee') 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 in ('TotalShipping','PartShipping') 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_meijiadian + m.manager_cost_yangshan + m.manager_cost_xinhuilan;

                //管理成本合计=直接管理成本+销售相关的管理成本
                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;
            }
            


            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                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,manager_cost_meijiadian,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,manager_cost_yangshan,manager_cost_xinhuilan) 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.manager_cost_meijiadian},'{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},{m.manager_cost_yangshan},{m.manager_cost_xinhuilan})";

                cn.Execute(sql);
            }
        }

        public void Update(DateTime day)
        {
            
            var list = new List<dc_month_sale_profit>();

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                list = cn.Query<dc_month_sale_profit>("select * from dc_month_sale_profit where month>'" + day.AddMonths(-3).ToString("yyyy-MM") + "'").AsList();
            }

            foreach (var m in list)
            {
                var start = DateTime.Parse(m.month + "-01");

                var objOrder = new Models.Orders.dc_base_oms_order();
                var objFBA = new Models.Orders.dc_base_oms_order();

                var sql = "";

                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    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>='{start.ToString("yyyy-MM-dd")}' and t1.shipping_time<'{start.AddMonths(1).ToString("yyyy-MM-dd")}'";

                    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>='{start.ToString("yyyy-MM-dd")}' and tb.create_time<'{start.AddMonths(1).ToString("yyyy-MM-dd")}'";
                    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.cost_sale += m.cost_sale_;

                    //平台费
                    m.pt_platformfee = objOrder.cost_platform_fee;
                    //FBA
                    m.pt_platformfee += (objFBA.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','CreditInsertion','FeeGalleryPlus','PrivateListing','SubscriptioneBayStores','SubscriptionSMBasic','SubscriptionSMBasicPro','SubtitleFee') and t1.bj_date>='{start.ToString("yyyy-MM-dd")}' and t1.bj_date<'{start.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;
                    m.pt_platform_logistics_fee += (m.pt_platform_logistics_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>='{start.ToString("yyyy-MM-dd")}' and t1.bj_date<'{start.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>='{start.ToString("yyyy-MM-dd")}' and t1.report_date<'{start.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);

                    m.pt_platform_ad_subscribe_fee += m.pt_platform_ad_subscribe_fee_;

                    //退款
                    sql = $@"select sum(t1.amount_refund_rmb) from dc_base_crm_refund t1 where t1.shipping_status in ('TotalShipping','PartShipping') and t1.is_deleted=0 and is_freeze=0 and t1.company_id=1 and t1.refund_time>='{start.ToString("yyyy-MM-dd")}' and t1.refund_time<'{start.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_head_cost += m.ls_head_cost_;

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

                //平台操作费
                //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_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_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.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_meijiadian + m.manager_cost_yangshan + m.manager_cost_xinhuilan;

                //管理成本合计=直接管理成本+销售相关的管理成本
                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;


                //更新前半年的
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    sql = $@"update dc_month_sale_profit set amount_sale=" + m.amount_sale + ",cost_sale=" + m.cost_sale + ",pt_platformfee=" + m.pt_platformfee + ",pt_ebay_postingfee=" + m.pt_ebay_postingfee + ",pt_paypal_fee=" + m.pt_paypal_fee + ",pt_platform_logistics_fee=" + m.pt_platform_logistics_fee + ",pt_platform_ad_subscribe_fee=" + m.pt_platform_ad_subscribe_fee + ",pt_refund_amount=" + m.pt_refund_amount + ",ls_head_cost=" + m.ls_head_cost + ",ls_tail_cost=" + m.ls_tail_cost + ",ls_platform_operation_fee=" + m.ls_platform_operation_fee + ",pt_count=" + m.pt_count + ",pt_incoming=" + m.pt_incoming + ",ls_count=" + m.ls_count + ",sale_fee_count=" + m.sale_fee_count + ",sale_profit=" + m.sale_profit + ",sale_profit_rate=" + m.sale_profit_rate + ",sale_manager_cost=" + m.sale_manager_cost + ",direct_manager_cost=" + m.direct_manager_cost + ",manager_cost_count=" + m.manager_cost_count + ",business_profit=" + m.business_profit + ",business_profit_rate=" + m.business_profit_rate + ",actual_profit=" + m.actual_profit + ",balance_of_profit=" + m.balance_of_profit + ",manager_cost_yangshan=" + m.manager_cost_yangshan + ",manager_cost_xinhuilan=" + m.manager_cost_xinhuilan + " where id=" + m.id;
                    cn.Execute(sql);
                }

            }
        }

        /// <summary>
        /// 新月销售利润统计报表  
        /// </summary>
        /// <param name="day">每月1日</param>        
        public void InitNew(DateTime day)
        {

            var objOrder = new List<Models.Orders.dc_base_oms_sku>();
            var objFBA = new List<Models.Orders.dc_base_oms_sku>();
            var sql = "";

            var m = new dc_month_sales_profit
            {
                amount_sales = 0,
                amount_sales_dianzi = 0,
                amount_sales_fuzhuang = 0,
                amount_sales_jiaju = 0,
                amount_sales_jingyou = 0,
                amount_sales_meirongmj = 0,

                actual_profit = 0,
                amount_withdraw = 0,
                fee_platform_and_refund = 0,
                fee_fba = 0,
                fee_finance = 0,
                fee_logistics_direct = 0,
                fee_logistics_first = 0,
                fee_logistics_storage = 0,
                fee_paycash_sales = 0,
                fee_platform = 0,
                fee_refund = 0,
                fee_sales_count = 0,
                fee_storage = 0,
                
                
                cost_fuzhuang = 0,
                cost = 0,
                cost_dianzi = 0,
                cost_jiaju = 0,
                cost_jingyou = 0,
                cost_meirongmj = 0,
                createtime = DateTime.Now,
                managercost_chengpincang = 0,
                managercost_count = 0,
                managecost_bl_gz = 0,
                managecost_bl_xg = 0,
                managecost_meijia = 0,
                managercost_yangshan = 0,
                month = day.ToString("yyyy-MM"),
                dividend = 0,
                incoming_non_operating = 0,
                incoming_other = 0,
                isedit = 0,
                lastupdatetime = DateTime.Now,
                lastupdateuserid = 0,
                lastupdateusername = "admin",
                profit_sales = 0,
                pay_non_operating = 0,
                rate_profit_sales = 0,
                profit = 0,
                profit_balance = 0,

                rate_profit=0,
                rate_profit_actual=0,
            };

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                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,t6.financecategoryname,((t2.total_fee-t2.shipping)*t2.wms_to_cny_exchange_rate*t1.quantity_shipped) as total_fee 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
							left join dc_base_sku t5 on t2.bailun_sku=t5.bailun_sku
							left join dc_base_sku_finance_category t6 on t6.bailuncategoryid=t5.bailun_category_id
                            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")}'
                  group by t6.financecategoryname    
                  ";

                objOrder = cn.Query<Models.Orders.dc_base_oms_sku>(sql, null, null, 6 * 60).AsList();

                sql = $@"select sum(amount_sales*seller_order_exchange_rate*bailun_sku_quantity_ordered) as amount_sales,sum(cost_product*bailun_sku_quantity_ordered) as cost_product,sum(cost_platform_fee*seller_order_exchange_rate*bailun_sku_quantity_ordered) as cost_platform_fee,sum(cost_first*bailun_sku_quantity_ordered) as cost_first,sum(cost_logistics*bailun_sku_quantity_ordered) as cost_logistics,sum(cost_fba_fee*seller_order_exchange_rate*bailun_sku_quantity_ordered) cost_fba_fee,t6.financecategoryname from dc_base_oms_order tb left join dc_base_sku t5 on tb.bailun_sku=t5.bailun_sku
                    left join dc_base_sku_finance_category t6 on t6.bailuncategoryid=t5.bailun_category_id
                    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")}'
                    group by t6.financecategoryname
                    ";
                objFBA = cn.Query<Models.Orders.dc_base_oms_sku>(sql, null, null, 6 * 60).AsList();

                decimal? obj = 0;

                //销售额=家居用品类+服装类+电子产品类+精油类+美容美甲类+其他
                m.amount_sales_dianzi = objOrder.Where(a => a.financecategoryname == "电子产品类")?.Sum(a => a.amount_sales)??0+ objFBA.Where(a => a.financecategoryname == "电子产品类")?.Sum(a => a.amount_sales) ?? 0;
                m.amount_sales_fuzhuang = objOrder.Where(a => a.financecategoryname == "服装类")?.Sum(a => a.amount_sales) ?? 0+ objFBA.Where(a => a.financecategoryname == "服装类")?.Sum(a => a.amount_sales) ?? 0;
                m.amount_sales_jiaju = objOrder.Where(a => a.financecategoryname == "家居用品类")?.Sum(a => a.amount_sales) ?? 0+ objFBA.Where(a => a.financecategoryname == "家居用品类")?.Sum(a => a.amount_sales) ?? 0;
                m.amount_sales_jingyou = objOrder.Where(a => a.financecategoryname == "精油类")?.Sum(a => a.amount_sales) ?? 0+ objFBA.Where(a => a.financecategoryname == "精油类")?.Sum(a => a.amount_sales) ?? 0;
                m.amount_sales_meirongmj = objOrder.Where(a => a.financecategoryname == "美容美甲类")?.Sum(a => a.amount_sales) ?? 0+ objFBA.Where(a => a.financecategoryname == "美容美甲类")?.Sum(a => a.amount_sales) ?? 0;

                m.amount_sales = m.amount_sales_dianzi + m.amount_sales_fuzhuang + m.amount_sales_jiaju + m.amount_sales_jingyou + m.amount_sales_meirongmj;
                m.amount_sales_other = objOrder.Sum(a => a.amount_sales) + objFBA.Sum(a=>a.amount_sales) - m.amount_sales;
                m.amount_sales += m.amount_sales_other;

                //产品成本=家居用品类+服装类+电子产品类+精油类+美容美甲类+其他类型成本
                m.cost_dianzi = objOrder.Where(a => a.financecategoryname == "电子产品类")?.Sum(a => a.cost_product) ?? 0+ objFBA.Where(a => a.financecategoryname == "电子产品类")?.Sum(a => a.cost_product) ?? 0;
                m.cost_fuzhuang = objOrder.Where(a => a.financecategoryname == "服装类")?.Sum(a => a.cost_product) ?? 0+ objFBA.Where(a => a.financecategoryname == "服装类")?.Sum(a => a.cost_product) ?? 0;
                m.cost_jiaju = objOrder.Where(a => a.financecategoryname == "家居用品类")?.Sum(a => a.cost_product) ?? 0+ objFBA.Where(a => a.financecategoryname == "家居用品类")?.Sum(a => a.cost_product) ?? 0;
                m.cost_jingyou = objOrder.Where(a => a.financecategoryname == "精油类")?.Sum(a => a.cost_product) ?? 0+ objFBA.Where(a => a.financecategoryname == "精油类")?.Sum(a => a.cost_product) ?? 0;
                m.cost_meirongmj = objOrder.Where(a => a.financecategoryname == "美容美甲类")?.Sum(a => a.cost_product) ?? 0+ objFBA.Where(a => a.financecategoryname == "美容美甲类")?.Sum(a => a.cost_product) ?? 0;

                m.cost = m.cost_dianzi + m.cost_fuzhuang + m.cost_jiaju + m.cost_jingyou + m.cost_meirongmj;
                m.cost_other = objOrder.Sum(a => a.cost_product) + objFBA.Sum(a => a.cost_product) - m.cost;
                m.cost += m.cost_other;

                //平台扣费及退款
                //平台费=平台费+paypal费+处理费+广告费+上架费
                m.fee_platform = objOrder.Sum(a => a.cost_platform_fee+a.cost_paypal_fee+a.cost_handle_platform) + objFBA.Sum(a => a.cost_platform_fee);

                //FBA费
                m.fee_fba = objFBA.Sum(a => a.cost_fba_fee);

                //平台退款
                sql = $@"select sum(t1.amount_refund_rmb) from dc_base_crm_refund t1 where t1.shipping_status in ('TotalShipping','PartShipping') 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.fee_refund = obj ?? 0;

                //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','CreditInsertion','FeeGalleryPlus','PrivateListing','SubscriptioneBayStores','SubscriptionSMBasic','SubscriptionSMBasicPro','SubtitleFee') 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.fee_platform = obj ?? 0;

                //广告费
                //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.fee_platform = 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.fee_platform += (obj ?? 0);

                //平台提现回款
                sql = "";


                //物流仓储费用 = 头程运输+直邮物流费+海外仓仓储及其他费用
                m.fee_logistics_first = objOrder.Sum(a => a.cost_first) + objFBA.Sum(a => a.cost_first);
                m.fee_logistics_direct = objOrder.Sum(a => a.cost_logistics);
                m.fee_storage = objOrder.Sum(a => a.total_fee);

                m.fee_logistics_storage = m.fee_logistics_first + m.fee_logistics_direct + m.fee_storage;

                
                //利息支出
                var cwurl = "http://cw.bailuntec.com/api/api/GetRepayPlanDetails?" + "BeginRepayTime=" + day.ToString("yyyy-MM-dd") + "&EndRepayTime=" + day.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd"); ;
                var listInterest = new Bailun.DC.Services.FinanceReportServices().ListInterestExpense(cwurl);
                //管理成本
                var list = new Bailun.DC.Services.FinanceReportServices().ListFinanceManageCost(day, day.AddMonths(1).AddDays(-1), "", "", "", 1);
                //手续费
                var listHandleFee = new Bailun.DC.Services.FinanceReportServices().ListFinanceHandleFee(day, day.AddMonths(1).AddDays(-1), 1);


                //付现销售费用
                m.fee_paycash_sales = list.Where(a => a.fee_super_type == "推广费" || a.fee_super_type == "物流费用")?.Sum(a => a.amount_rmb) ?? 0;

                //销售费用合计=平台扣费及退款+物流仓储费用+付现销售费用
                m.fee_sales_count = m.fee_platform_and_refund + m.fee_logistics_storage + m.fee_paycash_sales;

                //销售利润=销售额-产品成本-销售费用合计
                m.profit_sales = m.amount_sales - m.cost - m.fee_sales_count;

                //销售毛利率=销售利润/销售额
                m.rate_profit_sales = m.amount_sales > 0 ? m.profit_sales / m.amount_sales : 0;


                //管理成本=香港百伦+广州百伦+美容美甲店+杨杉+信荟蓝+成品仓
                m.managecost_bl_xg = list.Where(a => a.company_name.Contains("香港百伦"))?.Sum(a => a.amount_rmb)??0;
                m.managecost_bl_gz = list.Where(a => a.company_name.Contains("广州百伦"))?.Sum(a => a.amount_rmb) ?? 0+listInterest?.Sum(a=>a.RepayInterestRMB)??0+listHandleFee?.Sum(a=>a.pay_money_rMB)??0;
                m.managecost_meijia = list.Where(a => a.company_name.Contains("歌戈儿") || a.company_name.Contains("拉古娜") || a.company_name.Contains("迪致美容")||a.company_name.Contains("美甲生产仓"))?.Sum(a => a.amount_rmb) ?? 0;
                m.managercost_chengpincang = list.Where(a => a.company_name.Contains("广州电子服装仓"))?.Sum(a => a.amount_rmb) ?? 0;
                m.managercost_yangshan = list.Where(a => a.company_name.Contains("扬杉"))?.Sum(a => a.amount_rmb) ?? 0;
                m.managercost_xinhuilan = list.Where(a => a.company_name.Contains("信荟蓝"))?.Sum(a => a.amount_rmb) ?? 0;
                m.managercost_count = m.managecost_bl_gz + m.managecost_bl_xg + m.managecost_meijia + m.managercost_chengpincang + m.managercost_xinhuilan + m.managercost_yangshan;


                //财务费用=手续费
                m.fee_finance = listHandleFee?.Sum(a=>a.pay_money_rMB)??0;

                //营业利润=销售利润-管理成本合计-财务费用
                m.profit = m.profit_sales - m.managercost_count - m.fee_finance;

                //营业毛利率=营业利润/销售额
                m.rate_profit = m.amount_sales > 0 ? m.profit / m.amount_sales : 0;

                //实际利润=营业利润+其他收入+营业外收入-营业外支出
                m.actual_profit = m.profit + m.incoming_other + m.incoming_non_operating - m.pay_non_operating;

                //利润结余=实际利润-分红
                m.profit_balance = m.actual_profit - m.dividend;

                //净利率 = 利润结余/销售额
                m.rate_profit_actual = m.amount_sales > 0 ? m.profit_balance / m.amount_sales : 0;
            }



            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                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,manager_cost_meijiadian,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,manager_cost_yangshan,manager_cost_xinhuilan) 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.manager_cost_meijiadian},'{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},{m.manager_cost_yangshan},{m.manager_cost_xinhuilan})";

                cn.Execute(sql);
            }
        }
    
    }
}
