using System;
using System.Collections.Generic;
using System.Text;
using AutoTurnOver.Models.dc_base_purchase;
using Dapper;
using System.Linq;
using AutoTurnOver.Models;
using MySql.Data.MySqlClient;

namespace AutoTurnOver.DB
{
    public class AveragePurchase : connectionHelper
    {

        /// <summary>
        /// 取最近2天有发生采购记录的SKU
        /// 采购记录
        /// 到货记录
        /// 入库记录
        /// </summary>
        /// <param name="day">最近几天</param>
        /// <returns></returns>
        public static IEnumerable<temp_sku_dto> GetChangeSku(int day)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("time", DateTime.Now.AddDays(0 - day).ToString("yyyy-MM-dd 00:00:00"));
            return _connection.Query<temp_sku_dto>(@"select t2.hq_type,t1.bailun_sku,t1.warehouse_code,t1.supplier_id,t3.buyer_name from dc_base_purchase as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
where t1.supplier_id>0 and buy_status=4 and t1.update_time>=@c_time
-- and EXISTS ( select * from  dc_base_stock as t2 where t1.bailun_sku =t2.bailun_sku and t1.warehouse_code = t2.warehouse_code)
  -- and t1.bailun_sku='366628401' and t1.warehouse_code='GZBLWH'
GROUP BY t1.bailun_sku,t1.warehouse_code,t1.supplier_id ", new { c_time  = DateTime.Now.AddDays(-7)});
            //return _connection.Query<temp_sku_dto>("select t1.bailun_sku,t1.warehouse_code,t1.supplier_id from dc_base_purchase as t1 where t1.update_time>=@time group by t1.bailun_sku,t1.warehouse_code,t1.supplier_id ", parameters);
        }

        public static IEnumerable<temp_sku_dto> GetTransferChangeSku(int day,string bailun_sku = "")
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("c_time", DateTime.Now.AddDays(0 - day).ToString("yyyy-MM-dd 00:00:00"));
            var sql = @"select t2.hq_type,tsku.sku as 'bailun_sku',t1.targetwareno as 'warehouse_code'
from dc_base_transfer_info as t1 
left join dc_base_transfer_info_skus as tsku on t1.dataid = tsku.dataid
left join dc_base_warehouse as t2 on t1.targetwareno = t2.warehouse_code
where t1.isdeleted=0  and t1.`timestamp`>=@c_time
";
            if (!string.IsNullOrWhiteSpace(bailun_sku))
            {
                sql += " and tsku.sku=@bailun_sku ";
                parameters.Add("bailun_sku", bailun_sku);
            }
            return _connection.Query<temp_sku_dto>(sql, parameters);
            

            //return _connection.Query<temp_sku_dto>("select t1.bailun_sku,t1.warehouse_code,t1.supplier_id from dc_base_purchase as t1 where t1.update_time>=@time group by t1.bailun_sku,t1.warehouse_code,t1.supplier_id ", parameters);
        }

