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 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);
}
}
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);
}
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 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()));
}
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));
}
}
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);
}
}
private ProductDescriptor[] GetProducts(DBConnector dbConnector, int[] productIds, string validationFailedField, string validationMessageField)
{
var idList = SqlQuerySyntaxHelper.IdList(dbConnector.DatabaseType, "@ids", "ids");
var productQueryCommand = dbConnector.CreateDbCommand(GetProductQuery(idList));
productQueryCommand.Parameters.AddWithValue("@validationFailedField", validationFailedField);
productQueryCommand.Parameters.AddWithValue("@validationMessageField", validationMessageField);
productQueryCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", productIds, dbConnector.DatabaseType));
var contentIdsData = dbConnector.GetRealData(productQueryCommand);
if (!contentIdsData.Rows.Any())
{
return(new ProductDescriptor[0]);
}
List <string> queries = new List <string>(contentIdsData.Rows.Count);
foreach (DataRow row in contentIdsData.Rows)
{
string contentId = row["CONTENT_ID"].ToString();
queries.Add($@"SELECT {contentId} AS ContentId,
a.CONTENT_ITEM_ID AS Id,
a.{validationFailedField} AS PublicationFailed,
CONVERT(BIT, (CASE WHEN a.{validationMessageField} IS NULL THEN 1 ELSE 0 END)) AS ValidationMessageIsEmpty
FROM content_{contentId}_united AS a
JOIN {idList} ON a.CONTENT_ITEM_ID = ids.ID
WHERE a.ARCHIVE = 0 AND a.VISIBLE = 1");
}
var productCommand = dbConnector.CreateDbCommand(string.Join("\nUNION ALL\n", queries));
productCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", productIds, dbConnector.DatabaseType));
var dt = dbConnector.GetRealData(productCommand);
var products = dt.AsEnumerable()
.Select(Converter.ToModelFromDataRow <ProductDescriptor>)
.ToArray();
return(products);
}
public IDictionary <int, M2mRelations> GetManyToManyData(IEnumerable <int> ids, bool isStage, IDbTransaction transaction = null)
{
var m2MTableName = QpTableNameHelper.GetM2MTableName(isStage);
var idListTable = SqlQuerySyntaxHelper.IdList(UnitOfWork.DatabaseType, "@ids", "ids");
var withNoLock = SqlQuerySyntaxHelper.WithNoLock(UnitOfWork.DatabaseType);
var query = $@"
SELECT link_id, item_id, linked_item_id
FROM {m2MTableName} link {withNoLock}
INNER JOIN {idListTable} on Id = link.item_id";
using (var command = UnitOfWork.Connection.CreateCommand())
{
command.CommandText = query;
command.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, UnitOfWork.DatabaseType));
command.Transaction = transaction;
var result = new Dictionary <int, M2mRelations>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var itemId = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("item_id")));
if (!result.ContainsKey(itemId))
{
result[itemId] = new M2mRelations();
}
result[itemId]
.AddRelation(Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("link_id"))),
Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("linked_item_id"))));
}
return(result);
}
}
}
private static string ParseIdentifierParam(ArticleSearchQueryParam p, ICollection <DbParameter> sqlParams)
{
Ensure.NotNull(p);
Ensure.That(p.SearchType == ArticleFieldSearchType.Identifier);
if (string.IsNullOrWhiteSpace(p.FieldColumn))
{
throw new ArgumentException("FieldColumn");
}
// параметры не пустые и их не меньше 4х (используем 1й, 2й, 3й и 4й - остальные отбрасываем)
if (p.QueryParams == null || p.QueryParams.Length < 4)
{
throw new ArgumentException();
}
// первый параметр должен быть bool
if (!(p.QueryParams[0] is bool))
{
throw new InvalidCastException();
}
// второй параметр должен быть int или long или null
if (p.QueryParams[1] != null && !(p.QueryParams[1] is int || p.QueryParams[1] is long))
{
throw new InvalidCastException();
}
// третий параметр должен быть int или long или null
if (p.QueryParams[2] != null && !(p.QueryParams[2] is int || p.QueryParams[2] is long))
{
throw new InvalidCastException();
}
// четвертый параметр должен быть bool
if (!(p.QueryParams[3] is bool))
{
throw new InvalidCastException();
}
// пятый параметр должен быть null или object[]
if (p.QueryParams[4] != null && !(p.QueryParams[4] is object[]))
{
throw new InvalidCastException();
}
// шестой параметр должен быть bool
if (!(p.QueryParams[5] is bool))
{
throw new InvalidCastException();
}
var inverse = (bool)p.QueryParams[0];
var isByValue = (bool)p.QueryParams[3];
var isByText = (bool)p.QueryParams[5];
var dbType = QPContext.DatabaseType;
var escapedFieldColumnName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, p.FieldColumn.ToLower());
if (isByText)
{
// Если массив null или пустой - то возвращаем null
if (p.QueryParams[4] == null || ((object[])p.QueryParams[4]).Length == 0)
{
return(null);
}
var fieldId = p.FieldId ?? string.Empty;
var paramName = "@field" + fieldId.Replace("-", "_");
var values = ((object[])p.QueryParams[4]).Select(n => n.ToString()?.Trim())
.Where(n => !String.IsNullOrEmpty(n)).Select(int.Parse).ToArray();
if (values.Length == 1)
{
sqlParams.Add(SqlQuerySyntaxHelper.CreateDbParameter(dbType, paramName, values[0]));
return(string.Format(inverse ? "({1}.{0} <> {2} OR {1}.{0} IS NULL)" : "({1}.{0} = {2})", escapedFieldColumnName, GetTableAlias(p), paramName));
}
sqlParams.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam(paramName, values, dbType));
return(string.Format(inverse ? "({1}.{0} NOT IN (select id from {2}) OR {1}.{0} IS NULL)" : "({1}.{0} IN (select id from {2}))",
escapedFieldColumnName, GetTableAlias(p), SqlQuerySyntaxHelper.IdList(dbType, paramName, "i")));
}
// ReSharper disable MergeSequentialChecks
var numberFrom = p.QueryParams[1] is int || p.QueryParams[1] == null ? (int?)p.QueryParams[1] : (long?)p.QueryParams[1];
var numberTo = p.QueryParams[2] is int || p.QueryParams[2] == null ? (int?)p.QueryParams[2] : (long?)p.QueryParams[2];
// ReSharper restore MergeSequentialChecks
if (isByValue)
{
return(!numberFrom.HasValue ? null : string.Format(inverse ? "({1}.{0} <> {2} OR {1}.{0} IS NULL)" : "({1}.{0} = {2})", escapedFieldColumnName, GetTableAlias(p), numberFrom));
}
if (!numberFrom.HasValue && !numberTo.HasValue)
{
return(null);
}
if (numberFrom.HasValue && !numberTo.HasValue)
{
return(inverse
? $"({GetTableAlias(p)}.{escapedFieldColumnName} < {numberFrom})"
: $"({GetTableAlias(p)}.{escapedFieldColumnName} >= {numberFrom})");
}
if (!numberFrom.HasValue)
{
return(inverse
? $"({GetTableAlias(p)}.{escapedFieldColumnName} > {numberTo})"
: $"({GetTableAlias(p)}.{escapedFieldColumnName} <= {numberTo})");
}
if (numberFrom.Value < numberTo.Value)
{
return($"({GetTableAlias(p)}.{escapedFieldColumnName} {(inverse ? "NOT" : "")} BETWEEN {numberFrom} AND {numberTo})");
}
return(numberFrom.Value > numberTo.Value
? $"({GetTableAlias(p)}.{escapedFieldColumnName} {(inverse ? "NOT" : "")} BETWEEN {numberTo} AND {numberFrom})"
: inverse
? $"({GetTableAlias(p)}.{escapedFieldColumnName} <> {numberFrom})"
: $"({GetTableAlias(p)}.{escapedFieldColumnName} = {numberFrom})");
}
private static string ParseO2MRelationParam(ArticleSearchQueryParam p, ICollection <DbParameter> sqlParams)
{
Ensure.NotNull(p);
Ensure.That(p.SearchType == ArticleFieldSearchType.O2MRelation || p.SearchType == ArticleFieldSearchType.Classifier);
if (string.IsNullOrWhiteSpace(p.FieldColumn))
{
throw new ArgumentException("FieldColumn");
}
// параметры не пустые и их не меньше 2х (используем 1й и 2й - остальные отбрасываем)
if (p.QueryParams == null || p.QueryParams.Length < 3)
{
throw new ArgumentException();
}
// первый параметр должен быть null или object[]
if (p.QueryParams[0] != null && !(p.QueryParams[0] is object[]))
{
throw new InvalidCastException();
}
// второй параметр должен быть bool
if (!(p.QueryParams[1] is bool))
{
throw new InvalidCastException();
}
if (!(p.QueryParams[2] is bool))
{
throw new InvalidCastException();
}
var isNull = (bool)p.QueryParams[1];
var inverse = (bool)p.QueryParams[2];
var inverseString = inverse ? "NOT" : string.Empty;
var dbType = QPContext.DatabaseType;
var fieldName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, p.FieldColumn.ToLower());
if (isNull)
{
return(string.Format("({1}.{0} IS {2} NULL)", fieldName, GetTableAlias(p), inverseString));
}
// Если массив null или пустой - то возвращаем null
if (p.QueryParams[0] == null || ((object[])p.QueryParams[0]).Length == 0)
{
return(null);
}
var values = ((object[])p.QueryParams[0]).Select(n => int.Parse(n.ToString())).ToArray();
var fieldId = p.FieldId ?? string.Empty;
var paramName = "@field" + fieldId.Replace("-", "_");
if (values.Length == 1)
{
sqlParams.Add(SqlQuerySyntaxHelper.CreateDbParameter(dbType, paramName, values[0]));
return(inverse
? $"({GetTableAlias(p)}.{fieldName} <> {paramName} OR {GetTableAlias(p)}.{fieldName} IS NULL)"
: $"({GetTableAlias(p)}.{fieldName} = {paramName})");
}
sqlParams.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam(paramName, values, dbType));
return(inverse
? $"({GetTableAlias(p)}.{fieldName} NOT IN (select id from {SqlQuerySyntaxHelper.IdList(dbType, paramName, "i")}) OR {GetTableAlias(p)}.{fieldName} IS NULL)"
: $"({GetTableAlias(p)}.{fieldName} IN (select id from {SqlQuerySyntaxHelper.IdList(dbType, paramName, "i")}))");
}
public List <AbstractItemData> GetItems(int siteId,
bool isArchive,
IEnumerable <int> parentIds,
bool useRegion,
IDbTransaction transaction = null)
{
_logger.LogDebug(
$"getItems. siteId: {siteId}, isArchive: {isArchive}, useRegion: {useRegion}, parentIds: {SerializeData(parentIds)}");
const int maxParentIdsPerRequest = 500;
List <AbstractItemData> items;
string query;
if (parentIds == null || !parentIds.Any())
{
query = GetAbstractItemsQuery(siteId, isArchive, "IS NULL");
items = _uow.Connection.Query <AbstractItemData>(query, transaction).ToList();
if (useRegion)
{
AddRegionInfo(siteId, isArchive, items);
}
_logger.LogDebug($"getItems. count: {items.Count}");
return(items);
}
if (parentIds.Count() > maxParentIdsPerRequest)
{
items = new List <AbstractItemData>();
for (var i = 0; i < (float)parentIds.Count() / maxParentIdsPerRequest; i++)
{
int[] r = parentIds.Skip(i * maxParentIdsPerRequest).Take(maxParentIdsPerRequest).ToArray();
items.AddRange(GetItems(siteId, isArchive, r, useRegion));
}
_logger.LogDebug($"getItems. count: {items.Count}");
return(items);
}
var idList = SqlQuerySyntaxHelper.IdList(_uow.DatabaseType, "@ParentIds", "parentIds");
query = GetAbstractItemsQuery(siteId, isArchive, $"IN (SELECT Id FROM {idList})");
if (_uow.DatabaseType == DatabaseType.SqlServer)
{
items = _uow.Connection.Query <AbstractItemData>(query,
new { ParentIds = SqlQuerySyntaxHelper.IdsToDataTable(parentIds).AsTableValuedParameter("Ids") },
transaction)
.ToList();
}
else
{
items = _uow.Connection.Query <AbstractItemData>(query, new { ParentIds = parentIds }, transaction).ToList();
}
if (useRegion)
{
AddRegionInfo(siteId, isArchive, items);
}
return(items);
}