using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace DA
{
   public class DBMembers: IDisposable 
    {

       string sql;
       SqlHelpers sqlh;
       DataSet ds;
       SqlDataReader dr;
       public DBMembers()
       {
           sqlh = new SqlHelpers();
       }
       public DBMembers(SqlHelpers sh)
       {
           sqlh = sh;
       }
       public void Dispose()
       {
           sqlh.Dispose();
       }
  
       /// <summary>
       /// 自动产生会员 编号的方法
       /// </summary>
       /// <returns></returns>
       public string DBGetNewHY()
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@id", SqlDbType.VarChar, 20));
           pars[0].Direction = ParameterDirection.Output;
           sql = "pro_getNewHYZD";//执行存储过程
           sqlh.NonQuery(sql, pars, CommandType.StoredProcedure);
           return pars[0].Value.ToString();//返回参数的值
       }
       /// <summary>
       ///DB   查询所有会员信息的方法
       /// </summary>
       /// <returns></returns>
       public DataSet DBMembersQuery()
       {
           sql = "select m.mId,m.mName,m.mSex,mg.mgName,m.mPoint,m.mBirthday,m.mLinkphone,m.mStartDate,m.mState,m.mRemark from Members m inner join MemberGrade mg on m.mgId=mg.mgId";
           ds = sqlh.ExcuteSelect(sql, "MMG", null);
           return ds;
       }

       /// <summary>
       ///DB   根据会员编号查询会员信息的方法
       /// </summary>
       /// <param name="mId"></param>
       /// <returns></returns>
       public DataSet DBMemberIDsQuery(string mId)
       {
           List<SqlParameter> pats = new List<SqlParameter>();
           pats.Add(new SqlParameter("@mId", SqlDbType.VarChar, 10));
           pats[0].Value = mId;
           sql = "select m.mId,m.mName,m.mSex,mg.mgId,mg.mgName,m.mPoint,m.mBirthday,m.mLinkphone,m.mStartDate,m.mState,m.mRemark from Members m inner join MemberGrade mg on m.mgId=mg.mgId where m.mId=@mId";
           ds = sqlh.ExcuteSelect(sql,"MMG",pats );
           return ds;
       }

       /// <summary>
       /// DB   根据会员等级查询会员信息的方法
       /// </summary>
       /// <returns></returns>
       public DataSet DBMemberDJsQuery(string mgName)
       {
           List<SqlParameter> pats = new List<SqlParameter>();
           pats.Add(new SqlParameter("@mgName", SqlDbType.VarChar, 10));
           pats[0].Value = mgName;
           sql = "select m.mId,m.mName,m.mSex,mg.mgName,m.mPoint,m.mBirthday,m.mLinkphone,m.mStartDate,m.mState,m.mRemark from Members m inner join MemberGrade mg on m.mgId=mg.mgId where mg.mgName=@mgName";
           ds = sqlh.ExcuteSelect(sql, "MMG", pats);
           return ds;
       }
       /// <summary>
       ///DB   根据会员姓名查询会员信息的业务
       /// </summary>
       /// <param name="mgName"></param>
       /// <returns></returns>
       public DataSet DBMemberNamesQuery(string mgName)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@mName", SqlDbType.VarChar, 20));
           pars[0].Value = mgName ;
           sql = "select m.mId,m.mName,m.mSex,mg.mgName,m.mPoint,m.mBirthday,m.mLinkphone,m.mStartDate,m.mState,m.mRemark from Members m inner join MemberGrade mg on m.mgId=mg.mgId where m.mName=@mName";
           ds = sqlh.ExcuteSelect(sql, "MMG", pars );
           return ds;
       }


       /// <summary>
       /// DB   查询所有会员编号的方法
       /// </summary>
       /// <returns></returns>
       public List<CMembers> DBMembersQueryID()
       {
           List<CMembers> lm = new List<CMembers>();
           sql = "select * from Members";
           dr = sqlh.RQuery(sql, null, CommandType.Text);
           while (dr.Read())
           {
               CMembers cm = new CMembers();
               cm.CmId1 = dr["mId"].ToString();
               lm.Add(cm);
           }
           dr.Close();
           return lm;
       }
       /// <summary>
       /// DB  插入会员信息的方法
       /// </summary>
       /// <param name="m"></param>
       public void DBMemberInsert(CMembers m)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@mId", SqlDbType.VarChar, 50));
           pars[0].Value = m.CmId1 ;
           pars.Add(new SqlParameter("@mName", SqlDbType.VarChar, 20));
           pars[1].Value = m.CmName1 ;
           pars.Add(new SqlParameter("@mSex", SqlDbType.Char, 2));
           pars[2].Value = m.CmSex1 ;
           pars.Add(new SqlParameter("@mgId", SqlDbType.Int));
           pars[3].Value = m.CmgId1 ;
           pars.Add(new SqlParameter("@mPoint", SqlDbType.Int));
           pars[4].Value = m.CmPoint1 ;
           pars.Add(new SqlParameter("@mBirthday", SqlDbType.DateTime));
           pars[5].Value = m.CmBirthday1 ;
           pars.Add(new SqlParameter("@mLinkphone", SqlDbType.VarChar, 30));
           pars[6].Value = m.CmLinkphone1 ;
           pars.Add(new SqlParameter("@mStartDate", SqlDbType.DateTime));
           pars[7].Value = m.CmStartDate1 ;
           pars.Add(new SqlParameter("@mState", SqlDbType.VarChar, 10));
           pars[8].Value = m.CmState1 ;
           pars.Add(new SqlParameter("@mRemark", SqlDbType.VarChar, 50));
           pars[9].Value = m.CmRemark1 ;
           sql = "insert into Members values(@mId,@mName,@mSex,@mgId,@mPoint,@mBirthday,@mLinkphone,@mStartDate,@mState,@mRemark)";
           sqlh.ExcuteInsertUpdateDelete (sql, pars);
       }

       /// <summary>
       ///DB   修改会员信息的方法
       /// </summary>
       /// <param name="m"></param>
       public void DBMemberUpdate(CMembers m)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@mId", SqlDbType.VarChar, 50));
           pars[0].Value = m.CmId1 ;
           pars.Add(new SqlParameter("@mName", SqlDbType.VarChar, 20));
           pars[1].Value = m.CmName1 ;
           pars.Add(new SqlParameter("@mSexr", SqlDbType.Char, 2));
           pars[2].Value = m.CmSex1 ;
           pars.Add(new SqlParameter("@mgId", SqlDbType.Int));
           pars[3].Value = m.CmgId1 ;
           pars.Add(new SqlParameter("@mPoint", SqlDbType.Int));
           pars[4].Value = m.CmPoint1 ;
           pars.Add(new SqlParameter("@mBirthday", SqlDbType.DateTime));
           pars[5].Value = m.CmBirthday1 ;
           pars.Add(new SqlParameter("@mLinkphone", SqlDbType.VarChar, 30));
           pars[6].Value = m.CmLinkphone1 ;
           pars.Add(new SqlParameter("@mState", SqlDbType.VarChar, 10));
           pars[7].Value = m.CmState1 ;
           pars.Add(new SqlParameter("@mRemark", SqlDbType.VarChar, 50));
           pars[8].Value = m.CmRemark1 ;
           sql = "update Members set mName=@mName,mSex=@mSexr,mgId=@mgId,mPoint=@mPoint,mBirthday=@mBirthday,mLinkphone=@mLinkphone,mState=@mState,mRemark=@mRemark where mId=@mId";
           sqlh.ExcuteInsertUpdateDelete (sql, pars);
       }

       /// <summary>
       ///DB   删除会员信息的方法
       /// </summary>
       /// <param name="mId"></param>
       public void DBMembersDelete(string mId)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@mId", SqlDbType.VarChar, 50));
           pars[0].Value = mId;
           sql = "delete from Members where mId=@mId";
          sqlh.ExcuteInsertUpdateDelete (sql, pars);
       }

       /// <summary>
       ///DB   根据会员生日查询会员信息的方法
       /// </summary>
       /// <param name="start">起始日期</param>
       /// <param name="end">截止日期</param>
       /// <returns></returns>
       public DataSet DBMembersBirthday(DateTime start, DateTime end)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@start", SqlDbType.DateTime));
           pars[0].Value = start;
           pars.Add(new SqlParameter("@end", SqlDbType.DateTime));
           pars[1].Value = end;
           sql = "select m.mId,m.mName,m.mSex,mg.mgName,m.mPoint,m.mBirthday,m.mLinkphone,m.mStartDate,m.mState,m.mRemark from Members m inner join MemberGrade mg on m.mgId=mg.mgId where m.mBirthday>=@start and m.mBirthday<=@end";
           ds =sqlh.ExcuteSelect (sql, "MMG", pars);
           return ds;
       }

       /// <summary>
       ///DB  修改会员积分的方法
       /// </summary>
       /// <param name="mId"></param>
       /// <param name="point"></param>
       public void DBMembersUpPoint(string mId, int point)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@mId", SqlDbType.VarChar, 10));
           pars[0].Value = mId;
           pars.Add(new SqlParameter("@mPoint", SqlDbType.Int));
           pars[1].Value = point;
           sql = "update Members set mPoint=mPoint+@mPoint where mId=@mId";
           sqlh.ExcuteInsertUpdateDelete (sql, pars);
       }
    }
}