        public static IEnumerable<dc_average_purchase> GetListByWCode(string sku, string warehouse_code)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehouse_code);
            return _connection.Query<dc_average_purchase>("select * from dc_average_purchase where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", parameters);
        }

        public static IEnumerable<dc_average_purchase> GetList(string supplier_ids,string bailun_skus)
        {
            DynamicParameters parameters = new DynamicParameters();
            var sql = @" select t1.* from dc_average_purchase as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
where t1.supplier_id = t2.suppliers_id  ";
            if (!string.IsNullOrWhiteSpace(supplier_ids))
            {
                sql += " and t1.supplier_id in @supplier_ids ";
                parameters.Add("supplier_ids", supplier_ids.Split(',').Select(s=>int.Parse(s)).ToArray());
            }
            if (!string.IsNullOrWhiteSpace(bailun_skus))
            {
                sql += "  and t1.bailun_sku in @bailun_skus  ";
                parameters.Add("bailun_skus", bailun_skus.Split(','));
            }
            if (parameters.ParameterNames.Count() <= 0)
            {
                throw new Exception("请至少传递一个参数");
            }
            return _connection.Query<dc_average_purchase>(sql, parameters);
        }

        /// <summary>
        /// 查询仓库指标
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_average_warehouse GetWarehouse(string sku, string warehousecode)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehousecode);
            return _connection.QueryFirstOrDefault<dc_average_warehouse>("select * from dc_average_warehouse where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ", parameters);
        }


        /// <summary>
        /// 获取SKU 的最近5条采购完成的采购单
        /// </summary>
        /// <param name="skuData">sku 信息</param>
        /// <param name="seed_count">取数天数</param>
        /// <returns></returns>
        public static IEnumerable<dc_base_purchase_dto> GetPurchaseList(temp_sku_dto skuData, int seed_count)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", skuData.bailun_sku);
            parameters.Add("supplier_id", skuData.supplier_id);
            parameters.Add("warehouse_code", skuData.warehouse_code);
            parameters.Add("seed_count", seed_count);
            return _connection.Query<dc_base_purchase_dto>(@"select t1.price,t1.bailun_sku,t1.warehouse_code,t1.purchase_id,t1.warehouse_name,t1.count,t1.sku_name,t1.supplier_id,t1.supplier_name,t1.confirm_time,t1.pay_time,t1.pay_type from dc_base_purchase as t1
where t1.buy_status=4 and t1.bailun_sku=@bailun_sku and t1.supplier_id=@supplier_id and t1.warehouse_code=@warehouse_code
ORDER BY t1.update_time desc LIMIT @seed_count
 ", parameters);

        }

        /// <summary>
        /// 获取SKU 的最近5条采购完成的采购单
        /// </summary>
        /// <param name="skuData">sku 信息</param>
        /// <param name="seed_count">取数天数</param>
        /// <returns></returns>
        public static IEnumerable<dc_base_purchase_dto> GetPurchaseListByWode(temp_sku_dto skuData, int seed_count)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", skuData.bailun_sku);
            parameters.Add("warehouse_code", skuData.warehouse_code);
            parameters.Add("seed_count", seed_count);
            return _connection.Query<dc_base_purchase_dto>(@"select t1.price,t1.bailun_sku,t1.warehouse_code,t1.purchase_id,t1.warehouse_name,t1.count,t1.sku_name,t1.supplier_id,t1.supplier_name,t1.confirm_time,t1.pay_time,t1.pay_type from dc_base_purchase as t1
where t1.buy_status=4 and t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code  and t1.count>0
ORDER BY t1.update_time desc LIMIT @seed_count
 ", parameters);

        }

        /// <summary>
        /// 获取指定采购单的到货数据
        /// </summary>
        /// <param name="skuData">sku 信息</param>
        /// <param name="purchase_ids">采购单号</param>
        /// <returns></returns>
        public static IEnumerable<dc_base_purchase_arrival_dto> GetArrivalList(IEnumerable<string> purchase_ids,string bailun_sku)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("purchase_ids", purchase_ids.ToArray());
            parameters.Add("bailun_sku", bailun_sku);
            return _connection.Query<dc_base_purchase_arrival_dto>(@"select * from dc_base_purchase_arrival where purchase_id in @purchase_ids and bailun_sku = @bailun_sku
 ", parameters);
        }

        /// <summary>
        /// 获取指定采购单的入库数据
        /// </summary>
        /// <param name="skuData">sku 信息</param>
        /// <param name="purchase_ids">采购单号</param>
        /// <returns></returns>
        public static IEnumerable<dc_base_purchase_inbound_dto> GetInboundList(IEnumerable<string> purchase_ids, string bailun_sku)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("purchase_ids", purchase_ids.ToArray());
            parameters.Add("bailun_sku", bailun_sku);
            return _connection.Query<dc_base_purchase_inbound_dto>(@"
select t1.*,t2.update_time as 'arrival_time' from dc_base_purchase_inbound as t1 
left join dc_base_purchase_arrival as t2 on t1.purchase_id = t2.purchase_id and t1.bailun_sku = t2.bailun_sku
where t1.purchase_id in @purchase_ids and t1.bailun_sku = @bailun_sku
and t2.update_time is not null and t1.update_time>t2.update_time
 ", parameters);
        }

        /// <summary>
        /// / 获取指定SKU的最近调拨数据
        /// </summary>
        /// <param name="bailun_sku"></param>
        /// <param name="warehouse_code"></param>
        /// <param name="type">调拨类型</param>
        /// <param name="seed_count"></param>
        /// <returns></returns>
        public static IEnumerable<dc_base_transfer_log> GetTransportList_bak(string bailun_sku,string warehouse_code, string type,string logistics_code,  int seed_count)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", bailun_sku);
           
            parameters.Add("seed_count", seed_count); 
                parameters.Add("warehouse_code", warehouse_code);
            string sql = @" 
