using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SysCore;
using SysCore.Control;
using SysCore.Model;
using SysCore.DAL;

public class EmployeeDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Employee m)
    {
        string sql = @"insert Base_Employee(ele_code,ele_name,dep_id,uid,position,entry_date,exit_date,
                        birthday,sex,native_place,degree,id_card_no,office_phone,
                        cellphone,home_phone,email,qq,address,kl,
                        sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                        ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select @ele_code,@ele_name,@dep_id,@uid,@position,@entry_date,@exit_date,
                        @birthday,@sex,@native_place,@degree,@id_card_no,@office_phone,
                        @cellphone,@home_phone,@email,@qq,@address,@kl,
                        @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                        @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@ele_code",m.ele_code),
            new SqlParameter("@ele_name",m.ele_name),
            new SqlParameter("@dep_id",m.dep_id),
            new SqlParameter("@uid",XTools.IntToDBNull(m.uid)),
            new SqlParameter("@position",XTools.StringToDBNull(m.position)),
            new SqlParameter("@entry_date",XTools.DateTimeToDBNull(m.entry_date)),
            new SqlParameter("@exit_date",XTools.DateTimeToDBNull(m.exit_date)),
            new SqlParameter("@birthday",XTools.DateTimeToDBNull(m.birthday)),
            new SqlParameter("@sex",XTools.IntToDBNull(m.sex)),
            new SqlParameter("@native_place",XTools.StringToDBNull(m.native_place)),
            new SqlParameter("@degree",XTools.StringToDBNull(m.degree)),
            new SqlParameter("@id_card_no",XTools.StringToDBNull(m.id_card_no)),
            new SqlParameter("@office_phone",XTools.StringToDBNull(m.office_phone)),
            new SqlParameter("@cellphone",XTools.StringToDBNull(m.cellphone)),
            new SqlParameter("@home_phone",XTools.StringToDBNull(m.home_phone)),
            new SqlParameter("@email",XTools.StringToDBNull(m.email)),
            new SqlParameter("@qq",XTools.StringToDBNull(m.qq)),
            new SqlParameter("@address",XTools.StringToDBNull(m.address)),
            new SqlParameter("@kl",XTools.DecimalToDBNull(m.kl)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">ele_id</param>
    /// <returns>Employee</returns>
    public Employee Get(int ele_id)
    {
        Employee m = new Employee();
        string sql = @"select *
                      from Base_Employee where ele_id=@ele_id";
        SqlParameter[] par = {
                new SqlParameter("@ele_id",ele_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            DepartmentDAL dep_dal = new DepartmentDAL();
            MemberDAL m_dal = new MemberDAL();
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.ele_id = Convert.ToInt32(r["ele_id"]);
                m.ele_code = r["ele_code"].ToString();
                m.ele_name = r["ele_name"].ToString();
                m.dep_id = XTools.DBNullToInt(r["dep_id"]);
                if (r["dep_id"] != DBNull.Value)
                {
                    m.department = dep_dal.Get(Convert.ToInt32(r["dep_id"]));
                }
                m.uid = XTools.DBNullToInt(r["uid"]);
                if (r["uid"] != DBNull.Value)
                {
                    m.member = m_dal.Get(Convert.ToInt32(r["uid"]));
                }
                m.position = r["position"].ToString();
                m.entry_date = XTools.DBNullToTime(r["entry_date"]);
                m.exit_date = XTools.DBNullToTime(r["exit_date"]);
                m.birthday = XTools.DBNullToTime(r["birthday"]);
                m.sex=Convert.ToInt32(r["sex"]);
                m.native_place = r["native_place"].ToString();
                m.degree = r["degree"].ToString();
                m.id_card_no = r["id_card_no"].ToString();
                m.office_phone = r["office_phone"].ToString();
                m.cellphone = r["cellphone"].ToString();
                m.home_phone = r["home_phone"].ToString();
                m.email = r["email"].ToString();
                m.qq = r["qq"].ToString();
                m.address = r["address"].ToString();
                m.kl = XTools.DBNullToDecimal(r["kl"]);
                m.sdefine1 = r["sdefine1"].ToString();
                m.sdefine2 = r["sdefine2"].ToString();
                m.sdefine3 = r["sdefine3"].ToString();
                m.sdefine4 = r["sdefine4"].ToString();
                m.sdefine5 = r["sdefine5"].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(r["ndefine1"]);
                m.ndefine2 = XTools.DBNullToDecimal(r["ndefine2"]);
                m.ndefine3 = XTools.DBNullToDecimal(r["ndefine3"]);
                m.ndefine4 = XTools.DBNullToDecimal(r["ndefine4"]);
                m.ndefine5 = XTools.DBNullToDecimal(r["ndefine5"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 通过编码得到ID
    /// </summary>
    /// <param name="编码">vc_code</param>
    /// <returns>int</returns>
    public int GetIdByCode(string ele_code)
    {
        InventoryClass m = new InventoryClass();
        string sql = @"select ele_id
                      from Base_Employee where ele_code=@ele_code";
        SqlParameter[] par = {
                new SqlParameter("@ele_code",ele_code)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Employee m)
    {
        string sql = @"update Base_Employee set 
                        ele_code=@ele_code,
                        ele_name=@ele_name,
                        position=@position,
                        dep_id=@dep_id,
                        uid=@uid,
                        entry_date=@entry_date,
                        exit_date=@exit_date,
                        birthday=@birthday,
                        sex=@sex,
                        native_place=@native_place,
                        degree=@degree,
                        id_card_no=@id_card_no,
                        office_phone=@office_phone,
                        cellphone=@cellphone,
                        home_phone=@home_phone,
                        email=@email,
                        qq=@qq,
                        address=@address,
                        kl=@kl,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where ele_id=@ele_id";
        SqlParameter[] par = {
            new SqlParameter("@ele_id",m.ele_id),
            new SqlParameter("@ele_code",m.ele_code),
            new SqlParameter("@ele_name",m.ele_name),
            new SqlParameter("@dep_id",m.dep_id),
            new SqlParameter("@uid",XTools.IntToDBNull(m.uid)),
            new SqlParameter("@position",m.position),
            new SqlParameter("@entry_date",XTools.DateTimeToDBNull(m.entry_date)),
            new SqlParameter("@exit_date",XTools.DateTimeToDBNull(m.exit_date)),
            new SqlParameter("@birthday",XTools.DateTimeToDBNull(m.birthday)),
            new SqlParameter("@sex",m.sex),
            new SqlParameter("@native_place",m.native_place),
            new SqlParameter("@degree",m.degree),
            new SqlParameter("@id_card_no",m.id_card_no),
            new SqlParameter("@cellphone",m.cellphone),
            new SqlParameter("@office_phone",m.office_phone),
            new SqlParameter("@home_phone",m.home_phone),
            new SqlParameter("@email",m.email),
            new SqlParameter("@qq",m.qq),
            new SqlParameter("@address",m.address),
            new SqlParameter("@kl",XTools.DecimalToDBNull(m.kl)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int ele_id)
    {
        Employee m = new Employee();
        string sql = "delete Base_Employee where ele_id in (@ele_id)";
        SqlParameter[] par = {
                new SqlParameter("@ele_id",ele_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有员工
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Employee order by ele_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 快速下拉查询
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetList_FastSearch(string keyword)
    {
        string sql = @"select top 10 e.ele_id,e.ele_code,e.ele_name,d.dep_name
                       from Base_Employee e left join
                       Base_Department d on e.dep_id=d.dep_id
                       where e.exit_date is null
                       and (e.ele_code like '%'+@keyword+'%' or e.ele_name like '%'+@keyword+'%') 
                       order by e.ele_code";
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    /// <summary>
    /// 手机界面快速查询
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetList_MSearch(string keyword)
    {
        string sql = @"select top 10 e.ele_id,e.ele_name
                       from Base_Employee e
                       where e.exit_date is null
                       and (e.ele_code like '%'+@keyword+'%' or e.ele_name like '%'+@keyword+'%') 
                       order by e.ele_name";
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    
    //打印用的sql
    public string sql_print = "";
    //列表查询用的sql
    string sql_list = @"
                    select e.*,d.dep_code,d.dep_name,m.username,
                    case when e.sex=0 then '女' when sex=1 then '男' else '未知' end as sex_name
                    from Base_Employee e 
                    left join Base_Department d on e.dep_id=d.dep_id
                    left join System_Member m on e.uid=m.uid";
    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select ROW_NUMBER() OVER(@order_field) AS row_num,@sql_select_field 
                from(@sql_list)t
                where 1=1 ";
        sql = sql.Replace("@sql_list", sql_list)
                 .Replace("@order_field", order_field)
                 .Replace("@sql_select_field", sql_select_field);
        //打印是则不需要分页,只需要加上筛选条件
        sql_print = "select top 1000000000 * from (" + sql + ") t where 1=1 " + pg["sql_end"] + " " + pg["order_field"].ToString();
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = "select count(1) from (" + sql_list + ") t where 1=1 ";
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }
    /// <summary>
    /// 验证编码是否存在
    /// </summary>
    /// <param name="ele_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string ele_code)
    {
        string sql = "select count(1) from Base_Employee where ele_code=@ele_code";
        SqlParameter[] par ={
                new SqlParameter("@ele_code", ele_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }

    /// <summary>
    /// 通过登陆用户ID得到员工ID
    /// </summary>
    /// <param name="编码">vc_code</param>
    /// <returns>ele_id</returns>
    public int GetEmployeeIdByUserId(int uid)
    {
        InventoryClass m = new InventoryClass();
        string sql = @"select isnull(max(ele_id),0)
                      from Base_Employee where uid=@uid";
        SqlParameter[] par = {
                new SqlParameter("@uid",uid)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
}
