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

namespace AutoTurnOver.DB
{
    public class dc_base_transfer_extend_dao : connectionHelper
    {
        /// <summary>
        /// 刷新数据
        /// </summary>
        public static void ResetData()
        {
            // 查询所有当前未完成的调拨单
            var temp_trans_order_datas = _connection.Query<temp_trans_order_dto>(@"select 
t2.sku as 'bailun_sku',
t1.targetwareno as 'warehouse_code',
ifnull(t3.expectarrivaltime,t1.expectarrivaltime)  as 'expectarrivaltime',
t1.`code` as 'order_no',
t2.deliverycount-t2.storagecount as 'count',
t1.trackingno,
t1.transporttype,
t1.logisticsname
from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid =t2.dataid
left join (select system_order_no,max(expectarrivaltime) as 'expectarrivaltime' from  dc_lms_order_transfer_current_node GROUP BY system_order_no ) as t3 on t1.`code` = t3.system_order_no
where t1.isdeleted=0 and t1.stateid not in (5,11,12)
and t2.deliverycount > t2.storagecount
and t1.isexception = 0
and t1.expectarrivaltime >='1991-01-01' ",commandTimeout:0).ToList();
            if (temp_trans_order_datas != null && temp_trans_order_datas.Count>=1)
            {
                var db_datas = new List<dc_base_transfer_extend>();
                foreach (var item in temp_trans_order_datas.GroupBy(s => new { s.bailun_sku, s.warehouse_code }))
                {
                    var lately_item = item.OrderBy(s => s.expectarrivaltime).FirstOrDefault();
                    var lately_data = new dc_base_transfer_extend
                    {
                        bailun_sku = lately_item.bailun_sku,
                        warehouse_code = lately_item.warehouse_code,
                        bailun_sku_warehouse_code = $"{lately_item.bailun_sku}{lately_item.warehouse_code}",
                        lately_transfer_count = lately_item.count,
                        lately_transfer_expect_arrival_time = lately_item.expectarrivaltime,
                        lately_transfer_order_no = lately_item.order_no ?? "",
                        lately_transfer_trackingno = lately_item.trackingno ?? "",
                        lately_transfer_logisticsname = lately_item.logisticsname ?? "",
                        lately_transfer_transporttype = lately_item.transporttype ?? ""
                    };
                    lately_data.id = _connection.QueryFirstOrDefault<long?>(" select id from dc_base_transfer_extend where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ",new {
                        bailun_sku_warehouse_code = lately_data.bailun_sku_warehouse_code
                    }) ?? 0;
                    if (lately_data.id > 0)
                    {
                        _connection.Update(lately_data);
                    }
                    else
                    {
                        _connection.Insert(lately_data);
                    }
                    db_datas.Add(lately_data);
                }
                // 清空没有在途的数据
                _connection.Execute(" delete from dc_base_transfer_extend where bailun_sku_warehouse_code not in @bailun_sku_warehouse_codes ",new {
                    bailun_sku_warehouse_codes = db_datas.Select(s=>s.bailun_sku_warehouse_code).ToList()
                });

            }
            else
            {
                _connection.Execute(" delete from dc_base_transfer_extend  ");
            }
        }

        public class temp_trans_order_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
            public DateTime? expectarrivaltime { get; set; }
            public string order_no { get; set; }
            public int count { get; set; }
            public string trackingno { get; set; }
            public string transporttype { get; set; }
            public string logisticsname { get; set; }
        }

        /// <summary>
        /// 抓取调拨单当前节点
        /// </summary>
        public static void SynchroLmsTransferOrder()
        {
            var task_name = "SynchroLmsTransferOrder_v14";
            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.AddMinutes(-3),
                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(2020, 05, 14);
            }

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


            var page = 1;
            var rows = 1000;
            while (true)
            {
                var datas = ApiUtility.GetLmsTransferOrderNode(page,rows, new_task_synchro_log.start_time, new_task_synchro_log.end_time);
                if (datas == null || datas.Count <= 0)
                {
                    break;
                }
                page++;

                foreach (var item in datas)
                {
                    var db_data = new dc_lms_order_transfer_current_node()
                    {
                       box_id = item.box_id??"",
                       data_id = item.data_id,
                        sku = item.sku??"",
                       node_date  =item.node_date,
                       lms_update_date = item.update_date,
                       node_name = item.node_name??"",
                       sign_exe  =item.sign_exe,
                       system_order_no =item.system_order_no,
                       trackingno = item.trackingno??"",
                       transfer_no = item.transfer_no??"",
                       logistics_code = item.logistics_code ?? "",
                       logistics_name = item.logistics_name ?? "",
                       expectarrivaltime = item.expectarrivaltime,
                       surplus_node_days=item.surplus_node_days,
                        sys_update_date = DateTime.Now,
                        quantity = item.quantity,
                        storage_count  = item.storage_count,
                        sign_time = item.sign_time,
                        put_time = item.put_time,

                        collect_time = item.collect_time,
                        flight_time = item.flight_time,
                        transport_time = item.transport_time,
                        ascan_time = item.ascan_time,
                        arrival_time = item.arrival_time,
                        clearance_time = item.clearance_time,
                        sail_time = item.sail_time
                    };

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

                // 如果抓到箱子号了，则把之前没有箱子号的数据删了
                var dataIds = datas.Where(s => !string.IsNullOrWhiteSpace(s.box_id)).Select(s=>s.data_id).ToList();
                if (dataIds!=null && dataIds.Count>=1)
                {
                    _connection.Execute(" delete from dc_lms_order_transfer_current_node where box_id='' and data_id in @data_ids ",new {
                        data_ids = dataIds
                    });
               }
            }


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