Good day to all ask , why always same the expense but different in Balanace
public ActionResult Index(string DepName, int month = 1, int year = 2017)
{
//check if the user has an Account
if (!RoleAuthentication.HasAccount(User.Identity.GetUserName()))
{
return RedirectToAction("Create", "Employee");
}
//check if the user is a requestor
if (!RoleAuthentication.IsAuthenticated(User.Identity.GetUserName(), "6"))
{
return RedirectToAction("Index", "Budget");
}
//this is for authentication
var reqContext = new RequestContext();
var InvContext = new InventoryContext();
var TRANSACTIONSISSUED = new List<TRANSACTIONISSUEDITEM>();
var DepartmentContext = new DepartmentContext();
var ListTrans = InvContext.Transactions();
var Requests = InvContext.Transactions();
var Employeedb = new EmployeeContext();
var Employee = Employeedb.Find(User.Identity.GetUserName());
var budgetDb = new BudgetContext();
var DeptBudget = budgetDb.List().ToList();
TRANSACTIONSISSUED = InvContext.IssuedItems();
ViewBag.Message = "";
var YearSelected = new List<string>();
ViewBag.month = month;
ViewBag.year = year;
var List = (from b in BudgetDb.List()
join i in DepartmentDB.DistinctBudgetDeptList()
on b.DEPARTMENTID.Trim() equals i.DEPARTMENTID.Trim()
into a
from d in a.DefaultIfEmpty(new BudgetViewModel())
select new BudgetViewModel
{
MONTH = b.MONTH,
YEAR = b.YEAR,
DEPARTMENTID = b.DEPARTMENTID,
DEPARTMENTNAME = d.DEPARTMENTNAME,
DATETIME = b.DATETIME,
BUDGET = b.BUDGET
}).Where(c => c.YEAR == year && c.MONTH == month).ToList();
var Expences = (from i in TRANSACTIONSISSUED
join r in ListTrans
on i.REQUESTNO.Trim() equals r.REQUESTNO.Trim()
where r.DEPARTMENT.Trim() == Employee.DEPTCODE.Trim() &&
r.DATETIME.Month == month && r.DATETIME.Year == year
select new EXpenseTransactionViewModel
{
Month = r.DATETIME.Month,
Year = r.DATETIME.Year,
Cost = i.COST,
Qty = i.QTY
}).ToList();
foreach(BudgetViewModel item in List)
{
double Expense = Expences.Where(e => e.Month == item.MONTH && e.Year == item.YEAR).Sum(e => e.Cost * e.Qty);
item.BALANCE = item.BUDGET- Expense;
item.EXPENCES = Expense;
}
return View(List.ToList());
}
Since all the records in the List has the same YEAR and MONTH value, so the Expences.Where(e => e.Month == item.MONTH && e.Year == item.YEAR).Sum(e => e.Cost * e.Qty); will always return the same result. That's why the EXPENCES always same.
However when you calculate the BALANCE, the item.BUDGET's is not always the same, so the Balanace is different.
Best Regards,
Jean
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
sorry for my very late response, I tried to find my own way to solve my problem eventually didn't work , may i ask yo all what are the best way to solved this problem , I want o create new budget table with an expense for 7 days do you have an idea
about this? can you please help me
I want o create new budget table with an expense for 7 days do you have an idea about this?
I'm not clear about the logic to calculate the expense value for 7 days, could you please explain this more detail? Do you mean you need to total for 7 days or each record for a day and get the recent 7 days records?
1. The architecture of the budget table?
2. What's the meaning of each column in the table and the logic to calculate each column's data?
Best Regards,
Jean
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
I want o create new budget table with an expense for 7 days do you have an idea about this?
I'm not clear about the logic to calculate the expense value for 7 days, could you please explain this more detail? Do you mean you need to total for 7 days or each record for a day and get the recent 7 days records?
1. The architecture of the budget table?
2. What's the meaning of each column in the table and the logic to calculate each column's data?
Best Regards,
Jean
yes, you are right overall total for 7 days if exceed to the budget inputted , if exceeding to the budget got negative sign for the budget inputted,
architecture for the budget , I want to add a Fiscal Year Calendar to recognized a 7 days budget,
I need a Column , 1. Year = like now 2017 , 2. WorkWeek = ww1 to ww52 , each workweek1 -> have a seven days , 3.Department = what department you want to give a budget limit for 7 days, Budget = what budget you inputted and also expence for 7 days
Hmm, I tried to figure out the logic to calculate this, but without knowing the details, I couldn't figure it out.
Perhaps, you should communicate with some one face to face to figure out this.
Best Regards,
Jean
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
if you don't mind , are you available for that using teamviewer?
Sorry, due to various reasons I can't use the teamviewer.
If you still need help with this issue, please provide the description of the function you want to implement, more detailed will be better.
Best Regards,
Jean
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
my problem still the same the expense value but different in Balance , can you suggest what wrong with my code?
In previous posts, we already talked about why the expense value are sane but different in balance. Have you made any changes based on these suggestions in previous posts?
And about your new requirement, your description is not clear enough for me to figure out the logic.
Best Regards,
Jean
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
36 Points
256 Posts
Ask question always show same expense on table
Jul 12, 2017 09:55 AM|Cloudfiers|LINK
Good day to all ask , why always same the expense but different in Balanace
public ActionResult Index(string DepName, int month = 1, int year = 2017) { //check if the user has an Account if (!RoleAuthentication.HasAccount(User.Identity.GetUserName())) { return RedirectToAction("Create", "Employee"); } //check if the user is a requestor if (!RoleAuthentication.IsAuthenticated(User.Identity.GetUserName(), "6")) { return RedirectToAction("Index", "Budget"); } //this is for authentication var reqContext = new RequestContext(); var InvContext = new InventoryContext(); var TRANSACTIONSISSUED = new List<TRANSACTIONISSUEDITEM>(); var DepartmentContext = new DepartmentContext(); var ListTrans = InvContext.Transactions(); var Requests = InvContext.Transactions(); var Employeedb = new EmployeeContext(); var Employee = Employeedb.Find(User.Identity.GetUserName()); var budgetDb = new BudgetContext(); var DeptBudget = budgetDb.List().ToList(); TRANSACTIONSISSUED = InvContext.IssuedItems(); ViewBag.Message = ""; var YearSelected = new List<string>(); ViewBag.month = month; ViewBag.year = year; var List = (from b in BudgetDb.List() join i in DepartmentDB.DistinctBudgetDeptList() on b.DEPARTMENTID.Trim() equals i.DEPARTMENTID.Trim() into a from d in a.DefaultIfEmpty(new BudgetViewModel()) select new BudgetViewModel { MONTH = b.MONTH, YEAR = b.YEAR, DEPARTMENTID = b.DEPARTMENTID, DEPARTMENTNAME = d.DEPARTMENTNAME, DATETIME = b.DATETIME, BUDGET = b.BUDGET }).Where(c => c.YEAR == year && c.MONTH == month).ToList(); var Expences = (from i in TRANSACTIONSISSUED join r in ListTrans on i.REQUESTNO.Trim() equals r.REQUESTNO.Trim() where r.DEPARTMENT.Trim() == Employee.DEPTCODE.Trim() && r.DATETIME.Month == month && r.DATETIME.Year == year select new EXpenseTransactionViewModel { Month = r.DATETIME.Month, Year = r.DATETIME.Year, Cost = i.COST, Qty = i.QTY }).ToList(); foreach(BudgetViewModel item in List) { double Expense = Expences.Where(e => e.Month == item.MONTH && e.Year == item.YEAR).Sum(e => e.Cost * e.Qty); item.BALANCE = item.BUDGET- Expense; item.EXPENCES = Expense; } return View(List.ToList()); }View
@model IEnumerable<WarehouseRtoRSystem.Models.BudgetViewModel> @{ ViewBag.Title = "Index"; } <div class="w3-white"> <div class="w3-container w3-teal header w3-text-white w3-card w3-border"> <div class="w3-col m12 "> <br /> </div> <h2 class="">Budget</h2> <p class="w3-blockquote"></p> <div class="w3-col m12 "> <br /> </div> </div> <div class="w3-container"> <div class="w3-col m12 "> <br /> </div> <div class="w3-col m12 "> <br /> </div> @using (Html.BeginForm()) { @ViewBag.Message <label>Month </label> <select id="month" name="month"> @{ string[] Months = { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }; } @for (var i = 0; i < 12; i++) { var m = i + 1; if (Convert.ToInt32(ViewBag.month) == m) { <option value=@m selected>@Months[i]</option> } else { <option value=@m>@Months[i]</option> } } </select> <label>YEAR</label> <select id="year" name="year"> @for (var c = 0; c < 1000; c++) { var yr = c + 2017; if (Convert.ToInt32(ViewBag.year) == yr) { <option value=@yr selected> @yr </option> } else { <option value=@yr> @yr</option> } } </select> <input type="submit" value="Filter" class="btn btn-default" /> } <br /> <div class="w3-col m12 "> <span> @Html.ActionLink("Update Budget", "Create", "Budget", new { @class = "btn btn-success" })</span> <br /> <br /> </div> <div class="w3-col m12 table-responsive" style="height: 300px"> <table class="table w3-white w3-striped w3-border"> <tr class="w3-teal "> <th> DATE INSERTED </th> <th> @Html.DisplayNameFor(model => model.DEPARTMENTID) </th> <th> @Html.DisplayNameFor(model => model.DEPARTMENTNAME) </th> <th> @Html.DisplayNameFor(model => model.BUDGET) </th> <th>EXPENCE</th> <th>BALANCE</th> </tr> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.DATETIME) </td> <td> @Html.DisplayFor(modelItem => item.DEPARTMENTID) </td> <td> @Html.DisplayFor(modelItem => item.DEPARTMENTNAME) </td> <td> @Html.DisplayFor(modelItem => item.BUDGET) </td> <td> @Html.DisplayFor(modelItem => item.EXPENCES) </td> <td> @Html.DisplayFor(modelItem => item.BALANCE) </td> </tr> } </table> </div> <div class="w3-col m12 "> @Html.ActionLink("Export to Excel", "GenerateExcels", "Budget", new { @class = "w3-btn btn-success" }) </div> <div class="w3-col m12 "> <br /> </div> </div> </div>Thank you for your help
Participant
1370 Points
608 Posts
Re: Ask question always show same expense on table
Jul 12, 2017 01:19 PM|JBetancourt|LINK
your formula to calculate the Expence is always the same for the month and independent from the budget or the department information:
but the balance depends on the budget which in fact is different for all departments:
I think your solution is to group your "List" ignoring the deparment or add the department to the "Expences" list
Please remember to click "Mark as Answer" the responsES that resolved your issue.
Contributor
2290 Points
794 Posts
Re: Ask question always show same expense on table
Jul 12, 2017 05:53 PM|codemovement.pk|LINK
It does not looks logical as is simple mathematics.
same number - different number should have different answer .
just as a hack to try.
try
Get more information: http://codemovement.pk
Thanks,
Contributor
6450 Points
2525 Posts
Re: Ask question always show same expense on table
Jul 13, 2017 06:32 AM|Jean Sun|LINK
Hi Cloudfiers,
In your above code your are selecting the BudgetViewModel from database and all the records in the List has the same YEAR value and MONTH value.
Since all the records in the List has the same YEAR and MONTH value, so the Expences.Where(e => e.Month == item.MONTH && e.Year == item.YEAR).Sum(e => e.Cost * e.Qty); will always return the same result. That's why the EXPENCES always same.
However when you calculate the BALANCE, the item.BUDGET's is not always the same, so the Balanace is different.
Best Regards,
Jean
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
36 Points
256 Posts
Re: Ask question always show same expense on table
Jul 17, 2017 06:45 AM|Cloudfiers|LINK
Good day to all,
sorry for my very late response, I tried to find my own way to solve my problem eventually didn't work , may i ask yo all what are the best way to solved this problem , I want o create new budget table with an expense for 7 days do you have an idea about this? can you please help me
thank you
Contributor
6450 Points
2525 Posts
Re: Ask question always show same expense on table
Jul 17, 2017 08:24 AM|Jean Sun|LINK
Hi Cloudfiers,
I'm not clear about the logic to calculate the expense value for 7 days, could you please explain this more detail? Do you mean you need to total for 7 days or each record for a day and get the recent 7 days records?
1. The architecture of the budget table?
2. What's the meaning of each column in the table and the logic to calculate each column's data?
Best Regards,
Jean
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
36 Points
256 Posts
Re: Ask question always show same expense on table
Jul 17, 2017 09:24 AM|Cloudfiers|LINK
yes, you are right overall total for 7 days if exceed to the budget inputted , if exceeding to the budget got negative sign for the budget inputted,
architecture for the budget , I want to add a Fiscal Year Calendar to recognized a 7 days budget,
I need a Column , 1. Year = like now 2017 , 2. WorkWeek = ww1 to ww52 , each workweek1 -> have a seven days , 3.Department = what department you want to give a budget limit for 7 days, Budget = what budget you inputted and also expence for 7 days
thanks hope you understand my explanation
Contributor
6450 Points
2525 Posts
Re: Ask question always show same expense on table
Jul 19, 2017 07:39 AM|Jean Sun|LINK
Hi Cloundfiers,
Hmm, I tried to figure out the logic to calculate this, but without knowing the details, I couldn't figure it out.
Perhaps, you should communicate with some one face to face to figure out this.
Best Regards,
Jean
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
36 Points
256 Posts
Re: Ask question always show same expense on table
Jul 21, 2017 06:10 AM|Cloudfiers|LINK
if you don't mind , are you available for that using teamviewer?
Contributor
6450 Points
2525 Posts
Re: Ask question always show same expense on table
Jul 25, 2017 05:20 AM|Jean Sun|LINK
Hi Cloudfiers,
Sorry, due to various reasons I can't use the teamviewer.
If you still need help with this issue, please provide the description of the function you want to implement, more detailed will be better.
Best Regards,
Jean
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
36 Points
256 Posts
Re: Ask question always show same expense on table
Jul 25, 2017 06:20 AM|Cloudfiers|LINK
my problem still the same the expense value but different in Balance , can you suggest what wrong with my code?
thanks in advance
Contributor
6450 Points
2525 Posts
Re: Ask question always show same expense on table
Jul 25, 2017 07:11 AM|Jean Sun|LINK
Hi Cloundfiers,
In previous posts, we already talked about why the expense value are sane but different in balance. Have you made any changes based on these suggestions in previous posts?
And about your new requirement, your description is not clear enough for me to figure out the logic.
Best Regards,
Jean
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.