/// <summary>
/// Add attributes from SearchSpringAttributes to the given DataTable in the JSON format defined by SearchSpring.
/// </summary>
/// <param name="table"></param>
/// <param name="integrationSqlCon"></param>
/// <returns></returns>
public static DataTable addAttributes(DataTable table, SqlConnection integrationSqlCon)
{
//If/when we move to SQL 2017+, this function can be elimitnated and done in the view with STRING_AGG
SqlCommand attributeCommand = new SqlCommand("SELECT parentSKU, attribute, retailPrice FROM SearchSpringAttribute", integrationSqlCon);
SqlDataReader attributeReader = attributeCommand.ExecuteReader();
table.Columns["attribute"].ReadOnly = false;
table.Columns["attribute"].MaxLength = 50000; //Arbitary big number
if (attributeReader.HasRows)
{
string SKU, attribute, retailPrice, currentAttribute;
EnumerableRowCollection <DataRow> tableRowSearch;
Dictionary <string, string> attributeDictionary = new Dictionary <string, string>();
while (attributeReader.Read())
{
SKU = attributeReader.GetString(0);
attribute = attributeReader.GetString(1);
retailPrice = attributeReader.GetDecimal(2).ToString("0.00");
if (Regex.IsMatch(SKU, "([0-9]{2}-[0-9]{3}-[0-9]{2})") || SKU.ToUpper().Contains("RENT"))
{
if (attributeDictionary.ContainsKey(SKU))
{
attributeDictionary[SKU] += $",{{'option': '{attribute}', 'price': '{retailPrice}'}}";
}
else
{
attributeDictionary.Add(SKU, $"{{'option': '{attribute}', 'price': '{retailPrice}'}}");
}
}
}
foreach (KeyValuePair <string, string> item in attributeDictionary)
{
tableRowSearch = table.AsEnumerable().Where(row => row.Field <string>("sku") == item.Key);
if (tableRowSearch != null && tableRowSearch.Count() > 0)
{
tableRowSearch.First().SetField("attribute", $"[{{'name' : 'Rental Rates', 'values' : [{item.Value}]}}]");
}
}
}
attributeReader.Close();
return(table);
}