public ModelReader GetSchema()
{
var connector = GetDBConnector(_connection);
int siteId = connector.GetSiteId(_siteName);
bool replaceUrls;
using (var cmd = connector.CreateDbCommand($"SELECT {SqlQuerySyntaxHelper.Top(connector.DatabaseType, "1")} REPLACE_URLS FROM SITE WHERE SITE_ID = @siteId"))
{
cmd.Parameters.AddWithValue("@siteId", siteId);
replaceUrls = (bool)connector.GetRealScalarData(cmd);
}
var attributes = GetAttributes(connector, siteId);
var contents = GetContents(connector, siteId, attributes);
var model = new ModelReader();
model.Schema.DBType = connector.DatabaseType;
model.Schema.ReplaceUrls = replaceUrls;
model.Schema.SiteName = _siteName;
model.Attributes.AddRange(attributes);
model.Contents.AddRange(contents);
return(model);
}
/// <summary>
/// Проверяет текст запроса на корректность
/// </summary>
internal static bool IsQueryQueryCorrect(string userQuery, out string errorMessage)
{
errorMessage = null;
using (QPConfiguration.OutOfTransaction())
{
try
{
var viewName = $"uq_v_test_{DateTime.Now.Ticks}";
var schema = SqlQuerySyntaxHelper.DbSchemaName(QPContext.DatabaseType);
var createTestViewSql = $"CREATE VIEW {schema}.{viewName} AS {userQuery}";
using (var connect = QPContext.CreateDbConnection())
{
connect.Open();
Common.ExecuteSql(connect, createTestViewSql);
Common.DropView(connect, viewName);
}
return(true);
}
catch (SqlException ex)
{
errorMessage = ex.ErrorsToString();
return(false);
}
catch (NpgsqlException ex)
{
errorMessage = ex.Message;
return(false);
}
}
}
private static string ParseBooleanParam(ArticleSearchQueryParam p)
{
Ensure.NotNull(p);
Ensure.That(p.SearchType == ArticleFieldSearchType.Boolean);
if (string.IsNullOrWhiteSpace(p.FieldColumn))
{
throw new ArgumentException("FieldColumn");
}
// параметры не пустые и их не меньше 2х (используем 1й, 2й - остальные отбрасываем)
if (p.QueryParams == null || p.QueryParams.Length < 2)
{
throw new ArgumentException();
}
// первый параметр должен быть bool
if (!(p.QueryParams[0] is bool))
{
throw new InvalidCastException();
}
// второй параметр должен быть bool
if (!(p.QueryParams[1] is bool))
{
throw new InvalidCastException();
}
var dbType = QPContext.DatabaseType;
var fieldName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, p.FieldColumn.ToLower());
return((bool)p.QueryParams[0]
? $"({GetTableAlias(p)}.{fieldName} IS NULL)"
: $"({GetTableAlias(p)}.{fieldName} = {((bool)p.QueryParams[1] ? 1 : 0)})");
}
public List <AbstractItemData> GetByIds(int siteId, bool isArchive, IEnumerable <int> itemIds, IDbTransaction transaction = null)
{
_logger.LogDebug($"getByIds. siteId: {siteId}, isArchive: {isArchive}, itemIds: {SerializeData(itemIds)}");
if (itemIds == null || !itemIds.Any())
{
throw new ArgumentNullException("itemIds");
}
if (itemIds.Any(x => x <= 0))
{
throw new ArgumentException("itemId <= 0");
}
var idList = SqlQuerySyntaxHelper.IdList(_uow.DatabaseType, "@ItemIds", "itemIds");
var query = GetCmdGetAbstractItemByIds(siteId, isArchive, idList);
List <AbstractItemData> result;
if (_uow.DatabaseType == DatabaseType.SqlServer)
{
result = _uow.Connection.Query <AbstractItemData>(query,
new { ItemIds = SqlQuerySyntaxHelper.IdsToDataTable(itemIds).AsTableValuedParameter("Ids") },
transaction)
.ToList();
}
else
{
result = _uow.Connection.Query <AbstractItemData>(query, new { ItemIds = itemIds }, transaction).ToList();
}
_logger.LogDebug($"getByIds. count: {result.Count}, result: {SerializeData(result)}");
return(result);
}
public async Task <CustomActionInfo> GetCustomActionByAlias(string alias)
{
if (String.IsNullOrWhiteSpace(alias))
{
throw new ArgumentException(nameof(alias));
}
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
await connection.OpenAsync();
var actionInfo = await connection.QuerySingleAsync <CustomActionInfo>($@"
SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")}
ba.CODE AS {nameof(CustomActionInfo.ActionCode)},
et.CODE AS {nameof(CustomActionInfo.EntityTypeCode)}
FROM dbo.CUSTOM_ACTION AS ca
INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.BACKEND_ACTION AS ba ON ca.ACTION_ID = ba.ID
INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.ENTITY_TYPE AS et ON ba.ENTITY_TYPE_ID = et.ID
WHERE ca.ALIAS = @{nameof(alias)} {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}",
new { alias });
return(actionInfo);
}
}
public void ResetFreezing(int[] productIds)
{
if (string.IsNullOrEmpty(_freezeFieldName))
{
return;
}
int userId = _userProvider.GetUserId();
List <string> freezeFieldQueries = new List <string>();
var extensionFreezeMeta = GetExtensionsFreezeMeta();
var dbConnector = GetConnector();
string idsQueryPart = SqlQuerySyntaxHelper.IdList(dbConnector.DatabaseType, "@Ids", "Ids");
foreach (ExtentionFreezeMetaRow row in extensionFreezeMeta)
{
freezeFieldQueries.Add($@"SELECT {row.ContentId} AS ContentId, CONTENT_ITEM_ID AS Id, {row.AttributeId} AS FieldId
FROM content_{row.ContentId}_united
WHERE visible = 1 AND archive = 0
AND {row.AttributeName} in (
SELECT Id
FROM {idsQueryPart})" );
}
var productFreezeMeta = GetProductFreezeMeta();
foreach (ProductFreezeMetaRow row in productFreezeMeta)
{
freezeFieldQueries.Add($@"SELECT {row.ContentId} AS ContentId, CONTENT_ITEM_ID AS Id, {row.AttributeId} AS FieldId
FROM content_{row.ContentId}_united
WHERE visible = 1 AND archive = 0
AND CONTENT_ITEM_ID in (
SELECT Id
FROM {idsQueryPart})" );
}
var dbCommand = dbConnector.CreateDbCommand(string.Join("\nunion all\n", freezeFieldQueries));
dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", productIds, dbConnector.DatabaseType));
var freezeDatesData = dbConnector.GetRealData(dbCommand);
var products = freezeDatesData.AsEnumerable()
.Select(Converter.ToModelFromDataRow <ProductDescriptor>)
.ToArray();
foreach (var g in products.GroupBy(p => new { p.ContentId, p.FieldId }))
{
var extentionContentId = g.Key.ContentId;
var fieldIds = new[] { g.Key.FieldId };
var values = g.Select(p => new Dictionary <string, string>
{
{ FieldName.ContentItemId, p.Id.ToString() },
{ _freezeFieldName, string.Empty }
}).ToArray();
dbConnector.ImportToContent(extentionContentId, values, userId, fieldIds);
}
}
private string GetRegionLinkIdQuery(int siteId)
{
string query =
$@"SELECT {SqlQuerySyntaxHelper.Top(_uow.DatabaseType, "1")} |QPAbstractItem.Regions| FROM |QPAbstractItem| WHERE |QPAbstractItem.Regions|
IS NOT NULL {SqlQuerySyntaxHelper.Limit(_uow.DatabaseType, "1")}";
return(_netNameQueryAnalyzer.PrepareQuery(query, siteId, false, true));
}
private List <DataRow> GetArticlesForExport(IEnumerable <ExportSettings.FieldSetting> fieldsToExpand)
{
var dbType = QPContext.DatabaseType;
var sb = new StringBuilder();
if (_settings.FieldNames.Any())
{
foreach (var s in _settings.FieldNames)
{
sb.AppendFormat(", {0}", s);
}
}
var ns = SqlQuerySyntaxHelper.DbSchemaName(dbType);
foreach (var field in fieldsToExpand)
{
if (field.ExcludeFromSQLRequest)
{
sb.AppendFormat(", NULL as {0}", field.Alias);
}
else if (field.ExactType == FieldExactTypes.O2MRelation)
{
var fieldName = string.Join(" + '; ' + ", GetParts(field));
sb.Append($", {fieldName} as {SqlQuerySyntaxHelper.EscapeEntityName(dbType, field.Alias)}");
}
else if (field.ExactType == FieldExactTypes.M2ORelation && field.Related.Any())
{
var related = field.Related.First(f => !f.IsRelation).Id;
var contentItemIdField = dbType == DatabaseType.Postgres ? "content_item_id::integer" : "content_item_id";
sb.Append($", {ns}.qp_m2o_titles(base.{contentItemIdField}, {related}, {field.RelatedAttributeId}, 255) as {SqlQuerySyntaxHelper.EscapeEntityName(dbType, field.Alias)}");
}
else
{
var contentCondition = field.FromExtension
? $"{SqlQuerySyntaxHelper.EscapeEntityName(dbType, $"ex{field.ContentName}")}.content_item_id"
: "base.content_item_id";
if (dbType == DatabaseType.Postgres)
{
contentCondition += "::integer";
}
sb.Append($", {ns}.qp_link_titles({field.LinkId}, {contentCondition}, {field.RelatedAttributeId}, 255) as {SqlQuerySyntaxHelper.EscapeEntityName(dbType, field.Alias)}");
}
}
var stepLength = Math.Min(_itemsPerStep, _ids.Length - StartFrom + 1);
var stepIds = new int[stepLength];
Array.Copy(_ids, StartFrom - 1, stepIds, 0, stepLength);
var orderBy = string.IsNullOrEmpty(_settings.OrderByField) ? IdentifierFieldName : _settings.OrderByField;
var archive = _settings.IsArchive ? "1": "0";
var filter = $"base.content_item_id in ({string.Join(",", stepIds)}) and base.archive = {archive}";
return(ArticleRepository.GetArticlesForExport(_contentId, _settings.ExtensionsStr, sb.ToString(), filter, 1, _itemsPerStep, orderBy, fieldsToExpand));
}
public static T[] GetFieldValues <T>(DBConnector localdbConnector, int contentId, string fieldName, int[] ids = null, bool isAsync = false)
{
var asyncString = isAsync ? "_async" : string.Empty;
var idsString = ids != null ? $"where content_item_id in ({IdsStr(ids)})" : string.Empty;
var fn = SqlQuerySyntaxHelper.FieldName(localdbConnector.DatabaseType, fieldName);
var sql = $"select {fn} from content_{contentId}{asyncString} {idsString} order by content_item_id asc";
return(localdbConnector.GetRealData(sql).Select().Select(row => ConvertHelpers.ChangeType <T>(row[fieldName])).ToArray());
}
public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE EXTERNAL_NOTIFICATION_QUEUE SET
TRIES = TRIES + 1,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseTypeHelper.ResolveDatabaseType(connection)));
cmd.ExecuteNonQuery();
}
}
private string GetCommonQuery(string method, DatabaseType dbType)
{
int authorizationCoontentId = GetContentId(SettingsTitles.API_AUTHORIZATION_CONTENT_ID);
int tokensCoontentId = GetContentId(SettingsTitles.HIGHLOAD_API_USERS_CONTENT_ID);
var fieldName = method == "GET" ? "Read" : "Write";
var field = SqlQuerySyntaxHelper.EscapeEntityName(dbType, fieldName);
var qp = SqlQuerySyntaxHelper.EscapeEntityName(dbType, "Qp User");
var token = SqlQuerySyntaxHelper.EscapeEntityName(dbType, "Token User");
return(string.Format(CommonQueryTemplate, authorizationCoontentId, tokensCoontentId, field, qp, token));
}
public static void DeleteSentNotifications(DbConnection connection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $"DELETE FROM SYSTEM_NOTIFICATION_QUEUE WHERE SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids, string lastExceptionMessage = null)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
TRIES = TRIES + 1,
LastExceptionMessage = @lastExceptionMessage,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids, lastExceptionMessage);
}
public static void UpdateSentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)},
LastExceptionMessage = NULL,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
public AuthenticationService(DBConnector connector)
{
_connector = connector;
_clearSIDQuery = $@"UPDATE sessions_log SET sid = NULL {
SqlQuerySyntaxHelper.Output(_connector.DatabaseType, new[] { "USER_ID", "SESSION_ID" })
} WHERE sid = @sid {SqlQuerySyntaxHelper.Returning(_connector.DatabaseType, new[] { "USER_ID", "SESSION_ID" })}";
_tokenQuery = $@"select UserId, ExpirationDate
from access_token
where Token = @token and Application = @application and {
SqlQuerySyntaxHelper.Now(_connector.DatabaseType)
} < ExpirationDate";
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids, string lastExceptionMessage = null)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
cmd.Parameters.AddWithValue("@lastExceptionMessage", lastExceptionMessage);
cmd.ExecuteNonQuery();
}
}
public static void InsertNotifications(DbConnection connection, string notificationsXml)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = dbType == DatabaseType.Postgres ? PgInsertNotificationsQuery : SqlInsertNotificationsQuery;
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@notifications", notificationsXml, dbType));
cmd.ExecuteNonQuery();
}
}
private static IEnumerable <string> GetParts(ExportSettings.FieldSetting field)
{
var parts = new List <string>();
var dbType = QPContext.DatabaseType;
var ns = SqlQuerySyntaxHelper.DbSchemaName(dbType);
var contentItemIdField = dbType == DatabaseType.Postgres ? "content_item_id::integer" : "content_item_id";
if (field.Related == null || !field.Related.Any())
{
parts.Add($"{SqlQuerySyntaxHelper.CastToString(dbType, field.TableAlias)}.content_item_id");
}
else
{
foreach (var f in field.Related)
{
switch (f.ExactType)
{
case FieldExactTypes.M2MRelation:
parts.Add($"{ns}.qp_link_titles({f.LinkId.Value}, {field.TableAlias}.{contentItemIdField}, {f.RelatedAttributeId}, 255)");
break;
case FieldExactTypes.O2MRelation:
parts.Add($"{SqlQuerySyntaxHelper.CastToString(dbType, $"{f.TableAlias}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, f.RelatedAttributeName)}")}");
break;
case FieldExactTypes.M2ORelation:
parts.Add($"{ns}.qp_m2o_titles(base.{contentItemIdField}, {f.Id}, {field.RelatedAttributeId}, 255)");
break;
default:
parts.Add(new[]
{
FieldExactTypes.Date,
FieldExactTypes.DateTime,
FieldExactTypes.Time,
FieldExactTypes.Textbox,
FieldExactTypes.VisualEdit,
FieldExactTypes.Numeric,
FieldExactTypes.Classifier
}.Contains(f.ExactType)
? $"coalesce({SqlQuerySyntaxHelper.CastToString(dbType, $"{field.TableAlias}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, f.Name)}")}, '')"
: $"coalesce( {field.TableAlias}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, f.Name)}, '')");
break;
}
}
}
return(parts);
}
private static string GetExtensions(IEnumerable <Content> extensionContents)
{
var sb = new StringBuilder();
foreach (var content in extensionContents)
{
var name = content.Fields.Single(f => f.Aggregated).Name;
var dbType = QPContext.DatabaseType;
var tableName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, $"ex{content.Name}");
sb.Append($" LEFT JOIN CONTENT_{content.Id}_united {tableName} ON {tableName}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, name)} = base.CONTENT_ITEM_ID ");
}
return(sb.ToString());
}
/// <summary>
/// Возвращает информацию о столбцах запроса
/// </summary>
internal static IEnumerable <UserQueryColumn> GetQuerySchema(string userQuery)
{
using (var scope = new QPConnectionScope())
{
var viewName = $"uq_v_test_{DateTime.Now.Ticks}";
var schema = SqlQuerySyntaxHelper.DbSchemaName(QPContext.DatabaseType);
var createTestViewSql = $"CREATE VIEW {schema}.{viewName} AS {userQuery}";
RunCreateViewDdl(createTestViewSql);
var dttU = Common.GetViewColumnUsage(scope.DbConnection, viewName);
DropView(viewName);
return(DataTableToUserQueryColumns(dttU));
}
}
public void AddFormToContent_ThrowsException_ValidateConstraintForDataConflict()
{
var titleName = DbConnector.FieldName(Global.SiteId, ContentName, "Title");
var numberName = DbConnector.FieldName(Global.SiteId, ContentName, "Number");
var fn = SqlQuerySyntaxHelper.FieldName(DbConnector.DatabaseType, "Title");
var cmd = DbConnector.CreateDbCommand($"select content_item_id from content_{ContentId}_united where {fn} <> 'Name2'");
var id = (int)(decimal)DbConnector.GetRealScalarData(cmd);
var article1 = new Hashtable
{
[titleName] = "Name2",
[numberName] = "9,5"
};
Assert.That(() => { id = DbConnector.AddFormToContent(Global.SiteId, ContentName, "Published", ref article1, id); }, Throws.Exception.TypeOf <QpInvalidAttributeException>().And.Message.Contains("Unique constraint violation"), "Duplicate of test data should violate rules");
}
public string TranslateSortExpression(string sortExpression, DatabaseType dbType)
{
if (string.IsNullOrEmpty(sortExpression))
{
sortExpression = $"{SqlQuerySyntaxHelper.EscapeEntityName(dbType, "ORDER")} ASC";
}
var replaces = new Dictionary <string, string>
{
{ "LastModifiedByUser", "LOGIN" },
{ "ActionTypeName", "ACTION_TYPE_NAME" },
{ "EntityTypeName", "ENTITY_TYPE_NAME" }
};
return(TranslateHelper.TranslateSortExpression(sortExpression, replaces));
}
public void MassUpdate_ThrowsException_ValidateConstraintForDataConflict()
{
var values = new List <Dictionary <string, string> >();
var fn = SqlQuerySyntaxHelper.FieldName(DbConnector.DatabaseType, "Title");
var cmd = DbConnector.CreateDbCommand($"select content_item_id from content_{ContentId}_united where {fn} <> 'Name2'");
var id = (decimal)DbConnector.GetRealScalarData(cmd);
var article1 = new Dictionary <string, string>
{
[FieldName.ContentItemId] = id.ToString(CultureInfo.InvariantCulture),
["Title"] = "Name2",
["Number"] = "9,5"
};
values.Add(article1);
Assert.That(() => DbConnector.MassUpdate(ContentId, values, 1), Throws.Exception.TypeOf <QpInvalidAttributeException>().And.Message.Contains("for content articles"), "Duplicate of test data should violate rules");
}
protected override string GetQuery()
{
var limitsContentId = SettingsService.GetSetting(SettingsTitles.HIGHLOAD_API_LIMITS_CONTENT_ID);
var methodsContentId = SettingsService.GetSetting(SettingsTitles.HIGHLOAD_API_METHODS_CONTENT_ID);
var usersContentId = SettingsService.GetSetting(SettingsTitles.HIGHLOAD_API_USERS_CONTENT_ID);
if (string.IsNullOrEmpty(limitsContentId) || string.IsNullOrEmpty(methodsContentId) || string.IsNullOrEmpty(usersContentId))
{
return(null);
}
var user = SqlQuerySyntaxHelper.EscapeEntityName(_dbType, "User");
return(string.Format(QueryTemplate, limitsContentId, methodsContentId, usersContentId, user));
}
private string[] GetFieldNames(IEnumerable <Content> extensionContents)
{
var result = new List <string>();
var dbType = QPContext.DatabaseType;
var fields = GetBaseFields().Select(s => $"base.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, s.Name)}");
result.AddRange(fields);
foreach (var content in extensionContents)
{
var template = $"{Escape(dbType, "ex{0}")}.{Escape(dbType, "{1}")} {Escape(dbType, "{0}.{1}")}";
fields = GetExtensionFields(content, template, dbType);
result.AddRange(fields);
}
return(result.ToArray());
}
public int[] GetFrozenProductIds(int[] productIds)
{
if (string.IsNullOrEmpty(_freezeFieldName))
{
return(new int[0]);
}
List <string> freezeFieldQueries = new List <string>();
var extensionFreezeMeta = GetExtensionsFreezeMeta();
var dbConnector = GetConnector();
string idsQueryPart = SqlQuerySyntaxHelper.IdList(dbConnector.DatabaseType, "@Ids", "Ids");
foreach (ExtentionFreezeMetaRow row in extensionFreezeMeta)
{
freezeFieldQueries.Add($@"SELECT {row.AttributeName} Id FROM content_{row.ContentId}_united
WHERE visible = 1 AND archive = 0
AND {_freezeFieldName} >= @date
AND {row.AttributeName} in (
SELECT Id
FROM {idsQueryPart})" );
}
var productFreezeMeta = GetProductFreezeMeta();
foreach (ProductFreezeMetaRow row in productFreezeMeta)
{
freezeFieldQueries.Add($@"SELECT CONTENT_ITEM_ID Id FROM content_{row.ContentId}_united
WHERE visible = 1 AND archive = 0
AND {_freezeFieldName} >= @date
AND CONTENT_ITEM_ID in (
SELECT Id
FROM {idsQueryPart})" );
}
var dbCommand = dbConnector.CreateDbCommand(string.Join("\nunion all\n", freezeFieldQueries));
dbCommand.Parameters.AddWithValue("@date", DateTime.Now);
dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", productIds, dbConnector.DatabaseType));
var freezeDatesData = dbConnector.GetRealData(dbCommand);
var ids = GetIds(freezeDatesData);
return(ids);
}
public static Dictionary <int, int[]> GetContentIds(IEnumerable <int> ids, Customer customer)
{
var dbConnector = new DBConnector(customer.ConnectionString, customer.DatabaseType);
var idList = SqlQuerySyntaxHelper.IdList(customer.DatabaseType, "@Ids", "Ids");
var dbCommand = dbConnector.CreateDbCommand($@"SELECT CONTENT_ID, CONTENT_ITEM_ID
FROM CONTENT_ITEM
{SqlQuerySyntaxHelper.WithNoLock(customer.DatabaseType)}
WHERE CONTENT_ITEM_ID IN (SELECT ID FROM {idList})" );
dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, customer.DatabaseType));
var dt = dbConnector.GetRealData(dbCommand);
return(dt
.AsEnumerable()
.GroupBy(x => (int)(decimal)x["CONTENT_ID"])
.ToDictionary(x => x.Key, x => x.Select(y => (int)(decimal)y["CONTENT_ITEM_ID"]).ToArray()));
}
private string GetSqlCommandSelect(int contentId)
{
string select = null;
if (!string.IsNullOrEmpty(Fields))
{
var orderBy = GetSqlCommandOrderBy();
var orderByAttrs = string.IsNullOrEmpty(orderBy)
? new string[] { }
: orderBy
.Split(',')
.Select(n => n.Trim())
.Select(n => CRegex.Replace(AscRegex.Replace(DescRegex.Replace(n, ""), ""), ""))
.Select(n => n.Trim().Replace("[", "").Replace("]", ""))
.ToArray();
var attrs = new HashSet <string>(
DbConnector.GetContentAttributeObjects(contentId)
.Select(n => n.Name.ToLowerInvariant())
.Union(new[] { "content_item_id", "archive", "visible", "created", "modified", "last_modified_by" })
);
select = string.Join(", ", Fields
.Split(',')
.Select(n => n.Trim().Replace("[", "").Replace("]", ""))
.Union(orderByAttrs, StringComparer.InvariantCultureIgnoreCase)
.Where(n => attrs.Contains(n.ToLowerInvariant()))
.Select(n => SqlQuerySyntaxHelper.FieldName(DbConnector.DatabaseType, n))
.ToArray()
);
}
if (string.IsNullOrEmpty(select))
{
select = "c.*";
}
if (UseSecurity && !FilterRecords)
{
select += ", IsNull(pi.permission_level, 0) as current_permission_level ";
}
return(select);
}
public IDictionary <int, AbstractItemExtensionCollection> GetAbstractItemExtensionlessData(IEnumerable <int> ids,
ContentPersistentData baseContent,
bool isStage, IDbTransaction transaction = null)
{
var idListTable = SqlQuerySyntaxHelper.IdList(UnitOfWork.DatabaseType, "@ids", "ids");
var withNoLock = SqlQuerySyntaxHelper.WithNoLock(UnitOfWork.DatabaseType);
string extFieldsQuery =
$@"SELECT * FROM {baseContent.GetTableName(isStage)} ai {withNoLock}
INNER JOIN {idListTable} on Id = ai.Content_item_id";
using (var command = UnitOfWork.Connection.CreateCommand())
{
command.CommandText = extFieldsQuery;
command.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, UnitOfWork.DatabaseType));
command.Transaction = transaction;
return(LoadAbstractItemExtension(command));
}
}