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 == 1 && now.Minute == 45)  //每个月的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"));
                    //InitNew(start);

                    Console.WriteLine("开始启动4K销售利润报表计算 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    InitNew4K(start);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else if(now.Day==5 && now.Hour == 1 && now.Minute == 45)
                {
                    Console.WriteLine("开始启动新销售利润报表计算 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(DateTime.Parse(now.ToString("yyyy-MM")+"-01").AddMonths(-1).ToShortDateString());
                    InitNew(start);
                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                //else if(now.Day==16 && now.Hour==0 && now.Minute==2)
                //{
                //    Console.WriteLine("重跑上个月的数据 启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                //    InitNew(DateTime.Parse(now.ToString("yyyy-MM")+"-01").AddMonths(-1));
                //    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 DC.Models.Orders.dc_base_oms_order();
            var objFBA = new DC.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<DC.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<DC.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 DC.Models.Orders.dc_base_oms_order();
                var objFBA = new DC.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<DC.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<DC.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<dc_month_sales_profit_orderdetail>();
            var objFBA = new List<dc_month_sales_profit_orderdetail>();
            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_ad = 0,
                fee_finance = 0,
                fee_logistics_direct = 0,
                fee_logistics_first = 0,
                fee_logistics_tail = 0,
                fee_logistics_storage = 0,
                fee_paycash_sales = 0,
                fee_platform = 0,
                fee_refund = 0,
                fee_sales_count = 0,
                fee_storage = 0,
                fee_storage_incidentals=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,
                managercost_dizhi = 0,
                managercost_meijiashengchan = 0,
                managercost_xinhuilan = 0,
                month = day.ToString("yyyy-MM"),
                dividend = 0,
                incoming_non_operating = 0,
                incoming_other = 0,
                incoming_refundtax = 0,
                incoming_gogirl = 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,
            };

            //先保存订单明细，为了下次更新时，订单数据能不变
            SaveOrderDetail(day);

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

                //利息支出
                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().ListFinanceFee(day, day.AddMonths(1).AddDays(-1), "", "", "", null,null);
                //手续费
                var listHandleFee = new Bailun.DC.Services.FinanceReportServices().ListFinanceHandleFee(day, day.AddMonths(1).AddDays(-1), 1);

                //自发货订单明细
                sql = $@"select sum(amount_sales*quantity_shipped*seller_order_exchange_rate) as amount_sales,sum(cost_product*quantity_shipped) as cost_product,sum(cost_platform_fee*(if(platform_type='Ebay',seller_other_exchange_rate,seller_order_exchange_rate))*quantity_shipped) as cost_platform_fee,sum(cost_paypal_fee*seller_order_exchange_rate*quantity_shipped) as cost_paypal_fee,sum(cost_first*quantity_shipped) as cost_first,sum(cost_logistics*quantity_shipped) as cost_logistics,sum(cost_handle_platform*quantity_shipped) as cost_handle_platform,if(financecategoryname is null,'',financecategoryname) as financecategoryname,((total_fee-shipping)*wms_to_cny_exchange_rate*quantity_shipped) as total_fee from dc_month_sales_profit_orderdetail where month='{day.ToString("yyyy-MM")}' and platform_type!='FBA' group by financecategoryname";
                objOrder = cn.Query<dc_month_sales_profit_orderdetail>(sql).ToList();

                //FBA订单明细
                sql = $@"select sum(amount_sales*quantity_shipped*seller_order_exchange_rate) as amount_sales,sum(cost_product*quantity_shipped) as cost_product,sum(cost_platform_fee*(if(platform_type='Ebay',seller_other_exchange_rate,seller_order_exchange_rate))*quantity_shipped) as cost_platform_fee,sum(cost_paypal_fee*seller_order_exchange_rate*quantity_shipped) as cost_paypal_fee,sum(cost_first*quantity_shipped) as cost_first,sum(cost_logistics*quantity_shipped) as cost_logistics,sum(cost_handle_platform*quantity_shipped) as cost_handle_platform,if(financecategoryname is null,'',financecategoryname) as financecategoryname,sum((total_fee-shipping)*wms_to_cny_exchange_rate*quantity_shipped) as total_fee,sum(cost_fba_fee*quantity_shipped*seller_order_exchange_rate) as cost_fba_fee from dc_month_sales_profit_orderdetail where month='{day.ToString("yyyy-MM")}' and platform_type='FBA' group by financecategoryname";
                objFBA = cn.Query<dc_month_sales_profit_orderdetail>(sql).ToList();

                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_other = ((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;
                
                //产品成本=家居用品类+服装类+电子产品类+精油类+美容美甲类+其他类型成本
                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_other = (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;
                
                
                //平台费=平台费+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);
                m.fee_refund = obj ?? 0;

                //销售额需要减去退款 Add by Allan at 20210102
                m.amount_sales -= m.fee_refund;

                //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);
                m.fee_ad += (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);
                m.fee_ad += (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);
                m.fee_ad += (obj ?? 0);

                //平台提现回款
                //m.amount_withdraw = WithDrawBalance(day, day.AddMonths(1).AddDays(-1));

                //平台费用 = 平台费+FBA费+(广告费+上架费)
                m.fee_platform_and_refund = m.fee_platform + m.fee_fba+m.fee_ad;

                sql = $@"select sum(t2.cost_logistics*t1.quantity_shipped) as cost_logistics,t3.hq_type 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') and t2.has_scalp =0  and t2.has_delete=0 and t2.company_id=1
join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                            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 t3.hq_type";
                var objLogistics = cn.Query<Models.mLogisticsFee>(sql,null,null,true,6*60).ToList();


                //物流运输费用 = 头程运输+直邮物流费（国内发货）+尾程物流费（国外发货）+海外仓仓储+海外仓杂费
                //头程运输费改为从调拨系统读取，预估费用，交运确认时间
                //var objFirst = cn.QueryFirstOrDefault<decimal>($"select sum(estimatedcost) from dc_base_transfer_info where isdeleted=0 and stateid>1 and deliverytime >='{day.ToString("yyyy-MM-dd")}' and deliverytime<'{day.AddMonths(1).ToString("yyyy-MM-dd")}'");
                m.fee_logistics_first = objOrder.Sum(a => a.cost_first) + objFBA.Sum(a => a.cost_first);//objFirst; //
                m.fee_logistics_direct = objLogistics.Where(a => a.hq_type == "国内仓")?.Sum(b => b.cost_logistics) ?? 0; //objOrder.Sum(a => a.cost_logistics??0);
                m.fee_logistics_tail = objLogistics.Where(a => a.hq_type != "国内仓")?.Sum(b => b.cost_logistics) ?? 0;  
                m.fee_storage = objOrder.Sum(a => a.total_fee);

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


                //付现销售费用
                m.managecost_bl_xg = (list.Where(a => a.company_name.Contains("香港百伦") && a.type_name != "物流费" && a.type_name!= "销售费用/物流费")?.Sum(a => a.amount_rmb) ?? 0);
                m.fee_paycash_sales = 0; //list.Where(a => (a.fee_super_type == "推广费") && !a.company_name.Contains("香港百伦") && !a.company_name.Contains("四千里") && !a.company_name.Contains("YOUNGTOP INTERNATIONAL LIMITED") && !a.company_name.Contains("miles") && !a.company_name.Contains("拉古娜") && !a.company_name.Contains("哈倪蔓"))?.Sum(a => a.amount_rmb) ?? 0;
                //付现销售费用加上香港百伦的管理成本金额 
                //Add by Allan at 20210102
                m.fee_paycash_sales += m.managecost_bl_xg;

                //销售费用合计=平台扣费及退款+物流仓储费用+付现销售费用
                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_gz = (list.Where(a => a.company_name==("广州百伦供应链科技有限公司") && (a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);
                m.managecost_meijia = (list.Where(a => a.company_name.Contains("歌戈儿") && (a.type_name != "销售收入" && a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);
                m.managercost_chengpincang = (list.Where(a => (a.company_name.Contains("广州电子服装仓") || a.company_name.Contains("阳山")) && (a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);
                m.managercost_yangshan = (list.Where(a => a.company_name.Contains("扬杉") && (a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);
                m.managercost_xinhuilan = (list.Where(a => a.company_name.Contains("信荟蓝") && (a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);
                m.managercost_meijiashengchan = (list.Where(a => a.company_name.Contains("美甲生产仓") && (a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);
                m.managercost_dizhi = (list.Where(a => a.company_name.Contains("迪致美容") && (a.type_name != "出口退税款"))?.Sum(a => a.amount_rmb) ?? 0);

                m.managercost_count = m.managecost_bl_gz + m.managecost_meijia + m.managercost_chengpincang + m.managercost_xinhuilan + m.managercost_yangshan+m.managercost_dizhi;

                //财务费用=利息费用
                m.fee_finance = (listInterest?.Sum(a => a.RepayInterestRMB) ?? 0)+(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.incoming_refundtax = list.Where(a => a.type_name == "出口退税款")?.Sum(a => -a.amount_rmb)??0;

                //歌戈尔收入
                m.incoming_gogirl = list.Where(a => a.company_name.Contains("歌戈儿") && a.type_name == "销售收入")?.Sum(a => -a.amount_rmb) ?? 0;

                //其他收入=出口退税+歌戈尔收入
                m.incoming_other = m.incoming_refundtax + m.incoming_gogirl;

                //实际利润=营业利润+其他收入+营业外收入-营业外支出
                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_sales_profit where isedit=0 and month='" + m.month + "'");
                sql = $@"insert dc_month_sales_profit (month,amount_sales,amount_sales_jingyou,amount_sales_dianzi,amount_sales_jiaju,amount_sales_meirongmj,amount_sales_fuzhuang,amount_sales_other,cost,cost_jingyou,cost_dianzi,cost_jiaju,cost_meirongmj,cost_fuzhuang,cost_other,fee_platform_and_refund,fee_platform,fee_fba,fee_refund,amount_withdraw,fee_logistics_storage,fee_logistics_first,fee_logistics_direct,fee_logistics_tail,fee_storage,fee_paycash_sales,fee_sales_count,profit_sales,rate_profit_sales,managecost_bl_xg,managecost_bl_gz,managecost_meijia,managercost_yangshan,managercost_chengpincang,managercost_xinhuilan,managercost_count,fee_finance,profit,rate_profit,incoming_other,incoming_non_operating,pay_non_operating,actual_profit,dividend,profit_balance,rate_profit_actual,createtime,lastupdateuserid,lastupdateusername,lastupdatetime,isedit,managercost_dizhi,managercost_meijiashengchan,fee_ad,fee_storage_incidentals,incoming_refundtax,incoming_gogirl) values 
                        ('{m.month}',{m.amount_sales},{m.amount_sales_jingyou},{m.amount_sales_dianzi},{m.amount_sales_jiaju},{m.amount_sales_meirongmj},{m.amount_sales_fuzhuang},{m.amount_sales_other},{m.cost},{m.cost_jingyou},{m.cost_dianzi},{m.cost_jiaju},{m.cost_meirongmj},{m.cost_fuzhuang},{m.cost_other},{m.fee_platform_and_refund},{m.fee_platform},{m.fee_fba},{m.fee_refund},{m.amount_withdraw},{m.fee_logistics_storage},{m.fee_logistics_first},{m.fee_logistics_direct},{m.fee_logistics_tail},{m.fee_storage},{m.fee_paycash_sales},{m.fee_sales_count},{m.profit_sales},{m.rate_profit_sales},{m.managecost_bl_xg},{m.managecost_bl_gz},{m.managecost_meijia},{m.managercost_yangshan},{m.managercost_chengpincang},{m.managercost_xinhuilan},{m.managercost_count},{m.fee_finance},{m.profit},{m.rate_profit},{m.incoming_other},{m.incoming_non_operating},{m.pay_non_operating},{m.actual_profit},{m.dividend},{m.profit_balance},{m.rate_profit_actual},'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{m.lastupdateuserid},'{m.lastupdateusername}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',0,{m.managercost_dizhi},{m.managercost_meijiashengchan},{m.fee_ad},{m.fee_storage_incidentals},{m.incoming_refundtax},{m.incoming_gogirl})";

                cn.Execute(sql);
            }
        }
        
        public void InitNew4K(DateTime day)
        {
            var m = new dc_month_sales_profit_4k
            {
                cost_operating_assets_impairment = 0,
                loss_ondisposal_noncurrent_assets = 0,

                cost_operating = 0,
                cost_operating_dspchannel = 0,
                cost_operating_dspsign = 0,
                cost_operating_exchange = 0,
                cost_operating_fair_value_change = 0,
                cost_operating_finance = 0,
                cost_operating_investment = 0,
                cost_operating_investment_in = 0,
                cost_operating_it = 0,
                cost_operating_manager = 0,
                cost_operating_mh = 0,
                cost_operating_sales = 0,
                cost_operating_taxes = 0,
                cost_operating_tp = 0,
                cost_out_operating = 0,

                createtime = DateTime.Now,
                fee_income_tax = 0,

                incoming_operating_mh = 0,
                incoming_operating = 0,
                incoming_operating_dsp = 0,
                incoming_operating_fansservice = 0,
                incoming_operating_other = 0,
                incoming_operating_saas = 0,
                incoming_operating_test = 0,
                incoming_operating_tp = 0,
                incoming_out_operating = 0,

                lastupdatetime = DateTime.Now,
                lastupdateuserid = 0,
                lastupdateusername = "admin",

                profit = 0,
                profit_operating = 0,
                profit_retained = 0,

                month = day.ToString("yyyy-MM"),
                isedit = 0,
                
            };


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

                listFinance = cn.Query<dc_base_finance_fee>($"select * from dc_base_finance_fee where cost_status=4 and company_name in ('（英国）4kmiles technologies ltd','香港四千里数据科技有限公司','YOUNGTOP INTERNATIONAL LIMITED','深圳市四千里数据科技有限公司','4Kmiles tec Limited','广州四千里数据科技有限公司') and pay_time>='{day.ToString("yyyy-MM-dd")}' and pay_time<'{day.AddMonths(1).ToString("yyyy-MM-dd")}'").ToList();
            }

            //收款
            var objIncoming = listFinance.Where(a => a.cost_form == 2);

            //付款
            var objPay = listFinance.Where(a => a.cost_form == 1 || (a.cost_form == 3 && a.is_lend == 2));

            m.incoming_operating_dsp = objIncoming.Where(a => a.project_type!=null && a.project_type.ToUpper() == "DSP")?.Sum(a => a.amount_rmb) ?? 0;
            m.incoming_operating_saas = objIncoming.Where(a => a.project_type != null && a.project_type.ToUpper() == "SAAS")?.Sum(a => a.amount_rmb) ?? 0;
            m.incoming_operating_test = objIncoming.Where(a => a.project_type != null && a.project_type.ToUpper() == "测评")?.Sum(a => a.amount_rmb) ?? 0;
            m.incoming_operating_other = objIncoming.Where(a => a.project_type != null && a.project_type=="其他"||string.IsNullOrEmpty(a.project_type))?.Sum(a => a.amount_rmb) ?? 0;


            //营业收入=SAAS套餐收入+测评收入+DSP服务收入+MH代理收入+代运营收入+粉丝系统服务收入+其他收入
            m.incoming_operating = m.incoming_operating_saas + m.incoming_operating_test + m.incoming_operating_dsp + m.incoming_operating_mh + m.incoming_operating_tp + m.incoming_operating_fansservice + m.incoming_operating_other;

            //营业成本=IT人工成本+DSP渠道成本+DSP签约成本+代运营成本+MH客户成本
            m.cost_operating = m.cost_operating_it + m.cost_operating_dspchannel + m.cost_operating_dspsign + m.cost_operating_tp + m.cost_operating_mh;


            //销售费用
            m.cost_operating_sales = objPay.Where(a => a.type_name == "差旅费" || a.type_name == "招待费" || a.type_name == "推广费")?.Sum(a => a.amount_rmb) ?? 0;

            //管理费用
            m.cost_operating_manager = objPay.Where(a => a.type_name == "服务费" || a.type_name == "福利费" || a.type_name == "人工支出" || a.type_name== "办公支出" || a.type_name== "其他"||a.type_name== "税费"||a.type_name== "车辆使用费")?.Sum(a => a.amount_rmb) ?? 0;

            //财务费用
            m.cost_operating_finance = objPay.Where(a => a.type_name == "手续费")?.Sum(a => a.amount_rmb) ?? 0;


            //营业利润=营业收入-营业成本-营业税金及附加-销售费用-管理费用-财务费用
            m.profit_operating = m.incoming_operating - m.cost_operating - m.cost_operating_taxes - m.cost_operating_sales - m.cost_operating_manager - m.cost_operating_finance;

            //利润总额=营业利润+营业外利润-营业务支出
            m.profit = m.profit_operating + m.incoming_out_operating - m.cost_out_operating;

            //净利润=利润总额-所得税费用
            m.profit_retained = m.profit - m.fee_income_tax;

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

                cn.Execute("delete from dc_month_sales_profit_4k where isedit=0 and month='" + m.month + "'");
                var sql = $@"insert dc_month_sales_profit_4k (cost_operating_assets_impairment,loss_ondisposal_noncurrent_assets,cost_operating,cost_operating_dspchannel,cost_operating_dspsign,cost_operating_exchange,cost_operating_fair_value_change,cost_operating_finance,cost_operating_investment,cost_operating_investment_in,cost_operating_it,cost_operating_manager,cost_operating_mh,cost_operating_sales,cost_operating_taxes,cost_operating_tp,cost_out_operating,createtime,fee_income_tax,incoming_operating_mh,incoming_operating,incoming_operating_dsp,incoming_operating_fansservice,incoming_operating_other,incoming_operating_saas,incoming_operating_test,incoming_operating_tp,incoming_out_operating,lastupdatetime,lastupdateuserid,lastupdateusername,profit,profit_operating,profit_retained,month,isedit) values 
                        ({m.cost_operating_assets_impairment},{m.loss_ondisposal_noncurrent_assets},{m.cost_operating},{m.cost_operating_dspchannel},{m.cost_operating_dspsign},{m.cost_operating_exchange},{m.cost_operating_fair_value_change},{m.cost_operating_finance},{m.cost_operating_investment},{m.cost_operating_investment_in},{m.cost_operating_it},{m.cost_operating_manager},{m.cost_operating_mh},{m.cost_operating_sales},{m.cost_operating_taxes},{m.cost_operating_tp},{m.cost_out_operating},'{m.createtime.ToString("yyyy-MM-dd HH:mm:ss")}',{m.fee_income_tax},{m.incoming_operating_mh},{m.incoming_operating},{m.incoming_operating_dsp},{m.incoming_operating_fansservice},{m.incoming_operating_other},{m.incoming_operating_saas},{m.incoming_operating_test},{m.incoming_operating_tp},{m.incoming_out_operating},'{m.lastupdatetime.ToString("yyyy-MM-dd HH:mm:ss")}',{m.lastupdateuserid},'{m.lastupdateusername}',{m.profit},{m.profit_operating},{m.profit_retained},'{m.month}',{m.isedit})";

                cn.Execute(sql);
            }

        }

        /// <summary>
        /// 获取提现到账金额
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        private decimal WithDrawBalance(DateTime start,DateTime end)
        {
            var _url_withdraw = $@"http://cw.bailuntec.com/API/API/GetWithdrawSummaries?BeginConfirmDate={start.ToString("yyyy-MM-dd")}&EndConfirmDate={end.ToString("yyyy-MM-dd")}";

            var _url_exchange = $"http://oa.bailuntec.com/Api/ExchangeRate/GetMonthlyExchangeRateByDate?date={start.ToString("yyyy-MM")}&toCur=CNY&fromCur=";

            var result = Common.HttpHelper.NetHelper.Request(_url_withdraw);
            var json = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.response_WithDraw>(result);

            var listExchange = new List<Tuple<string, decimal>>();

            decimal amount = 0;

            foreach(var item in json.list)
            {
                var objRate = listExchange.Where(a => a.Item1.ToLower() == item.Currency.ToLower()).FirstOrDefault();
                if(objRate==null)
                {
                    var result_Exchange = Common.HttpHelper.NetHelper.Request(_url_exchange+item.Currency);
                    if(!string.IsNullOrEmpty(result_Exchange) && result_Exchange!="null")
                    {
                        var jsonExchange = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.response_Exchange>(result_Exchange);
                        objRate = new Tuple<string, decimal>(item.Currency, jsonExchange.rate);
                        listExchange.Add(objRate);
                    }
                    else
                    {
                        objRate = new Tuple<string, decimal>("", 1);
                    }
                }


                amount += item.DaozhangMoney * objRate.Item2;
            }

            return amount;

        }
    
        private void SaveOrderDetail(DateTime day)
        {
            var objOrder = new List<dc_month_sales_profit_orderdetail>();
            var objFBA = new List<dc_month_sales_profit_orderdetail>();

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

                var checkSaved = cn.QueryFirstOrDefault<dc_month_sales_profit>("select * from dc_month_sales_profit where isedit=0 and month='" + day.ToString("yyyy-MM") + "'");
                if(checkSaved!=null)
                {
                    return;
                }

                var sql = $@"select t1.id as pickid,t2.id as orderskuid,t2.bailun_sku,t2.amount_sales,t2.cost_product,t2.cost_platform_fee,t2.cost_paypal_fee,t2.cost_first,t2.cost_logistics,t2.cost_handle_platform,t2.total_fee,t2.seller_order_exchange_rate,t2.seller_other_exchange_rate,t2.wms_to_cny_exchange_rate,t2.shipping,t1.quantity_shipped,t1.shipping_time,t2.platform_type,t2.cost_fba_fee,if(t6.financecategoryname is null,'',t6.financecategoryname) as financecategoryname 
                            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")}'   
                  ";

                objOrder = cn.Query<dc_month_sales_profit_orderdetail>(sql, null, null, true, 6 * 60).ToList();

                var page = 0;
                var pagesize = 3000;
                Console.WriteLine($"开始保存自发货SKU明细 {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");
                while (page==0||(page*pagesize)<objOrder.Count)
                {
                    var tempList = objOrder.Skip(page*pagesize).Take(pagesize).ToList();
                    var str = "insert dc_month_sales_profit_orderdetail (month,pickid,orderskuid,bailun_sku,amount_sales,cost_product,cost_platform_fee,cost_paypal_fee,cost_first,cost_logistics,cost_handle_platform,total_fee,shipping,seller_order_exchange_rate,seller_other_exchange_rate,wms_to_cny_exchange_rate,quantity_shipped,shipping_time,platform_type,cost_fba_fee,financecategoryname) values ";
                    foreach(var item in tempList)
                    {
                        str += $"('{day.ToString("yyyy-MM")}',{item.pickid},{item.orderskuid},'{item.bailun_sku}',{item.amount_sales},{item.cost_product},{item.cost_platform_fee},{item.cost_paypal_fee},{item.cost_first},{item.cost_logistics},{item.cost_handle_platform},{item.total_fee},{item.shipping},{item.seller_order_exchange_rate},{item.seller_other_exchange_rate},{item.wms_to_cny_exchange_rate},{item.quantity_shipped},'{item.shipping_time.ToString("yyyy-MM-dd HH:mm:ss")}','{item.platform_type}',{item.cost_fba_fee},'{item.financecategoryname}'),";
                    }

                    Console.WriteLine($"总记录数：{objOrder.Count}，第{(page + 1)}页，记录数：{tempList.Count} {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");
                    SaveData(str.Substring(0, str.Length - 1)); 
                    page++;
                }

                sql = $@"select 0 as pickid,tb.id as orderskuid,tb.bailun_sku,tb.amount_sales,tb.cost_product,tb.cost_platform_fee,tb.cost_paypal_fee,tb.cost_first,tb.cost_logistics,tb.cost_handle_platform,tb.total_fee,tb.seller_order_exchange_rate,tb.seller_other_exchange_rate,tb.wms_to_cny_exchange_rate,tb.shipping,tb.bailun_sku_quantity_ordered as quantity_shipped,tb.purchase_create_time as shipping_time,tb.platform_type,tb.cost_fba_fee,if(t6.financecategoryname is null,'',t6.financecategoryname) as financecategoryname 
                    from dc_base_oms_sku 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.has_scalp=0 and tb.has_innersale=0 and tb.bailun_interception_status in ('None','Failed') and tb.platform_type='FBA' and tb.purchase_create_time>='{day.ToString("yyyy-MM-dd")}' and tb.purchase_create_time<'{day.AddMonths(1).ToString("yyyy-MM-dd")}'
                    
                    ";
                objFBA = cn.Query<dc_month_sales_profit_orderdetail>(sql, null, null, true, 6 * 60).ToList();
                Console.WriteLine($"开始保存FBA明细 {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");
                page = 0;
                pagesize = 3000;
                while (page == 0 || (page * pagesize) < objFBA.Count)
                {
                    var tempList = objFBA.Skip(page*pagesize).Take(pagesize).ToList();
                    var str = "insert dc_month_sales_profit_orderdetail (month,pickid,orderskuid,bailun_sku,amount_sales,cost_product,cost_platform_fee,cost_paypal_fee,cost_first,cost_logistics,cost_handle_platform,total_fee,shipping,seller_order_exchange_rate,seller_other_exchange_rate,wms_to_cny_exchange_rate,quantity_shipped,shipping_time,platform_type,cost_fba_fee,financecategoryname) values ";
                    foreach (var item in tempList)
                    {
                        str += $"('{day.ToString("yyyy-MM")}',{item.pickid},{item.orderskuid},'{item.bailun_sku}',{item.amount_sales},{item.cost_product},{item.cost_platform_fee},{item.cost_paypal_fee},{item.cost_first},{item.cost_logistics},{item.cost_handle_platform},{item.total_fee},{item.shipping},{item.seller_order_exchange_rate},{item.seller_other_exchange_rate},{item.wms_to_cny_exchange_rate},{item.quantity_shipped},'{item.shipping_time.ToString("yyyy-MM-dd HH:mm:ss")}','{item.platform_type}',{item.cost_fba_fee},'{item.financecategoryname}'),";
                    }

                    Console.WriteLine($"总记录数：{objFBA.Count}，第{(page + 1)}页，记录数：{tempList.Count} {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");
                    SaveData(str.Substring(0, str.Length - 1));
                    page++;
                }

            }

            
        }

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

                cn.Execute(sql);

                return "";
            }
        }

    }
}
