/// <summary>
/// Gets an IDataReader with rows from the mp_FileAttachment table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader SelectBySpecial2(Guid specialGuid2)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_fileattachment ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("specialguid2 = :specialguid2 ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("specialguid2", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = specialGuid2.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GoogleCheckoutLog table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetOne(Guid rowGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("rowguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_googlecheckoutlog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("rowguid = :rowguid ");
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetHistory(int moduleId, int itemId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = moduleId
};
arParams[1] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = itemId
};
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_sharedfileshistory_select(:moduleid,:itemid)",
arParams));
}
public static IDataReader GetRssFeed(int itemId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_rssfeeds ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("itemid = :itemid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_BannedIPAddresses table.
/// </summary>
/// <param name="ipAddress"> ipAddress </param>
public static IDataReader GeByIpAddress(string ipAddress)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_bannedipaddresses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("bannedip = :bannedip ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("bannedip", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = ipAddress;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetHistoryByModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_sharedfileshistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid = :moduleid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetByUser(string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_userclaims ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("userid = :userid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_EmailSendQueue table where DateToSend >= CurrentTime.
/// </summary>
/// <param name="currentTime"> currentTime </param>
public static IDataReader GetEmailToSend(DateTime currentTime)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_emailsendqueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("datetosend >= :currenttime ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("currenttime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = currentTime;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetSharedFolders(int moduleId, int parentId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("parentid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = parentId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("sff.*, ");
sqlCommand.Append("(SELECT COALESCE(SUM(sf.sizeinkb),0) ");
sqlCommand.Append("FROM mp_sharedfiles sf ");
sqlCommand.Append("WHERE sf.folderid = sff.folderid) As sizeinkb ");
sqlCommand.Append("FROM mp_sharedfilefolders sff ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("sff.moduleid = :moduleid ");
sqlCommand.Append("AND sff.parentid = :parentid ");
sqlCommand.Append("ORDER BY sff.foldername ;");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
//return NpgsqlHelper.ExecuteReader(
// GetConnectionString(),
// CommandType.StoredProcedure,
// "mp_sharedfilefolders_selectbymodulev2(:moduleid,:parentid)",
// arParams);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_ContentRating table.
/// </summary>
/// <param name="contentGuid"> contentGuid </param>
public static IDataReader GetStatsByContent(Guid contentGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COALESCE(AVG(rating),0) As currentrating, ");
sqlCommand.Append("Count(*) As totalratings ");
sqlCommand.Append("FROM mp_contentrating ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("contentguid = :contentguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_TaxClass table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetBySite(Guid siteGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_taxclass ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append("ORDER BY title ");
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_PlugNPayLog table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetByCart(Guid cartGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("cartguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = cartGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_PlugNPaylog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cartguid = :cartguid ");
sqlCommand.Append("ORDER BY createdutc ");
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_GeoZone table.
/// </summary>
public static IDataReader GetByCountry(Guid countryGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_geozone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("countryguid = :countryguid ");
sqlCommand.Append("ORDER BY name ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = countryGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static Guid GetDraftSubmitter(Guid contentWorkflowGuid)
{
Guid result = Guid.Empty;
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("contentworkflowguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentWorkflowGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT userguid ");
sqlCommand.Append("FROM mp_contentworkflowaudithistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("contentworkflowguid = :contentworkflowguid ");
sqlCommand.Append("AND ");
sqlCommand.Append("newstatus = 'AwaitingApproval' ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("createddateutc DESC ");
sqlCommand.Append("LIMIT 1 ");
sqlCommand.Append(";");
using (IDataReader reader = NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams))
{
if (reader.Read())
{
result = new Guid(reader[0].ToString());
}
}
return(result);
}
public static DataTable GetDefaultModuleSettingsForModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("m.moduleid, ");
sqlCommand.Append("m.guid AS moduleguid, ");
sqlCommand.Append("ds.settingname, ");
sqlCommand.Append("ds.settingvalue, ");
sqlCommand.Append("ds.controltype, ");
sqlCommand.Append("ds.controlsrc, ");
sqlCommand.Append("ds.helpkey, ");
sqlCommand.Append("ds.sortorder, ");
sqlCommand.Append("ds.groupname, ");
sqlCommand.Append("ds.regexvalidationexpression ");
sqlCommand.Append("FROM mp_modules m ");
sqlCommand.Append("JOIN mp_moduledefinitionSettings ds ");
sqlCommand.Append("ON ds.moduledefid = m.moduledefid ");
sqlCommand.Append("WHERE m.moduleid = :moduleid ");
sqlCommand.Append("ORDER BY ds.sortorder, ds.groupname ;");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
IDataReader reader = NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(DBPortal.GetTableFromDataReader(reader));
}
public static IDataReader GetFeeds(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT f.*, ");
sqlCommand.Append(" (SELECT COUNT(*) FROM mp_rssfeedentries e WHERE e.feedid = f.itemid) AS totalentries ");
sqlCommand.Append("FROM mp_rssfeeds f ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("f.moduleid = :moduleid ");
sqlCommand.Append("ORDER BY f.sortrank, f.author ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_Comments table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetByContentDesc(Guid contentGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT c.*, ");
sqlCommand.Append("COALESCE(u.name, c.username) AS postauthor, ");
sqlCommand.Append("COALESCE(u.userid, -1) AS userid, ");
sqlCommand.Append("COALESCE(u.email, c.useremail) AS authoremail, ");
sqlCommand.Append("COALESCE(u.totalrevenue, 0) AS userrevenue, ");
sqlCommand.Append("COALESCE(u.trusted, false) AS trusted, ");
sqlCommand.Append("u.avatarurl AS postauthoravatar, ");
sqlCommand.Append("COALESCE(c.userurl, u.websiteurl) AS postauthorwebsiteurl ");
sqlCommand.Append("FROM mp_Comments c ");
sqlCommand.Append("LEFT OUTER JOIN mp_users u ");
sqlCommand.Append("ON c.userguid = u.userguid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("c.contentguid = :contentguid ");
sqlCommand.Append("ORDER BY c.createdutc DESC ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_SavedQuery table.
/// </summary>
/// <param name="id"> id </param>
public static IDataReader GetOne(string name)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_savedquery ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("name = :name ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = name;
return NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
}
public static IDataReader GetByModule(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_category ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleguid = :moduleguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("category ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_LetterInfo table.
/// </summary>
/// <param name="letterInfoGuid"> letterInfoGuid </param>
public static IDataReader GetOne(Guid letterInfoGuid)
{
//NpgsqlParameter[] arParams = new NpgsqlParameter[1];
//arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
//arParams[0].Direction = ParameterDirection.Input;
//arParams[0].Value = letterInfoGuid.ToString();
//return NpgsqlHelper.ExecuteReader(
// ConnectionString.GetReadConnectionString(),
// CommandType.StoredProcedure,
// "mp_letterinfo_select_one(:letterinfoguid)",
// arParams);
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_letterinfo ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("letterinfoguid = :letterinfoguid ");
//sqlCommand.Append("ORDER BY ");
// sqlCommand.Append("sortrank, title ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
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);
}
/// <summary>
/// Gets a page of data from the mp_SystemLog 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 GetPageDescending(
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_systemlog ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY id DESC ");
//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));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_RssFeedEntries table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static DataTable GetEntries(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("f.author As feedname, ");
sqlCommand.Append("e.* ");
sqlCommand.Append("FROM mp_rssfeedentries e ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_rssfeeds f ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.feedid = f.itemid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.moduleguid = :moduleguid ");
sqlCommand.Append("ORDER BY e.pubdate DESC ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
IDataReader reader = NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
DataTable dataTable = new DataTable();
dataTable.Columns.Add("FeedId", typeof(int));
dataTable.Columns.Add("FeedName", typeof(string));
dataTable.Columns.Add("PubDate", typeof(DateTime));
dataTable.Columns.Add("Author", typeof(string));
dataTable.Columns.Add("Title", typeof(string));
dataTable.Columns.Add("Description", typeof(string));
dataTable.Columns.Add("BlogUrl", typeof(string));
dataTable.Columns.Add("Link", typeof(string));
dataTable.Columns.Add("Confirmed", typeof(bool));
dataTable.Columns.Add("EntryHash", typeof(int));
using (reader)
{
while (reader.Read())
{
DataRow row = dataTable.NewRow();
row["FeedId"] = reader["FeedId"];
row["FeedName"] = reader["FeedName"];
row["PubDate"] = Convert.ToDateTime(reader["PubDate"]);
row["Author"] = reader["Author"];
row["Title"] = reader["Title"];
row["Description"] = reader["Description"];
row["BlogUrl"] = reader["BlogUrl"];
row["Link"] = reader["Link"];
row["Confirmed"] = Convert.ToBoolean(reader["Confirmed"]);
row["EntryHash"] = reader["EntryHash"];
dataTable.Rows.Add(row);
}
}
return(dataTable);
}
/// <summary>
/// Gets a page of data from the mp_RedirectList table with search term
/// </summary>
public static IDataReader GetPage(
int siteId,
int pageNumber,
int pageSize,
out int totalPages,
string searchTerm = "")
{
var useSearch = !string.IsNullOrWhiteSpace(searchTerm);
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteId, searchTerm);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
Math.DivRem(totalRows, pageSize, out int remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = siteId
},
new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = pageSize
},
new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = pageLowerBound
}
};
if (useSearch)
{
sqlParams.Add(
new NpgsqlParameter("?searchterm", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input,
Value = "%" + searchTerm + "%"
}
);
}
var sqlCommand = $@"SELECT *
FROM mp_redirectlist
WHERE siteid = :siteid
{(useSearch ? "AND (newurl LIKE :searchterm OR oldurl Like :searchterm)" : "")}
ORDER BY oldurl
LIMIT :pagesize
{(pageNumber > 1 ? " OFFSET :pageoffset " : "")};" ;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand,
sqlParams.ToArray()));
}
/// <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;
}
}
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("skinname", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = skinName;
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_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 :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
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 = true ");
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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
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;
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("status", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
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("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 :pageoffset ");
}
sqlCommand.Append(";");
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;
}
}
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("status", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
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 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 = true ");
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 :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_GeoZone 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 countryGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(countryGuid);
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("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = countryGuid.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 gz.*, ");
sqlCommand.Append("gc.name As countryname ");
sqlCommand.Append("FROM mp_geozone gz ");
sqlCommand.Append("JOIN mp_geocountry gc ");
sqlCommand.Append("ON gz.countryguid = gc.Guid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("gz.countryguid = :countryguid ");
sqlCommand.Append("ORDER BY name ");
//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));
}
