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 CostDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Cost m)
    {
        string sql = @"insert Base_Cost(cst_code,cst_name,parent_id,is_stop)
                        select @cst_code,@cst_name,@parent_id,@is_stop";
        SqlParameter[] par = {
            new SqlParameter("@cst_code",m.cst_code),
            new SqlParameter("@cst_name",m.cst_name),
            new SqlParameter("@parent_id",m.parent_id),
            new SqlParameter("@is_stop",m.is_stop),
            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">cst_id</param>
    /// <returns>Cost</returns>
    public Cost Get(int cst_id)
    {
        Cost m = new Cost();
        string sql = @"select *
                      from Base_Cost where cst_id=@cst_id";
        SqlParameter[] par = {
                new SqlParameter("@cst_id",cst_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.cst_id = Convert.ToInt32(r["cst_id"]);
                m.cst_code = r["cst_code"].ToString();
                m.cst_name = r["cst_name"].ToString();
                m.parent_id = XTools.DBNullToInt(r["parent_id"]);
                if (r["parent_id"] != null)
                {
                    m.parent = Get(Convert.ToInt32(r["parent_id"]));
                }
                m.is_stop = XTools.DBNullToInt(r["is_stop"]);
                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>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Cost m)
    {
        string sql = @"update Base_Cost set 
                        cst_code=@cst_code,
                        cst_name=@cst_name,
                        parent_id=@parent_id,
                        is_stop=@is_stop,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where cst_id=@cst_id";
        SqlParameter[] par = {
                new SqlParameter("@cst_code",m.cst_code),
                new SqlParameter("@cst_name",m.cst_name),
                new SqlParameter("@is_stop",m.is_stop),
                new SqlParameter("@parent_id",m.parent_id),
                new SqlParameter("@cst_id",m.cst_id),
                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 cst_id)
    {
        Cost m = new Cost();
        string sql = "delete Base_Cost where cst_id in (@cst_id)";
        SqlParameter[] par = {
                new SqlParameter("@cst_id",cst_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有部门
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll(string where)
    {
        string sql = @"select * from Base_Cost @where order by cst_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 sql = @"select top 10 cst_id,cst_code,cst_name from Base_Cost where isnull(is_stop,0)=0 and cst_id not in (select parent_id from base_Cost) and (cst_code like '%'+@keyword+'%' or cst_name like '%'+@keyword+'%') order by cst_code";
        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 d.*,dp.cst_name as parent_name
                    from Base_Cost d left join
                    Base_Cost dp on d.parent_id=dp.cst_id
                )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_Cost 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="cst_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string cst_code)
    {
        string sql = "select count(1) from Base_Cost where cst_code=@cst_code";
        SqlParameter[] par ={
                new SqlParameter("@cst_code", cst_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证是否有子类
    /// </summary>
    /// <param name="cst_id">主键id</param>
    /// <returns>bool</returns>
    public bool CheckHasChild(int cst_id)
    {
        string sql = "select count(1) from Base_Cost where parent_id=@cst_id";
        SqlParameter[] par ={
                new SqlParameter("@cst_id", cst_id)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证编码是否与父级部门一致
    /// </summary>
    /// <param name="cst_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckLikeParent(string cst_code, string parent_code)
    {
        bool like = false;
        //父级部门编码与当前部门编码的前几位不一样，代表规则不一致
        if (cst_code.Length <= parent_code.Length) return false;
        if (cst_code.Substring(0, parent_code.Length).Equals(parent_code))
            like = true;
        //如果除父级部门编码外，还有规则与当前编码一样的且长度大于父级编码的,代表规则不对
        string sql = @"select count(1) from Base_Cost 
                        where  @cst_code like cst_code+'%'
                        and cst_code not in (@parent_code,@cst_code)
                        and len(cst_code)>len(@parent_code)";
        SqlParameter[] par = {
                new SqlParameter("@cst_code",cst_code),
                new SqlParameter("@parent_code",parent_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            like = false;
        return like;
    }
}
