using Commom.Utility;
using Common;
using Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BLL.DAL
{
    public class DALDepotsLocation
    {
        public DataTable GetDictionaryItems() 
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select * from Dictionary where IsDel = 0 and topCode is not null ");
                stringBuilder.Append("order by Ord ;");

                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
                return dt;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public IList<DepotsLocation> GetDepotsLocationItems(QueryDLocation dLocation, ref PageInfo page) 
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select * from View_RepotsLocation_DepotsArea where IsDel = 0 ");
                // 库区
                if (dLocation.DepotsAreaID != null && dLocation.DepotsAreaID != "")  
                {
                    stringBuilder.Append(" and DepotsAreaID = " + dLocation.DepotsAreaID.AddQuotes());
                }
                // 排、列、层
                if (dLocation.LRow != 0) 
                {
                    stringBuilder.Append(" and LRow = " + dLocation.LRow);
                }
                if (dLocation.LColumn != 0)
                {
                    stringBuilder.Append(" and LColumn = " + dLocation.LColumn);
                }
                if (dLocation.LLayer != 0)
                {
                    stringBuilder.Append(" and LLayer = " + dLocation.LLayer);
                }
                // 编码及库位各属性
                if (dLocation.LocationCode != null && dLocation.LocationCode != "")  
                {
                    stringBuilder.Append(" and LocationCode like '%" + dLocation.LocationCode+ "%'");
                }
                if (dLocation.PropertyID != null && dLocation.PropertyID != "")
                {
                    stringBuilder.Append(" and PropertyID = " + dLocation.PropertyID.AddQuotes());
                }
                if (dLocation.TurnoverDemandID != null && dLocation.TurnoverDemandID != "")
                {
                    stringBuilder.Append(" and TurnoverDemandID = " + dLocation.TurnoverDemandID.AddQuotes());
                }
                if (dLocation.TypeID != null && dLocation.TypeID != "")
                {
                    stringBuilder.Append(" and TypeID = " + dLocation.TypeID.AddQuotes());
                }

                SqlParam[] para = new SqlParam[]
                {
                };

                DataTable dt = DataFactory.SqlDataBase().GetPageList(stringBuilder.ToString(), para, "LocationCode", "DESC", ref page);
                IList<DepotsLocation> list = ModelConvertHelper<DepotsLocation>.DataTableToModel(dt);

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IList<DepotsLocation> GetDepotsLocationItems(string guid) 
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select * from View_RepotsLocation_DepotsArea where IsDel = 0  ");
                if (guid != null && guid != "")
                {
                    stringBuilder.Append(" and Guid = " + guid.AddQuotes());
                }

                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
                return ModelConvertHelper<DepotsLocation>.DataTableToModel(dt);
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public bool SetDLocations(DepotsLocation DLocation, string loginUser)
        {
            try
            {
                bool bl = false;
                int rowCount = 0;

                Hashtable ht = new Hashtable();
                ht["Type"] = DLocation.Type.AddQuotes();
                ht["Property"] = DLocation.Property.AddQuotes();
                ht["TurnoverDemand"] = DLocation.TurnoverDemand.AddQuotes();        // 库位状态

                ht["Long"] = DLocation.Long;
                ht["Width"] = DLocation.Width;
                ht["Height"] = DLocation.Height;
                ht["IsProduct"] = DLocation.IsProduct;
                ht["IsBatch"] = DLocation.IsBatch;
                ht["Weight"] = DLocation.Weight;
                ht["Volume"] = DLocation.Volume;
                ht["Demo"] = DLocation.Demo.AddQuotes();
                ht["UpdateTime"] = "convert(varchar(20),getdate(),120)";
                ht["UpdateUser"] = loginUser.AddQuotes();

                rowCount = DataFactory.SqlDataBase().UpdateByHashtable("DepotsLocation",
                    "Guid", DLocation.Guid.AddQuotes(), ht);
                

                if (rowCount > 0)
                {
                    bl = true;
                }

                return bl;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public DataTable GetChartItmes(string pai) 
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();

                stringBuilder.Append(@"select (LColumn-1) as LColumn,(LLayer-1) as LLayer,
case cast(TurnoverDemand as int) when 2 then 
	case (select top 1 isnull(MatNo,'') from log_Store where LocationCode = tb1.LocationCode) when '' then 5 else cast(TurnoverDemand as int) end 
else cast(TurnoverDemand as int) end as TurnoverDemand
from DepotsLocation as tb1
where LRow = '"+pai+"' and Property in ('02','03');");

                // liudl 增加空托盘展示
                //stringBuilder.Append("select (LColumn-1) as LColumn,(LLayer-1) as LLayer, " +
                //    "cast(TurnoverDemand as int) as TurnoverDemand from DepotsLocation where LRow = '" + pai +"' ");
                //stringBuilder.Append("and Property in ('02','03');");

                return DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
            }
            catch
            {
                throw new NotImplementedException();
            }
        }
        /// <summary>
        /// 获取立体库货位状态
        /// </summary>
        /// <returns></returns>
        public DataTable GetCharts() 
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select cast(TurnoverDemand as int) as TurnoverDemand,COUNT(*) as rowNum " +
                    "from DepotsLocation group by cast(TurnoverDemand as int); ");

                return DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// 删除区域(逻辑删除)
        /// </summary>
        /// <param name="regionCodes"></param>
        /// <param name="loginUser"></param>
        /// <returns>true:成功 false:失败</returns>
        public bool DelLocations(string[] DepotsAreaGuids, string loginUser)
        {
            try
            {
                bool bl = false;
                int rowCount = 0;
                Hashtable ht = new Hashtable();
                foreach (string DepotsAreaGuid in DepotsAreaGuids)
                {
                    ht["IsDel"] = 1;
                    rowCount += DataFactory.SqlDataBase().UpdateByHashtable("DepotsLocation",
                            "Guid", DepotsAreaGuid.AddQuotes(), ht);
                }

                if (rowCount > 0)
                {
                    bl = true;
                }

                return bl;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// 验证库位是否操作员所属部门占用  
        /// </summary>
        /// <param name="Guid"></param>
        /// <param name="DepartGuid"></param>
        /// <returns>true：是本部门 False：不是本部门</returns>
        public bool CheckDepartment(string Guid,string DepartGuid) 
        {
            try
            {
                bool bl = false;
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($"select count(*) from log_Store where IsDel = '0' and DepartGuid = '"+ DepartGuid + "' " +
                    $"and LocationCode in (select LocationCode from DepotsLocation where guid = '"+ Guid + "') ");
                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);

                if (dt.Rows[0][0].ToString() == "0")
                {
                    bl = true;
                }

                return bl;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }
    }
}