using CAPI.Custom.Data;
using CAPI.Custom.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Diagnostics;
using NLog;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;
using framework_business;
using CAPI.Custom.Repositories;
namespace CAPI.Custom.Services
{
public class QuoteProcessor
{
private readonly ILogger logger;
private readonly QuotesContext db;
private readonly QuoteRepository repository = new QuoteRepository();
public QuoteProcessor(QuotesContext dbContext = null, ILogger logger = null)
{
this.logger = logger ?? LogManager.CreateNullLogger();
this.db = dbContext ?? QuotesContext.Create();
}
public void ProcessQuote(int quoteId)
{
Quotes_ProcessEnquiryItems4(quoteId);
}
#region Stored Procedures and Queries
///
/// Gets EnquiryPartNumbers for quote where quantity > 0
///
///
///
public IQueryable GetQuoteEnquiryPartNumbers(int quoteId)
{
return db.QuoteEnquiryItems
.Where(p =>
p.isActive.Value
&& p.ParentSurfaceItemId == quoteId
&& p.Quantity > 0
).OrderBy(p => p.Sequence)
.Select(p => p.PartNumber)
.Distinct();
}
public List GetParts(IEnumerable partNumbers, string delimeter = "|")
{
var partNumbersParamValue = string.Join(delimeter, partNumbers.ToArray());
var query = @"sp_CAPI1_GetPartsAndAlternates @partNumbers, @delimeter";
var result = db.Database.SqlQuery(query,
new SqlParameter("@partNumbers", partNumbersParamValue),
new SqlParameter("@delimeter", delimeter))
.ToList();
return result;
}
public List GetInventoryItems(IEnumerable partNumbers, string delimeter = "|")
{
var partNumbersParamValue = string.Join(delimeter, partNumbers.ToArray());
var query = @"sp_CAPI1_GetInventoryItems @partNumbers, @delimeter";
var result = db.Database.SqlQuery(query,
new SqlParameter("@partNumbers", partNumbersParamValue),
new SqlParameter("@delimeter", delimeter))
.ToList();
return result;
}
public List GetSupplierCATListParts(IEnumerable partNumbers, string delimeter = "|")
{
var partNumbersParamValue = string.Join(delimeter, partNumbers.ToArray());
var query = @"sp_CAPI1_GetSupplierCATListParts @partNumbers, @delimeter";
var result = db.Database.SqlQuery(query,
new SqlParameter("@partNumbers", partNumbersParamValue),
new SqlParameter("@delimeter", delimeter))
.ToList();
return result;
}
public List GetSupplierPriceListParts(IEnumerable partNumbers, string delimeter = "|")
{
var partNumbersParamValue = string.Join(delimeter, partNumbers.ToArray());
var query = @"sp_CAPI1_GetSupplierPricelistParts @partNumbers, @delimeter";
var result = db.Database.SqlQuery(query,
new SqlParameter("@partNumbers", partNumbersParamValue),
new SqlParameter("@delimeter", delimeter))
.ToList();
return result;
}
///
/// CVH 2019-03-02 Get distinct list of suppliers from quote items
///
///
///
public List GetQuoteItemsSuppliers(int quoteId)
{
//sp_CAPI1_GetQuoteItemsSuppliers
var query = @"sp_CAPI1_GetQuoteItemsSuppliers @quoteId";
var result = db.Database.SqlQuery(query,
new SqlParameter("@quoteId", quoteId))
.ToList();
return result;
}
public List GetBuyoutRequestItems(int quoteId)
{
var _buyoutRequestItems = new List();
// 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
var query = $@"
;WITH cte AS
(
SELECT [itemID]
,[isActive]
,[surfaceId]
,[ParentSurfaceItemId]
,[Sequence] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence]
,[PartNumber] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber]
,[MasterNumber] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber]
,[Required] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired]
,[Description] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription]
,[Selected] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected]
,[Outstanding] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding]
,[recId]
,[AdditionalDescription] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsAdditionalDescription]
,[WeightKG] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG]
,[RowNum] = ROW_NUMBER() OVER(
PARTITION BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber
ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber DESC
,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence ASC)
FROM publ_QuoteRequestItems
WHERE [isActive] = 1
AND [ParentSurfaceItemId] = {quoteId}
)
SELECT [MasterNumber]
, [PartNumber]
, [Description]
, [WeightKG]
, [Sequence]
, SUM([Required]) AS [Required]
FROM cte
WHERE [RowNum] = 1
GROUP BY [MasterNumber]
,[PartNumber]
,[Description]
,[WeightKG]
,[Sequence]
ORDER BY [MasterNumber]";
_buyoutRequestItems = db.Database.SqlQuery(query).ToList();
return _buyoutRequestItems;
}
#endregion
private void Quotes_ProcessEnquiryItems4(int quoteId /*quote.itemID*/)
{
var userId = db.User.Id;
try
{
db.Configuration.AutoDetectChangesEnabled = false;
var quote = repository.GetByItemId(quoteId);// db.Quotes.FirstOrDefault(p => p.itemID == quoteId);
//build string of master items and alternate numbers to find in InventoryItems for bottom Quote Items child grid
HashSet masterListHash = new HashSet();
HashSet masterSupplierListHash = new HashSet();
HashSet alternateListHash = new HashSet();
//get all enquiry items with part number and quantity linked to item
var dtEnquiryItems = repository.GetEnquiryItems(quoteId).ToList();
var requestItems = repository.GetRequestItems(quoteId).ToList();
foreach (var enquiryItem in dtEnquiryItems)
{
string enquiryPartNumber = enquiryItem.PartNumber.TrimString();
int sequence = enquiryItem.Sequence.GetValueOrDefault();
int quantity = enquiryItem.Quantity.GetValueOrDefault();
//find enquiry part number in PartsMaster.PartsMaster_PartsMasterDetails_PartsMasterPartNumber, or PartsAlternates.PartsAlternates_PartsAlternates_AlternatePartNumber
string masterNumber = string.Empty;
string partNumber = string.Empty;
string description = string.Empty;
string addDescription = string.Empty;
decimal kg = 0m;
if (enquiryPartNumber.IsNotEmpty())
{
var dtMasterMatch = (from p in db.PartsMasters
where p.isActive.Value
&& p.PartNumber == enquiryPartNumber
select p)
.FirstOrDefault();
var dtAlternatesMatch = (from p in db.PartsAlternates
where p.isActive.Value
&& p.ParentSurfaceItemId != 0
&& p.AlternateNumber == enquiryPartNumber
//&& p.PartsAlternates_PartsAlternates_PartsMasterPartNumber == enquiryPartNumber
select new
{
p.AlternateNumber,
p.PartNumber
}).ToList();
if (dtMasterMatch.IsNotNull()) // master match found
{
if (masterNumber.IsEmpty()) masterNumber = dtMasterMatch.PartNumber;
if (partNumber.IsEmpty()) partNumber = masterNumber;
if (description.IsEmpty()) description = dtMasterMatch.Description;
if (addDescription.IsEmpty()) addDescription = dtMasterMatch.AdditionalDescription;
kg = dtMasterMatch.WeightKG.GetValueOrDefault();
masterSupplierListHash.Add(masterNumber);
masterSupplierListHash.Add(partNumber);
}
foreach (var match in dtAlternatesMatch)
{
if (partNumber.IsEmpty())
{
partNumber = match.AlternateNumber;
}
if (masterNumber.IsEmpty())
{
masterNumber = match.PartNumber;
}
masterSupplierListHash.Add(masterNumber);
masterSupplierListHash.Add(partNumber);
alternateListHash.Add(partNumber);
var dtAlternateMasters = db.PartsMasters
.Where(p => p.isActive.Value
&& p.PartNumber == masterNumber)
.ToList();
foreach (var alternateMaster in dtAlternateMasters)
{
if (description.IsEmpty())
{
description = alternateMaster.Description;
}
if (addDescription.IsEmpty())
{
addDescription = alternateMaster.AdditionalDescription;
}
if (kg == 0m)
{
kg = alternateMaster.WeightKG.GetValueOrDefault();
}
var moreAlternates = db.PartsAlternates
.Where(p =>
p.isActive.Value
&& p.PartNumber == alternateMaster.PartNumber
)
.Select(p => new
{
p.AlternateNumber,
p.PartNumber
})
.ToList();
moreAlternates.ForEach(p =>
{
if (partNumber.IsEmpty()) partNumber = p.AlternateNumber;
if (masterNumber.IsEmpty()) masterNumber = p.PartNumber;
masterSupplierListHash.Add(p.AlternateNumber);
masterSupplierListHash.Add(p.PartNumber);
});
}
}
if (dtMasterMatch.IsNull() && !dtAlternatesMatch.Any())
{
var dtCATMatch = (from p in db.SupplierPricelistParts
where p.isActive.Value
&& "CAT" == p.SupplierName
&& "Current" == p.PricelistStatus
&& enquiryPartNumber == p.PartNumber
select p).FirstOrDefault();
if (dtCATMatch.IsNotNull())
{
partNumber = enquiryPartNumber;
masterNumber = dtCATMatch.PartNumber;
description = dtCATMatch.Description;
addDescription = dtCATMatch.AdditionalDescription;
kg = dtCATMatch.WeightKG.GetValueOrDefault();
masterSupplierListHash.Add(masterNumber);
masterSupplierListHash.Add(partNumber);
//CVH 2019-01-07 Create a new Part Master record based on CAT item details
var partsMaster = BusinessUtils.Create(new PartsMaster
{
PartsMaster_PartsMasterAlternates_AlternatePartNumbers = dtCATMatch.AlternateNumbers,
AdditionalDescription = dtCATMatch.AdditionalDescription,
Description = dtCATMatch.Description,
ItemVetted = YesNo.No,
PartNumber = dtCATMatch.PartNumber,
MasterNumber = dtCATMatch.PartNumber,
WeightKG = dtCATMatch.WeightKG.GetValueOrDefault(),
WeightLBS = dtCATMatch.WeightLBS.GetValueOrDefault(),
// CEPNumber
CEPNumber = BusinessUtils.GetNextCEPNumber(),
});
var partsAlternate = BusinessUtils.Create(new PartsAlternate
{
CEPNumber = partsMaster.CEPNumber,
AlternateNumber = partsMaster.MasterNumber,
PartNumber = partsMaster.MasterNumber,
ParentSurfaceItemId = partsMaster.itemID
});
}
else
{
partNumber = enquiryPartNumber;
masterNumber = enquiryPartNumber;
description = "***Description Required***";
addDescription = "";
masterSupplierListHash.Add(masterNumber);
masterSupplierListHash.Add(partNumber);
//CVH 2019-01-07 No match in CAT list either, Create a new Part Master record based on Enquiry item details
var partsMaster = BusinessUtils.Create(new PartsMaster
{
CEPNumber = BusinessUtils.GetNextCEPNumber(),
Description = description,
ItemVetted = 1074, // No
MasterNumber = partNumber,
PartNumber = partNumber
});
var partsAlternate = BusinessUtils.Create(new PartsAlternate
{
CEPNumber = partsMaster.CEPNumber,
AlternateNumber = partsMaster.MasterNumber,
PartNumber = partsMaster.MasterNumber,
ParentSurfaceItemId = partsMaster.itemID
});
}
}
}
//save a request item for each enquiry item with a quantity
var requestItem = requestItems.FirstOrDefault(p => p.Sequence == sequence)
?? new QuoteRequestItem();
// 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 (ValidateUnchanged(requestItem, quantity, (masterNumber.IsEmpty() ? enquiryPartNumber : partNumber))
|| ValidateNoEnquiryOrQuantity(requestItem, enquiryPartNumber, quantity))
{
//delete item if needed
if (!DeleteIfInvalid(requestItem, enquiryPartNumber, quantity))
{
//update part details
requestItem.Description = description;
requestItem.AdditionalDescription = addDescription;
requestItem.MasterNumber = masterNumber.IsEmpty() ? "Not found" : masterNumber;
requestItem.PartNumber = masterNumber.IsEmpty() ? enquiryPartNumber : partNumber;
requestItem.WeightKG = kg;
db.Update(requestItem);
}
continue;
}
else
{
// set quantities
// if saved quantity is 0, it means this is new/add
if (requestItem.QuantityRequired.GetValueOrDefault() == 0)
{
requestItem.QuantityOutstanding = quantity;
requestItem.QuantityRequired = quantity;
requestItem.QuantitySelected = 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.QuantitySelected.GetValueOrDefault() > 0
&& requestItem.PartNumber
!= (masterNumber.IsEmpty() ? enquiryPartNumber : partNumber))
|| quantity < requestItem.QuantitySelected.GetValueOrDefault())
{
int qtyLess = 0;
//part number has changed
if (requestItem.PartNumber
!= (masterNumber.IsEmpty() ? enquiryPartNumber : partNumber))
{
qtyLess = requestItem.QuantitySelected
.GetValueOrDefault();
requestItem.QuantityOutstanding = quantity;
requestItem.QuantityRequired = quantity;
requestItem.QuantitySelected = 0;
}
else
{
//quantity has been reduced
qtyLess = requestItem.QuantitySelected
.GetValueOrDefault() - quantity;
requestItem.QuantityOutstanding = 0;
requestItem.QuantityRequired = quantity;
requestItem.QuantitySelected = quantity;
}
var quoteItems = repository.GetItems(quoteId).Where(p =>
p.PartNumber == requestItem.PartNumber
|| p.MasterPartNumber == requestItem.MasterNumber
).ToList();
foreach (var upQtItem in quoteItems)
{
if (qtyLess <= 0)
break;
if (upQtItem.QuantitySelected >= qtyLess)
{
upQtItem.QuantitySelected =
upQtItem.QuantitySelected.GetValueOrDefault() - qtyLess;
qtyLess = 0;
}
else
{
qtyLess = qtyLess - upQtItem.QuantitySelected.GetValueOrDefault();
upQtItem.QuantitySelected = 0;
}
upQtItem.Description = requestItem.Description;
repository.Context.Update(upQtItem);
}
}
// 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.QuantityOutstanding = quantity - requestItem.QuantitySelected.GetValueOrDefault();
requestItem.QuantityRequired = quantity;
}
//set part details
requestItem.Description = description;
requestItem.AdditionalDescription = addDescription;
requestItem.MasterNumber = masterNumber.IsEmpty() ? "Not found" : masterNumber;
requestItem.PartNumber = masterNumber.IsEmpty() ? enquiryPartNumber : partNumber;
requestItem.WeightKG = kg;
// if new item create and save, otherwise update
if (requestItem.recId == 0)
{
requestItem.ParentSurfaceItemId = quote.itemID;
requestItem.Sequence = sequence;
requestItem = repository.Context.Create(requestItem);
requestItems.Add(requestItem);
}
else
{
requestItem = repository.Context.Update(requestItem);
}
}
if (masterNumber.IsNotEmpty())
{
masterListHash.Add(masterNumber);
if (masterNumber == partNumber)
{
masterListHash.Add(masterNumber);
}
else if (partNumber.IsNotEmpty())
{
alternateListHash.Add(partNumber);
}
}
}
//get default margin
decimal margin = quote.CustomerDefaultMargin.TryParseDecimal().GetValueOrDefault();
//get all Inventory Items linked to either Master or Alternate and save as Quote Items (bottom grid)
string inventoryItemsFilter = string.Empty;
string supplierItemsFilter = string.Empty;
masterListHash.Remove("");
alternateListHash.Remove("");
if (masterSupplierListHash.Any())
{
string filter = string.Join("','", masterSupplierListHash.ToArray());
supplierItemsFilter = $" SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber in ('{filter}') ";
}
var partNumbers = masterListHash.Union(alternateListHash).ToList();
var dtInventoryItems = GetInventoryItems(partNumbers);
var storedQuoteItems = repository.GetItems(quoteId);
//foreach (var requestItem in rItems)
//{
// create a Quote Item object for each Inventory item returned
foreach (var inventoryItem in dtInventoryItems)
{
// fisrt: check if a quote item already exists
var storedItem = storedQuoteItems
.FirstOrDefault(p =>
p.UniqueNumber == inventoryItem.UniqueNumber
&& p.MasterPartNumber == inventoryItem.MasterNumber
&& p.BinLocation == inventoryItem.BinLocation
&& p.Brand == inventoryItem.Brand
&& p.Category == inventoryItem.Category
&& p.Condition == inventoryItem.Condition
&& p.Description == inventoryItem.Description
&& p.PartNumber == inventoryItem.PartNumber
&& p.Subcategory == inventoryItem.SubCategory
&& p.WarehouseLocation == inventoryItem.WarehouseLocation
);
if (storedItem.IsNull())
{
decimal costPrice = inventoryItem.AverageCost;
string partNumber = inventoryItem.MasterNumber;
string partMasterNumber = inventoryItem.PartNumber;
decimal sellingPrice = BusinessUtils.GetSellingPrice(costPrice, margin);
/*
Ref: "QuoteItem"."BinLocation" < "InventoryItem"."BinLocation"
Ref: "QuoteItem"."Brand" < "InventoryItem"."PartDetails_Brand"
Ref: "QuoteItem"."Condition" < "InventoryItem"."PartDetails_Condition"
Ref: "QuoteItem"."CostPrice" < "InventoryItem"."PartDetails_AverageCost"
Ref: "QuoteItem"."Description" < "InventoryItem"."PartDetails_Description"
Ref: "QuoteItem"."InventoryItemsUniqueNumber" < "InventoryItem"."PartDetails_UniqueNumber"
Ref: "QuoteItem"."PartNumber" < "InventoryItem"."PartDetails_PartsMasterPartNumber"
Ref: "QuoteItem"."PartsMasterPartNumber" < "InventoryItem"."PartDetails_PartsMasterPartNumber"
Ref: "QuoteItem"."QuantityAvailable" < "InventoryItem"."PartDetails_TotalAvailable"
Ref: "QuoteItem"."Supplier" < "InventoryItem"."PartDetails_Supplier"
Ref: "InventoryItem"."ParentSurfaceItemId" < "PartMaster"."itemID"
Ref: "PartMaster"."Details_WeightKG" < "QuoteItem"."WeightKG"
Ref: "PartMaster"."Details_PartsCategoryName" < "QuoteItem"."Category"
Ref: "PartMaster"."Details_PartsMinorCodeName" < "QuoteItem"."MinorCode"
Ref: "PartMaster"."Details_PartsSubCategoryName" < "QuoteItem"."Subcategory"
Ref: "InventoryManagement"."Location_BinLocation" < "QuoteItem"."BinLocation"
Ref: "InventoryManagement"."Location_WarehouseLocationName" < "QuoteItem"."WarehouseLocation"
Ref: "InventoryManagement"."ParentSurfaceItemId" < "InventoryItem"."itemID"
*/
var quoteItemObj = new QuoteItem()
{
isActive = true,
ParentSurfaceItemId = quoteId,
UniqueNumber = inventoryItem.UniqueNumber,
MasterPartNumber = inventoryItem.MasterNumber,
BinLocation = inventoryItem.BinLocation,
Brand = inventoryItem.Brand,
Category = inventoryItem.Category,
Condition = inventoryItem.Condition,
CostPrice = inventoryItem.AverageCost,
Description = inventoryItem.Description,
LeadTime = "STOCK",
Margin = margin,
MinorCode = inventoryItem.MinorCode,
PartNumber = inventoryItem.PartNumber,
QuantityAvailable = inventoryItem.Available,
QuantitySelected = 0,
SellingPrice = sellingPrice,
Sequence = 0,
Subcategory = inventoryItem.SubCategory,
Supplier = "INVENTORY",
SupplierCurrency = "ZAR",
SupplierSequence = BusinessUtils.GetQuoteItemSupplierSequence("INVENTORY"),
WarehouseLocation = inventoryItem.WarehouseLocation,
WeightKG = inventoryItem.WeightKG,
};
//get first matched request item
var linkedReqItem = db.QuoteRequestItems
.Where(p =>
p.isActive.Value
&& quoteId == p.ParentSurfaceItemId
&& (quoteItemObj.MasterPartNumber ==
p.MasterNumber
|| quoteItemObj.PartNumber ==
p.PartNumber)
).OrderByDescending(p => p.recId)
.FirstOrDefault();
if (linkedReqItem.IsNotNull())
{
quoteItemObj.Sequence = linkedReqItem
.Sequence
.GetValueOrDefault();
//quoteItemObj.QuoteItems_QuoteItems_QuoteItemsWeightKG = linkedReqItem
// .QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG
// .GetValueOrDefault();
quoteItemObj.Description = linkedReqItem
.Description;
}
quoteItemObj.SupplierSequence =
BusinessUtils.GetQuoteItemSupplierSequence(quoteItemObj.Supplier);
quoteItemObj = repository.Context.Create(quoteItemObj);
}
else
{
storedItem.QuantityAvailable = inventoryItem.Available;
repository.Context.Update(storedItem);
}
}
//}
DataTable dtSupplierParts = new DataTable();
//var supplierParts = new List();
if (supplierItemsFilter.IsNotEmpty())
{
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
//BusinessUtils.Log(pricelistQuery.ToString());
string query = pricelistQuery.ToString();
dtSupplierParts = xData.GetCustomTypedTable(query);
}
//create a Quote Item object for each Supplier part returned
if (dtSupplierParts.IsNotNull())
{
int itemSequenceNumber = 0;
var quoteItems = db.QuoteItems.Where(p => p.ParentSurfaceItemId == quoteId).ToList();
foreach (DataRow supplierItem in dtSupplierParts.Rows)
{
//only add new quote item if one hasn't already been saved
bool addNew = true;
string s_brand = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand"].ToString();
string s_condition = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition"].ToString();
string s_supplier = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName"].ToString();
string s_currency = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency"].ToString();
string s_leadTime = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime"].ToString();
string s_alternateNumbers = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers"].ToString();
string s_partNumber = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber"].ToString();
int s_availability = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability"].ToString().TryParseInt().GetValueOrDefault();
string s_note = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote"].ToString();
decimal s_salesPrice = supplierItem["SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice"].ToString().TryParseDecimal().GetValueOrDefault();
string partNumberCheck = s_alternateNumbers;
if (partNumberCheck.IsEmpty())
{
partNumberCheck = s_partNumber;
}
//CVH 2019-01-25 Leave Master number out of the check, only check on part number,
//supplier, condition and brand, otherwise we get duplicates
var existing = quoteItems.FirstOrDefault(p =>
p.ParentSurfaceItemId == quoteId
&& p.PartNumber == partNumberCheck
&& p.Brand == s_brand
&& p.Condition == s_condition
&& p.Supplier == s_supplier
&& p.WarehouseLocation == s_note);
// check for existing inactive surplus warehouse items
if (existing.IsNotNull())
{
if (existing.isActive.Value || (!existing.isActive.Value && "SURPLUS WAREHOUSE" == existing.Supplier))
{
addNew = false;
}
}
if (addNew)
{
string currency = s_currency;
decimal costPrice = BusinessUtils.ApplyExchangeRate(s_salesPrice, currency);
var quoteItemObj = new QuoteItem
{
isActive = true,
ParentSurfaceItemId = quoteId,
UniqueNumber = string.Empty,
BinLocation = string.Empty,
Brand = s_brand,
Category = string.Empty,
Condition = s_condition,
CostPrice = costPrice,
LeadTime = s_leadTime,
Margin = margin,
MinorCode = string.Empty,
PartNumber = s_partNumber,
QuantityAvailable = s_availability,
QuantitySelected = 0,
SellingPrice = BusinessUtils.GetSellingPrice(costPrice, margin),
Subcategory = string.Empty,
Supplier = s_supplier,
WarehouseLocation = s_note,
SupplierCurrency = currency,
};
if (quoteItemObj.MasterPartNumber.IsEmpty())
{
string mNumber = (from p in db.PartsAlternates
where p.isActive.Value
&& p.AlternateNumber == quoteItemObj.PartNumber
select p.PartNumber)
.FirstOrDefault();
if (mNumber.IsNotEmpty())
{
decimal? wKG = (from p in db.PartsMasters
where p.isActive.Value
&& p.PartNumber == mNumber
select p.WeightKG)
.FirstOrDefault();
quoteItemObj.WeightKG = wKG.GetValueOrDefault();
}
quoteItemObj.MasterPartNumber = mNumber;
quoteItemObj.Sequence = itemSequenceNumber;
}
string requestPartNumber = partNumberCheck;
var linkedReqItem = db.QuoteRequestItems
.Where(p =>
p.isActive.Value
&& p.ParentSurfaceItemId == quoteId
&& (p.MasterNumber == quoteItemObj.MasterPartNumber
|| p.PartNumber == requestPartNumber))
.Select(p => new
{
p.Sequence,
p.WeightKG,
p.Description,
p.MasterNumber
}).FirstOrDefault();
//string query = $@"select *
// from publ_QuoteRequestItems
// where ParentSurfaceItemId = {quoteId} and isActive = 1
// and (QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber = '{quoteItemObj.QuoteItems_QuoteItems_PartsMasterPartNumber/*quoteItemObj.QuoteItems_QuoteItems_QuoteItemsPartNumber*/}'
// or QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber = '{requestPartNumber}')";
if (linkedReqItem.IsNotNull())
//foreach (oQuoteRequestItems linkedReqItem in xData.GetCustomCollection("recId", typeof(oQuoteRequestItems), query, "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence", "publ_", false))
{
quoteItemObj.Sequence = linkedReqItem.Sequence;
quoteItemObj.WeightKG = linkedReqItem.WeightKG;
quoteItemObj.Description = linkedReqItem.Description;
//CVH 2019-01-24 Use Request Master number as Quote Item master number
quoteItemObj.MasterPartNumber = linkedReqItem.MasterNumber;
itemSequenceNumber = quoteItemObj.Sequence.GetValueOrDefault();
//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;
}
quoteItemObj.SupplierSequence = BusinessUtils.GetQuoteItemSupplierSequence(
quoteItemObj.Supplier);
quoteItemObj = BusinessUtils.Create(quoteItemObj);
// add to quoteItems collection so it will get picked up on next loop
quoteItems.Add(quoteItemObj);
}
}
//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.IsEmpty())
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 = string.Empty;
if (!new string[] { "", "ZAR" }.Contains(currency))
{
eRate = db.ExchangeRates.FirstOrDefault(p => p.ForexCurrency == currency)
?.BuyExchangeRate.TryParseDecimal()
.GetValueOrDefault() ?? 0m;
currencySymbol = db.Currencies.FirstOrDefault(p => p.Code == currency)
?.Symbol ?? string.Empty;
}
//var suppObj = db.QuoteBuyoutSuppliers
// .Where(p =>
// p.TradingName == supplierName
// && p.ParentSurfaceItemId == quoteId
// ).OrderByDescending(p => p.recId)
// .FirstOrDefault();
var suppObj = repository.GetBuyoutSuppliers(quoteId).FirstOrDefault(p => p.TradingName == supplierName);
if (suppObj.IsNull())
{
decimal freight = db.Freights
.FirstOrDefault(p => p.SupplierTradingName == supplierName)
?.Freight.TryParseDecimal()
.GetValueOrDefault() ?? 0m;
suppObj = new QuoteBuyoutSupplier()
{
ParentSurfaceItemId = quoteId,
TradingName = supplierName,
Telephone = supplierPhone,
Email = supplierEmail,
ContactPerson = contactPerson,
VATRate = supplierVatRate.TryParseDecimal().GetValueOrDefault(),
Currency = supplierCurrency,
ExchangeRate = eRate,
DefaultDiscount = supplierDefaultDiscount,
DefaultLeadtime = supplierDefaultLeadtime,
BuyoutQuoteType = 1137, //selected
Freight = freight,
Duty = 0m,
Status = 1131, //Quote Requested
};
suppObj = BusinessUtils.Create(suppObj);
}
//load supplier quote items, if existing
quoteItems = (from p in db.QuoteItems
where p.isActive.Value
&& quoteId == p.ParentSurfaceItemId
&& supplierName == p.Supplier
select p).ToList();
if (quoteItems.Any())
{
foreach (var quoteItem in quoteItems)
{
var buyObj = (from p in db.QuoteBuyoutItems
where p.MasterPartNumber
== quoteItem.MasterPartNumber
&& p.ParentSurfaceItemId == suppObj.itemID
select p).FirstOrDefault();
if (buyObj.IsNull())
{
buyObj = new QuoteBuyoutItem();
buyObj.ParentSurfaceItemId = suppObj.itemID;
buyObj.MasterPartNumber = quoteItem.MasterPartNumber;
buyObj.Brand = quoteItem.Brand;
buyObj.Condition = quoteItem.Condition;
decimal zar = quoteItem.CostPrice.GetValueOrDefault();
decimal forex = 0;
if (eRate != 0)
{
forex = zar / eRate;
}
buyObj.CostPriceZAR = zar;
buyObj.CostPriceForex = forex;
buyObj.Description = quoteItem.Description;
decimal discount = supplierDefaultDiscount.TryParseDecimal().GetValueOrDefault();
buyObj.Discount = discount;
decimal supplierDuty = suppObj.Duty.GetValueOrDefault();
decimal dutyCost = BusinessUtils.GetSellingPrice(zar, supplierDuty) - zar;
buyObj.DutyCost = dutyCost;
decimal supplierFreight = suppObj.Freight.GetValueOrDefault();
decimal freightCost = BusinessUtils.GetSellingPrice(zar, supplierFreight) - zar;
buyObj.FreightCost = freightCost;
buyObj.Leadtime = supplierDefaultLeadtime;
buyObj.PartNumber = quoteItem.PartNumber;
buyObj.QuantityAvailable = quoteItem.QuantityAvailable;
buyObj.QuantityRequired = quoteItem.QuantitySelected;
buyObj.TotalCost = zar + freightCost + dutyCost - (zar * discount / 100m);
buyObj.WeightKG = quoteItem.WeightKG;
buyObj.Sequence = quoteItem.Sequence;
buyObj.Selected = true;
buyObj = BusinessUtils.Create(buyObj);
//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.CostPrice = zar + freightCost + dutyCost - (zar * discount / 100m);
quoteItem.CalculateSellingPrice(margin);
BusinessUtils.Update(quoteItem);
//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);
var buyoutItems = db.QuoteBuyoutItems.Where(p => p.ParentSurfaceItemId == suppObj.itemID).ToList();
//load all requested items
foreach (DataRow reqItem in dtBuyoutRequestItems.Rows)
{
string masterNumber = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber"].ToString();
bool exists = (from p in buyoutItems // QuoteBuyoutItems
where masterNumber == p.MasterPartNumber
&& suppObj.itemID == p.ParentSurfaceItemId
select p).Any();
if (!exists)
{
decimal duty = BusinessUtils.GetSellingPrice(0, 0);
decimal freight = BusinessUtils.GetSellingPrice(0, 0);
decimal supplierDiscount = supplierDefaultDiscount.TryParseDecimal().GetValueOrDefault();
var buyObj = new QuoteBuyoutItem();
//buyObj.isActive = true;
//buyObj.itemID = buyItem.recId;
buyObj.ParentSurfaceItemId = suppObj.itemID;
buyObj.MasterPartNumber = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber"].ToString();
buyObj.Brand = string.Empty;
buyObj.Condition = string.Empty;
buyObj.CostPriceForex = 0;
buyObj.CostPriceZAR = 0;
buyObj.Description = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription"].ToString();
buyObj.Discount = supplierDiscount;
buyObj.DutyCost = duty;
buyObj.FreightCost = freight;
buyObj.Leadtime = supplierDefaultLeadtime;
buyObj.PartNumber = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber"].ToString();
buyObj.QuantityAvailable = 0;
int qty = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired"]
.ToString().TryParseInt().GetValueOrDefault();
buyObj.QuantityRequired = qty;
buyObj.TotalCost = 0;
decimal kg = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG"]
.ToString().TryParseDecimal().GetValueOrDefault();
buyObj.WeightKG = kg;
int sequence = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence"]
.ToString().TryParseInt().GetValueOrDefault();
buyObj.Sequence = sequence;
buyObj.Selected = true;
buyObj = BusinessUtils.Create(buyObj);
buyoutItems.Add(buyObj);
}
}
}
}
}
#endregion
}
}
catch (Exception ex)
{
BusinessUtils.HandleException(ex);
throw;
}
finally
{
db.Configuration.AutoDetectChangesEnabled = true;
}
}
private void Quotes_ProcessEnquiryItems5(int quoteId /*quote.itemID*/)
{
var userId = db.User.Id;
try
{
db.Configuration.AutoDetectChangesEnabled = false;
var quote = db.Quotes.FirstOrDefault(p => p.itemID == quoteId);
//get all enquiry items with part number and quantity linked to item
var enquiryItems = db.QuoteEnquiryItems.GetByParentId(quote.itemID)
.OrderBy(p => p.Sequence.Value)
.ToList();
var enquiryPartNumbers = enquiryItems.Select(p => p.PartNumber).Where(p => p.IsNotEmpty()).Distinct().ToList();
var parts = GetParts(enquiryPartNumbers);
var foundPartNumbers = parts.Select(p => p.PartNumber).Distinct()
.Union(parts.Select(p => p.AlternateNumber).Distinct())
.Where(p => p.IsNotEmpty())
.Distinct().ToList();
var createCatListPartNumbers = enquiryPartNumbers.Where(p => !foundPartNumbers.Contains(p)).ToList();
//CVH 2019-01-07 Create a new Part Master record based on CAT item details
var createdCatListParts = CreatePartsFromSupplierCATList(createCatListPartNumbers);
var createdCatListPartNumbers = createdCatListParts.Select(p => p.PartNumber).ToList();
var createNewPartNumbers = createCatListPartNumbers.Where(p => !createdCatListPartNumbers.Contains(p)).ToList();
//CVH 2019-01-07 No match in CAT list either, Create a new Part Master record based on Enquiry item details
var createdNewParts = CreateNewPartsFromEnquiryItems(createNewPartNumbers);
parts.AddRange(createdCatListParts);
parts.AddRange(createdNewParts);
parts = parts.OrderBy(p => p.MasterNumber)
.ThenBy(p => p.PartNumber)
.ThenBy(p => p.AlternateNumber)
.ToList();
var requestItems = db.QuoteRequestItems.GetByParentId(quoteId)
.OrderBy(p => p.Sequence)
.ToList();
foundPartNumbers = parts.Select(p => p.PartNumber).Distinct()
.Union(parts.Select(p => p.AlternateNumber).Distinct())
.Where(p => p.IsNotEmpty())
.Distinct().ToList();
foreach (var enquiryItem in enquiryItems)
{
if (enquiryItem.PartNumber.IsEmpty()) continue;
var part = parts.FirstOrDefault(p => new string[] { p.PartNumber, p.AlternateNumber }.Contains(enquiryItem.PartNumber));
//save a request item for each enquiry item with a quantity
var requestItem = requestItems.FirstOrDefault(p =>
p.MasterNumber == enquiryItem.PartNumber)
?? new QuoteRequestItem()
{
ParentSurfaceItemId = quoteId,
AdditionalDescription = part.AdditionalDescription,
Sequence = enquiryItem.Sequence,
Description = part.Description,
MasterNumber = part.MasterNumber,
PartNumber = part.AlternateNumber.NullOrEmptyCoalesce(part.PartNumber),
QuantityOutstanding = enquiryItem.Quantity,
QuantityRequired = enquiryItem.Quantity,
QuantitySelected = 0,
WeightKG = part.WeightKG
};
requestItem.QuantityRequired = enquiryItem.Quantity;
requestItem = BusinessUtils.Upsert(requestItem);
var reqItemIndex = requestItems.FindIndex(p => p.itemID == requestItem.itemID);
if (reqItemIndex < 0)
{
// item created
requestItems.Add(requestItem);
}
else
{
// item updated
requestItems[reqItemIndex] = requestItem;
}
}
// Delete any request item where related enquity item's quantity is 0
// enquiry.PartNumber == request.PartNumber
// enquiry.Quantity == 0
(from e in enquiryItems
join r in requestItems
on e.PartNumber
equals r.PartNumber
where e.Quantity <= 0
select r).ToList()
.ForEach(requestItem =>
{
if (BusinessUtils.Delete(requestItem) > 0)
{
requestItems.Remove(requestItem);
}
});
//get default margin
decimal margin = quote.ItemsDefaultMargin
.TryParseDecimal().GetValueOrDefault();
#region Inventory Items
//get all Inventory Items linked to either Master or Alternate and save as Quote Items (bottom grid)
var inventoryItems = GetInventoryItems(foundPartNumbers);
var storedQuoteItems = db.QuoteItems.GetByParentId(quoteId).ToList();
// create a Quote Item object for each Inventory item returned
foreach (var inventoryItem in inventoryItems)
{
// fisrt: check if a quote item already exists
var storedItem = storedQuoteItems
.FirstOrDefault(p =>
p.UniqueNumber == inventoryItem.UniqueNumber
&& p.MasterPartNumber == inventoryItem.MasterNumber
&& p.BinLocation == inventoryItem.BinLocation
&& p.Brand == inventoryItem.Brand
&& p.Category == inventoryItem.Category
&& p.Condition == inventoryItem.Condition
&& p.Description == inventoryItem.Description
&& p.PartNumber == inventoryItem.PartNumber
&& p.Subcategory == inventoryItem.SubCategory
&& p.WarehouseLocation == inventoryItem.WarehouseLocation
);
if (storedItem.IsNull())
{
decimal costPrice = inventoryItem.AverageCost;
string partNumber = inventoryItem.MasterNumber;
string partMasterNumber = inventoryItem.PartNumber;
decimal sellingPrice = BusinessUtils.GetSellingPrice(costPrice, margin);
/*
Ref: "QuoteItem"."BinLocation" < "InventoryItem"."BinLocation"
Ref: "QuoteItem"."Brand" < "InventoryItem"."PartDetails_Brand"
Ref: "QuoteItem"."Condition" < "InventoryItem"."PartDetails_Condition"
Ref: "QuoteItem"."CostPrice" < "InventoryItem"."PartDetails_AverageCost"
Ref: "QuoteItem"."Description" < "InventoryItem"."PartDetails_Description"
Ref: "QuoteItem"."InventoryItemsUniqueNumber" < "InventoryItem"."PartDetails_UniqueNumber"
Ref: "QuoteItem"."PartNumber" < "InventoryItem"."PartDetails_PartsMasterPartNumber"
Ref: "QuoteItem"."PartsMasterPartNumber" < "InventoryItem"."PartDetails_PartsMasterPartNumber"
Ref: "QuoteItem"."QuantityAvailable" < "InventoryItem"."PartDetails_TotalAvailable"
Ref: "QuoteItem"."Supplier" < "InventoryItem"."PartDetails_Supplier"
Ref: "InventoryItem"."ParentSurfaceItemId" < "PartMaster"."itemID"
Ref: "PartMaster"."Details_WeightKG" < "QuoteItem"."WeightKG"
Ref: "PartMaster"."Details_PartsCategoryName" < "QuoteItem"."Category"
Ref: "PartMaster"."Details_PartsMinorCodeName" < "QuoteItem"."MinorCode"
Ref: "PartMaster"."Details_PartsSubCategoryName" < "QuoteItem"."Subcategory"
Ref: "InventoryManagement"."Location_BinLocation" < "QuoteItem"."BinLocation"
Ref: "InventoryManagement"."Location_WarehouseLocationName" < "QuoteItem"."WarehouseLocation"
Ref: "InventoryManagement"."ParentSurfaceItemId" < "InventoryItem"."itemID"
*/
var quoteItem = new QuoteItem()
{
isActive = true,
ParentSurfaceItemId = quoteId,
UniqueNumber = inventoryItem.UniqueNumber,
MasterPartNumber = inventoryItem.MasterNumber,
BinLocation = inventoryItem.BinLocation,
Brand = inventoryItem.Brand,
Category = inventoryItem.Category,
Condition = inventoryItem.Condition,
CostPrice = inventoryItem.AverageCost,
Description = inventoryItem.Description,
LeadTime = "STOCK",
Margin = margin,
MinorCode = inventoryItem.MinorCode,
PartNumber = inventoryItem.PartNumber,
QuantityAvailable = inventoryItem.Available,
QuantitySelected = 0,
SellingPrice = sellingPrice,
Sequence = 0,
Subcategory = inventoryItem.SubCategory,
Supplier = "INVENTORY",
SupplierCurrency = "ZAR",
SupplierSequence = 0,
WarehouseLocation = inventoryItem.WarehouseLocation,
WeightKG = inventoryItem.WeightKG,
};
//get first matched request item
var linkedReqItem = db.QuoteRequestItems
.Where(p =>
p.isActive.Value
&& quoteId == p.ParentSurfaceItemId
&& (quoteItem.MasterPartNumber ==
p.MasterNumber
|| quoteItem.PartNumber ==
p.PartNumber)
).OrderByDescending(p => p.recId)
.FirstOrDefault();
if (linkedReqItem.IsNotNull())
{
quoteItem.Sequence = linkedReqItem
.Sequence
.GetValueOrDefault();
//quoteItemObj.QuoteItems_QuoteItems_QuoteItemsWeightKG = linkedReqItem
// .QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG
// .GetValueOrDefault();
quoteItem.Description = linkedReqItem
.Description;
}
quoteItem.SupplierSequence =
BusinessUtils.GetQuoteItemSupplierSequence(quoteItem.Supplier);
quoteItem = BusinessUtils.Create(quoteItem);
storedQuoteItems.Add(quoteItem);
}
else
{
storedItem.QuantityAvailable = inventoryItem.Available;
BusinessUtils.Update(storedItem);
storedQuoteItems[storedQuoteItems.FindIndex(p => p.itemID == storedItem.itemID)] = storedItem;
}
}
#endregion
#region Supplier Pricelist Parts
var supplierParts = GetSupplierPriceListParts(foundPartNumbers);
//create a Quote Item object for each Supplier part returned
if (supplierParts.IsNotEmpty())
{
int itemSequenceNumber = 0;
var quoteItems = db.QuoteItems.Where(p => p.ParentSurfaceItemId == quoteId).ToList();
var requestItemsMasterNumbers = requestItems
.OrderBy(p => p.Sequence)
.Select(p => p.MasterNumber)
.ToList();
foreach (var supplierItem in supplierParts)
{
//only add new quote item if one hasn't already been saved
bool addNew = true;
var itemPart = parts.FirstOrDefault(p => new string[] { p.PartNumber, p.AlternateNumber }.Contains(supplierItem.PartNumber));
var itemSequence = requestItemsMasterNumbers.IndexOf(itemPart.MasterNumber);
if (itemSequence < 0) itemSequence = requestItemsMasterNumbers.IndexOf(itemPart.AlternateNumber);
string partNumberCheck = itemPart.AlternateNumber.NullOrEmptyCoalesce(itemPart.PartNumber);
//CVH 2019-01-25 Leave Master number out of the check, only check on part number,
//supplier, condition and brand, otherwise we get duplicates
var existing = quoteItems.FirstOrDefault(p =>
p.ParentSurfaceItemId == quoteId
&& p.PartNumber == partNumberCheck
&& p.Brand == supplierItem.Brand
&& p.Condition == supplierItem.Condition
&& p.Supplier == supplierItem.SupplierName
&& p.WarehouseLocation == supplierItem.Note);
// check for existing inactive surplus warehouse items
if (existing.IsNotNull())
{
if (existing.isActive.Value || (!existing.isActive.Value && "SURPLUS WAREHOUSE" == existing.Supplier))
{
addNew = false;
}
}
if (addNew)
{
decimal costPrice = BusinessUtils.ApplyExchangeRate(supplierItem.SalesPrice, supplierItem.Currency);
var quoteItem = new QuoteItem
{
isActive = true,
ParentSurfaceItemId = quoteId,
Brand = supplierItem.Brand,
Condition = supplierItem.Condition,
CostPrice = costPrice,
LeadTime = supplierItem.LeadTime,
Margin = margin,
PartNumber = supplierItem.PartNumber,
QuantityAvailable = supplierItem.Availability,
QuantitySelected = 0,
SellingPrice = BusinessUtils.GetSellingPrice(costPrice, margin),
Supplier = supplierItem.SupplierName,
WarehouseLocation = supplierItem.Note,
SupplierCurrency = supplierItem.Currency,
WeightKG = itemPart.WeightKG,
MasterPartNumber = itemPart.MasterNumber,
Sequence = itemSequence,
SupplierSequence = BusinessUtils.GetQuoteItemSupplierSequence(supplierItem.SupplierName),
Description = itemPart.Description
};
string requestPartNumber = partNumberCheck;
var linkedReqItem = db.QuoteRequestItems
.Where(p =>
p.isActive.Value
&& p.ParentSurfaceItemId == quoteId
&& (p.MasterNumber == quoteItem.MasterPartNumber
|| p.PartNumber == requestPartNumber))
.Select(p => new
{
p.Sequence,
p.WeightKG,
p.Description,
p.MasterNumber
}).FirstOrDefault();
if (linkedReqItem.IsNotNull())
//foreach (oQuoteRequestItems linkedReqItem in xData.GetCustomCollection("recId", typeof(oQuoteRequestItems), query, "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence", "publ_", false))
{
quoteItem.Sequence = linkedReqItem.Sequence;
quoteItem.WeightKG = linkedReqItem.WeightKG;
quoteItem.Description = linkedReqItem.Description;
//CVH 2019-01-24 Use Request Master number as Quote Item master number
quoteItem.MasterPartNumber = linkedReqItem.MasterNumber;
itemSequenceNumber = quoteItem.Sequence.GetValueOrDefault();
//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;
}
quoteItem = BusinessUtils.Create(quoteItem);
// add to quoteItems collection so it will get picked up on next loop
quoteItems.Add(quoteItem);
}
}
}
#endregion
#region Buyout Suppliers
//CVH 2019-03-02 Get distinct list of suppliers from quote items
var dtQuoteItemSuppliers = GetQuoteItemsSuppliers(quoteId);
var buyoutSupplierNames = db.QuoteBuyoutSuppliers.GetByParentId(quoteId)
.Select(p => p.TradingName)
.ToList();
// List of supplier names missing from existing buyout supplier names
var supplierNamesToAdd = dtQuoteItemSuppliers.Where(p => !buyoutSupplierNames.Contains(p.Supplier))
.Select(p => p.Supplier).ToList();
var suppliers = db.Suppliers.Where(p => supplierNamesToAdd.Contains(p.TradingName))
.OrderBy(p => p.TradingName).ToList();
if (suppliers.Any())
{
var defaultMargin = quote.CustomerDefaultMargin.TryParseDecimal().GetValueOrDefault();
var eRates = db.ExchangeRates.ToList();
var freights = db.Freights.Where(p => supplierNamesToAdd.Contains(p.SupplierTradingName)).ToList();
suppliers.ForEach(supplier =>
{
var contacts = db.Contacts.Where(p => p.ParentSurfaceItemId == supplier.itemID.ToString()).ToList();
var contactPerson = contacts
.Select(p => $"{p.Name} {p.Surname}")
.ToList()
.StringJoin(", ");
decimal eRate = 0;
if (!new string[] { "", "ZAR" }.Contains(supplier.Currency))
{
eRate = eRates.FirstOrDefault(p => p.ForexCurrency == supplier.Currency)
?.BuyExchangeRate.TryParseDecimal()
.GetValueOrDefault() ?? 0m;
}
var freight = freights.FirstOrDefault(p => p.SupplierTradingName == supplier.TradingName)
?.Freight.TryParseDecimal()
.GetValueOrDefault() ?? 0m;
var buyoutSupplier = new QuoteBuyoutSupplier
{
ParentSurfaceItemId = quote.itemID,
BuyoutQuoteType = BuyoutQuoteType.SelectedItems,
ContactPerson = contactPerson,
Currency = supplier.Currency.NullOrEmptyCoalesce("ZAR"),
DefaultDiscount = supplier.DefaultDiscount,
DefaultLeadtime = supplier.DefaultLeadtime,
Email = supplier.PrimaryEmail,
ExchangeRate = eRate,
Freight = freight,
Status = BuyoutStatus.QuoteRequested,
Telephone = supplier.Telephone,
VATRate = supplier.VATRate.TryParseDecimal().GetValueOrDefault(),
TradingName = supplier.TradingName
};
buyoutSupplier = BusinessUtils.Create(buyoutSupplier);
});
}
#endregion
#region BuyoutItems
db.QuoteBuyoutSuppliers.GetByParentId(quoteId).Select(p => new
{
p.itemID,
Currency = p.Currency.NullOrEmptyCoalesce("ZAR"),
DefaultDiscount = p.DefaultDiscount.TryParseDecimal().GetValueOrDefault(),
DefaultLeadTime = p.DefaultLeadtime,
Duty = p.Duty.GetValueOrDefault(),
ExchangeRate = p.ExchangeRate.GetValueOrDefault(),
Freight = p.Freight.GetValueOrDefault(),
VATRate = p.VATRate.GetValueOrDefault(),
Supplier = p.TradingName,
}).ToList().ForEach(buyoutSupplier =>
{
var quoteItems = db.QuoteItems.GetByParentId(quoteId)
.Where(p =>
p.Supplier == buyoutSupplier.Supplier
).Select(p => new
{
p.itemID,
MasterNumber = p.MasterPartNumber,
Brand = p.Brand,
Condition = p.Condition,
CostPrice = p.CostPrice.GetValueOrDefault(),
LeadTime = p.LeadTime,
PartNumber = p.PartNumber,
SellingPrice = p.SellingPrice.GetValueOrDefault(),
Sequence = p.Sequence.GetValueOrDefault(),
Supplier = p.Supplier,
Currency = p.SupplierCurrency,
WeightKG = p.WeightKG.GetValueOrDefault(),
Description = p.Description,
Available = p.QuantityAvailable.GetValueOrDefault(),
QuantitySelected = p.QuantitySelected.GetValueOrDefault()
}).ToList();
var buyoutItems = db.QuoteBuyoutItems.GetByParentId(buyoutSupplier.itemID).ToList();
if (quoteItems.Any())
{
foreach (var quoteItem in quoteItems)
{
var exists = buyoutItems.Any(p =>
p.MasterPartNumber
== quoteItem.MasterNumber);
if (exists)
{
decimal eRate = buyoutSupplier.ExchangeRate;
decimal zar = quoteItem.CostPrice;
decimal forex = 0;
if (eRate != 0)
{
forex = zar / eRate;
}
var discount = buyoutSupplier.DefaultDiscount;
var dutyCost = BusinessUtils.GetSellingPrice(zar, buyoutSupplier.Duty) - zar;
var freightCost = BusinessUtils.GetSellingPrice(zar, buyoutSupplier.Freight) - zar;
var totalCost = zar + freightCost + dutyCost - (zar * discount / 100m);
var buyoutItem = new QuoteBuyoutItem
{
ParentSurfaceItemId = buyoutSupplier.itemID,
MasterPartNumber = quoteItem.MasterNumber,
Brand = quoteItem.Brand,
Condition = quoteItem.Condition,
CostPriceZAR = zar,
CostPriceForex = forex,
Description = quoteItem.Description,
Discount = buyoutSupplier.DefaultDiscount,
DutyCost = dutyCost,
FreightCost = freightCost,
Leadtime = buyoutSupplier.DefaultLeadTime,
PartNumber = quoteItem.PartNumber,
QuantityAvailable = quoteItem.Available,
QuantityRequired = quoteItem.QuantitySelected,
WeightKG = quoteItem.WeightKG,
Sequence = quoteItem.Sequence,
Selected = true,
TotalCost = totalCost
};
buyoutItem = BusinessUtils.Create(buyoutItem);
//BuyoutItems.Add(buyoutItem);
// 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
var qItem = db.QuoteItems.First(p => p.itemID == quoteItem.itemID);
qItem.CostPrice = zar + freightCost + dutyCost - (zar * discount / 100m);
qItem.CalculateSellingPrice(margin);
qItem = BusinessUtils.Update(qItem);
// quoteItems[quoteItems.FindIndex(p => p.itemID == qItem.itemID)] = qItem;
}
}
}
else
{
GetBuyoutRequestItems(quoteId).ForEach(reqItem =>
{
var exists = buyoutItems.Any(p =>
p.MasterPartNumber
== reqItem.MasterNumber
);
if (!exists)
{
var buyoutItem = new QuoteBuyoutItem
{
ParentSurfaceItemId = quoteId,
MasterPartNumber = reqItem.MasterNumber,
Description = reqItem.Description,
Leadtime = buyoutSupplier.DefaultLeadTime,
PartNumber = reqItem.PartNumber,
QuantityRequired = reqItem.Required,
WeightKG = reqItem.WeightKG,
Sequence = reqItem.Sequence,
Selected = true
};
buyoutItem = BusinessUtils.Create(buyoutItem);
}
});
}
});
#endregion
}
catch (Exception ex)
{
BusinessUtils.HandleException(ex);
throw;
}
finally
{
db.Configuration.AutoDetectChangesEnabled = true;
}
}
#region Private
private bool ValidateUnchanged(QuoteRequestItem item, int quantity, string partNumber)
{
return item.QuantityRequired.GetValueOrDefault() == quantity
&& item.PartNumber == partNumber;
}
private bool ValidateNoEnquiryOrQuantity(QuoteRequestItem item, string enquiryPartNumber, int quantity)
{
return item.QuantityRequired.GetValueOrDefault() == 0
&& (enquiryPartNumber.IsEmpty() || quantity == 0);
}
private bool DeleteIfInvalid(QuoteRequestItem item, string enquiryPartNumber, int quantity)
{
if (item.recId > 0
&& item.QuantityRequired.GetValueOrDefault() == 0
&& (enquiryPartNumber.IsEmpty() || quantity == 0))
{
return db.Delete(item) > 0;
}
return false;
}
/////////////////////////////
private List CreatePartsFromSupplierCATList(IEnumerable missingPartNumbers)
{
var foundCatListParts = new List();
if (missingPartNumbers.Any())
{
// look for Supplier CAT List Parts for missing enquiry part numbers
var catList = GetSupplierCATListParts(missingPartNumbers);
// CVH 2019-01-07 Create a new Part Master record based on CAT item details
foreach (var catItem in catList)
{
var partsMaster = CreatePartsMasterFromPriceListItem(catItem);
foundCatListParts.Add(new PartResult(partsMaster));
}
}
return foundCatListParts;
}
public List CreateNewPartsFromEnquiryItems(IEnumerable partNumbers)
{
var createdParts = new List();
if (partNumbers.Any())
{
foreach (var partNumber in partNumbers)
{
var partMaster = CreatePartsMasterFromPartNumber(partNumber);
createdParts.Add(new PartResult(partMaster));
}
}
return createdParts;
}
private PartsMaster CreatePartsMasterFromPriceListItem(ISupplierPricelistPart priceListPart)
{
var partsMaster = BusinessUtils.Create(new PartsMaster
{
AdditionalDescription = priceListPart.AdditionalDescription,
CEPNumber = BusinessUtils.GetNextCEPNumber(),
Description = priceListPart.Description,
ItemVetted = YesNo.No,
PartNumber = priceListPart.PartNumber,
MasterNumber = priceListPart.PartNumber,
WeightKG = priceListPart.WeightKG.GetValueOrDefault(),
WeightLBS = priceListPart.WeightLBS.GetValueOrDefault(),
});
var partsAlternate = BusinessUtils.Create(new PartsAlternate
{
CEPNumber = partsMaster.CEPNumber,
AlternateNumber = partsMaster.MasterNumber,
PartNumber = partsMaster.MasterNumber,
ParentSurfaceItemId = partsMaster.itemID
});
return partsMaster;
}
private PartsMaster CreatePartsMasterFromPartNumber(string partNumber)
{
var partsMaster = BusinessUtils.Create(new PartsMaster
{
CEPNumber = BusinessUtils.GetNextCEPNumber(),
ItemVetted = YesNo.No, // No
MasterNumber = partNumber,
PartNumber = partNumber
});
var partsAlternate = BusinessUtils.Create(new PartsAlternate
{
CEPNumber = partsMaster.CEPNumber,
AlternateNumber = partsMaster.MasterNumber,
PartNumber = partsMaster.MasterNumber,
ParentSurfaceItemId = partsMaster.itemID
});
return partsMaster;
}
#endregion
#region Process InMemory
//public class FullBuyoutSupplier : QuoteBuyoutSupplier
//{
// public List BuyoutItems { get; set; } = new List();
//}
public class FullQuote
{
public int QuoteId { get; set; }
public decimal Margin { get; set; }
public List PartNumbers { get; set; }
public List Parts { get; set; }
public List InventoryItems { get; set; }
public List PriceListParts { get; set; }
public List EnquiryItems { get; set; } = new List();
public List RequestItems { get; set; } = new List();
public List BuyoutSuppliers { get; set; } = new List();
public List BuyoutItems { get; set; } = new List();
public List Items { get; set; } = new List();
}
public FullQuote ProcessInMemory(int quoteId)
{
var storedQuote = repository.GetByItemId(quoteId);
//var quote = new State.Quote()
//{
// Date = storedQuote.Date.GetValueOrDefault(),
// DefaultMargin = storedQuote.CustomerDefaultMargin.TryParseDecimal().GetValueOrDefault(),
// QuoteId = storedQuote.itemID,
// VATRate = storedQuote.CustomerVATRate.TryParseDecimal().GetValueOrDefault(),
// BuyoutSuppliers = repository.GetBuyoutSuppliers(quoteId).ToList().Select(p => new State.BuyoutSupplier()
// {
// BuyoutQuoteTypeId = p.BuyoutQuoteType.GetValueOrDefault(),
// Currency = p.Currency.NullOrEmptyCoalesce("ZAR"),
// DefaultDiscount = p.DefaultDiscount.TryParseDecimal().GetValueOrDefault(),
// DefaultLeadTime = p.DefaultLeadtime,
// Duty = p.Duty.GetValueOrDefault(),
// ExchangeRate = p.ExchangeRate.GetValueOrDefault(),
// Freight = p.Freight.GetValueOrDefault(),
// TradingName = p.TradingName,
// VATRate = p.VATRate.GetValueOrDefault(),
// Items = repository.GetBuyoutItems(p.itemID).ToList().Select(p => new State.BuyoutItem()
// {
// Brand = p.Brand,
// Condition = p.Condition,
// CostPriceForex = p.CostPriceForex.GetValueOrDefault(),
// CostPriceZAR = p.CostPriceZAR.GetValueOrDefault(),
// Discount = p.Discount.GetValueOrDefault(),
// DutyCost = p.DutyCost.GetValueOrDefault(),
// FreightCost = p.FreightCost.GetValueOrDefault(),
// LeadTime = p.Leadtime,
// MasterPartNumber = p.MasterPartNumber,
// PartNumber = p.PartNumber,
// QuantityAvailable = p.QuantityAvailable.GetValueOrDefault(),
// QuantityRequired = p.QuantityRequired.GetValueOrDefault(),
// Selected = p.Selected.GetValueOrDefault(),
// Sequence = p.Sequence.GetValueOrDefault(),
// TotalCost = p.TotalCost.GetValueOrDefault()
// }).ToList()
// }).ToList(),
// EnquiryItems = repository.GetEnquiryItems(quoteId).ToList().Select(p => new State.EnquiryItem()
// {
// PartNumber = p.PartNumber,
// QuantityRequired = p.Quantity.GetValueOrDefault(),
// Sequence = p.Sequence.GetValueOrDefault()
// }).ToList(),
// Items = repository.GetItems(quoteId).ToList().Select(p => new State.QuoteItem()
// {
// BinLocation = p.BinLocation,
// Brand = p.Brand,
// CATListCostPrice = p.CATListCostPrice.GetValueOrDefault()
// // TODO: Finish mapping
// }).ToList()
//};
var quote = new FullQuote()
{
QuoteId = quoteId,
Margin = storedQuote.CustomerDefaultMargin.TryParseDecimal().GetValueOrDefault(),
EnquiryItems = repository.GetEnquiryItems(quoteId).ToList().Where(p => p.Quantity > 0 && p.PartNumber.IsNotEmpty()).ToList()
};
var enquiryPartNumbers = quote.EnquiryItems.Select(p => p.PartNumber).Distinct().ToList();
var parts = GetParts(enquiryPartNumbers);
// quote.Parts = parts;
var partNumbers = parts.Where(p => !p.IsAlternate).Select(p => p.PartNumber)
.Union(parts.Where(p => p.IsAlternate).Select(p => p.AlternateNumber))
.Where(p => p.IsNotEmpty())
.ToList();
// quote.PartNumbers = partNumbers;
var inventoryItems = GetInventoryItems(partNumbers);
var supplierParts = GetSupplierPriceListParts(partNumbers);
var supplierNames = supplierParts.Select(p => p.SupplierName).Distinct().ToList();
var suppliers = db.Suppliers.Where(p => supplierNames.Contains(p.TradingName)).OrderBy(p => p.TradingName).ToList();
var eRates = db.ExchangeRates.ToList();
var freights = db.Freights.Where(p => supplierNames.Contains(p.SupplierTradingName)).ToList();
// quote.InventoryItems = inventoryItems;
//quote.PriceListParts = supplierParts;
int supplierItemId = 1;
suppliers.ForEach(supplier =>
{
var contacts = db.Contacts.Where(p => p.ParentSurfaceItemId == supplier.itemID.ToString()).ToList();
var contactPerson = contacts
.Select(p => $"{p.Name} {p.Surname}")
.ToList().StringJoin(", ");
decimal eRate = 0;
if (!new string[] { "", "ZAR" }.Contains(supplier.Currency))
{
eRate = eRates.FirstOrDefault(p => p.ForexCurrency == supplier.Currency)?
.BuyExchangeRate.TryParseDecimal().GetValueOrDefault() ?? 0m;
}
var freight = freights.FirstOrDefault(p => p.SupplierTradingName == supplier.TradingName)?
.Freight.TryParseDecimal().GetValueOrDefault() ?? 0m;
var buyoutSupplier = new QuoteBuyoutSupplier()
{
itemID = supplierItemId++,
ParentSurfaceItemId = quote.QuoteId,
BuyoutQuoteType = BuyoutQuoteType.SelectedItems,
ContactPerson = contactPerson,
Currency = supplier.Currency.NullOrEmptyCoalesce("ZAR"),
DefaultDiscount = supplier.DefaultDiscount,
DefaultLeadtime = supplier.DefaultLeadtime,
Email = supplier.PrimaryEmail,
ExchangeRate = eRate,
Freight = freight,
Status = BuyoutStatus.QuoteRequested,
Telephone = supplier.Telephone,
VATRate = supplier.VATRate.TryParseDecimal().GetValueOrDefault(),
TradingName = supplier.TradingName
};
quote.BuyoutSuppliers.Add(buyoutSupplier);
});
quote.EnquiryItems.ForEach(enquiryItem =>
{
if (enquiryItem.PartNumber.IsNotEmpty())
{
var part = parts.FirstOrDefault(p => new string[] { p.PartNumber, p.AlternateNumber }.Contains(enquiryItem.PartNumber));
var invItems = inventoryItems.Where(p => new string[] { p.MasterNumber, p.PartNumber }.Contains(enquiryItem.PartNumber)).ToList();
var suppItems = supplierParts.Where(p => new string[] { p.PartNumber, p.AlternateNumber }.Contains(enquiryItem.PartNumber)).ToList();
var requestItem = new QuoteRequestItem()
{
ParentSurfaceItemId = quoteId,
AdditionalDescription = part.AdditionalDescription,
Sequence = enquiryItem.Sequence,
Description = part.Description,
MasterNumber = part.MasterNumber,
PartNumber = part.AlternateNumber.NullOrEmptyCoalesce(part.PartNumber),
QuantityOutstanding = enquiryItem.Quantity,
QuantityRequired = enquiryItem.Quantity,
QuantitySelected = 0,
WeightKG = part.WeightKG,
};
quote.RequestItems.Add(requestItem);
}
});
inventoryItems.ForEach(inventoryItem =>
{
quote.Items.Add(new QuoteItem()
{
isActive = true,
ParentSurfaceItemId = quoteId,
UniqueNumber = inventoryItem.UniqueNumber,
MasterPartNumber = inventoryItem.MasterNumber,
BinLocation = inventoryItem.BinLocation,
Brand = inventoryItem.Brand,
Category = inventoryItem.Category,
Condition = inventoryItem.Condition,
CostPrice = inventoryItem.AverageCost,
Description = inventoryItem.Description,
LeadTime = "STOCK",
Margin = quote.Margin,
MinorCode = inventoryItem.MinorCode,
PartNumber = inventoryItem.PartNumber,
QuantityAvailable = inventoryItem.Available,
QuantitySelected = 0,
SellingPrice = BusinessUtils.GetSellingPrice(inventoryItem.AverageCost, quote.Margin),
Sequence = 0,
Subcategory = inventoryItem.SubCategory,
Supplier = "INVENTORY",
SupplierCurrency = "ZAR",
SupplierSequence = BusinessUtils.GetQuoteItemSupplierSequence("INVENTORY"),
WarehouseLocation = inventoryItem.WarehouseLocation,
WeightKG = inventoryItem.WeightKG,
});
});
quote.RequestItems.ForEach(requestItem =>
{
quote.BuyoutSuppliers.ForEach(buyoutSupplier =>
{
supplierParts.Where(p => p.SupplierName == buyoutSupplier.TradingName)
.OrderBy(p => p.PartNumber).ThenBy(p => p.AlternateNumber).ToList()
.ForEach(supplierPart =>
{
int sequence = 1;
decimal eRate = buyoutSupplier.ExchangeRate.GetValueOrDefault();
decimal costPriceZAR = BusinessUtils.ApplyExchangeRate(supplierPart.SalesPrice, supplierPart.Currency);
decimal costPriceForex = 0;
if (eRate != 0)
{
costPriceForex = costPriceZAR / eRate;
}
decimal freight = BusinessUtils.GetSellingPrice(costPriceZAR, buyoutSupplier.Freight) - costPriceZAR;
decimal duty = BusinessUtils.GetSellingPrice(costPriceZAR, buyoutSupplier.Duty) - costPriceZAR;
decimal discountAmount = (costPriceZAR * buyoutSupplier.DefaultDiscount.TryParseDecimal().GetValueOrDefault() / 100m);
// changed variable discount for supplierDiscount
decimal total = costPriceZAR + freight + duty - discountAmount;
var buyoutItem = new QuoteBuyoutItem()
{
ParentSurfaceItemId = buyoutSupplier.itemID,
MasterPartNumber = requestItem.MasterNumber,
Brand = supplierPart.Brand,
Condition = supplierPart.Condition,
CostPriceForex = costPriceForex,
CostPriceZAR = costPriceZAR,
Description = supplierPart.Description,
Discount = discountAmount,
DutyCost = duty,
FreightCost = freight,
Leadtime = buyoutSupplier.DefaultLeadtime,
PartNumber = supplierPart.AlternateNumber.NullOrEmptyCoalesce(supplierPart.PartNumber),
QuantityAvailable = supplierPart.Availability,
QuantityRequired = requestItem.QuantityRequired,
Sequence = sequence++,
TotalCost = total,
WeightKG = supplierPart.WeightKG
};
quote.BuyoutItems.Add(buyoutItem);
var part = parts.FirstOrDefault(p => new string[] { p.MasterNumber, p.PartNumber, p.AlternateNumber }.Contains(requestItem.PartNumber));
var quoteItem = new QuoteItem()
{
ParentSurfaceItemId = quote.QuoteId,
MasterPartNumber = buyoutItem.MasterPartNumber,
Brand = buyoutItem.Brand,
Category = part.Category,
Condition = buyoutItem.Condition,
CostPrice = buyoutItem.TotalCost,
Description = buyoutItem.Description,
LeadTime = buyoutItem.Leadtime,
Margin = quote.Margin,
MinorCode = part.MinorCode,
PartNumber = buyoutItem.PartNumber,
QuantityAvailable = buyoutItem.QuantityAvailable,
SellingPrice = BusinessUtils.GetSellingPrice(
buyoutItem.TotalCost,
quote.Margin),
Sequence = requestItem.Sequence,
Subcategory = part.SubCategory,
Supplier = buyoutSupplier.TradingName,
SupplierCurrency = buyoutSupplier.Currency,
WarehouseLocation = supplierPart.Note,
SupplierSequence = BusinessUtils.GetQuoteItemSupplierSequence(buyoutSupplier.TradingName),
WeightKG = buyoutItem.WeightKG,
};
quote.Items.Add(quoteItem);
});
});
});
quote.Items = quote.Items.OrderBy(p => p.Sequence)
.ThenBy(p => p.SupplierSequence)
.ThenBy(p => p.Supplier)
.ToList();
return quote;
}
#endregion
// =================================================================================================================================
// public Quote Quote { get; protected set; }
// public ProcessQuoteResult ProcessQuote(int quoteId)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// logger.Debug($"Quote Id: {quoteId}");
// var quote = db.Quotes.GetFirstByItemId(quoteId);
// if (quote.IsNull())
// {
// logger.Warn($"Could not find Quote with id: {quoteId}");
// }
// this.Quote = quote;
// this._buyoutItems.Clear();
// this._buyoutRequestItems.Clear();
// this._buyoutSuppliers.Clear();
// this._enquiryItems.Clear();
// this._enquiryPartNumbers.Clear();
// this._inventoryItems.Clear();
// this._partNumbers.Clear();
// this._parts.Clear();
// this._quoteItems.Clear();
// this._quoteItemsSupplierNames.Clear();
// this._requestItems.Clear();
// this._supplierPriceListParts.Clear();
// logger.Debug($"Quote Number: {quote.QuoteInformation_QuoteNumber}");
// ProcessEnquiryPartNumbres(quoteId);
// ProcessRequestItems();
// ProcessInventoryItems();
// ProcessSupplierPriceList();
// ProcessBuyoutSuppliers();
// ProcessBuyoutItems();
// RemoveMatchingSurplusWarehouseQuoteItems();
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// return new ProcessQuoteResult { QuoteId = quoteId };
// }
// #region Quote Child Collections
// private List _enquiryItems = new List();
// public List EnquiryItems
// {
// get
// {
// if (_enquiryItems.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// var query = db.QuoteEnquiryItems.GetByParentId(Quote.itemID)
// .OrderBy(p => p.QuoteEnquiryItems_QuoteEnquiryItems_Sequence);
// logger.Debug($"EnquiryItems: db.QuoteEnquiryItems.GetByParentId({Quote.itemID})");
// logger.Trace($"Executing SQL: {query.ToString()}");
// _enquiryItems = query.ToList();
// timer.Stop();
// logger.Trace($"Returned {_enquiryItems.Count} row in {timer.Elapsed}");
// }
// return _enquiryItems;
// }
// }
// private List _enquiryPartNumbers = new List();
// public List EnquiryPartNumbers
// {
// get
// {
// if (_enquiryPartNumbers.IsEmpty())
// {
// _enquiryPartNumbers = EnquiryItems
// .Where(p => p.QuoteEnquiryItems_QuoteEnquiryItems_Quantity > 0)
// .Select(p => p.QuoteEnquiryItems_QuoteEnquiryItems_PartNumber)
// .OrderBy(p => p)
// .ToList();
// }
// return _enquiryPartNumbers;
// }
// }
// private List _parts = new List();
// public List Parts
// {
// get
// {
// if (_parts.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// logger.Debug($"Parts: db.GetParts($EnquiryPartNumbers).ToList();");
// _parts = db.GetParts(EnquiryPartNumbers).ToList();
// timer.Stop();
// logger.Debug($"Returned {_parts.Count} rows in {timer.Elapsed}");
// }
// return _parts;
// }
// }
// private List _partNumbers = new List();
// ///
// /// All found partNumbers combined with found alternateNumbers
// ///
// public List PartNumbers
// {
// get
// {
// if (_partNumbers.IsEmpty())
// {
// _partNumbers = Parts.Select(p => p.PartNumber).Distinct()
// .Union(Parts.Select(p => p.AlternateNumber).Distinct())
// .OrderBy(p => p)
// .ToList();
// logger.LogCollection(nameof(PartNumbers), _partNumbers);
// }
// return _partNumbers;
// }
// }
// private List _supplierPriceListParts = new List();
// public List SupplierPriceListParts
// {
// get
// {
// if (_supplierPriceListParts.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// logger.Debug($"SupplierPriceListParts: db.GetSupplierPriceListParts($PartNumbers)");
// _supplierPriceListParts = db.GetSupplierPriceListParts(PartNumbers);
// timer.Stop();
// logger.Debug($"Returned {_supplierPriceListParts.Count} rows in {timer.Elapsed}");
// }
// return _supplierPriceListParts;
// }
// }
// private List _requestItems = new List();
// public List RequestItems
// {
// get
// {
// if (_requestItems.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// var query = db.QuoteRequestItems.GetByItemId(Quote.itemID)
// .OrderBy(p => p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence);
// logger.Debug($"RequestItems: db.QuoteRequestItems.GetByParentId({Quote.itemID})");
// logger.Trace($"Executing SQL: {query.ToString()}");
// _requestItems = query.ToList();
// timer.Stop();
// logger.Trace($"Returned {_requestItems.Count} row in {timer.Elapsed}");
// }
// return _requestItems;
// }
// }
// private List _inventoryItems = new List();
// public List InventoryItems
// {
// get
// {
// if (_inventoryItems.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// logger.Debug($"SupplierPriceListParts: db.GetInventoryItems($PartNumbers)");
// _inventoryItems = db.GetInventoryItems(PartNumbers);
// timer.Stop();
// logger.Debug($"Returned {_inventoryItems.Count} rows in {timer.Elapsed}");
// }
// return _inventoryItems;
// }
// }
// private List _quoteItems = new List();
// public List QuoteItems
// {
// get
// {
// if (_quoteItems.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// var query = db.QuoteItems.GetByParentId(Quote.itemID);
// logger.Debug($"QuoteItems: db.QuoteItems.GetByParentId({Quote.itemID})");
// logger.Trace($"Executing SQL: {query.ToString()}");
// _quoteItems = query.ToList();
// timer.Stop();
// logger.Trace($"Returned {_quoteItems.Count} row in {timer.Elapsed}");
// }
// return _quoteItems;
// }
// }
// private List _buyoutSuppliers = new List();
// public List BuyoutSuppliers
// {
// get
// {
// if (_buyoutSuppliers.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// var query = db.QuoteBuyoutSuppliers.GetByParentId(Quote.itemID);
// logger.Debug($"BuyoutSuppliers: db.QuoteBuyoutSuppliers.GetByParentId({Quote.itemID})");
// logger.Trace($"Executing SQL: {query.ToString()}");
// _buyoutSuppliers = query.ToList();
// timer.Stop();
// logger.Trace($"Returned {_buyoutSuppliers.Count} row in {timer.Elapsed}");
// }
// return _buyoutSuppliers;
// }
// }
// private List _buyoutRequestItems = new List();
// public List BuyoutRequestItems
// {
// get
// {
// if (_buyoutRequestItems.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// // 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
// var query = $@"
// ;WITH cte AS
// (
// SELECT [itemID]
// ,[isActive]
// ,[surfaceId]
// ,[ParentSurfaceItemId]
// ,[Sequence] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence]
// ,[PartNumber] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber]
// ,[MasterNumber] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber]
// ,[Required] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired]
// ,[Description] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription]
// ,[Selected] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected]
// ,[Outstanding] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding]
// ,[recId]
// ,[AdditionalDescription] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsAdditionalDescription]
// ,[WeightKG] = [QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG]
// ,[RowNum] = ROW_NUMBER() OVER(
// PARTITION BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber
// ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber DESC
// ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence ASC)
// FROM publ_QuoteRequestItems
// WHERE [isActive] = 1
// AND [ParentSurfaceItemId] = {Quote.itemID}
// )
// SELECT [MasterNumber]
// , [PartNumber]
// , [Description]
// , [WeightKG]
// , [Sequence]
// , SUM([Required]) AS [Required]
// FROM cte
// WHERE [RowNum] = 1
// GROUP BY [MasterNumber]
// ,[PartNumber]
// ,[Description]
// ,[WeightKG]
// ,[Sequence]
// ORDER BY [MasterNumber]";
// logger.Debug($"BuyoutRequestItems:");
// logger.Trace($"Executing SQL: {query}");
// _buyoutRequestItems = db.Database.SqlQuery(query).ToList();
// timer.Stop();
// logger.Trace($"Returned {_buyoutRequestItems.Count} row in {timer.Elapsed}");
// }
// return _buyoutRequestItems;
// }
// }
// private List _buyoutItems = new List();
// public List BuyoutItems
// {
// get
// {
// if (_buyoutItems.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// var query = db.QuoteBuyoutItems.GetByParentId(Quote.itemID);
// logger.Debug($"BuyoutItems:");
// logger.Trace($"Executing SQL: {query.ToString()}");
// _buyoutItems = query.ToList();
// timer.Stop();
// logger.Trace($"Returned {_buyoutItems.Count} row in {timer.Elapsed}");
// }
// return _buyoutItems;
// }
// }
// private List _quoteItemsSupplierNames = new List();
// public List QuoteItemsSupplierNames
// {
// get
// {
// if (_quoteItemsSupplierNames.IsEmpty())
// {
// var timer = Stopwatch.StartNew();
// var query = $@"SELECT
// MAX(QuoteItems_QuoteItems_QuoteItemsSupplierCurrency) AS Currency
// ,Supplier = QuoteItems_QuoteItems_QuoteItemsSupplier
// FROM publ_QuoteItems
// WHERE isActive = 1
// AND QuoteItems_QuoteItems_QuoteItemsSupplier <> 'CEP Inventory'
// AND ParentSurfaceItemId = {Quote.itemID}
// GROUP by QuoteItems_QuoteItems_QuoteItemsSupplier";
// logger.Debug($"QuoteItemsSupplierNames:");
// logger.Trace($"Executing SQL: {query}");
// _quoteItemsSupplierNames = db.Database
// .SqlQuery(query)
// .ToList();
// timer.Stop();
// logger.Trace($"Returned {_quoteItemsSupplierNames.Count} row in {timer.Elapsed}");
// }
// return _quoteItemsSupplierNames;
// }
// }
// #endregion
// #region Private Methods
// private void ProcessEnquiryPartNumbres(int quoteId)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// // Enquiry Part Numbers in Quote
// logger.LogCollection(nameof(EnquiryPartNumbers), EnquiryPartNumbers);
// // All found partNumbers combined with found alternateNumbers
// logger.LogCollection(nameof(Parts), Parts.Select(p => new PartReduced(p)));
// var masterCount = Parts.Count(p => !p.IsAlternate);
// var alternateCount = Parts.Count(p => p.IsAlternate);
// logger.Debug($"Results: {Parts.Count}, Masters {masterCount}, Alternates {alternateCount}");
// logger.LogCollection(nameof(PartNumbers), PartNumbers);
// // List of enquiryPartNumbers not in fountPartNumbers
// var missingPartNumbers = EnquiryPartNumbers
// .Where(p => !PartNumbers.Contains(p))
// .ToList();
// logger.LogCollection(nameof(missingPartNumbers), missingPartNumbers);
// if (missingPartNumbers.Any())
// {
// logger.Debug("Not all $enquiryPartNumbers were found.");
// var createdCATListParNumbers = CreatePartsFromSupplierCATList(missingPartNumbers);
// logger.LogCollection(nameof(createdCATListParNumbers), createdCATListParNumbers);
// logger.Debug($"PartNumbers still missing after creating from CAT List");
// var missingFromCATList = missingPartNumbers.Where(p => !createdCATListParNumbers.Contains(p)).ToList();
// logger.LogCollection(nameof(missingFromCATList), missingFromCATList);
// if (missingFromCATList.Any())
// {
// logger.Debug($"Creating Master and Alternate for {missingFromCATList.Count} remaining items.");
// }
// }
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// }
// private void ProcessRequestItems()
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var allEnquiryItems = db.QuoteEnquiryItems
// .Where(p => p.ParentSurfaceItemId == Quote.itemID)
// .OrderBy(p => p.QuoteEnquiryItems_QuoteEnquiryItems_Sequence)
// .Select(p => new
// {
// p.itemID,
// PartNumber = p.QuoteEnquiryItems_QuoteEnquiryItems_PartNumber,
// Quantity = p.QuoteEnquiryItems_QuoteEnquiryItems_Quantity ?? 0,
// Sequence = p.QuoteEnquiryItems_QuoteEnquiryItems_Sequence ?? 0
// });
// var enquiryItems = allEnquiryItems.Where(p => p.Quantity > 0).ToList();
// var requestItems = RequestItems.ToList();
// logger.Debug(@"Delete any request item where related enquity item's quantity is 0
//enquiry.PartNumber == request.PartNumber
//enquiry.Quantity == 0");
// // Delete any request item where related enquity item's quantity is 0
// // enquiry.PartNumber == request.PartNumber
// // enquiry.Quantity == 0
// (from e in allEnquiryItems
// join r in requestItems
// on e.PartNumber
// equals r.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber
// where e.Quantity <= 0
// select r).ToList()
// .ForEach(requestItem => {
// if (BusinessUtils.Delete(requestItem) > 0)
// {
// var itemJson = new RequestItemReduced(requestItem).ToJson();
// logger.Debug($"Deleted RequestItem: {itemJson}");
// RequestItems.Remove(requestItem);
// }
// });
// logger.Debug(@"Update required quantity in existing request items
//enquiry.PartNumber == request.PartNumber
//and enquiry.Quantity != request.QuantityRequired");
// // Update required quantity in existing request items
// // enquiry.PartNumber == request.PartNumber
// // and enquiry.Quantity != request.QuantityRequired
// (from e in allEnquiryItems
// join r in requestItems
// on e.PartNumber equals r.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber
// where e.Quantity != r.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired
// select new
// {
// enquiryItem = e,
// requestItem = r
// }).ToList()
// .ForEach(itemPair =>
// {
// var enquiryItem = itemPair.enquiryItem;
// var requestItem = itemPair.requestItem;
// var enquiryItemJson = enquiryItem.ToJson();
// var requestItemJson = new RequestItemReduced(requestItem).ToJson();
// logger.Debug($"Updating RequestItem Required Quantity from {requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired} to {enquiryItem.Quantity}");
// logger.Debug($"EnquiryItem: {enquiryItemJson}");
// logger.Debug($"ReqestItem: {requestItemJson}");
// requestItem = BusinessUtils.Update(requestItem);
// RequestItems[RequestItems.FindIndex(p => p.Equals(requestItem))] = requestItem;
// });
// // Create new Request Items
// var enquiryItemsWithNoRequestItem =
// (from e in enquiryItems
// where !requestItems.Any(r =>
// r.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber
// == e.PartNumber)
// select e).ToList();
// logger.LogCollection(nameof(enquiryItemsWithNoRequestItem), enquiryItemsWithNoRequestItem);
// var remainingPartNumbers = enquiryItemsWithNoRequestItem
// .Select(p => p.PartNumber)
// .Distinct().ToList();
// logger.LogCollection(nameof(remainingPartNumbers), remainingPartNumbers);
// foreach (var enquiryItem in enquiryItemsWithNoRequestItem)
// {
// var part = Parts.FirstOrDefault(p => !p.IsAlternate && p.PartNumber == enquiryItem.PartNumber)
// ?? Parts.FirstOrDefault(p => p.IsAlternate && p.AlternateNumber == enquiryItem.PartNumber);
// // There is no reason for a part not to be found.
// System.Diagnostics.Debug.Assert(part.IsNotNull());
// logger.Debug($"Creating RequestItem from Part: {new PartReduced2(part).ToJson()}");
// var requestItem = new QuoteRequestItem
// {
// ParentSurfaceItemId = Quote.itemID,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsAdditionalDescription = part.AdditionalDescription,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription = part.Description,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence = enquiryItem.Sequence,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber = part.MasterNumber,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber = enquiryItem.PartNumber,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityOutstanding = enquiryItem.Quantity,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired = enquiryItem.Quantity,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantitySelected = 0,
// QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG = part.WeightKG
// };
// requestItem = BusinessUtils.Create(requestItem);
// RequestItems.Add(requestItem);
// }
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// }
// private void ProcessInventoryItems()
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var defaultMargin = Quote.QuoteItems_QuoteItems_QuoteItemsDefaultMargin
// .TryParseDecimal().GetValueOrDefault();
// if (InventoryItems.Any())
// {
// //var quoteItems = db.QuoteItems.GetByParentId(quote.itemID).ToList();
// var requestItems = RequestItems
// .OrderBy(p => p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence)
// .ToList();
// foreach (var inventoryItem in InventoryItems)
// {
// // fisrt: check if a quote item already exists
// var storedItem = QuoteItems
// .FirstOrDefault(p => p.QuoteItems_QuoteItems_InventoryItemsUniqueNumber == inventoryItem.UniqueNumber
// && p.QuoteItems_QuoteItems_QuoteItemsBinLocation == inventoryItem.BinLocation);
// if (storedItem.IsNull())
// {
// decimal sellingPrice = BusinessUtils.GetSellingPrice(inventoryItem.AverageCost, defaultMargin);
// var quoteItem = new QuoteItem
// {
// ParentSurfaceItemId = Quote.itemID,
// QuoteItems_QuoteItems_QuoteItemsSellingPrice = sellingPrice,
// QuoteItems_QuoteItems_QuoteItemsMargin = defaultMargin
// }.FillFrom(inventoryItem);
// //get first matched request item
// var linkedReqItem = quoteItem.GetMatchingRequestItem(requestItems);
// System.Diagnostics.Debug.Assert(linkedReqItem.IsNotNull());
// quoteItem.QuoteItems_QuoteItems_QuoteItemsSequenceNumber =
// linkedReqItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence
// .GetValueOrDefault();
// quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber =
// BusinessUtils.GetQuoteItemSupplierSequence(quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplier);
// quoteItem = BusinessUtils.Create(quoteItem);
// QuoteItems.Add(quoteItem);
// }
// else
// {
// storedItem.QuoteItems_QuoteItems_QuoteItemsQuantityAvailable = inventoryItem.Available;
// storedItem = BusinessUtils.Update(storedItem);
// QuoteItems[QuoteItems.FindIndex(p => p.itemID == storedItem.itemID)] = storedItem;
// }
// }
// }
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// }
// public void ProcessSupplierPriceList()
// {
// var defaultMargin = Quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteCustomerDefaultMargin
// .TryParseDecimal().GetValueOrDefault();
// var quoteItems = db.QuoteItems.Where(p => p.ParentSurfaceItemId == Quote.itemID)
// .ToList();
// foreach (var supplierItem in SupplierPriceListParts)
// {
// var existing = quoteItems.FirstOrDefault(p =>
// p.QuoteItems_QuoteItems_QuoteItemsPartNumber == supplierItem.PartNumber
// && p.QuoteItems_QuoteItems_QuoteItemsBrand == supplierItem.Brand
// && p.QuoteItems_QuoteItems_QuoteItemsCondition == supplierItem.Condition
// && p.QuoteItems_QuoteItems_QuoteItemsSupplier == supplierItem.SupplierName
// && p.QuoteItems_QuoteItems_QuoteItemsWarehouseLocation == supplierItem.Note
// );
// // check for existing inactive surplus warehouse items
// if (existing.IsNotNull())
// {
// if (existing.isActive.Value
// || (!existing.isActive.Value && "SURPLUS WAREHOUSE" == existing.QuoteItems_QuoteItems_QuoteItemsSupplier))
// {
// continue;
// }
// }
// if (existing.IsNull())
// {
// var costPrice = BusinessUtils.ApplyExchangeRate(
// supplierItem.SalesPrice, supplierItem.Currency);
// var sellingPrice = BusinessUtils.GetSellingPrice(
// costPrice, defaultMargin);
// var linkedReqItem = RequestItems
// .Where(p =>
// p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber
// == supplierItem.AlternateNumber
// || p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber
// == supplierItem.PartNumber)
// .Select(p => new
// {
// Sequence = p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence,
// WeightKG = p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG,
// Description = p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription,
// MasterNumber = p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber
// }).FirstOrDefault();
// var quoteItem = new QuoteItem
// {
// ParentSurfaceItemId = Quote.itemID,
// QuoteItems_QuoteItems_QuoteItemsBrand = supplierItem.Brand,
// QuoteItems_QuoteItems_QuoteItemsCondition = supplierItem.Condition,
// QuoteItems_QuoteItems_QuoteItemsCostPrice = costPrice,
// QuoteItems_QuoteItems_QuoteItemsLeadTime = supplierItem.LeadTime,
// QuoteItems_QuoteItems_QuoteItemsMargin = defaultMargin,
// QuoteItems_QuoteItems_QuoteItemsPartNumber = supplierItem.PartNumber,
// QuoteItems_QuoteItems_QuoteItemsQuantityAvailable = supplierItem.Availability,
// QuoteItems_QuoteItems_QuoteItemsSellingPrice = sellingPrice,
// QuoteItems_QuoteItems_QuoteItemsSupplier = supplierItem.SupplierName,
// QuoteItems_QuoteItems_QuoteItemsWarehouseLocation = supplierItem.Note,
// QuoteItems_QuoteItems_QuoteItemsSupplierCurrency = supplierItem.Currency,
// QuoteItems_QuoteItems_QuoteItemsWeightKG = supplierItem.WeightKG,
// QuoteItems_QuoteItems_QuoteItemsSequenceNumber = linkedReqItem.Sequence,
// //CVH 2019-01-24 Use Request Master number as Quote Item master number
// QuoteItems_QuoteItems_PartsMasterPartNumber = linkedReqItem.MasterNumber,
// QuoteItems_QuoteItems_QuoteItemsDescription = supplierItem.Description
// .NullOrEmptyCoalesce(linkedReqItem.Description),
// QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber =
// BusinessUtils.GetQuoteItemSupplierSequence(supplierItem.SupplierName)
// };
// quoteItem = BusinessUtils.Create(quoteItem);
// QuoteItems.Add(quoteItem);
// logger.Debug($"Created QuoteItem: {quoteItem.ToJsonIndented()}");
// }
// }
// }
// public void ProcessBuyoutSuppliers()
// {
// var supplierNames = QuoteItemsSupplierNames;
// var buyoutSupplierNames = BuyoutSuppliers
// .Select(p => p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName)
// .ToList();
// // List of supplier names missing from existing buyout supplier names
// var supplierNamesToAdd = supplierNames.Where(p => !buyoutSupplierNames.Contains(p.Supplier))
// .Select(p => p.Supplier).ToList();
// var suppliers = db.Suppliers.Where(p => supplierNamesToAdd.Contains(p.TradingName))
// .OrderBy(p => p.TradingName).ToList();
// if (suppliers.Any())
// {
// var defaultMargin = Quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteCustomerDefaultMargin.TryParseDecimal().GetValueOrDefault();
// var eRates = db.ExchangeRates.ToList();
// var freights = db.Freights.Where(p => supplierNamesToAdd.Contains(p.SupplierTradingName)).ToList();
// suppliers.ForEach(supplier =>
// {
// var contacts = db.Contacts.Where(p => p.ParentSurfaceItemId == supplier.itemID.ToString()).ToList();
// var contactPerson = contacts
// .Select(p => $"{p.ContactDetails_ContactDetails_ContactName} {p.ContactDetails_ContactDetails_ContactSurname}")
// .ToList()
// .StringJoin(", ");
// decimal eRate = 0;
// if (!new string[] { "", "ZAR" }.Contains(supplier.Currency))
// {
// eRate = eRates.FirstOrDefault(p => p.ForexCurrency == supplier.Currency)
// ?.BuyExchangeRate.TryParseDecimal()
// .GetValueOrDefault() ?? 0m;
// }
// var freight = freights.FirstOrDefault(p => p.SupplierTradingName == supplier.TradingName)
// ?.Freight.TryParseDecimal()
// .GetValueOrDefault() ?? 0m;
// var buyoutSupplier = new QuoteBuyoutSupplier
// {
// ParentSurfaceItemId = Quote.itemID,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutQuoteType = BuyoutQuoteType.SelectedItems,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierContactPerson = contactPerson,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierCurrency = supplier.Currency.NullOrEmptyCoalesce("ZAR"),
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDefaultDiscount = supplier.DefaultDiscount,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDefaultLeadtime = supplier.DefaultLeadtime,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierEmail = supplier.PrimaryEmail,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierExchangeRate = eRate,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierFreight = freight,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierStatus = BuyoutStatus.QuoteRequested,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierTelephone = supplier.Telephone,
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierVATRate = supplier.VATRate.TryParseDecimal().GetValueOrDefault(),
// QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName = supplier.TradingName
// };
// buyoutSupplier = BusinessUtils.Create(buyoutSupplier);
// BuyoutSuppliers.Add(buyoutSupplier);
// });
// }
// }
// private void ProcessBuyoutItems()
// {
// var eRates = db.ExchangeRates.ToList();
// BuyoutSuppliers.Select(p => new {
// p.itemID,
// Currency = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierCurrency.NullOrEmptyCoalesce("ZAR"),
// DefaultDiscount = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDefaultDiscount.TryParseDecimal().GetValueOrDefault(),
// DefaultLeadTime = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDefaultLeadtime,
// Duty = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierDuty.GetValueOrDefault(),
// ExchangeRate = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierExchangeRate.GetValueOrDefault(),
// Freight = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierFreight.GetValueOrDefault(),
// VATRate = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierVATRate.GetValueOrDefault(),
// Supplier = p.QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_SupplierTradingName,
// }).ToList().ForEach(buyoutSupplier =>
// {
// var quoteItems = QuoteItems
// .Where(p =>
// p.QuoteItems_QuoteItems_QuoteItemsSupplier == buyoutSupplier.Supplier
// ).Select(p => new
// {
// p.itemID,
// MasterNumber = p.QuoteItems_QuoteItems_PartsMasterPartNumber,
// Brand = p.QuoteItems_QuoteItems_QuoteItemsBrand,
// Condition = p.QuoteItems_QuoteItems_QuoteItemsCondition,
// CostPrice = p.QuoteItems_QuoteItems_QuoteItemsCostPrice.GetValueOrDefault(),
// LeadTime = p.QuoteItems_QuoteItems_QuoteItemsLeadTime,
// PartNumber = p.QuoteItems_QuoteItems_QuoteItemsPartNumber,
// SellingPrice = p.QuoteItems_QuoteItems_QuoteItemsSellingPrice.GetValueOrDefault(),
// Sequence = p.QuoteItems_QuoteItems_QuoteItemsSequenceNumber.GetValueOrDefault(),
// Supplier = p.QuoteItems_QuoteItems_QuoteItemsSupplier,
// Currency = p.QuoteItems_QuoteItems_QuoteItemsSupplierCurrency,
// WeightKG = p.QuoteItems_QuoteItems_QuoteItemsWeightKG.GetValueOrDefault(),
// Description = p.QuoteItems_QuoteItems_QuoteItemsDescription,
// Available = p.QuoteItems_QuoteItems_QuoteItemsQuantityAvailable.GetValueOrDefault(),
// QuantitySelected = p.QuoteItems_QuoteItems_QuoteItemsQuantitySelected.GetValueOrDefault()
// }).ToList();
// var buyoutItems = BuyoutItems.Where(p => p.ParentSurfaceItemId == buyoutSupplier.itemID);
// if (quoteItems.Any())
// {
// foreach(var quoteItem in quoteItems)
// {
// var exists = buyoutItems.Any(p =>
// p.QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber
// == quoteItem.MasterNumber);
// if (exists)
// {
// decimal eRate = buyoutSupplier.ExchangeRate;
// decimal zar = quoteItem.CostPrice;
// decimal forex = 0;
// if (eRate != 0)
// {
// forex = zar / eRate;
// }
// var discount = buyoutSupplier.DefaultDiscount;
// var dutyCost = BusinessUtils.GetSellingPrice(zar, buyoutSupplier.Duty) - zar;
// var freightCost = BusinessUtils.GetSellingPrice(zar, buyoutSupplier.Freight) - zar;
// var totalCost = zar + freightCost + dutyCost - (zar * discount / 100m);
// var buyoutItem = new QuoteBuyoutItem
// {
// ParentSurfaceItemId = buyoutSupplier.itemID,
// QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber = quoteItem.MasterNumber,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsBrand = quoteItem.Brand,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCondition = quoteItem.Condition,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCostPriceZAR = zar,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsCostPriceForex = forex,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDescription = quoteItem.Description,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDiscount = buyoutSupplier.DefaultDiscount,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDutyCost = dutyCost,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsFreightCost = freightCost,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsLeadtime = buyoutSupplier.DefaultLeadTime,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsPartNumber = quoteItem.PartNumber,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityAvailable = quoteItem.Available,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityRequired = quoteItem.QuantitySelected,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsWeightKG = quoteItem.WeightKG,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSequence = quoteItem.Sequence,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSelected = true,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsTotalCost = totalCost
// };
// buyoutItem = BusinessUtils.Create(buyoutItem);
// BuyoutItems.Add(buyoutItem);
// // 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
// var qItem = QuoteItems.First(p => p.itemID == quoteItem.itemID);
// qItem.QuoteItems_QuoteItems_QuoteItemsCostPrice = zar + freightCost + dutyCost - (zar * discount / 100m);
// qItem.CalculateSellingPrice(Quote.QuoteItems_QuoteItems_QuoteItemsTotalMargin.GetValueOrDefault());
// qItem = BusinessUtils.Update(qItem);
// QuoteItems[QuoteItems.FindIndex(p => p.itemID == qItem.itemID)] = qItem;
// }
// }
// }
// else
// {
// BuyoutRequestItems.ForEach(reqItem =>
// {
// var exists = BuyoutItems.Any(p =>
// p.QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber
// == reqItem.MasterNumber
// );
// if (!exists)
// {
// var buyoutItem = new QuoteBuyoutItem {
// ParentSurfaceItemId = Quote.itemID,
// QuoteBuyoutItems_QuoteBuyoutItems_PartsMasterPartNumber = reqItem.MasterNumber,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsDescription = reqItem.Description,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsLeadtime = buyoutSupplier.DefaultLeadTime,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsPartNumber = reqItem.PartNumber,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsQuantityRequired = reqItem.Required,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsWeightKG = reqItem.WeightKG,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSequence = reqItem.Sequence,
// QuoteBuyoutItems_QuoteBuyoutItems_QuoteBuyoutItemsSelected = true
// };
// buyoutItem = BusinessUtils.Create(buyoutItem);
// BuyoutItems.Add(buyoutItem);
// }
// });
// }
// });
// }
// #endregion
// #region QuoteItems post process
// private void RemoveMatchingSurplusWarehouseQuoteItems()
// {
// var context = QuotesContext.Create();
// string supplierName = "SURPLUS WAREHOUSE";
// var masterGroups = (from qi in context.QuoteItems
// where qi.isActive.Value
// && qi.ParentSurfaceItemId == Quote.itemID
// && supplierName == qi.QuoteItems_QuoteItems_QuoteItemsSupplier
// orderby qi.QuoteItems_QuoteItems_QuoteItemsCostPrice descending
// group qi by qi.QuoteItems_QuoteItems_PartsMasterPartNumber into grp
// where grp.Count() > 1
// select grp)
// .ToList();
// foreach (var group in masterGroups)
// {
// var nullOr0 = new int?[] { null, 0 };
// var priciest = group.OrderByDescending(p => p.QuoteItems_QuoteItems_QuoteItemsCostPrice).First();
// context.QuoteItems.RemoveRange(group.Where(p => p.itemID != priciest.itemID
// // item has none selected
// && nullOr0.Contains(p.QuoteItems_QuoteItems_QuoteItemsQuantitySelected)));
// context.SaveChanges();
// }
// }
// public void CheckPreviouslyQuotedPrices()
// {
// var context = QuotesContext.Create();
// var quoteId = Quote.itemID;
// var withinDate = DateTime.Now.AddMonths(-6);
// var currentQuote = context.vQuotes.SingleOrDefault(p => p.itemID == quoteId);
// var quoteItems = context.vQuoteItems.Where(p => p.ParentSurfaceItemId == currentQuote.itemID && !p.IsPriceChecked.Value)
// .ToList();
// if (!quoteItems.Any()) return;
// var hasChanges = new List();
// // get previously quoted items
// foreach (var currentItem in quoteItems.Where(p => !p.IsCatList.Value))
// {
// var prevQuotedItem = (from item in context.vQuoteItems
// join quote in context.vQuotes on item.ParentSurfaceItemId equals quote.itemID
// //join status in SurfaceLookups on quote.None_QuoteInformation_QuoteStatus equals status.Id into tmpStatus
// //from status in tmpStatus.DefaultIfEmpty()
// where quote.IsFinalized.Value
// && quote.QuoteInformation_CustomerTradingName == currentQuote.QuoteInformation_CustomerTradingName
// && item.QuoteItems_QuoteItems_PartsMasterPartNumber == currentItem.QuoteItems_QuoteItems_PartsMasterPartNumber
// //&& item.QuoteItems_QuoteItems_QuoteItemsPartNumber == currentItem.QuoteItems_QuoteItems_QuoteItemsPartNumber
// //&& item.QuoteItems_QuoteItems_QuoteItemsBrand == currentItem.QuoteItems_QuoteItems_QuoteItemsBrand
// && item.QuoteItems_QuoteItems_QuoteItemsCondition == currentItem.QuoteItems_QuoteItems_QuoteItemsCondition
// //&& item.QuoteItems_QuoteItems_QuoteItemsSupplier == currentItem.QuoteItems_QuoteItems_QuoteItemsSupplier
// && item.QuoteItems_QuoteItems_QuoteItemsQuantitySelected > 0
// && quote.None_QuoteInformation_QuoteDate.Value <= currentQuote.None_QuoteInformation_QuoteDate.Value
// && quote.itemID < currentQuote.itemID
// && quote.None_QuoteInformation_QuoteDate.Value >= withinDate
// orderby quote.itemID descending,
// item.QuoteItems_QuoteItems_QuoteItemsSellingPrice.Value descending
// select new { quote, item /*, status*/ })
// .FirstOrDefault();
// var priceCheck = context.QuoteItemsPriceChecks.SingleOrDefault(p => p.QuoteItemId == currentItem.recId)
// ?? new QuoteItemsPriceCheck();
// if (!prevQuotedItem.IsNull())
// {
// priceCheck.NewSellPrice = prevQuotedItem.item.QuoteItems_QuoteItems_QuoteItemsSellingPrice;
// priceCheck.OldSellPrice = currentItem.QuoteItems_QuoteItems_QuoteItemsSellingPrice;
// priceCheck.QuoteDate = prevQuotedItem.quote.None_QuoteInformation_QuoteDate;
// priceCheck.QuoteNumber = prevQuotedItem.quote.QuoteInformation_QuoteNumber;
// priceCheck.ReferenceQuoteItemId = prevQuotedItem.item.recId;
// QuoteItem item = context.QuoteItems.SingleOrDefault(p => p.recId == currentItem.recId);
// item.QuoteItems_QuoteItems_QuoteItemsSellingPrice = priceCheck.NewSellPrice;
// // recalculate margin
// item.QuoteItems_QuoteItems_QuoteItemsMargin = BusinessUtils.GetMargin(
// costPrice: item.QuoteItems_QuoteItems_QuoteItemsCostPrice,
// newSellingPrice: item.QuoteItems_QuoteItems_QuoteItemsSellingPrice);
// }
// else
// {
// priceCheck.OldSellPrice = currentItem.QuoteItems_QuoteItems_QuoteItemsSellingPrice;
// }
// if (priceCheck.QuoteItemId == 0)
// {
// priceCheck.QuoteItemId = currentItem.recId;
// hasChanges.Add(priceCheck);
// }
// }
// if (hasChanges.Any())
// {
// context.QuoteItemsPriceChecks.AddRange(hasChanges);
// }
// context.SaveChanges();
// }
// public void ApplyCATListPrices()
// {
// var context = QuotesContext.Create();
// var quoteId = Quote.itemID;
// var quoteItems = context.vQuoteItems.Where(p => p.ParentSurfaceItemId == quoteId && !p.CatListCostPrice.HasValue)
// .OrderBy(p => p.QuoteItems_QuoteItems_QuoteItemsSequenceNumber)
// .ThenBy(p => p.QuoteItems_QuoteItems_PartsMasterPartNumber)
// .ThenBy(p => p.QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber)
// .ThenBy(p => p.QuoteItems_QuoteItems_QuoteItemsSupplier)
// .ToList();
// var addItems = new List();
// foreach (var quoteItem in quoteItems)
// {
// if (!quoteItem.IsCatList.Value)
// //if (!"CAT".Equals(quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplier))
// {
// // get highest cat list cost price for the master number and set as CatListCostPrice
// var catListCostPrice = quoteItems.Where(catItem => catItem.IsCatList.Value
// && quoteItem.QuoteItems_QuoteItems_PartsMasterPartNumber == catItem.QuoteItems_QuoteItems_PartsMasterPartNumber
// // check condition?
// //&& quoteItem.QuoteItems_QuoteItems_QuoteItemsCondition == catItem.QuoteItems_QuoteItems_QuoteItemsCondition
// )
// .Select(catItem => catItem.QuoteItems_QuoteItems_QuoteItemsCostPrice).Max().GetValueOrDefault();
// if (catListCostPrice == 0)
// {
// catListCostPrice = (from p in context.vQuoteItems
// where p.ParentSurfaceItemId == quoteId
// && p.QuoteItems_QuoteItems_PartsMasterPartNumber == quoteItem.QuoteItems_QuoteItems_PartsMasterPartNumber
// select p)
// .Select(p => p.CatListCostPrice).Max().GetValueOrDefault();
// }
// var priceCheck = context.QuoteItemsPriceChecks.SingleOrDefault(p => p.QuoteItemId == quoteItem.recId)
// ?? new QuoteItemsPriceCheck();
// priceCheck.CatListCostPrice = catListCostPrice;
// if (priceCheck.QuoteItemId == 0)
// {
// priceCheck.QuoteItemId = quoteItem.recId;
// addItems.Add(priceCheck);
// }
// }
// }
// var emptyId = addItems.Where(p => p.QuoteItemId == 0);
// context.QuoteItemsPriceChecks.AddRange(addItems);
// // remove cat list items from result
// //var catItems = QuoteItems.Where(p => p.ParentSurfaceItemId == quoteId && p.QuoteItems_QuoteItems_QuoteItemsSupplier == "CAT");
// //QuoteItems.RemoveRange(catItems);
// context.SaveChanges();
// }
// public void FixQuoteItemsSorting()
// {
// var sortedMasterNumbers = RequestItems
// .OrderBy(p => p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence)
// .Select(p => p.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber)
// .ToArray();
// var quoteItems = QuoteItems
// .OrderBy(p => Array.IndexOf(sortedMasterNumbers, p.QuoteItems_QuoteItems_PartsMasterPartNumber))
// //.ThenBy(p => p.QuoteItems_QuoteItems_QuoteItemsSequenceNumber)
// .ThenBy(p => p.QuoteItems_QuoteItems_PartsMasterPartNumber)
// .ThenBy(p => BusinessUtils.GetQuoteItemSupplierSequence(p.QuoteItems_QuoteItems_QuoteItemsSupplier) /* p.QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber */)
// .ThenBy(p => p.QuoteItems_QuoteItems_QuoteItemsSupplier)
// .ToList();
// }
// #endregion
// #region ProcessEnquiryItems Helpers
// private List CreatePartsFromSupplierCATList(IEnumerable missingPartNumbers)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var foundCatListParts = new List();
// if (missingPartNumbers.Any())
// {
// // look for Supplier CAT List Parts for missing enquiry part numbers
// var catList = db.GetSupplierCATListParts(missingPartNumbers);
// //var catList = db.SupplierCATListParts
// // .Where(p => missingPartNumbers.Contains(p.PartNumber));
// // CVH 2019-01-07 Create a new Part Master record based on CAT item details
// foundCatListParts.AddRange(catList.Select(p => p.PartNumber));
// foreach (var catItem in catList)
// {
// var partsMaster = CreatePartsMasterFromPriceListItem(catItem);
// var partsAlternate = CreateAlternateFromMaster(partsMaster);
// }
// }
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// return foundCatListParts;
// }
// public List CreateNewPartsFromEnquiryItems(IEnumerable partNumbers)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var createdParts = new List();
// foreach(var partNumber in partNumbers)
// {
// var partMaster = CreatePartsMasterFromPartNumber(partNumber);
// var partAlternate = CreateAlternateFromMaster(partMaster);
// }
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// return createdParts;
// }
// public PartsMaster CreatePartsMasterFromPriceListItem(ISupplierPricelistPart priceListPart)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var created = BusinessUtils.Create(new PartsMaster
// {
// AdditionalDescription = priceListPart.AdditionalDescription,
// CEPNumber = BusinessUtils.GetNextCEPNumber(),
// Description = priceListPart.Description,
// ItemVetted = YesNo.No,
// PartNumber = priceListPart.PartNumber,
// MasterNumber = priceListPart.PartNumber,
// WeightKG = priceListPart.WeightKG.GetValueOrDefault(),
// WeightLBS = priceListPart.WeightLBS.GetValueOrDefault(),
// });
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// return created;
// }
// public PartsMaster CreatePartsMasterFromPartNumber(string partNumber)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var created = BusinessUtils.Create(new PartsMaster
// {
// CEPNumber = BusinessUtils.GetNextCEPNumber(),
// ItemVetted = YesNo.No, // No
// MasterNumber = partNumber,
// PartNumber = partNumber
// });
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// return created;
// }
// public PartsAlternate CreateAlternateFromMaster(PartsMaster partsMaster)
// {
// var methodName = MethodInfo.GetCurrentMethod().Name;
// var stopwatch = Stopwatch.StartNew();
// logger.Debug($"Start {methodName} ...");
// var created = BusinessUtils.Create(new PartsAlternate
// {
// CEPNumber = partsMaster.CEPNumber,
// AlternateNumber = partsMaster.MasterNumber,
// PartNumber = partsMaster.MasterNumber,
// ParentSurfaceItemId = partsMaster.itemID
// });
// stopwatch.Stop();
// logger.Debug($"End {methodName} {stopwatch.Elapsed}");
// return created;
// }
// #endregion
// #region Nested Classes
// public class ProcessQuoteResult
// {
// public int QuoteId { get; set; }
// }
// public class PartReduced
// {
// public string PartNumber { get; set; }
// public string AlternateNumber { get; set; }
// public bool IsAlternate { get; set; }
// public PartReduced() { }
// public PartReduced(PartResult part)
// {
// this.AlternateNumber = part.AlternateNumber;
// this.IsAlternate = part.IsAlternate;
// this.PartNumber = part.PartNumber;
// }
// }
// public class PartReduced2
// {
// public string PartNumber { get; set; }
// public string AlternateNumber { get; set; }
// public bool IsAlternate { get; set; }
// public string Description { get; set; }
// public PartReduced2() { }
// public PartReduced2(PartResult part)
// {
// PartNumber = part.PartNumber;
// AlternateNumber = part.AlternateNumber;
// IsAlternate = part.IsAlternate;
// Description = part.Description;
// }
// }
// public class RequestItemReduced
// {
// public int itemID { get; set; }
// public string MasterNumber { get; set; }
// public string PartNumber { get; set; }
// public RequestItemReduced() { }
// public RequestItemReduced(QuoteRequestItem requestItem)
// {
// itemID = requestItem.itemID;
// MasterNumber = requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber;
// PartNumber = requestItem.QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber;
// }
// }
// public class BuyoutRequestItem
// {
// public string MasterNumber { get; set; }
// public string PartNumber { get; set; }
// public string Description { get; set; }
// public decimal WeightKG { get; set; }
// public int Sequence { get; set; }
// public int Required { get; set; }
// }
//#endregion
}
public static class ILoggerExtensions
{
public static void LogCollection(this ILogger logger,
string varName,
IEnumerable collection,
Formatting formatting = Formatting.None)
{
logger.Debug($"${varName}[{collection.Count()}]: {collection.ToJson(formatting)}");
}
}
}