/*
* Wrapper class managing all of the Logging, timings and email sending
* for accessing stock details and stock levels
*/
public void Enrich()
{
sqlLog = new MnS_SQL_Logger();
dtS = DateTime.Now;
hshJSONErrors = new HashSet<string>();
hshNullErrors = new HashSet<string>();
sqlLog.logMessage(CLASS_NAME, "Starting");
custom_update_process();
// Where there any JSON errors that need reporting?
string strErrors = logArrayToDB("M&S Errors. ", "The following entries in the M&S JSON Service have changed:"
, hshJSONErrors
, "\nUpdate Failed"
, sqlLog);
// Were there any null fields that need reporting?
string strWarnings = logArrayToDB("M&S Warnings. ", "The following entries contained null values:\n"
, hshNullErrors
, "\nUpdate succeeded with skipped items"
, sqlLog);
string strTimings = "Timings:\nSQL Fetch:" + formatTime(elapasedTimeMillsecsFetch)
+ " \nSQL Update:" + formatTime(elapasedTimeMillsecsUpdate)
+ " \nSQL Fetch Parent:" + formatTime(elapasedTimeMillsecsParent)
+ " \nAccess MnS Stock Details Webservice:" + formatTime(elapasedTimeMillsecsMnSService)
+ " \nAccess MnS Stock Details Webservice 404:" + formatTime(elapasedTimeMillsecsMnSService404)
+ " \nXML:" + formatTime(elapasedTimeMillsecsXML);
sqlLog.logMessage(CLASS_NAME, "Timings. " + strTimings);
string strProcessed = "\nRecord Counts\nProcessed:" + recordsProcessed
+ " \nNot found in M&S service 404 errors:" + fetchError404
+ " \nNot found in M&S service 400 errors:" + productsMissing
+ " \nUpdated:" + successfulUpdates;
sqlLog.logMessage(CLASS_NAME, "Ended. " + strProcessed);
// Send an email summarising the job
if (strErrors.Equals(""))
{
sendEmail("M&S MCFP " + CLASS_NAME, "Batch ran successfully with the following output\n"
+ strProcessed + "\n"
+ strTimings + "\n"
+ strWarnings);
}
else
{
sendEmail("M&S MCFP " + CLASS_NAME , "Batch Failed\n\n"
+ strErrors + "\n"
+ strProcessed + "\n"
+ strTimings + "\n"
+ strWarnings);
}
}
/*
* Iterate through every one of the 67,000 products,fetch the product details and propogate the database
* The 67,000 products have been presented to us by M&S and are hard coded in the database
*/
public void Enrich()
{
MnS_SQL_Logger sqlLog = new MnS_SQL_Logger();
DateTime dtS = DateTime.Now;
HashSet<string> hshJSONErrors = new HashSet<string>();
HashSet<string> hshNullErrors = new HashSet<string>();
sqlLog.logMessage(CLASS_NAME, "Starting");
// SQL Connection for updating the product table
using (SqlConnection conDB = new SqlConnection(MS.DHC.Configuration.Database.WorkerConnection))
{
conDB.Open();
using (SqlCommand command = new SqlCommand("SELECT P.id, P.ProductExternalID, C.TCode FROM Catalogue c"
+ " join Product P on P.asin = c.asin"
+ " where MnSCatalogueReference = 'MS.DHC.13.0'"
+ " and c.asin > '' and P.ProductExternalID > ''"
+ " and SKU > ''", conDB))
using (SqlDataReader rdrCatProducts = command.ExecuteReader())
{
DateTime dtf = DateTime.Now;
TimeSpan span = dtf - dtS;
int ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsFetch = elapasedTimeMillsecsFetch + ms;
while (rdrCatProducts.Read())
{
/*
* Single External Product ID
* Access the M&S Product REST service in order to flesh out the latest product details in our database
* TODO - drop MS.DHC.Amazon.Mapping.BatchResult
*/
DateTime dt1 = DateTime.Now;
int intID = rdrCatProducts.GetInt32(0);
string ProductExternalID = rdrCatProducts.GetString(1);
string strTCode = rdrCatProducts.GetString(2);
string strUrl = "http://marksandspencer-stage.apigee.net/v1/catalog/products/" + ProductExternalID + "?apikey=nIXc5NLAXfUGuxnviQJAODHKdBIh9Lsp";
WebRequest request = WebRequest.Create(strUrl) as HttpWebRequest;
WebResponse response = null;
recordsProcessed++;
try
{
response = request.GetResponse();
}
// TODO break is for 404 errors only - handle all the real errors
catch (System.Net.WebException e)
{
products404++;
DateTime dtMiss = DateTime.Now;
span = dtMiss - dt1;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsMnSService404 = elapasedTimeMillsecsMnSService404 + ms;
continue;
}
Stream dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);
string responseFromServer = reader.ReadToEnd();
reader.Close();
response.Close();
JObject jo = JObject.Parse(responseFromServer);
// a 400 error means the item is missing - ignore
string strStatus = (string)jo["status"];
if (strStatus == "400")
{
DateTime dtMf = DateTime.Now;
span = dtMf - dt1;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsMnSService404 = elapasedTimeMillsecsMnSService404 + ms;
productsMissing++;
}
else
{
successfulUpdates++;
DateTime dtM = DateTime.Now;
span = dtM - dt1;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsMnSService = elapasedTimeMillsecsMnSService + ms;
try
{
string strProductID = (string)jo["product"]["id"];
string strDescription = (string)jo["product"]["description"];
if (strDescription == null)
{
hshNullErrors.Add("[product][description]");
}
else
{
strDescription = strDescription.Replace("'", "''");
}
string strMFPartNumber = (string)jo["product"]["mfPartNumber"];
strMFPartNumber = checkNullString("[product][mfPartNumber]", hshNullErrors, strMFPartNumber);
string strSKU = (string)jo["product"]["skus"][0]["id"];
strSKU = checkNullString("[product][skus][0][id]", hshNullErrors, strSKU);
dynamic data = JsonConvert.DeserializeAnonymousType(responseFromServer, new Object());
IEnumerable<dynamic> attributes = data.product.attributes;
string strAdditionalFeatureValue = "";
string strDeliveryInformationPanel = "";
string strCareInstruction = "";
if (attributes != null)
{
// get the addition features attribute
var additionalFeatures = attributes.FirstOrDefault(a => a.name == "AdditionalFeatures");
strAdditionalFeatureValue = null;
if (additionalFeatures != null) strAdditionalFeatureValue = additionalFeatures.value.Value;
strAdditionalFeatureValue = checkNullString("[product][AdditionalFeatures][value]", hshNullErrors, strAdditionalFeatureValue);
var deliveryInformationPanel = attributes.FirstOrDefault(a => a.name == "DeliveryInformationPanel");
strDeliveryInformationPanel = null;
if (deliveryInformationPanel != null) strDeliveryInformationPanel = deliveryInformationPanel.value.Value; ;
strDeliveryInformationPanel = checkNullString("[product][DeliveryInformationPanel][value]", hshNullErrors, strDeliveryInformationPanel);
var careInstruction = attributes.FirstOrDefault(a => a.name == "CareInstruction");
strCareInstruction = null;
if (careInstruction != null) strCareInstruction = careInstruction.value.Value; ;
strCareInstruction = checkNullString("[product][CareInstruction][value]", hshNullErrors, strCareInstruction);
//string strAdditionalFeatures = (string)jo["product"]["attributes"][0]["AdditionalFeatures"]["value"];
}
DateTime dtX = DateTime.Now;
span = dtX - dtM;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsXML = elapasedTimeMillsecsXML + ms;
// Fetch the Parent ID - necessitates reading the Product table to find the ID
string strParentPartNumber = (string)jo["product"]["parentProductId"];
strParentPartNumber = checkNullString("[product][parentProductId]", hshNullErrors, strParentPartNumber);
int intParentID = -1; // Denote not found
using (SqlConnection con2DB = new SqlConnection(MS.DHC.Configuration.Database.WorkerConnection))
{
con2DB.Open();
using (SqlCommand command2 = new SqlCommand("Select ID from product " +
"where ProductExternalID = '" + strParentPartNumber + "';", con2DB))
using (SqlDataReader rdrProduct = command2.ExecuteReader())
{
while (rdrProduct.Read())
{
intParentID = rdrProduct.GetInt32(0);
}
}
con2DB.Close();
}
DateTime dtP = DateTime.Now;
span = dtP - dtX;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsParent = elapasedTimeMillsecsParent + ms;
// Fetch the List Price
strFieldBeingProcessed = "minPrice";
string strListPrice = (string)jo["product"]["price"]["minPrice"];
strListPrice = checkNullString("[product][price][minPrice][value]", hshNullErrors, strListPrice);
// safety first - check if numberic
double dblListprice = -1; // Denote not found
double dblOut;
bool isNum = double.TryParse(strListPrice, out dblOut);
if (isNum) dblListprice = dblOut;
string strPreviousPrice;
double dblPreviousPrice = -1; // Denote not found
try
{
// Fetch the Previous Price
strFieldBeingProcessed = "wasPrice";
strPreviousPrice = (string)jo["product"]["wasPrice"]["Value"];
strPreviousPrice = checkNullString("[product][wasPrice][value]", hshNullErrors, strPreviousPrice);
// safety first - check if numberic
dblPreviousPrice = -1; // Denote not found
isNum = double.TryParse(strPreviousPrice, out dblOut);
if (isNum) dblPreviousPrice = dblOut;
}
catch (Exception e)
{
registerBadField(strFieldBeingProcessed, hshJSONErrors);
dblPreviousPrice = -2;
}
/* // Columns I cannot find or am guessing
string strTitle = (string)jo["product"]["name"];// Logical Guess
string strMechant = (string)jo["product"]["merchant"];// Can't find
string strHREF = (string)jo["product"]["HREF"];// Can't find. Lot's of thumbnails. Presume this is a link to web
string strListing = (string)jo["product"]["Listing"];// Can't find
string strPromotion = (string)jo["product"]["promotions"];// Not sure because plural also in SKU collection
string strReturns = (string)jo["product"]["Returns"];// This might be FusePump
string strReturnPolicy = (string)jo["product"]["ReturnsPolicy"];// This might be FusePump
string strSafety = (string)jo["product"]["SafetyInformation"];// This might be FusePump
string strStoreCollection = (string)jo["product"]["StoreCollection"];// This might be DeliveryInformationPanel
string strDeliveryAvailable = (string)jo["product"]["DeliveryAvailable"];// DeliveryInformationPanel present?
string strTCode = (string)jo["product"]["TCode"];// Can't find*/
// Update the table with the new information that we have
using (SqlConnection con3DB = new SqlConnection(MS.DHC.Configuration.Database.WorkerConnection))
{
con3DB.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm = con3DB.CreateCommand();
string strSQL = "UPDATE product SET " +
"description = '" + strDescription + "'"
+ ", SKU = '" + strSKU + "'"
+ ", TCode = '" + strTCode + "'"
+ ", Price = " + dblListprice
+ ", PreviousPrice = " + dblPreviousPrice;
/* if (additionalFeatureValue != null)
{
strSQL = strSQL + ", additionalFeatureValue = '" + additionalFeatureValue + "'";
}*/
if (strDeliveryInformationPanel != null)
{
// Delivery available is a boolean!
// strSQL = strSQL + ", DeliveryAvailable = '" + strDeliveryInformationPanel + "'";
}
if (strCareInstruction != null)
{
strSQL = strSQL + ", CareInstructions = '" + strCareInstruction + "'";
}
strSQL = strSQL + ", Parent = " + intParentID +
" where ID = '" + intID + "'";
sqlComm.CommandText = strSQL;
try
{
sqlComm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("oops!");
}
DateTime dtU = DateTime.Now;
span = dtU - dtP;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsUpdate = elapasedTimeMillsecsUpdate + ms;
con3DB.Close();
}
}
catch (Exception e)
{
// Whoops! JSON element not found. Log and report to the job output log
registerBadField(strFieldBeingProcessed, hshJSONErrors);
}
}
}
}
// Close the database
conDB.Close();
// Where there any JSON errors that need reporting?
string strErrors = logArrayToDB("M&S Errors. ", "The following entries in the M&S JSON Service have changed:"
, hshJSONErrors
, "\nUpdate Failed"
, sqlLog);
// Were there any null fields that need reporting?
string strWarnings = logArrayToDB("M&S Warnings. ", "The following entries contained null values:\n"
, hshNullErrors
, "\nUpdate succeeded with skipped items"
, sqlLog);
string strTimings = "Timings:\nSQL Fetch:" + formatTime(elapasedTimeMillsecsFetch)
+ " \nSQL Update:" + formatTime(elapasedTimeMillsecsUpdate)
+ " \nSQL Fetch Parent:" + formatTime(elapasedTimeMillsecsParent)
+ " \nAccess MnS Stock Details Webservice:" + formatTime(elapasedTimeMillsecsMnSService)
+ " \nAccess MnS Stock Details Webservice 404:" + formatTime(elapasedTimeMillsecsMnSService404)
+ " \nXML:" + formatTime(elapasedTimeMillsecsXML);
sqlLog.logMessage(CLASS_NAME, "Timings. " + strTimings);
string strProcessed = "\nRecord Counts\nProcessed:" + recordsProcessed
+ " \nNot found in M&S service 404 errors:" + products404
+ " \nNot found in M&S service 400 errors:" + productsMissing
+ " \nUpdated:" + successfulUpdates;
sqlLog.logMessage(CLASS_NAME, "Ended. " + strProcessed);
// Send an email summarising the job
if (strErrors.Equals(""))
{
sendEmail("M&S MCFP Update Stock Details", "Batch ran successfully with the following output\n\n"
+ strProcessed + "\n"
+ strTimings + "\n"
+ strWarnings);
}
else
{
sendEmail("M&S MCFP Update Stock Details", "Batch Failed\n\n"
+ strErrors + "\n"
+ strProcessed + "\n"
+ strTimings + "\n"
+ strWarnings);
}
}
}
// If the array contains entries then something has gone wrong with the job
// Dump and report to the SQL Event Log
private string logArrayToDB(string strClass, string strPreceeding, HashSet<string> hshTable, string strFollowing, MnS_SQL_Logger sqlLog)
{
string strMessage = "";
if (hshTable.Count > 0)
{
strMessage = strPreceeding;
string[] array1 = hshTable.ToArray();
for (int i = 0; i < hshTable.Count; i++)
{
if (i > 0)
{
strMessage = strMessage + "\n";
}
strMessage = strMessage + " " + array1[i];
}
strMessage = strMessage + strFollowing;
sqlLog.logMessage(CLASS_NAME, strClass + strMessage);
}
return strMessage;
}
/*
* Iterate through every one of the live 68,000 products,fetch the stock level
*/
public void Enrich()
{
// Grab an SQL Logger
MnS_SQL_Logger sqlLog = new MnS_SQL_Logger();
sqlLog.logMessage(CLASS_NAME, "Starting");
HashSet<string> hshJSONErrors = new HashSet<string>();
HashSet<string> hshNullErrors = new HashSet<string>();
DateTime dtS = DateTime.Now;
// SQL Connection for updating the product table
using (SqlConnection conDB = new SqlConnection(MS.DHC.Configuration.Database.WorkerConnection))
{
conDB.Open();
using (SqlCommand command = new SqlCommand("SELECT SKU, P.ID FROM Catalogue C "
+ " join product P on P.asin = C.asin"
+ " where MnSCatalogueReference = 'MS.DHC.13.0'"
+ " and C.asin > '' "
+ " and P.ProductExternalID > '' "
+ " and SKU > ''", conDB))
using (SqlDataReader rdrCatProducts = command.ExecuteReader())
{
DateTime dtf = DateTime.Now;
TimeSpan span = dtf - dtS;
int ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsFetch = elapasedTimeMillsecsFetch + ms;
while (rdrCatProducts.Read())
{
/*
* Single SKU
* Access the M&S Product REST service to get current stock level
*/
DateTime dt1 = DateTime.Now;
string strSKU = rdrCatProducts.GetString(0);
int intID = rdrCatProducts.GetInt32(1);
string strUrl = "http://marksandspencer-stage.apigee.net/v1/inventory/" + strSKU + "?apikey=nIXc5NLAXfUGuxnviQJAODHKdBIh9Lsp";
WebRequest request = WebRequest.Create(strUrl) as HttpWebRequest;
WebResponse response = null;
recordsProcessed++;
try
{
response = request.GetResponse();
}
// TODO break is for 404 errors only - handle all the real errors
catch (System.Net.WebException e)
{
failedUpdates++;
DateTime dtMf = DateTime.Now;
span = dtMf - dt1;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsMnSService404 = elapasedTimeMillsecsMnSService404 + ms;
continue;
}
DateTime dtM = DateTime.Now;
span = dtM - dt1;
//ms = (int)span.TotalMilliseconds;
//elapasedTimeMillsecsMnSService = elapasedTimeMillsecsMnSService + ms;
Stream dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);
string responseFromServer = reader.ReadToEnd();
reader.Close();
response.Close();
// get the stock level
JObject jo = JObject.Parse(responseFromServer);
DateTime dtX = DateTime.Now;
span = dtX - dtM;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsXML = elapasedTimeMillsecsXML + ms;
// a 400 error means the item is missing - ignore
string strStatus = (string)jo["status"];
if (strStatus == "400")
{
DateTime dtMf = DateTime.Now;
span = dtMf - dt1;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsMnSService404 = elapasedTimeMillsecsMnSService404 + ms;
productsMissing++;
}
else
{
DateTime dtOK = DateTime.Now;
span = dtOK - dt1;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsMnSService = elapasedTimeMillsecsMnSService + ms;
successfulUpdates++;
string strFieldBeingProcessed = "[product][skus][0][quantity]";
try
{
}
catch (Exception e)
{
// Whoops! JSON element not found. Log and report to the job output log
registerBadField(strFieldBeingProcessed, hshJSONErrors);
continue;
}
string strStockLevel = (string)jo["inventory"]["skus"][0]["quantity"];
strStockLevel = checkNullString(strFieldBeingProcessed, hshNullErrors, strStockLevel);
double dblStockLevel = -1; // Denote not found
double dblOut;
bool isNum = double.TryParse(strStockLevel, out dblOut);
if (isNum)
{
validStockLevel++;
dblStockLevel = dblOut;
// Update the table with the new information that we have
using (SqlConnection con3DB = new SqlConnection(MS.DHC.Configuration.Database.WorkerConnection))
{
con3DB.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm = con3DB.CreateCommand();
string strSQL = "UPDATE product SET "
+ "Stock = '" + dblStockLevel + "'"
+ " where ID = '" + intID + "'";
sqlComm.CommandText = strSQL;
try
{
sqlComm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("oops!");
}
con3DB.Close();
}
DateTime dtU = DateTime.Now;
span = dtU - dtX;
ms = (int)span.TotalMilliseconds;
elapasedTimeMillsecsUpdate = elapasedTimeMillsecsUpdate + ms;
}
}
}
}
// Close the database
conDB.Close();
// Where there any JSON errors that need reporting?
logArrayToDB("M&S Errors. ", "The following entries in the M&S JSON Service have changed:"
, hshJSONErrors
, ". Update Failed"
, sqlLog);
// Were there any null fields that need reporting?
logArrayToDB("M&S Warnings. ", "The following entries contained null values:"
, hshNullErrors
, ". Update succeeded with skipped items"
, sqlLog);
string strTimings = "Timings:\nSQL Fetch:" + formatTime(elapasedTimeMillsecsFetch)
+ " \nSQL Update:" + formatTime(elapasedTimeMillsecsUpdate)
+ " \nAccess MnS Stock Details Webservice:" + formatTime(elapasedTimeMillsecsMnSService)
+ " \nAccess MnS Stock Details Webservice 404:" + formatTime(elapasedTimeMillsecsMnSService404)
+ " \nXML:" + formatTime(elapasedTimeMillsecsXML);
sqlLog.logMessage(CLASS_NAME, "Timings. " + strTimings);
string strMessage = "Processed:" + recordsProcessed
+ "\nM&S 400 errors:" + productsMissing
+ "\nUpdated:" + successfulUpdates
+ "\nFailed Updates (404) + " + failedUpdates
+ "\nValid Stock Level:" + validStockLevel;
sqlLog.logMessage(CLASS_NAME, "Ended. " + strMessage);
}
}
// If the array contains entries then something has gone wrong with the job
// Dump and report to the SQL Event Log
private void logArrayToDB(string strClass, string strPreceeding, HashSet<string> hshTable, string strFollowing, MnS_SQL_Logger sqlLog)
{
string strMessage = strPreceeding;
if (hshTable.Count > 0)
{
String[] array1 = new String[hshTable.Count];
hshTable.CopyTo(array1);
for (int i = 0; i < hshTable.Count; i++)
{
if (i > 0)
{
strMessage = strMessage + ", ";
}
strMessage = strMessage + array1[i];
}
strMessage = strMessage + strFollowing;
sqlLog.logMessage(CLASS_NAME, strClass + strMessage);
}
}
