using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace Mtxfw.DAL
{
    /// <summary>
    /// 数据访问类:ConvertiblePaper
    /// </summary>
    public partial class ConvertiblePaper : Mtxfw.Utility.Myabstract
    {
        public ConvertiblePaper()
            : base("ConvertiblePaper")
        { }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Mtxfw.Model.ConvertiblePaper model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ConvertiblePaper(");
            strSql.Append("CardId,AccountId,Password,Amount,State,Utype)");
            strSql.Append(" values (");
            strSql.Append("@CardId,@AccountId,@Password,@Amount,@State,@Utype)");
            strSql.Append(";select SCOPE_IDENTITY()");
            SqlParameter[] parameters = {
					new SqlParameter("@CardId", SqlDbType.VarChar,50),
					new SqlParameter("@AccountId", SqlDbType.VarChar,50),
					new SqlParameter("@Password", SqlDbType.VarChar,50),
					new SqlParameter("@Amount", SqlDbType.Decimal,9),
					new SqlParameter("@State", SqlDbType.VarChar,20),
                    new SqlParameter("@Utype", SqlDbType.VarChar,20)};
            parameters[0].Value = model.CardId;
            parameters[1].Value = model.AccountId;
            parameters[2].Value = model.Password;
            parameters[3].Value = model.Amount;
            parameters[4].Value = model.State;
            parameters[5].Value = model.Utype;
            object obj = Mtxfw.Utility.SqlDbHelper_U.ExecuteCmd(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Mtxfw.Model.ConvertiblePaper model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ConvertiblePaper set ");
            strSql.Append("CardId=@CardId,");
            strSql.Append("AccountId=@AccountId,");
            strSql.Append("Password=@Password,");
            strSql.Append("Amount=@Amount,");
            strSql.Append("State=@State");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@CardId", SqlDbType.VarChar,50),
					new SqlParameter("@AccountId", SqlDbType.VarChar,50),
					new SqlParameter("@Password", SqlDbType.VarChar,50),
					new SqlParameter("@Amount", SqlDbType.Decimal,9),
					new SqlParameter("@State", SqlDbType.VarChar,20),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = model.CardId;
            parameters[1].Value = model.AccountId;
            parameters[2].Value = model.Password;
            parameters[3].Value = model.Amount;
            parameters[4].Value = model.State;
            parameters[5].Value = model.Id;

            int rows = Mtxfw.Utility.SqlDbHelper_U.ExecuteCmd(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ConvertiblePaper ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
};
            parameters[0].Value = Id;

            int rows = Mtxfw.Utility.SqlDbHelper_U.ExecuteCmd(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ConvertiblePaper ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            int rows = Mtxfw.Utility.SqlDbHelper_U.ExecuteCmd(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Mtxfw.Model.ConvertiblePaper GetModel(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,CardId,AccountId,Password,Amount,State from ConvertiblePaper ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
};
            parameters[0].Value = Id;

            Mtxfw.Model.ConvertiblePaper model = new Mtxfw.Model.ConvertiblePaper();
            DataSet ds = Mtxfw.Utility.SqlDbHelper_U.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.CardId = ds.Tables[0].Rows[0]["CardId"].ToString();
                model.AccountId = ds.Tables[0].Rows[0]["AccountId"].ToString();
                model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                if (ds.Tables[0].Rows[0]["Amount"].ToString() != "")
                {
                    model.Amount = decimal.Parse(ds.Tables[0].Rows[0]["Amount"].ToString());
                }
                model.State = ds.Tables[0].Rows[0]["State"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,CardId,AccountId,Password,Amount,State ");
            strSql.Append(" FROM ConvertiblePaper ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return Mtxfw.Utility.SqlDbHelper_U.GetDataSet(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" Id,CardId,AccountId,Password,Amount,State ");
            strSql.Append(" FROM ConvertiblePaper ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return Mtxfw.Utility.SqlDbHelper_U.GetDataSet(strSql.ToString());
        }

        public bool IsGood(string card, string pwd, string utype)
        {
            String sql = "select * from [ConvertiblePaper] where [AccountId]=@AccountId and Password=@Password and utype=@utype and State='有效'";
            SqlParameter[] parameters = {
					new SqlParameter("@AccountId", SqlDbType.VarChar,50),
					new SqlParameter("@Password", SqlDbType.VarChar,50),
                    new SqlParameter("@utype", SqlDbType.VarChar,50)};
            parameters[0].Value = card;
            parameters[1].Value = pwd;
            parameters[2].Value = utype;
            SqlDataReader Dr = Mtxfw.Utility.SqlDbHelper_U.GetDataReader(sql, parameters);
            Boolean isbool = false;
            if (Dr.Read()) 
                isbool = true;
            Dr.Close();
            return isbool;
        }
        /// <summary>
        /// 判断卡号是否存在
        /// </summary>
        /// <returns></returns>
        public bool IsExistsCar(string Card)
        {
            string sqlStr = "select top 1 * from [ConvertiblePaper] where AccountId=@Card";
            SqlParameter[] para = { new SqlParameter("@Card", SqlDbType.VarChar, 50) };
            para[0].Value = Card;
            bool Isbool = false;
            SqlDataReader Dr = Mtxfw.Utility.SqlDbHelper_U.GetDataReader(sqlStr.ToString(), para);
            if (Dr.Read()) Isbool = true;
            Dr.Close();
            return Isbool;
        }
        public void UpdateCard(string AccountID, string Province)
        {
            String sql = "update [ConvertiblePaper] set State='无效',Province=@Province where [AccountId]=@AccountId ";
            SqlParameter[] parameters = {
					new SqlParameter("@AccountId", SqlDbType.VarChar,50),new SqlParameter("@Province", SqlDbType.VarChar,50)};
            parameters[0].Value = AccountID;
            parameters[1].Value = Province;
            Mtxfw.Utility.SqlDbHelper_U.ExecuteCmd(sql, parameters);
        }
    }
}