select 
t1.bailun_sku,
t1.warehouse_code,
t1.transfer_order_id,
-- GROUP_CONCAT(DISTINCT t1.bailun_sku),
t1.create_time as 'delivery_time', -- 交运时间
t2.create_time as 'bale_time', -- 打包时间
t5.create_time as 'verify_time', -- 确认时间
t6.create_time as 'complete_time', -- 完成时间
t3.create_time as 'arrival_time', -- 到货时间
t4.create_time as 'inbound_time', -- 入库时间
t3.transport_type
from dc_base_transfer_delivery as t1 -- 交运时间
left join (select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_bale as t2_1 where deleted =0 and bailun_sku=@bailun_sku and warehouse_code=@warehouse_code  GROUP BY t2_1.transfer_order_id) as t2 on t1.transfer_order_id = t2.transfer_order_id
left join (select t2_1.transfer_order_id,max(t2_1.arrival_time) as 'create_time',t2_1.transport_type from dc_base_transfer_transport as t2_1 where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code  GROUP BY t2_1.transfer_order_id) as t3 on t1.transfer_order_id = t3.transfer_order_id
left join (select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_inbound as t2_1 where deleted =0 and bailun_sku=@bailun_sku and warehouse_code=@warehouse_code  GROUP BY t2_1.transfer_order_id) as t4 on t1.transfer_order_id = t4.transfer_order_id
left join (select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_verify as t2_1 where deleted =0  and bailun_sku=@bailun_sku and warehouse_code=@warehouse_code GROUP BY t2_1.transfer_order_id) as t5 on t1.transfer_order_id = t5.transfer_order_id
left join (select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_complete as t2_1 where deleted =0 and bailun_sku=@bailun_sku and warehouse_code=@warehouse_code  GROUP BY t2_1.transfer_order_id) as t6 on t1.transfer_order_id = t6.transfer_order_id
left join dc_base_warehouse as t_w on t1.warehouse_code = t_w.warehouse_code
left join ( select `code`,`logisticscode` from dc_base_transfer_info where logisticscode!='' and logisticscode is not null  GROUP BY  `code` ) as t_info on t1.transfer_order_id = t_info.`code`
where t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code and t1.deleted=0 
and t1.create_time is not null and t2.create_time is not null  and t4.create_time is not null and t5.create_time is not null and t6.create_time is not null 
and t4.create_time>='2018-01-01' and t3.create_time>='2018-01-01' 
and t2.create_time < t1.create_time -- 调拨天数不为负数
and t5.create_time < t3.create_time -- 头程天数不为负数
and  t3.create_time < t4.create_time -- 入库天数不为负数
and t_w.hq_type!='国内仓'
";
            if (!string.IsNullOrWhiteSpace(type))
            {
                sql += " and t3.transport_type=@transport_type ";
                parameters.Add("transport_type", type);
            }
            if (!string.IsNullOrWhiteSpace(logistics_code))
            {
                sql += " and t_info.logisticscode=@logisticscode ";
                parameters.Add("logisticscode", logistics_code);
            }
            sql += "LIMIT @seed_count ";
            var conn = _connection;
            var datas = conn.Query<dc_base_transfer_log>(sql, parameters);
            //if(datas!=null && datas.Count() >= 1)
            //{
            //    foreach (var item in datas)
            //    {
            //        item.bale_time = conn.QuerySingleOrDefault<DateTime>(" select t2_1.transfer_order_id,max(t2_1.arrival_time) as 'create_time',t2_1.transport_type from dc_base_transfer_transport as t2_1 where deleted =0 and transfer_order_id=@transfer_order_id ", new { transfer_order_id  = item.transfer_order_id });
            //        item.verify_time = conn.QuerySingleOrDefault<DateTime>(" select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_verify as t2_1 where deleted =0 and transfer_order_id=@transfer_order_id ", new { transfer_order_id = item.transfer_order_id });
            //        item.complete_time = conn.QuerySingleOrDefault<DateTime>(" select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_complete as t2_1 where deleted =0  and transfer_order_id=@transfer_order_id ", new { transfer_order_id = item.transfer_order_id });
            //        item.arrival_time = conn.QuerySingleOrDefault<DateTime>(" select t2_1.transfer_order_id,max(t2_1.arrival_time) as 'create_time',t2_1.transport_type from dc_base_transfer_transport as t2_1 where transfer_order_id=@transfer_order_id ", new { transfer_order_id = item.transfer_order_id });
            //        item.inbound_time = conn.QuerySingleOrDefault<DateTime>(" select t2_1.transfer_order_id,max(t2_1.create_time) as 'create_time' from dc_base_transfer_inbound as t2_1 where deleted =0 and transfer_order_id=@transfer_order_id ", new { transfer_order_id = item.transfer_order_id });
            //    }
            //}
            return datas;
        }


        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="data"></param>
        public static void Save(dc_average_purchase data, MyMySqlConnection conn)
        {
            if (conn == null)
            {
                conn = _connection;
            }

            data.update_time = DateTime.Now;

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", data.bailun_sku);
            parameters.Add("supplier_id", data.supplier_id);
            parameters.Add("warehouse_code", data.warehouse_code);
            var oldData = conn.QueryFirstOrDefault(" select * from dc_average_purchase where bailun_sku=@bailun_sku and supplier_id=@supplier_id and warehouse_code=@warehouse_code ", parameters);
            if (oldData == null)
            {
                conn.Insert(data);
            }
            else
            {
                data.ID = oldData.ID;
                conn.Update(data);
            }
        }


        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="data"></param>
        public static void Save(dc_average_warehouse data, MyMySqlConnection conn)
        {
            if (conn == null)
            {
                conn = _connection;
            }

            data.update_time = DateTime.Now;

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", data.bailun_sku);
            parameters.Add("warehouse_code", data.warehouse_code);
            var oldData = conn.QueryFirstOrDefault(" select * from dc_average_warehouse where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", parameters);
            if (oldData == null)
            {
                conn.Insert(data);
            }
            else
            {
                data.ID = oldData.ID;
                conn.Update(data);
            }
        }
    }

    public class temp_sku_dto
    {
        public string bailun_sku { get; set; }

        public string warehouse_code { get; set; }

        public int? supplier_id { get; set; }

        public string hq_type { get; set; }

        public string buyer_name { get; set; }
    }
}
