// GET: CustomEstimateCost/Create
public IActionResult Create(int id)
{
DateTime today = DateTime.UtcNow;
EstimateController controller = new EstimateController(_context, _config);
CustomEstimateCostCreateViewModel viewModel = new CustomEstimateCostCreateViewModel {
EstimateId = id,
Estimate = controller.GetEstimateById(id),
CustomCosts = new List <CustomEstimateCost>(),
};
CustomEstimateCost customCost = new CustomEstimateCost {
EstimateId = id,
ItemName = "",
UnitOfMeasure = "",
Category = "Custom",
Quantity = 0
};
viewModel.CustomCosts.Add(customCost);
ViewData["EstimateId"] = id;
return(View(viewModel));
}
public async Task <IActionResult> Edit(int id,
[Bind("Id,ItemName,EstimateId,Category,UnitOfMeasure,CostPerUnit,MarkupPercent,Quantity")]
CustomEstimateCost customEstimateCost)
{
if (id != customEstimateCost.Id)
{
return(NotFound());
}
ModelState.Remove("Estimate");
if (ModelState.IsValid)
{
_context.Update(customEstimateCost);
await _context.SaveChangesAsync();
return(RedirectToAction("Details", "Estimate", new { id = customEstimateCost.EstimateId }));
}
EstimateController controller = new EstimateController(_context, _config);
customEstimateCost.Estimate = controller.GetEstimateById(customEstimateCost.EstimateId);
ViewData["ProjectId"] = customEstimateCost.EstimateId;
return(View(customEstimateCost));
}
public Estimate GetEstimateById(int?id)
{
using (SqlConnection conn = Connection) {
conn.Open();
using (SqlCommand cmd = conn.CreateCommand()) {
cmd.CommandText = @"SELECT e.id AS EstimateId, e.ProjectTitle, e.EstimateDate,
e.ExpirationDate, c.Id AS CustomerId, c.FirstName, c.LastName,
c.PhoneNumber, c.Email, ec.Id AS EstimateCostId, ci.Id AS CostItemId,
ci.ItemName, um.Id AS UnitId, um.UnitName, cc.Id AS CostCategoryId,
cc.CategoryName, cc.MarkupPercent, cpu.Cost, ec.Quantity, cec.Id AS CustomCostID,
cec.ItemName AS CustomItem, cec.CostPerUnit AS CustomCPU, cec.Quantity AS CustomQuantity,
cec.MarkupPercent, cec.UnitOfMeasure AS CustomUnits, cec.Category AS CustomCategory
FROM Estimate e
LEFT JOIN Customer c ON e.CustomerId = c.Id
LEFT JOIN EstimateCost ec on ec.EstimateId = e.id
LEFT JOIN CustomEstimateCost cec on cec.EstimateId = e.id
LEFT JOIN CostItem ci ON ec.CostItemId = ci.Id
LEFT JOIN CostPerUnit cpu ON cpu.CostItemId = ec.CostItemId AND cpu.EndDate IS NULL
LEFT JOIN CostCategory cc ON ci.CostCategoryId = cc.Id
LEFT JOIN UnitOfMeasure um ON ci.UnitOfMeasureId = um.Id
WHERE e.Id = @id
ORDER BY e.EstimateDate DESC;";
cmd.Parameters.Add(new SqlParameter("@id", id));
SqlDataReader reader = cmd.ExecuteReader();
Estimate estimate = null;
while (reader.Read())
{
if (estimate == null)
{
estimate = new Estimate {
Id = reader.GetInt32(reader.GetOrdinal("EstimateId")),
ProjectTitle = reader.GetString(reader.GetOrdinal("ProjectTitle")),
EstimateDate = reader.GetDateTime(reader.GetOrdinal("EstimateDate")),
ExpirationDate = reader.GetDateTime(reader.GetOrdinal("ExpirationDate")),
Customer = new Customer {
Id = reader.GetInt32(reader.GetOrdinal("CustomerId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
Email = reader.GetString(reader.GetOrdinal("Email")),
PhoneNumber = reader.GetString(reader.GetOrdinal("PhoneNumber"))
},
EstimateCosts = new List <EstimateCost>(),
Categories = new List <CostCategory>(),
CustomCosts = new List <CustomEstimateCost>()
};
}
if (!reader.IsDBNull(reader.GetOrdinal("CustomCostId")))
{
int customCostId = reader.GetInt32(reader.GetOrdinal("CustomCostId"));
if (!estimate.CustomCosts.Any(cpc => cpc.Id == customCostId))
{
CustomEstimateCost customCost = new CustomEstimateCost {
Id = reader.GetInt32(reader.GetOrdinal("CustomCostId")),
ItemName = reader.GetString(reader.GetOrdinal("CustomItem")),
CostPerUnit = reader.GetDouble(reader.GetOrdinal("CustomCPU")),
Quantity = reader.GetInt32(reader.GetOrdinal("CustomQuantity")),
UnitOfMeasure = reader.GetString(reader.GetOrdinal("CustomUnits")),
Category = reader.GetString(reader.GetOrdinal("CustomCategory")),
MarkupPercent = reader.GetDouble(reader.GetOrdinal("MarkupPercent"))
};
estimate.CustomCosts.Add(customCost);
}
}
if (!reader.IsDBNull(reader.GetOrdinal("EstimateCostId")))
{
int estimateCostId = reader.GetInt32(reader.GetOrdinal("EstimateCostId"));
if (!estimate.EstimateCosts.Any(ec => ec.Id == estimateCostId))
{
EstimateCost estimateCost = new EstimateCost {
Id = reader.GetInt32(reader.GetOrdinal("EstimateCostId")),
Quantity = reader.GetDouble(reader.GetOrdinal("Quantity")),
CostItem = new CostItem {
Id = reader.GetInt32(reader.GetOrdinal("CostItemId")),
ItemName = reader.GetString(reader.GetOrdinal("ItemName")),
UnitOfMeasure = new UnitOfMeasure {
Id = reader.GetInt32(reader.GetOrdinal("UnitId")),
UnitName = reader.GetString(reader.GetOrdinal("UnitName"))
},
CostCategory = new CostCategory {
Id = reader.GetInt32(reader.GetOrdinal("CostCategoryId")),
CategoryName = reader.GetString(reader.GetOrdinal("CategoryName")),
MarkupPercent = reader.GetDouble(reader.GetOrdinal("MarkupPercent"))
},
},
CostPerUnit = reader.GetDouble(reader.GetOrdinal("Cost"))
};
estimate.EstimateCosts.Add(estimateCost);
}
}
if (!reader.IsDBNull(reader.GetOrdinal("CostCategoryId")))
{
int costCategoryId = reader.GetInt32(reader.GetOrdinal("CostCategoryId"));
if (!estimate.Categories.Any(c => c.Id == costCategoryId))
{
CostCategory category = new CostCategory {
Id = reader.GetInt32(reader.GetOrdinal("CostCategoryId")),
CategoryName = reader.GetString(reader.GetOrdinal("CategoryName")),
MarkupPercent = reader.GetDouble(reader.GetOrdinal("MarkupPercent"))
};
estimate.Categories.Add(category);
}
}
}
reader.Close();
return(estimate);
}
}
}
public async Task <IActionResult> Create(CustomEstimateCostCreateViewModel customEstimateCosts)
{
List <CustomEstimateCost> CustomEstimateCostsInContext = await _context.CustomEstimateCost
.Where(cec => cec.EstimateId == customEstimateCosts.EstimateId).ToListAsync();
List <CustomEstimateCost> CostsEntered = (customEstimateCosts.CustomCosts?.Count > 0) ?
customEstimateCosts.CustomCosts : customEstimateCosts.RejectedEntries;
List <CustomEstimateCost> RejectecdEntries = new List <CustomEstimateCost>();
List <CustomEstimateCost> UpdatedRecords = new List <CustomEstimateCost>();
Estimate Estimate = await _context.Estimate.FirstOrDefaultAsync(e => e.Id == customEstimateCosts.EstimateId);
foreach (var cost in CostsEntered.ToList())
{
CustomEstimateCost ExistingCost = new CustomEstimateCost();
if (cost.ItemName != null)
{
ExistingCost = CustomEstimateCostsInContext
.FirstOrDefault(cec => cec.EstimateId == cost.EstimateId && cec.ItemName.ToUpper() == cost.ItemName.ToUpper());
}
else
{
ExistingCost = null;
}
if (cost.ItemName == null || cost.UnitOfMeasure == null || cost.CostPerUnit <= 0 ||
cost.Quantity == 0 || cost.MarkupPercent <= 0)
{
CostsEntered.Remove(cost);
RejectecdEntries.Add(cost);
}
if (ExistingCost != null)
{
ExistingCost.Quantity += cost.Quantity;
CostsEntered.Remove(cost);
UpdatedRecords.Add(ExistingCost);
_context.Update(ExistingCost);
await _context.SaveChangesAsync();
}
}
foreach (var estimateCost in CostsEntered)
{
_context.Add(estimateCost);
await _context.SaveChangesAsync();
}
if (RejectecdEntries.Count > 0 || UpdatedRecords.Count > 0)
{
CustomEstimateCostCreateViewModel viewModel = new CustomEstimateCostCreateViewModel {
EstimateId = customEstimateCosts.EstimateId,
RejectedEntries = RejectecdEntries,
UpdatedRecords = UpdatedRecords
};
ViewData["EstimateId"] = customEstimateCosts.EstimateId;
return(View("CreateFinish", viewModel));
}
return(RedirectToAction("Details", "Estimate", new { id = customEstimateCosts.EstimateId }));
}
