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;

public class Employee_ChildDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Employee_Child m)
    {
        string sql = @"insert Base_Employee_Child(parent_ele_id,child_ele_id)
                        select @parent_ele_id,@child_ele_id";
        SqlParameter[] par = {
            new SqlParameter("@parent_ele_id",m.parent_ele_id),
            new SqlParameter("@child_ele_id",m.child_ele_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">ec_id</param>
    /// <returns>Employee_Child</returns>
    public Employee_Child Get(int ec_id)
    {
        Employee_Child m = new Employee_Child();
        string sql = @"select *
                      from Base_Employee_Child where ec_id=@ec_id";
        SqlParameter[] par = {
                new SqlParameter("@ec_id",ec_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.ec_id = Convert.ToInt32(r["ec_id"]);
                m.parent_ele_id = Convert.ToInt32(r["parent_ele_id"]);
                m.child_ele_id = Convert.ToInt32(r["child_ele_id"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Employee_Child m)
    {
        string sql = @"update Base_Employee_Child set 
                        parent_ele_id=@parent_ele_id,
                        child_ele_id=@child_ele_id
                       where ec_id=@ec_id";
        SqlParameter[] par = {
            new SqlParameter("@ec_id",m.ec_id),
            new SqlParameter("@parent_ele_id",m.parent_ele_id),
            new SqlParameter("@child_ele_id",m.child_ele_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int ec_id)
    {
        Employee_Child m = new Employee_Child();
        string sql = "delete Base_Employee_Child where ec_id in (@ec_id)";
        SqlParameter[] par = {
                new SqlParameter("@ec_id",ec_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有记录
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Employee_Child order by ec_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    //打印用的sql
    public string sql_print = "";
    /// <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(
                    select ec.ec_id,c.ele_id as child_ele_id,
                    c.ele_code as child_ele_code,
                    c.ele_name as child_ele_name,d.dep_name as child_dep_name
                    from Base_Employee_Child ec
                    left join Base_Employee c on ec.child_ele_id=c.ele_id
                    left join Base_Department d on c.dep_id=d.dep_id
                    where 1=1
                )t
                where 1=1 ";
        sql = sql.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 Base_Employee_Child 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="parent_ele_id">上级ID</param>
    /// <param name="ele_id">下级ID</param>
    /// <returns>bool</returns>
    public bool CheckChildExist(int parent_ele_id, int child_ele_id)
    {
        string sql = "select count(1) from Base_Employee_Child where parent_ele_id=@parent_ele_id and child_ele_id=@child_ele_id";
        SqlParameter[] par ={
                new SqlParameter("@parent_ele_id", parent_ele_id),
                new SqlParameter("@child_ele_id", child_ele_id)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
}
