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

namespace BLL
{
    public class DALCheckData : IDALCheckData
    {
        public IList<CheckData> GetList(AjaxCheckDataList Json, ref PageInfo page)
        {
            try
            {
                IList<CheckData> list = new List<CheckData>();
                StringBuilder strSql = new StringBuilder();
                List<SqlParam> para = new List<SqlParam>();
                strSql.Append("Select * from View_CheckData_User where IsDel = 0 " + $" AND DepartGuid='{Json.DepartGuid}' ");

                if (!string.IsNullOrEmpty(Json.CDNo))
                {
                    strSql.Append("and CDNo like '%' + @CDNo + '%' ");
                    para.Add(new SqlParam("@CDNo", Json.CDNo));
                }

                if (!string.IsNullOrEmpty(Json.Statu))
                {
                    strSql.Append("and StatuId = @Statu ");
                    para.Add(new SqlParam("@Statu", Json.Statu));
                }

                if (Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MaxValue)
                {
                    strSql.Append("and CreatTime >= @CreateTime1 ");
                    para.Add(new SqlParam("@CreateTime1", Convert.ToDateTime(Json.BeCreateTime).ToShortDateString()));
                }

                if (Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MaxValue)
                {
                    strSql.Append("and CreatTime <= @CreateTime2 ");
                    para.Add(new SqlParam("@CreateTime2", Convert.ToDateTime(Json.EnCreateTime).ToShortDateString() + " 23:59:59.999"));
                }

                SqlParam[] param = null;
                if (para != null)
                    param = para.ToArray();

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreatTime", "desc", ref page);
                list = ModelConvertHelper<CheckData>.DataTableToModel(dt);

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

        }

        public CheckData GetModel(string CDNo)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append($"SELECT * FROM dbo.View_CheckData_User WHERE CDNO='{CDNo}'");
                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);

                return ModelConvertHelper<CheckData>.ReaderToModel(dt);
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public DataTable GetCheckData(string CDNo)
        {
            try
            {
                StringBuilder cdStr = new StringBuilder();
                cdStr.Append("select tb1.CDNo,tb2.TypeName as Statu,tb1.Demo,tb1.CreatTime,tb3.RealName as Creatuser ");
                cdStr.Append("from CheckData as tb1 ");
                cdStr.Append("left join Dictionary as tb2 on tb1.Statu = tb2.code ");
                cdStr.Append("left join UserInfo as tb3 on tb1.CreatUser = tb3.UserCode ");
                cdStr.Append("where tb1.isdel = 0 and tb2.topCode = 'CDStatu' and tb1.CDNo = " + CDNo.AddQuotes());
                DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(cdStr);

                return dtt;
            }
            catch
            {
                return null;
            }
        }

        public CheckData GetModel(string CDNo, ref IList<CheckMat> erpMats)
        {
            try
            {
                CheckData us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select * from CheckData where ");
                strSql.Append("CDNo = @CDNo and IsDel = 0 ");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@CDNo", CDNo),
                };

                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);

                us = ModelConvertHelper<CheckData>.ReaderToModel(dt);

                DALCheckDetail erpInDetail = new DALCheckDetail();
                erpMats = erpInDetail.GetList(CDNo);

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

