using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MSXML2;

namespace BLL.Models

{
    
    public class MemberModels
    {
        public string[] files = {"ID","UserName","UserPwd","Roleid","Intro","Logo","UserState","ClassId","Mobile","Email","Address","Phone","QQ","MSN","Sex","BirthDay","ZipCode","Fax","Oline","Regtime","LastLogintime","LastLoginIp","Areid","Name"};
        /// <summary>
        /// 创建一个用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CreateMeber(MODEL.MMember model) {

            bool ret = false;
            Library.SqlProvider.OleDb accdb = new Library.SqlProvider.OleDb();
            #region--sql--
            string str_sql = @"insert into [TB_MC_User]
           ([UserName]
           ,[UserPwd]
           ,[Roleid]
           ,[Intro]
           ,[Logo]
           ,[UserState]
           ,[ClassId]
           ,[Mobile]
           ,[Email]
           ,[Address]
           ,[Phone]
           ,[QQ]
           ,[MSN]
           ,[Sex]
           ,[BirthDay]
           ,[ZipCode]
           ,[Fax]
           ,[Regtime]
           ,[LastLogintime]
           ,[LastLoginIp]
           ,[Areid])
     values
           ('"+model.UserName+@"'
           ,'"+model.UserPwd+@"'
           ,"+model.Roleid.ToString()+@"
           ,'"+model.Intro+@"'
           ,'"+model.Logo+@"'
           ,"+model.UserState.ToString()+@"
           ,"+model.ClassId.ToString()+@"
           ,'"+model.Mobile+@"'
           ,'"+model.Email+@"'
           ,'"+model.Address+@"'
           ,'"+model.Phone+@"'
           ,'"+model.Qq+@"'
           ,'"+model.Msn+@"'
           ,"+model.Sex.ToString()+@"
           ,'"+model.BirthDay.ToString()+@"'
           ,'"+model.ZipCode.ToString()+@"'
           ,'"+model.Fax.ToString()+@"'
           ,'"+model.Regtime.ToString()+@"'
           ,'"+model.LastLogtime+@"'
           ,'"+model.LastLoginIp+@"'
           ,0)           
            ";
            #endregion
            try {
                accdb.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            
            }catch(Exception ex){}
            
            return ret;
        }
        /// <summary>
        /// 修改一个用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateMember(MODEL.MMember model) {
            // "UserState", "ClassId", "Mobile", "Email", "Address", "Phone", "QQ", 
            //"MSN", "Sex", "BirthDay", "ZipCode", 
            //"Fax", "Oline", "Regtime"，"Name"];
            //["ID", "UserName", "UserPwd", "Roleid", "Intro",

