using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Linq;

using MySql.Data.MySqlClient;
using Dapper;
using Bailun.DC.SyncSemiStock.Models;

namespace Bailun.DC.SyncSemiStock
{
    public class Services : BackgroundService
    {
        private static bool isRuning = false;
        private Timer _timer;
        private static List<Tuple<DateTime, string, decimal>> listMonthExchange = new List<Tuple<DateTime, string, decimal>>();

        public Services()
        {
            Dapper.SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        }

        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 == 5 && now.Minute==2)  
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    Init();
                    Console.WriteLine("任务结束 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }

                if(now.Hour==0 && now.Minute==30)
                {
                    Console.WriteLine("启动获取供应商最新报价服务" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    SyncSupplierPrice();
                    Console.WriteLine("结束获取供应商最新报价服务" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));


                    Console.WriteLine("启动获取半成品库存流水日志服务" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    SyncStockLog();
                    Console.WriteLine("结束获取半成品库存流水日志服务" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    Console.WriteLine("启动保存每日半成品库存服务" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    SaveDailySemiStock();
                    Console.WriteLine("启动结束每日半成品库存服务" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public void Init()
        {
            var url = "http://mjzz.bailuntec.com/api/ProdStockPage";

            var page = 1;
            var rows = 100;
            var total = 0;
            var totalPage = 0;

            var result = DC.Common.HttpHelper.NetHelper.Request(url + "?page=1&rows=" + rows);
            var json = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.response_StockData>(result);

            if(json.success)
            {
                totalPage = json.data.TotalPages;
                total = json.data.TotalItems;

                try
                {
                    //删除历史数据
                    using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                    {
                        if (cn.State == System.Data.ConnectionState.Closed)
                        {
                            cn.Open();
                        }

                        cn.Execute("TRUNCATE dc_semi_stock_prod_new");

                    }

                    //保存第一页数据
                    SaveData(json.data.Items);

                    //请求第二页之后的数据
                    while (page < totalPage)
                    {
                        page++;

                        result = DC.Common.HttpHelper.NetHelper.Request(url + "?page=" + page + "&rows=" + rows);
                        json = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.response_StockData>(result);

                        if (json.success)
                        {
                            page = json.data.CurrentPage;
                            totalPage = json.data.TotalPages;
                            total = json.data.TotalItems;

                            //保存当前页数据
                            SaveData(json.data.Items);
                        }
                    }

                    //保存每日库存
                    using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                    {
                        if (cn.State == System.Data.ConnectionState.Closed)
                        {
                            cn.Open();
                        }

                        cn.Execute("delete from dc_semi_stock_prod_new_daily where record_time='" + DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + "'");

                        cn.Execute($"insert dc_semi_stock_prod_new_daily (material_id,warehouse_id,jit_prod_stock,plan_prod_stock,warehouse_name,material_code,material_name,material_category,new_price,gmt_create,gmt_modify,record_time) select material_id,warehouse_id,jit_prod_stock,plan_prod_stock,warehouse_name,material_code,material_name,material_category,new_price,gmt_create,gmt_modify,(DATE_SUB(curdate(),INTERVAL 1 DAY)) as 'record_time' from dc_semi_stock_prod_new");

                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("获取半成品实时库存数据异常："+ex.Message);
                    System.Threading.Thread.Sleep(5 * 1000);
                    Init();
                }

                
            }

        }

        private string SaveData(List<Models.DataItem> items)
        {
            try
            {
                var sqlhead = "insert dc_semi_stock_prod_new (material_id,warehouse_id,jit_prod_stock,plan_prod_stock,warehouse_name,material_code,material_name,material_category,new_price,gmt_create,gmt_modify) values ";

                var str = "";

                foreach (var item in items)
                {
                    str += $"('{item.material_id}','{item.warehouse_id}','{item.jit_prod_stock}','{item.plan_prod_stock}','{item.warehouse_name}','{item.material_code}','{item.material_name}','{item.material_category}','{item.new_price}','{DateTime.Now}','{DateTime.Now}'),";
                }

                if (str.Length > 0)
                {
                    str = str.Substring(0, str.Length - 1);
                }

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

                    cn.Execute(sqlhead + str);
                }

                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            
        }

        /// <summary>
        /// 同步供应商最新报价
        /// </summary>
        public void SyncSupplierPrice()
        {
            try
            {
                var start = DateTime.Parse("2017-01-01");
                var end = start.AddDays(1);
                var url = "http://mjgys.bailuntec.com/api/SupofferLog?";


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

                    var obj = cn.QueryFirstOrDefault<DC.Models.dc_semi_supplier_offer_log>("select * from dc_semi_supplier_offer_log_new order by create_time desc limit 1");
                    if (obj != null)
                    {
                        start = obj.update_time;
                        end = start.AddDays(1);
                    }

                    if (end > DateTime.Now)
                    {
                        end = DateTime.Now;
                    }

                }

                while (end <= DateTime.Now)
                {
                    var result = Common.HttpHelper.NetHelper.Request(url + $"btime={start.AddMinutes(-3).ToString("yyyy-MM-dd HH:mm:ss")}&etime={end.ToString("yyyy-MM-dd HH:mm:ss")}");
                    if (!string.IsNullOrEmpty(result))
                    {
                        var json = Newtonsoft.Json.JsonConvert.DeserializeObject<response_SupplierPrice>(result);

                        if (json.success)
                        {
                            Console.WriteLine(url + $"btime={start.AddMinutes(-3).ToString("yyyy-MM-dd HH:mm:ss")}&etime={end.ToString("yyyy-MM-dd HH:mm:ss")}" + "::获取数据成功！" + DateTime.Now);
                            SaveSupplierPrice(json.data.Select(a => new DC.Models.dc_semi_supplier_offer_log_new
                            {
                                create_time = a.create_time,
                                log_id = a.log_id,
                                sku_code = a.sku,
                                price = a.price,
                                sku_id = a.sku_id,
                                sku_name = a.sku_name,
                                supplier_id = a.supplier_id,
                                supplier_name = a.supplier_name,
                                unit_id = a.unit_id,
                                unit_name = a.unit_name,
                            }).ToList());
                            start = end;
                            end = end.AddDays(1);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("出现异常："+ex.Message+"；10s后重启");
                System.Threading.Thread.Sleep(10 * 1000);
                SyncSupplierPrice();
            }
            
        }

        /// <summary>
        /// 保存供应商报价数据
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private string SaveSupplierPrice(List<DC.Models.dc_semi_supplier_offer_log_new> list)
        {

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

                    foreach (var item in list)
                    {
                        var obj = cn.QueryFirstOrDefault<DC.Models.dc_semi_supplier_offer_log_new>($"select * from dc_semi_supplier_offer_log_new where sku_code='{item.sku_code}'");

                        if (obj == null)
                        {
                            obj = item;

                            obj.sku_id = item.sku_id;
                            obj.sku_code = item.sku_code ?? "";
                            obj.update_time = item.create_time;

                            cn.Insert(obj);
                        }
                        else
                        {
                            obj.log_id = item.log_id;
                            obj.price = item.price;
                            obj.sku_id = item.sku_id;
                            obj.sku_code = item.sku_code??"";
                            obj.sku_name = item.sku_name;
                            obj.supplier_id = item.supplier_id;
                            obj.supplier_name = item.supplier_name;
                            obj.unit_id = item.unit_id;
                            obj.unit_name = item.unit_name;
                            obj.update_time = item.create_time;

                            cn.Update(obj);

                        }
                    }
                }

                return "";
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                System.Threading.Thread.Sleep(10 * 1000);
                SaveSupplierPrice(list);
            }

            return "";
        }

        public void SyncStockLog()
        {

            var start = DateTime.Parse("2018-07-27");
            var end = start.AddDays(1);
            var url = "http://mjzz.bailuntec.com/api/StockLog?";


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

                var obj = cn.QueryFirstOrDefault<DC.Models.dc_semi_stock_log_new>("select * from dc_semi_stock_log_new order by create_time desc limit 1");
                if (obj != null)
                {
                    start = obj.create_time;
                    end = start.AddDays(1);
                }
            }

            while (end < DateTime.Now)
            {
                var result = Common.HttpHelper.NetHelper.Request(url + $"btime={start.AddMinutes(-3).ToString("yyyy-MM-dd")}&etime={end.ToString("yyyy-MM-dd")}&page=1&rows=100");

                var page = 1;
                //var pagesize = 100;
                var totalpage = 0;

                if (!string.IsNullOrEmpty(result))
                {
                    var json = Newtonsoft.Json.JsonConvert.DeserializeObject<response_StockLog>(result);

                    if (json.success)
                    {
                        Console.WriteLine(url + $"btime={start.AddMinutes(-3).ToString("yyyy-MM-dd")}&etime={end.ToString("yyyy-MM-dd")}" + "::page1::获取数据成功！" + DateTime.Now);

                        //保存第一页数据
                        SaveStockLog(json.data.Items);


                        page = json.data.CurrentPage;
                        totalpage = json.data.TotalPages;

                        

                        while(page<totalpage)
                        {
                            page++;

                            reRead: Console.Write("");

                            result = Common.HttpHelper.NetHelper.Request(url + $"btime={start.AddMinutes(-3).ToString("yyyy-MM-dd")}&etime={end.ToString("yyyy-MM-dd")}&page={page}&rows=100");

                            if (!string.IsNullOrEmpty(result))
                            {
                                json = Newtonsoft.Json.JsonConvert.DeserializeObject<response_StockLog>(result);
                                if (json.success)
                                {
                                    Console.WriteLine(url + $"btime={start.AddMinutes(-3).ToString("yyyy-MM-dd")}&etime={end.ToString("yyyy-MM-dd")}" + "::page" + page + "::获取数据成功！" + DateTime.Now);

                                    //保存数据
                                    SaveStockLog(json.data.Items);

                                    page = json.data.CurrentPage;
                                    totalpage = json.data.TotalPages;
                                }
                            }
                            else
                            {
                                goto reRead;
                            }

                        }

                        start = end;
                        end = end.AddDays(1);
                    }
                }

            }
        }

        private void SaveStockLog(List<response_StockLog_Data_Items> list)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    foreach (var item in list)
                    {
                        var obj = cn.QueryFirstOrDefault<DC.Models.dc_semi_stock_log_new>("select * from dc_semi_stock_log_new where log_id=" + item.log_id);

                        if (obj == null)
                        {
                            var m = new DC.Models.dc_semi_stock_log_new
                            {
                                log_id = item.log_id,
                                log_quantity = item.log_quantity,
                                log_type = item.log_type,
                                log_type_id = item.log_type_id,
                                sku_code = item.sku,
                                sku_name = item.sku_name,
                                final_stock = item.final_stock,

                                create_time = item.create_time,
                                product_category_id = item.product_category_id ?? 0,
                                product_category_name = item.product_category_id_name ?? "",
                                redundancy_stock = item.redundancy_stock,
                                type_id = item.type_id,
                                type_name = item.type_name,

                                update_time = DateTime.Now,
                                warehouse_id = item.warehouse_id,
                                warehouse_name = item.warehouse_name,
                                warehouse_type = item.warehouse_type,
                            };

                            cn.Insert(m);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("保存StockLog出现异常，1分钟后重试。");
                System.Threading.Thread.Sleep(60 * 1000);

                SaveStockLog(list);
            }
            
        }


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

                    cn.Execute($"delete from dc_semi_daily_stock_new where day='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}'");

                    var sql = $@"insert into dc_semi_daily_stock_new(day,inventory_amount,sku_code,sku_name,stock_quantity,price,warehouse_id,warehouse_name,redundancy_stock,product_category_id,product_category_name,type_id,type_name)
                        select '{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}',l.final_stock * ifnull(ol.price,0),l.sku_code,l.sku_name,l.final_stock,ifnull(ol.price,0),l.warehouse_id,l.warehouse_name,
                        (case when l.redundancy_stock is null then 0 when l.redundancy_stock < 0 then 0 else l.redundancy_stock end),l.product_category_id,l.product_category_name,l.type_id,l.type_name
                        from dc_semi_stock_log_new l 
                        join (select max(log_id) maxid from dc_semi_stock_log_new group by warehouse_id,sku_code) t2 on l.log_id=t2.maxid
                        left join dc_semi_supplier_offer_log_new ol on l.sku_code = ol.sku_code";

                    cn.Execute(sql,null,null,6*60);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("保存每日半成品库存异常，10秒后重试！异常信息："+ex.Message);
                System.Threading.Thread.Sleep(10 * 1000);

                SaveDailySemiStock();
            }
            
        }

    }
}
