public static void UpdateSentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE EXTERNAL_NOTIFICATION_QUEUE SET
SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)},
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
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 List <AbstractItemData> GetItems(int siteId, bool isArchive, IEnumerable <int> parentIds, IDbTransaction transaction = null)
{
_logger.LogDebug(
$"getItems. siteId: {siteId}, isArchive: {isArchive}, parentIds: {SerializeData(parentIds)}");
const int maxParentIdsPerRequest = 500;
var idList = SqlQuerySyntaxHelper.IdList(_uow.DatabaseType, "@ParentIds", "parentIds");
var query = _netNameQueryAnalyzer.PrepareQuery(CmdGetAbstractWidgetItem($"IN (SELECT Id FROM {idList})"), siteId, false, true);
if (parentIds == null)
{
throw new ArgumentNullException(nameof(parentIds), "need not null and not empty parent ids");
}
if (!parentIds.Any())
{
return(new List <AbstractItemData>());
}
if (parentIds.Count() > maxParentIdsPerRequest)
{
var result = new List <AbstractItemData>();
for (var i = 0; i < (float)parentIds.Count() / maxParentIdsPerRequest; i++)
{
int[] r = parentIds.Skip(i * maxParentIdsPerRequest).Take(maxParentIdsPerRequest).ToArray();
result.AddRange(GetItems(siteId, isArchive, r));
}
_logger.LogDebug($"getItems. count: {result.Count}");
return(result);
}
else
{
var result = _uow.Connection
.Query <AbstractItemData>(
string.Format(query, SqlQuerySyntaxHelper.ToBoolSql(_uow.DatabaseType, isArchive)),
new { Archive = isArchive, ParentIds = parentIds }, transaction)
.ToList();
_logger.LogDebug($"getItems. count: {result.Count}");
return(result);
}
}
public List <FieldAttributeData> GetItemExtensionFields(int siteId, int value, int extensionId, IDbTransaction transaction = null)
{
_logger.LogDebug($"getItemExtensionFields. siteId: {siteId}, value: {value}, extensionId: {extensionId}");
var fieldNamesQuery = string.Format(CmdGetFieldNames, extensionId, SqlQuerySyntaxHelper.ToBoolSql(_uow.DatabaseType, true));
var fieldNames = _uow.Connection.Query <FieldAttributeData>(fieldNamesQuery, transaction);
var contentRowsQuery = string.Format(CmdGetContentRow, extensionId, value);
var contentRow = _uow.Connection.Query <object>(contentRowsQuery, transaction);
var relatedFieldsQuery = string.Format(CmdGetExtentionItems, extensionId, value);
var relatedFields = _uow.Connection.Query <RelationExtension>(relatedFieldsQuery, transaction);
var dict = contentRow.FirstOrDefault() as IDictionary <string, object>;
foreach (var field in fieldNames)
{
field.Value = dict.FirstOrDefault(x => string.Equals(x.Key, field.FieldName, StringComparison.OrdinalIgnoreCase)).Value;
field.RelationExtensionId = relatedFields.FirstOrDefault(x => x.FieldName == field.FieldName)?.ExtensionId;
}
_logger.LogDebug($"getItemExtensionFields. fieldNames: {SerializeData(fieldNames)}");
return(fieldNames.ToList());
}
//запрос с использованием NetName таблиц и столбцов
private string CmdGetAbstractWidgetItem(string parentExpression)
{
return($@"
SELECT
ai.content_item_id AS Id,
ai.archive AS IsArchive,
ai.|QPAbstractItem.Name| as Alias,
ai.|QPAbstractItem.Title| as Title,
ai.|QPAbstractItem.Parent| AS ParentId,
ai.|QPAbstractItem.IsVisible| AS IsVisible,
ai.|QPAbstractItem.ZoneName| AS ZoneName,
ai.|QPAbstractItem.IndexOrder| AS IndexOrder,
ai.|QPAbstractItem.ExtensionId| AS ExtensionId,
ai.|QPAbstractItem.VersionOf| AS VersionOfId,
def.content_item_id AS DiscriminatorId,
def.|QPDiscriminator.Name| as Discriminator,
def.|QPDiscriminator.IsPage| as IsPage,
CASE WHEN ai.STATUS_TYPE_ID IN (SELECT st.STATUS_TYPE_ID FROM STATUS_TYPE st WHERE st.STATUS_TYPE_NAME=N'Published') THEN 1 ELSE 0 END AS Published
FROM |QPAbstractItem| ai
INNER JOIN |QPDiscriminator| def on ai.|QPAbstractItem.Discriminator| = def.content_item_id
WHERE ai.archive={SqlQuerySyntaxHelper.ToBoolSql(_uow.DatabaseType, false)} AND def.|QPDiscriminator.IsPage|={SqlQuerySyntaxHelper.ToBoolSql(_uow.DatabaseType, false)} AND ai.|QPAbstractItem.Parent| {parentExpression}
");
}
private string GetExstensionIdQuery(DatabaseType dbType) => $@"
select ATTRIBUTE_NAME from CONTENT_ATTRIBUTE
where
CLASSIFIER_ATTRIBUTE_ID = @attributeId and
CONTENT_ID = @contentId and
AGGREGATED = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}" ;