using AutoTurnOver.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AutoTurnOver.DB
{
    public class dc_base_trans_temp_schedule_dao : connectionHelper
    {

        /// <summary>
        /// 从调拨系统同步数据
        /// </summary>
        public static void SynchroTransTempScheduleData()
        {
            var task_name = "SynchroTransTempScheduleData_v2";
            var conn = _connection;
            // 查询最后一次成功抓取的记录
            var last_task_synchro_log = conn.QuerySingleOrDefault<task_synchro_log>(" select  * from task_synchro_log where task_name=@task_name and status=1 order by end_time desc limit 1 ", new { task_name = task_name });

            var new_task_synchro_log = new task_synchro_log
            {
                create_date = DateTime.Now,
                end_time = DateTime.Now,
                status = 0,
                task_name = task_name
            };

            if (last_task_synchro_log != null)
            {
                new_task_synchro_log.start_time = last_task_synchro_log.end_time.AddMinutes(-1);
            }
            else
            {
                new_task_synchro_log.start_time = new DateTime(2001, 05, 14);
            }

            new_task_synchro_log.id = conn.Insert(new_task_synchro_log) ?? 0;
            int count = 0;


            var page = 1;
            var data_skus = new List<dc_base_trans_temp_schedule>();
            while (true)
            {
                var datas = ApiUtility.GetTransOrderTempSchedule(new_task_synchro_log.start_time, new_task_synchro_log.end_time, page);
                if (datas == null || datas.Count <= 0)
                {
                    break;
                }
                page++;

                foreach (var item in datas)
                {
                    foreach (var itemSku in item.allotScheduleProducts)
                    {
                        var db_data = new dc_base_trans_temp_schedule()
                        {
                            code = item.code ?? "",
                            creater_name = item.createrName,
                            data_id = item.purchaseCode + "-" + itemSku.sku,
                            create_time = item.createTime,
                            sku = itemSku.sku,
                            gmt_create_date = DateTime.Now,
                            gmt_update_date = DateTime.Now,
                            is_aims = item.isAims == true ? 1 : 0,
                            is_delete = item.isDeleted == true ? 1 : 0,
                            is_temp_data = item.isTempData == true ? 1 : 0,
                            is_exception = item.IsException == true ? 1 : 0,
                            product_name = itemSku.productName,
                            product_num = itemSku.productNum,
                            purchase_code = item.purchaseCode,
                            sku_ware_no = $"{itemSku.sku}{item.targetWareNo}",
                            source_ware_name = item.sourceWareName,
                            source_ware_no = item.sourceWareNo,
                            status = 0,
                            target_ware_name = item.targetWareName,
                            target_ware_no = item.targetWareNo,
                            time_stamp = DateTime.Now,
                            create_state = item.createState,
                            temp_delivery_count = itemSku.tempDeliveryCount
                        };

                        data_skus.Add(db_data);

                        db_data.id = conn.QueryFirstOrDefault<int?>(" select id from dc_base_trans_temp_schedule where `purchase_code`=@purchase_code and `sku`=@sku ", new { sku = db_data.sku, purchase_code = db_data.purchase_code }) ?? 0;
                        if (db_data.id > 0)
                        {
                            conn.Update(db_data);
                        }
                        else
                        {
                            conn.Insert(db_data);
                        }
                        count++;
                    }

                }

            }
            if (data_skus != null && data_skus.Count >= 1)
            {
                // 计算相关sku的总在途
                var temp_datas = _connection.Query<temp_sum_dto>(@"select sku_ware_no,sku as 'bailun_sku',target_ware_no as 'warehouse_code',sum(product_num) as 'count' from dc_base_trans_temp_schedule 
where is_delete=0 and is_temp_data=1 and is_exception=0 and create_state=@create_state
and sku_ware_no in @sku_ware_nos
GROUP BY sku_ware_no", new { sku_ware_nos = data_skus.Select(s=>s.sku_ware_no).Distinct().ToList(), create_state =(int)dc_base_trans_temp_schedule_create_state_enum.未创建 });

                foreach (var item in temp_datas)
                {
                    _connection.Execute("update dc_mid_transit set quantity_transfer_temp_schedule=@quantity_transfer_temp_schedule where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new
                    {
                        bailun_sku = item.bailun_sku,
                        warehouse_code = item.warehouse_code,
                        quantity_transfer_temp_schedule = item.count
                    });
                }

                var data_0 = data_skus.Where(s => !temp_datas.Any(v => v.sku_ware_no == s.sku_ware_no)).ToList();
                if (data_0 != null && data_0.Count>=1)
                {
                    foreach (var item in data_0.Select(s=>new { s.sku,s.target_ware_no}).Distinct())
                    {
                        _connection.Execute("update dc_mid_transit set quantity_transfer_temp_schedule=0 where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new
                        {
                            bailun_sku = item.sku,
                            warehouse_code = item.target_ware_no
                        });
                    }
                }

                // 计算相关sku的总暂存
                var temp_datas2 = _connection.Query<temp_sum_dto>(@"select sku_ware_no,sku as 'bailun_sku',target_ware_no as 'warehouse_code',sum(temp_delivery_count) as 'count' from dc_base_trans_temp_schedule 
where is_delete=0 and is_temp_data=1 and is_exception=0 and create_state=@create_state
and sku_ware_no in @sku_ware_nos
GROUP BY sku_ware_no", new { sku_ware_nos = data_skus.Select(s => s.sku_ware_no).Distinct().ToList(), create_state = (int)dc_base_trans_temp_schedule_create_state_enum.已暂存 });

                foreach (var item in temp_datas2)
                {
                    _connection.Execute("update dc_mid_transit set quantity_transfer_temporary_storage=@quantity_transfer_temporary_storage where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new
                    {
                        bailun_sku = item.bailun_sku,
                        warehouse_code = item.warehouse_code,
                        quantity_transfer_temporary_storage = item.count
                    });
                }

                var data_1 = data_skus.Where(s => !temp_datas2.Any(v => v.sku_ware_no == s.sku_ware_no)).ToList();
                if (data_1 != null && data_1.Count >= 1)
                {
                    foreach (var item in data_1.Select(s => new { s.sku, s.target_ware_no }).Distinct())
                    {
                        _connection.Execute("update dc_mid_transit set quantity_transfer_temporary_storage=0 where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new
                        {
                            bailun_sku = item.sku,
                            warehouse_code = item.target_ware_no
                        });
                    }
                }
            }

            new_task_synchro_log.count = count;
            new_task_synchro_log.status = 1;
            conn.Update(new_task_synchro_log);
        }

        public class temp_sum_dto
        {
            public string sku_ware_no { get; set; }
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
            public int count { get; set; }
        }
    }
}
