public DbCommand GetContentItemLinkCommand(int linkId, string itemIds, bool isManyToMany, bool returnAll)
{
if (linkId == LegacyNotFound)
{
return(null);
}
var ids = itemIds.Split(',');
string table;
string query;
if (isManyToMany)
{
table = IsStage ? "item_link_united" : "item_link";
if (ids.Length == 1)
{
query = $@"SELECT linked_item_id FROM {table} {WithNoLock} WHERE item_id = @itemId AND link_id = @linkId";
}
else
{
query = returnAll ? "item_id, linked_item_id" : "DISTINCT linked_item_id";
query = $@"SELECT {query} FROM {table} {WithNoLock} where item_id in ({itemIds}) AND link_id = @linkId";
}
}
else
{
var attr = GetContentAttributeObject(linkId);
if (attr == null)
{
return(null);
}
var attrName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
table = IsStage ? $@"content_{attr.ContentId}_united" : $@"content_{attr.ContentId}";
if (ids.Length == 1)
{
query = $"SELECT content_item_id FROM {table} {WithNoLock} WHERE {attrName} = @itemId";
}
else
{
query = returnAll
? $"[{attr.Name}] as item_id, content_item_id as linked_item_id"
: "DISTINCT content_item_id";
query = $@"SELECT {query} FROM {table} {WithNoLock} WHERE {attrName} in ({itemIds})";
}
}
var cmd = CreateDbCommand(query);
cmd.Parameters.AddWithValue("@linkId", linkId);
cmd.Parameters.AddWithValue("@itemId", ids[0]);
return(cmd);
}
private DbCommand GetValidateConstraintCommand(XContainer validatedDataDoc, IReadOnlyList <ContentAttribute> attrs, out string attrNames)
{
var sb = new StringBuilder();
var validatedIds = validatedDataDoc
.Descendants("ITEM")
.Where(n => !n.Descendants("MISSED_DATA").Any())
.Select(n => int.Parse(n.Attribute("id")?.Value ?? throw new InvalidOperationException()))
.ToArray();
var contentId = attrs[0].ContentId;
attrNames = string.Join(", ", attrs.Select(n => SqlQuerySyntaxHelper.FieldName(DatabaseType, n.Name)));
if (DatabaseType == DatabaseType.SqlServer)
{
sb.AppendLine("declare @default_num int, @default_date datetime;");
sb.AppendLine("set @default_num = -2147483648;");
sb.AppendLine("set @default_date = getdate();");
sb.AppendLine($"WITH X(CONTENT_ITEM_ID, {attrNames})");
sb.AppendLine(@"AS (SELECT doc.col.value('./@id', 'int') CONTENT_ITEM_ID");
foreach (var attr in attrs)
{
var aName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
sb.AppendLine($",doc.col.value('(DATA)[@id={attr.Id}][1]', 'nvarchar(max)') {aName}");
}
sb.AppendLine("FROM @xmlParameter.nodes('/ITEMS/ITEM') doc(col))");
}
else
{
sb.AppendLine($@"WITH X AS(
select xml.* from XMLTABLE('/ITEMS/ITEM' PASSING @xmlParameter COLUMNS
content_item_id int PATH '@id'" );
foreach (var attr in attrs)
{
var aName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
sb.AppendLine($@", {aName} text PATH 'DATA[@id={attr.Id}]'");
}
sb.AppendLine(") xml )");
}
var defaultDate = (DatabaseType == DatabaseType.SqlServer) ? "@default_date" : "now()";
var defaultNum = (DatabaseType == DatabaseType.SqlServer) ? "@default_num" :"-2147483648";
sb.AppendLine($" SELECT c.CONTENT_ITEM_ID FROM CONTENT_{contentId}_UNITED c {WithNoLock} INNER JOIN X ON c.CONTENT_ITEM_ID NOT IN (select v.id from {IdList("@validatedIds", "v")})");
foreach (var attr in attrs)
{
var attrName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
if (attr.IsNumeric)
{
sb.AppendLine($"AND coalesce(c.{attrName}, {defaultNum}) = case when X.{attrName} = '' then {defaultNum} else cast (X.{attrName} as numeric(18, {attr.Size})) end");
}
else if (attr.IsDateTime)
{
sb.AppendLine($"AND coalesce(c.{attrName}, {defaultDate}) = case when X.{attrName} = '' then {defaultDate} else cast (X.{attrName} as timestamp without time zone) end");
}
else
{
sb.AppendLine($"AND coalesce(c.{attrName}, '') = coalesce(X.{attrName}, '')");
}
}
var xml = validatedDataDoc.ToString(SaveOptions.None);
var cmd = CreateDbCommand(sb.ToString());
cmd.CommandTimeout = 120;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam("@xmlParameter", xml, DatabaseType));
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@validatedIds", validatedIds, DatabaseType));
return(cmd);
}
