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);
}
}
/// <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 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));
}
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);
}
/// <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 async Task <DateTime?> GetMaxPublicationTime()
{
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
await connection.OpenAsync();
DateTime?timestamp = await connection.QuerySingleAsync <DateTime?>($@"
SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")}
Updated
FROM {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.Products
ORDER BY Updated DESC {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}");
return(timestamp);
}
}
public CultureInfo GetCurrentUserCulture()
{
int userId = _userProvider.GetUserId();
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
connection.Open();
int?culture = connection.QueryFirstOrDefault <int?>($@"
SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")} l.LOCALE
FROM {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.LANGUAGES AS l
INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.USERS AS u ON l.LANGUAGE_ID = u.LANGUAGE_ID
WHERE u.USER_ID = @{nameof(userId)} {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}",
new { userId });
return(culture != null?CultureInfo.GetCultureInfo(culture.Value) : null);
}
}
public async Task <IEnumerable <ProductTimestamp> > GetProductTimestamps(DateTime updatedSince)
{
DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
: new SqlConnection(_customer.ConnectionString);
using (connection)
{
await connection.OpenAsync();
var timestamps = await connection.QueryAsync <ProductTimestamp>($@"
SELECT
DpcId AS {nameof(ProductTimestamp.ProductId)},
IsLive AS {nameof(ProductTimestamp.IsLive)},
Updated AS {nameof(ProductTimestamp.Updated)}
FROM {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.Products
WHERE Updated > @{nameof(updatedSince)}",
new { updatedSince });
return(timestamps);
}
}
private static string ParseTimeRangeParam(ArticleSearchQueryParam p)
{
Ensure.NotNull(p);
Ensure.That(p.SearchType == ArticleFieldSearchType.TimeRange);
if (string.IsNullOrWhiteSpace(p.FieldColumn))
{
throw new ArgumentException("FieldColumn");
}
// параметры не пустые и их не меньше 3х (используем 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();
}
// второй параметр должен быть строкой или null
if (p.QueryParams[1] != null && !(p.QueryParams[1] is string))
{
throw new InvalidCastException();
}
// третий параметр должен быть строкой или null
if (p.QueryParams[2] != null && !(p.QueryParams[2] is string))
{
throw new InvalidCastException();
}
// четвертый параметр должен быть bool
if (!(p.QueryParams[3] is bool))
{
throw new InvalidCastException();
}
var isNull = (bool)p.QueryParams[0];
var timeFromString = (string)p.QueryParams[1];
var timeToString = (string)p.QueryParams[2];
var isByValue = (bool)p.QueryParams[3];
TimeSpan?timeFrom = null;
TimeSpan?timeTo = null;
var dbType = QPContext.DatabaseType;
var fieldName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, p.FieldColumn.ToLower());
// isnull == true
if (isNull)
{
return($"({GetTableAlias(p)}.{fieldName} IS NULL)");
}
var ns = SqlQuerySyntaxHelper.DbSchemaName(dbType);
if (isByValue)
{
if (string.IsNullOrWhiteSpace(timeFromString))
{
return(null);
}
if (!Converter.TryConvertToSqlTimeString(timeFromString, out _, out timeFrom))
{
throw new FormatException("time From");
}
// ReSharper disable once PossibleInvalidOperationException
return($"({ns}.qp_abs_time_seconds({GetTableAlias(p)}.{fieldName}) = {timeFrom.Value.TotalSeconds})");
}
// если обе даты пустые - то возвращаем null
if (string.IsNullOrWhiteSpace(timeFromString) && string.IsNullOrWhiteSpace(timeToString))
{
return(null);
}
if (!string.IsNullOrWhiteSpace(timeFromString))
{
if (!Converter.TryConvertToSqlTimeString(timeFromString, out _, out timeFrom))
{
throw new FormatException("time From");
}
}
if (!string.IsNullOrWhiteSpace(timeToString))
{
if (!Converter.TryConvertToSqlTimeString(timeToString, out var _, out timeTo))
{
throw new FormatException("time To");
}
}
// дата "до" пустая а "от" не пустая
if (!string.IsNullOrWhiteSpace(timeFromString) && string.IsNullOrWhiteSpace(timeToString))
{
// ReSharper disable once PossibleInvalidOperationException
return($"({ns}.qp_abs_time_seconds({GetTableAlias(p)}.{fieldName}) >= {timeFrom.Value.TotalSeconds})");
}
// дата "от" пустая а "до" не пустая
if (string.IsNullOrWhiteSpace(timeFromString) && !string.IsNullOrWhiteSpace(timeToString))
{
// ReSharper disable once PossibleInvalidOperationException
return($"({ns}.qp_abs_time_seconds({GetTableAlias(p)}.{fieldName}) <= {timeTo.Value.TotalSeconds})");
}
// обе границы диапазона не пустые
// From < To
// ReSharper disable PossibleInvalidOperationException
if (timeFrom.Value < timeTo.Value)
{
return($"({ns}.qp_abs_time_seconds({GetTableAlias(p)}.{fieldName}) BETWEEN {timeFrom.Value.TotalSeconds} AND {timeTo.Value.TotalSeconds})");
}
// ReSharper restore PossibleInvalidOperationException
return(timeFrom.Value > timeTo.Value
? $"({ns}.qp_abs_time_seconds({GetTableAlias(p)}.{fieldName}) BETWEEN {timeTo.Value.TotalSeconds} AND {timeFrom.Value.TotalSeconds})"
: $"({ns}.qp_abs_time_seconds({GetTableAlias(p)}.{fieldName}) = {timeFrom.Value.TotalSeconds})");
}
