public static int GetCountOfSiteRoles(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("COUNT(*) ");
sqlCommand.Append("FROM mp_roles ");
sqlCommand.Append("WHERE siteid = :siteid ");
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(Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
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));
}
public static bool DeleteBySite(int siteId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_contactformmessage ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid) ");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <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 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));
}
/// <summary>
/// Deletes rows from the mp_RssFeedEntries table. Returns true if row deleted.
/// </summary>
/// <param name="feedId"> feedId </param>
/// <returns>bool</returns>
public static bool DeleteEntriesByFeed(int feedId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_rssfeedentries ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("feedid = :feedid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("feedid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = feedId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
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 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));
}
///// <summary>
///// Updates a row in the mp_IndexingQueue table. Returns true if row updated.
///// </summary>
///// <param name="rowId"> rowId </param>
///// <param name="indexPath"> indexPath </param>
///// <param name="serializedItem"> serializedItem </param>
///// <param name="itemKey"> itemKey </param>
///// <param name="removeOnly"> removeOnly </param>
///// <returns>bool</returns>
//public static bool Update(
// Int64 rowId,
// string indexPath,
// string serializedItem,
// string itemKey,
// bool removeOnly)
//{
// NpgsqlParameter[] arParams = new NpgsqlParameter[5];
// arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Bigint);
// arParams[0].Direction = ParameterDirection.Input;
// arParams[0].Value = rowId;
// arParams[1] = new NpgsqlParameter("indexpath", NpgsqlTypes.NpgsqlDbType.Varchar,255);
// arParams[1].Direction = ParameterDirection.Input;
// arParams[1].Value = indexPath;
// arParams[2] = new NpgsqlParameter("serializeditem", NpgsqlTypes.NpgsqlDbType.Text);
// arParams[2].Direction = ParameterDirection.Input;
// arParams[2].Value = serializedItem;
// arParams[3] = new NpgsqlParameter("itemkey", NpgsqlTypes.NpgsqlDbType.Varchar,255);
// arParams[3].Direction = ParameterDirection.Input;
// arParams[3].Value = itemKey;
// arParams[4] = new NpgsqlParameter("removeonly", NpgsqlTypes.NpgsqlDbType.Boolean);
// arParams[4].Direction = ParameterDirection.Input;
// arParams[4].Value = removeOnly;
// StringBuilder sqlCommand = new StringBuilder();
// sqlCommand.Append("UPDATE mp_indexingqueue ");
// sqlCommand.Append("SET ");
// sqlCommand.Append("indexpath = :indexpath, ");
// sqlCommand.Append("serializeditem = :serializeditem, ");
// sqlCommand.Append("itemkey = :itemkey, ");
// sqlCommand.Append("removeonly = :removeonly ");
// sqlCommand.Append("WHERE ");
// sqlCommand.Append("rowid = :rowid ");
// sqlCommand.Append(";");
// int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
// CommandType.Text,
// sqlCommand.ToString(),
// arParams);
// return (rowsAffected > -1);
//}
/// <summary>
/// Deletes a row from the mp_IndexingQueue table. Returns true if row deleted.
/// </summary>
/// <param name="rowId"> rowId </param>
/// <returns>bool</returns>
public static bool Delete(Int64 rowId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Bigint);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_indexingqueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("rowid = :rowid ");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Language table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetOne(Guid guid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_language ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("guid = :guid ");
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static bool DeleteByExtraGuid(Guid extraGuid)
{
const string sqlCommand = @"DELETE FROM mp_tagitem WHERE extraguid = :extraguid;";
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("extraguid", NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = extraGuid.ToString()
}
}.ToArray();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams
);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes a row from the mp_Language table. Returns true if row deleted.
/// </summary>
/// <param name="guid"> guid </param>
/// <returns>bool</returns>
public static bool Delete(Guid guid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_language ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("guid = :guid ");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
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 a count of rows in the mp_ContentRating table.
/// </summary>
public static int GetCountByContent(Guid contentGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
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(Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams)));
}
public static byte[] GetPersonalizationBlob(
int siteId,
String path,
Guid userGuid)
{
Guid pathID = GetOrCreatePathId(siteId, path);
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
arParams[1] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pathID.ToString();
byte[] result = null;
try
{
result = (byte[])NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_sitepersonalizationperuser_getpersonalizationblob(:userid,:pathid)",
arParams);
}
catch (System.InvalidCastException ex)
{
if (log.IsErrorEnabled)
{
log.Error("dbPortal.SitePersonalization_GetPersonalizationBlob", ex);
}
}
return(result);
}
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);
}
public static bool DeleteHistoryByItemID(int itemId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_sharedfileshistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("itemid = :itemid ");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool DeleteModuleDefinitionFromSites(int moduleDefId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_sitemoduledefinitions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduledefid = :moduledefid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduledefid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <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 bool DeleteByLetterInfo(Guid letterInfoGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_lettersendlog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("letterguid IN (SELECT letterguid FROM mp_letter WHERE letterinfoguid = :letterinfoguid) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes rows from the mp_RssFeedEntries table. Returns true if row deleted.
/// </summary>
/// <param name="moduleGuid"> moduleGuid </param>
/// <returns>bool</returns>
public static bool DeleteUnPublishedEntriesByModule(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_rssfeedentries ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleguid = :moduleguid ");
sqlCommand.Append(" AND confirmed = false ");
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();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static void ResetPersonalizationBlob(
int siteId,
String path,
Guid userGuid)
{
Guid pathID = GetOrCreatePathId(siteId, path);
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
arParams[1] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pathID.ToString();
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_sitepersonalizationperuser_deletebypath(:userid,:pathid)",
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));
}
public static bool DeleteByUser(string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE 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;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public static bool DeleteByLevel(string logLevel)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_systemlog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("loglevel = :loglevel ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("loglevel", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = logLevel;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes rows from the mp_SystemLog table. Returns true if rows deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public static bool DeleteOlderThan(DateTime cutoffDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_systemlog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("logdate < :cutoffdate ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("cutoffdate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = cutoffDate;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <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>
/// Updates the subscriber count on a row in the mp_LetterInfo table. Returns true if row updated.
/// </summary>
/// <param name="letterInfoGuid"> letterInfoGuid </param>
/// <returns>bool</returns>
public static bool UpdateSubscriberCount(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();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_letterInfo ");
sqlCommand.Append("SET ");
sqlCommand.Append("subscribercount = ( ");
sqlCommand.Append("SELECT COUNT(*) ");
sqlCommand.Append("FROM mp_lettersubscribe ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("letterinfoguid = :letterinfoguid ");
sqlCommand.Append("), ");
sqlCommand.Append("unverifiedcount = ( ");
sqlCommand.Append("SELECT COUNT(*) ");
sqlCommand.Append("FROM mp_lettersubscribe ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("letterinfoguid = :letterinfoguid AND isverified = false ");
sqlCommand.Append(") ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("letterinfoguid = :letterinfoguid ;");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
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));
}
/// <summary>
/// Deletes a row from the mp_ContentStyle table. Returns true if row deleted.
/// </summary>
/// <param name="guid"> guid </param>
/// <returns>bool</returns>
public static bool SetActivationBySkin(Guid siteGuid, string skinName, bool isActive)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_contentstyle ");
sqlCommand.Append("SET ");
sqlCommand.Append("isactive = :isactive ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append("AND ");
sqlCommand.Append("skinname = :skinname ");
sqlCommand.Append(";");
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("skinname", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = skinName;
arParams[2] = new NpgsqlParameter("isactive", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = isActive;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
