using System;
using System.Collections.Generic;
using System.Text;
using Bailun.DC.Models;
using Bailun.DC.DB;
using Dapper;
using MySql.Data.MySqlClient;
using Bailun.DC.Models.Allot;

namespace Bailun.DC.Services
{
    public class AllotServices
    {
        /// <summary>
        /// 调拨单列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="status">调拨单状态</param>
        /// <param name="orderno">调拨单号</param>
        /// <param name="sku">sku编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Allot.mAllotOrder> ListAllot(BtTableParameter parameter, string warehouse, int? status, string orderno, string sku, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select t1.transfer_order_id,t1.`status`,t1.bailun_sku,t2.sku_image,t2.category_name,t2.sku_title_en,t2.sku_title_cn,t1.warehouse_code,t1.warehouse_name,t1.count,t1.create_time,t1.gmt_modified from dc_base_transfer_bale t1
                                left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";

            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            if (status.HasValue)
            {
                sql += " and t1.status="+status.Value;
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sql += " and t1.transfer_order_id=@transfer_order_id";
                sqlparam.Add("transfer_order_id", orderno);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sql += " order by t1." + parameter.sort;
                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    sql += " " + parameter.order;
                }
            }
            else
            {
                sql += " order by t1.create_time desc";
            }

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

                var obj = cn.Page<Models.Allot.mAllotOrder>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam);

                return obj.AsList();
            }
        }

        /// <summary>
        /// 调拨单 汇总
        /// </summary>
        /// <param name="warehouse"></param>
        /// <param name="status"></param>
        /// <param name="orderno"></param>
        /// <param name="sku"></param>
        /// <returns></returns>
        public Models.Allot.mAllotOrder ListAllotCount(string warehouse, int? status, string orderno, string sku)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select sum(t1.count) count from dc_base_transfer_bale t1
                                left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";

            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            if (status.HasValue)
            {
                sql += " and t1.status=" + status.Value;
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sql += " and t1.transfer_order_id=@transfer_order_id";
                sqlparam.Add("transfer_order_id", orderno);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirst<Models.Allot.mAllotOrder>(sql, sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取调拨单详情接口
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Allot.dc_base_transfer_info> ListAllot(int page, int pagesize, DateTime start, DateTime end, ref int total)
        {
            var sql = $"select * from dc_base_transfer_info where dc_createtime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and dc_createtime<='{end.ToString("yyyy-MM-dd HH:mm:ss")}'";

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

                var obj = cn.Page<Models.Allot.dc_base_transfer_info>(page,pagesize,sql,ref total);

                return obj.AsList();

            }
        }

        /// <summary>
        /// 获取调拨单的在途明细
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="orderno">调拨单号</param>
        /// <param name="bailun_sku">百伦SKU</param>
        /// <param name="warehousetype">目的仓库类型</param>
        /// <param name="warehousecode">目的仓库编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Allot.mAllotOrderSku> ListAllotOnWay(BtTableParameter parameter, string orderno,string bailun_sku,string warehousetype,string warehousecode,ref int total)
        {
            var sql = $@"select t1.code,t1.schedulecode,t1.stateid,t1.sourcewareno,t1.sourcewarename,t1.targetwareno,t1.targetwarename,t2.sku,t2.deliverycount,t2.storagecount,t2.deliverycount-t2.storagecount as onwaycount from dc_base_transfer_info t1 
                        join dc_base_transfer_info_skus t2 on t1.dataid=t2.dataid and t2.deliverycount>t2.storagecount
                        join dc_base_warehouse t3 on t1.targetwareno=t3.warehouse_code
                        where t1.stateid<5 and t1.isdeleted=0 and t1.isexception=0";

            var sqlparam = new DynamicParameters();

            if (!string.IsNullOrEmpty(orderno))
            {
                sql += " and t1.code=@code";
                sqlparam.Add("code", orderno);
            }

            if (!string.IsNullOrEmpty(bailun_sku))
            {
                sql += " and t2.sku=@sku";
                sqlparam.Add("sku", bailun_sku);
            }

            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " and t3.hq_type=@warehousetype";
                sqlparam.Add("warehousetype", warehousetype);
            }

            if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " and t1.targetwareno=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }


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

                if (parameter.limit == 0)
                {
                    var obj = cn.Query<Models.Allot.mAllotOrderSku>(sql, sqlparam);
                    return obj.AsList();
                }
                else
                {
                    var obj = cn.Page<Models.Allot.mAllotOrderSku>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam);

                    return obj.AsList();
                }
            }
        }

        /// <summary>
        /// 获取调拨单的在途明细汇总
        /// </summary>
        /// <param name="orderno">调拨单号</param>
        /// <param name="bailun_sku">百伦SKU</param>
        /// <param name="warehousetype">目的仓库类型</param>
        /// <param name="warehousecode">目的仓库编码</param>
        /// <returns></returns>
        public Models.Allot.mAllotOrderSku ListAllotOnWayCount(string orderno, string bailun_sku, string warehousetype, string warehousecode)
        {
            var sql = $@"select sum(t2.deliverycount) as deliverycount,sum(t2.storagecount) storagecount,sum(t2.deliverycount-t2.storagecount) onwaycount from dc_base_transfer_info t1 
                        join dc_base_transfer_info_skus t2 on t1.dataid=t2.dataid and t2.deliverycount>t2.storagecount
                        join dc_base_warehouse t3 on t1.targetwareno=t3.warehouse_code
                        where t1.stateid<5 and t1.isdeleted=0 and t1.isexception=0";

            var sqlparam = new DynamicParameters();

            if (!string.IsNullOrEmpty(orderno))
            {
                sql += " and t1.code=@code";
                sqlparam.Add("code", orderno);
            }

            if (!string.IsNullOrEmpty(bailun_sku))
            {
                sql += " and t2.sku=@sku";
                sqlparam.Add("sku", bailun_sku);
            }

            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " and t3.hq_type=@warehousetype";
                sqlparam.Add("warehousetype", warehousetype);
            }

            if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " and t1.targetwareno=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }


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

                var obj = cn.QueryFirstOrDefault<Models.Allot.mAllotOrderSku>(sql,sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取调拨计划在途
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="orderno">调拨单号</param>
        /// <param name="sku">百伦sku</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库名称</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<dc_base_transfer_plan> ListAllotPlanOnWay(BtTableParameter parameter, string orderno, string sku, string warehousetype, string warehousecode, ref int total)
        {
            var sql = $@"select t1.transfer_plan_order_id,t1.warehouse_code,t2.warehouse_name,t1.bailun_sku,t1.count from dc_base_transfer_plan t1 
                        join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        where t1.`status`=1 and t1.deleted=0";

            var sqlparam = new DynamicParameters();

            if (!string.IsNullOrEmpty(orderno))
            {
                sql += " and t1.transfer_plan_order_id=@orderno";
                sqlparam.Add("orderno", orderno);
            }

            if (!string.IsNullOrEmpty(sku))
            {
                sql += " and t1.bailun_sku=@sku";
                sqlparam.Add("sku", sku);
            }

            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " and t2.hq_type=@warehousetype";
                sqlparam.Add("warehousetype", warehousetype);
            }

            if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " and t1.warehouse_code=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }

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

                if (parameter.limit == 0)
                {
                    var obj = cn.Query<dc_base_transfer_plan>(sql, sqlparam);

                    return obj.AsList();
                }
                else
                {
                    var obj = cn.Page<dc_base_transfer_plan>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam);

                    return obj.AsList();
                }
                
            }
        }

        /// <summary>
        /// 获取调拨计划在途 合计
        /// </summary>
        /// <param name="orderno">调拨单号</param>
        /// <param name="sku">百伦sku</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库名称</param>
        /// <returns></returns>
        public dc_base_transfer_plan ListAllotPlanOnWayCount(string orderno, string sku, string warehousetype, string warehousecode)
        {
            var sql = $@"select sum(t1.count) count from dc_base_transfer_plan t1 
                        join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        where t1.`status`=1 and t1.deleted=0";

            var sqlparam = new DynamicParameters();

            if (!string.IsNullOrEmpty(orderno))
            {
                sql += " and t1.transfer_plan_order_id=@orderno";
                sqlparam.Add("orderno", orderno);
            }

            if (!string.IsNullOrEmpty(sku))
            {
                sql += " and t1.bailun_sku=@sku";
                sqlparam.Add("sku", sku);
            }

            if (!string.IsNullOrEmpty(warehousetype))
            {
                sql += " and t2.hq_type=@warehousetype";
                sqlparam.Add("warehousetype", warehousetype);
            }

            if (!string.IsNullOrEmpty(warehousecode))
            {
                sql += " and t1.warehouse_code=@warehousecode";
                sqlparam.Add("warehousecode", warehousecode);
            }

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

                var obj = cn.QueryFirstOrDefault<dc_base_transfer_plan>(sql,sqlparam);

                return obj;
            }
        }

    }
}
