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 CloseMonthDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(CloseMonth m)
    {
        string sql = @"insert System_CloseMonth(cm_year,cm_month)
                        select @cm_year,@cm_month";
        SqlParameter[] par = {
            new SqlParameter("@cm_year",m.cm_year),
            new SqlParameter("@cm_month",m.cm_month)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">cm_id</param>
    /// <returns>CloseMonth</returns>
    public CloseMonth Get(int cm_id)
    {
        CloseMonth m = new CloseMonth();
        string sql = @"select *
                      from System_CloseMonth where cm_id=@cm_id";
        SqlParameter[] par = {
                new SqlParameter("@cm_id",cm_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.cm_id = Convert.ToInt32(r["cm_id"]);
                m.cm_year = Convert.ToInt32(r["cm_year"]);
                m.cm_month = Convert.ToInt32(r["cm_month"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <returns>CloseMonth</returns>
    public CloseMonth GetCurrentMonth()
    {
        CloseMonth m = new CloseMonth();
        string sql = @"select top 1 * from System_CloseMonth order by cm_year desc,cm_month desc";
        using (SqlDataReader r = new SqlUtil().GetDataReader(sql, null))
        {
            while (r.Read())
            {
                m.cm_id = Convert.ToInt32(r["cm_id"]);
                m.cm_year = Convert.ToInt32(r["cm_year"]);
                m.cm_month = Convert.ToInt32(r["cm_month"]);
            }
        }
        return m;
    }

    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(CloseMonth m)
    {
        string sql = @"update System_CloseMonth set 
                        cm_year=@cm_year,
                        cm_month=@cm_month
                       where cm_id=@cm_id";
        SqlParameter[] par = {
                new SqlParameter("@cm_month",m.cm_month),
                new SqlParameter("@cm_year",m.cm_year)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int cm_id)
    {
        CloseMonth m = new CloseMonth();
        string sql = "delete System_CloseMonth where cm_id in (@cm_id)";
        SqlParameter[] par = {
                new SqlParameter("@cm_id",cm_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll(string where)
    {
        string sql = @"select * from System_CloseMonth @where order by cm_id";
        sql = sql.Replace("@where", where);
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }

    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select ROW_NUMBER() OVER(@order_field) AS row_num,cm_id,cm_month
                from(
                    select *
                    from System_CloseMonth
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_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 System_CloseMonth 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="cm_year">年份</param>
    /// <returns>bool</returns>
    public bool CheckExist(string cm_year)
    {
        string sql = "select count(1) from System_CloseMonth where cm_year=@cm_year";
        SqlParameter[] par ={
                new SqlParameter("@cm_year", cm_year)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 判断某操作日期是否已月结
    /// </summary>
    /// <param name="op_date">操作日期</param>
    /// <returns>bool</returns>
    public bool CheckIsCloseMonth(DateTime op_date)
    {
        string sql = "select count(1) from System_CloseMonth where cm_year>=@cm_year and cm_month>=@cn_month";
        SqlParameter[] par ={
                new SqlParameter("@cm_year", op_date.Year),
                new SqlParameter("@cn_month", op_date.Month)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
}
