
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 CurrencyRateDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(CurrencyRate m)
    {
        string sql = @"insert Base_CurrencyRate(crc_id,crc_year,crc_month,exchange_rate)
                        select @crc_id,@crc_year,@crc_month,@exchange_rate";
        SqlParameter[] par = {
            new SqlParameter("@crc_id",m.crc_id),
            new SqlParameter("@crc_year",m.crc_year),
            new SqlParameter("@crc_month",m.crc_month),
            new SqlParameter("@exchange_rate",m.exchange_rate)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">rate_id</param>
    /// <returns>CurrencyRate</returns>
    public CurrencyRate Get(int rate_id)
    {
        CurrencyRate m = new CurrencyRate();
        string sql = @"select *
                      from Base_CurrencyRate where rate_id=@rate_id";
        SqlParameter[] par = {
                new SqlParameter("@rate_id",rate_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.rate_id = Convert.ToInt32(r["rate_id"]);
                m.crc_id = Convert.ToInt32(r["crc_id"]);
                m.crc_year = Convert.ToInt32(r["crc_year"]);
                m.crc_month = Convert.ToInt32(r["crc_month"]);
                m.exchange_rate = XTools.DBNullToDecimal(r["exchange_rate"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(CurrencyRate m)
    {
        string sql = @"update Base_CurrencyRate set 
                       exchange_rate=@exchange_rate
                       where rate_id=@rate_id";
        SqlParameter[] par = {
            new SqlParameter("@rate_id",m.rate_id),
            new SqlParameter("@crc_id",m.crc_id),
            new SqlParameter("@exchange_rate",m.exchange_rate),
            new SqlParameter("@crc_year",m.crc_year),
            new SqlParameter("@crc_month",m.crc_month)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int rate_id)
    {
        CurrencyRate m = new CurrencyRate();
        string sql = "delete Base_CurrencyRate where rate_id in (@rate_id)";
        SqlParameter[] par = {
                new SqlParameter("@rate_id",rate_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <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_CurrencyRate
                )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_CurrencyRate 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="crc_id">币种id</param>
    /// <param name="crc_year">年</param>
    /// <param name="crc_month">月</param>
    /// <returns>bool</returns>
    public bool CheckRateExist(int crc_id, int crc_year, int crc_month)
    {
        string sql = @"select count(1) from Base_CurrencyRate 
                       where crc_id=@crc_id and crc_year=@crc_year and crc_month=@crc_month";
        SqlParameter[] par ={
            new SqlParameter("@crc_id", crc_id),
            new SqlParameter("@crc_year", crc_year),
            new SqlParameter("@crc_month", crc_month)
        };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 通过币种得到当前登录月份的汇率
    /// </summary>
    /// <param name="crc_id">币种id</param>
    /// <returns>decimal</returns>
    public decimal GetCurrentMonthExchangeRate(int crc_id)
    {
        try
        {
            string sql = @"select exchange_rate from Base_CurrencyRate 
                           where crc_id=@crc_id and crc_year=@crc_year and crc_month=@crc_month";
            LoginInfo li = new LoginInfo();
            DateTime d = DateTime.Parse(li.login_date);

            SqlParameter[] par ={
                new SqlParameter("@crc_id", crc_id),
                new SqlParameter("@crc_year", d.Year),
                new SqlParameter("@crc_month", d.Month)
            };

            decimal exchange_rate = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
            if (exchange_rate == 0)
                return 1;
            return exchange_rate;
        }
        catch
        {
            return 1;
        }
    }
}
