public static IDataReader GetMostPopular(int siteId, int numberToGet)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("m.moduleid, ");
sqlCommand.Append("m.siteid, ");
sqlCommand.Append("m.moduledefid, ");
sqlCommand.Append("m.moduletitle, ");
sqlCommand.Append("m.allowmultipleinstancesonmypage, ");
sqlCommand.Append("m.countofuseonmypage , ");
sqlCommand.Append("m.icon As moduleicon, ");
sqlCommand.Append("md.icon As featureicon, ");
sqlCommand.Append("md.featurename, ");
sqlCommand.Append("md.resourcefile, ");
sqlCommand.Append("false As isassembly, ");
sqlCommand.Append("'' As webpartid ");
sqlCommand.Append("FROM mp_modules m ");
sqlCommand.Append("JOIN mp_moduledefinitions md ");
sqlCommand.Append("ON m.moduledefid = md.moduledefid ");
sqlCommand.Append("WHERE m.siteid = " + siteId.ToString(CultureInfo.InvariantCulture)
+ " AND m.availableformypage = true ");
sqlCommand.Append(" UNION ");
sqlCommand.Append("SELECT ");
sqlCommand.Append("-1 As moduleid, ");
sqlCommand.Append("w.siteid, ");
sqlCommand.Append("0 As moduledefid, ");
sqlCommand.Append("w.title As moduletitle, ");
sqlCommand.Append("w.allowmultipleinstancesonmypage, ");
sqlCommand.Append("w.countofuseonmypage , ");
sqlCommand.Append("w.imageurl As moduleicon, ");
sqlCommand.Append("w.imageUrl As featureicon, ");
sqlCommand.Append("w.description As featurename, ");
sqlCommand.Append("'Resource' As resourcefile, ");
sqlCommand.Append("true As isassembly, ");
sqlCommand.Append("w.webpartid ");
sqlCommand.Append("FROM mp_webparts w ");
sqlCommand.Append("WHERE w.siteid = " + siteId.ToString(CultureInfo.InvariantCulture)
+ " AND w.availableformypage = true ");
sqlCommand.Append("ORDER BY countofuseonmypage DESC, moduletitle ");
sqlCommand.Append("LIMIT " + numberToGet.ToString(CultureInfo.InvariantCulture) + "; ");
//NpgsqlParameter[] arParams = new NpgsqlParameter[1];
//arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
//arParams[0].Direction = ParameterDirection.Input;
//arParams[0].Value = siteID;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString()));
}
public static IDataReader GetWorkInProgress(Guid moduleGuid, string status)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT cw.*, ");
sqlCommand.Append("m.ModuleID, ");
sqlCommand.Append("m.ModuleTitle, ");
sqlCommand.Append("createdBy.Name as CreatedByUserName, ");
sqlCommand.Append("createdBy.LoginName as CreatedByUserLogin, ");
sqlCommand.Append("createdBy.Email as CreatedByUserEmail, ");
sqlCommand.Append("createdBy.FirstName as CreatedByFirstName, ");
sqlCommand.Append("createdBy.LastName as CreatedByLastName, ");
sqlCommand.Append("createdBy.UserID as CreatedByUserID, ");
sqlCommand.Append("createdBy.AvatarUrl as CreatedByAvatar, ");
sqlCommand.Append("createdBy.AuthorBio as CreatedByAuthorBio, ");
sqlCommand.Append("modifiedBy.Name as ModifiedByUserName, ");
sqlCommand.Append("modifiedBy.FirstName as ModifiedByFirstName, ");
sqlCommand.Append("modifiedBy.LastName as ModifiedByLastName, ");
sqlCommand.Append("modifiedBy.LoginName as ModifiedByUserLogin, ");
sqlCommand.Append("modifiedBy.Email as ModifiedByUserEmail, ");
sqlCommand.Append("cwah.Notes as Notes, ");
sqlCommand.Append("cwah.CreatedDateUtc as RecentActionOn, ");
sqlCommand.Append("recentActionBy.Name as RecentActionByUserName, ");
sqlCommand.Append("recentActionBy.LoginName as RecentActionByUserLogin, ");
sqlCommand.Append("recentActionBy.Email as RecentActionByUserEmail ");
sqlCommand.Append("FROM mp_ContentWorkflow cw ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("cw.ModuleGuid = m.Guid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Users createdBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("createdBy.UserGuid = cw.UserGuid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Users modifiedBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("modifiedBy.UserGuid = cw.LastModUserGuid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_ContentWorkflowAuditHistory cwah ");
sqlCommand.Append("ON ");
sqlCommand.Append("cwah.ContentWorkflowGuid = cw.Guid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cwah.Active = 1 ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Users recentActionBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("recentActionBy.UserGuid = cwah.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cw.ModuleGuid = ?ModuleGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cw.Status = ?Status ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("CreatedDateUtc DESC ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new MySqlParameter("?Status", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_LetterInfo table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid siteGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_letterinfo ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append("ORDER BY sortrank, title ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
//totalPages = 1;
//int totalRows
// = GetCount(siteGuid);
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
//NpgsqlParameter[] arParams = new NpgsqlParameter[3];
//arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
//arParams[0].Direction = ParameterDirection.Input;
//arParams[0].Value = siteGuid.ToString();
//arParams[1] = new NpgsqlParameter("pagenumber", NpgsqlTypes.NpgsqlDbType.Integer);
//arParams[1].Direction = ParameterDirection.Input;
//arParams[1].Value = pageNumber;
//arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
//arParams[2].Direction = ParameterDirection.Input;
//arParams[2].Value = pageSize;
//return NpgsqlHelper.ExecuteReader(
// ConnectionString.GetReadConnectionString(),
// CommandType.StoredProcedure,
// "mp_letterinfo_selectpage(:siteguid,:pagenumber,:pagesize)",
// arParams);
}
public static DataTable SelectPage(
int siteId,
int pageNumber,
int pageSize,
bool sortByClassName,
bool sortByAssemblyName)
{
int totalRows = Count(siteId);
int totalPages = totalRows / pageSize;
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder = 0;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[5];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("pagenumber", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageNumber;
arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new NpgsqlParameter("sortbyclassname", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = sortByClassName;
arParams[4] = new NpgsqlParameter("sortbyassemblyname", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = sortByAssemblyName;
DataTable dt = new DataTable();
dt.Columns.Add("WebPartID", typeof(String));
dt.Columns.Add("Title", typeof(String));
dt.Columns.Add("Description", typeof(String));
dt.Columns.Add("ImageUrl", typeof(String));
dt.Columns.Add("ClassName", typeof(String));
dt.Columns.Add("AssemblyName", typeof(String));
dt.Columns.Add("AvailableForMyPage", typeof(bool));
dt.Columns.Add("AllowMultipleInstancesOnMyPage", typeof(bool));
dt.Columns.Add("AvailableForContentSystem", typeof(bool));
dt.Columns.Add("TotalPages", typeof(int));
using (IDataReader reader = NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_webparts_selectpage(:siteid,:pagenumber,:pagesize,:sortbyclassname,:sortbyassemblyname)",
arParams))
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["WebPartID"] = reader["WebPartID"].ToString();
row["Title"] = reader["Title"];
row["Description"] = reader["Description"];
row["ImageUrl"] = reader["ImageUrl"];
row["ClassName"] = reader["ClassName"];
row["AssemblyName"] = reader["AssemblyName"];
row["AvailableForMyPage"] = reader["AvailableForMyPage"];
row["AllowMultipleInstancesOnMyPage"] = reader["AllowMultipleInstancesOnMyPage"];
row["AvailableForContentSystem"] = reader["AvailableForContentSystem"];
row["TotalPages"] = totalPages;
dt.Rows.Add(row);
}
}
return(dt);
}
/// <summary>
/// Gets a page of data from the mp_ContentStyle table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid siteGuid,
string skinName,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteGuid, skinName);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ContentStyle ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("SkinName = ?SkinName ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("Name ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?SkinName", MySqlDbType.VarChar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = skinName;
arParams[2] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
Guid letterInfoGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountByLetter(letterInfoGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("ls.Guid, ");
sqlCommand.Append("ls.SiteGuid, ");
sqlCommand.Append("ls.LetterInfoGuid, ");
sqlCommand.Append("ls.UserGuid, ");
sqlCommand.Append("ls.IsVerified, ");
sqlCommand.Append("ls.VerifyGuid, ");
sqlCommand.Append("ls.BeginUtc, ");
sqlCommand.Append("ls.UseHtml, ");
sqlCommand.Append("ls.IpAddress, ");
sqlCommand.Append("COALESCE(u.Email, ls.Email) As Email, ");
sqlCommand.Append("COALESCE(u.Name, ls.Email) AS Name, ");
sqlCommand.Append("u.Email AS UserEmail, ");
sqlCommand.Append("u.FirstName, ");
sqlCommand.Append("u.LastName ");
sqlCommand.Append("FROM mp_LetterSubscribe ls ");
sqlCommand.Append("LEFT OUTER JOIN mp_Users u ");
sqlCommand.Append("ON u.UserGuid = ls.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ls.LetterInfoGuid = ?LetterInfoGuid ");
sqlCommand.Append(" ORDER BY ls.BeginUtc DESC ");
sqlCommand.Append("LIMIT " + pageLowerBound.ToString()
+ ", ?PageSize ; ");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?LetterInfoGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPageByFeature(
Guid siteGuid,
Guid featureGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountByFeature(siteGuid, featureGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new NpgsqlParameter("featureguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = featureGuid.ToString();
arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_emailtemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append("AND featureguid = :featureguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("name ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static DataTable SelectGlobalPage(
int siteId,
int moduleDefId,
int pageId,
int pageNumber,
int pageSize,
out int totalPages)
{
totalPages = 1;
int totalRows = GetGlobalCount(siteId, moduleDefId, pageId);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_Modules_SelectGlobalPage", 5);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@ModuleDefID", SqlDbType.Int, ParameterDirection.Input, moduleDefId);
sph.DefineSqlParameter("@PageID", SqlDbType.Int, ParameterDirection.Input, pageId);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
DataTable dt = new DataTable();
dt.Columns.Add("ModuleID", typeof(int));
dt.Columns.Add("ModuleTitle", typeof(String));
dt.Columns.Add("FeatureName", typeof(String));
dt.Columns.Add("ResourceFile", typeof(String));
dt.Columns.Add("ControlSrc", typeof(String));
dt.Columns.Add("AuthorizedEditRoles", typeof(String));
dt.Columns.Add("CreatedBy", typeof(String));
dt.Columns.Add("CreatedDate", typeof(DateTime));
dt.Columns.Add("UseCount", typeof(int));
using (IDataReader reader = sph.ExecuteReader())
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["ModuleID"] = reader["ModuleID"];
row["ModuleTitle"] = reader["ModuleTitle"];
row["FeatureName"] = reader["FeatureName"];
row["ResourceFile"] = reader["ResourceFile"];
row["ControlSrc"] = reader["ControlSrc"];
row["AuthorizedEditRoles"] = reader["AuthorizedEditRoles"];
row["CreatedBy"] = reader["CreatedBy"];
row["CreatedDate"] = reader["CreatedDate"];
row["UseCount"] = reader["UseCount"];
dt.Rows.Add(row);
}
}
return(dt);
}
//public static void SyncDefinitions()
//{
// StringBuilder sqlCommand = new StringBuilder();
// sqlCommand.Append("UPDATE mp_ModuleSettings ");
// sqlCommand.Append("SET ControlSrc = (SELECT mds.ControlSrc ");
// sqlCommand.Append("FROM mp_ModuleDefinitionSettings mds ");
// sqlCommand.Append("WHERE mds.ModuleDefId IN (SELECT ModuleDefId ");
// sqlCommand.Append("FROM mp_Modules m ");
// sqlCommand.Append("WHERE m.ModuleID = mp_ModuleSettings.ModuleID) ");
// sqlCommand.Append("AND mds.SettingName = mp_ModuleSettings.SettingName LIMIT 1 ) ");
// //sqlCommand.Append(" ");
// sqlCommand.Append("; ");
// MySqlHelper.ExecuteNonQuery(
// ConnectionString.GetWriteConnectionString(),
// sqlCommand.ToString(),
// null);
// sqlCommand = new StringBuilder();
// sqlCommand.Append("UPDATE mp_ModuleSettings ");
// sqlCommand.Append("SET ControlType = (SELECT mds.ControlType ");
// sqlCommand.Append("FROM mp_ModuleDefinitionSettings mds ");
// sqlCommand.Append("WHERE mds.ModuleDefId IN (SELECT ModuleDefId ");
// sqlCommand.Append("FROM mp_Modules m ");
// sqlCommand.Append("WHERE m.ModuleID = mp_ModuleSettings.ModuleID) ");
// sqlCommand.Append("AND mds.SettingName = mp_ModuleSettings.SettingName LIMIT 1 ) ");
// sqlCommand.Append("; ");
// MySqlHelper.ExecuteNonQuery(
// ConnectionString.GetWriteConnectionString(),
// sqlCommand.ToString(),
// null);
// sqlCommand = new StringBuilder();
// sqlCommand.Append("UPDATE mp_ModuleSettings ");
// sqlCommand.Append("SET SortOrder = COALESCE((SELECT mds.SortOrder ");
// sqlCommand.Append("FROM mp_ModuleDefinitionSettings mds ");
// sqlCommand.Append("WHERE mds.ModuleDefId IN (SELECT ModuleDefId ");
// sqlCommand.Append("FROM mp_Modules m ");
// sqlCommand.Append("WHERE m.ModuleID = mp_ModuleSettings.ModuleID) ");
// sqlCommand.Append("AND mds.SettingName = mp_ModuleSettings.SettingName LIMIT 1 ), 100); ");
// sqlCommand.Append(" ");
// MySqlHelper.ExecuteNonQuery(
// ConnectionString.GetWriteConnectionString(),
// sqlCommand.ToString(),
// null);
// sqlCommand = new StringBuilder();
// sqlCommand.Append("UPDATE mp_ModuleSettings ");
// sqlCommand.Append("SET HelpKey = (SELECT mds.HelpKey ");
// sqlCommand.Append("FROM mp_ModuleDefinitionSettings mds ");
// sqlCommand.Append("WHERE mds.ModuleDefId IN (SELECT ModuleDefId ");
// sqlCommand.Append("FROM mp_Modules m ");
// sqlCommand.Append("WHERE m.ModuleID = mp_ModuleSettings.ModuleID) ");
// sqlCommand.Append("AND mds.SettingName = mp_ModuleSettings.SettingName LIMIT 1 ); ");
// sqlCommand.Append(" ");
// MySqlHelper.ExecuteNonQuery(
// ConnectionString.GetWriteConnectionString(),
// sqlCommand.ToString(),
// null);
// sqlCommand = new StringBuilder();
// sqlCommand.Append("UPDATE mp_ModuleSettings ");
// sqlCommand.Append("SET RegexValidationExpression = (SELECT mds.RegexValidationExpression ");
// sqlCommand.Append("FROM mp_ModuleDefinitionSettings mds ");
// sqlCommand.Append("WHERE mds.ModuleDefId IN (SELECT ModuleDefId ");
// sqlCommand.Append("FROM mp_Modules m ");
// sqlCommand.Append("WHERE m.ModuleID = mp_ModuleSettings.ModuleID) ");
// sqlCommand.Append("AND mds.SettingName = mp_ModuleSettings.SettingName LIMIT 1 ); ");
// sqlCommand.Append(" ");
// MySqlHelper.ExecuteNonQuery(
// ConnectionString.GetWriteConnectionString(),
// sqlCommand.ToString(),
// null);
//}
public static bool UpdateModuleDefinitionSetting(
Guid featureGuid,
int moduleDefId,
string resourceFile,
string groupName,
string settingName,
string settingValue,
string controlType,
string regexValidationExpression,
string controlSrc,
string helpKey,
int sortOrder,
string attributes,
string options)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT count(*) ");
sqlCommand.Append("FROM mp_ModuleDefinitionSettings ");
sqlCommand.Append("WHERE (ModuleDefID = ?ModuleDefID OR FeatureGuid = ?FeatureGuid) ");
sqlCommand.Append("AND SettingName = ?SettingName ;");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?ModuleDefID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new MySqlParameter("?SettingName", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new MySqlParameter("?FeatureGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = featureGuid;
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
sqlCommand = new StringBuilder();
int rowsAffected = 0;
if (count > 0)
{
sqlCommand.Append("UPDATE mp_ModuleDefinitionSettings ");
sqlCommand.Append("SET SettingValue = ?SettingValue, ");
sqlCommand.Append("FeatureGuid = ?FeatureGuid, ");
sqlCommand.Append("ResourceFile = ?ResourceFile, ");
sqlCommand.Append("ControlType = ?ControlType, ");
sqlCommand.Append("ControlSrc = ?ControlSrc, ");
sqlCommand.Append("HelpKey = ?HelpKey, ");
sqlCommand.Append("SortOrder = ?SortOrder, ");
sqlCommand.Append("GroupName = ?GroupName, ");
sqlCommand.Append("RegexValidationExpression = ?RegexValidationExpression, ");
sqlCommand.Append("Attributes = ?Attributes, ");
sqlCommand.Append("Options = ?Options ");
sqlCommand.Append("WHERE (ModuleDefID = ?ModuleDefID OR FeatureGuid = ?FeatureGuid) ");
sqlCommand.Append("AND SettingName = ?SettingName ; ");
arParams = new MySqlParameter[13];
arParams[0] = new MySqlParameter("?ModuleDefID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new MySqlParameter("?SettingName", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new MySqlParameter("?SettingValue", MySqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new MySqlParameter("?ControlType", MySqlDbType.VarChar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new MySqlParameter("?RegexValidationExpression", MySqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new MySqlParameter("?FeatureGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = featureGuid;
arParams[6] = new MySqlParameter("?ResourceFile", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new MySqlParameter("?ControlSrc", MySqlDbType.VarChar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new MySqlParameter("?HelpKey", MySqlDbType.VarChar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new MySqlParameter("?SortOrder", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new MySqlParameter("?GroupName", MySqlDbType.VarChar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = groupName;
arParams[11] = new MySqlParameter("?Attributes", MySqlDbType.Text);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = attributes;
arParams[12] = new MySqlParameter("?Options", MySqlDbType.Text);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = options;
rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
else
{
sqlCommand.Append("INSERT INTO mp_ModuleDefinitionSettings ");
sqlCommand.Append("( ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleDefID, ");
sqlCommand.Append("ResourceFile, ");
sqlCommand.Append("SettingName, ");
sqlCommand.Append("SettingValue, ");
sqlCommand.Append("ControlType, ");
sqlCommand.Append("ControlSrc, ");
sqlCommand.Append("HelpKey, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("GroupName, ");
sqlCommand.Append("RegexValidationExpression, ");
sqlCommand.Append("Attributes, ");
sqlCommand.Append("Options ");
sqlCommand.Append(")");
sqlCommand.Append("VALUES ( ");
sqlCommand.Append(" ?FeatureGuid , ");
sqlCommand.Append(" ?ModuleDefID , ");
sqlCommand.Append(" ?ResourceFile , ");
sqlCommand.Append(" ?SettingName , ");
sqlCommand.Append(" ?SettingValue ,");
sqlCommand.Append(" ?ControlType ,");
sqlCommand.Append(" ?ControlSrc, ");
sqlCommand.Append(" ?HelpKey, ");
sqlCommand.Append(" ?SortOrder, ");
sqlCommand.Append(" ?GroupName, ");
sqlCommand.Append(" ?RegexValidationExpression, ");
sqlCommand.Append(" ?Attributes, ");
sqlCommand.Append(" ?Options ");
sqlCommand.Append(");");
arParams = new MySqlParameter[13];
arParams[0] = new MySqlParameter("?ModuleDefID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new MySqlParameter("?SettingName", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new MySqlParameter("?SettingValue", MySqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new MySqlParameter("?ControlType", MySqlDbType.VarChar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new MySqlParameter("?RegexValidationExpression", MySqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new MySqlParameter("?FeatureGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = featureGuid;
arParams[6] = new MySqlParameter("?ResourceFile", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new MySqlParameter("?ControlSrc", MySqlDbType.VarChar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new MySqlParameter("?HelpKey", MySqlDbType.VarChar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new MySqlParameter("?SortOrder", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new MySqlParameter("?GroupName", MySqlDbType.VarChar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = groupName;
arParams[11] = new MySqlParameter("?Attributes", MySqlDbType.Text);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = attributes;
arParams[12] = new MySqlParameter("?Options", MySqlDbType.Text);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = options;
rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
}
public static IDataReader GetSiteList()
{
SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_Sites_SelectAll", 0);
return(sph.ExecuteReader());
}
public static IDataReader GetSharedFilesByPage(int siteId, int pageId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = siteId
};
arParams[1] = new NpgsqlParameter("pageid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = pageId
};
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("ce.itemid, ");
sqlCommand.Append("ce.moduleid, ");
sqlCommand.Append("ce.uploaduserid, ");
sqlCommand.Append("ce.friendlyname, ");
sqlCommand.Append("ce.originalfilename, ");
sqlCommand.Append("ce.serverfilename, ");
sqlCommand.Append("ce.sizeinkb, ");
sqlCommand.Append("ce.uploaddate, ");
sqlCommand.Append("ce.folderid, ");
sqlCommand.Append("ce.itemguid, ");
sqlCommand.Append("ce.folderguid, ");
sqlCommand.Append("ce.userguid, ");
sqlCommand.Append("ce.moduleguid, ");
sqlCommand.Append("ce.description, ");
sqlCommand.Append("ce.downloadcount, ");
sqlCommand.Append("ce.viewroles, ");
sqlCommand.Append("m.moduletitle, ");
sqlCommand.Append("m.viewroles, ");
sqlCommand.Append("md.featurename ");
sqlCommand.Append("FROM mp_sharedFiles ce ");
sqlCommand.Append("JOIN mp_modules m ");
sqlCommand.Append("ON ce.moduleid = m.moduleid ");
sqlCommand.Append("JOIN mp_moduledefinitions md ");
sqlCommand.Append("ON m.moduledefid = md.moduledefid ");
sqlCommand.Append("JOIN mp_pagemodules pm ");
sqlCommand.Append("ON m.moduleid = pm.moduleid ");
sqlCommand.Append("JOIN mp_pages p ");
sqlCommand.Append("ON p.pageid = pm.pageid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.siteid = :siteid ");
sqlCommand.Append("AND pm.pageid = :pageid ");
sqlCommand.Append(" ; ");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetHtmlContentByPage(int siteId, int pageId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("pageid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT h.*, ");
sqlCommand.Append("m.moduletitle, ");
sqlCommand.Append("m.viewroles, ");
sqlCommand.Append("m.IncludeInSearch, ");
sqlCommand.Append("md.featurename, ");
sqlCommand.Append("u1.name AS createdbyname, ");
sqlCommand.Append("u1.firstname AS createdbyfirstname, ");
sqlCommand.Append("u1.lastname AS createdbylastname, ");
sqlCommand.Append("u1.email AS createdbyemail, ");
sqlCommand.Append("u1.authorbio, ");
sqlCommand.Append("u1.avatarurl, ");
sqlCommand.Append("COALESCE(u1.userid, -1) As authoruserid ");
sqlCommand.Append("FROM mp_htmlcontent h ");
sqlCommand.Append("JOIN mp_modules m ");
sqlCommand.Append("ON h.moduleid = m.moduleid ");
sqlCommand.Append("JOIN mp_moduledefinitions md ");
sqlCommand.Append("ON m.moduledefid = md.moduledefid ");
sqlCommand.Append("JOIN mp_pagemodules pm ");
sqlCommand.Append("ON m.moduleid = pm.moduleid ");
sqlCommand.Append("JOIN mp_pages p ");
sqlCommand.Append("ON p.pageid = pm.pageid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_users u1 ");
sqlCommand.Append("ON h.userguid = u1.userguid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.siteid = :siteid ");
sqlCommand.Append("AND pm.pageid = :pageid ");
sqlCommand.Append(" ; ");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
//return NpgsqlHelper.ExecuteReader(
// GetConnectionString(),
// CommandType.StoredProcedure,
// "mp_htmlcontent_selectbypage(:siteid,:pageid)",
// arParams);
}
public static IDataReader GetHtmlForMetaWeblogApi(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("pm.*, ");
sqlCommand.Append("m.moduletitle, ");
sqlCommand.Append("m.sauthorizededitroles, ");
sqlCommand.Append("m.isglobal, ");
sqlCommand.Append("h.body, ");
sqlCommand.Append("h.itemid, ");
sqlCommand.Append("h.itemguid, ");
sqlCommand.Append("h.lastmoduserguid, ");
sqlCommand.Append("h.lastmodutc, ");
sqlCommand.Append("p.pageguid, ");
sqlCommand.Append("p.parentid, ");
sqlCommand.Append("p.parentguid, ");
sqlCommand.Append("p.pagename, ");
sqlCommand.Append("p.useurl, ");
sqlCommand.Append("p.url, ");
sqlCommand.Append("p.reditroles, ");
sqlCommand.Append("p.pageorder, ");
sqlCommand.Append("p.enablecomments, ");
sqlCommand.Append("p.ispending, ");
sqlCommand.Append("pp.pagename As parentname ");
sqlCommand.Append("FROM mp_pagemodules pm ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.moduleid = m.moduleid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_htmlcontent h ");
sqlCommand.Append("ON ");
sqlCommand.Append("h.moduleid = m.moduleid ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_moduledefinitions md ");
sqlCommand.Append("ON ");
sqlCommand.Append("md.moduledefid = m.moduledefid ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_pages p ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.pageid = p.pageid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_pages pp ");
sqlCommand.Append("ON ");
sqlCommand.Append("pp.pageid = p.parentid ");
sqlCommand.Append("WHERE p.siteid = :siteid ");
sqlCommand.Append("AND ");
sqlCommand.Append("md.guid = '881e4e00-93e4-444c-b7b0-6672fb55de10' ");
sqlCommand.Append("AND ");
sqlCommand.Append("pm.panename = 'contentpane' ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("p.pagename, pm.moduleorder ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
Guid siteGuid,
string status,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteGuid, status);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append(" cw.*, ");
sqlCommand.Append("m.ModuleID, ");
sqlCommand.Append("m.ModuleTitle, ");
sqlCommand.Append("createdBy.Name as CreatedByUserName, ");
sqlCommand.Append("createdBy.LoginName as CreatedByUserLogin, ");
sqlCommand.Append("createdBy.Email as CreatedByUserEmail, ");
sqlCommand.Append("cwah.Notes as Notes, ");
sqlCommand.Append("cwah.CreatedDateUtc as RecentActionOn, ");
sqlCommand.Append("recentActionBy.Name as RecentActionByUserName, ");
sqlCommand.Append("recentActionBy.LoginName as RecentActionByUserLogin, ");
sqlCommand.Append("recentActionBy.Email as RecentActionByUserEmail ");
sqlCommand.Append("FROM mp_ContentWorkflow cw ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("cw.ModuleGuid = m.Guid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Users createdBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("createdBy.UserGuid = cw.UserGuid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_ContentWorkflowAuditHistory cwah ");
sqlCommand.Append("ON ");
sqlCommand.Append("cwah.ContentWorkflowGuid = cw.Guid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cwah.Active = 1 ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Users recentActionBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("recentActionBy.UserGuid = cwah.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cw.SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cw.Status = ?Status ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("cw.CreatedDateUtc DESC ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?Status", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
arParams[2] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetUsersInRole(
int siteId,
int roleId,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountOfUsersInRole(siteId, roleId);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("u.userid, ");
sqlCommand.Append("u.name, ");
sqlCommand.Append("u.email, ");
sqlCommand.Append("u.loginname ");
sqlCommand.Append("FROM mp_users u ");
sqlCommand.Append("JOIN mp_userroles ur ");
sqlCommand.Append("ON u.userid = ur.userid ");
sqlCommand.Append("AND ur.roleid = :roleid ");
sqlCommand.Append("WHERE u.siteid = :siteid ");
sqlCommand.Append("ORDER BY u.name ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = roleId;
arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPageInfoForPage(
Guid siteGuid,
string status,
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("p.PageID, ");
sqlCommand.Append("p.PageGuid, ");
sqlCommand.Append("p.PageName, ");
sqlCommand.Append("p.UseUrl, ");
sqlCommand.Append("p.Url As PageUrl, ");
sqlCommand.Append("cw.Guid as WorkflowGuid ");
sqlCommand.Append("FROM mp_ContentWorkflow cw ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("cw.ModuleGuid = m.Guid ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_PageModules pm ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.ModuleID = m.ModuleID ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Pages p ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.PageID = p.PageID ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cw.SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cw.Status = ?Status ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("cw.CreatedDateUtc DESC ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?Status", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
arParams[2] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_PayPalLog table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount();
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageSize;
arParams[1] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_paypallog ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
int moduleId,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(moduleId);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Links ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = ?ModuleID ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ViewOrder, Title ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static DataTable GetWebImageByPage(
int moduleId,
int pageNumber)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_GalleryImages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = ?ModuleID ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int totalRows = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
int pageSize = 1;
int totalPages = totalRows / pageSize;
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder = 0;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
int offset = pageSize * (pageNumber - 1);
sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("i.ItemID, ");
sqlCommand.Append("i.ModuleID, ");
sqlCommand.Append(totalPages.ToString(CultureInfo.InvariantCulture) + " As TotalPages ");
sqlCommand.Append("FROM mp_GalleryImages i ");
sqlCommand.Append("WHERE i.ModuleID = ?ModuleID ");
sqlCommand.Append("ORDER BY i.DisplayOrder, i.ItemID ");
if (pageNumber > 1)
{
sqlCommand.Append("LIMIT " + offset.ToString(CultureInfo.InvariantCulture)
+ ", " + pageSize.ToString(CultureInfo.InvariantCulture) + " ");
}
else
{
sqlCommand.Append("LIMIT "
+ pageSize.ToString(CultureInfo.InvariantCulture) + " ");
}
sqlCommand.Append(" ; ");
arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
DataTable dt = new DataTable();
dt.Columns.Add("ItemID", typeof(int));
//dt.Columns.Add("ModuleID", typeof(int));
//dt.Columns.Add("Caption", typeof(String));
//dt.Columns.Add("ThumbnailFile", typeof(String));
dt.Columns.Add("TotalPages", typeof(int));
using (IDataReader reader = MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams))
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["ItemID"] = reader["ItemID"];
// row["ModuleID"] = reader["ModuleID"];
//row["Caption"] = reader["Caption"];
row["TotalPages"] = reader["TotalPages"];
dt.Rows.Add(row);
}
}
return(dt);
}
/// <summary>
/// Gets a page of data from the mp_ContentHistory table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid contentGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(contentGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
arParams[1] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ch.*, ");
sqlCommand.Append("u.name, ");
sqlCommand.Append("u.loginname, ");
sqlCommand.Append("u.email ");
sqlCommand.Append("FROM mp_contenthistory ch ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_users u ");
sqlCommand.Append("ON ");
sqlCommand.Append("u.userguid = ch.userguid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ch.contentguid = :contentguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ch.historyutc DESC ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetTop1000UsersNotSubscribed(Guid siteGuid, Guid letterInfoGuid, bool excludeIfAnyUnsubscribeHx)
{
int intExcludeIfAnyUnsubscribeHx = 0;
if (excludeIfAnyUnsubscribeHx)
{
intExcludeIfAnyUnsubscribeHx = 1;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("u.UserID, ");
sqlCommand.Append("u.UserGuid, ");
sqlCommand.Append("u.Email ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("u.SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND u.IsDeleted = 0 ");
sqlCommand.Append("AND u.ProfileApproved = 1 ");
sqlCommand.Append("AND u.IsLockedOut = 0 ");
sqlCommand.Append("AND (u.RegisterConfirmGuid IS NULL OR u.RegisterConfirmGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append("AND u.UserGuid NOT IN ");
sqlCommand.Append("(SELECT ls.UserGuid ");
sqlCommand.Append("FROM mp_LetterSubscribe ls ");
sqlCommand.Append("WHERE ls.LetterInfoGuid = ?LetterInfoGuid ");
sqlCommand.Append(") ");
sqlCommand.Append("AND u.UserGuid NOT IN ");
sqlCommand.Append("(SELECT lsx.UserGuid ");
sqlCommand.Append("FROM mp_LetterSubscribeHx lsx ");
sqlCommand.Append("WHERE ((?ExcludeIfAnyUnsubscribeHx = 1) OR (lsx.LetterInfoGuid = ?LetterInfoGuid)) ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY u.UserID ");
sqlCommand.Append("LIMIT 1000 ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?LetterInfoGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = letterInfoGuid.ToString();
arParams[2] = new MySqlParameter("?ExcludeIfAnyUnsubscribeHx", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = intExcludeIfAnyUnsubscribeHx;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
