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;

/// <summary>
/// 结算账户
/// </summary>
public class AccountDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Account m)
    {
        string sql = @"insert Base_Account(ac_code,ac_name,bank_name,bank_no,balance,is_stop)
                       select @ac_code,@ac_name,@bank_name,@bank_no,@balance,@is_stop";
        SqlParameter[] par = {
            new SqlParameter("@ac_code",m.ac_code),
            new SqlParameter("@ac_name",m.ac_name),
            new SqlParameter("@bank_name",m.bank_name),
            new SqlParameter("@bank_no",m.bank_no),
            new SqlParameter("@balance",XTools.DecimalNullToZero(m.balance)),
            new SqlParameter("@is_stop",m.is_stop)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">ac_id</param>
    /// <returns>Account</returns>
    public Account Get(int ac_id)
    {
        Account m = new Account();
        string sql = @"select *
                      from Base_Account where ac_id=@ac_id";
        SqlParameter[] par = {
                new SqlParameter("@ac_id",ac_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.ac_id = Convert.ToInt32(r["ac_id"]);
                m.ac_code = r["ac_code"].ToString();
                m.ac_name = r["ac_name"].ToString();
                m.bank_name = r["bank_name"].ToString();
                m.bank_no = r["bank_no"].ToString();
                m.balance = XTools.DBNullToDecimal(r["balance"]);
                m.is_stop = XTools.DBNullToInt(r["is_stop"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Account m)
    {
        string sql = @"update Base_Account set 
                        ac_code=@ac_code,
                        ac_name=@ac_name,
                        bank_name=@bank_name,
                        bank_no=@bank_no,
                        balance=@balance,
                        is_stop=@is_stop
                        where ac_id=@ac_id";
        SqlParameter[] par = {
                new SqlParameter("@ac_code",m.ac_code),
                new SqlParameter("@ac_name",m.ac_name),
                new SqlParameter("@bank_name",m.bank_name),
                new SqlParameter("@bank_no",m.bank_no),
                new SqlParameter("@balance",XTools.DecimalNullToZero(m.balance)),
                new SqlParameter("@is_stop",m.is_stop),
                new SqlParameter("@ac_id",m.ac_id),
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int ac_id)
    {
        Account m = new Account();
        string sql = "delete Base_Account where ac_id in (@ac_id)";
        SqlParameter[] par = {
                new SqlParameter("@ac_id",ac_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Account order by ac_id";
        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 ac_id,ac_code,ac_name,bank_name from Base_Account where (ac_name like '%'+@keyword+'%' or bank_name like '%'+@keyword+'%') order by ac_name";
        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 *,
                    case when isnull(is_stop,0)=1 then '是' else '否' end as is_stop_name
                    from Base_Account
                )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_Account 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="ac_code">账户编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string ac_code)
    {
        string sql = "select count(1) from Base_Account where ac_code=@ac_code";
        SqlParameter[] par ={
                new SqlParameter("@ac_code", ac_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证账户名称是否存在
    /// </summary>
    /// <param name="ac_name">账户名称</param>
    /// <returns>bool</returns>
    public bool CheckExistName(string ac_name)
    {
        string sql = "select count(1) from Base_Account where ac_name=@ac_name";
        SqlParameter[] par ={
                new SqlParameter("@ac_name", ac_name)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 更新账户余额
    /// </summary>
    /// <param name="ac_id">账户ID</param>
    /// <param name="amount">金额</param>
    /// <returns></returns>
    public string UpdateAccountBalance(int ac_id, decimal amount)
    {
        string sql = @"
            update Base_Account set balance=ISNULL(balance,0)+@amount where ac_id=@ac_id";
        sql = sql.Replace("@ac_id", ac_id.ToString())
                 .Replace("@amount", amount.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);
        return "success";
    }
}
