using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace SK.MemberDAL
{
	/// <summary>
	/// Member 的摘要说明。
	/// </summary>
	public class Member
	{
		public Member()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public void Add(object MemberNO,object MemberName,object TrueName,object Password,object Sex,object Email,
			object Email2,object QuestionID,object Answer,object BirthDay,object Zip,object CountryID,
			object ProvinceID,object City,object EmailHasValid,object EmailValidCode,object Point)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Member_Add";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberNO;
			oParms[1].Value=MemberName;
			oParms[2].Value=TrueName;
			oParms[3].Value=Password;
			oParms[4].Value=Sex;
			oParms[5].Value=Email;
			oParms[6].Value=Email2;
			oParms[7].Value=QuestionID;
			oParms[8].Value=Answer;
			oParms[9].Value=BirthDay;
			oParms[10].Value=Zip;
			oParms[11].Value=CountryID;
			oParms[12].Value=ProvinceID;
			oParms[13].Value=City;
			oParms[14].Value=EmailHasValid;
			oParms[15].Value=EmailValidCode;
			oParms[16].Value=Point;
			
			SqlHelper.ExecuteNonQuery(Data.ConnString, spName, oParms);		
		}
		public int Count()
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString,CommandType.Text,"select count(MemberID) from Member"));
		}
		public int Count_Today()
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString,CommandType.Text,"select count(MemberID) from Member where DATEDIFF (day,RegTime,getdate())=0"));
		}
		public void Update(object MemberID,object MemberNO,object MemberName,object TrueName,object Sex,
			object Email2,object QuestionID,object Answer,object BirthDay,object Zip,object CountryID,
			object ProvinceID,object City,object Tel)
		{
			SqlParameter[] oParms =new SqlParameter[14];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@MemberNO",MemberNO);
			oParms[2] = new SqlParameter("@MemberName",MemberName);
			oParms[3] = new SqlParameter("@TrueName",TrueName);
			oParms[4] = new SqlParameter("@Sex",Sex);
			oParms[5] = new SqlParameter("@Email2",Email2);
			oParms[6] = new SqlParameter("@QuestionID",QuestionID);
			oParms[7] = new SqlParameter("@Answer",Answer);
			oParms[8] = new SqlParameter("@BirthDay",BirthDay);
			oParms[9] = new SqlParameter("@Zip",Zip);
			oParms[10] = new SqlParameter("@CountryID",CountryID);
			oParms[11] = new SqlParameter("@ProvinceID",ProvinceID);
			oParms[12] = new SqlParameter("@City",City);
			oParms[13] = new SqlParameter("@Tel",Tel);


			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set MemberNO=@MemberNO, MemberName=@MemberName, TrueName=@TrueName, Sex=@Sex, Email2=@Email2, QuestionID=@QuestionID, Answer=@Answer, BirthDay=@BirthDay, Zip=@Zip, CountryID=@CountryID, ProvinceID=@ProvinceID, City=@City,Tel=@Tel where MemberID=@MemberID",oParms);
		}
		public void UpdateCompany(object MemberID,object IsCompany)
		{
			SqlParameter[] oParms =new SqlParameter[16];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@IsCompany",IsCompany);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set IsCompany=@IsCompany where MemberID=@MemberID",oParms);
		}
		public bool MemberHasCompany(object MemberID)
		{
			SqlParameter[] oParms =new SqlParameter[16];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			

			DataRow dr = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"Select IsCompany from member where MemberID=@MemberID",oParms).Tables[0].Rows[0];
			if(dr[0].ToString() != "")
				return true;
			else
				return false;

				
		}
		public void Update(object MemberID,object MemberNO,object MemberName,object TrueName,object Sex,
			object Email2,object QuestionID,object Answer,object BirthDay,object Zip,object CountryID,
			object ProvinceID,object City,object Point,object Score,object IsDenied)
		{
			SqlParameter[] oParms =new SqlParameter[16];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@MemberNO",MemberNO);
			oParms[2] = new SqlParameter("@MemberName",MemberName);
			oParms[3] = new SqlParameter("@TrueName",TrueName);
			oParms[4] = new SqlParameter("@Sex",Sex);
			oParms[5] = new SqlParameter("@Email2",Email2);
			oParms[6] = new SqlParameter("@QuestionID",QuestionID);
			oParms[7] = new SqlParameter("@Answer",Answer);
			oParms[8] = new SqlParameter("@BirthDay",BirthDay);
			oParms[9] = new SqlParameter("@Zip",Zip);
			oParms[10] = new SqlParameter("@CountryID",CountryID);
			oParms[11] = new SqlParameter("@ProvinceID",ProvinceID);
			oParms[12] = new SqlParameter("@City",City);
			oParms[13] = new SqlParameter("@Point",Point);
			oParms[14] = new SqlParameter("@Score",Score);
			oParms[15] = new SqlParameter("@IsDenied",IsDenied);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set MemberNO=@MemberNO, MemberName=@MemberName, TrueName=@TrueName, Sex=@Sex, Email2=@Email2, QuestionID=@QuestionID, Answer=@Answer, BirthDay=@BirthDay, Zip=@Zip, CountryID=@CountryID, ProvinceID=@ProvinceID, City=@City,Point=@Point,Score=@Score,IsDenied=@IsDenied where MemberID=@MemberID",oParms);
		}
		public DataRow Info(object MemberID)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@MemberID",MemberID);

			using(DataSet ds = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select * from Member where MemberID=@MemberID",oParms))
			{
				if(ds.Tables[0].Rows.Count>0)
					return ds.Tables[0].Rows[0];
				else
					return null;
			}
		}
		public DataRow Info_Name(object MemberName)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@MemberName",MemberName);

			using(DataSet ds = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select * from Member where MemberName=@MemberName",oParms))
			{
				if(ds.Tables[0].Rows.Count>0)
					return ds.Tables[0].Rows[0];
				else
					return null;
			}
		}
		
		/// <summary>
		/// 登录验证
		/// </summary>
		/// <param name="MemberName"></param>
		/// <param name="Password"></param>
		/// <returns></returns>
		public object Login(object MemberName,object Password)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Member_Login";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberName;
			oParms[1].Value=Password;
			
			return SqlHelper.ExecuteScalar(Data.ConnString, spName, oParms);
		}
		public object Login_MD5(object MemberName,object Password)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberName",MemberName);
			oParms[1] = new SqlParameter("@Password",Password);

			using(DataSet ds = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select MemberID from Member where MemberName=@MemberName and MD5PWD=@Password",oParms))
			{
				if(ds.Tables[0].Rows.Count>0)
					return ds.Tables[0].Rows[0]["MemberID"];
				else
					return null;
			}
		}
		/// <summary>
		/// 重设密码
		/// </summary>
		/// <param name="MemberID">会员编号</param>
		/// <param name="Password">密码</param>
		public void UpdatePassword(object MemberID,object Password)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@Password",Password);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set Password=@Password where MemberID=@MemberID",oParms);
		}
		public void UpdatePassword_MD5(object MemberID,object Password)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@Password",Password);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set Password=@Password,MD5PWD=null where MemberID=@MemberID",oParms);
		}
		public void UpdatePoint(object MemberID,object Point)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@Point",Point);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set Point=@Point where MemberID=@MemberID",oParms);

		}

		public void UpdateCompanyID(object MemberName,object CompanyID)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberName",MemberName);
			oParms[1] = new SqlParameter("@CompanyID",CompanyID);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set CompanyMemberID=@CompanyID where MemberName=@MemberName",oParms);
		}
		/// <summary>
		/// 搜索包含指定关键字的用户
		/// </summary>
		/// <param name="MemberName">关键字</param>
		/// <param name="PageSize">每页显示记录数</param>
		/// <param name="PageIndex">页号</param>
		/// <returns></returns>
		public DataSet Search(object MemberName,object PageSize,object PageIndex)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Member_Search";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberName;
			oParms[1].Value=PageSize;
			oParms[2].Value=PageIndex;
			oParms[3].Value=0;

			return SqlHelper.ExecuteDataset(Data.ConnString, spName, oParms);		
		}
		/// <summary>
		/// 返回指定关键字的用户数量
		/// </summary>
		/// <param name="MemberName">关键字</param>
		/// <returns></returns>
		public int Search_Count(object MemberName)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Member_Search";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberName;
			oParms[1].Value=0;
			oParms[2].Value=0;
			oParms[3].Value=1;

			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString, spName, oParms));		
		}
		/// <summary>
		/// 删除用户
		/// </summary>
		/// <param name="MemberID">编号</param>
		public void Delete(object MemberID)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Member_Delete";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberID;

			SqlHelper.ExecuteNonQuery(Data.ConnString, spName, oParms);
		}
		public void Delete_MemberName(object MemberName)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@MemberName",MemberName);

			SqlHelper.ExecuteNonQuery(Data.ConnString, CommandType.Text,"delete Member where MemberName=@MemberName", oParms);
		}
		public void Deduct_Point(object MemberID,object Point,object Remark)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Member_Deduct_Point";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberID;
			oParms[1].Value=Point;
			oParms[2].Value=Remark;

			SqlHelper.ExecuteNonQuery(Data.ConnString, spName, oParms);
		}
		public object Exists_MemberName(object MemberName)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@MemberName",MemberName);

			using(DataSet ds = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select MemberID from Member where MemberName=@MemberName",oParms))
			{
				if(ds.Tables[0].Rows.Count>0)
					return ds.Tables[0].Rows[0]["MemberID"];
				else
					return null;
			}
		}
		public bool Exists_Email(object Email)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@Email",Email);

			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString,CommandType.Text,"select count(*) from Member where Email=@Email",oParms))>0;
		}
		public bool EmailValidCode_Right(object MemberID,object EmailValidCode)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@EmailValidCode",EmailValidCode);

			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString,CommandType.Text,"select count(*) from Member where MemberID=@MemberID and EmailValidCode=@EmailValidCode",oParms))>0;

		}
		public void ActiveEmail(object MemberID)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@MemberID",MemberID);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set EmailHasValid=1 where MemberID=@MemberID",oParms);
		}
		public void SetRoles(object MemberID,object Roles)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberID",MemberID);
			oParms[1] = new SqlParameter("@Roles",Roles);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set Roles=@Roles where MemberID=@MemberID",oParms);
		}
		public DataSet ManagerList()
		{
			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select * from Member where Roles like '%Administrator%'");
		}
		public string MemberName(object MemberID)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@MemberID",MemberID);

			using(DataSet ds = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select MemberName from Member where MemberID=@MemberID",oParms))
			{
				if(ds.Tables[0].Rows.Count>0)
					return ds.Tables[0].Rows[0]["MemberName"].ToString();
				else
					return "";
			}
		}
		public void AddPoint(object Point)
		{
			SqlParameter[] oParms =new SqlParameter[1];
			oParms[0] = new SqlParameter("@Point",Point);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Member set Point=Point+@Point",oParms);
		}
		public void Score2Point(object MemberID,object Score,object Score2Point)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Score2Point";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=MemberID;
			oParms[1].Value=Score;
			oParms[2].Value=Score2Point;
			SqlHelper.ExecuteNonQuery(Data.ConnString,spName, oParms);
		}
		
		public DataSet MemberList(object MemberName)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberName",MemberName);
			

			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"Select MemberName,MemberID from member where MemberName like '%'+@MemberName+'%'",oParms);
		}

		public DataSet MemberList_Company()
		{
			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"Select MemberName,MemberID,TrueName from member where IsCompany=1");
		}

		public DataSet MemberList_Member()
		{
			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"Select MemberName,MemberID,TrueName from member where IsCompany=0");
		}

		public DataSet CompanyHadMember(object MemberID)
		{
			SqlParameter[] oParms =new SqlParameter[2];
			oParms[0] = new SqlParameter("@MemberID",MemberID);

			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"Select MemberName,MemberID,TrueName from member where CompanyMemberID=@MemberID",oParms);
		}
	}
}