            bool ret = false;
            string str_sql = " update  [tb_mc_user]  set qq='"+model.Qq+"',phone='"+model.Phone+"',address='"+model.Address+"', email='"+model.Email+"', mobile='"+model.Mobile+"' , classid="+model.ClassId.ToString()+", userstate="+model.UserState.ToString()+", zipcode='"+model.ZipCode+"', birthday='"+model.BirthDay.ToString()+"', sex="+model.Sex.ToString()+", msn='"+model.Msn+"', fax='"+model.Fax+"', username='"+model.UserName+"',userpwd='"+model.UserPwd+"',intro='"+model.Intro+"',[name]='"+model.Name+"'  where id="+model.Id.ToString();
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            try
            {
                db.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }
            return ret;
        }
        /// <summary>
        /// 删除一个用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelMember(string id) {

            
            bool ret = false;
            string str_sql = " delete from [tb_mc_user] where id in (" + id + ")";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            try
            {
                db.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }
            return ret;
           
        }
        /// <summary>
        /// 简单模式用户注册
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CreateMemberS(MODEL.MMember model) {
            bool ret = true;
            Library.SqlProvider.OleDb accdb = new Library.SqlProvider.OleDb();
            #region--sql--
            string str_sql = @"INSERT INTO [TB_MC_User]
           ([UserName]
           ,[UserPwd]
           ,[Email])
     VALUES
           ('" + model.Email + @"'
           ,'" + model.UserPwd + @"'
           ,'" + model.Email + @"'
           )           
            ";
            #endregion
            try {
                
                accdb.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }catch(Exception ex){}
            
            return ret;
        }
        /// <summary>
        /// 用户登陆验证
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckUser(MODEL.MMember model){
        
            bool ret=false;
            string str_sql = "select * from tb_mc_user where username='" + model.UserName + "' and userpwd='" + model.UserPwd + "' ";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try {

               dt= db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];

            }catch(Exception ex){}
            if(dt.Rows.Count>0){
               
                ret = true;
            }
            return ret;
        }
        /// <summary>
        /// 定单跟新用户信息
        /// </summary>
        /// <returns></returns>
        public bool UpdateSimgUser( MODEL.MMember model) { 
        
            

            bool ret=false;
            string str_sql = " update [tb_mc_user] set [name]='"+model.Name+"',zipcode='"+model.ZipCode+"',mobile='"+model.Mobile+"',address='"+model.Address+"' where username='"+model.UserName+"' ";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try {

               dt= db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];

            }catch(Exception ex){}
            if(dt.Rows.Count>0){
               
                ret = true;
            }
            return ret;
        }
        public bool UpdateCenterUser(MODEL.MMember model)
        {



            bool ret = false;
            string str_sql = " update [tb_mc_user] set [name]='" + model.Name + "',zipcode='" + model.ZipCode + "',mobile='" + model.Mobile + "',address='" + model.Address + "',email='"+model.Email+"',sex="+model.Sex.ToString()+",phone='"+model.Phone+"',qq='"+model.Qq+"',msn='"+model.Msn+"' where username='" + model.UserName + "' ";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {

                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];

            }
            catch (Exception ex) { }
            if (dt.Rows.Count > 0)
            {

                ret = true;
            }
            return ret;
        }
        /// <summary>
        /// 验证电子邮件是否已经存在
        /// </summary>
        /// <param name="useremail"></param>
        /// <returns></returns>
        public bool CheckUserEmail(string useremail) {

            bool ret = false;
            string str_sql = "select * from [tb_mc_user] where [email]='"+useremail+"'";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {
                dt= db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
               
            }
            catch (Exception ex) { }
            if (dt.Rows.Count == 0) {

                ret = true;
            }
            return ret;
        }
        /// <summary>
        /// 按指定条件获得用户信息
        /// </summary>
        /// <param name="fiter"></param>
        /// <returns></returns>
        public DataTable GetuserInfoByFiter(string fiter) {

            DataTable ret = new DataTable();
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            string str_sql = "  select  * from [tb_mc_user] where  " + fiter;
            DataTable dt = new DataTable();
            try
            {

                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
            }
            catch (Exception ex) { }
            ret = dt;
            return ret;
        }
        /// <summary>
        /// 通过用户名取得用户id
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public string GetUserIdByName(string username) {

            string ret = string.Empty;
            DataTable dt = new DataTable();
            dt = GetuserInfoByFiter(" username= '" + username + "'");
            ret = dt.Rows[0]["id"].ToString();

            return ret;
        }
        /// <summary>
        /// 修改密码提示问题
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ChangePwdAnswer( MODEL.MMember model ) {

            bool ret = false;
            string str_sql = "update  [tb_mc_user] set UserPwdQuestion='" + model.UserPwdQuestion + "',UserPwdPwd='" + model.UserPwdPwd + "' where id="+model.Id.ToString();
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            try
            {
                db.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }
            return ret;
        }
        /// <summary>
        /// 修改用户注册信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ChangeMemberInfo(MODEL.MMember model) {

            bool ret = false;
            string str_sql = "update  [tb_mc_user] set [name]='" + model.Name + "',BirthDay='" + model.BirthDay + "',address='"+model.Address+"',qq='"+model.Qq+"',email='"+model.Email+"',sex="+model.Sex.ToString()+",mobile='"+model.Mobile+"',phone='"+model.Phone+"',zipcode='"+model.ZipCode+"' where id=" + model.Id.ToString();
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            try
            {
                db.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }
            return ret;

        }
        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ChangePwd(MODEL.MMember model,string pwdo) {

            bool ret = false;
            string str_sqln = "update [tb_mc_user] set userpwd='" + model.UserPwd + "' where username='" + model.UserName+"' and userpwd='"+pwdo+"' ";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {
                dt= db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sqln).Tables[0];
              
            }
            catch (Exception ex) { }
            if(dt.Rows.Count==0){

                return ret;
            }
            string str_sql = "update  [tb_mc_user] set userpwd='"+model.UserPwd+"' where username='"+model.UserName+"'" ;
            try
            {
                db.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }
            return ret;
        }
        /// <summary>
        /// 获取所有用户信息分页
        /// </summary>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <param name="fiter"></param>
        /// <returns></returns>
        public string AllMember(string start,string limit,string fiter) {

            string ret = string.Empty;
            string str_sql = "select top " + limit + " * from [tb_mc_user] where id not in( select " + start + " id from [tb_mc_user] order by id desc ) order by id desc";
            if (start == "0")
            {

                str_sql = " select top " + limit + " * from [tb_mc_user] order by id desc ";
            }
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            string tcount = "0";
            try
            {
                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
                tcount = db.ExecuteScalar(COMM.PucConfig.ConnectionStringA, CommandType.Text, "select count(*) from [tb_mc_user] ").ToString();
            }
            catch (Exception ex) { }

            BLL.JSONHelper jsons = new BLL.JSONHelper();



            foreach (DataRow dr in dt.Rows)
            {

                foreach (string s in files)
                {

                    jsons.AddItem(s, dr[s].ToString());
                }
                jsons.ItemOk();

            }


            jsons.totlalCount = Convert.ToInt32(tcount);
            ret = jsons.ToString();
            return ret;
        }
        /// <summary>
        /// 获取一条管理信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetMemberInfoByid(string id){

            string ret = string.Empty;
            string str_sql = "select * from [tb_mc_user] where id=" + id;
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {
                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
            }
            catch (Exception ex) { }
            BLL.FormJSONHelper jsons = new BLL.FormJSONHelper();
            jsons.success = true;


            foreach (DataRow dr in dt.Rows)
            {

                foreach (string s in files)
                {

                    jsons.AddItem(s, dr[s].ToString());
                }
                jsons.ItemOk();

            }



            ret = jsons.ToString();
            return ret;
        }

        /// <summary>
        /// 简单模式用户注册
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CreateMemberTemp(MODEL.MMember model)
        {
            bool ret = true;
            Library.SqlProvider.OleDb accdb = new Library.SqlProvider.OleDb();
            if (string.IsNullOrEmpty(model.UserName)) {

                model.UserName = model.Email;
            }
            #region--sql--
            string str_sql = @"INSERT INTO [TB_MC_User]
           ([UserName]
           ,[UserPwd]
           )
     VALUES
           ('" + model.UserName + @"'
           ,'" + model.UserPwd + @"'
           
           )           
            ";
            #endregion
            try
            {

                accdb.ExecuteNonQuery(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }

            return ret;
        }
        /// <summary>
        /// 取得短信密码
        /// </summary>
        /// <param name="smspass"></param>
        /// <param name="sitepass"></param>
        /// <param name="?"></param>
        public void GetSmsPassInfo(ref string smspass,ref string sitepass,ref string smsRpass) {

            DataTable dt=new DataTable();
            DataTable dt1=new DataTable();
            string str_sql = "select * from  [tb_mc_sms] '";
            string str_sql1="select * from [tb_mc_config] where id=5,or id=16 order by id desc";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
           
            try
            {
                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
                dt1= db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql1).Tables[0];
            }
            catch (Exception ex) { }
            smsRpass = dt.Rows[0]["smspass"].ToString();
            smspass = dt1.Rows[0]["value"].ToString();
            sitepass = dt1.Rows[1]["value"].ToString();
           
        }
        /// <summary>
        /// 设置短信一次性密码
        /// </summary>
        /// <param name="smsRpass"></param>
        /// <returns></returns>
        public bool SetSmsPass(string smsRpass) {
        
                     bool ret=false;
            string str_sql = "update  [tb_mc_sms] set smspass='"+ smsRpass+"'";
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {
                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
            }
            catch (Exception ex) { }
       
        return ret;
 }
        /// <summary>
        /// 发送短信
        /// </summary>
        /// <param name="MobileNo"></param>
        /// <param name="Smscontent"></param>
        /// <param name="Rcontent"></param>
        public  void SendMsg(string smspass,string smsrpass,string sitepass,string MobileNo, string Smscontent, ref string Rcontent)
        {
            string url = "http://www.mcvrlab.com/content/webservices/sms.ashx?no=" + MobileNo + "&x_passo=" + smsrpass + "&x_pass=" + sitepass + "&x_content=" + Smscontent + "&x_sitepass" + sitepass;
            MSXML2.XMLHTTP xre = new XMLHTTP();
            xre.open("GET", url, false, null, null);
            xre.send("");
            Rcontent = xre.responseText.ToString();
        }
        /// <summary>
        /// 取得收藏夹
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public string GetFav(string userid)
        {
            string ret = string.Empty;
            string str_sql = "select fav from  [tb_mc_fav] where userid=" +userid;
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {
                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
            }
            catch (Exception ex) { }
            if (dt.Rows.Count == 0)
            {

                return ret;
            }
            else {

                ret = dt.Rows[0]["fav"].ToString();
            }
            return ret;
        }
        /// <summary>
        /// 设置收藏夹
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="fav"></param>
        /// <returns></returns>
        public bool SetFav(string userid, string fav) {
            bool ret = false;
            string strfav = string.Empty;
            string str_sql = "select fav from  [tb_mc_fav] where userid=" + userid;
            Library.SqlProvider.OleDb db = new Library.SqlProvider.OleDb();
            DataTable dt = new DataTable();
            try
            {
                dt = db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql).Tables[0];
            }
            catch (Exception ex) { }
            if (dt.Rows.Count > 0)
            {

                strfav =  "," + fav;
                str_sql = " update [tb_mc_fav] set fav='" + strfav + "' where userid=" + userid;
            }
            else {

                strfav = "," + fav;
                str_sql = " insert into [tb_mc_fav] ( userid,fav) values("+userid+",'"+strfav+"')" ;
            }

            try
            {
                db.ExecuteDataset(COMM.PucConfig.ConnectionStringA, CommandType.Text, str_sql);
                ret = true;
            }
            catch (Exception ex) { }
            return ret;
        }
        public string CheckUserId()
        {

            string ret = string.Empty;
            if (System.Web.HttpContext.Current.Session["MemberName"] == null)
            {

                System.Web.HttpContext.Current.Response.Redirect("/member/member_login.aspx");

            }
            else
            {

                ret = System.Web.HttpContext.Current.Session["MemberName"].ToString();
            }

            return ret;

        }
        public string CheckUserIdOrder()
        {

            string ret = string.Empty;
            if (System.Web.HttpContext.Current.Session["MemberName"] == null)
            {

                System.Web.HttpContext.Current.Response.Redirect("/Order/Order_Login.aspx");

            }
            else
            {

                ret = System.Web.HttpContext.Current.Session["MemberName"].ToString();
            }

            return ret;

        }
    }
}
