public static string GeneratePayrollReport(DateTime firstDayOfPeriod, App.Providers.Models.PayrollFilter filter)
{
var data = new DataHelper();
var baseReportID = data.GetPayrollBaseReportID();
data.InsertPayablesRecord(baseReportID, firstDayOfPeriod, filter);
return(baseReportID.ToString());
}
public List <Models.Providers.PayrollGridItemVM> GetPayablesByPeriod(DateTime firstDayOfMonthOfPeriod, App.Providers.Models.PayrollFilter filter)
{
var domainList = new Data.Services.HoursService().GetPayrollHoursByPeriod(firstDayOfMonthOfPeriod, (int)filter);
var list = new List <Models.Providers.PayrollGridItemVM>();
foreach (var di in domainList)
{
var item = new Models.Providers.PayrollGridItemVM();
item.ID = di.ID;
item.PayrollID = di.PayrollID;
item.FirstName = di.FirstName;
item.LastName = di.LastName;
item.Hours = di.TotalHours;
item.EntriesMissingCatalystData = di.EntriesMissingCatalystData;
list.Add(item);
}
return(list);
}
public void InsertPayablesRecord(int reportID, DateTime firstDayOfPeriod, App.Providers.Models.PayrollFilter filter)
{
using (var conn = new SqlConnection(connectionString))
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO dbo.CasePayableReports (ReportBaseID, ReportID) " +
"VALUES (@BaseID, @ReportID);";
cmd.Parameters.AddWithValue("@BaseID", reportID);
cmd.Parameters.AddWithValue("@ReportID", reportID.ToString());
cmd.InsertToIdentity();
cmd.Parameters.Clear();
switch (filter)
{
case App.Providers.Models.PayrollFilter.NYOnly:
cmd.CommandText =
"UPDATE cah SET cah.HoursPayableRef = @ReportID FROM dbo.CaseAuthHours AS cah INNER JOIN dbo.Providers AS p ON cah.CaseProviderID = p.ID " +
"WHERE HoursDate >= @StartDate AND HoursDate <= @EndDate AND HoursStatus = 3 AND HoursPayableRef IS NULL " +
" AND p.ProviderState = 'NY';";
break;
case App.Providers.Models.PayrollFilter.NonNY:
cmd.CommandText =
"UPDATE cah SET cah.HoursPayableRef = @ReportID FROM dbo.CaseAuthHours AS cah INNER JOIN dbo.Providers AS p ON cah.CaseProviderID = p.ID " +
"WHERE HoursDate >= @StartDate AND HoursDate <= @EndDate AND HoursStatus = 3 AND HoursPayableRef IS NULL " +
" AND (p.ProviderState IS NULL OR p.ProviderState <> 'NY');";
break;
default:
cmd.CommandText = "UPDATE dbo.CaseAuthHours SET HoursPayableRef = @ReportID " +
"WHERE HoursDate >= @StartDate AND HoursDate <= @EndDate AND HoursStatus = 3 AND HoursPayableRef IS NULL;";
break;
}
cmd.Parameters.AddWithValue("@StartDate", firstDayOfPeriod);
cmd.Parameters.AddWithValue("@EndDate", firstDayOfPeriod.AddMonths(1).AddDays(-1));
cmd.Parameters.AddWithValue("@ReportID", reportID.ToString());
cmd.ExecuteNonQueryToInt();
}
}
