public ReturnBool update_password(bl_Usr_Registration ur)
{
using (TransactionScope transScope = new TransactionScope())
{
query = @"insert into user_login_log select * from user_login where applicant_verified=@applicant_verified and user_id=@user_id";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant_verified", ur.Verified),
new MySqlParameter("user_id", ur.User_Id)
};
rb = db.executeInsertQuery(query, pm);
if (rb.status == true)
{
query = @"update user_login set applicant_password=@applicant_password where applicant_verified=@applicant_verified and user_id=@user_id";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("applicant_password", ur.Password),
new MySqlParameter("applicant_verified", ur.Verified),
new MySqlParameter("user_id", ur.User_Id)
};
rb = db.executeUpdateQuery(query, pmm);
}
if (rb.status == true)
{
query = @"insert into user_registration_log select * from user_registration where applicant_active=@applicant_verified and applicant_mobile=@applicant_mobile";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("applicant_verified", ur.Verified),
new MySqlParameter("applicant_mobile", ur.User_Id)
};
rb = db.executeInsertQuery(query, pmm);
}
if (rb.status == true)
{
query = @"update user_registration set applicant_password=@applicant_password where applicant_active=@applicant_verified and applicant_mobile=@applicant_mobile";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("applicant_password", ur.Password),
new MySqlParameter("applicant_verified", ur.Verified),
new MySqlParameter("applicant_mobile", ur.User_Id)
};
rb = db.executeUpdateQuery(query, pmm);
}
if (rb.status == true)
{
transScope.Complete();
}
}
return(rb);
}
public ReturnDataTable select_last_login(bl_Usr_Registration ur)
{
query = "SELECT date_format(login_time,'%Y-%m-%d %H:%i:%s') as login_time FROM login_trail WHERE user_id=@user_id order by login_time desc limit 1";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("user_id", ur.User_Id)
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnDataTable select_default_page(bl_Usr_Registration ur)
{
query = "SELECT * FROM role WHERE Role_id=@Role_id";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("Role_id", ur.Role_Id)
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnDataTable user_login_details_for_forgot_password(bl_Usr_Registration ur)
{
query = "select * from user_login u where u.applicant_verified=@applicant_verified and u.user_id=@user_id";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("user_id", ur.Cell_No),
new MySqlParameter("applicant_verified", ur.Verified)
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnDataTable user_login(bl_Usr_Registration ur)
{
query = "SELECT * FROM user_login WHERE applicant_email=@applicant or applicant_mobile=@applicant and applicant_verified=@applicant_verified and applicant_password=@applicant_password";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant", ur.Login_Id),
new MySqlParameter("applicant_verified", ur.Verified),
new MySqlParameter("applicant_password", ur.Password)
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnDataTable check_profile_update(bl_Usr_Registration ur)
{
query = "select * from user_login s where s.user_id=@user_id";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("user_id", ur.User_Id)
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnBool Update_user_profile(bl_Usr_Registration ur)
{
query = @"update user_login set profile_update=@profile_update where user_id=@user_id";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("user_id", ur.User_Id),
new MySqlParameter("profile_update", "Y")
};
rb = db.executeUpdateQuery(query, pm);
return(rb);
}
public ReturnDataTable check_user_email(bl_Usr_Registration ur)
{
query = "SELECT * FROM user_registration WHERE applicant_email=@applicant_email and applicant_email_verified=@applicant_email_verified";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant_email", ur.Email),
new MySqlParameter("applicant_email_verified", "Y")
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnDataTable check_user_mobile(bl_Usr_Registration ur)
{
query = "SELECT * FROM user_registration WHERE applicant_mobile=@applicant_mobile and applicant_mobile_verified=@applicant_mobile_verified";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant_mobile", ur.Telephone_No),
new MySqlParameter("applicant_mobile_verified", "Y")
};
dt = db.executeSelectQuery(query, pm);
return(dt);
}
public ReturnBool sms_log(bl_Usr_Registration lt)
{
query = @" INSERT INTO sms_trail (mobile_no,client_ip,message,success, type)
VALUES (@mobile_no,@client_ip,@message,@success, @type)";
MySqlParameter[] pr = new MySqlParameter[] {
new MySqlParameter("mobile_no", lt.Cell_No),
new MySqlParameter("client_ip", lt.ClientIp),
new MySqlParameter("message", lt.Message),
new MySqlParameter("success", lt.Success),
new MySqlParameter("type", lt.Type)
};
rb = db.executeInsertQuery(query, pr);
return(rb);
}
public static string Check_user_email(string email)
{
string retval = "";
ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
dl_Usr_Registration db = new dl_Usr_Registration();
bl_Usr_Registration ur = new bl_Usr_Registration();
ur.Email = email;
dt = db.check_user_email(ur);
if (dt.table.Rows.Count > 0)
{
retval = "false";
}
else
{
retval = "true";
}
return(retval);
}
public static string Check_user_mobile(string mobile)
{
string retval = "";
ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
dl_Usr_Registration db = new dl_Usr_Registration();
bl_Usr_Registration ur = new bl_Usr_Registration();
ur.Telephone_No = mobile;
dt = db.check_user_mobile(ur);
if (dt.table.Rows.Count > 0)
{
retval = "false";
}
else
{
retval = "true";
}
return(retval);
}
//public ReturnDataTable check_user_email(bl_Usr_Registration ur)
//{
// query = "SELECT * FROM user_registration WHERE applicant_email=@applicant_email and applicant_email_verified=@applicant_email_verified";
// MySqlParameter[] pm = new MySqlParameter[]
// {
// new MySqlParameter("applicant_email",ur.Email),
// new MySqlParameter("applicant_email_verified","Y")
// };
// dt = db.executeSelectQuery(query, pm);
// return dt;
//}
public ReturnBool Update_user_signup_internally(bl_Usr_Registration ur)
{
using (TransactionScope transScope = new TransactionScope())
{
query = @"update user_registration set applicant_active=@applicant_active,applicant_mobile_verified=@applicant_mobile_verified
where applicant_mobile=@applicant_mobile and applicant_mobile_verified=@applicant_mobile_verifiedd and
applicant_active=@applicant_activee and date_format(date_time,'%Y/%m/%d')=@date_time";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant_mobile", ur.Cell_No),
new MySqlParameter("applicant_active", "Y"),
new MySqlParameter("applicant_mobile_verified", "Y"),
new MySqlParameter("applicant_mobile_verifiedd", "N"),
new MySqlParameter("applicant_activee", "N"),
new MySqlParameter("date_time", ur.Action_Date)
};
rb = db.executeUpdateQuery(query, pm);
if (rb.status == true)
{
query = @"update user_login set applicant_verified=@applicant_verified where
applicant_mobile=@applicant_mobile and
applicant_verified=@applicant_verifiedd and date_format(date_time,'%Y/%m/%d')=@date_time";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("applicant_mobile", ur.Cell_No),
new MySqlParameter("applicant_verified", "Y"),
new MySqlParameter("applicant_verifiedd", "N"),
new MySqlParameter("date_time", ur.Action_Date)
};
rb = db.executeUpdateQuery(query, pmm);
}
if (rb.status == true)
{
if (ur.User_type == "USER")
{
query = @"insert into user_profile_master (user_id,client_ip,date_time,profile_update) VALUES(@user_id,@client_ip,@date_time,@profile_update)";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("user_id", ur.Cell_No),
new MySqlParameter("client_ip", ur.ClientIp),
new MySqlParameter("profile_update", "N"),
new MySqlParameter("date_time", ur.Action_Date)
};
rb = db.executeInsertQuery(query, pmm);
}
//else if (ur.User_type == "DOC")
//{
// query = @"insert into doctor_profile_master (user_id,client_ip,date_time,profile_update) VALUES(@user_id,@client_ip,@date_time,@profile_update)";
// MySqlParameter[] pmm = new MySqlParameter[]
// {
//new MySqlParameter("user_id",ur.Cell_No),
//new MySqlParameter("client_ip",ur.ClientIp),
//new MySqlParameter("profile_update","N"),
//new MySqlParameter("date_time",ur.Action_Date)
// };
// rb = db.executeInsertQuery(query, pmm);
//}
if (rb.status == true)
{
query = @"update user_family_members set verified=@verified where
mobile=@mobile and date_format(date_time,'%Y/%m/%d')=@date_time";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("mobile", ur.Cell_No),
new MySqlParameter("verified", "Y"),
new MySqlParameter("date_time", ur.Action_Date)
};
rb = db.executeUpdateQuery(query, pmm);
}
}
if (rb.status == true)
{
transScope.Complete();
}
}
return(rb);
}
public ReturnBool Insert_user_signup(bl_Usr_Registration ur)
{
using (TransactionScope transScope = new TransactionScope())
{
query = @"insert into user_registration (applicant_first_name,applicant_last_name,applicant_email,applicant_mobile,
applicant_password,client_ip,applicant_active,applicant_mobile_verified,applicant_email_verified,date_time,role_id,user_type,class,stream)
values(@applicant_first_name,@applicant_last_name,@applicant_email,@applicant_mobile,@applicant_password,@client_ip,
@applicant_active,@applicant_mobile_verified,@applicant_email_verified,@date_time,@role_id,@user_type,@class,@stream)";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant_first_name", ur.Name),
new MySqlParameter("applicant_last_name", ur.Last_name),
new MySqlParameter("applicant_email", ur.Email),
new MySqlParameter("applicant_mobile", ur.Cell_No),
new MySqlParameter("applicant_password", ur.Password),
new MySqlParameter("client_ip", ur.ClientIp),
new MySqlParameter("applicant_active", ur.Applicant_active),
new MySqlParameter("applicant_mobile_verified", ur.Applicant_mobile_verified),
new MySqlParameter("applicant_email_verified", ur.Applicant_email_verified),
new MySqlParameter("date_time", ur.Action_Date),
new MySqlParameter("role_id", ur.Role_Id),
new MySqlParameter("user_type", ur.User_type),
new MySqlParameter("class", ur.Classes),
new MySqlParameter("stream", ur.Stream)
};
rb = db.executeInsertQuery(query, pm);
if (rb.status == true)
{
query = @"INSERT INTO user_login (applicant_mobile, applicant_email, applicant_first_name,
applicant_last_name, applicant_verified, client_ip, user_id, role_id,user_type,date_time,applicant_password,profile_update,refer_code,extra_3,class,stream)
VALUES (@applicant_mobile,@applicant_email,@applicant_first_name,@applicant_last_name,@applicant_verified,@client_ip,@user_id,@role_id,@user_type,@date_time,@applicant_password,@profile_update,@refer_code,@extra_3,@class,@stream)";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("applicant_mobile", ur.Cell_No),
new MySqlParameter("applicant_email", ur.Email),
new MySqlParameter("applicant_first_name", ur.Name),
new MySqlParameter("applicant_last_name", ur.Last_name),
new MySqlParameter("applicant_verified", ur.Applicant_active),
new MySqlParameter("client_ip", ur.ClientIp),
new MySqlParameter("user_id", ur.Cell_No),
new MySqlParameter("role_id", ur.Role_Id),
new MySqlParameter("user_type", ur.User_type),
new MySqlParameter("date_time", ur.Action_Date),
new MySqlParameter("applicant_password", ur.Password),
new MySqlParameter("profile_update", "N"),
new MySqlParameter("refer_code", ur.Refer_code),
new MySqlParameter("extra_3", ur.Country_code),
new MySqlParameter("class", ur.Classes),
new MySqlParameter("stream", ur.Stream)
};
rb = db.executeInsertQuery(query, pmm);
}
if (rb.status == true)
{
transScope.Complete();
}
}
return(rb);
}
public ReturnBool Insert_user_signup_internally(bl_Usr_Registration ur)
{
using (TransactionScope transScope = new TransactionScope())
{
query = @"insert into user_registration (applicant_first_name,applicant_last_name,applicant_email,applicant_mobile,
applicant_password,client_ip,applicant_active,applicant_mobile_verified,applicant_email_verified,date_time,role_id,user_type)
values(@applicant_first_name,@applicant_last_name,@applicant_email,@applicant_mobile,@applicant_password,@client_ip,
@applicant_active,@applicant_mobile_verified,@applicant_email_verified,@date_time,@role_id,@user_type)";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("applicant_first_name", ur.Name),
new MySqlParameter("applicant_last_name", ur.Last_name),
new MySqlParameter("applicant_email", ur.Email),
new MySqlParameter("applicant_mobile", ur.Cell_No),
new MySqlParameter("applicant_password", ur.Password),
new MySqlParameter("client_ip", ur.ClientIp),
new MySqlParameter("applicant_active", ur.Applicant_active),
new MySqlParameter("applicant_mobile_verified", ur.Applicant_mobile_verified),
new MySqlParameter("applicant_email_verified", ur.Applicant_email_verified),
new MySqlParameter("date_time", ur.Action_Date),
new MySqlParameter("role_id", ur.Role_Id),
new MySqlParameter("user_type", ur.User_type)
};
rb = db.executeInsertQuery(query, pm);
if (rb.status == true)
{
query = @"INSERT INTO user_login (applicant_mobile, applicant_email, applicant_first_name,
applicant_last_name, applicant_verified, client_ip, user_id, role_id,user_type,date_time,applicant_password,profile_update,refer_code,user_for)
VALUES (@applicant_mobile,@applicant_email,@applicant_first_name,@applicant_last_name,@applicant_verified,@client_ip,@user_id,@role_id,@user_type,@date_time,@applicant_password,@profile_update,@refer_code,@user_for)";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("applicant_mobile", ur.Cell_No),
new MySqlParameter("applicant_email", ur.Email),
new MySqlParameter("applicant_first_name", ur.Name),
new MySqlParameter("applicant_last_name", ur.Last_name),
new MySqlParameter("applicant_verified", ur.Applicant_active),
new MySqlParameter("client_ip", ur.ClientIp),
new MySqlParameter("user_id", ur.Cell_No),
new MySqlParameter("role_id", ur.Role_Id),
new MySqlParameter("user_type", ur.User_type),
new MySqlParameter("date_time", ur.Action_Date),
new MySqlParameter("applicant_password", ur.Password),
new MySqlParameter("profile_update", "N"),
new MySqlParameter("refer_code", ur.Refer_code),
new MySqlParameter("user_for", ur.Family_member)
};
rb = db.executeInsertQuery(query, pmm);
}
if (rb.status == true)
{
query = @"INSERT INTO user_family_members (family_member, first_name, last_name,
mobile, email_id, client_ip, user_id, date_time,verified)
VALUES (@family_member,@first_name,@last_name,@mobile,@email_id,@client_ip,@user_id,@date_time,@verified)";
MySqlParameter[] pmm = new MySqlParameter[]
{
new MySqlParameter("family_member", ur.Family_member),
new MySqlParameter("first_name", ur.Name),
new MySqlParameter("last_name", ur.Last_name),
new MySqlParameter("client_ip", ur.ClientIp),
new MySqlParameter("user_id", ur.User_Id),
new MySqlParameter("mobile", ur.Cell_No),
new MySqlParameter("email_id", ur.Email),
new MySqlParameter("date_time", ur.Action_Date),
new MySqlParameter("verified", "N"),
};
rb = db.executeInsertQuery(query, pmm);
}
if (rb.status == true)
{
transScope.Complete();
}
}
return(rb);
}
