# Keeping unused code for reference ```c# public void Quotes_ProcessEnquiryItems3(int userId, int quoteId /*quote.itemID*/) { try { var quote = Quotes.FirstOrDefault(p => p.itemID == quoteId); //build string of master items and alternate numbers to find in InventoryItems for bottom Quote Items child grid string masterList = ""; string masterSupplierList = ""; string alternateList = ""; string alternateSupplierWhere = ""; //get all enquiry items with part number and quantity linked to item DataTable dtEnquiryItems = xData.GetChildPublishedSurfaceData(3057, quoteId, userId, 0, "QuoteEnquiryItems_QuoteEnquiryItems_Sequence ASC", false); foreach (DataRow enquiryItem in dtEnquiryItems.Rows) { int sequence = 0; int.TryParse(enquiryItem["QuoteEnquiryItems_QuoteEnquiryItems_Sequence"].ToString(), out sequence); string enquiryPartNumber = enquiryItem["QuoteEnquiryItems_QuoteEnquiryItems_PartNumber"].ToString().Trim(); int quantity = 0; int.TryParse(enquiryItem["QuoteEnquiryItems_QuoteEnquiryItems_Quantity"].ToString(), out quantity); //if quantity is now 0 or part number is blank, update request item and process next item //if (quantity <= 0 || enquiryPartNumber == "") //{ // continue; //} //find enquiry part number in PartsMaster.PartsMaster_PartsMasterDetails_PartsMasterPartNumber, or PartsAlternates.PartsAlternates_PartsAlternates_AlternatePartNumber string masterNumber = ""; string partNumber = ""; string description = ""; string addDescription = ""; decimal kg = 0m; if (enquiryPartNumber != "") { DataTable dtMasterMatch = new DataTable(); string masterFilter = " PartsMaster_PartsMasterDetails_PartsMasterPartNumber = '" + enquiryPartNumber + "' "; oSurface partsMasterSurface = new oSurface(); foreach (oSurface tmp in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", "PartsMaster")) { partsMasterSurface = tmp; if (partsMasterSurface.isPublished) { dtMasterMatch = xData.GetPublishedSurfaceDataPaged(partsMasterSurface.recId, 0, 1, "", "", userId, 0, masterFilter, true, false); } else { dtMasterMatch = xData.GetSurfaceQueryDataPaged(partsMasterSurface.recId, 0, 1, "", "", userId, 0, masterFilter, true, false); } } if (dtMasterMatch != null && dtMasterMatch.Rows.Count > 0) { masterNumber = dtMasterMatch.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterPartNumber"].ToString(); partNumber = masterNumber; description = dtMasterMatch.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterDescription"].ToString(); addDescription = dtMasterMatch.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription"].ToString(); decimal.TryParse(dtMasterMatch.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterWeightKG"].ToString(), out kg); } else { //no master match, try to find match in Alternates DataTable dtAlternatesMatch = new DataTable(); foreach (oSurface partsAlternatesSurface in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", "PartsAlternates")) { //use custom queries, because it's a child surface, but need to search all items, our stored procs don't allow that if (partsAlternatesSurface.isPublished) { string q1 = "select PartsAlternates_PartsAlternates_AlternatePartNumber,PartsAlternates_PartsAlternates_PartsMasterPartNumber from publ_PartsAlternates where PartsAlternates_PartsAlternates_AlternatePartNumber = '" + enquiryPartNumber.Replace("'", "''") + "' AND ParentSurfaceItemId <> 0 and isActive = 1 "; dtAlternatesMatch = xData.GetCustomTypedTable(q1); } else { string q2 = "select PartsAlternates_PartsAlternates_AlternatePartNumber,PartsAlternates_PartsAlternates_PartsMasterPartNumber from qt_PartsAlternates where PartsAlternates_PartsAlternates_AlternatePartNumber = '" + enquiryPartNumber.Replace("'", "''") + "' AND ParentSurfaceItemId <> 0 "; dtAlternatesMatch = xData.GetCustomTypedTable(q2); } } if (dtAlternatesMatch != null && dtAlternatesMatch.Rows.Count > 0) { partNumber = dtAlternatesMatch.Rows[0]["PartsAlternates_PartsAlternates_AlternatePartNumber"].ToString(); masterNumber = dtAlternatesMatch.Rows[0]["PartsAlternates_PartsAlternates_PartsMasterPartNumber"].ToString(); string alternateMasterFilter = " PartsMaster_PartsMasterDetails_PartsMasterPartNumber = '" + masterNumber + "' "; DataTable dtAlternateMaster = new DataTable(); if (partsMasterSurface.isPublished) dtAlternateMaster = xData.GetPublishedSurfaceDataPaged(partsMasterSurface.recId, 0, 1, "", "", userId, 0, alternateMasterFilter, true, false); else dtAlternateMaster = xData.GetSurfaceQueryDataPaged(partsMasterSurface.recId, 0, 1, "", "", userId, 0, alternateMasterFilter, true, false); if (dtAlternateMaster != null && dtAlternateMaster.Rows.Count > 0) { description = dtAlternateMaster.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterDescription"].ToString(); addDescription = dtAlternateMaster.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription"].ToString(); decimal.TryParse(dtAlternateMaster.Rows[0]["PartsMaster_PartsMasterDetails_PartsMasterWeightKG"].ToString(), out kg); } } //CVH 2018-12-18 No match in alternates either, try find match in CAT list else { //CVH 2019-02-15 Optimizing query //string query = "select * " + // "from publ_SupplierPricelistParts p left outer join qt_SupplierPricelists s on s.itemID = p.ParentSurfaceItemId " + // "where s.SupplierPricelists_SupplierPricelistDetails_SupplierTradingName = 'CAT' " + // "and p.isActive = 1 and s.SupplierPricelists_SupplierPricelistDetails_SupplierPricelistStatus = 'Current' " + // "and(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber in ('" + enquiryPartNumber.Replace("'", "''") + "') " + // "or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '" + enquiryPartNumber.Replace("'", "''") + "' or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like ' %, " + enquiryPartNumber.Replace("'", "''") + "' or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '" + enquiryPartNumber.Replace("'", "''") + ",% ' )"; string query = "select p.itemID, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, " + "p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsDescription, " + "p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAdditionalDescription, " + "p.SupplierPricelistParts_SupplierPricelistParts_WeightKG, " + "p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, " + "p.SupplierPricelistParts_SupplierPricelistParts_WeightLBS " + "from publ_SupplierPricelistParts p " + "where p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName = 'CAT' " + "and p.isActive = 1 and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = 'Current' " + "and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber in ('" + enquiryPartNumber.Replace("'", "''") + "') "; DataTable dtCATMatch = xData.GetCustomTypedTable(query); if (dtCATMatch != null && dtCATMatch.Rows.Count > 0) { partNumber = enquiryPartNumber; masterNumber = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString(); description = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsDescription"].ToString(); addDescription = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAdditionalDescription"].ToString(); decimal.TryParse(dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_WeightKG"].ToString(), out kg); //CVH 2019-01-07 Create a new Part Master record based on CAT item details oSurfaceItem masterItem = new oSurfaceItem(); masterItem.createdBy = userId; masterItem.dateCreated = System.DateTime.Now; masterItem.isActive = true; masterItem.isDeleted = false; masterItem.surfaceId = partsMasterSurface.recId; masterItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), masterItem); //ArrayList masterData = new ArrayList(); oPartsMaster partsMaster = new oPartsMaster(); partsMaster.itemID = masterItem.recId; partsMaster.surfaceId = masterItem.surfaceId; partsMaster.isActive = masterItem.isActive; //CVH 2019-03-19 Don't save surfacefield data for newly published surface PartsMaster partsMaster.PartsMaster_PartsMasterAlternates_AlternatePartNumbers = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers"].ToString(); partsMaster.PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAdditionalDescription"].ToString(); foreach (oSurfaceField masterField in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceField), "surfaceId,isActive,surfaceFieldName", masterItem.surfaceId + ",1,PartsMaster_PartsMasterDetails_PartsMasterCEPNumber")) { int value = 0; int.TryParse(masterField.controlledValue, out value); value++; masterField.controlledValue = value.ToString(); xData.UpdateTyped("recId", masterField.recId.ToString(), typeof(oSurfaceField), masterField); partsMaster.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber = masterField.controlPrefix + value; } partsMaster.PartsMaster_PartsMasterDetails_PartsMasterDescription = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsDescription"].ToString(); partsMaster.PartsMaster_PartsMasterDetails_PartsMasterItemVetted = 1074; //No partsMaster.PartsMaster_PartsMasterDetails_PartsMasterPartNumber = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString(); partsMaster.PartsMaster_PartsMasterDetails_MasterNumber = dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString(); decimal weightKG = 0; decimal.TryParse(dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_WeightKG"].ToString(), out weightKG); partsMaster.PartsMaster_PartsMasterDetails_PartsMasterWeightKG = weightKG; decimal weightLBS = 0; decimal.TryParse(dtCATMatch.Rows[0]["SupplierPricelistParts_SupplierPricelistParts_WeightLBS"].ToString(), out weightLBS); partsMaster.PartsMaster_PartsMasterDetails_PartsMasterWeightLBS = weightLBS; //xData.SaveTypedCollection("recId", typeof(oSurfaceFieldData), masterData); partsMaster.recId = xData.SaveTyped("recId", typeof(oPartsMaster), partsMaster, "publ_", false); //xData.SaveSurfaceItemToQueryTable(masterItem.surfaceId, masterItem.recId); foreach (oSurface apSurface in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", "PartsAlternates")) { oSurfaceItem partAlternate = new oSurfaceItem(); partAlternate.createdBy = userId; partAlternate.dateCreated = System.DateTime.Now; partAlternate.isActive = true; partAlternate.isDeleted = false; partAlternate.surfaceId = apSurface.recId; partAlternate.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), partAlternate); oPartsAlternates partsAlternates = new oPartsAlternates(); partsAlternates.PartsAlternates_PartsAlternates_PartsMasterCEPNumber = partsMaster.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber; partsAlternates.PartsAlternates_PartsAlternates_AlternatePartNumber = partsMaster.PartsMaster_PartsMasterDetails_MasterNumber; partsAlternates.PartsAlternates_PartsAlternates_PartsMasterPartNumber = partsMaster.PartsMaster_PartsMasterDetails_MasterNumber; partsAlternates.itemID = partAlternate.recId; partsAlternates.surfaceId = partAlternate.surfaceId; partsAlternates.isActive = true; partsAlternates.ParentSurfaceItemId = partsMaster.itemID; partsAlternates.recId = xData.SaveTyped("recId", typeof(oPartsAlternates), partsAlternates, "publ_"); } } else { partNumber = enquiryPartNumber; masterNumber = enquiryPartNumber; description = "***Description Required***"; addDescription = ""; //CVH 2019-01-07 No match in CAT list either, Create a new Part Master record based on Enquiry item details oSurfaceItem masterItem = new oSurfaceItem(); masterItem.createdBy = userId; masterItem.dateCreated = System.DateTime.Now; masterItem.isActive = true; masterItem.isDeleted = false; masterItem.surfaceId = partsMasterSurface.recId; masterItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), masterItem); ArrayList masterData = new ArrayList(); oPartsMaster partsMaster = new oPartsMaster(); partsMaster.itemID = masterItem.recId; partsMaster.surfaceId = masterItem.surfaceId; partsMaster.isActive = masterItem.isActive; foreach (oSurfaceField masterField in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceField), "surfaceId,isActive", masterItem.surfaceId + ",1")) { if (masterField.surfaceFieldTypeId == pNums.FieldType.Tab.GetHashCode() || masterField.surfaceFieldTypeId == pNums.FieldType.Group.GetHashCode() || masterField.surfaceFieldTypeId == pNums.FieldType.HeaderGroup.GetHashCode()) continue; oSurfaceFieldData data = new oSurfaceFieldData(); data.surfaceFieldID = masterField.recId; data.surfaceFieldLookupID = 0; data.surfaceFieldValueBool = false; data.surfaceFieldValueChar = ""; data.surfaceFieldValueDate = System.DateTime.MinValue; data.surfaceFieldValueDecimal = 0; data.surfaceFieldValueNum = 0; data.surfaceFieldValueTime = System.DateTime.MinValue; data.surfaceId = masterItem.surfaceId; data.surfaceItemId = masterItem.recId; if (masterField.surfaceFieldName == "PartsMaster_PartsMasterDetails_PartsMasterCEPNumber") { int value = 0; int.TryParse(masterField.controlledValue, out value); value++; masterField.controlledValue = value.ToString(); xData.UpdateTyped("recId", masterField.recId.ToString(), typeof(oSurfaceField), masterField); data.surfaceFieldValueChar = masterField.controlPrefix + value; partsMaster.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber = data.surfaceFieldValueChar; } else if (masterField.surfaceFieldName == "PartsMaster_PartsMasterDetails_PartsMasterDescription") { data.surfaceFieldValueChar = description; partsMaster.PartsMaster_PartsMasterDetails_PartsMasterDescription = data.surfaceFieldValueChar; } else if (masterField.surfaceFieldName == "PartsMaster_PartsMasterDetails_PartsMasterItemVetted") { data.surfaceFieldLookupID = 1074;//No partsMaster.PartsMaster_PartsMasterDetails_PartsMasterItemVetted = 1074; } else if (masterField.surfaceFieldName == "PartsMaster_PartsMasterDetails_PartsMasterPartNumber") { data.surfaceFieldValueChar = partNumber; partsMaster.PartsMaster_PartsMasterDetails_MasterNumber = data.surfaceFieldValueChar; partsMaster.PartsMaster_PartsMasterDetails_PartsMasterPartNumber = data.surfaceFieldValueChar; } masterData.Add(data); } xData.SaveTypedCollection("recId", typeof(oSurfaceFieldData), masterData); partsMaster.recId = xData.SaveTyped("recId", typeof(oPartsMaster), partsMaster, "publ_", false); foreach (oSurface apSurface in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", "PartsAlternates")) { oSurfaceItem partAlternate = new oSurfaceItem(); partAlternate.createdBy = userId; partAlternate.dateCreated = System.DateTime.Now; partAlternate.isActive = true; partAlternate.isDeleted = false; partAlternate.surfaceId = apSurface.recId; partAlternate.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), partAlternate); oPartsAlternates partsAlternates = new oPartsAlternates(); partsAlternates.PartsAlternates_PartsAlternates_PartsMasterCEPNumber = partsMaster.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber; partsAlternates.PartsAlternates_PartsAlternates_AlternatePartNumber = partsMaster.PartsMaster_PartsMasterDetails_MasterNumber; partsAlternates.PartsAlternates_PartsAlternates_PartsMasterPartNumber = partsMaster.PartsMaster_PartsMasterDetails_MasterNumber; partsAlternates.itemID = partAlternate.recId; partsAlternates.surfaceId = partAlternate.surfaceId; partsAlternates.isActive = true; partsAlternates.ParentSurfaceItemId = partsMaster.itemID; partsAlternates.recId = xData.SaveTyped("recId", typeof(oPartsAlternates), partsAlternates, "publ_"); } //xData.SaveSurfaceItemToQueryTable(masterItem.surfaceId, masterItem.recId); } } } } //CVH 2019-02-15 We're duplicating numbers //master where in (list) //if (partNumber != "") //{ // if (masterSupplierList == "") // masterSupplierList = "'" + masterNumber.Replace("'", "''") + "','" + partNumber.Replace("'", "''") + "'"; // else // masterSupplierList += ",'" + masterNumber.Replace("'", "''") + "','" + partNumber.Replace("'", "''") + "'"; //} if (partNumber != "" && !masterSupplierList.Contains("'" + partNumber.Replace("'", "''") + "'")) { if (masterSupplierList == "") masterSupplierList = "'" + partNumber.Replace("'", "''") + "'"; else masterSupplierList += ",'" + partNumber.Replace("'", "''") + "'"; } if (partNumber != "" && masterNumber != "" && masterNumber != partNumber && !masterSupplierList.Contains("'" + masterNumber.Replace("'", "''") + "'")) { if (masterSupplierList == "") masterSupplierList = "'" + masterNumber.Replace("'", "''") + "'"; else masterSupplierList += ",'" + masterNumber.Replace("'", "''") + "'"; } //CVH 2019-02-15 Don't need this anymore, SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers is not being used //alternate comma separated list //if (enquiryPartNumber != "") //{ // if (alternateSupplierWhere == "") // alternateSupplierWhere = " SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '" + enquiryPartNumber.Replace("'", "''") + "' or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '%," + enquiryPartNumber.Replace("'", "''") + "' or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '" + enquiryPartNumber.Replace("'", "''") + ",%' "; // else // alternateSupplierWhere += " or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '" + enquiryPartNumber.Replace("'", "''") + "' or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '%," + enquiryPartNumber.Replace("'", "''") + "' or SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers like '" + enquiryPartNumber.Replace("'", "''") + ",%' "; //} //save a request item for each enquiry item with a quantity oQuoteRequestItems requestItem = new oQuoteRequestItems(); foreach (oQuoteRequestItems reqTmp in xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteRequestItems), "ParentSurfaceItemId,isActive,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence", quoteId + ",1," + sequence, "", "publ_", false)) { requestItem = reqTmp; } //if no quantity or part number entered, and 0 qty saved for this sequence number, //or the quantity and the part number is unchanged, skip and process next enquiry item if ((requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired == 0 && (enquiryPartNumber == "" || quantity == 0)) || requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired == quantity && requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber == (masterNumber == "" ? enquiryPartNumber : partNumber)) { //delete item if needed if (requestItem.recId > 0 && requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired == 0 && (enquiryPartNumber == "" || quantity == 0)) { xData.DeletePublishedSurfaceItem(requestItem.surfaceId, requestItem.itemID); foreach (oSurfaceItem delItem in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceItem), "recId", requestItem.itemID.ToString())) { delItem.isDeleted = true; delItem.isActive = false; xData.UpdateTyped("recId", delItem.recId.ToString(), typeof(oSurfaceItem), delItem); } } continue; } else { //set quantities //if saved quantity is 0, it means this is new/add if (requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired == 0) { requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding = quantity; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired = quantity; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected = 0; } //if the part number changes and there has been a quantity selected //or the new quantity is less than the quantity selected, need to adjust the quantities, but also update the quote item(s) else if ((requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected > 0 && requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber != (masterNumber == "" ? enquiryPartNumber : partNumber)) || quantity < requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected) { int qtyLess = 0; //part number has changed if (requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber != (masterNumber == "" ? enquiryPartNumber : partNumber)) { qtyLess = requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding = quantity; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired = quantity; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected = 0; } else { //quantity has been reduced qtyLess = requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected - quantity; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding = 0; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired = quantity; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected = quantity; } string query = "select * from publ_QuoteItems where ParentSurfaceItemId = " + quoteId + " and isActive = 1 " + " and (QuoteItems_QuoteItems_QuoteItemsPartNumber = '" + requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber + "' or QuoteItems_QuoteItems_PartsMasterPartNumber = '" + requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber + "')"; foreach (oQuoteItems upQtItem in xData.GetCustomCollection("recId", typeof(oQuoteItems), query, "itemID DESC", "publ_", false)) { if (qtyLess <= 0) break; if (upQtItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected >= qtyLess) { upQtItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected = upQtItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected - qtyLess; qtyLess = 0; } else { qtyLess = qtyLess - upQtItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected; upQtItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected = 0; } xData.UpdateTyped("recId", upQtItem.recId.ToString(), typeof(oQuoteItems), upQtItem, "publ_", false); } } //if new quantity is more than saved quantity, or quantity is less but no change to Selected quantity, just adjust quantities, no change to quote items else { requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding = quantity - requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired = quantity; } //set part details requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription = description; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsAdditionalDescription = addDescription; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber = masterNumber == "" ? "Not found" : masterNumber; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber = masterNumber == "" ? enquiryPartNumber : partNumber; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG = kg; //if new item create and save, otherwise update if (requestItem.recId == 0) { oSurfaceItem item = new oSurfaceItem(); item.createdBy = userId; item.dateCreated = System.DateTime.Now; item.isActive = true; item.surfaceId = 3055; //Quote Request Item item.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), item); requestItem.isActive = true; requestItem.itemID = item.recId; requestItem.ParentSurfaceItemId = quoteId; requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence = sequence; requestItem.surfaceId = item.surfaceId; requestItem.recId = xData.SaveTyped("recId", typeof(oQuoteRequestItems), requestItem, "publ_", false); } else { xData.UpdateTyped("recId", requestItem.recId.ToString(), typeof(oQuoteRequestItems), requestItem, "publ_", false); } } if (masterNumber != "") { if (masterNumber == partNumber) { if (masterList == "") masterList = "'" + masterNumber.Replace("'", "''") + "'"; else masterList += ",'" + masterNumber.Replace("'", "''") + "'"; } else if (partNumber != "") { if (alternateList == "") alternateList = "'" + partNumber.Replace("'", "''") + "'"; else alternateList += ",'" + partNumber.Replace("'", "''") + "'"; } } } //get all requested items and load child grid //DataTable dtRequestItems = xData.GetChildPublishedSurfaceData(3055,quoteId, userId, 0, "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence ASC", false); //rptQuoteRequestItemsChild0QuoteItems_QuoteRequestItems_QuoteRequestItems.DataSource = dtRequestItems; //rptQuoteRequestItemsChild0QuoteItems_QuoteRequestItems_QuoteRequestItems.DataBind(); //utils.disposeSession("childButtons"); //upSurfaceGrid3054_QuoteItems_QuoteRequestItems_QuoteRequestItems.Update(); //get default margin decimal margin = 0; decimal.TryParse(quote.QuoteItems_QuoteItems_QuoteItemsDefaultMargin, out margin); //get all Inventory Items linked to either Master or Alternate and save as Quote Items bottom grid string inventoryItemsFilter = ""; string supplierItemsFilter = ""; if (masterList != "") { inventoryItemsFilter = " InventoryItemPartDetails_InventoryItemMasterPartDetails_PartsMasterPartNumber in (" + masterList + ") "; } if (alternateList != "") { if (inventoryItemsFilter == "") inventoryItemsFilter = " InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsAlternatePartNumber in (" + alternateList + ") "; else inventoryItemsFilter += " or InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsAlternatePartNumber in (" + alternateList + ") "; } if (masterSupplierList != "") { supplierItemsFilter = " SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber in (" + masterSupplierList + ") "; } //CVH 2019-02-15 Don't need this anymore, that alternate field is not being used //if (alternateSupplierWhere != "") //{ // if (supplierItemsFilter == "") // supplierItemsFilter = alternateSupplierWhere; // else // supplierItemsFilter += " or " + alternateSupplierWhere; //} if (inventoryItemsFilter != "") inventoryItemsFilter = " ( " + inventoryItemsFilter + " ) "; //CVH 2019-02-15 Don't need this anymore //if (supplierItemsFilter != "") // supplierItemsFilter = " ( " + supplierItemsFilter + " ) "; DataTable dtInventoryItems = new DataTable(); if (inventoryItemsFilter != "") { foreach (oSurface inventoryItemsSurface in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", "InventoryItems")) { if (inventoryItemsSurface.isPublished) dtInventoryItems = xData.GetCustomTypedTable("select * from publ_InventoryItems where isActive = 1 and " + inventoryItemsFilter); else dtInventoryItems = xData.GetCustomTypedTable("select * from qt_InventoryItems where " + inventoryItemsFilter); } } //create a Quote Item object for each Inventory item returned if (dtInventoryItems != null) { foreach (DataRow inventoryItem in dtInventoryItems.Rows) { oSurfaceItem item = new oSurfaceItem(); item.createdBy = userId; item.dateCreated = System.DateTime.Now; item.isActive = true; item.surfaceId = 3058; //QuoteItems item.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), item); oQuoteItems quoteItemObj = new oQuoteItems(); quoteItemObj.isActive = true; quoteItemObj.itemID = item.recId; quoteItemObj.ParentSurfaceItemId = quoteId; quoteItemObj.QuoteItems_QuoteItems_InventoryItemsUniqueNumber = inventoryItem["InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsUniqueNumber"].ToString(); quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber = inventoryItem["InventoryItemPartDetails_InventoryItemMasterPartDetails_PartsMasterPartNumber"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsBinLocation = ""; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsBrand = inventoryItem["InventoryItemPartDetails_InventoryItemItemDetails_InventoryItemsBrand"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCategory = inventoryItem["InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsCategory"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCondition = inventoryItem["InventoryItemPartDetails_InventoryItemItemDetails_InventoryItemsCondition"].ToString(); decimal costPrice = 0; decimal.TryParse(inventoryItem["InventoryItemInventoryDetails_InventoryItemInventoryDetails_InventoryItemAverageCost"].ToString(), out costPrice); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCostPrice = costPrice; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsLeadTime = "STOCK"; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsMargin = margin; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsMinorCode = inventoryItem["InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsMinorCode"].ToString(); string partNumber = inventoryItem["InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsAlternatePartNumber"].ToString(); if (partNumber == "") partNumber = quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber = partNumber; int qtyAvailable = 0; int.TryParse(inventoryItem["InventoryItemInventoryDetails_InventoryItemInventoryDetails_InventoryItemTotalAvailable"].ToString(), out qtyAvailable); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsQuantityAvailable = qtyAvailable; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsQuantitySelected = 0; decimal divideBy = 1m - (margin / 100m); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSellingPrice = (divideBy == 0) ? 0 : quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCostPrice / divideBy; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSubcategory = inventoryItem["InventoryItemPartDetails_InventoryItemMasterPartDetails_InventoryItemsSubcategory"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplier = "CEP"; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplierCurrency = "ZAR"; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsWarehouseLocation = ""; //get first matched enquiry item string query = "select * from publ_QuoteRequestItems where ParentSurfaceItemId = " + quoteId + " and isActive = 1 " + " and (QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber = '" + quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber + "' or QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber = '" + quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber + "')"; foreach (oQuoteRequestItems linkedReqItem in xData.GetCustomCollection("recId", typeof(oQuoteRequestItems), query, "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence", "publ_", false)) { quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSequenceNumber = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsWeightKG = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsDescription = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription; //CVH 2019-01-08 Need to break out of loop after first match has been processed, otherwise it uses last sequence number, not first. break; } /* CVH 2019-01-22 Set supplier sequence: 0 - Inventory Items (not used) 1 - CEP Inventory pricelist 2 - CAT pricelist 3 - Other pricelists in alpha order 4 - Buyout supplier lists in alpha order */ quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber = 0; quoteItemObj.surfaceId = item.surfaceId; quoteItemObj.recId = xData.SaveTyped("recId", typeof(oQuoteItems), quoteItemObj, "publ_", false); } } DataTable dtSupplierParts = new DataTable(); if (supplierItemsFilter != "") { foreach (oSurface supplierPartsSurface in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", "SupplierPricelistParts")) { //CVH 2019-02-15 Optimizing query #region backup ///*CVH 2019-01-24 Supplier pricelists have same item repeating as CEP Master number and CEP Alternate Number, but same item, just newer price. // * In that scenario, only use latest item with highest price. // * Link to CEP Alternates table to get list of master and alternate numbers, and if supplier item is alternate CEP number, // * do the grouping by Supplier, Item number, Brand, Condition and only select the highest price item // Without the UNION it takes 4 seconds, with UNNION 8 seconds */ ////CVH 2018-11-16 Only select from CURRENT lists ////if (!supplierPartsSurface.isPublished) //// dtSupplierParts = xData.GetCustomTypedTable("select * from qt_SupplierPricelistParts p left outer join qt_SupplierPricelists s on s.itemID = p.ParentSurfaceItemId where " + supplierItemsFilter + " and s.SupplierPricelists_SupplierPricelistDetails_SupplierPricelistStatus = 'Current' "); ////else //// dtSupplierParts = xData.GetCustomTypedTable("select * from publ_SupplierPricelistParts p left outer join qt_SupplierPricelists s on s.itemID = p.ParentSurfaceItemId where " + supplierItemsFilter + " and p.isActive = 1 and s.SupplierPricelists_SupplierPricelistDetails_SupplierPricelistStatus = 'Current'"); //StringBuilder pricelistQuery = new StringBuilder(); //pricelistQuery.AppendLine("select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice "); //pricelistQuery.AppendLine("from ( "); //pricelistQuery.AppendLine(" select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, "); //pricelistQuery.AppendLine(" ISNULL(m.PartsAlternates_PartsAlternates_PartsMasterPartNumber, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber) as alternateMaster, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice, "); //pricelistQuery.AppendLine(" ROW_NUMBER() OVER (PARTITION BY "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); //pricelistQuery.AppendLine(" ISNULL(m.PartsAlternates_PartsAlternates_PartsMasterPartNumber, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber), "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); //pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability "); //pricelistQuery.AppendLine(" ORDER BY SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice DESC) priceRank "); //pricelistQuery.AppendLine(" from publ_SupplierPricelistParts p "); //pricelistQuery.AppendLine(" left outer join qt_SupplierPricelists s on s.itemID = p.ParentSurfaceItemId "); //pricelistQuery.AppendLine(" left outer join qt_PartsAlternates m on m.PartsAlternates_PartsAlternates_AlternatePartNumber = p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber "); //pricelistQuery.AppendLine(" where " + supplierItemsFilter + " "); //pricelistQuery.AppendLine(" and p.isActive = 1 and s.SupplierPricelists_SupplierPricelistDetails_SupplierPricelistStatus = 'Current' "); ////pricelistQuery.AppendLine(" and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName = 'CAT' "); //pricelistQuery.AppendLine(" and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName IN ('CAT', 'SURPLUS WAREHOUSE') "); //pricelistQuery.AppendLine(") t "); //pricelistQuery.AppendLine("where t.priceRank = 1 "); //pricelistQuery.AppendLine("union "); //pricelistQuery.AppendLine("select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, "); //pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice "); //pricelistQuery.AppendLine("from publ_SupplierPricelistParts p "); //pricelistQuery.AppendLine("left outer join qt_SupplierPricelists s on s.itemID = p.ParentSurfaceItemId "); //pricelistQuery.AppendLine("where " + supplierItemsFilter + " "); //pricelistQuery.AppendLine("and p.isActive = 1 and s.SupplierPricelists_SupplierPricelistDetails_SupplierPricelistStatus = 'Current' "); //pricelistQuery.AppendLine("and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName NOT IN ('CAT', 'SURPLUS WAREHOUSE') "); #endregion StringBuilder pricelistQuery = new StringBuilder(); pricelistQuery.AppendLine("select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice "); pricelistQuery.AppendLine("from ( "); pricelistQuery.AppendLine(" select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, "); pricelistQuery.AppendLine(" ISNULL(m.PartsAlternates_PartsAlternates_PartsMasterPartNumber, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber) as alternateMaster, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice, "); pricelistQuery.AppendLine(" ROW_NUMBER() OVER (PARTITION BY "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); pricelistQuery.AppendLine(" ISNULL(m.PartsAlternates_PartsAlternates_PartsMasterPartNumber, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber), "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote, "); pricelistQuery.AppendLine(" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability "); pricelistQuery.AppendLine(" ORDER BY SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice DESC) priceRank "); pricelistQuery.AppendLine(" from publ_SupplierPricelistParts p "); pricelistQuery.AppendLine(" left outer join publ_PartsAlternates m on m.PartsAlternates_PartsAlternates_AlternatePartNumber = p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber "); pricelistQuery.AppendLine(" where p.isActive = 1 and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = 'Current' "); pricelistQuery.AppendLine(" and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName IN ('CAT', 'SURPLUS WAREHOUSE') "); pricelistQuery.AppendLine(" and " + supplierItemsFilter + " "); pricelistQuery.AppendLine(") t "); pricelistQuery.AppendLine("where t.priceRank = 1 "); pricelistQuery.AppendLine("union "); pricelistQuery.AppendLine("select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote, "); pricelistQuery.AppendLine("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice "); pricelistQuery.AppendLine("from publ_SupplierPricelistParts p "); pricelistQuery.AppendLine("where " + supplierItemsFilter + " "); pricelistQuery.AppendLine("and p.isActive = 1 and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = 'Current' "); pricelistQuery.AppendLine("and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName NOT IN ('CAT', 'SURPLUS WAREHOUSE') "); //get table dtSupplierParts = xData.GetCustomTypedTable(pricelistQuery.ToString()); } } //create a Quote Item object for each Supplier part returned if (dtSupplierParts != null) { foreach (DataRow supplierItem in dtSupplierParts.Rows) { //only add new quote item if one hasn't already been saved bool addNew = true; string partNumberCheck = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers"].ToString(); if (partNumberCheck == "") partNumberCheck = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString(); //CVH 2019-01-25 Leave Master number out of the check, only check on part number, supplier, condition and brand, otherwise we get duplicates foreach (oQuoteItems existQtItem in xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteItems), //"isActive,ParentSurfaceItemId,QuoteItems_QuoteItems_PartsMasterPartNumber,QuoteItems_QuoteItems_QuoteItemsPartNumber,QuoteItems_QuoteItems_QuoteItemsBrand,QuoteItems_QuoteItems_QuoteItemsCondition,QuoteItems_QuoteItems_QuoteItemsSupplier", //"1," + base.SurfaceAppItemId + "," + supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString() + "," + partNumberCheck + "," + //supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand"].ToString() + "," + supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition"].ToString() + "," + //supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName"].ToString(), //"", "publ_", false "isActive,ParentSurfaceItemId,QuoteItems_QuoteItems_QuoteItemsPartNumber,QuoteItems_QuoteItems_QuoteItemsBrand,QuoteItems_QuoteItems_QuoteItemsCondition,QuoteItems_QuoteItems_QuoteItemsSupplier", "1," + quoteId + "," + partNumberCheck + "," + supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand"].ToString() + "," + supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition"].ToString() + "," + supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName"].ToString(), "", "publ_", false )) { addNew = false; } if (addNew) { oSurfaceItem item = new oSurfaceItem(); item.createdBy = userId; item.dateCreated = System.DateTime.Now; item.isActive = true; item.surfaceId = 3058; //QuoteItems item.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), item); oQuoteItems quoteItemObj = new oQuoteItems(); quoteItemObj.isActive = true; quoteItemObj.itemID = item.recId; quoteItemObj.ParentSurfaceItemId = quoteId; quoteItemObj.QuoteItems_QuoteItems_InventoryItemsUniqueNumber = ""; //CVH 2019-01-24 This needs to be the CEP Master number if there is one (get from Request Item in loop below) //quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString();) quoteItemObj.QuoteItems_QuoteItems_QuoteItemsBinLocation = ""; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsBrand = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCategory = ""; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCondition = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition"].ToString(); decimal costPrice = 0; decimal.TryParse(supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice"].ToString(), out costPrice); //CVH 2018-11-19 If currency is not blank or ZAR, calculate using exchange rate string currency = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency"].ToString(); if (currency != "" && currency != "ZAR") { DataTable dtExRate = xData.GetSurfaceQueryDataPaged(3060, 0, 1, "", "", userId, 0, "ExchangeRateManagement_ExchangeRateManagement_ForexCurrency = '" + currency.Replace("'", "''") + "'", true, false); if (dtExRate != null & dtExRate.Rows.Count > 0) { decimal exRate = 0; if (decimal.TryParse(dtExRate.Rows[0]["ExchangeRateManagement_ExchangeRateManagement_BuyExchangeRate"].ToString(), out exRate)) { costPrice = costPrice * exRate; } } } quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCostPrice = costPrice; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsLeadTime = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsMargin = margin; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsMinorCode = ""; //CVH 2019-01-24 Always use the supplier partnumber, the master number will be the CEP master number if there is one //string partNumber = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers"].ToString(); //if (partNumber == "") // partNumber = quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber; //quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber = partNumber; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString(); ; int qtyAvailable = 0; int.TryParse(supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability"].ToString(), out qtyAvailable); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsQuantityAvailable = qtyAvailable; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsQuantitySelected = 0; decimal divideBy = 1m - (margin / 100m); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSellingPrice = divideBy == 0 ? 0 : quoteItemObj.QuoteItems_QuoteItems_QuoteItemsCostPrice / divideBy; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSubcategory = ""; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplier = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsWarehouseLocation = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote"].ToString(); quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplierCurrency = currency; //CVH 2019-01-24 Changed the master and item numbers, use the original in this query that was working //quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber = partNumber; //get first matched enquiry item //string query = "select * from publ_QuoteRequestItems where ParentSurfaceItemId = " + this.SurfaceAppItemId + " and isActive = 1 " + // " and (QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber = '" + quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber + "' or QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber = '" + quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber + "')"; string requestPartNumber = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers"].ToString(); if (requestPartNumber == "") requestPartNumber = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString(); string query = "select * from publ_QuoteRequestItems where ParentSurfaceItemId = " + quoteId + " and isActive = 1 " + " and (QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber = '" + quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber + "' or QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber = '" + requestPartNumber + "')"; foreach (oQuoteRequestItems linkedReqItem in xData.GetCustomCollection("recId", typeof(oQuoteRequestItems), query, "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence", "publ_", false)) { quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSequenceNumber = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsWeightKG = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG; quoteItemObj.QuoteItems_QuoteItems_QuoteItemsDescription = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription; //CVH 2019-01-24 Use Request Master number as Quote Item master number quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber = linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber; //CVH 2019-01-08 Need to break out of loop after first match has been processed, otherwise it uses last sequence number, not first. break; } /* CVH 2019-01-22 Set supplier sequence: 0 - Inventory Items (not used) 1 - CEP Inventory pricelist 2 - CAT pricelist 3 - Other pricelists in alpha order 4 - Buyout supplier lists in alpha order */ quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber = quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplier == "CEP Inventory" ? 1 : quoteItemObj.QuoteItems_QuoteItems_QuoteItemsSupplier == "CAT" ? 2 : 3; quoteItemObj.surfaceId = item.surfaceId; quoteItemObj.recId = xData.SaveTyped("recId", typeof(oQuoteItems), quoteItemObj, "publ_", false); } } //CVH 2019-03-02 Adding this here instead *********************************************************************************************** //add buyout item #region Buyout Item //CVH 2019-03-02 Get distinct list of suppliers from quote items string quoteItemSuppliersQuery = "select MAX(QuoteItems_QuoteItems_QuoteItemsSupplierCurrency) as QuoteItems_QuoteItems_QuoteItemsSupplierCurrency, QuoteItems_QuoteItems_QuoteItemsSupplier " + " from publ_QuoteItems where isActive = 1 and QuoteItems_QuoteItems_QuoteItemsSupplier <> 'CEP Inventory' and ParentSurfaceItemId = " + quoteId + " " + " group by QuoteItems_QuoteItems_QuoteItemsSupplier "; DataTable dtQuoteItemSuppliers = xData.GetCustomTypedTable(quoteItemSuppliersQuery); if (dtQuoteItemSuppliers != null && dtQuoteItemSuppliers.Rows.Count > 0) { foreach (DataRow qtItemSupplier in dtQuoteItemSuppliers.Rows) { string supplierName = qtItemSupplier["QuoteItems_QuoteItems_QuoteItemsSupplier"].ToString(); string currency = qtItemSupplier["QuoteItems_QuoteItems_QuoteItemsSupplierCurrency"].ToString(); //CVH 2018-12-21 Supplier must be unique per quote //DataTable dtExistSupplier = xData.GetCustomTypedTable("select * from publ_QuoteBuyoutSuppliers where QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName = '" + supplierName.Replace("'", "''") + "' and ParentSurfaceItemId = " + base.ParentSurfaceItemId + " and isActive = 1"); //DataTable dtExistSupplier = xData.GetCustomTypedTable("select * from publ_QuoteBuyoutSuppliers where QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName = '" + supplierName.Replace("'", "''") + "' and ParentSurfaceItemId = " + base.SurfaceAppItemId + " and isActive = 1"); //if (dtExistSupplier != null && dtExistSupplier.Rows.Count > 0) //{ // break; //} //if (supplierName.Equals("CEP Inventory")) //{ // break; //} //get contact from supplier item - but first the itemid from name string supplierQuery = "SELECT * FROM dbo.qt_Suppliers WHERE SupplierDetails_SupplierDetails_SupplierTradingName = '" + supplierName.Replace("'", "''") + "'"; string contactPerson = "", supplierEmail = "", supplierPhone = "", supplierVatRate = "", supplierCurrency = "", supplierDefaultDiscount = "", supplierDefaultLeadtime = ""; foreach (oSuppliers supplier in xData.GetCustomCollection("recId", typeof(oSuppliers), supplierQuery, "", "publ_", false)) { foreach (DataRow row in xData.GetChildSurfaceQueryData(3038, supplier.itemID, userId).Rows) { if (contactPerson == "") contactPerson = row["ContactDetails_ContactDetails_ContactName"].ToString() + " " + row["ContactDetails_ContactDetails_ContactSurname"].ToString(); else contactPerson += ", " + row["ContactDetails_ContactDetails_ContactName"].ToString() + " " + row["ContactDetails_ContactDetails_ContactSurname"].ToString(); } supplierEmail = supplier.SupplierDetails_SupplierDetails_SupplierPrimaryEmail; supplierPhone = supplier.SupplierDetails_SupplierDetails_SupplierTelephone; supplierVatRate = supplier.SupplierFinancialandCreditDetails_SupplierFinancialDetails_SupplierVATRate; supplierCurrency = supplier.SupplierFinancialandCreditDetails_SupplierFinancialDetails_SupplierCurrency; supplierDefaultDiscount = supplier.SupplierFinancialandCreditDetails_SupplierFinancialDetails_SupplierDefaultDiscount; supplierDefaultLeadtime = supplier.SupplierDetails_SupplierDetails_SupplierDefaultLeadtime; } decimal eRate = 0; string currencySymbol = ""; if (currency != "" && currency != "ZAR") { DataTable dtExRate = xData.GetSurfaceQueryDataPaged(3060, 0, 1, "", "", userId, 0, "ExchangeRateManagement_ExchangeRateManagement_ForexCurrency = '" + currency.Replace("'", "''") + "'", true, false); if (dtExRate != null & dtExRate.Rows.Count > 0) decimal.TryParse(dtExRate.Rows[0]["ExchangeRateManagement_ExchangeRateManagement_BuyExchangeRate"].ToString(), out eRate); foreach (DataRow row in xData.GetSurfaceQueryDataPaged(3047, 0, 1, "", "", userId, 0, "Currencies_Currencies_CurrencyCode = '" + currency + "'", true, false).Rows) { currencySymbol = row["Currencies_Currencies_CurrencySymbol"].ToString(); } } oQuoteBuyoutSuppliers suppObj = new oQuoteBuyoutSuppliers(); foreach (oQuoteBuyoutSuppliers qbs in xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteBuyoutSuppliers), "QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName,ParentSurfaceItemId", supplierName + "," + quoteId, "", "publ_")) { suppObj = qbs; } if (suppObj.recId == 0) { oSurfaceItem suppItem = new oSurfaceItem(); suppItem.createdBy = userId; suppItem.dateCreated = System.DateTime.Now; suppItem.isActive = true; suppItem.isDeleted = false; suppItem.surfaceId = 3061;//QuoteBuyoutSuppliers suppItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), suppItem); //oQuoteBuyoutSuppliers suppObj = new oQuoteBuyoutSuppliers(); suppObj.isActive = true; suppObj.itemID = suppItem.recId; suppObj.ParentSurfaceItemId = quoteId; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName = supplierName; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierTelephone = supplierPhone; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierEmail = supplierEmail; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierContactPerson = contactPerson; decimal vatRate = 0; decimal.TryParse(supplierVatRate, out vatRate); suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierVATRate = vatRate; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierCurrency = supplierCurrency; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierExchangeRate = eRate; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDefaultDiscount = supplierDefaultDiscount; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDefaultLeadtime = supplierDefaultLeadtime; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutQuoteType = 1137; //selected decimal freight = 0; foreach (oFreightManagement freightManagement in xData.GetTypedByCriteriaSpecific("recId", typeof(oFreightManagement), "FreightManagement_FreightManagement_SupplierTradingName", supplierName, "", "qt_")) { freight = Convert.ToDecimal(freightManagement.FreightManagement_FreightManagement_Freight); } suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierFreight = freight; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDuty = 0; suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierStatus = 1131;//Quote Requested suppObj.surfaceId = suppItem.surfaceId; suppObj.recId = xData.SaveTyped("recId", typeof(oQuoteBuyoutSuppliers), suppObj, "publ_", false); //base.SurfaceAppItem = suppItem; //base.SurfaceAppItemId = suppItem.recId; } //load supplier quote items, if existing ArrayList quoteItems = xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteItems), "isActive,ParentSurfaceItemId,QuoteItems_QuoteItems_QuoteItemsSupplier", "1," + quoteId + "," + supplierName, "", "publ_", false); if (quoteItems != null && quoteItems.Count > 0) { foreach (oQuoteItems quoteItem in quoteItems) { oQuoteBuyoutItems buyObj = new oQuoteBuyoutItems(); foreach (oQuoteBuyoutItems qbi in xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteBuyoutItems), "QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber,ParentSurfaceItemId", quoteItem.QuoteItems_QuoteItems_PartsMasterPartNumber + "," + suppObj.itemID, "", "publ_")) { buyObj = qbi; } if (buyObj.recId == 0) { oSurfaceItem buyItem = new oSurfaceItem(); buyItem.createdBy = userId; buyItem.dateCreated = System.DateTime.Now; buyItem.isActive = true; buyItem.isDeleted = false; buyItem.surfaceId = 3059;//Quote Buyout Items buyItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), buyItem); //oQuoteBuyoutItems buyObj = new oQuoteBuyoutItems(); buyObj.isActive = true; buyObj.itemID = buyItem.recId; buyObj.ParentSurfaceItemId = suppObj.itemID; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber = quoteItem.QuoteItems_QuoteItems_PartsMasterPartNumber; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsBrand = quoteItem.QuoteItems_QuoteItems_QuoteItemsBrand; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCondition = quoteItem.QuoteItems_QuoteItems_QuoteItemsCondition; decimal zar = quoteItem.QuoteItems_QuoteItems_QuoteItemsCostPrice; decimal forex = 0; if (eRate != 0) { forex = zar / eRate; } buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCostPriceZAR = zar; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCostPriceForex = forex; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDescription = quoteItem.QuoteItems_QuoteItems_QuoteItemsDescription; decimal discount = 0; decimal.TryParse(supplierDefaultDiscount, out discount); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDiscount = discount; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDutyCost = 0; decimal freightCost = (suppObj.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierFreight / 100) * zar; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsFreightCost = freightCost; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsLeadtime = supplierDefaultLeadtime; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsPartNumber = quoteItem.QuoteItems_QuoteItems_QuoteItemsPartNumber; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityAvailable = quoteItem.QuoteItems_QuoteItems_QuoteItemsQuantityAvailable; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityRequired = quoteItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsTotalCost = zar + freightCost - discount; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsWeightKG = quoteItem.QuoteItems_QuoteItems_QuoteItemsWeightKG; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSequence = quoteItem.QuoteItems_QuoteItems_QuoteItemsSequenceNumber; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSelected = true; buyObj.surfaceId = buyItem.surfaceId; buyObj.recId = xData.SaveTyped("recId", typeof(oQuoteBuyoutItems), buyObj, "publ_", false); //CVH 2019-03-20 This updates the quote item with the freight and discount from the pricelist, but we also need to recalc the selling price quoteItem.QuoteItems_QuoteItems_QuoteItemsCostPrice = zar + freightCost - discount; decimal divideBy = 1m - (margin / 100m); quoteItem.QuoteItems_QuoteItems_QuoteItemsSellingPrice = divideBy == 0 ? 0 : quoteItem.QuoteItems_QuoteItems_QuoteItemsCostPrice / divideBy; xData.UpdateTyped("recId", quoteItem.recId.ToString(), typeof(oQuoteItems), quoteItem, "publ_"); } } } else { //string query = "select QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber, " + // "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription, SUM(QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired) as QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired " + // "from publ_QuoteRequestItems where isActive = 1 and ParentSurfaceItemId = " + base.ParentSurfaceItemId + " " + // "group by QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber, " + // "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " + // "order by QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber"; //CVH 2019-01-24 Do Row number with order by master number desc, sequence number asc. Otherwise it uses the wrong sequence number on the buyout item string buyoutQuery = ";WITH cte AS " + "( " + " SELECT *, " + " ROW_NUMBER() " + " OVER(PARTITION BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " + " ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber DESC, QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence ASC) AS rn " + " FROM publ_QuoteRequestItems " + " WHERE isActive = 1 AND ParentSurfaceItemId = " + quoteId + " " + ") " + "SELECT QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " + " , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber " + " , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " + " , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG " + " , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence " + " , SUM(QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired) AS QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired " + "FROM cte " + "WHERE rn = 1 " + "GROUP BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " + " ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber " + " ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " + " ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG " + " ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence " + "ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber "; DataTable dtBuyoutRequestItems = xData.GetCustomTypedTable(buyoutQuery); //load all requested items foreach (DataRow reqItem in dtBuyoutRequestItems.Rows) { oQuoteBuyoutItems buyObj = new oQuoteBuyoutItems(); foreach (oQuoteBuyoutItems qbi in xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteBuyoutItems), "QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber,ParentSurfaceItemId", reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber"].ToString() + "," + suppObj.itemID, "", "publ_")) { buyObj = qbi; } if (buyObj.recId == 0) { oSurfaceItem buyItem = new oSurfaceItem(); buyItem.createdBy = userId; buyItem.dateCreated = System.DateTime.Now; buyItem.isActive = true; buyItem.isDeleted = false; buyItem.surfaceId = 3059;//Quote Buyout Items buyItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), buyItem); //oQuoteBuyoutItems buyObj = new oQuoteBuyoutItems(); buyObj.isActive = true; buyObj.itemID = buyItem.recId; buyObj.ParentSurfaceItemId = suppObj.itemID; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber"].ToString(); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsBrand = ""; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCondition = ""; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCostPriceForex = 0; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCostPriceZAR = 0; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDescription = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription"].ToString(); decimal discount = 0; decimal.TryParse(supplierDefaultDiscount, out discount); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDiscount = discount; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDutyCost = 0; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsFreightCost = 0; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsLeadtime = supplierDefaultLeadtime; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsPartNumber = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber"].ToString(); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityAvailable = 0; int qty = 0; int.TryParse(reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired"].ToString(), out qty); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityRequired = qty; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsTotalCost = 0; decimal kg = 0; decimal.TryParse(reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG"].ToString(), out kg); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsWeightKG = kg; int sequence = 0; int.TryParse(reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence"].ToString(), out sequence); buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSequence = sequence; buyObj.QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSelected = true; buyObj.surfaceId = buyItem.surfaceId; buyObj.recId = xData.SaveTyped("recId", typeof(oQuoteBuyoutItems), buyObj, "publ_", false); } } } } } //BindBuyoutItems(); #endregion } //BindQuoteItems(); //ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "changeTabQuoteItems", "setCurrentTabNoWizard('.fQuoteItemsHidden255', 'fsurfaceTabsQuotes');", true); } catch (Exception ex) { BusinessUtils.HandleException(ex, userId: userId); throw; } } ```