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"));
                    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<Models.mOrder>();
            var objFBA = new List<Models.mOrder>();
            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_tail = 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,
                managercost_dizhi = 0,
                managercost_meijiashengchan = 0,
                managercost_xinhuilan = 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();
                }

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

                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,if(t6.financecategoryname is null,'',t6.financecategoryname) as 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.mOrder>(sql,null,null,true,6*60).ToList();

                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,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.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.mOrder>(sql,null,null,true,6*60).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??0+a.cost_paypal_fee??0+a.cost_handle_platform??0) + objFBA.Sum(a => a.cost_platform_fee??0);

                //FBA费
                m.fee_fba = objFBA.Sum(a => a.cost_fba_fee??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);
                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);
                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);
                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);
                m.fee_platform += (obj ?? 0);

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

                //平台扣费及退款
                m.fee_platform_and_refund = m.fee_platform + m.fee_fba + m.fee_refund;

                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();


                //物流仓储费用 = 头程运输+直邮物流费（国内发货）+尾程物流费（国外发货）+海外仓仓储及其他费用
                m.fee_logistics_first = objOrder.Sum(a => a.cost_first??0) + objFBA.Sum(a => a.cost_first??0);
                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??0);

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

                
                //付现销售费用
                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("歌戈儿"))?.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_meijiashengchan = list.Where(a => a.company_name.Contains("美甲生产仓"))?.Sum(a => a.amount_rmb) ?? 0;
                m.managercost_dizhi = 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.managercost_meijiashengchan+m.managercost_dizhi;


                //财务费用=手续费
                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_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) 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})";

                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,
            };




        }

        /// <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))
                    {
                        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;

        }
    
    }
}
