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 IOTypeDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(IOType m)
    {
        string sql = @"insert Base_IOType(iot_code,iot_name,parent_id,iot_flag)
                        select @iot_code,@iot_name,@parent_id,@iot_flag";
        SqlParameter[] par = {
            new SqlParameter("@iot_code",m.iot_code),
            new SqlParameter("@iot_name",m.iot_name),
            new SqlParameter("@parent_id",m.parent_id),
            new SqlParameter("@iot_flag",m.iot_flag)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">iot_id</param>
    /// <returns>IOType</returns>
    public IOType Get(int iot_id)
    {
        IOType m = new IOType();
        string sql = @"select *
                      from Base_IOType where iot_id=@iot_id";
        SqlParameter[] par = {
                new SqlParameter("@iot_id",iot_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.iot_id = Convert.ToInt32(r["iot_id"]);
                m.iot_code = r["iot_code"].ToString();
                m.iot_name = r["iot_name"].ToString();
                m.parent_id = XTools.DBNullToInt(r["parent_id"]);
                if (r["parent_id"] != null)
                {
                    m.parent = Get(Convert.ToInt32(r["parent_id"]));
                }
                m.iot_flag = Convert.ToInt32(r["iot_flag"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(IOType m)
    {
        string sql = @"update Base_IOType set 
                        iot_code=@iot_code,
                        iot_name=@iot_name,
                        parent_id=@parent_id,
                        iot_flag=@iot_flag
                       where iot_id=@iot_id";
        SqlParameter[] par = {
                new SqlParameter("@iot_id",m.iot_id),
                new SqlParameter("@iot_code",m.iot_code),
                new SqlParameter("@iot_name",m.iot_name),
                new SqlParameter("@parent_id",m.parent_id),
                new SqlParameter("@iot_flag",m.iot_flag)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int iot_id)
    {
        IOType m = new IOType();
        string sql = "delete Base_IOType where iot_id in (@iot_id)";
        SqlParameter[] par = {
                new SqlParameter("@iot_id",iot_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll(string where)
    {
        string sql = @"select * from Base_IOType @where order by iot_id";
        sql = sql.Replace("@where", where);
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 快速下拉查询
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetList_FastSearch(string keyword, string iot_flag)
    {
        string sql = @"select iot_id,iot_code,iot_name from Base_IOType where iot_id not in (select parent_id from base_IOType) and (iot_code like '%'+@keyword+'%' or iot_name like '%'+@keyword+'%')";
        if (!iot_flag.Equals(""))
        {
            sql += " and iot_flag=@iot_flag";
        }
        sql += " order by iot_code";
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword),
            new SqlParameter("@iot_flag",iot_flag)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    //列表查询用的sql
    string sql_list = @"
                    select t1.*,
                    t2.iot_code as parent_code,t2.iot_name as parent_name,
                    case when t1.iot_flag=1 then '收' else '发' end as iot_flag_name
                    from Base_IOType t1 left join
                    Base_IOType t2 on t1.parent_id=t2.iot_id";
    /// <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);

        //分页处理与查询条件
        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="iot_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string iot_code)
    {
        string sql = "select count(1) from Base_IOType where iot_code=@iot_code";
        SqlParameter[] par ={
                new SqlParameter("@iot_code", iot_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证是否有子类
    /// </summary>
    /// <param name="iot_id">主键id</param>
    /// <returns>bool</returns>
    public bool CheckHasChild(int iot_id)
    {
        string sql = "select count(1) from Base_IOType where parent_id=@iot_id";
        SqlParameter[] par ={
                new SqlParameter("@iot_id", iot_id)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证编码是否与父级一致
    /// </summary>
    /// <param name="iot_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckLikeParent(string iot_code, string parent_code)
    {
        bool like = false;
        //父级编码与当前编码的前几位不一样，代表规则不一致
        if (iot_code.Length <= parent_code.Length) return false;
        if (iot_code.Substring(0, parent_code.Length).Equals(parent_code))
            like = true;
        //如果除父级编码外，还有规则与当前编码一样的且长度大于父级编码的,代表规则不对
        string sql = @"select count(1) from Base_IOType 
                        where  @iot_code like iot_code+'%'
                        and iot_code not in (@parent_code,@iot_code)
                        and len(iot_code)>len(@parent_code)";
        SqlParameter[] par = {
                new SqlParameter("@iot_code",iot_code),
                new SqlParameter("@parent_code",parent_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            like = false;
        return like;
    }
    /// <summary>
    /// 更新分类级次
    /// </summary>
    /// <param name="parent_id">当前ID</param>
    /// <param name="layer">当前级次</param>
    public void FetchNodeAndUpdateLayer(int parent_id, int layer)
    {
        DataTable dt = GetListAll("where isnull(parent_id,0)=" + parent_id);
        SqlUtil su = new SqlUtil();
        foreach (DataRow dr in dt.Rows)
        {
            int cur_id = Convert.ToInt32(dr["iot_id"]);
            string iot_name = dr["iot_name"].ToString();
            string sql = "update Base_IOType set layer=" + layer + " where iot_id=" + cur_id;
            su.ExecuteNonQuery(sql, null);
            bool has_child = CheckHasChild(cur_id);
            if (has_child)
            {
                FetchNodeAndUpdateLayer(cur_id, layer + 1);
            }
        }
    }
    /// <summary>
    /// 通过ID得到编码
    /// </summary>
    /// <param name="ID">iot_id</param>
    /// <returns>int</returns>
    public string GetCodeById(int iot_id)
    {
        string sql = @"select iot_code
                      from Base_IOType where iot_id=@iot_id";
        SqlParameter[] par = {
                new SqlParameter("@iot_id",iot_id)
            };
        return new SqlUtil().ExecuteScalar(sql, par).ToString();
    }
    /// <summary>
    /// 通过名称得到收发类型
    /// </summary>
    /// <param name="iot_name"></param>
    /// <returns></returns>
    public IOType GetByName(string iot_name)
    {
        PurchaseType m = new PurchaseType();
        string sql = @"select iot_id from Base_IOType where iot_name='" + iot_name + "'";
        try
        {
            int iot_id = (int)new SqlUtil().ExecuteScalar(sql, null);
            return Get(iot_id);
        }
        catch
        {
            return null;
        }
    }
}
