using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DA
{
    /// <summary>
    /// DB　　 会员类型表
    /// </summary>
   public class DBMemberGrade: IDisposable 
    {
         string sql;
        SqlHelpers sqlh;
      
        SqlDataReader dr;
        public DBMemberGrade()
        {
            sqlh = new SqlHelpers();
        }
       public DBMemberGrade(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }

       /// <summary>
        ///DB   查询所有会员等级的方法
       /// </summary>
       /// <returns></returns>
        public List<CMemberGrade> DBMemberGradeQuery()
        {
            List<CMemberGrade> lmg = new List<CMemberGrade>();
            sql = "select * from MemberGrade";
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CMemberGrade mg = new CMemberGrade();
                mg.CmgId1  = Convert.ToInt32(dr["mgId"]);
                mg.CmgName1  = dr["mgName"].ToString();
                mg.CmgStartPoint1  = Convert.ToInt32(dr["mgStartPoint"]);
                mg.CmgDiscount1  = Convert.ToSingle(dr["mgDiscount"]);
                lmg.Add(mg);
            }
            dr.Close();
            return lmg;
        }

        /// <summary>
        ///DB   根据会员的等级查询相应的初始积分的方法
        /// </summary>
        /// <param name="mgName">会员等级</param>
        /// <returns></returns>
       public int DBMemberGradePoint(string mgName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mgName", SqlDbType.VarChar, 10));
            pars[0].Value = mgName;
            sql = "select mgStartPoint from MemberGrade where mgName=@mgName";
            return sqlh.ExcuteInsert(sql, pars);
        }
        /// <summary>
        /// 根据会员等级查询相应的编号的方法
        /// </summary>
        /// <param name="mgName">会员等级</param>
        /// <returns></returns>
       public int DBMemberGradeID(string mgName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mgName", SqlDbType.VarChar, 10));
            pars[0].Value = mgName;
            sql = "select mgId from MemberGrade where mgName=@mgName";
            return sqlh .ExcuteInsert (sql, pars);
        }

        /// <summary>
        /// 插入会员等级信息的方法
        /// </summary>
        /// <param name="mg"></param>
       public void DBMemberGradeInsert(CMemberGrade mg)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mgName", SqlDbType.VarChar, 10));
            pars[0].Value = mg.CmgName1 ;
            pars.Add(new SqlParameter("@mgStartPoint", SqlDbType.Int));
            pars[1].Value = mg.CmgStartPoint1 ;
            pars.Add(new SqlParameter("@mgDiscount", SqlDbType.Float));
            pars[2].Value = mg.CmgDiscount1 ;
            sql = "insert into MemberGrade values(@mgName,@mgStartPoint,@mgDiscount)";
            sqlh.ExcuteInsertUpdateDelete (sql, pars);
        }

        /// <summary>
        /// 修改会员等级信息的方法
        /// </summary>
        /// <param name="mg"></param>
       public void DBMemberGradeUpdate(CMemberGrade mg)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mgId", SqlDbType.Int));
            pars[0].Value = mg.CmgId1 ;
            pars.Add(new SqlParameter("@mgName", SqlDbType.VarChar, 10));
            pars[1].Value = mg.CmgName1 ;
            pars.Add(new SqlParameter("@mgStartPoint", SqlDbType.Int));
            pars[2].Value = mg.CmgStartPoint1 ;
            pars.Add(new SqlParameter("@mgDiscount", SqlDbType.Float));
            pars[3].Value = mg.CmgDiscount1 ;
            sql = "update MemberGrade set mgName=@mgName,mgStartPoint=@mgStartPoint,mgDiscount=@mgDiscount where mgId=@mgId";
            sqlh.ExcuteInsertUpdateDelete (sql, pars);
        }

        /// <summary>
        /// 删除会员等级信息的方法
        /// </summary>
        /// <param name="mgId">会员等级编号</param>
       public void DBMemberGradeDelete(int mgId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mgId", SqlDbType.Int));
            pars[0].Value = mgId;
            sql = "delete from MemberGrade where mgId=@mgId";
            sqlh.ExcuteInsertUpdateDelete (sql, pars);
        }

        /// <summary>
        /// 根据会员等级编号查询折扣率的方法
        /// </summary>
        /// <param name="mgId"></param>
        /// <returns></returns>
       public float DBMemberGradeDiscount(int mgId)
        {
            float discount = 0;//折扣率
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mgId", SqlDbType.Int));
            pars[0].Value = mgId;
            sql = "select mgDiscount from MemberGrade where mgId=@mgId";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            if (dr.Read())
            {
                discount = Convert.ToSingle(dr["mgDiscount"]);
            }
            dr.Close();
            return discount;
        }
    }
}