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 DefineItemDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(DefineItem m)
    {
        string sql = @"insert Base_DefineItem(di_code,di_value)
                        select @di_code,@di_value";
        SqlParameter[] par = {
            new SqlParameter("@di_code",m.di_code),
            new SqlParameter("@di_value",m.di_value)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">di_id</param>
    /// <returns>DefineItem</returns>
    public DefineItem Get(int di_id)
    {
        DefineItem m = new DefineItem();
        string sql = @"select *
                      from Base_DefineItem where di_id=@di_id";
        SqlParameter[] par = {
                new SqlParameter("@di_id",di_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.di_id = Convert.ToInt32(r["di_id"]);
                m.di_code = r["di_code"].ToString();
                m.di_value = r["di_value"].ToString();
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(DefineItem m)
    {
        string sql = @"update Base_DefineItem set 
                        di_code=@di_code,
                        di_value=@di_value
                       where di_id=@di_id";
        SqlParameter[] par = {
                new SqlParameter("@di_code",m.di_code),
                new SqlParameter("@di_value",m.di_value),
                new SqlParameter("@di_id",m.di_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int di_id)
    {
        DefineItem m = new DefineItem();
        string sql = "delete Base_DefineItem where di_id in (@di_id)";
        SqlParameter[] par = {
                new SqlParameter("@di_id",di_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有档案
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_DefineItem order by di_code";
        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 di_id,di_value from Base_DefineItem 
                       where di_value like '%'+@keyword+'%' order by di_value";
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    /// <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 *
                    from Base_DefineItem
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .Replace("@sql_select_field", sql_select_field);

        //分页处理与查询条件
        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_DefineItem 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="di_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string di_code)
    {
        string sql = "select count(1) from Base_DefineItem where di_code=@di_code";
        SqlParameter[] par ={
                new SqlParameter("@di_code", di_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
}
