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 });
}