        public bool Add(CheckData model, List<CheckMat> Mats)
        {
            bool result = false;
            try
            {
                Hashtable ht1 = new Hashtable();
                Hashtable rs = new Hashtable();
                ht1["sCode"] = "CDNO";
                ht1["OUT_sResult"] = "";
                int num = DataFactory.SqlDataBase().ExecuteByProcReturn("GetSerialNo", ht1, ref rs);
                if (num == 0)
                {
                    return result;
                }
                string CDNo = rs["OUT_sResult"].ToString();

                Hashtable ht = new Hashtable();
                ht["CDNo"] = CDNo.AddQuotes();
                ht["Statu"] = "'01'";
                ht["AccessCode"] = model.AccessCode.AddQuotes();
                ht["DepartGuid"] = model.DepartGuid.AddQuotes();
                ht["CreatUser"] = model.CreatUser.AddQuotes();
                ht["Demo"] = model.Demo.AddQuotes();
                int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("CheckData", ht);
                if (_ret != 1)
                {
                    return result;
                }

                result = this.SetCDDetail(CDNo, Mats, model.CreatUser);

                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool Update(CheckData model, List<CheckMat> Mats)
        {
            bool result = false;
            try
            {
                // 修改总表
                Hashtable ht = new Hashtable();
                ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";
                ht["AccessCode"] = model.AccessCode.AddQuotes();
                int _ret = DataFactory.SqlDataBase().UpdateByHashtable("CheckData", "CDNo", model.CDNo.AddQuotes(), ht);
                if (_ret == 1)
                {
                    StringBuilder sqlStr = new StringBuilder();
                    sqlStr.Append("Delete From CheckDataDetail where CDNo = " + model.CDNo.AddQuotes() + ";");
                    int rowCount = DataFactory.SqlDataBase().ExecuteBySql(sqlStr);
                    if (rowCount > 0)
                    {
                        result = this.SetCDDetail(model.CDNo, Mats, model.CreatUser);
                    }
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool UpdateAudit(CheckData movePos)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();


                ht["AuditStatu"] = "'AD01'";
                ht["Statu"] = "'ER04'";
                ht["CDNo"] = "'" + movePos.CDNo + "'";


                Hashtable ht1 = new Hashtable();
                ht1["AuditStatu"] = "'" + movePos.AuditStatu + "'";

                ht1["AuditUser"] = "'" + movePos.AuditUser + "'";


                int _ret = DataFactory.SqlDataBase().UpdateByHashtable("CheckData", ht1, ht);
                if (_ret == 1) result = true;

                return result;

            }
            catch
            {
                return result;
            }
        }

        public bool UpdateClose(string[] CDNo, string Statu)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();
                ht["Statu"] = "ER03";

                int dt = DataFactory.SqlDataBase().IsExist("CheckData", "CDNo", CDNo, ht);
                if (dt >= CDNo.Length)
                {
                    int _ret = DataFactory.SqlDataBase().BatchDeleteData("CheckData", "CDNo", CDNo);
                    if (_ret >= CDNo.Length) result = true;
                }
                return result;

            }
            catch
            {
                return result;
            }
        }

        public bool IsExist(string name, string value)
        {
            bool result = false;
            try
            {
                string[] para = new string[] { value };
                int dt = DataFactory.SqlDataBase().IsExist("CheckData", name, para);
                if (dt > 0) result = true;
                return result;
            }
            catch
            {
                return result;
            }

        }

        public DataTable GetDataTable(string[] strWhere)
        {
            DataTable dt = null;
            try
            {
                int index = 0;
                string str = "@RoleNum" + index;
                SqlParam[] param = new SqlParam[strWhere.Length];
                StringBuilder sql = new StringBuilder();
                sql.Append("Select RoleNum as 角色编号, RoleName as 角色名,CreatUser as 创建人,CreatTime as 创建时间,UpdateUser as 更新人,UpdateTime as 更新时间,Demo as 备注 FROM View_GetRoles where RoleNum in (");

                for (int i = 0; i < param.Length - 1; i++)
                {
                    string obj2 = strWhere[i];
                    str = "@RoleNum" + index;
                    sql.Append(str).Append(",");
                    param[index] = new SqlParam(str, obj2);
                    index++;
                }
                str = "@RoleNum" + index;
                sql.Append(str);
                param[index] = new SqlParam(str, strWhere[index]);
                sql.Append(")");

                dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql, param, "");

                return dt;

            }
            catch
            {
                throw new NotImplementedException();
            }

        }

