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 CurrencyDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Currency m)
    {
        string sql = @"insert Base_Currency(crc_code,crc_name,is_standard)
                        select @crc_code,@crc_name,@is_standard";
        SqlParameter[] par = {
            new SqlParameter("@crc_code",m.crc_code),
            new SqlParameter("@crc_name",m.crc_name),
            new SqlParameter("@is_standard",m.is_standard)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">crc_id</param>
    /// <returns>Currency</returns>
    public Currency Get(int crc_id)
    {
        LoginInfo li = new LoginInfo();
        DateTime d = DateTime.Parse(li.login_date);
        Currency m = new Currency();
        string sql = @"select c.*,cr.exchange_rate
                    from Base_Currency c left join
                    (select * from Base_CurrencyRate cr where crc_year=@crc_year and crc_month=@crc_month) as cr
                    on c.crc_id=cr.crc_id
                    where c.crc_id=@crc_id";
        SqlParameter[] par = {
                new SqlParameter("@crc_id",crc_id),
                new SqlParameter("@crc_year",d.Year),
                new SqlParameter("@crc_month",d.Month)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.crc_id = Convert.ToInt32(r["crc_id"]);
                m.crc_code = r["crc_code"].ToString();
                m.crc_name = r["crc_name"].ToString();
                m.is_standard = XTools.DBNullToInt(r["is_standard"]);
                m.exchange_rate = XTools.DBNullToDecimal(r["exchange_rate"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 得到本位币
    /// </summary>
    /// <returns>Currency</returns>
    public Currency GetStandard()
    {
        string sql = @"select crc_id from Base_Currency where is_standard=1";
        try
        {
            int crc_id = (int)new SqlUtil().ExecuteScalar(sql, null);
            return Get(crc_id);
        }
        catch
        {
            return null;
        }
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Currency m)
    {
        string sql = @"update Base_Currency set 
                        crc_code=@crc_code,
                        crc_name=@crc_name,
                        is_standard=@is_standard
                       where crc_id=@crc_id";
        SqlParameter[] par = {
                new SqlParameter("@crc_code",m.crc_code),
                new SqlParameter("@crc_name",m.crc_name),
                new SqlParameter("@is_standard",m.is_standard),
                new SqlParameter("@crc_id",m.crc_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int crc_id)
    {
        Currency m = new Currency();
        string sql = "delete Base_Currency where crc_id in (@crc_id)";
        SqlParameter[] par = {
                new SqlParameter("@crc_id",crc_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有币种
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Currency order by crc_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 快速下拉查询
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetList_FastSearch(string keyword)
    {
        LoginInfo li = new LoginInfo();
        DateTime d = DateTime.Parse(li.login_date);
        string sql = @"
                    select top 10 c.crc_id,c.crc_code,c.crc_name,cr.exchange_rate
                    from Base_Currency c left join
                    (select * from Base_CurrencyRate cr where crc_year=@crc_year and crc_month=@crc_month) as cr
                    on c.crc_id=cr.crc_id
                    where (c.crc_code like '%'+@keyword+'%' or c.crc_name like '%'+@keyword+'%')
                    order by c.crc_code";
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword),
            new SqlParameter("@crc_year",d.Year),
            new SqlParameter("@crc_month",d.Month)
        };
        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 *,
                    case when is_standard=1 then '是' else '否' end as is_standard_name
                    from Base_Currency
                )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_Currency 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_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckCodeExist(string crc_code)
    {
        string sql = "select count(1) from Base_Currency where crc_code=@crc_code";
        SqlParameter[] par ={
                new SqlParameter("@crc_code", crc_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证名称是否存在
    /// </summary>
    /// <param name="old_name">原名称</param>
    /// <param name="new_name">新名称</param>
    /// <returns>bool</returns>
    public bool CheckNameExist(string old_name, string new_name)
    {
        string sql = "select count(1) from Base_Currency where crc_name!=@old_name and crc_name=@new_name";
        SqlParameter[] par ={
                new SqlParameter("@old_name", old_name),
                new SqlParameter("@new_name", new_name)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }

    /// <summary>
    /// 取得本位币个数
    /// </summary>
    /// <returns>int</returns>
    public int CountStandard(int crc_id)
    {
        string sql = "select count(1) from Base_Currency where isnull(is_standard,0)=1 and crc_id!=@crc_id ";
        SqlParameter[] par ={
                new SqlParameter("@crc_id", crc_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
       
    }
    /// <summary>
    /// 通过编码得到ID
    /// </summary>
    /// <param name="币种编码">crc_code</param>
    /// <returns>int</returns>
    public int GetIdByCode(string crc_code)
    {
        InventoryClass m = new InventoryClass();
        string sql = @"select crc_id
                      from Base_Currency where crc_code=@crc_code";
        SqlParameter[] par = {
                new SqlParameter("@crc_code",crc_code)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
}
