public static List <MenuInfo> GetMenuFront(int PortalID, string UserName, string CultureCode)
{
List <MenuInfo> lstPages = new List <MenuInfo>();
string StoredProcedureName = "[dbo].[usp_SageMenuGetClientView]";
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@prefix", "---"));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@IsDeleted", 0));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@Username", UserName));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@CultureCode", CultureCode));
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
lstPages.Add(new MenuInfo(int.Parse(SQLReader["PageID"].ToString()), int.Parse(SQLReader["PageOrder"].ToString()), SQLReader["PageName"].ToString(), int.Parse(SQLReader["ParentID"].ToString()), int.Parse(SQLReader["Level"].ToString()), SQLReader["LevelPageName"].ToString(), SQLReader["SEOName"].ToString(), SQLReader["TabPath"].ToString(), bool.Parse(SQLReader["IsVisible"].ToString()), bool.Parse(SQLReader["ShowInMenu"].ToString())));
}
return(lstPages);
}
/// <summary>
/// Connect to the database and check condition for suspended IP.
/// </summary>
/// <param name="IpAddress">IpAddress</param>
/// <returns>Returns True for SuspendedIP Address</returns>
public bool IsSuspendedIP(string IpAddress)
{
string sp = "[dbo].[usp_IsSuspendedIP]";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@IpAddress", IpAddress));
SqlDataReader reader = null;
bool isSuspended = true;
try
{
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
isSuspended = bool.Parse(reader["Suspended"].ToString());
}
reader.Close();
return isSuspended;
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connects to database and returns SageBannerInfo list for given usermodule, portalID and culture.
/// </summary>
/// <param name="UserModuleID">UserModuleID</param>
/// <param name="PortalID">PortalID</param>
/// <param name="CultureCode">CultureCode</param>
/// <returns>List of SageBannerInfo class containing bannername and bannerid</returns>
public List <SageBannerInfo> LoadBannerOnDropDown(int UserModuleID, int PortalID, string CultureCode)
{
SqlDataReader Reader = null;
try
{
List <KeyValuePair <string, object> > Parameter = new List <KeyValuePair <string, object> >();
Parameter.Add(new KeyValuePair <string, object>("@UserModuleID", UserModuleID));
Parameter.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
Parameter.Add(new KeyValuePair <string, object>("@CultureCode", CultureCode));
SQLHandler sqlH = new SQLHandler();
Reader = sqlH.ExecuteAsDataReader("usp_SageBannerGetAllBanner", Parameter);
List <SageBannerInfo> BannerListForDropDown = new List <SageBannerInfo>();
while (Reader.Read())
{
SageBannerInfo obj = new SageBannerInfo();
obj.BannerID = Convert.ToInt32(Reader["BannerID"]);
obj.BannerName = Convert.ToString(Reader["BannerName"]);
BannerListForDropDown.Add(obj);
}
return(BannerListForDropDown);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Reader != null)
{
Reader.Close();
}
}
}
/// <summary>
/// Connects to database and returns RobotsInfo list for given PortalID.
/// </summary>
/// <param name="PortalID">PortalID</param>
/// <returns>RobotsInfo List</returns>
public static List <RobotsInfo> GetRobots(int PortalID)
{
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
SqlDataReader reader = null;
try
{
SQLHandler Objsql = new SQLHandler();
reader = Objsql.ExecuteAsDataReader("[dbo].[usp_SEOGetRobots]", ParaMeterCollection);
List <RobotsInfo> lstRobots = new List <RobotsInfo>();
while (reader.Read())
{
lstRobots.Add(new RobotsInfo(reader["PageName"].ToString(), reader["TabPath"].ToString(), reader["SEOName"].ToString(), reader["Description"].ToString()));
}
return(lstRobots);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <int> GetListPortalIDs()
{
try
{
List <int> objHDSetting = new List <int>();
List <KeyValuePair <string, object> > parameterCollection = new List <KeyValuePair <string, object> >();
SQLHandler sqLH = new SQLHandler();
SqlDataReader result = sqLH.ExecuteAsDataReader("[dbo].[usp_Aspx_CatalogGetPortalIDs]", parameterCollection);
try
{
while (result.Read())
{
objHDSetting.Add(Convert.ToInt32(result["PortalID"]));
}
}
catch (Exception e)
{
throw e;
}
return(objHDSetting);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Connects to database and returns roles details by role name.
/// </summary>
/// <param name="RoleName">Role name.</param>
/// <returns>Role details.</returns>
public RolesManagementInfo GetRoleIDByRoleName(string RoleName)
{
SqlDataReader reader = null;
try
{
SQLHandler SQLH = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@RoleName", RoleName));
reader = SQLH.ExecuteAsDataReader("[dbo].[sp_GetRoleIDByRoleName]", ParamCollInput);
RolesManagementInfo objList = new RolesManagementInfo();
while (reader.Read())
{
objList.ApplicationId = new Guid(reader["ApplicationId"].ToString());
objList.RoleId = new Guid(reader["RoleId"].ToString());
objList.RoleName = reader["RoleName"].ToString();
objList.LoweredRoleName = reader["LoweredRoleName"].ToString();
objList.Description = reader["Description"].ToString();
}
reader.Close();
return objList;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <Roles> GetAllRoles(int portalID, bool isAll, string userName)
{
List <Roles> lstRoles = new List <Roles>();
string StoredProcedureName = "sp_PortalRoleList";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", portalID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@IsAll", isAll));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@Username", userName));
SqlDataReader SQLReader;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
Roles obj = new Roles();
obj.RoleID = new Guid(SQLReader["RoleID"].ToString());
obj.RoleName = SQLReader["RoleName"].ToString();
lstRoles.Add(obj);
}
}
catch (Exception)
{
}
return(lstRoles);
}
/// <summary>
/// Connect to the database and check condition for dashboard access.
/// </summary>
/// <param name="UserName">UserName</param>
/// /// <param name="PortalID">PortalID</param>
/// <returns></returns>
public bool IsDashboardAccesible(string UserName, int PortalID)
{
string sp = "[dbo].[usp_IsDashboardAccesible]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@UserName", UserName));
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
SqlDataReader reader = null;
bool isAccessible = false;
try
{
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
isAccessible = bool.Parse(reader["Accessible"].ToString());
}
reader.Close();
return(isAccessible);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <DashBoardSettingInfo> ListDashBoardSettingForView(int UserModuleID, int PortalID)
{
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@UserModuleID", UserModuleID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
SqlDataReader reader = null;
try
{
SQLHandler Objsql = new SQLHandler();
reader = Objsql.ExecuteAsDataReader("[dbo].[usp_DashBoardSettingsSelect]", ParaMeterCollection);
List <DashBoardSettingInfo> lstSetting = new List <DashBoardSettingInfo>();
while (reader.Read())
{
lstSetting.Add(new DashBoardSettingInfo(reader["START_DATE"].ToString(), reader["END_DATE"].ToString(), reader["SELECT_TYPE"].ToString()));
}
return(lstSetting);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connect to the database and obtain application settings.
/// </summary>
/// <returns>List of SettingInfo class.</returns>
public static List <SettingInfo> GetSettings()
{
string sp = "usp_GetMembershipSettings";
SQLHandler sagesql = new SQLHandler();
List <SettingInfo> lstSetting = new List <SettingInfo>();
SqlDataReader reader = null;
try
{
reader = sagesql.ExecuteAsDataReader(sp);
while (reader.Read())
{
SettingInfo obj = new SettingInfo();
obj.SettingKey = reader["SettingKey"].ToString();
obj.SettingValue = reader["SettingValue"].ToString();
lstSetting.Add(obj);
}
reader.Close();
return(lstSetting);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <MenuInfo> GetSideMenu(int PortalID, string UserName, string PageName, string CultureCode)
{
List <MenuInfo> lstPages = new List <MenuInfo>();
string StoredProcedureName = "[dbo].[usp_SageMenuGetSideMenu]";
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@Username", UserName));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PageName", PageName));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@CultureCode", CultureCode));
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
MenuInfo objMenu = new MenuInfo();
objMenu.PageID = int.Parse(SQLReader["PageID"].ToString());
objMenu.PageOrder = int.Parse(SQLReader["PageOrder"].ToString());
objMenu.PageName = SQLReader["PageName"].ToString();
objMenu.ParentID = int.Parse(SQLReader["ParentID"].ToString());
objMenu.Level = int.Parse(SQLReader["Level"].ToString());
objMenu.TabPath = SQLReader["TabPath"].ToString();
lstPages.Add(objMenu);
}
return(lstPages);
}
/// <summary>
/// Connects to database and returns RobotsInfo list for given PortalID.
/// </summary>
/// <param name="PortalID">PortalID</param>
/// <returns>RobotsInfo List</returns>
public static List<RobotsInfo> GetRobots(int PortalID)
{
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
SqlDataReader reader = null;
try
{
SQLHandler Objsql = new SQLHandler();
reader = Objsql.ExecuteAsDataReader("[dbo].[usp_SEOGetRobots]", ParaMeterCollection);
List<RobotsInfo> lstRobots = new List<RobotsInfo>();
while (reader.Read())
{
lstRobots.Add(new RobotsInfo(reader["PageName"].ToString(), reader["TabPath"].ToString(), reader["SEOName"].ToString(), reader["Description"].ToString()));
}
return lstRobots;
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connects to database and obtains user list for folder for given FolderID.
/// </summary>
/// <param name="FolderID">FolderID</param>
/// <returns>List of FolderPermission class containing UserID and UserName.</returns>
public static List <FolderPermission> GetUserListForFolder(int FolderID)
{
List <FolderPermission> lstFolderPer = new List <FolderPermission>();
string StoredProcedureName = "usp_FileManagerGetUsersWithPermissions";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@FolderID", FolderID));
SqlDataReader SQLReader = null;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
FolderPermission obj = new FolderPermission();
obj.UserID = int.Parse(SQLReader["UserID"].ToString());
obj.UserName = SQLReader["UserName"].ToString() ?? SQLReader["UserName"].ToString();
lstFolderPer.Add(obj);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
return(lstFolderPer);
}
/// <summary>
/// Connects to database and returns message template by message template ID.
/// </summary>
/// <param name="MessageTemplateID">Message Template ID.</param>
/// <param name="PortalID">Portal ID.</param>
/// <returns>MessageManagemenetInfo class onject contain message details</returns>
public MessageManagementInfo GetMessageTemplate(int MessageTemplateID, int PortalID)
{
SqlDataReader reader = null;
try
{
SQLHandler SQLH = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@MessageTemplateID", MessageTemplateID));
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
reader = SQLH.ExecuteAsDataReader("[dbo].[sp_GetMessageTemplate]", ParamCollInput);
MessageManagementInfo objList = new MessageManagementInfo();
while (reader.Read())
{
objList.Subject = reader["Subject"].ToString();
objList.Body = reader["Body"].ToString();
objList.MailFrom = reader["MailFrom"].ToString();
objList.IsActive = bool.Parse(reader["IsActive"].ToString());
objList.MessageTemplateTypeID = int.Parse(reader["MessageTemplateTypeID"].ToString());
}
return(objList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <TemplateInfo> GetTemplateList(int PortalID, string UserName)
{
string sp = "[dbo].[sp_TemplateGetList]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
ParamCollInput.Add(new KeyValuePair <string, object>("@Username", UserName));
List <TemplateInfo> lstTemplate = new List <TemplateInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
TemplateInfo obj = new TemplateInfo();
obj.TemplateID = int.Parse(reader["TemplateID"].ToString());
obj.TemplateTitle = reader["TemplateTitle"].ToString();
obj.PortalID = int.Parse(reader["PortalID"].ToString());
obj.Author = reader["Author"].ToString();
obj.AuthorURL = reader["AuthorURL"].ToString();
obj.Description = reader["Description"].ToString();
lstTemplate.Add(obj);
}
reader.Close();
return(lstTemplate);
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// Connects to database and obtains module permission.
/// </summary>
/// <param name="UserModuleID">UserModuleID</param>
/// <param name="UserName">UserName</param>
/// <returns>List of file manager module permission key.</returns>
public static List <string> GetModulePermission(int UserModuleID, string UserName)
{
List <string> lstPermissions = new List <string>();
string StoredProcedureName = "usp_FileManagerGetModulePermission";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@UserModuleID", UserModuleID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@UserName", UserName));
SqlDataReader SQLReader = null;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
lstPermissions.Add(SQLReader["permissionkey"].ToString());
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
return(lstPermissions);
}
public static int ScheduleDateGetNextMonth(int ScheduleID, int month)
{
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@ScheduleID", ScheduleID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@NextMonth", month));
int nextStartDate = 0;
SqlDataReader reader = null;
try
{
SQLHandler sagesql = new SQLHandler();
reader = sagesql.ExecuteAsDataReader("usp_ScheduleDateGetNextMonth", ParaMeterCollection);
while (reader.Read())
{
nextStartDate = (int)reader["NextMonth"];
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(nextStartDate);
}
/// <summary>
/// Connects to database and returns RobotsInfo list.
/// </summary>
/// <param name="UserAgent">UserAgent(Crawler)</param>
/// <returns>RobotsInfo list</returns>
public static List <RobotsInfo> GenerateRobots(string UserAgent)
{
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@UserAgent", UserAgent));
SqlDataReader reader = null;
try
{
SQLHandler Objsql = new SQLHandler();
reader = Objsql.ExecuteAsDataReader("usp_SEOGenerateRobots", ParaMeterCollection);
List <RobotsInfo> lstRobots = new List <RobotsInfo>();
while (reader.Read())
{
lstRobots.Add(new RobotsInfo(int.Parse(reader["PortalID"].ToString()), reader["UserAgent"].ToString(), reader["PagePath"].ToString()));
}
return(lstRobots);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <RoleInfo> GetPortalRoles(int PortalID, int IsAll, string UserName)
{
string sp = "[dbo].[sp_PortalRoleList]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
ParamCollInput.Add(new KeyValuePair <string, object>("@IsAll", IsAll));
ParamCollInput.Add(new KeyValuePair <string, object>("@Username", UserName));
List <RoleInfo> lstPortalRoles = new List <RoleInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
RoleInfo obj = new RoleInfo();
obj.RoleName = reader["RoleName"].ToString();
obj.RoleID = new Guid(reader["RoleID"].ToString());
lstPortalRoles.Add(obj);
}
reader.Close();
return(lstPortalRoles);
}
catch (Exception ex)
{
throw (ex);
}
}
public static string GetRoleNames(string UserName, int PortalID)
{
string sp = "[dbo].[usp_RoleNamesGetByUserName]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@UserName", UserName));
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);;
string RoleName = "";
while (reader.Read())
{
RoleName = reader["RoleName"].ToString();
}
reader.Close();
return(RoleName);
}
catch (Exception ex)
{
throw (ex);
}
}
public static List <UserInfo> GetPortalUsers(int PortalID)
{
string sp = "[dbo].[usp_PortalUserListGet]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
List <UserInfo> lstUsers = new List <UserInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
UserInfo obj = new UserInfo();
obj.UserID = new Guid(reader["UserID"].ToString());
obj.UserName = reader["UserName"].ToString();
obj.IsApproved = bool.Parse(reader["IsActive"].ToString());
lstUsers.Add(obj);
}
reader.Close();
return(lstUsers);
}
catch (Exception ex)
{
throw (ex);
}
}
public static SageFrameUserCollection GetAllUsers()
{
string sp = "[dbo].[usp_UsersGetAll]";
SQLHandler sagesql = new SQLHandler();
SageFrameUserCollection userColl = new SageFrameUserCollection();
List <UserInfo> lstUsers = new List <UserInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp);
while (reader.Read())
{
UserInfo obj = new UserInfo();
obj.UserID = new Guid(reader["userid"].ToString());
obj.UserName = reader["username"].ToString();
obj.FirstName = reader["firstname"].ToString();
obj.LastName = reader["lastname"].ToString();
obj.Email = reader["email"].ToString();
lstUsers.Add(obj);
}
reader.Close();
userColl.UserList = lstUsers;
return(userColl);
}
catch (Exception ex)
{
throw (ex);
}
}
public static List <LanguageSwitchKeyValue> GetLanguageSwitchSettings(int portalId, int UserModuleID)
{
List <LanguageSwitchKeyValue> lstSettings = new List <LanguageSwitchKeyValue>();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", portalId));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@UserModuleID", UserModuleID));
string StoredProcedureName = "usp_loc_GetLanguageSwitchSettings";
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
lstSettings.Add(new LanguageSwitchKeyValue(SQLReader["SettingKey"].ToString(), SQLReader["SettingValue"].ToString()));
}
SQLReader.Close();
return(lstSettings);
}
public static List <Language> GetPortalLanguages(int portalId)
{
List <Language> lstPortalLanguages = new List <Language>();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", portalId));
string StoredProcedureName = "usp_loc_PortalLanguagesGet";
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
Language obj = new Language(int.Parse(SQLReader["LanguageID"].ToString()), SQLReader["CultureName"].ToString(), SQLReader["CultureCode"].ToString());
obj.LanguageN = SQLReader["CultureName"].ToString();
obj.Country = SQLReader["CultureName"].ToString();
lstPortalLanguages.Add(obj);
}
SQLReader.Close();
return(lstPortalLanguages);
}
public static List<TemplateInfo> GetTemplateList(int PortalID, string UserName)
{
string sp = "[dbo].[sp_TemplateGetList]";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
ParamCollInput.Add(new KeyValuePair<string, object>("@Username", UserName));
List<TemplateInfo> lstTemplate = new List<TemplateInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
TemplateInfo obj = new TemplateInfo();
obj.TemplateID = int.Parse(reader["TemplateID"].ToString());
obj.TemplateTitle = reader["TemplateTitle"].ToString();
obj.PortalID = int.Parse(reader["PortalID"].ToString());
obj.Author = reader["Author"].ToString();
obj.AuthorURL = reader["AuthorURL"].ToString();
obj.Description = reader["Description"].ToString();
lstTemplate.Add(obj);
}
reader.Close();
return lstTemplate;
}
catch (Exception ex)
{
throw (ex);
}
}
public string GetUserAgent(int PortalID, bool IsActive)
{
string sp = "[dbo].[usp_UserAgentGetType]";
SQLHandler sagesql = new SQLHandler();
string content = "";
SqlDataReader reader = null;
try
{
List<KeyValuePair<string, object>> paramColl = new List<KeyValuePair<string, object>>();
paramColl.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
paramColl.Add(new KeyValuePair<string, object>("@IsActive", IsActive));
reader = sagesql.ExecuteAsDataReader(sp, paramColl);
while (reader.Read())
{
content = reader["AgentMode"] as string;
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return content;
}
/// <summary>
/// Connects to database and obtain news content.
/// </summary>
/// <returns>String.</returns>
public static string GetNewsContent()
{
string sp = "[dbo].[usp_NewsRssContentGet]";
SQLHandler sagesql = new SQLHandler();
string content = "";
SqlDataReader reader = null;
try
{
reader = sagesql.ExecuteAsDataReader(sp);
while (reader.Read())
{
content = reader["NewsContent"] as string;
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(content);
}
public static DateTime?GetNextScheduleDate(int ScheduleID, string CurrentStartDate)
{
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@ScheduleID", ScheduleID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@CurrentCalendarDate ", CurrentStartDate));
DateTime? nextStartDate = null;
SqlDataReader reader = null;
try
{
SQLHandler sagesql = new SQLHandler();
reader = sagesql.ExecuteAsDataReader("usp_ScheduleDateGetNextCalendarDate", ParaMeterCollection);
while (reader.Read())
{
nextStartDate = DBNull.Value.Equals(reader["ScheduleDate"]) ? null : (DateTime?)(reader["ScheduleDate"]);
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(nextStartDate);
}
public static List<Roles> GetAllRoles(int portalID,bool isAll,string userName)
{
List<Roles> lstRoles = new List<Roles>();
string StoredProcedureName = "sp_PortalRoleList";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", portalID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsAll", isAll));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", userName));
SqlDataReader SQLReader = null;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
Roles obj = new Roles();
obj.RoleID = new Guid(SQLReader["RoleID"].ToString());
obj.RoleName = SQLReader["RoleName"].ToString();
lstRoles.Add(obj);
}
}
catch(Exception)
{
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
return lstRoles;
}
public static List<MenuInfo> GetFooterMenu(int PortalID, string UserName,string CultureCode)
{
List<MenuInfo> lstPages = new List<MenuInfo>();
string StoredProcedureName = "[dbo].[usp_SageMenuGetFooter]";
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@Username", UserName));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@CultureCode", CultureCode));
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName,ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
lstPages.Add(new MenuInfo(int.Parse(SQLReader["PageID"].ToString()), int.Parse(SQLReader["PageOrder"].ToString()), SQLReader["PageName"].ToString(), int.Parse(SQLReader["ParentID"].ToString()), int.Parse(SQLReader["Level"].ToString()), SQLReader["LevelPageName"].ToString(), SQLReader["SEOName"].ToString(), SQLReader["TabPath"].ToString(), bool.Parse(SQLReader["IsVisible"].ToString()), bool.Parse(SQLReader["ShowInMenu"].ToString())));
}
return lstPages;
}
/// <summary>
/// Connect to the database and obtain roles name.
/// </summary>
/// <param name="UserName">User name.</param>
/// <param name="PortalID">PortalID</param>
/// <returns>Role names with comma separator.</returns>
public static string GetRoleNames(string UserName, int PortalID)
{
string sp = "[dbo].[usp_RoleNamesGetByUserName]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@UserName", UserName));
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
SqlDataReader reader = null;
try
{
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);;
List <string> lstRoles = new List <string>();
while (reader.Read())
{
lstRoles.Add(reader["RoleName"].ToString());
}
reader.Close();
return(String.Join(",", lstRoles.ToArray()));
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connects to database and returns permission for any permission code and permission key.
/// </summary>
/// <param name="PermissionCode">Permission Code.</param>
/// <param name="PermissionKey">Permission key.</param>
/// <returns>Returns Permossion ID.</returns>
public int GetPermissionByCodeAndKey(string PermissionCode, string PermissionKey)
{
string sp = "[dbo].[sp_GetPermissionByCodeAndKey]";
SQLHandler SQLH = new SQLHandler();
SqlDataReader reader = null;
try
{
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@PermissionCode", PermissionCode));
ParamCollInput.Add(new KeyValuePair <string, object>("@PermissionKey", PermissionKey));
reader = SQLH.ExecuteAsDataReader(sp, ParamCollInput);
int PermissionID = 0;
while (reader.Read())
{
PermissionID = int.Parse(reader["PermissionID"].ToString());
}
return(PermissionID);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connect to the database and check condition for suspended IP.
/// </summary>
/// <param name="IpAddress">IpAddress</param>
/// <returns>Returns True for SuspendedIP Address</returns>
public bool IsSuspendedIP(string IpAddress)
{
string sp = "[dbo].[usp_IsSuspendedIP]";
SQLHandler sagesql = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@IpAddress", IpAddress));
SqlDataReader reader = null;
bool isSuspended = true;
try
{
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
isSuspended = bool.Parse(reader["Suspended"].ToString());
}
reader.Close();
return(isSuspended);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connects to database and returns roles details by role name.
/// </summary>
/// <param name="RoleName">Role name.</param>
/// <returns>Role details.</returns>
public RolesManagementInfo GetRoleIDByRoleName(string RoleName)
{
SqlDataReader reader = null;
try
{
SQLHandler SQLH = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@RoleName", RoleName));
reader = SQLH.ExecuteAsDataReader("[dbo].[sp_GetRoleIDByRoleName]", ParamCollInput);
RolesManagementInfo objList = new RolesManagementInfo();
while (reader.Read())
{
objList.ApplicationId = new Guid(reader["ApplicationId"].ToString());
objList.RoleId = new Guid(reader["RoleId"].ToString());
objList.RoleName = reader["RoleName"].ToString();
objList.LoweredRoleName = reader["LoweredRoleName"].ToString();
objList.Description = reader["Description"].ToString();
}
reader.Close();
return(objList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public string GetUserAgent(int PortalID, bool IsActive)
{
string sp = "[dbo].[usp_UserAgentGetType]";
SQLHandler sagesql = new SQLHandler();
string content = "";
SqlDataReader reader = null;
try
{
List <KeyValuePair <string, object> > paramColl = new List <KeyValuePair <string, object> >();
paramColl.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
paramColl.Add(new KeyValuePair <string, object>("@IsActive", IsActive));
reader = sagesql.ExecuteAsDataReader(sp, paramColl);
while (reader.Read())
{
content = reader["AgentMode"] as string;
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(content);
}
/// <summary>
/// Connects to database and obtains all folders.
/// </summary>
/// <returns>List of Folder class containing FolderId,FolderPath and StorageLocation.</returns>
public static List <Folder> GetAllFolders()
{
List <Folder> lstFolders = new List <Folder>();
string StoredProcedureName = "usp_FileManagerGetAllFolders";
SqlDataReader SQLReader = null;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName);
while (SQLReader.Read())
{
Folder obj = new Folder();
obj.FolderId = int.Parse(SQLReader["FolderId"].ToString());
obj.FolderPath = SQLReader["FolderPath"].ToString();
obj.StorageLocation = int.Parse(SQLReader["StorageLocation"].ToString());
lstFolders.Add(obj);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
return(lstFolders);
}
/// <summary>
/// Connects to database and returns activation code
/// </summary>
/// <param name="Username">User's name</param>
/// <param name="PortalID">Portal ID</param>
/// <returns>Activation code</returns>
public MessageManagementInfo GetUserActivationCode(string Username, int PortalID)
{
SqlDataReader reader = null;
try
{
string sp = "[dbo].[sp_GetUserActivationCode]";
SQLHandler SQLH = new SQLHandler();
List <KeyValuePair <string, object> > ParamCollInput = new List <KeyValuePair <string, object> >();
ParamCollInput.Add(new KeyValuePair <string, object>("@UserName", Username));
ParamCollInput.Add(new KeyValuePair <string, object>("@PortalID", PortalID));
reader = SQLH.ExecuteAsDataReader(sp, ParamCollInput);
MessageManagementInfo objInfo = new MessageManagementInfo();
while (reader.Read())
{
objInfo.UserId = new Guid(reader["UserId"].ToString());
}
return(objInfo);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List<SiteMapInfo> GetSiteMap(string prefix, bool IsActive, bool IsDeleted, int PortalID, string Username, bool IsVisible, bool IsRequiredPage)
{
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@prefix", prefix));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsActive", IsActive));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsDeleted", IsDeleted));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", Username));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsVisible", IsVisible));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsRequiredPage", IsRequiredPage));
SqlDataReader reader = null;
try
{
SQLHandler Objsql = new SQLHandler();
reader = Objsql.ExecuteAsDataReader("[dbo].[sp_PageGetByCustomPrefix]", ParaMeterCollection);
List<SiteMapInfo> lstSetting = new List<SiteMapInfo>();
while (reader.Read())
{
SiteMapInfo obj = new SiteMapInfo();
obj.PageID = reader["PageID"].ToString();
obj.PageName = reader["PageName"].ToString();
obj.TabPath = reader["TabPath"].ToString();
obj.SEOName = reader["SEOName"].ToString();
obj.LevelPageName = reader["LevelPageName"].ToString();
obj.Description = reader["Description"].ToString();
if (reader["UpdatedOn"].ToString() == string.Empty)
{
obj.UpdatedOn = DateTime.Parse(reader["AddedOn"].ToString());
}
else
{
obj.UpdatedOn = DateTime.Parse(reader["UpdatedOn"].ToString());
}
obj.AddedOn = DateTime.Parse(reader["AddedOn"].ToString());
lstSetting.Add(obj);
}
return lstSetting;
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List<MenuInfo> GetMenuFront(int PortalID, string UserName, string CultureCode)
{
List<MenuInfo> lstPages = new List<MenuInfo>();
string StoredProcedureName = "[dbo].[usp_SageMenuGetClientView]";
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@prefix", "---"));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsDeleted", 0));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", UserName));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@CultureCode", CultureCode));
SqlDataReader SQLReader = null;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
MenuInfo objMenu = new MenuInfo();
objMenu.PageID = int.Parse(SQLReader["PageID"].ToString());
objMenu.PageOrder = int.Parse(SQLReader["PageOrder"].ToString());
objMenu.ParentID = int.Parse(SQLReader["ParentID"].ToString());
objMenu.Level = int.Parse(SQLReader["Level"].ToString());
objMenu.LevelPageName = SQLReader["LevelPageName"].ToString();
objMenu.SEOName = SQLReader["SEOName"].ToString();
objMenu.TabPath = SQLReader["TabPath"].ToString();
objMenu.IsVisible = bool.Parse(SQLReader["IsVisible"].ToString());
objMenu.ShowInMenu = bool.Parse(SQLReader["ShowInMenu"].ToString());
objMenu.IconFile = SQLReader["IconFile"].ToString();
lstPages.Add(objMenu);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
return lstPages;
}
/// <summary>
/// Connects to database and returns portal details by portal ID.
/// </summary>
/// <param name="PortalID">Portal ID.</param>
/// <param name="UserName"> User's name.</param>
/// <returns>Returns portal </returns>
public static PortalInfo GetPortalByPortalID(int PortalID, string UserName)
{
string sp = "[dbo].[sp_PortalGetByPortalID]";
SqlDataReader reader = null;
try
{
SQLHandler SQLH = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", UserName));
reader = SQLH.ExecuteAsDataReader(sp, ParamCollInput);
PortalInfo objList = new PortalInfo();
while (reader.Read())
{
objList.PortalID = int.Parse(reader["PortalID"].ToString());
objList.Name = reader["Name"].ToString();
objList.SEOName = reader["SEOName"].ToString();
objList.IsParent = bool.Parse(reader["IsParent"].ToString());
objList.ParentPortalName = reader["ParentPortalName"].ToString();
}
return objList;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static string LoadProviderSetting(int providerId, AspxCommonInfo aspxCommonObj)
{
List<KeyValuePair<string, object>> parameter = new List<KeyValuePair<string, object>>();
parameter.Add(new KeyValuePair<string, object>("@ShippingProviderID", providerId));
parameter.Add(new KeyValuePair<string, object>("@StoreID", aspxCommonObj.StoreID));
parameter.Add(new KeyValuePair<string, object>("@PortalID", aspxCommonObj.PortalID));
SQLHandler sqlH = new SQLHandler();
var reader = sqlH.ExecuteAsDataReader("[dbo].[usp_Aspx_GetShippingProviderSetting]", parameter);
var dataQuery = from d in reader.Cast<System.Data.Common.DbDataRecord>()
select new
{
SettingKey = (String)d["SettingKey"],
SettingValue = (String)d["SettingValue"]
};
var data = dataQuery.ToDictionary(z => z.SettingKey, z => z.SettingValue);
string retString = JSONHelper.Serialize<Dictionary<string, string>>(data);
return retString;
}
public static List<MenuInfo> GetAdminMenu()
{
List<MenuInfo> lstPages = new List<MenuInfo>();
string StoredProcedureName = "[dbo].[usp_sagemenugetadminmenu]";
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
lstPages.Add(new MenuInfo(int.Parse(SQLReader["PageID"].ToString()), int.Parse(SQLReader["PageOrder"].ToString()), SQLReader["PageName"].ToString(), int.Parse(SQLReader["ParentID"].ToString()), int.Parse(SQLReader["Level"].ToString()), SQLReader["LevelPageName"].ToString(), SQLReader["SEOName"].ToString(), SQLReader["TabPath"].ToString(), bool.Parse(SQLReader["IsVisible"].ToString()), bool.Parse(SQLReader["ShowInMenu"].ToString())));
}
return lstPages;
}
public static string GetNewsContent()
{
string sp = "[dbo].[usp_NewsRssContentGet]";
SQLHandler sagesql = new SQLHandler();
string content = "";
SqlDataReader reader = null;
try
{
reader = sagesql.ExecuteAsDataReader(sp);
while (reader.Read())
{
content = reader["NewsContent"] as string;
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return content;
}
public static List<Folder> GetFolders()
{
List<Folder> lstFolders = new List<Folder>();
string StoredProcedureName = "usp_FileManagerGetFolders";
SqlDataReader SQLReader;
SQLHandler sagesql = new SQLHandler();
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName);
while (SQLReader.Read())
{
Folder obj = new Folder();
obj.FolderId = int.Parse(SQLReader["FolderId"].ToString());
obj.FolderPath = SQLReader["FolderPath"].ToString();
obj.StorageLocation = int.Parse(SQLReader["StorageLocation"].ToString());
lstFolders.Add(obj);
}
SQLReader.Dispose();
}
catch (Exception e)
{
throw e;
}
return lstFolders;
}
public MessageManagementInfo GetUserActivationCode(string Username, int PortalID)
{
SqlDataReader reader = null;
try
{
string sp = "[dbo].[sp_GetUserActivationCode]";
SQLHandler SQLH = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", Username));
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
reader = SQLH.ExecuteAsDataReader(sp, ParamCollInput);
MessageManagementInfo objInfo = new MessageManagementInfo();
while (reader.Read())
{
objInfo.UserId = new Guid(reader["UserId"].ToString());
}
return objInfo;
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List<FolderPermission> GetUserListForFolder(int FolderID)
{
List<FolderPermission> lstFolderPer = new List<FolderPermission>();
string StoredProcedureName = "usp_FileManagerGetUsersWithPermissions";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@FolderID", FolderID));
SqlDataReader SQLReader;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
FolderPermission obj = new FolderPermission();
obj.UserID = int.Parse(SQLReader["UserID"].ToString());
obj.UserName = SQLReader["UserName"].ToString() ?? SQLReader["UserName"].ToString();
lstFolderPer.Add(obj);
}
SQLReader.Dispose();
}
catch (Exception e)
{
throw e;
}
return lstFolderPer;
}
public static List<string> GetModulePermission(int UserModuleID, string UserName)
{
List<string> lstPermissions = new List<string>();
string StoredProcedureName = "usp_FileManagerGetModulePermission";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserModuleID", UserModuleID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@Username", UserName));
SqlDataReader SQLReader;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
lstPermissions.Add(SQLReader["permissionkey"].ToString());
}
SQLReader.Dispose();
}
catch (Exception e)
{
throw e;
}
return lstPermissions;
}
public static DateTime? GetNextScheduleDate(int ScheduleID, string CurrentStartDate)
{
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@ScheduleID", ScheduleID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@CurrentCalendarDate ", CurrentStartDate));
DateTime? nextStartDate = null;
SqlDataReader reader = null;
try
{
SQLHandler sagesql = new SQLHandler();
reader = sagesql.ExecuteAsDataReader("usp_ScheduleDateGetNextCalendarDate", ParaMeterCollection);
while (reader.Read())
{
nextStartDate = DBNull.Value.Equals(reader["ScheduleDate"]) ? null : (DateTime?)(reader["ScheduleDate"]);
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return nextStartDate;
}
public static List<ATTFile> SearchFiles(string SearchQuery)
{
List<ATTFile> lstFiles = new List<ATTFile>();
string StoredProcedureName = "usp_FileManagerSearchFiles";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@SearchQuery", SearchQuery));
SqlDataReader SQLReader;
try
{
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
ATTFile obj = new ATTFile();
obj.FileId = int.Parse(SQLReader["FileId"].ToString());
obj.FileName = SQLReader["FileName"].ToString();
obj.Folder = SQLReader["Folder"].ToString();
obj.Extension = SQLReader["Extension"].ToString();
obj.Size = int.Parse(SQLReader["Size"].ToString());
obj.AddedOn = SQLReader["AddedOn"].ToString();
obj.Content = SQLReader["Content"] == DBNull.Value ? null : (byte[])SQLReader["Content"];
obj.StorageLocation = int.Parse(SQLReader["StorageLocation"].ToString());
lstFiles.Add(obj);
}
SQLReader.Dispose();
}
catch (Exception e)
{
throw e;
}
return lstFiles;
}
public static string GetRoleNames(string UserName, int PortalID)
{
string sp = "[dbo].[usp_RoleNamesGetByUserName]";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", UserName));
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput); ;
string RoleName = "";
while (reader.Read())
{
RoleName = reader["RoleName"].ToString();
}
reader.Close();
return RoleName;
}
catch (Exception ex)
{
throw (ex);
}
}
public static List<SettingInfo> GetSettings()
{
string sp = "usp_GetMembershipSettings";
SQLHandler sagesql = new SQLHandler();
List<SettingInfo> lstSetting = new List<SettingInfo>();
try
{
SqlDataReader reader = sagesql.ExecuteAsDataReader(sp);
while (reader.Read())
{
SettingInfo obj = new SettingInfo();
obj.SettingKey = reader["SettingKey"].ToString();
obj.SettingValue = reader["SettingValue"].ToString();
lstSetting.Add(obj);
}
reader.Close();
return lstSetting;
}
catch (Exception ex)
{
throw (ex);
}
}
public static UserInfo GetUserDetails(string UserName, int PortalID)
{
string sp = "[dbo].[usp_GetUserDetails]";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", UserName));
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
List<UserInfo> lstUser = new List<UserInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
UserInfo obj = new UserInfo();
obj.UserID = new Guid(reader["userid"].ToString());
obj.UserName = reader["Username"].ToString();
obj.Password = reader["Password"].ToString();
obj.PasswordSalt = reader["PasswordSalt"].ToString();
obj.PasswordFormat = int.Parse(reader["PasswordFormat"].ToString());
obj.FirstName = reader["FirstName"].ToString();
obj.LastName = reader["LastName"].ToString();
obj.Email = reader["Email"].ToString();
obj.LastPasswordChangeDate = DateTime.Parse(reader["LastPasswordChangedDate"].ToString());
obj.LastActivityDate = DateTime.Parse(reader["LastActivityDate"].ToString());
obj.LastLoginDate = DateTime.Parse(reader["LastLoginDate"].ToString());
obj.IsApproved = bool.Parse(reader["IsApproved"].ToString());
obj.UserExists = true;
lstUser.Add(obj);
}
reader.Close();
UserInfo userObj = lstUser.Count > 0 ? lstUser[0] : new UserInfo(false);
return userObj;
}
catch (Exception ex)
{
throw (ex);
}
}
public static List<RoleInfo> GetPortalRoles(int PortalID, int IsAll, string UserName)
{
string sp = "[dbo].[sp_PortalRoleList]";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
ParamCollInput.Add(new KeyValuePair<string, object>("@IsAll", IsAll));
ParamCollInput.Add(new KeyValuePair<string, object>("@Username", UserName));
List<RoleInfo> lstPortalRoles = new List<RoleInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
RoleInfo obj = new RoleInfo();
obj.RoleName = reader["RoleName"].ToString();
obj.RoleID = new Guid(reader["RoleID"].ToString());
lstPortalRoles.Add(obj);
}
reader.Close();
return lstPortalRoles;
}
catch (Exception ex)
{
throw (ex);
}
}
public static SageFrameUserCollection GetAllUsers()
{
string sp = "[dbo].[usp_UsersGetAll]";
SQLHandler sagesql = new SQLHandler();
SageFrameUserCollection userColl = new SageFrameUserCollection();
List<UserInfo> lstUsers = new List<UserInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp);
while (reader.Read())
{
UserInfo obj = new UserInfo();
obj.UserID = new Guid(reader["userid"].ToString());
obj.UserName = reader["username"].ToString();
obj.FirstName = reader["firstname"].ToString();
obj.LastName = reader["lastname"].ToString();
obj.Email = reader["email"].ToString();
lstUsers.Add(obj);
}
reader.Close();
userColl.UserList = lstUsers;
return userColl;
}
catch (Exception ex)
{
throw (ex);
}
}
public static List<UserInfo> GetPortalUsers(int PortalID)
{
string sp = "[dbo].[usp_PortalUserListGet]";
SQLHandler sagesql = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
List<UserInfo> lstUsers = new List<UserInfo>();
try
{
SqlDataReader reader;
reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
while (reader.Read())
{
UserInfo obj = new UserInfo();
obj.UserID = new Guid(reader["UserID"].ToString());
obj.UserName = reader["UserName"].ToString();
obj.IsApproved = bool.Parse(reader["IsActive"].ToString());
lstUsers.Add(obj);
}
reader.Close();
return lstUsers;
}
catch (Exception ex)
{
throw (ex);
}
}
public List<PagePermissionInfo> GetPagePermissionDetails(PageEntity obj)
{
SqlDataReader reader = null;
try
{
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>
{
new KeyValuePair<string, object>("@PageID",
obj.PageID),
new KeyValuePair<string, object>(
"@portalID", obj.PortalID)
};
SQLHandler sqlH = new SQLHandler();
List<PagePermissionInfo> lst = new List<PagePermissionInfo>();
reader = sqlH.ExecuteAsDataReader("sp_GetPagePermissionByPageID", ParaMeterCollection);
while (reader.Read())
{
PagePermissionInfo objPP = new PagePermissionInfo
{
PageID = int.Parse(reader["PageID"].ToString()),
PermissionID =
int.Parse(reader["PermissionID"].ToString()),
RoleID =
reader["RoleID"] == DBNull.Value
? ""
: reader["RoleID"].ToString(),
Username =
reader["Username"] == DBNull.Value
? ""
: reader["Username"].ToString(),
IsActive =
bool.Parse(reader["IsActive"].ToString()),
AllowAccess =
bool.Parse(reader["AllowAccess"].ToString()),
AddedBy = reader["AddedBy"].ToString()
};
lst.Add(objPP);
}
return lst;
}
catch (Exception e)
{
throw e;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Connects to database and returns message template by message template type ID.
/// </summary>
/// <param name="MessageTemplateTypeID">Message template type ID.</param>
/// <param name="PortalID">Portal ID.</param>
/// <returns>User's password details.</returns>
public static ForgotPasswordInfo GetMessageTemplateByMessageTemplateTypeID(int MessageTemplateTypeID, int PortalID)
{
SqlDataReader reader = null;
try
{
SQLHandler SQLH = new SQLHandler();
List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
ParamCollInput.Add(new KeyValuePair<string, object>("@MessageTemplateTypeID", MessageTemplateTypeID));
ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
reader = SQLH.ExecuteAsDataReader("[dbo].[sp_MessageTemplateByMessageTemplateTypeID]", ParamCollInput);
ForgotPasswordInfo objList = new ForgotPasswordInfo();
while (reader.Read())
{
objList.MessageTemplateID = int.Parse(reader["MessageTemplateID"].ToString());
objList.MessageTemplateTypeID = int.Parse(reader["MessageTemplateTypeID"].ToString());
objList.Subject = reader["Subject"].ToString();
objList.Body = reader["Body"].ToString();
objList.MailFrom = reader["MailFrom"].ToString();
}
return objList;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List<int> GetListPortalIDs()
{
try
{
List<int> objHDSetting = new List<int>();
List<KeyValuePair<string, object>> parameterCollection = new List<KeyValuePair<string, object>>();
SQLHandler sqLH = new SQLHandler();
SqlDataReader result = sqLH.ExecuteAsDataReader("[dbo].[usp_Aspx_CatalogGetPortalIDs]", parameterCollection);
try
{
while (result.Read())
{
objHDSetting.Add(Convert.ToInt32(result["PortalID"]));
}
}
catch (Exception e)
{
throw e;
}
return objHDSetting;
}
catch (Exception e)
{
throw e;
}
}
public List<SageBannerInfo> LoadBannerOnDropDown(int UserModuleID, int PortalID,string CultureCode)
{
SqlDataReader Reader = null;
try
{
List<KeyValuePair<string, object>> Parameter = new List<KeyValuePair<string, object>>();
Parameter.Add(new KeyValuePair<string, object>("@UserModuleID", UserModuleID));
Parameter.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
Parameter.Add(new KeyValuePair<string, object>("@CultureCode", CultureCode));
SQLHandler sqlH = new SQLHandler();
Reader = sqlH.ExecuteAsDataReader("usp_SageBannerGetAllBanner", Parameter);
List<SageBannerInfo> BannerListForDropDown = new List<SageBannerInfo>();
while (Reader.Read())
{
SageBannerInfo obj = new SageBannerInfo();
obj.BannerID = Convert.ToInt32(Reader["BannerID"]);
obj.BannerName = Convert.ToString(Reader["BannerName"]);
BannerListForDropDown.Add(obj);
}
return BannerListForDropDown;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Reader != null)
{
Reader.Close();
}
}
}
public static int ScheduleDateGetNextMonth(int ScheduleID, int month)
{
List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
ParaMeterCollection.Add(new KeyValuePair<string, object>("@ScheduleID", ScheduleID));
ParaMeterCollection.Add(new KeyValuePair<string, object>("@NextMonth", month));
int nextStartDate = 0;
SqlDataReader reader = null;
try
{
SQLHandler sagesql = new SQLHandler();
reader = sagesql.ExecuteAsDataReader("usp_ScheduleDateGetNextMonth", ParaMeterCollection);
while (reader.Read())
{
nextStartDate = (int)reader["NextMonth"];
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return nextStartDate;
}