public bool SaveRoleAccessRightDetails(RoleAccessRightsBE roleAccessBE)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Sp_UM_RoleAccessRightUpdate";
cmd.Parameters.Add("@PRAId", SqlDbType.Int).Value = roleAccessBE.PRAId;
cmd.Parameters.Add("@RoleId", SqlDbType.Int).Value = roleAccessBE.RoleID;
cmd.Parameters.Add("@AccessRights", SqlDbType.Int).Value = roleAccessBE.AddRec;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int).Value = roleAccessBE.LastModifiedBy;
cmd.Connection = con;
//con.Open();
int Count = cmd.ExecuteNonQuery();
con.Close();
if (Count == 2)
{
return true;
}
else
{
return false;
}
}
public bool UpdateEmailCredentials(CompanySetupBE company)
{
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlCommand cmd = new SqlCommand("Sp_UM_CompanySettingCredentialsUpdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = company.UserName;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = company.Password;
cmd.Parameters.Add("@ServerName", SqlDbType.VarChar).Value = company.IPAddress;
cmd.Parameters.Add("@PortNo", SqlDbType.VarChar).Value = company.PortNo;
//con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
if (count == 2)
{
return true;
}
else
{
return false;
}
}
public bool SaveForgotPassDetails(ForgotPasswordBE forgotpass)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Sp_UM_ForgotPasswordInsert";
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = forgotpass.UserName;
cmd.Parameters.Add("@EmailId", SqlDbType.VarChar).Value = forgotpass.PrimaryEmailId;
cmd.Parameters.Add("@SecretQuestion", SqlDbType.VarChar).Value = forgotpass.SecretQuest;
cmd.Parameters.Add("@SecretAns", SqlDbType.VarChar).Value = forgotpass.SecretAns;
cmd.Connection = con;
//con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
if (count > 0)
{
return true;
}
else
{
return false;
}
}
public bool DeleteRole(RoleBE role)
{
int msg = 0;
SqlCommand cmd = new SqlCommand();
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
cmd = new SqlCommand("Sp_UM_RolesDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@RoleId", SqlDbType.Int).Value = role.RoleId;
cmd.Parameters.Add("@ReturnCode", System.Data.SqlDbType.Int);
cmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
//con.Open();
int count = cmd.ExecuteNonQuery();
msg = Convert.ToInt32(cmd.Parameters["@ReturnCode"].Value);
con.Close();
if (msg==0)
{
return true;
}
else
{
return false;
}
}
public bool GetEmailMessageHTML(string EmailMessageKey, ref string EmailMessageHTML)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand("Sp_UM_EmailMessageTemplateGetByKey", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@EmailMessageKey", System.Data.SqlDbType.VarChar, 50).Value = EmailMessageKey;
cmd.Parameters.Add("@EmailMessageHTML", System.Data.SqlDbType.VarChar, 1500);
cmd.Parameters["@EmailMessageHTML"].Direction = ParameterDirection.Output;
//con.Open();
int Count = cmd.ExecuteNonQuery();
EmailMessageHTML = cmd.Parameters["@EmailMessageHTML"].Value.ToString();
con.Close();
if (EmailMessageHTML.Trim().Length > 0 && EmailMessageHTML!="")
{
return true;
}
else
{
return false;
}
}
public Boolean Validate(ref UserBE user)
{
CommonDAL commondal = new CommonDAL();
DataTable dt = new DataTable();
SqlConnection con = commondal.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_UserInfo", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@UserName ", SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter("@EncPass", SqlDbType.VarChar);
p1.Value = user.UserName;
p2.Value = user.EncPass;
da.SelectCommand.Parameters.Add(p1);
da.SelectCommand.Parameters.Add(p2);
da.Fill(dt);
if (loadUserProfile(ref user, ref dt))
{
return true;
}
else
{
return false;
}
}
public bool AddRole(RoleBE role)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Sp_UM_RolesInsert";
cmd.Parameters.Add("@RoleShortDesc", SqlDbType.VarChar).Value = role.RoleShortDesc;
cmd.Parameters.Add("@RoleLongDesc", SqlDbType.VarChar).Value = role.RoleLongDesc;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int).Value = role.LastModifiedBy;
cmd.Connection = con;
//con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
if (count > 0)
{
return true;
}
else
{
return false;
}
}
public bool ForgotPassword(ref DataTable dt, ForgotPasswordBE forgotpass)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_ForgotPasswordGetDetailsofUserByUserName", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@UserName ", SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter("@PrimaryEmailId", SqlDbType.VarChar);
p1.Value = forgotpass.UserName;
p2.Value = forgotpass.PrimaryEmailId;
da.SelectCommand.Parameters.Add(p1);
da.SelectCommand.Parameters.Add(p2);
da.Fill(dt);
if (dt.Rows.Count == 1)
{
return true;
}
else
{
return false;
}
}
public DataSet GetSecretQuestion()
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand("Sp_UM_GetSecretQuest", con);
cmd.CommandType = CommandType.StoredProcedure;
DataSet Ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(Ds);
return Ds;
}
public bool GetEmailMessageHTML(string EmailMessageKey, ref string EmailMessageHTML)
{
CommonDAL commonDAL = new CommonDAL();
if (commonDAL.GetEmailMessageHTML(EmailMessageKey, ref EmailMessageHTML))
{
return true;
}
else
{
return false;
}
}
public bool GetAllRoleId(ref DataTable dt)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_GetAllRoleId", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(dt);
if (loadAllRoleId(ref dt))
{
return true;
}
else
{
return false;
}
}
public bool GetAllRoleDetails(ref DataTable dt)
{
// DataTable dt1 = new DataTable();
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_RolesAllDetails", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(dt);
if (loadRoleProfile(ref dt))
{
return true;
}
else
{
return false;
}
}
public bool GetEmailCredentials(ref DataTable dtEmailCredentials)
{
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_GetCompanySettingCredentials", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(dtEmailCredentials);
if (dtEmailCredentials.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
public bool GetAllRoleMembers(ref DataTable dt, ref RoleMemberBE roleMember)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
//con.Open();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_GetAllRoleMembers", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@RoleId", SqlDbType.Int);
p1.Value = roleMember.RoleId;
da.SelectCommand.Parameters.Add(p1);
da.Fill(dt);
if (loadRoleMembersProfile(ref dt))
{
return true;
}
else
{
return false;
}
}
public bool AddRoleMember(RoleMemberBE roleMember)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Sp_UM_RoleMembersInsert";
cmd.Parameters.Add("@RoleId", SqlDbType.Int).Value = roleMember.RoleId;
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = roleMember.UserId;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int).Value = roleMember.LastModifiedBy;
cmd.Connection = con;
//con.Open();
int count = cmd.ExecuteNonQuery();
if (count ==1)
{
return true;
}
else
{
return false;
}
}
public bool GetAllUserDetails(ref DataTable dt)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_UsersAllDetails", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(dt);
//call function to decrypt the passwords of each row
DecryptPasswords(ref dt);
if (GetUserTable(ref dt))
{
return true;
}
else
{
return false;
}
}
public bool QueriesTotalViewCountrIncrement(int QId, int UserId) //am??
{
CommonDAL commDAL = new CommonDAL();
if (commDAL.QueriesTotalViewCountrIncrement(QId, UserId))
{
return true;
}
else
{
return false;
}
}
public bool ViewRole(RoleBE role)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_GetAllRolesByRoleID", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@RoleId ", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
p1.Value = role.RoleId;
da.SelectCommand.Parameters.Add(p1);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
//string title = ds.Tables[0].Rows[0]["Title"].ToString();
role.RoleShortDesc = dt.Rows[0]["RoleLongDesc"].ToString();
return true;
}
else
{
return false;
}
}
public DataSet InsertUser(UserBE userInfo)
{
DataSet ds = new DataSet();
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlCommand cmd = new SqlCommand("InsertUser", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);
cmd.Parameters.AddWithValue("@FName", userInfo.FirstName);
cmd.Parameters.AddWithValue("@LName", userInfo.LastName);
cmd.Parameters.AddWithValue("@EmailAddress", userInfo.PrimaryEmailId);
cmd.Parameters.AddWithValue("@SecretQuest", userInfo.SecretQuest );
cmd.Parameters.AddWithValue("@SecretAns", userInfo.SecretAns);
//con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
con.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
private void DecryptPasswords(ref DataTable dt)
{
CommonDAL cmndal = new CommonDAL();
for (int dr = 0; dr < dt.Rows.Count; dr++ )
{
dt.Rows[dr]["Pass"] = cmndal.Decrypt(dt.Rows[dr]["EncPass"].ToString(),false);
}
}
public bool QueriesTotalViewCountrIncrement(int QId, int UserId) //am??
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
//SqlConnection con = Connection();
SqlCommand cmd = new SqlCommand("Sp_QA_QueriesTotalViewCountrIncrement", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@QId", System.Data.SqlDbType.Int).Value = QId;
cmd.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = UserId;
int intRetCode = cmd.ExecuteNonQuery();
if (intRetCode != 0)
{
con.Close();
return false;
}
con.Close();
return true;
}
public DataSet GetUsers()
{
DataSet ds = new DataSet();
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlCommand cmd = new SqlCommand("GetUsers", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
//con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
con.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public Boolean ViewUser(UserBE user)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_GetAllDetalisOfUserByUserId", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@UserId ", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
p1.Value = user.UserId;
da.SelectCommand.Parameters.Add(p1);
da.Fill(dt);
//call function to decrypt the passwords of each row
DecryptPasswords(ref dt);
if (dt.Rows.Count > 0)
{
//string title = ds.Tables[0].Rows[0]["Title"].ToString();
user.UserName = dt.Rows[0]["UserName"].ToString();
return true;
}
else
{
return false;
}
}
public bool UserAccessRight(UserBE user,ref DataTable dt)
{
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlDataAdapter da = new SqlDataAdapter("Sp_UM_UserAccessRights", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@UserId ", SqlDbType.Int);
p1.Value = user.UserId;
da.SelectCommand.Parameters.Add(p1);
da.Fill(dt);
if (dt.Rows.Count>0)
{
return true;
}
else
{
return false;
}
}
public int SaveConfirmation(string UserId, string Key, string Password)
{
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlCommand cmd = new SqlCommand("SaveConfirmation", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@Key", Key);
cmd.Parameters.AddWithValue("@Password", Password);
//con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
//
public DataSet CheckConfirmationRequest(string UserId, string key)
{
DataSet ds = new DataSet();
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlCommand cmd = new SqlCommand("CheckConfirmationRequest", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@Key", key);
//con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
con.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public Boolean Modify(UserBE user)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand("Sp_UM_UsersUpdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = user.UserId;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = user.UserName;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = user.FirstName;
cmd.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = user.MiddleName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = user.LastName;
cmd.Parameters.Add("@PrimaryEmailId", SqlDbType.VarChar).Value = user.PrimaryEmailId;
cmd.Parameters.Add("@SecondaryEmailId", SqlDbType.VarChar).Value = user.SecondaryEmailId;
cmd.Parameters.Add("@MobileNo", SqlDbType.VarChar).Value = user.MobileNo;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = user.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = user.Address2;
cmd.Parameters.Add("@Street", SqlDbType.VarChar).Value = user.Street;
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = user.City;
cmd.Parameters.Add("@State1", SqlDbType.VarChar).Value = user.State1;
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = user.Country;
cmd.Parameters.Add("@SecretQuest", SqlDbType.VarChar).Value = user.SecretQuest;
cmd.Parameters.Add("@SecretAns", SqlDbType.VarChar).Value = user.SecretAns;
cmd.Parameters.Add("@EncPass", SqlDbType.VarChar).Value = user.EncPass;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int).Value = user.LastModifiedBy;
cmd.Connection = con;
//con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
if (count ==2)
{
return true;
}
else
{
return false;
}
}
public bool ResetPassword(UserBE user)
{
CommonDAL commonDAL = new CommonDAL();
SqlConnection con = commonDAL.Connection();
SqlCommand cmd = new SqlCommand("Sp_UM_ResetUserPassword", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = user.UserId;
cmd.Parameters.Add("@EncPass", SqlDbType.VarChar).Value = user.EncPass;
//con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
if (count > 0)
{
return true;
}
else
{
return false;
}
}
public bool KOTotalViewCountrIncrement(int KOId, int UserId) //am??
{
CommonDAL commDAL = new CommonDAL();
if (commDAL.KOTotalViewCountrIncrement(KOId, UserId))
{
return true;
}
else
{
return false;
}
}
public bool AddUser(UserBE user , ref int ReturnOutput)
{
CommonDAL commondal = new CommonDAL();
SqlConnection con = commondal.Connection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Sp_UM_UsersInsert";
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = user.UserName;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = user.FirstName;
cmd.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = user.MiddleName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = user.LastName;
cmd.Parameters.Add("@PrimaryEmailId", SqlDbType.VarChar).Value = user.PrimaryEmailId;
cmd.Parameters.Add("@SecondaryEmailId", SqlDbType.VarChar).Value = user.SecondaryEmailId;
cmd.Parameters.Add("@MobileNo", SqlDbType.VarChar).Value = user.MobileNo;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = user.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = user.Address2;
cmd.Parameters.Add("@Street", SqlDbType.VarChar).Value = user.Street;
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = user.City;
cmd.Parameters.Add("@State1", SqlDbType.VarChar).Value = user.State1;
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = user.Country;
cmd.Parameters.Add("@SecretQuest", SqlDbType.VarChar).Value = user.SecretQuest;
cmd.Parameters.Add("@SecretAns", SqlDbType.VarChar).Value = user.SecretAns;
cmd.Parameters.Add("@EncPass", SqlDbType.VarChar).Value = user.EncPass;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int).Value = user.LastModifiedBy;
cmd.Parameters.Add("@ReturnCode", SqlDbType.Int);
cmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
cmd.Connection = con;
//con.Open();
int count = cmd.ExecuteNonQuery();
ReturnOutput=Convert.ToInt32(cmd.Parameters["@ReturnCode"].Value);
con.Close();
if (count ==1 || ReturnOutput>0)
{
return true;
}
else
{
return false;
}
}
