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

namespace Bailun.DC.SkuProfitService
{
    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.Hour == 0 && now.Minute == 5)  //每天凌晨 00：05分启动
                {
                    Save();
                }

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

        public bool Save()
        {
            try
            {
                var start = DateTime.Parse(DateTime.Now.AddDays(-30).ToShortDateString());
                var end = DateTime.Parse(DateTime.Now.ToShortDateString());

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

                    //先删除历史记录
                    cn.Execute("delete from dc_mid_sku_profit", null, null, 2 * 60);

                    //30天销量
                    var list30day = cn.Query<Models.mSkuWarehouse>($@"select t.*,t2.unit_price,t2.category_id,t2.category_name,t3.usable_stock,t4.quantity_purchase,t4.quantity_transfer,t2.sku_title_cn from 
                                                                    (select t1.bailun_sku,t1.warehouse_code,t1.warehouse_name,sum(t1.bailun_sku_quantity_ordered) count,sum(t1.amount_sales*t1.bailun_sku_quantity_shipped*t1.seller_order_exchange_rate) amount,sum(t1.profit_total) profit_total from dc_base_oms_sku t1 
                                                                    where t1.bailun_order_status!='Canceled' and t1.has_delete=0 and t1.has_scalp=0 and t1.has_cancle=0 and t1.paid_time>='{start.ToString("yyyy-MM-dd")}' and paid_time<'{end.ToString("yyyy-MM-dd")}' and t1.warehouse_code!='' and t1.company_id=1
                                                                    group by t1.bailun_sku,t1.warehouse_code) t
                                                                    left join dc_base_sku t2 on t.bailun_sku=t2.bailun_sku
                                                                    left join dc_base_stock t3 on t.bailun_sku=t3.bailun_sku and t.warehouse_code=t3.warehouse_code
                                                                    left join dc_mid_transit t4 on t.bailun_sku=t4.bailun_sku and t.warehouse_code=t4.warehouse_code"
                                                                    ,null,null,true,5*60);
                    //前一天销量
                    var list1day = cn.Query<Models.mSkuWarehouse>($@"select t.*,t2.unit_price,t2.category_id,t2.category_name,t3.usable_stock,t4.quantity_purchase,t4.quantity_transfer,t2.sku_title_cn from 
                                                                    (select t1.bailun_sku,t1.warehouse_code,t1.warehouse_name,sum(t1.bailun_sku_quantity_ordered) count,sum(t1.amount_sales*t1.bailun_sku_quantity_shipped*t1.seller_order_exchange_rate) amount,sum(t1.profit_total) profit_total from dc_base_oms_sku t1 
                                                                    where t1.bailun_order_status!='Canceled' and t1.has_delete=0 and t1.has_scalp=0 and t1.has_cancle=0 and t1.paid_time>='{end.AddDays(-1).ToString("yyyy-MM-dd")}' and paid_time<'{end.ToString("yyyy-MM-dd")}' and t1.warehouse_code!='' and t1.company_id=1
                                                                    group by t1.bailun_sku,t1.warehouse_code) t
                                                                    left join dc_base_sku t2 on t.bailun_sku=t2.bailun_sku
                                                                    left join dc_base_stock t3 on t.bailun_sku=t3.bailun_sku and t.warehouse_code=t3.warehouse_code
                                                                    left join dc_mid_transit t4 on t.bailun_sku=t4.bailun_sku and t.warehouse_code=t4.warehouse_code"
                                                                    , null, null, true, 5 * 60);
                    //采购入库
                    var listPurchasePutin = cn.Query<Models.mPutIn>($@"select bailun_sku,warehouse_code,sum(count) count from dc_base_purchase_inbound where update_time>='{start.ToString("yyyy-MM-dd")}'and update_time<'{end.ToString("yyyy-MM-dd")}' and status=1 and bailun_sku in ('{string.Join("','", list30day.Select(a=>a.bailun_sku).Distinct())}')
group by bailun_sku,warehouse_code", null, null, true, 5 * 60);

                    //调拨入库
                    var listAllotPutin = cn.Query<Models.mPutIn>($@"select bailun_sku,warehouse_code,sum(count) count from dc_base_transfer_inbound where gmt_modified>='{start.ToString("yyyy-MM-dd")}'and gmt_modified<'{end.ToString("yyyy-MM-dd")}' and deleted=0 and bailun_sku in ('{string.Join("','", list30day.Select(a => a.bailun_sku).Distinct())}')
group by bailun_sku,warehouse_code", null, null, true, 5 * 60);

                    var listcount = list30day.Count();
                    var pagesize = 5000;
                    var index = 0;
                    var sqlInsert = @"insert dc_mid_sku_profit(day,createtime,bailun_sku,warehouse_code,warehouse_name,category_id,category_name,usable_stock,usable_stock_amount,quantity_purchase,quantity_purchase_amount,quantity_transfer,quantity_transfer_amount,sku_title_cn,yd_sales_amount,yd_sales_count,yd_sales_stockamount,yd_grossprofit,yd_grossprofit_rate,pre30_sales_amount,pre30_sales_count,pre30_stockamount,pre30_grossprofit,pre30_grossprofit_rate,pre30_return_rate,pre30_stockamount_turn_rate,pre30_stockamount_add,pre30_stockamount__rate,pre30_stocknum_turn_rate,pre30_stock_add_count,pre30_stocknum_rate,pre30_stockamount_vendibility_day,pre30_stocknum_vendibility_day) values ";
                    var str = "";
                    foreach (var item in list30day)
                    {
                        var m = new Bailun.DC.Models.dc_mid_sku_profit()
                        {
                            day = DateTime.Now.AddDays(-1),
                            createtime = DateTime.Now,
                            bailun_sku = item.bailun_sku,
                            warehouse_code = item.warehouse_code,
                            warehouse_name = item.warehouse_name,

                            yd_sales_amount = 0,
                            yd_sales_count = 0,
                            yd_sales_stockamount = 0,
                            yd_grossprofit = 0,
                            yd_grossprofit_rate = 0
                        };

                        var obj1day = list1day.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (obj1day != null)
                        {
                            m.yd_sales_amount = obj1day.amount;
                            m.yd_sales_count = obj1day.count;
                            m.yd_sales_stockamount = obj1day.unit_price * obj1day.count;

                            m.yd_grossprofit = obj1day.profit_total;
                            m.yd_grossprofit_rate = (obj1day.usable_stock * obj1day.unit_price) >0?Math.Round(m.yd_grossprofit /(obj1day.usable_stock*obj1day.unit_price),4):0;
                        }

                        var objPutinPurchase = listPurchasePutin.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        var objPutinAllot = listAllotPutin.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();

                        var obj30day = list30day.Where(a => a.bailun_sku == item.bailun_sku && a.warehouse_code == item.warehouse_code).FirstOrDefault();
                        if (obj30day != null)
                        {
                            m.pre30_sales_amount = obj30day.amount;
                            m.pre30_sales_count = obj30day.count;
                            m.pre30_stockamount = obj30day.count * obj30day.unit_price;
                            m.pre30_grossprofit = obj30day.profit_total;
                            m.pre30_grossprofit_rate = (obj30day.usable_stock * obj30day.unit_price) >0?Math.Round(m.pre30_grossprofit / (obj30day.usable_stock * obj30day.unit_price), 4):0;

                            m.pre30_return_rate = 0;
                            m.pre30_stockamount_turn_rate = (obj30day.usable_stock + obj30day.quantity_purchase + obj30day.quantity_transfer)>0?Math.Round((decimal)obj30day.count/(obj30day.usable_stock+obj30day.quantity_purchase+obj30day.quantity_transfer),4):0;
                            m.pre30_stockamount_add = ((objPutinPurchase != null ? objPutinPurchase.count * obj30day.unit_price : 0) + (objPutinAllot != null ? objPutinAllot.count * obj30day.unit_price : 0));
                            m.pre30_stockamount__rate = (objPutinPurchase != null ? objPutinPurchase.count : 0) + (objPutinAllot != null ? objPutinAllot.count : 0)>0?Math.Round((decimal)obj30day.count / ((objPutinPurchase != null ? objPutinPurchase.count : 0) + (objPutinAllot != null ? objPutinAllot.count : 0)), 4):0;
                            m.pre30_stocknum_turn_rate = (obj30day.usable_stock + obj30day.quantity_purchase + obj30day.quantity_transfer)>0?Math.Round((decimal)obj30day.count / (obj30day.usable_stock + obj30day.quantity_purchase + obj30day.quantity_transfer), 4):0;


                            //累积入库件数
                            m.pre30_stock_add_count = ((objPutinPurchase != null ? objPutinPurchase.count : 0) + (objPutinAllot != null ? objPutinAllot.count : 0));
                            m.pre30_stocknum_rate = (objPutinPurchase != null ? objPutinPurchase.count : 0) + (objPutinAllot != null ? objPutinAllot.count : 0)>0? Math.Round((decimal)obj30day.count / ((objPutinPurchase != null ? objPutinPurchase.count : 0) + (objPutinAllot != null ? objPutinAllot.count : 0)), 4):0;
                            m.pre30_stockamount_vendibility_day = obj30day.count>0?Math.Round((decimal)obj30day.usable_stock * 30 / obj30day.count, 4):0;
                            m.pre30_stocknum_vendibility_day = obj30day.count>0?Math.Round((decimal)obj30day.usable_stock * 30 / obj30day.count, 4):0;
                        }

                        index++;

                        str += ($@" ('{m.day.ToString("yyyy-MM-dd")}','{m.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{m.bailun_sku}','{m.warehouse_code}','{m.warehouse_name}',{item.category_id},'{item.category_name.Replace("'","")}',{item.usable_stock},{(item.usable_stock*item.unit_price)},{item.quantity_purchase},{(item.quantity_purchase*item.unit_price)},{item.quantity_transfer},{(item.quantity_transfer*item.unit_price)},'{item.sku_title_cn.Replace("'","")}',{m.yd_sales_amount},{m.yd_sales_count},{m.yd_sales_stockamount},{m.yd_grossprofit},{m.yd_grossprofit_rate},{m.pre30_sales_amount},{m.pre30_sales_count},{m.pre30_stockamount},{m.pre30_grossprofit},{m.pre30_grossprofit_rate},{m.pre30_return_rate},{m.pre30_stockamount_turn_rate},{m.pre30_stockamount_add},{m.pre30_stockamount__rate},{m.pre30_stocknum_turn_rate},{m.pre30_stock_add_count},{m.pre30_stocknum_rate},{m.pre30_stockamount_vendibility_day},{m.pre30_stocknum_vendibility_day}),");

                        if (index % pagesize == 0 || index == listcount)
                        {
                            if (str.Length > 0)
                            {
                                str = str.Substring(0, str.Length - 1);
                            }

                            cn.Execute(sqlInsert + str, null, null, 5 * 60);

                            str = "";

                            Console.WriteLine("保存数据成功，当前记录：" + index + ",总记录：" + listcount+",时间："+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                    }
                }


                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("保存数据失败，异常信息："+ex.Message);

                return false;
            }
            
        }

    }
}
