public FinancialExpandedResults getIndividualEmpDataForLastMonthInQuarter(int accountId, int empId, int year, int quarter) { string query = "SELECT top(1) [Id],[EmpId],[EmpName],[AccountId],[AccountName],[Year],[Month],[Quarter],[BillableType],[AllocatedHours],[BillableHours],[TotalReportedHours],[ConsiderableHours],[ExtraOrLag],[CumAllocatedHours],[CumBillableHours],[CumTotalReportedHours],[CumConsiderableHours] FROM [FinancialResults] where AccountId=" + accountId + " and EmpId=" + empId + " and Year=" + year + " and Quarter=" + quarter + " and BillableType=2 order by Month desc "; FinancialExpandedResults result = this._db.Query <FinancialExpandedResults>(query).SingleOrDefault(); if (result == null) { result = new FinancialExpandedResults(); result.EmpId = empId; result.AccountId = accountId; } return(result); }
public void updateFinancialResults([FromBody] List <FinancialResults> results) { Debug.WriteLine("RESULT SIZE " + results.Count); int didExistingResultesDeleted = 0; int didExistingSummaryDeleted = 0; if (results.Count != 0) { didExistingResultesDeleted = repo.deleteFinancialResults(results[0].AccountId, results[0].Year, results[0].Quarter, results[0].Month); didExistingSummaryDeleted = summaryRepo.deleteExistingSummary(results[0].AccountId, results[0].Year, results[0].Quarter, results[0].Month); } if (didExistingResultesDeleted > 0 & didExistingSummaryDeleted > 0) { // repo.add(results); double expectedHours = 0; double actualHours = 0; double coveredBillableHours = 0; if (results.Count != 0) { List <int> empIds = repo.getTimeReportedEmpIdForQuarter(results[0].Year, results[0].Quarter, results[0].AccountId); foreach (FinancialResults result in results) { empIds.Remove(result.EmpId); //repo.saveData(result); } foreach (int empId in empIds) { TeamMembers member = teamRepo.Get(empId); if (member != null) { FinancialExpandedResults tempResult = new FinancialExpandedResults(); tempResult.EmpId = empId; tempResult.EmpName = member.MemberName; tempResult.AccountId = results[0].AccountId; tempResult.AccountName = results[0].AccountName; tempResult.Month = results[0].Month; tempResult.Quarter = results[0].Quarter; tempResult.Year = results[0].Year; Console.WriteLine(tempResult.EmpName); // repo.saveData(tempResult); FinancialExpandedResults empFinancialResult = repo.getIndividualEmpDataForLastMonthInQuarter(results[0].AccountId, empId, results[0].Year, results[0].Quarter); if (empFinancialResult != null) { expectedHours += empFinancialResult.CumAllocatedHours; actualHours += empFinancialResult.BillableHours; coveredBillableHours += empFinancialResult.CumConsiderableHours; } } } } //Calculate Financial Summary List <FinancialExpandedResults> newResultList = new List <FinancialExpandedResults>(); foreach (FinancialResults result in results) { FinancialExpandedResults lastMonthresult = repo.getIndividualEmpDataForLastMonthInQuarter(result.AccountId, result.EmpId, result.Year, result.Quarter); FinancialExpandedResults currentResult = result; if (result.BillableType == Constants.FULLTIMEBILLABLE) { //results currentResult.CumAllocatedHours = lastMonthresult.CumAllocatedHours + currentResult.AllocatedHours; currentResult.CumBillableHours = lastMonthresult.CumBillableHours + currentResult.BillableHours; currentResult.CumTotalReportedHours = lastMonthresult.CumTotalReportedHours + currentResult.TotalReportedHours; if (currentResult.CumBillableHours > currentResult.CumAllocatedHours) { currentResult.CumConsiderableHours = currentResult.CumAllocatedHours; } else { currentResult.CumConsiderableHours = currentResult.CumBillableHours; } } else if (result.BillableType == Constants.NONBILLABLE || result.BillableType == Constants.HOURLYBILLABLE) { currentResult.CumAllocatedHours = lastMonthresult.CumAllocatedHours; currentResult.CumBillableHours = lastMonthresult.CumBillableHours; currentResult.CumTotalReportedHours = lastMonthresult.CumTotalReportedHours; currentResult.CumConsiderableHours = lastMonthresult.CumConsiderableHours; } newResultList.Add(currentResult); //summary expectedHours += currentResult.CumAllocatedHours; actualHours += currentResult.CumBillableHours; coveredBillableHours += currentResult.CumConsiderableHours; } if (results.Count != 0) { repo.add(newResultList); FinancialSummary savingSummary = new FinancialSummary(); savingSummary.Month = results[0].Month; savingSummary.Year = results[0].Year; savingSummary.Quarter = results[0].Quarter; savingSummary.AccountId = results[0].AccountId; savingSummary.AccountName = results[0].AccountName; savingSummary.MonthName = getMonth(results[0].Month); savingSummary.ExpectedHours = expectedHours; Debug.WriteLine("Saving Summary " + expectedHours); savingSummary.ActualHours = actualHours; savingSummary.coveredBillableHours = coveredBillableHours; summaryRepo.add(savingSummary); } } }
public int add(List <FinancialExpandedResults> results) { int datarows = 0; //String strDate = "5/" + results[0].Month + "/" + results[0].Year+ ""; //DateTime dateTime = DateTime.ParseExact(strDate, "dd/MM/yyyy", CultureInfo.InvariantCulture); //dateTime = dateTime.AddMonths(-1); List <int> empDataInAccount = new List <int>(); List <int> usingEmpDataInAccount = new List <int>(); if (results.Count != 0) { empDataInAccount = empRepo.getDistinctEmpIdsForAccounts(results[0].AccountId, results[0].Year, results[0].Quarter); usingEmpDataInAccount = empRepo.getDistinctEmpIdsForAccounts(results[0].AccountId, results[0].Year, results[0].Quarter); } foreach (FinancialExpandedResults result in results) { if (empDataInAccount.Count != 0) { foreach (int data in empDataInAccount) { if (data == result.EmpId) { usingEmpDataInAccount.Remove(data); } } } //Check the above method // This Add Statement Is Not Completed..... int tempMonth = result.Month; int pastMaxMonth = getEmployeeTimeReportMaxMonth(result.AccountId, result.Year, result.Quarter, result.EmpId); if (pastMaxMonth != 0) { int currentMonth = result.Month; int monthDifference = currentMonth - pastMaxMonth; if (monthDifference > 1) { monthDifference = monthDifference - 1; for (int i = (pastMaxMonth + 1); i <= (monthDifference + pastMaxMonth); i++) { result.Month = i; Console.WriteLine(result.EmpName); saveData(result); } } } else { int pastMonth = 0; int currentMonth = result.Month; if (result.Quarter == 1) { pastMonth = 1; } else { pastMonth = 7; } int minMonthForQuarter = getAccountTimeReportMinMonthForQuarter(result.AccountId, result.Year, result.Quarter); if (minMonthForQuarter == 0) { pastMonth = currentMonth; } else { pastMonth = minMonthForQuarter; } for (int i = pastMonth; i < currentMonth; i++) { FinancialExpandedResults tempResult = result; tempResult.Month = i; Console.WriteLine(tempResult.EmpName); saveData(tempResult); } } double considerableHours = 0; double extraOrLag = result.BillableHours - result.AllocatedHours; if (result.BillableHours >= result.AllocatedHours) { considerableHours = result.AllocatedHours; } else { considerableHours = result.BillableHours; } Debug.WriteLine("BillableType " + result.BillableType); Console.WriteLine(result.EmpName); result.Month = tempMonth; datarows = this._db.Execute(@"INSERT FinancialResults([EmpId],[EmpName],[AccountId],[AccountName],[Year],[Month],[Quarter],[BillableType],[AllocatedHours],[BillableHours],[TotalReportedHours],[ConsiderableHours],[ExtraOrLag],[CumAllocatedHours],[CumBillableHours],[CumTotalReportedHours],[CumConsiderableHours]) values (@EmpId,@EmpName,@AccountId,@AccountName,@Year,@Month,@Quarter,@BillableType,@AllocatedHours,@BillableHours,@TotalReportedHours,@ConsiderableHours,@ExtraOrLag,@CumAllocatedHours,@CumBillableHours,@CumTotalReportedHours,@CumConsiderableHours)", new { EmpId = result.EmpId, EmpName = result.EmpName, AccountId = result.AccountId, AccountName = result.AccountName, Year = result.Year, Month = result.Month, Quarter = result.Quarter, BillableType = result.BillableType, AllocatedHours = result.AllocatedHours, BillableHours = result.BillableHours, TotalReportedHours = result.TotalReportedHours, ConsiderableHours = considerableHours, ExtraOrLag = extraOrLag, CumAllocatedHours = result.CumAllocatedHours, CumBillableHours = result.CumBillableHours, CumTotalReportedHours = result.CumTotalReportedHours, CumConsiderableHours = result.CumConsiderableHours }); } if (usingEmpDataInAccount.Count != 0 && results.Count != 0) { foreach (int data in usingEmpDataInAccount) { FinancialExpandedResults tempFinancialResult = new FinancialExpandedResults(); tempFinancialResult.EmpId = data; TeamMembers employee = teamRepo.Get(data); tempFinancialResult.EmpName = employee.MemberName; tempFinancialResult.Year = results[0].Year; tempFinancialResult.Month = results[0].Month; tempFinancialResult.Quarter = results[0].Quarter; tempFinancialResult.AccountId = results[0].AccountId; tempFinancialResult.AccountName = results[0].AccountName; saveData(tempFinancialResult); } } return(datarows); }
public void saveData(FinancialExpandedResults result) { this._db.Execute(@"INSERT FinancialResults([EmpId],[EmpName],[AccountId],[AccountName],[Year],[Month],[Quarter],[BillableType],[AllocatedHours],[BillableHours],[TotalReportedHours],[ConsiderableHours],[ExtraOrLag],[CumAllocatedHours],[CumBillableHours],[CumTotalReportedHours],[CumConsiderableHours]) values (@EmpId,@EmpName,@AccountId,@AccountName,@Year,@Month,@Quarter,@BillableType,@AllocatedHours,@BillableHours,@TotalReportedHours,@ConsiderableHours,@ExtraOrLag,@CumAllocatedHours,@CumBillableHours,@CumTotalReportedHours,@CumConsiderableHours)", new { EmpId = result.EmpId, EmpName = result.EmpName, AccountId = result.AccountId, AccountName = result.AccountName, Year = result.Year, Month = result.Month, Quarter = result.Quarter, BillableType = 4, AllocatedHours = -1, BillableHours = -1, TotalReportedHours = -1, ConsiderableHours = -1, ExtraOrLag = -1, CumAllocatedHours = -1, CumBillableHours = -1, CumTotalReportedHours = -1, CumConsiderableHours = -1 }); }