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

namespace BLL
{
    public class DALDepartMent:IDALDepartMent
    {
        public IList<DepartMent> GetList()
        {
            try
            {
                IList<DepartMent> ls = new List<DepartMent>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select guid,(DepartNum+'-'+DepartName) as DepartName from DepartMent where IsDel != 1;");

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

                ls = ModelConvertHelper<DepartMent>.DataReaderToModel(dt);

                return ls;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }
        public IList<DepartMent> GetList(AjaxDepartList Json, ref PageInfo page)
        {
            try
            {

                IList<DepartMent> list = new List<DepartMent>();
                StringBuilder strSql = new StringBuilder();
                List<SqlParam> para = new List<SqlParam>();
                strSql.Append("Select * from View_GetDepart ");

                if (Json.DepartNum != null && Json.DepartNum != "")
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("DepartNum like '%' + @DepartNum + '%' ");
                    para.Add(new SqlParam("@DepartNum", Json.DepartNum));
                }

                if (Json.DepartName != null && Json.DepartName != "")
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("DepartName like '%' + @DepartName + '%'  ");
                    para.Add(new SqlParam("@DepartName", Json.DepartName));
                }

                if (para.Count < 1)
                {
                    strSql.Append(" Where  IsDel!=1");
                }
                else
                {
                    strSql.Append(" and IsDel!=1");
                }

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

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "Ord", "DESC", ref page);
                list = ModelConvertHelper<DepartMent>.DataTableToModel(dt);

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

        }
        public DepartMent GetModel(string DepartNum)
        {
            try
            {
                DepartMent us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select DepartNum,DepartName,AccessCode,IsDel,Demo from View_GetDepart where ");
                strSql.Append("DepartNum = @DepartNum ");
                strSql.Append("and IsDel != 1");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@DepartNum", DepartNum),
                };

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

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

                return us;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }
        public bool Add(DepartMent model)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();
                ht["DepartNum"] = model.DepartNum.AddQuotes();
                ht["DepartName"] = model.DepartName.AddQuotes();
                ht["AccessCode"] = model.AccessCode.AddQuotes();
                ht["Ord"] = "(select isnull(Max(Ord),0) from DepartMent) + 1";
                ht["IsDel"] = 0;
                ht["CreatUser"] = "'" + model.CreatUser + "'";
                ht["UpdateTime"] = "GetDate()";
                ht["CreatTime"] = "GetDate()";
                ht["Demo"] = "'" + model.Demo + "'";
                ht["UpdateUser"] = "'" + model.CreatUser + "'";

                int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("DepartMent", ht);
                if (_ret == 1) 
                {
                    result = true;
                } 

                return result;
            }
            catch
            {
                return result;
            }
        }
        public bool Update(DepartMent model)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();
                ht["DepartName"] = string.IsNullOrEmpty(model.DepartName) ? "''" : "'" + model.DepartName + "'";
                ht["DepartNum"] = string.IsNullOrEmpty(model.DepartName) ? "''" : "'" + model.DepartNum + "'";
                ht["AccessCode"] = model.AccessCode.AddQuotes();
                ht["UpdateTime"] = "GetDate()";
                ht["UpdateUser"] = string.IsNullOrEmpty(model.UpdateUser) ? "''" : "'" + model.UpdateUser + "'";
                ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";
                string ID = "'" + model.ID + "'";

                int _ret = DataFactory.SqlDataBase().UpdateByHashtable("DepartMent", nameof(model.ID), ID, ht);
                if (_ret == 1) 
                {
                    result = true;
                } 

                return result;
            }
            catch
            {
                return result;
            }
        }
        public bool IsExist(AjaxDepartInfo model)
        {
            bool result = false;
            try
            {
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append("select count(id) from DepartMent");
                sbStr.Append(" where isdel = 0 and (DepartNum  = "+model.DepartNum.AddQuotes());
                sbStr.Append(" or DepartName = " + model.DepartName.AddQuotes() + ") ");
                if (model.Operation == "Edit") 
                {
                    sbStr.Append("and DepartNum != " + model.DepartNum.AddQuotes() + ";");
                }

                DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
                if (row[0].ToString() != "0")
                {
                    result = true;
                } 

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

                for (int i = 0; i < param.Length - 1; i++)
                {
                    string obj2 = strWhere[i];
                    str = "@DepartNum" + index;
                    sql.Append(str).Append(",");
                    param[index] = new SqlParam(str, obj2);
                    index++;
                }
                str = "@DepartNum" + 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[] DepartNum)
        {
            bool result = false;
            try
            {
                //int dt = DataFactory.SqlDataBase().IsExist("DepartMent", "ID", DepartNum);
                //if (dt >= DepartNum.Length)
                //{
                    StringBuilder sql = new StringBuilder();
                    foreach (string departNum in DepartNum) 
                    {
                        sql.Append("update DepartMent set IsDel=1 where ID='" + departNum + "';");
                    }

                    int _ret = DataFactory.SqlDataBase().ExecuteBySql(sql);
                    if (_ret >= DepartNum.Length) 
                    {
                        result = true;
                    }
                //}
                return result;
            }
            catch
            {
                return result;
            }
        }

        /// <summary>
        /// 验证该部门是否包含员工
        /// </summary>
        /// <param name="DepartNum"></param>
        /// <returns></returns>
        public string IsChecks(string[] DepartNum)
        {
            try
            {
                string result = "";
                foreach (string departNum in DepartNum)
                {
                    StringBuilder sql = new StringBuilder();
                    // 验证是否存在获取部门信息
                    sql.Append("select guid,DepartNum+DepartName as name,isdel from DepartMent where id = '" + departNum + "';");
                    DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(sql);
                    if (row["guid"].ToString() == "1") 
                    {
                        result += row["name"].ToString() + ":已删除!";
                        continue;
                    }

                    sql.Clear();
                    sql.Append("select count(*) from userinfo where DepartNum = '"+ row["guid"].ToString()+"';");       
                    DataRow rowUser = DataFactory.SqlDataBase().GetDataRowBySQL(sql);
                    if (rowUser[0].ToString() != "0")
                    {
                        result += row["name"].ToString() + ":请先移除所属员工。";
                    }
                }

                return result;
            }
            catch
            {
                return "错误";
            }
        }
    }
}