public void InsertOptionalProduct(string marketId, string productId, ResearchProduct optionalProduct)
{
_logger.Debug($"Inserting optional products for ProductId: {productId}");
if (ProductExistsInOptionalProducts(marketId, productId))
{
return;
}
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
if (!(connection.State == System.Data.ConnectionState.Open))
{
connection.Open();
}
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "INSERT into OptionalProducts(MarketID, ProductID, SID, OptionalName, Similarity) VALUES(@MarketID, @ProductID, @SID, @OptionalName, @Similarity)";
command.Parameters.AddWithValue("@MarketID", marketId);
command.Parameters.AddWithValue("@ProductID", productId);
command.Parameters.AddWithValue("@SID", optionalProduct.sID);
command.Parameters.AddWithValue("@OptionalName", optionalProduct.name);
command.Parameters.AddWithValue("@Similarity", optionalProduct.similarity);
try
{
command.ExecuteNonQuery();
}
catch (Exception sqlException)
{
connection.Close();
throw new Exception("526", sqlException);
}
}
connection.Close();
}
}
/// <summary>
/// This method return top 6 similar products (List of ResearchProduct objects) to productName.
/// </summary>
/// <param name="productName"></param>
/// <returns></returns>
public List <ResearchProduct> GetTopFiveSimilarProducts(string productName)
{
if (productName == null)
{
return(new List <ResearchProduct>());
}
string[] words = productName.Split(' ', '.', '-');
List <string> newProductName = new List <string>();
for (int i = 0; i < words.Length && i < 2; i++)
{
newProductName.Add(words[i]);
}
string firstWord = words[0];
string secondWord = "";
if (words.Length > 1)
{
secondWord = words[1];
}
//Dictionary<ResearchProduct, double> topProductDic = new Dictionary<ResearchProduct, double>();
HashSet <ResearchProduct> topProductSet = new HashSet <ResearchProduct>();
foreach (string word in newProductName)
{
if (this.wordsToIgnoreInProductDesc.Contains(word))
{
continue;
}
Dictionary <string, string> similarProducts = DBConnection.GetSimiliarProductNames(word);
if (similarProducts.Count == 0)
{
if (word.Length > 4)
{
similarProducts = DBConnection.GetSimiliarProductNames(word.Substring(0, word.Length - 2));
}
}
foreach (KeyValuePair <string, string> entry in similarProducts)
{
double bonus = 0;
if (CheckIfContainsWord(entry.Value, firstWord) || CheckIfContainsWord(entry.Value, secondWord))
{
if (CheckIfContainsWord(entry.Value, firstWord))
{
bonus += 0.3;
}
if (!secondWord.Equals("") && CheckIfContainsWord(entry.Value, secondWord))
{
bonus += 0.2;
}
double similarity = CalculateSimilarity(productName, entry.Value) + bonus;
var rp = new ResearchProduct(entry.Key, entry.Value, similarity + "");
if (topProductSet.Contains(rp) || similarity < 0.55)
{
continue;
}
topProductSet.Add(rp);
}
}
}
List <ResearchProduct> topProductList = topProductSet.ToList();
topProductList.Sort((element1, element2) => Double.Parse(element2.similarity).CompareTo(Double.Parse(element1.similarity)));
List <ResearchProduct> toReturn = new List <ResearchProduct>();
for (int i = 0; i < Math.Min(6, topProductList.Count); i++)
{
toReturn.Add(topProductList.ElementAt(i));
}
return(toReturn);
}
/// <summary>
/// This method update reciept data after saving receipt.
/// First delete all products of given receipt.
/// Then add all products of updated receipt.
/// </summary>
/// <param name="familyID">family the receipt belongs to</param>
/// <param name="receiptToUpdate">receipt we need to update</param>
public void UpdateReceiptData(string familyID, ReceiptToReturn receiptToUpdate)
{
try
{
ProductDescriptionParser pdp = new ProductDescriptionParser();
string receiptID = receiptToUpdate.receiptID;
List<MetaData> sortedProducts = receiptToUpdate.products.ToList();
//delete -> insert -> updateStatus
DBConnection.DeleteReceiptData(receiptID);
foreach (MetaData product in sortedProducts)
{
string productID = product.getsID();
string productDescription = product.getDescription();
//Parse the desc to find quantity
double quantityInDesc;
string quantityFoundInDesc = pdp.GetQuantityFromDescription(productDescription);
string productQuantity = product.getQuantity();
//if not found quantity in description
if (quantityFoundInDesc.Equals("") || productQuantity.Contains('.'))
{
//if qunatitny is float (0.35 for example) than we set quantityFoundInDesc to 1
if (productQuantity.Contains('.'))
{
quantityFoundInDesc = "1";
}
//not float, set quantityFoundInDesc to 0.1 (default is 100 gr)
else
{
quantityFoundInDesc = "0.1";
}
}
//found quantity in description
else
{
if (double.TryParse(pdp.GetQuantityFromDescription(productDescription), out quantityInDesc))
{
quantityFoundInDesc = (quantityInDesc / 1000) + "";
}
}
string productPrice = product.getPrice();
ResearchProduct rp = product.getOptionalProductsChosen();
DBConnection.InsertReceiptData(familyID, receiptID, productID, productDescription, productQuantity, quantityFoundInDesc, productPrice, 0, true);
if (rp != null)
{
DBConnection.DeleteOptionalData(receiptToUpdate.marketID, product.getsID()); //delete all optional exists
DBConnection.InsertOptionalProduct(receiptToUpdate.marketID, product.getsID(), rp);
}
//not chosen - delete all up to 5 optionals
else if (rp == null)
{
DBConnection.DeleteOptionalData(receiptToUpdate.marketID, product.getsID());
}
}
DBConnection.UpdateStatus(familyID, receiptID, "1");
}
catch (Exception exception)
{
throw exception;
}
}
public void setOptionalProductsChosen(ResearchProduct optionalProducts)
{
this.optionalProductsChosen = optionalProducts;
}
