/* Build a SQL insert statement supporting multiple insert values, without duplicating any entries:
* MERGE INTO dbo.Tags AS Target
* USING(VALUES (@param1, @param2),(@param1, @param3)) AS Source (Name, Tag)
* ON Target.Name = Source.Name AND Target.Tag = Source.Tag
* WHEN NOT MATCHED BY Target THEN
* INSERT(Name, Tag) VALUES(Source.Name, Source.Tag);
*
* After substitution:
* USING(VALUES ('AA Battery', 'aa'),('AA Battery', 'battery')) AS Source (Name, Tag)
*/
private static int InsertTags(SqlConnection connection, string item, TagSet tagSet)
{
string insertTagsCommand = $@"
MERGE INTO dbo.Tags AS Target
USING(VALUES {string.Join(",", tagSet.Select((_, index) => $"(@param1, @param{index + 2})"))}) AS Source (NameKey, Tag)
ON Target.NameKey = Source.NameKey AND Target.Tag = Source.Tag
WHEN NOT MATCHED BY Target THEN
INSERT(NameKey, Tag) VALUES(Source.NameKey, Source.Tag);";
int tagsAdded = 0;
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandText = insertTagsCommand;
sqlCommand.Parameters.AddWithValue("@param1", QueryHelper.Instance.SingularizeAndLower(item));
int i = 2;
foreach (string tag in tagSet)
{
sqlCommand.Parameters.AddWithValue($"@param{i++}", tag);
}
tagsAdded = sqlCommand.ExecuteNonQuery();
}
return(tagsAdded);
}
public static BundleWithResponse BundleWithTags(string text, int quantity, SqlConnection connection, ILogger log)
{
string[] itemAndTags = text.Split(" with tags ", StringSplitOptions.RemoveEmptyEntries);
if (itemAndTags.Length != 2)
{
return(new BundleWithResponse());
}
string newItem = itemAndTags[0].Trim();
TagSet existingItemTags = new TagSet(itemAndTags[1]);
// Take a string of words: "Green motor driver"
// Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
// Format as params for SQL query, to defend against SQL injection attacks:
// "@param2,@param3,@param4"
string paramList = string.Join(",", existingItemTags.Select((tag, index) => $"@param{index+2}"));
log.LogInformation(paramList);
int maxResults = 3;
var queryString = $@"
SELECT TOP (@param1) i.Name, i.Quantity, i.Row, i.Col, i.IsSmallBox, t.TagsMatched
FROM dbo.Items i JOIN
(
SELECT NameKey, COUNT(NameKey) TagsMatched
FROM dbo.Tags
WHERE Tag IN({paramList})
GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";
List <TaggedItem> items = new List <TaggedItem>();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = queryString;
command.Parameters.AddWithValue("@param1", maxResults);
int index = 2;
foreach (string tag in existingItemTags)
{
command.Parameters.AddWithValue($"@param{index++}", tag);
}
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
items.Add(new TaggedItem
{
Name = (string)reader["Name"],
Row = (int)reader["Row"],
Col = (int)reader["Col"],
TagsMatched = (int)reader["TagsMatched"],
IsSmallBox = (bool)reader["IsSmallBox"]
});
}
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
return(new BundleWithResponse());
}
finally
{
reader.Close();
}
}
IEnumerable <TaggedItem> fullyMatchedItems = items.Where(a => a.TagsMatched.Value == existingItemTags.Count);
if (fullyMatchedItems.Count() == 1)
{
TaggedItem existingItem = fullyMatchedItems.First();
Item insertItem = new Item(
newItem,
quantity,
existingItem.Row.Value,
existingItem.Col.Value,
existingItem.IsSmallBox.Value);
TagSet newItemTags = new TagSet(newItem);
InsertItemResponse resp = InsertItemWithTags(insertItem, newItemTags, connection, log);
if (resp.Success)
{
return(new BundleWithResponse(true, newItem, quantity, existingItem.Name, insertItem.Row, insertItem.Col));
}
else
{
return(new BundleWithResponse(false, newItem, quantity, existingItem.Name));
}
}
return(new BundleWithResponse());
}
public static FindTagsResponse FindTags(TagSet tagSet, SqlConnection connection, ILogger log, int maxResults = 10)
{
if (tagSet.Count == 0)
{
return(new FindTagsResponse(-1, null));
}
// Take a string of words: "Green motor driver"
// Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
// Format as params for SQL query, to defend against SQL injection attacks:
// "@param2,@param3,@param4"
string paramList = string.Join(",", tagSet.Select((_, index) => $"@param{index + 2}"));
log.LogInformation(paramList);
var queryString = $@"
SELECT i.NameKey, i.Name, i.Quantity, i.Row, i.Col, t.TagsMatched
FROM dbo.Items i JOIN
(
SELECT NameKey, COUNT(NameKey) TagsMatched
FROM dbo.Tags
WHERE Tag IN({paramList})
GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = queryString;
command.Parameters.AddWithValue("@param1", maxResults);
int index = 2;
foreach (string tag in tagSet)
{
command.Parameters.AddWithValue($"@param{index++}", tag);
}
SqlDataReader reader = command.ExecuteReader();
try
{
int i = 0;
List <int[]> coordsAndMatches = new List <int[]>();
while (reader.Read() && i++ < maxResults)
{
coordsAndMatches.Add(new int[] { (int)reader["Row"], (int)reader["Col"], (int)reader["TagsMatched"] });
}
return(new FindTagsResponse(tagSet.Count, coordsAndMatches));
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
return(new FindTagsResponse(tagSet.Count, null));
}
finally
{
reader.Close();
}
}
}