/// <summary>
/// Gets an IDataReader with one row from the mp_EmailTemplate table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetByModule(Guid moduleGuid, Guid specialGuid1, Guid specialGuid2)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_EmailTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = @ModuleGuid ");
sqlCommand.Append("AND SpecialGuid1 = @SpecialGuid1 ");
sqlCommand.Append("AND SpecialGuid2 = @SpecialGuid2 ");
sqlCommand.Append("ORDER BY Name ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@ModuleGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new FbParameter("@SpecialGuid1", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = specialGuid1.ToString();
arParams[2] = new FbParameter("@SpecialGuid2", FbDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = specialGuid2.ToString();
return(FBSqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetByFeature(Guid siteGuid, Guid featureGuid)
{
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 Name ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@FeatureGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = featureGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_TaxRate table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetTaxRates(
Guid siteGuid,
Guid geoZoneGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_TaxRate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("AND GeoZoneGuid = @GeoZoneGuid ");
sqlCommand.Append("ORDER BY Priority ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@GeoZoneGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = geoZoneGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader Find(string loginProvider, string providerKey)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserLogins ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LoginProvider = @LoginProvider AND ");
sqlCommand.Append("ProviderKey = @ProviderKey ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@LoginProvider", FbDbType.VarChar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = loginProvider;
arParams[1] = new FbParameter("@ProviderKey", FbDbType.VarChar, 128);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = providerKey;
return(FBSqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with rows from the mp_Comments table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetByParentDesc(Guid parentGuid)
{
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, 0) 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.ParentGuid = @ParentGuid ");
sqlCommand.Append("ORDER BY c.CreatedUtc DESC ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ParentGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = parentGuid.ToString();
return(FBSqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetSiteSettingsExList(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT e.* ");
sqlCommand.Append("FROM mp_SiteSettingsEx e ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_SiteSettingsExDef d ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.KeyName = d.KeyName ");
sqlCommand.Append("AND e.GroupName = d.GroupName ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.SiteID = @SiteID ");
sqlCommand.Append("ORDER BY d.GroupName, d.SortOrder ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
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;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST " + pageSize.ToString() + " ");
sqlCommand.Append(" SKIP " + pageLowerBound.ToString() + " ");
sqlCommand.Append(" * ");
sqlCommand.Append("FROM mp_LetterInfo ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("ORDER BY SortRank, Title ");
sqlCommand.Append(" ; ");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@PageNumber", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageNumber;
arParams[2] = new FbParameter("@PageSize", FbDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_RedirectList table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetBySiteAndUrl(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_RedirectList ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND OldUrl = @OldUrl ");
sqlCommand.Append("AND ExpireUtc < @CurrentTime ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@OldUrl", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = oldUrl;
arParams[2] = new FbParameter("@CurrentTime", FbDbType.TimeStamp);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = DateTime.UtcNow;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetMostRecentByOrder(string googleOrderId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 * ");
sqlCommand.Append("FROM mp_GoogleCheckoutLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("OrderNumber = @OrderNumber ");
sqlCommand.Append("AND CartGuid <> '00000000-0000-0000-0000-000000000000' ");
sqlCommand.Append("AND NotificationType = 'NewOrderNotification' ");
sqlCommand.Append(" ");
sqlCommand.Append("ORDER BY CreatedUtc DESC ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@OrderNumber", FbDbType.VarChar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = googleOrderId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetRolesUserIsNotIn(
int siteId,
int userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT r.* ");
sqlCommand.Append("FROM mp_Roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur ");
sqlCommand.Append("ON r.RoleID = ur.RoleID ");
sqlCommand.Append("AND ur.UserID = @UserID ");
sqlCommand.Append("WHERE r.SiteID = @SiteID ");
sqlCommand.Append("AND ur.UserID IS NULL ");
sqlCommand.Append("ORDER BY r.DisplayName ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@UserID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetSharedFolders(int moduleId, int 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 ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new FbParameter("@ParentID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = parentId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets the most recent cache time for the module
/// </summary>
public static DateTime GetLastCacheTime(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 CachedTimeUtc ");
sqlCommand.Append("FROM mp_RssFeedEntries ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = @ModuleGuid ");
sqlCommand.Append("ORDER BY CachedTimeUtc DESC ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
DateTime result = DateTime.UtcNow.AddDays(-1);
using (IDataReader reader = FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams))
{
if (reader.Read())
{
result = Convert.ToDateTime(reader["CachedTimeUtc"]);
}
}
return(result);
}
/// <summary>
/// Gets an IDataReader with rows from the mp_Users table which have the passed in IP Address
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetUsersByIPAddress(Guid siteGuid, string ipv4Address)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT u.* ");
sqlCommand.Append("FROM mp_UserLocation ul ");
sqlCommand.Append("JOIN mp_Users u ");
sqlCommand.Append("ON ul.UserGuid = u.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(u.SiteGuid = @SiteGuid OR @SiteGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append("AND ul.IPAddress = @IPAddress ");
sqlCommand.Append("ORDER BY ul.LastCaptureUTC DESC ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@IPAddress", FbDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = ipv4Address;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader ModuleDefinitionSettingsGetSetting(
Guid featureGuid,
string settingName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ModuleDefinitionSettings ");
sqlCommand.Append("WHERE FeatureGuid = @FeatureGuid ");
sqlCommand.Append("AND SettingName = @SettingName ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@FeatureGuid", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = featureGuid.ToString();
arParams[1] = new FbParameter("@SettingName", FbDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_PayPalLog table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetMostRecentLog(Guid cartGuid, string requestType)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 * ");
sqlCommand.Append("FROM mp_PayPalLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("CartGuid = @CartGuid ");
if (requestType.Length > 0)
{
sqlCommand.Append("AND RequestType = @RequestType ");
}
sqlCommand.Append("ORDER BY CreatedUtc DESC ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@CartGuid", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = cartGuid.ToString();
arParams[1] = new FbParameter("@RequestType", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = requestType;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_UserLocation table.
/// </summary>
/// <param name="userguid"> userguid </param>
/// <param name="iPAddress"> iPAddress </param>
public static IDataReader GetOne(Guid userguid, long iPAddressLong)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Userguid = @Userguid ");
sqlCommand.Append("AND IPAddressLong = @IPAddressLong ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@Userguid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userguid.ToString();
arParams[1] = new FbParameter("@IPAddressLong", FbDbType.BigInt);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = iPAddressLong;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetHistory(int moduleId, int itemId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SharedFilesHistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = @ModuleID ");
sqlCommand.Append("AND ItemID = @ItemID ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new FbParameter("@ItemID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = itemId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetRoleMembers(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("mp_UserRoles.UserID, ");
sqlCommand.Append("mp_Users.Name, ");
sqlCommand.Append("mp_Users.LoginName, ");
sqlCommand.Append("mp_Users.Email ");
sqlCommand.Append("FROM mp_UserRoles ");
sqlCommand.Append("INNER JOIN mp_Users ");
sqlCommand.Append("ON mp_Users.UserID = mp_UserRoles.UserID ");
sqlCommand.Append("WHERE mp_UserRoles.RoleID = @RoleID ; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = roleId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetFriendlyUrl(int siteId, String friendlyUrl)
{
StringBuilder sqlCommand = new StringBuilder();
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@FriendlyUrl", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = friendlyUrl;
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_FriendlyUrls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append(" SiteID = @SiteID ");
sqlCommand.Append("AND FriendlyUrl = @FriendlyUrl ;");
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_ContentHistory table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetOne(Guid guid)
{
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.Guid = @Guid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
return(FBSqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_ContentRating table.
/// </summary>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="userGuid"> userGuid </param>
public static IDataReader GetOneByContentAndUser(Guid contentGuid, Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ContentRating ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ContentGuid = @ContentGuid ");
sqlCommand.Append(" AND UserGuid = @UserGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ContentGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
arParams[1] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoZone table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetByCode(Guid countryGuid, string code)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoZone ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("(CountryGuid = @CountryGuid ");
sqlCommand.Append("OR CountryGuid = UPPER(@CountryGuid)) ");
sqlCommand.Append("AND Code = @Code ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@CountryGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = countryGuid.ToString();
arParams[1] = new FbParameter("@Code", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = code;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_ContentStyle table.
/// </summary>
public static IDataReader GetAllActive(Guid siteGuid, string skinName)
{
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("AND ");
sqlCommand.Append("IsActive = 1 ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("Name ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@SkinName", FbDbType.VarChar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = skinName;
return(FBSqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static Guid GetDraftSubmitter(Guid contentWorkflowGuid)
{
Guid result = Guid.Empty;
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ContentWorkflowGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentWorkflowGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 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(";");
using (IDataReader reader = FBSqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams))
{
if (reader.Read())
{
result = new Guid(reader[0].ToString());
}
}
return(result);
}
//public static DataTable GetModuleDefinitionsBySite(int siteId)
//{
// StringBuilder sqlCommand = new StringBuilder();
// sqlCommand.Append("SELECT md.* ");
// sqlCommand.Append("FROM mp_ModuleDefinitions md ");
// sqlCommand.Append("JOIN mp_SiteModuleDefinitions smd ");
// sqlCommand.Append("ON md.ModuleDefID = smd.ModuleDefID ");
// sqlCommand.Append("WHERE smd.SiteID = @SiteID ");
// sqlCommand.Append("ORDER BY md.SortOrder, md.FeatureName ;");
// FbParameter[] arParams = new FbParameter[1];
// arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
// arParams[0].Direction = ParameterDirection.Input;
// arParams[0].Value = siteId;
// DataTable dt = new DataTable();
// dt.Columns.Add("ModuleDefID", typeof(int));
// dt.Columns.Add("FeatureName", typeof(String));
// dt.Columns.Add("ControlSrc", typeof(String));
// using (IDataReader reader = FBSqlHelper.ExecuteReader(
// GetConnectionString(),
// sqlCommand.ToString(),
// arParams))
// {
// while (reader.Read())
// {
// DataRow row = dt.NewRow();
// row["ModuleDefID"] = reader["ModuleDefID"];
// row["FeatureName"] = reader["FeatureName"];
// row["ControlSrc"] = reader["ControlSrc"];
// dt.Rows.Add(row);
// }
// }
// return dt;
//}
public static IDataReader GetUserModules(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT md.*, ");
sqlCommand.Append("smd.AuthorizedRoles ");
sqlCommand.Append("FROM mp_ModuleDefinitions md ");
sqlCommand.Append("JOIN mp_SiteModuleDefinitions smd ");
sqlCommand.Append("ON md.ModuleDefID = smd.ModuleDefID ");
sqlCommand.Append("WHERE smd.SiteID = @SiteID AND md.IsAdmin = 0 ");
sqlCommand.Append("ORDER BY md.SortOrder, md.FeatureName ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetHtmlContentByPage(int siteId, int 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("AND pm.PublishBeginDate < now() ");
//sqlCommand.Append("AND (pm.PublishEndDate IS NULL OR pm.PublishEndDate > now()) ;");
sqlCommand.Append(" ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@PageID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetSharedFilesByPage(int siteId, int 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("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(" ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@PageID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetWebPartsForMyPage(int siteId)
{
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("0 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 AND m.AvailableForMyPage = 1 )");
sqlCommand.Append(" UNION ");
sqlCommand.Append("(SELECT ");
sqlCommand.Append("-1 As ModuleID, ");
sqlCommand.Append("w.SiteID, ");
sqlCommand.Append("0 As MuduleDefID, ");
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("1 As IsAssembly, ");
sqlCommand.Append("w.WebPartID ");
sqlCommand.Append("FROM mp_WebParts w ");
sqlCommand.Append("WHERE w.SiteID = @SiteID AND w.AvailableForMyPage = 1 )");
sqlCommand.Append("ORDER BY ModuleTitle ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
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 FIRST " + pageSize.ToString(CultureInfo.InvariantCulture) + " ");
if (pageNumber > 1)
{
sqlCommand.Append(" SKIP " + pageLowerBound.ToString(CultureInfo.InvariantCulture) + " ");
}
sqlCommand.Append(" * ");
sqlCommand.Append("FROM mp_Links ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = @ModuleID ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ViewOrder, Title ");
sqlCommand.Append(" ; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_ContentRating table.
/// </summary>
/// <param name="contentGuid">contentGuid</param>
/// <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 = GetCountByContent(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;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST " + pageSize.ToString(CultureInfo.InvariantCulture) + " ");
if (pageNumber > 1)
{
sqlCommand.Append(" SKIP " + pageLowerBound.ToString(CultureInfo.InvariantCulture) + " ");
}
sqlCommand.Append(" * ");
sqlCommand.Append("FROM mp_ContentRating ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ContentGuid = @ContentGuid ");
sqlCommand.Append("ORDER BY CreatedUtc DESC ");
sqlCommand.Append(" ; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ContentGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}