using BLL.IDAL;
using Common;
using Commom.Utility;
using Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BLL.DAL
{
    public class DAL_PalletEmptyIn : IDAL_PalletEmptyIn
    {
        public IList<PalletEmptyIn> GetList(AjaxPalletEmptyIn Json, ref PageInfo page)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select * from View_IPalletEmptyIn where 1 = 1 and IsDel = 0");
                if (!string.IsNullOrEmpty(Json.Palno))
                {
                    strSql.Append(" and palno like '%" + Json.Palno + "%'");
                }
                if (Json.BeginTime != DateTime.MinValue && Json.BeginTime != null && Json.BeginTime != DateTime.MaxValue)
                {
                    strSql.Append(" and CreateTime >='" + Convert.ToDateTime(Json.BeginTime).ToShortDateString() + "'");
                }
                if (Json.EndTime != DateTime.MinValue && Json.EndTime != null && Json.EndTime != DateTime.MaxValue)
                {
                    strSql.Append(" and CreateTime <= '" + Convert.ToDateTime(Json.EndTime).ToShortDateString() + " 23:59:59.999'");
                }

                SqlParam[] param = null;
                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "desc", ref page);

                return ModelConvertHelper<PalletEmptyIn>.DataTableToModel(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 验证托盘是否重复使用
        /// </summary>
        /// <param name="PalletNo"></param>
        /// <returns></returns>
        public bool CheckPalletNo(string guid, string palno)
        {
            try
            {
                bool bl = false;
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append("select count(*) from IPalletEmptyIn ");
                sbStr.Append("where guid != '" + guid + "' and Palno = '" + palno + "' ");
                sbStr.Append("and Statu != '03' and IsDel = 0;");
                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbStr);
                if (dt != null)
                {
                    if (dt.Rows[0][0].ToString() != "0")
                    {
                        bl = true;
                    }
                }

                sbStr.Clear();
                sbStr.Append("select 1 from IPalletBind where Palno = '");
                sbStr.Append(palno);
                sbStr.Append("' and IsDel = 0 and Statu <> '03'");

                DataTable dt2 = DataFactory.SqlDataBase().GetDataTableBySQL(sbStr);
                if (dt2.Rows.Count > 0)
                {
                    bl = true;
                }
                //判断库位表是否存在重复托盘码
                //sbStr.Clear();
                //sbStr.Append("select TurnoverDemand from DepotsLocation where LocationCode in (");
                //sbStr.Append("select top 1 Addre from log_Store where Palno = '" + palno + "')");
                //DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(sbStr);
                //if (dtt.Rows.Count > 0)
                //{
                //    string tDemand = dtt.Rows[0][0].ToString();
                //    if (tDemand == "02" || tDemand == "04")
                //    {
                //        bl = true;
                //    }
                //}

                // 判断库中是否存在  是否已使用
                sbStr.Clear();
                sbStr.Append("select TurnoverDemand from DepotsLocation where LocationCode in (");
                sbStr.Append("select LocationCode from log_Store where Palno = '" + palno + "' AND IsDel=0)");
                DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(sbStr);
                if (dtt != null && dtt.Rows.Count > 0)
                {
                    string tDemand = dtt.Rows[0][0].ToString();
                    if (tDemand == "02" || tDemand == "04")
                    {
                        bl = true;
                    }
                }

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

        public bool Add(PalletEmptyIn model)
        {
            try
            {
                int rowCount = 0;
                bool result = false;
                if (!string.IsNullOrEmpty(model.Guid))
                {
                    StringBuilder stringBuilder = new StringBuilder();
                    stringBuilder.Append("select * from IPalletEmptyIn where IsDel = 0 and Statu = '01' and guid = '" + model.Guid + "'");
                    DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
                    if (model.LocationCode != null && model.LocationCode != "")
                    {
                        Hashtable ht2 = new Hashtable();
                        ht2["TurnoverDemand"] = "'03'";
                        int ret = DataFactory.SqlDataBase()
                            .UpdateByHashtable("DepotsLocation", "LocationCode", "'" + model.LocationCode + "'", ht2);
                    }
                    else
                    {
                        Hashtable ht2 = new Hashtable();
                        ht2["TurnoverDemand"] = "'01'";
                        int ret = DataFactory.SqlDataBase()
                            .UpdateByHashtable("DepotsLocation", "LocationCode", "'" + dt.Rows[0]["LocationCode"] + "'", ht2);
                    }

                    Hashtable ht = new Hashtable();
                    ht.Add("Palno", model.Palno.AddQuotes());
                    ht.Add("LocationCode", model.LocationCode.AddQuotes());
                    ht.Add("PalCount", model.PalCount.AddQuotes());
                    ht.Add("Demo", model.Demo.AddQuotes());
                    ht.Add("DepartGuid", model.DepartGuid.AddQuotes());
                    ht.Add("UpdateTime", "getDate()");
                    ht.Add("UpdateUser", model.CreateUser.AddQuotes());
                    rowCount = DataFactory.SqlDataBase().UpdateByHashtable("IPalletEmptyIn", "Guid", "'" + model.Guid + "'", ht);
                }
                else
                {
                    Hashtable ht = new Hashtable();
                    ht.Add("Palno", model.Palno);
                    ht.Add("LocationCode", model.LocationCode);
                    ht.Add("PalCount", model.PalCount);
                    ht.Add("Demo", model.Demo);
                    ht.Add("DepartGuid", model.DepartGuid);
                    ht.Add("Statu", "01");
                    ht.Add("CreateUser", model.CreateUser);
                    rowCount = DataFactory.SqlDataBase().InsertByHashtable("IPalletEmptyIn", ht);

                    if (model.LocationCode != null && model.LocationCode != "")
                    {
                        Hashtable ht2 = new Hashtable();
                        ht2["TurnoverDemand"] = "'03'";
                        int ret = DataFactory.SqlDataBase()
                            .UpdateByHashtable("DepotsLocation", "LocationCode", "'" + model.LocationCode + "'", ht2);
                    }

                }

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

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

        public bool IsCheckStatu(string guid,string LocationCode)
        {
            bool result = false;
            try
            {
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append("select statuCode from View_IPalletEmptyIn where guid = '" + guid + "';");
                DataRow dr = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
                if (dr[0].ToString() == "01")
                {
                    sbStr.Clear();
                    sbStr.Append("Update IPalletEmptyIn set IsDel = '1' where guid = '" + guid + "';");
                    DataFactory.SqlDataBase().ExecuteBySql(sbStr);

                    if (LocationCode != null && LocationCode != null)
                    {
                        Hashtable ht2 = new Hashtable();
                        ht2["TurnoverDemand"] = "'01'";
                        int ret2 = DataFactory.SqlDataBase()
                            .UpdateByHashtable("DepotsLocation", "LocationCode", "'" + LocationCode + "'", ht2);
                    }

                    result = true;
                }

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








        public bool Del(string PalletNo, int Id)
        {

            try
            {
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("Delete from IPalletEmptyIn where Statu= '1' and Id = @PalletNo");
                SqlParam[] param = new SqlParam[]
                {
                    new SqlParam("@PalletNo", Id),
                };

                int ret = DataFactory.SqlDataBase().ExecuteBySql(strSQL, param);

                
                if (ret < 0)
                {

                    return false;
                }

            }
            catch
            {
                return true;
            }
            return true;
        }

        public bool ChectStatus(string PalletNo, string status)
        {
            bool r = false;

            StringBuilder strSQL = new StringBuilder();

            strSQL.Append("select * from View_IPalletEmptyIn where PalletNo = '");
            strSQL.Append(PalletNo);
            strSQL.Append("' and Statu = '");
            strSQL.Append(status);
            strSQL.Append("'");


            DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSQL);
            if (dt == null)
            {
                return false;
            }
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}