public static POLineItemDTO MapFromDatabaseEntity(POLineItem projectPOLineItem)
{
if (projectPOLineItem == null)
{
return(null);
}
return(new POLineItemDTO
{
Id = projectPOLineItem.Id,
Item = projectPOLineItem.Item,
AccountingTotal = projectPOLineItem.AccountingTotal,
ProjectDescription = projectPOLineItem.ProjectDescription,
Commodity = projectPOLineItem.Commodity,
Account = projectPOLineItem.Account,
CostCode = projectPOLineItem.CostCode,
FixedAsset = projectPOLineItem.FixedAsset,
TargetLocationCode = projectPOLineItem.TargetLocationCode,
ShipTo = projectPOLineItem.ShipTo,
RequestedBy = projectPOLineItem.RequestedBy,
CreatedBy = projectPOLineItem.CreatedBy,
LastUpdatedBy = projectPOLineItem.LastUpdatedBy,
POHeaderId = projectPOLineItem.POHeaderId
});
}
public CoupaImporterJobDefinitionDTO ProcessPurchaseOrderImportJob(Guid jobDefinitionId)
{
var jobDefinition = _coupaImporterRepository.Get(jobDefinitionId);
if (jobDefinition == null)
{
return(null);
}
_unitOfWork.AutoDetectChanges(false);
var pOHeaders = new List <POHeader>();
var purchaseOrders = new List <PurchaseOrderDTO>();
// Here we use 1000 records batch so that we can reduce the number of loops.
var noOfRotation = jobDefinition.CoupaImporterJobDefinitionDetails.Count() / 1000;
noOfRotation += jobDefinition.CoupaImporterJobDefinitionDetails.Count() % 1000 > 0 ? 1 : 0;
for (var i = 0; i < noOfRotation; i++)
{
var strCoupaImporterJobDefinitionDetails = "[ ";
strCoupaImporterJobDefinitionDetails += string.Join(", ", jobDefinition.CoupaImporterJobDefinitionDetails
.Skip(i * 1000).Take(1000).Select(x => x.RawContent));
strCoupaImporterJobDefinitionDetails += " ]";
// Convert the RawContent to POHeader
pOHeaders.AddRange(JsonConvert.DeserializeObject <List <POHeader> >(strCoupaImporterJobDefinitionDetails));
// Convert the RawContent to PurchaseOrderDTO
purchaseOrders.AddRange(JsonConvert.DeserializeObject <List <PurchaseOrderDTO> >(strCoupaImporterJobDefinitionDetails));
}
// In every InvoiceHeader Entity ProjectId is added
pOHeaders.ForEach(i => { i.ProjectId = jobDefinition.ProjectId; i.Id = Guid.NewGuid(); });
// Grouping InvoiceHeaders in terms of Unique combination of Supplier & InvoiceNumber
pOHeaders = pOHeaders.GroupBy(i => new { i.PurchaseOrderNumber, i.Supplier }).Select(x => x.First()).ToList();
foreach (var objPoHeader in pOHeaders)
{
// Allocate all line items for a invoice header + InvoiceHeaderId added in every associated Line Items.
objPoHeader.POLineItems = purchaseOrders.Where(x => x.Supplier == objPoHeader.Supplier && x.PurchaseOrderNumber == objPoHeader.PurchaseOrderNumber)
.Select(x => POLineItem.MapFromDomainEntity(PurchaseOrderDTO.MapToPOLineItemDTO(x))).ToList();
objPoHeader.POLineItems.ToList().ForEach(x => { x.POHeaderId = objPoHeader.Id; x.Id = Guid.NewGuid(); });
_projectPurchaseOrderRepository.Add(objPoHeader);
}
// At last update all job definition details records
jobDefinition.CoupaImporterJobDefinitionDetails.ToList().ForEach(c => { c.IsProcessed = true; c.IsSuccessful = true; });
_coupaImporterRepository.UpdateAllJobDefinitionDetail(jobDefinitionId, jobDefinition.CoupaImporterJobDefinitionDetails);
jobDefinition.Status = (int)CoupaImporterStatus.Processed;
_coupaImporterRepository.Update(jobDefinitionId, jobDefinition);
_unitOfWork.SaveChanges();
_unitOfWork.AutoDetectChanges(true);
return(CoupaImporterJobDefinitionDTO.MapFromDatabaseEntity(jobDefinition));
}
public void MapFromDomainEntity_NullContent_ReturnNull()
{
//Act
var response = POLineItem.MapFromDomainEntity(null);
//Assert
Assert.IsNull(response);
}
public void MapFromDomainEntity_ValidEntity_ReturnDTOEntity()
{
//Arrange
var pOLineItem = new POLineItemDTO
{
Id = Guid.NewGuid(),
Account = "6063-521-5412424-203050-SN-00000-000-000",
AccountingTotal = 5608.64,
Commodity = "Office Supplies (5410615 Office Supplies)",
CostCode = "C1001 - Interior Construction - General",
CreatedBy = "Doris Ma",
FixedAsset = "",
Item = "Mindshare Social Media SOW",
LastUpdatedBy = "Ritu Vij",
POHeaderId = Guid.NewGuid(),
ProjectDescription = "",
RequestedBy = "Andy Lim",
ShipTo = "Andy Lim",
TargetLocationCode = "400 Bellevue Parkway, Wilmington - DE2 (21)",
};
//Act
var response = POLineItem.MapFromDomainEntity(pOLineItem);
//Assert
Assert.IsNotNull(response);
Assert.AreEqual(pOLineItem.Id, response.Id);
Assert.AreEqual(pOLineItem.Account, response.Account);
Assert.AreEqual(pOLineItem.AccountingTotal, response.AccountingTotal);
Assert.AreEqual(pOLineItem.Commodity, response.Commodity);
Assert.AreEqual(pOLineItem.CostCode, response.CostCode);
Assert.AreEqual(pOLineItem.CreatedBy, response.CreatedBy);
Assert.AreEqual(pOLineItem.FixedAsset, response.FixedAsset);
Assert.AreEqual(pOLineItem.Item, response.Item);
Assert.AreEqual(pOLineItem.LastUpdatedBy, response.LastUpdatedBy);
Assert.AreEqual(pOLineItem.POHeaderId, response.POHeaderId);
Assert.AreEqual(pOLineItem.ProjectDescription, response.ProjectDescription);
Assert.AreEqual(pOLineItem.RequestedBy, response.RequestedBy);
Assert.AreEqual(pOLineItem.ShipTo, response.ShipTo);
Assert.AreEqual(pOLineItem.TargetLocationCode, response.TargetLocationCode);
}
public POLineItem AddLineItem(POLineItem poLineItem)
{
return(_context.PurchaseOrderLineItems.Add(poLineItem).Entity);
}
public POLineItemDTO AddLineItem(POLineItemDTO poLineItemDTO)
{
var response = _projectPurchaseOrderRepository.AddLineItem(POLineItem.MapFromDomainEntity(poLineItemDTO));
return(POLineItemDTO.MapFromDatabaseEntity(response));
}
private string InsertIntoPOReceipts(POLineItem pPOLine, POReceipt pPOReceipt)
{
try
{
cmd.CommandText = @"
INSERT INTO [dbo].[POReceipts]
(
BusinessUnitCode,
BusinessUnit,
PONumber,
ReceiptNumber, DeliveryDate, ItemId, QuantityOrdered, QuantityReceived, QuantityRemaining,
UnitOfMeasure, UnitPrice, TotalPrice, VendorID, Description, POLineNumber,
IssueType, CurrencyLineCost, CurrencyUnitCost, LoadedCost, StatusDescription,
PackingSlipNum, Status, Tax1, Tax2, Consignment, MatRecTransID
) VALUES (
@BUSINESSUNITCODE,
@BUSINESSUNITNAME,
@PONUMBER,
@RECEIPTNUMBER, @DELIVERYDATE, @ITEMID, @QUANTITYORDERED, @QUANTITYRECEIVED, @QUANTITYREMAINING,
@UNITOFMEASURE, @UNITPRICE, @TOTALPRICE, @VENDORID, @DESCRIPTION, @POLINENUMBER,
@ISSUETYPE, @CURRENCYLINECOST, @CURRENCYUNITCOST, @LOADEDCOST, @STATUSDESCRIPTION,
@PACKINGSLIPNUM, @STATUS, @TAX1, @TAX2, @CONSIGNMENT, @MATRECTRANSID
)";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@BUSINESSUNITCODE", "2022"));
cmd.Parameters.Add(new SqlParameter("@BUSINESSUNITNAME", "Kaiser"));
cmd.Parameters.Add(new SqlParameter("@PONUMBER", poheader.ponum == null ? "" : poheader.ponum));
cmd.Parameters.Add(new SqlParameter("@RECEIPTNUMBER", ""));
cmd.Parameters.Add(new SqlParameter("@DELIVERYDATE", (object)pPOReceipt.actualdate ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@ITEMID", pPOLine.itemnum == null ? "" : pPOLine.itemnum));
cmd.Parameters.Add(new SqlParameter("@QUANTITYORDERED", pPOReceipt.quantity == null ? 0 : pPOReceipt.quantity));
cmd.Parameters.Add(new SqlParameter("@QUANTITYRECEIVED", pPOReceipt.quantity == null ? 0 : pPOReceipt.quantity));
cmd.Parameters.Add(new SqlParameter("@QUANTITYREMAINING", pPOReceipt.quantity == null ? 0 : pPOReceipt.quantity));
cmd.Parameters.Add(new SqlParameter("@UNITOFMEASURE", pPOLine.orderunit == null ? "" : pPOLine.orderunit));
cmd.Parameters.Add(new SqlParameter("@UNITPRICE", pPOLine.unitcost == null ? 0 : pPOLine.unitcost));
cmd.Parameters.Add(new SqlParameter("@TOTALPRICE", pPOReceipt.loadedcost == null ? 0 : pPOReceipt.loadedcost));
cmd.Parameters.Add(new SqlParameter("@VENDORID", poheader.vendor == null ? "" : poheader.vendor));
cmd.Parameters.Add(new SqlParameter("@DESCRIPTION", pPOReceipt.status_description == null ? "" : pPOReceipt.status_description));
cmd.Parameters.Add(new SqlParameter("@POLINENUMBER", pPOLine.polinenum == null ? 0 : pPOLine.polinenum));
cmd.Parameters.Add(new SqlParameter("@ISSUETYPE", pPOReceipt.issuetype == null ? "" : pPOReceipt.issuetype));
cmd.Parameters.Add(new SqlParameter("@CURRENCYLINECOST", pPOReceipt.currencylinecost == null ? 0 : pPOReceipt.currencylinecost));
cmd.Parameters.Add(new SqlParameter("@CURRENCYUNITCOST", pPOReceipt.currencyunitcost == null ? 0 : pPOReceipt.currencyunitcost));
cmd.Parameters.Add(new SqlParameter("@LOADEDCOST", pPOReceipt.loadedcost == null ? 0 : pPOReceipt.loadedcost));
cmd.Parameters.Add(new SqlParameter("@STATUSDESCRIPTION", pPOReceipt.status_description == null ? "" : pPOReceipt.status_description));
cmd.Parameters.Add(new SqlParameter("@PACKINGSLIPNUM", pPOReceipt.packingslipnum == null ? "" : pPOReceipt.packingslipnum));
cmd.Parameters.Add(new SqlParameter("@STATUS", pPOReceipt.status == null ? "" : pPOReceipt.status));
cmd.Parameters.Add(new SqlParameter("@TAX1", pPOReceipt.tax1 == null ? 0 : pPOReceipt.tax1));
cmd.Parameters.Add(new SqlParameter("@TAX2", pPOReceipt.tax2 == null ? 0 : pPOReceipt.tax2));
cmd.Parameters.Add(new SqlParameter("@CONSIGNMENT", pPOReceipt.consignment == null ? false : pPOReceipt.consignment));
cmd.Parameters.Add(new SqlParameter("@MATRECTRANSID", pPOReceipt.matrectransid == null ? 0 : pPOReceipt.matrectransid));
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tr.Rollback();
return("insertion failed (InsertIntoPOReceipts): " + ex.Message);
}
return("succeeded");
}
private string InsertIntoPOLineItems(POLineItem pPOLine)
{
decimal tax1 = 0, tax2 = 0;
try
{
#region [Get taxrate from OGAPS]
if (pPOLine.tax1code != null)
{
using (SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["OGAPS"].ConnectionString))
{
decimal taxrate = 0;
sqlconn.Open();
SqlCommand sqlcmd = sqlconn.CreateCommand();
sqlcmd.CommandText = @"SELECT TaxRate FROM Kaiser_TaxCodes WHERE TaxCode = @TAXCODE AND TaxType = 'Tax1Code'";
sqlcmd.Parameters.Clear();
sqlcmd.Parameters.Add(new SqlParameter("@TAXCODE", pPOLine.tax1code));
SqlDataReader dreader = sqlcmd.ExecuteReader();
while (dreader.Read())
{
taxrate = (decimal)dreader.GetValue(0);
break;
}
tax1 = pPOLine.linecost * taxrate;
}
}
if (pPOLine.tax2code != null && poheader.siteid != "LONDON")
{
using (SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["OGAPS"].ConnectionString))
{
decimal taxrate = 0;
sqlconn.Open();
SqlCommand sqlcmd = sqlconn.CreateCommand();
sqlcmd.CommandText = @"SELECT TaxRate FROM Kaiser_TaxCodes WHERE TaxCode = @TAXCODE AND TaxType = 'Tax2Code'";
sqlcmd.Parameters.Clear();
sqlcmd.Parameters.Add(new SqlParameter("@TAXCODE", pPOLine.tax2code));
SqlDataReader dreader = sqlcmd.ExecuteReader();
while (dreader.Read())
{
taxrate = (decimal)dreader.GetValue(0);
break;
}
tax2 = pPOLine.linecost * taxrate;
}
}
#endregion
cmd.CommandText = @"
INSERT INTO [dbo].[POLineItems]
(
BusinessUnitCode,
BusinessUnitName,
PONumber,
LineId,
LineNumber, PartNumber, UoQ, QuantityOrdered, QuantityReceived, QuantityRemaining,
UnitPrice, Description, LineTotal, SupplierID, LineType, Status,
GLCode, LoadedCost, ChargeStore, TAXExempt, ItemNum, ProRateService,
ReceiptsComplete, Tax1, Tax2, Consignment, StoreLoc, CatalogCode, ReceiptReqd, Tax1Code, Tax2Code
) VALUES (
@BUSINESSUNITCODE,
@BUSINESSUNITNAME,
@PONUMBER,
@LINEID,
@LINENUMBER, @PARTNUMBER, @UOQ, @QUANTITYORDERED, @QUANTITYRECEIVED, @QUANTITYREMAINING,
@UNITPRICE, @DESCRIPTION, @LINETOTAL, @SUPPLIERID, @LINETYPE, @STATUS,
@GLCODE, @LOADEDCOST, @CHARGESTORE, @TAXEXEMPT, @ITEMNUM, @PRORATESERVICE,
@RECEIPTSCOMPLETE, @TAX1, @TAX2, @CONSIGNMENT, @STORELOC, @CATALOGCODE, @RECEIPTREQD, @TAX1CODE, @TAX2CODE
)";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@BUSINESSUNITCODE", "2022"));
cmd.Parameters.Add(new SqlParameter("@BUSINESSUNITNAME", "Kaiser"));
cmd.Parameters.Add(new SqlParameter("@PONUMBER", poheader.ponum == null ? "" : poheader.ponum));
cmd.Parameters.Add(new SqlParameter("@LINEID", pPOLine.polinenum == null ? 0 : pPOLine.polinenum));
cmd.Parameters.Add(new SqlParameter("@LINENUMBER", pPOLine.polinenum == null ? 0 : pPOLine.polinenum));
cmd.Parameters.Add(new SqlParameter("@PARTNUMBER", pPOLine.itemnum == null ? "" : pPOLine.itemnum));
cmd.Parameters.Add(new SqlParameter("@UOQ", pPOLine.orderunit == null ? "" : pPOLine.orderunit));
cmd.Parameters.Add(new SqlParameter("@QUANTITYORDERED", pPOLine.orderqty == null ? 0 : pPOLine.orderqty));
cmd.Parameters.Add(new SqlParameter("@QUANTITYRECEIVED", pPOLine.orderqty == null ? 0 : pPOLine.orderqty));
cmd.Parameters.Add(new SqlParameter("@QUANTITYREMAINING", pPOLine.orderqty == null ? 0 : pPOLine.orderqty));
cmd.Parameters.Add(new SqlParameter("@UNITPRICE", pPOLine.unitcost == null ? 0 : pPOLine.unitcost));
cmd.Parameters.Add(new SqlParameter("@DESCRIPTION", pPOLine.description == null ? "" : pPOLine.description));
cmd.Parameters.Add(new SqlParameter("@LINETOTAL", pPOLine.linecost == null ? 0 : pPOLine.linecost));
cmd.Parameters.Add(new SqlParameter("@SUPPLIERID", poheader.vendor == null ? "" : poheader.vendor));
cmd.Parameters.Add(new SqlParameter("@LINETYPE", pPOLine.linetype == null ? "" : pPOLine.linetype));
cmd.Parameters.Add(new SqlParameter("@STATUS", ""));
cmd.Parameters.Add(new SqlParameter("@GLCODE", pPOLine.gldebitacct == null ? "" : pPOLine.gldebitacct));
cmd.Parameters.Add(new SqlParameter("@LOADEDCOST", pPOLine.loadedcost == null ? 0 : pPOLine.loadedcost));
cmd.Parameters.Add(new SqlParameter("@CHARGESTORE", pPOLine.chargestore == null ? false : pPOLine.chargestore));
cmd.Parameters.Add(new SqlParameter("@TAXEXEMPT", pPOLine.taxexempt == null ? false : pPOLine.taxexempt));
cmd.Parameters.Add(new SqlParameter("@ITEMNUM", pPOLine.itemnum == null ? "" : pPOLine.itemnum));
cmd.Parameters.Add(new SqlParameter("@PRORATESERVICE", pPOLine.prorateservice == null ? false : pPOLine.prorateservice));
cmd.Parameters.Add(new SqlParameter("@RECEIPTSCOMPLETE", pPOLine.receiptscomplete == null ? false : pPOLine.receiptscomplete));
cmd.Parameters.Add(new SqlParameter("@TAX1", pPOLine.tax1code == null ? pPOLine.tax1 : tax1));
cmd.Parameters.Add(new SqlParameter("@TAX2", pPOLine.tax2code == null ? pPOLine.tax2 : tax2));
cmd.Parameters.Add(new SqlParameter("@CONSIGNMENT", pPOLine.consignment == null ? false : pPOLine.consignment));
cmd.Parameters.Add(new SqlParameter("@STORELOC", pPOLine.storeloc == null ? "" : pPOLine.storeloc));
cmd.Parameters.Add(new SqlParameter("@CATALOGCODE", pPOLine.catalogcode == null ? "" : pPOLine.catalogcode));
cmd.Parameters.Add(new SqlParameter("@RECEIPTREQD", pPOLine.receiptreqd == null ? false : pPOLine.receiptreqd));
cmd.Parameters.Add(new SqlParameter("@TAX1CODE", pPOLine.tax1code == null ? "" : pPOLine.tax1code));
cmd.Parameters.Add(new SqlParameter("@TAX2CODE", pPOLine.tax2code == null ? "" : pPOLine.tax2code));
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tr.Rollback();
return("insertion failed (InsertIntoPOLineItems): " + ex.Message);
}
return("succeeded");
}
