/* * 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); } }