        public bool BatchDelete(string[] CDNo)
        {
            bool result = false;
            try
            {
                foreach (string cn in CDNo)
                {
                    StringBuilder sql = new StringBuilder();
                    sql.Append("update CheckData set IsDel=1 where CDNO=" + cn.AddQuotes() + ";");
                    sql.Append("Update CheckDataDetail set IsDel = 1 where CDNO=" + cn.AddQuotes() + ";");
                    int _ret = DataFactory.SqlDataBase().ExecuteBySql(sql);
                    if (_ret > 0)
                    {
                        result = true;
                    }
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        public IList<AjaxStoreSel> GetMaterial(AjaxStoreSel Json)
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select * from View_Store_Mat where ISNULL(MatNo,'0') <> '0' " + $" AND DepartGuid='{Json.DepartGuid}' ");
                // 库区
                if (!string.IsNullOrEmpty(Json.MatNo))
                {
                    stringBuilder.Append(" and MatNo like '%" + Json.MatNo + "%'");
                }
                if (!string.IsNullOrEmpty(Json.MatName))
                {
                    stringBuilder.Append(" and MatName like '%" + Json.MatName + "%'");
                }
                if (!string.IsNullOrEmpty(Json.LingNo))
                {
                    stringBuilder.Append(" and LingNo like '%" + Json.LingNo + "%'");
                }
                if (!string.IsNullOrEmpty(Json.LocationCode))
                {
                    stringBuilder.Append(" and LocationCode like '%" + Json.LocationCode + "%'");
                }
                if (!string.IsNullOrEmpty(Json.BatchNo))
                {
                    stringBuilder.Append(" and BatchNo like '%" + Json.BatchNo + "%'");
                }
                if (!string.IsNullOrEmpty(Json.BatchName))
                {
                    stringBuilder.Append(" and BatchName like '%" + Json.BatchName + "%'");
                }


                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
                IList<AjaxStoreSel> list = ModelConvertHelper<AjaxStoreSel>.DataTableToModel(dt);

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

        public bool SetCDDetail(string CDNo, List<CheckMat> EMats, string LoginUserCode)
        {
            bool result = false;
            try
            {
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();

                strSql.Append(" Insert into CheckDataDetail ");
                strSql.Append("(CDNO,MatNo,MatName,PackFormat,Unit,Quant,LocationCode,Palno,LingNo,CreatUser)");
                strSql.Append(" Values ");

                foreach (CheckMat emat in EMats)
                {
                    strSql.Append("(" + CDNo.AddQuotes() + "," + emat.MatNo.AddQuotes());
                    strSql.Append("," + emat.MatName.AddQuotes() + "," + emat.PackFormat.AddQuotes());
                    strSql.Append("," + emat.UnitFrist.AddQuotes() + "," + emat.Quant.AddQuotes());
                    strSql.Append("," + emat.LocationCode.AddQuotes() + "," + emat.Palno.AddQuotes());
                    strSql.Append("," + emat.LingNo.AddQuotes() + "," + LoginUserCode.AddQuotes() + "),");
                }

                SqlParam[] param = null;
                if (EMats.Count > 0)
                {
                    strSql = strSql.Remove(strSql.Length - 1, 1);
                    int rowCount = DataFactory.SqlDataBase().ExecuteBySql(strSql, param);
                    if (EMats.Count == rowCount)
                    {
                        result = true;
                    }
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        /// <summary>
        /// 生成盘库任务
        /// </summary>
        /// <param name="CDNo"></param>
        /// <param name="loginUserCode"></param>
        /// <returns></returns>
        public bool BuildTask(string CDNo, string loginUserCode, string departGuid)
        {
            try
            {
                StringBuilder cdStr = new StringBuilder();
                cdStr.Append("select LocationCode,Palno,Demo from [dbo].[CheckDataDetail] ");
                cdStr.Append("where CDNO = " + CDNo.AddQuotes());
                cdStr.Append("group by LocationCode,Palno,Demo;");
                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(cdStr);
                if (dt.Rows.Count <= 0)
                {
                    return false;
                }
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"
INSERT INTO dbo.CheckTask
(
    CDNO,
    LocationCode1,
    Palno1,
    statu,
    Demo,
    DepartGuid,
    CreatTime,
    CreatUser
)
VALUES
");
                foreach (DataRow dataRow in dt.Rows)
                {
                    strSql.Append($@"
(
'{CDNo}',
'{dataRow["LocationCode"].ToString()}',
'{dataRow["Palno"].ToString()}',
'01',
'{dataRow["Demo"].ToString()}',
'{departGuid}',
GETDATE(),
'{loginUserCode}'
),");
                }

                strSql = strSql.Remove(strSql.Length - 1, 1);
                strSql.Append(";Update CheckData set Statu = '02' where CDNo=" + CDNo.AddQuotes() + ";");
                int rowCount = DataFactory.SqlDataBase().ExecuteBySql(strSql);
                if (rowCount > 0)
                {
                    return true;
                }

                return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据盘库单更改库存数量
        /// </summary>
        /// <param name="CDNo"></param>
        /// <param name="loginUserCode"></param>
        /// <returns></returns>
        public bool AdjustInventory(string CDNo, string loginUserCode)
        {
            bool result = false;
            try
            {
                // 获取盘点明细
                StringBuilder cdStr = new StringBuilder();
                cdStr.Append("select MatNo,LingNo,LocationCode,Palno,Quant,guid,AQuant ");
                cdStr.Append("from CheckDataDetail ");
                cdStr.Append("where case isnull(AQuant,'') when '' then '-1' else AQuant end <> -1 and IsDel = 0 and CDNO = " + CDNo.AddQuotes() + ";");
                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(cdStr);

                // 循环取得每种物料的盘点量
                int rowcount = 0;
                int rowNum = 0;
                StringBuilder setSql = new StringBuilder();
                foreach (DataRow row in dt.Rows)
                {
                    int quant = int.Parse(row["Quant"].ToString());
                    int aQuant = int.Parse(row["AQuant"].ToString());
                    string value = (aQuant - quant).ToString();

                    // 插入出入库明细表
                    setSql.Append("Insert Into InOutDetails (");
                    setSql.Append("OrdNo,FkGuid,LingNo,LocationCode,Palno,MatNo,Quant,CreateUser)");
                    setSql.Append(" values (" + CDNo.AddQuotes() + "," + row["guid"].ToString().AddQuotes() + ",");
                    setSql.Append(row["LingNo"].ToString().AddQuotes() + ",");
                    setSql.Append(row["LocationCode"].ToString().AddQuotes() + ",");
                    setSql.Append(row["Palno"].ToString().AddQuotes() + ",");
                    setSql.Append(row["MatNo"].ToString().AddQuotes() + ",");
                    setSql.Append(value.AddQuotes() + "," + loginUserCode.AddQuotes() + "); ");
                    // 修改库存明细表
                    setSql.Append("Update log_Store set Quant = Quant + " + value);
                    setSql.Append(" where LocationCode = " + row["LocationCode"].ToString().AddQuotes());
                    setSql.Append(" and isnull(LingNo,'') = " + row["LingNo"].ToString().AddQuotes());
                    setSql.Append(" and Palno = " + row["Palno"].ToString().AddQuotes());
                    setSql.Append(" and isnull(MatNo,'') = " + row["MatNo"].ToString().AddQuotes() + "; ");
                    // 修改库存总表
                    if (row["MatNo"].ToString() != "")
                    {
                        setSql.Append("Update Log_Stroe_Mat set Quant = Quant + " + value);
                        setSql.Append(" where MatNo = " + row["MatNo"].ToString().AddQuotes() + "; ");
                    }

                    rowcount += 1;
                    if (rowcount == 10)
                    {
                        rowNum = DataFactory.SqlDataBase().ExecuteBySql(setSql);
                        if (rowNum > 0)
                        {
                            setSql.Clear();
                            rowcount = 0;
                            rowNum = 0;
                        }
                    }
                }

                if (rowcount > 0)
                {
                    rowNum = DataFactory.SqlDataBase().ExecuteBySql(setSql);
                    if (rowNum > 0)
                    {
                        setSql.Clear();
                        rowNum = 0;
                    }
                }

                // 更改盘点单状态 、逻辑删除指令
                setSql.Append("Update CheckData set Statu = '03',COMTime=getdate() where CDNO =" + CDNo.AddQuotes() + "; ");
                // 删除任务状态。
                setSql.Append("Update CheckTask set isDel = '1' where CDNO =" + CDNo.AddQuotes() + "; ");
                // 删除相关任务指令
                //setSql.Append("Update WH_CMD set IsDel = '1' where NumberNo =" + CDNo.AddQuotes() + ";");
                rowNum = DataFactory.SqlDataBase().ExecuteBySql(setSql);
                if (rowNum > 0)
                {
                    result = true;
                }
            }
            catch
            {
                return false;
            }

            return result;
        }
    }
}