Como relacionar tabelas em groupby e somar linha e coluna e Where (asp-route-id)
Primeira tabela é tabela de lista de nomes de usinas
public class PowerPlants
{
public int ID { get; set; }
[Required]
public string PowerPlantName { get; set; }
[Required]
public string PowerPlantShortName { get; set; }
[Required]
public string PowerPlantCode { get; set; }
public virtual ICollection<REHPViewModelData> REHPData { get; set; }
public virtual ICollection<BudgetYearlyAllocation> BudgetYearlyAllocation { get; set; }
}
A segunda tabela é a tabela BudgetYear
public class BudgetYear
{
public int ID { get; set; }
[Required]
public string BudgetYearName { get; set; }
public virtual ICollection<BudgetYearlyAllocation> BudgetYearlyAllocation { get; set; }
public virtual ICollection<REHPViewModelData> REHPData { get; set; }
}
Três é a tabela de alocação de cota. Esses dados da tabela sempre atribuem uma alocação de orçamento anual às nossas fábricas, mas não vemos o usuário cliente desses dados.
public class BudgetYearlyAllocation
{
public int ID { get; set; }
// This is Budget Head of our departments for power plants
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaTeen { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaTwo { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaFour { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaFive { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaSix { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaSeven { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal AdminThaTwoOneSeven{ get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaOneOneFour { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaTwo { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaFour { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaFiveOne { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaFiveTwo { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaSeven { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaEight { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaNine { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal MachineThaOneOneSeven { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal CapitalThaThreeOne { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal CapitalThaFourOne { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal CapitalThaFourThree { get; set; }
//This is client user assign for relationship dropdown list add or not add this Part relationship please help me.......
public string ShortDescription { get; set; }
//relationship for BudgetYearID
public int BudgetYearID { get; set; }
[ForeignKey("BudgetYearID")]
public virtual BudgetYear BudgetYear { get; set; }
// relationship for powerplantID
public int PowerPlantID { get; set; }
[ForeignKey("PowerPlantID")]
public virtual PowerPlants PowerPlants { get; set; }
public virtual ICollection<REHPViewModelData> REHPData { get; set; }
}
A tabela de entrada de dados de quatro usuários é
public class REHPViewModelData
{
public int ID { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? DLPermit { get; set; }
public string WANo { get; set; }
[Required]
public string WorkNo { get; set; }
[Required]
public string Description { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? WorkDurationStartDate { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? WorkDurationEnadDate { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal SubmittedAmount { get; set; }
[Required]
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal ApprovedAmount { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? ReachDateToREHP { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? ReachDateToCE { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? ReachDateToMD { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? ReachDateToEPGEEC { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? ReachDateToMOEE { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? ReachDateToMOEEEC { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? BudgetSubmitDate { get; set; }
[DataType(DataType.Date)]
[DisplayFormat( DataFormatString = "{0:dd/MM/yyyy}")]
public DateTime? BudgetPermitDate { get; set; }
[DisplayFormat(DataFormatString = "{0:N}")]
public decimal? PermittedAmount { get; set; }
public string UploadFilePath { get; set; }
//Relationship for PowerPlantID
public int PowerPlantID { get; set; }
[ForeignKey("PowerPlantID")]
public virtual PowerPlants PowerPlants { get; set; }
//Relationship for BudgetHeadID
public int BudgetHeadID { get; set; }
[ForeignKey("BudgetHeadID")]
public virtual BudgetHead BudgetHead { get; set; }
[Required]
public int CurrencyID { get; set; }
[ForeignKey("CurrencyID")]
public virtual Currency Currency { get; set; }
[Required]
public int WorkCategoriesID { get; set; }
[ForeignKey("WorkCategoriesID")]
public virtual WorkCategories WorkCategories { get; set; }
[Required]
public int ConfirmID { get; set; }
[ForeignKey("ConfirmID")]
public virtual Confirm Confirm { get; set; }
[Required]
public int AuthorityID { get; set; }
[ForeignKey("AuthorityID")]
public virtual BudgetAuthority BudgetAuthority { get; set; }
//Relationship of budgetyearID
[Required]
public int BudgetYearID { get; set; }
[ForeignKey("BudgetYearID")]
public virtual BudgetYear BudgetYear { get; set; }
//Relationship of budgetyearAllocationID this part is add or not add please advice because yearly budget allocation amount of powerplants - used budgethead amount and for calculation sum column and row.
public int BudgetYearAllocationID { get; set; }
[ForeignKey("BudgetYearAllocationID")]
public virtual BudgetYearlyAllocation BudgetYearlyAllocation{ get; set; }
}
Cinco Tabela BudgetHead Esta tabela da coluna de dados BudgetHeadName é igual à tabela BudgetYearlyAllocation em propriedades decimais. Essa relação de tabela com BudgetYearlyAllocation precisa ou não.
public class BudgetHead
{
public int ID { get; set; }
[Required]
public string BudgetHeadName { get; set; }
[Required]
public string BudgetHeadDescription { get; set; }
public virtual ICollection<REHPViewModelData> REHPData { get; set; }
}
Em seguida, meu HomeController de BudgetResultTable () no LINQ With groupby de PlantName e
public IActionResult BudgetResultTable(int id)
{
var query = _context.REHPData.Include(r => r.PowerPlants).Where(r=>r.BudgetYearID == id).GroupBy(r => r.PowerPlantID).Select(s => new BudgetResultTable
{
PowerPlantID = s.Key,
PowerPlantName = s.Select(p => p.PowerPlants.PowerPlantName).First(),
//Powerplants of budget head with yearly allocation from BudgetYearlyAllocationTable data
AdminThaTeen = s.Select(x => x.BudgetYearlyAllocation.AdminThaTeen).First(),
AdminThaTwo = s.Select(x => x.BudgetYearlyAllocation.AdminThaTwo).First(),
AdminThaFour = s.Select(x => x.BudgetYearlyAllocation.AdminThaFour).First(),
AdminThaFive = s.Select(x => x.BudgetYearlyAllocation.AdminThaFive).First(),
AdminThaSix = s.Select(x => x.BudgetYearlyAllocation.AdminThaSix).First(),
AdminThaSeven = s.Select(x => x.BudgetYearlyAllocation.AdminThaSeven).First(),
AdminThaTwoOneSeven = s.Select(x => x.BudgetYearlyAllocation.AdminThaTwoOneSeven).First(),
MachineThaOneOneFour = s.Select(x => x.BudgetYearlyAllocation.MachineThaOneOneFour).First(),
MachineThaTwo = s.Select(x => x.BudgetYearlyAllocation.MachineThaTwo).First(),
MachineThaFour = s.Select(x => x.BudgetYearlyAllocation.MachineThaFour).First(),
MachineThaFiveOne = s.Select(x => x.BudgetYearlyAllocation.MachineThaFiveOne).First(),
MachineThaFiveTwo = s.Select(x => x.BudgetYearlyAllocation.MachineThaFiveTwo).First(),
MachineThaSeven = s.Select(x => x.BudgetYearlyAllocation.MachineThaSeven).First(),
MachineThaEight = s.Select(x => x.BudgetYearlyAllocation.MachineThaEight).First(),
MachineThaNine = s.Select(x => x.BudgetYearlyAllocation.MachineThaNine).First(),
MachineThaOneOneSeven = s.Select(x => x.BudgetYearlyAllocation.MachineThaOneOneSeven).First(),
CapitalThaThreeOne = s.Select(x => x.BudgetYearlyAllocation.CapitalThaThreeOne).First(),
CapitalThaFourOne = s.Select(x => x.BudgetYearlyAllocation.CapitalThaFourOne).First(),
CapitalThaFourThree = s.Select(x => x.BudgetYearlyAllocation.CapitalThaFourThree).First(),
//used admin categories budget usage part
, UsedAdminThaTeen = s.Where(x => x.BudgetHeadID == 7).Sum(x => x.ApprovedAmount),
UsedAdminThaTwo = s.Where(x => x.BudgetHeadID == 9).Sum(x => x.ApprovedAmount),
UsedAdminThaFour = s.Where(x => x.BudgetHeadID == 10).Sum(x => x.ApprovedAmount),
UsedAdminThaFive = s.Where(x => x.BudgetHeadID == 11).Sum(x => x.ApprovedAmount),
UsedAdminThaSix = s.Where(x => x.BudgetHeadID == 12).Sum(x => x.ApprovedAmount),
UsedAdminThaSeven = s.Where(x => x.BudgetHeadID == 13).Sum(x => x.ApprovedAmount),
UsedAdminThaTwoOneSeven = s.Where(x => x.BudgetHeadID == 14).Sum(x => x.ApprovedAmount),
//used machine categories budget usage part
UsedMachineThaOneOneFour = s.Where(x => x.BudgetHeadID == 16).Sum(x => x.ApprovedAmount),
UsedMachineThaTwo = s.Where(x => x.BudgetHeadID == 17).Sum(x => x.ApprovedAmount),
UsedMachineThaFour = s.Where(x => x.BudgetHeadID == 18).Sum(x => x.ApprovedAmount),
UsedMachineThaFiveOne = s.Where(x => x.BudgetHeadID == 19).Sum(x => x.ApprovedAmount),
UsedMachineThaFiveTwo = s.Where(x => x.BudgetHeadID == 20).Sum(x => x.ApprovedAmount),
UsedMachineThaSeven = s.Where(x => x.BudgetHeadID == 21).Sum(x => x.ApprovedAmount),
UsedMachineThaEight = s.Where(x => x.BudgetHeadID == 22).Sum(x => x.ApprovedAmount),
UsedMachineThaNine = s.Where(x => x.BudgetHeadID == 23).Sum(x => x.ApprovedAmount),
UsedMachineThaOneOneSeven = s.Where(x => x.BudgetHeadID == 24).Sum(x => x.ApprovedAmount),
//used capital categories budget usage part
UsedCapitalThaThreeOne = s.Where(x => x.BudgetHeadID == 25).Sum(x => x.ApprovedAmount),
UsedCapitalThaFourOne = s.Where(x => x.BudgetHeadID == 26).Sum(x => x.ApprovedAmount),
UsedCapitalThaFourThree = s.Where(x => x.BudgetHeadID == 27).Sum(x => x.ApprovedAmount),
//Balance admin categories budget usage part
BalanceAdminThaTeen = s.Select(x => x.BudgetYearlyAllocation.AdminThaTeen).First() - s.Where(x => x.BudgetHeadID == 7).Sum(x => x.ApprovedAmount),
BalanceAdminThaTwo = s.Select(x => x.BudgetYearlyAllocation.AdminThaTwo).First() - s.Where(x => x.BudgetHeadID == 9).Sum(x => x.ApprovedAmount),
BalanceAdminThaFour = s.Select(x => x.BudgetYearlyAllocation.AdminThaFour).First() - s.Where(x => x.BudgetHeadID == 10).Sum(x => x.ApprovedAmount),
BalanceAdminThaFive = s.Select(x => x.BudgetYearlyAllocation.AdminThaFive).First() - s.Where(x => x.BudgetHeadID == 11).Sum(x => x.ApprovedAmount),
BalanceAdminThaSix = s.Select(x => x.BudgetYearlyAllocation.AdminThaSix).First() - s.Where(x => x.BudgetHeadID == 12).Sum(x => x.ApprovedAmount),
BalanceAdminThaSeven = s.Select(x => x.BudgetYearlyAllocation.AdminThaSeven).First() - s.Where(x => x.BudgetHeadID == 13).Sum(x => x.ApprovedAmount),
BalanceAdminThaTwoOneSeven = s.Select(x => x.BudgetYearlyAllocation.AdminThaTwoOneSeven).First() - s.Where(x => x.BudgetHeadID == 14).Sum(x => x.ApprovedAmount),
//Balance machine categories budget usage part
BalanceMachineThaOneOneFour = s.Select(x => x.BudgetYearlyAllocation.MachineThaOneOneFour).First() - s.Where(x => x.BudgetHeadID == 16).Sum(x => x.ApprovedAmount),
BalanceMachineThaTwo = s.Select(x => x.BudgetYearlyAllocation.MachineThaTwo).First() - s.Where(x => x.BudgetHeadID == 17).Sum(x => x.ApprovedAmount),
BalanceMachineThaFour = s.Select(x => x.BudgetYearlyAllocation.MachineThaFour).First() - s.Where(x => x.BudgetHeadID == 18).Sum(x => x.ApprovedAmount),
BalanceMachineThaFiveOne = s.Select(x => x.BudgetYearlyAllocation.MachineThaFiveOne).First() - s.Where(x => x.BudgetHeadID == 19).Sum(x => x.ApprovedAmount),
BalanceMachineThaFiveTwo = s.Select(x => x.BudgetYearlyAllocation.MachineThaFiveTwo).First() - s.Where(x => x.BudgetHeadID == 20).Sum(x => x.ApprovedAmount),
BalanceMachineThaSeven = s.Select(x => x.BudgetYearlyAllocation.MachineThaSeven).First() - s.Where(x => x.BudgetHeadID == 21).Sum(x => x.ApprovedAmount),
BalanceMachineThaEight = s.Select(x => x.BudgetYearlyAllocation.MachineThaEight).First() - s.Where(x => x.BudgetHeadID == 22).Sum(x => x.ApprovedAmount),
BalanceMachineThaNine = s.Select(x => x.BudgetYearlyAllocation.MachineThaNine).First() - s.Where(x => x.BudgetHeadID == 23).Sum(x => x.ApprovedAmount),
BalanceMachineThaOneOneSeven = s.Select(x => x.BudgetYearlyAllocation.MachineThaOneOneSeven).First() - s.Where(x => x.BudgetHeadID == 24).Sum(x => x.ApprovedAmount),
//Balance capital categories budget usage part
BalanceCapitalThaThreeOne = s.Select(x => x.BudgetYearlyAllocation.CapitalThaThreeOne).First() - s.Where(x => x.BudgetHeadID == 25).Sum(x => x.ApprovedAmount),
BalanceCapitalThaFourOne = s.Select(x => x.BudgetYearlyAllocation.CapitalThaFourOne).First() - s.Where(x => x.BudgetHeadID == 26).Sum(x => x.ApprovedAmount),
BalanceCapitalThaFourThree = s.Select(x => x.BudgetYearlyAllocation.CapitalThaFourThree).First() - s.Where(x => x.BudgetHeadID == 27).Sum(x => x.ApprovedAmount),
//How to sum total Column Calculation error this part using columnone ienumbeable not support therefor need partical view in table footer.
//ColumnOne = s.Select(x => x.BudgetYearlyAllocation.AdminThaTeen).Sum()
// ColumnTwo = s.Sum(x => x.BudgetYearlyAllocation.AdminThaTwo),
// ColumnThree= s.Sum(x => x.BudgetYearlyAllocation.AdminThaFour)
}).ToList();
return View(query);
}
Por favor, ajude-me a projetar a classe de alocação orçamentária e clas rehpdata e como somar a coluna de grupos por plantas com o valor da alocação orçamentária.