using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    /// <summary>
    /// 员工数据访问类
    /// </summary>
    public class UsersService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<Users> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<Users>.excuteSql(new Users(), "Users", sql, CommandType.Text, param);
        }


        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        Users excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<Users> usersList = excuteSql(sql, param);
            //判断集合是否为空
            if (usersList == null || usersList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return usersList[0];
        }


        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public Users GetUsersByID(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Users where Id = @id", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 获取全部有效员工，排除掉管理员
        /// </summary>
        /// <returns></returns>
        public List<Users> GetAll()
        {
            return excuteSql("select * from Users Where IsDel = 0 and Id<>4");
        }
        /// <summary>
        /// 获取全部有效员工，包含管理员
        /// </summary>
        /// <returns></returns>
        public List<Users> GetAllGM()
        {
            return excuteSql("select * from Users Where IsDel = 0");
        }


        /// <summary>
        /// 获取全部有效员工-主管级别
        /// </summary>
        /// <returns></returns>
        public List<Users> GetAll(string id)
        {
            return excuteSql("select * from Users Where IsDel = 0 and id in (" + id + ")");
        }

        /// <summary>
        /// 根据部门获取全部有效员工
        /// </summary>
        /// <returns></returns>
        public List<Users> GetAll(int did)
        {
            return excuteSql("select * from Users Where IsDel = 0 and Did = @Did", new SqlParameter("@Did", did));
        }

        /// <summary>
        /// 根据条件获取全部员工
        /// </summary>
        /// <param name="userNumber"></param>
        /// <returns></returns>
        public Users GetEntity(string userNumber)
        {
            return excuteType("select * from Users Where Number = @UserNumber and IsDel = 0", new SqlParameter("@UserNumber", userNumber));
        }

        /// <summary>
        /// 获取登录信息
        /// </summary>
        /// <returns></returns>
        public Users GetEntityByUserNoAndPwd(string userNo, string pwd)
        {
            return excuteType("select * from Users Where Number = @userNo and Password = @Password and IsDel = 0", new SqlParameter("@userNo", userNo), new SqlParameter("@Password", pwd));
        }

        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Users> GetAll(int pageIndex, out int sumPage, out int totalRecord, string departmentType, string name)
        {
            string sqlwhere = "";

            if (departmentType != "全部")
                sqlwhere += "Did = " + departmentType + " and IsDel = 0";
            if (!string.IsNullOrEmpty(name) && departmentType != "全部")
                sqlwhere += " and CnName like '%" + name + "%' and IsDel = 0";
            if (!string.IsNullOrEmpty(name) && departmentType == "全部")
                sqlwhere += "CnName like '%" + name + "%' and IsDel = 0";
            if (string.IsNullOrEmpty(name) && departmentType == "全部")
                sqlwhere += "CnName like '%" + name + "%' and IsDel = 0";

            return PageBase<Users>.excutePageSql(new Users(), "Users", "Users", "*", "id asc", sqlwhere, 15, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// 所有员工IsDel=1
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Users> GetAllNoIsDel(int pageIndex, out int sumPage, out int totalRecord, string departmentType, string name)
        {
            string sqlwhere = "";

            if (departmentType != "全部")
                sqlwhere += "Did = " + departmentType + " ";
            if (!string.IsNullOrEmpty(name) && departmentType != "全部")
                sqlwhere += " and CnName like '%" + name + "%'";
            if (!string.IsNullOrEmpty(name) && departmentType == "全部")
                sqlwhere += "CnName like '%" + name + "%'";
            if (string.IsNullOrEmpty(name) && departmentType == "全部")
                sqlwhere += "CnName like '%" + name + "%'";

            return PageBase<Users>.excutePageSql(new Users(), "Users", "Users", "*", "id asc", sqlwhere, 15, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool AddUsers(Users u)
        {
            string sql = "insert into Users values(@CnName,@EnName,@Number,@Did,@Password,@Sex,@Post,@Ext,@Phone,@UrgentPhone,@Email,@Address,@Edate,@Rdate,@Seniority,@Remark,@IsDel,@Company,@Birthday,@IDCard,@StartWorkDate,@Education,@Professional,@GraduateInstitutions,@TheOrAdultEducation,@MaritalStatus,@HomeAddress,@UsePeriod,@WorkExperience,@CertificateTime,@Certificate)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@CnName",u.CnName),
                new SqlParameter("@EnName",u.EnName),
                new SqlParameter("@Number",u.Number),
                new SqlParameter("@Did",u.Did),
                new SqlParameter("@Password",u.Password),
                new SqlParameter("@Sex",u.Sex),
                new SqlParameter("@Post",u.Post),
                new SqlParameter("@Ext",u.Ext),
                new SqlParameter("@Phone",u.Phone),
                new SqlParameter("@UrgentPhone",u.UrgentPhone),
                new SqlParameter("@Email",u.Email),
                new SqlParameter("@Address",u.Address),
                new SqlParameter("@Edate",u.Edate),
                new SqlParameter("@Rdate",u.Rdate),
                new SqlParameter("@Remark",u.Remark),
                new SqlParameter("@Seniority",u.Seniority),
                new SqlParameter("@IsDel",u.IsDel),
                new SqlParameter("@Company",u.Company),
                new SqlParameter("@Birthday",u.Birthday),
                new SqlParameter("@IDCard",u.IDCard),
                new SqlParameter("@StartWorkDate",u.StartWorkDate),
                new SqlParameter("@Education",u.Education),
                new SqlParameter("@Professional",u.Professional),
                new SqlParameter("@GraduateInstitutions",u.GraduateInstitutions),
                new SqlParameter("@TheOrAdultEducation",u.TheOrAdultEducation),
                new SqlParameter("@MaritalStatus",u.MaritalStatus),
                new SqlParameter("@HomeAddress",u.HomeAddress),
                new SqlParameter("@UsePeriod",u.UsePeriod),
                new SqlParameter("@WorkExperience",u.WorkExperience),
                new SqlParameter("@CertificateTime",u.CertificateTime),
                new SqlParameter("@Certificate",u.Certificate)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditUsers(Users u)
        {
            string sql = "update Users set CnName = @CnName,EnName = @EnName,Number = @Number,Did = @Did,Password = @Password,Sex = @Sex,Post = @Post,Ext = @Ext,Phone = @Phone,UrgentPhone = @UrgentPhone,Email = @Email,Address = @Address,Edate= @Edate,Rdate = @Rdate,Remark=@Remark,Seniority=@Seniority,IsDel=@IsDel,Company=@Company,Birthday=@Birthday,IDCard=@IDCard,StartWorkDate=@StartWorkDate,Education=@Education,Professional=@Professional,GraduateInstitutions=@GraduateInstitutions,TheOrAdultEducation=@TheOrAdultEducation,MaritalStatus=@MaritalStatus,HomeAddress=@HomeAddress,UsePeriod=@UsePeriod,WorkExperience=@WorkExperience,CertificateTime=@CertificateTime,Certificate=@Certificate where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] { 
                new SqlParameter("@Id",u.Id),
                new SqlParameter("@CnName",u.CnName),
                new SqlParameter("@EnName",u.EnName),
                new SqlParameter("@Number",u.Number),
                new SqlParameter("@Did",u.Did),
                new SqlParameter("@Password",u.Password),
                new SqlParameter("@Sex",u.Sex),
                new SqlParameter("@Post",u.Post),
                new SqlParameter("@Ext",u.Ext),
                new SqlParameter("@Phone",u.Phone),
                new SqlParameter("@UrgentPhone",u.UrgentPhone),
                new SqlParameter("@Email",u.Email),
                new SqlParameter("@Address",u.Address),
                new SqlParameter("@Edate",u.Edate),
                new SqlParameter("@Rdate",u.Rdate),
                new SqlParameter("@Remark",u.Remark),
                new SqlParameter("@Seniority",u.Seniority),
                new SqlParameter("@IsDel",u.IsDel),
                new SqlParameter("@Company",u.Company),
                new SqlParameter("@Birthday",u.Birthday),
                new SqlParameter("@IDCard",u.IDCard),
                new SqlParameter("@StartWorkDate",u.StartWorkDate),
                new SqlParameter("@Education",u.Education),
                new SqlParameter("@Professional",u.Professional),
                new SqlParameter("@GraduateInstitutions",u.GraduateInstitutions),
                new SqlParameter("@TheOrAdultEducation",u.TheOrAdultEducation),
                new SqlParameter("@MaritalStatus",u.MaritalStatus),
                new SqlParameter("@HomeAddress",u.HomeAddress),
                new SqlParameter("@UsePeriod",u.UsePeriod),
                new SqlParameter("@WorkExperience",u.WorkExperience),
                new SqlParameter("@CertificateTime",u.CertificateTime),
                new SqlParameter("@Certificate",u.Certificate)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelUsers(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update Users set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="userNo"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool DelUpdatePwd(string userNo, string pwd)
        {
            if (SqlHelper.ExecuteNonQuery("update Users set Password = @Password where Number = @UserNo and IsDel = 0", CommandType.Text, new SqlParameter("@Password", pwd), new SqlParameter("@UserNo", userNo)) > 0)
                return true;
            return false;
        }


        /// <summary>
        /// 根据姓名查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public Users GetUsersByName(string name)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Users where CnName = @name and Isdel=0", new SqlParameter("@name", name));
        }

        /// <summary>
        /// 根据职位查询有效员工
        /// </summary>
        /// <returns></returns>
        public List<Users> GetJobAll()
        {
            return excuteSql("select * from Users where Post like '%酒店%' and IsDel=0 or Post like '%机票%' or Id=149");
        }

    }
}