public JobCode[] GetJobCodes(int?franchiseId)
{
using (var ctx = new ReportingEntities())
{
IQueryable <vRpt_JobCode> qry = ctx.vRpt_JobCode;
if (franchiseId.HasValue)
{
qry = from vRpt_JobCode jobCode in qry
where jobCode.FranchiseID == franchiseId.Value
select jobCode;
}
var toReturn = (from vRpt_JobCode jobCode in qry
select new JobCode()
{
ActiveYN = jobCode.ActiveYN,
FranchiseID = jobCode.FranchiseID,
JobCodeDescription = jobCode.JobCodeDescription,
JobCodeID = jobCode.JobCodeID,
JobCodeName = jobCode.JobCode,
PriceBookActiveYN = jobCode.PriceBookActiveYN,
PriceBookID = jobCode.PriceBookID,
PriceBookName = jobCode.PriceBookName
});
return(toReturn.ToArray <JobCode>());;
}
}
public Employee[] GetEmployees(int?franchiseId)
{
using (var ctx = new ReportingEntities())
{
IQueryable <vRpt_Employee> qry = ctx.vRpt_Employee;
if (franchiseId.HasValue)
{
qry = from vRpt_Employee employee in qry
where employee.FranchiseID == franchiseId.Value
select employee;
}
var toReturn = (from vRpt_Employee employee in qry
select new Employee()
{
ActiveYN = employee.ActiveYN,
CommissionRate = employee.CommissionRate,
EmployeeID = employee.EmployeeID,
EmployeeName = employee.Employee,
FranchiseID = employee.FranchiseID,
FranchiseLegalName = employee.FranchiseLegalName,
FranchiseNUmber = employee.FranchiseNUmber,
ServiceProYN = employee.ServiceProYN
});
return(toReturn.ToArray <Employee>());;
}
}
private static IEnumerable <vRpt_WSR> GetWSRData(int?franchiseID, DateTime?fromDateInclusive, DateTime?toDateExclusive)
{
using (var context = new ReportingEntities())
{
var query = context.vRpt_WSR.AsQueryable();
if (franchiseID.HasValue)
{
query = query.Where(q => q.ClientID == franchiseID);
}
if (fromDateInclusive.HasValue)
{
query = query.Where(q => q.WSRCompletedDate >= fromDateInclusive);
}
if (toDateExclusive.HasValue)
{
query = query.Where(q => q.WSRCompletedDate <= toDateExclusive);
}
return(query.ToArray());
}
}
private static IEnumerable <vRPT_MembershipInfo> GetMembershipsData(int?franchiseId, DateTime?lastVisitFromDateInclusive, DateTime?lastVisitToDateExclusive)
{
DateTime dtFrom = lastVisitFromDateInclusive.Value;
DateTime dtTo = lastVisitToDateExclusive.Value.Date.AddDays(1);
using (var ctx = new ReportingEntities())
{
IQueryable <vRPT_MembershipInfo> qry = ctx.vRPT_MembershipInfo;
if (franchiseId.HasValue)
{
qry = qry.Where(c => c.ClientID == franchiseId);
}
if (lastVisitFromDateInclusive.HasValue)
{
qry = qry.Where(c => c.MembershipStartDate >= dtFrom);
}
if (lastVisitToDateExclusive.HasValue)
{
qry = qry.Where(c => c.MembershipStartDate < dtTo);
}
return(qry.ToArray());
}
}
private static IEnumerable <vRpt_AccountingDetail> GetAccountingDetailData(int?frid, DateTime?from, DateTime?to)
{
DateTime dtFrom = from.Value;
DateTime dtTo = to.Value.Date.AddDays(1);
using (var ctx = new ReportingEntities())
{
IQueryable <vRpt_AccountingDetail> qry = ctx.vRpt_AccountingDetail;
if (frid.HasValue)
{
qry = qry.Where(c => c.ClientID == frid);
}
if (from.HasValue)
{
qry = qry.Where(c => c.WSRCompletedDate >= dtFrom);
}
if (to.HasValue)
{
qry = qry.Where(c => c.WSRCompletedDate < dtTo);
}
return(qry.ToArray());
}
}
private static IEnumerable <vRpt_AccountingSummary> GetAccountSummaryData(int?franchiseId, DateTime?lastVisitFromDateInclusive, DateTime?lastVisitToDateExclusive)
{
DateTime dtFrom = lastVisitFromDateInclusive.Value;
DateTime dtTo = lastVisitToDateExclusive.Value.Date.AddDays(1);
using (var ctx = new ReportingEntities())
{
IQueryable <vRpt_AccountingSummary> qry = ctx.vRpt_AccountingSummary;
if (franchiseId.HasValue)
{
qry = qry.Where(c => c.ClientID == franchiseId);
}
if (lastVisitFromDateInclusive.HasValue)
{
qry = qry.Where(c => c.WSRCompletedDate >= dtFrom);
}
if (lastVisitToDateExclusive.HasValue)
{
qry = qry.Where(c => c.WSRCompletedDate < dtTo);
}
return(qry.ToArray());
}
}
private static IEnumerable <vRpt_PartUsagePerJob> GetJobTaskPartData(int?franchiseId, DateTime?callCompletedFromDateInclusive, DateTime?callCompletedToDateExclusive)
{
DateTime dtFrom = callCompletedFromDateInclusive.Value;
DateTime dtTo = callCompletedToDateExclusive.Value.Date.AddDays(1);
using (var ctx = new ReportingEntities())
{
//ctx.CommandTimeout = 30; // defaulted to SQL Server Remote Query TimeOut. Make sense only for this instance.
IQueryable <vRpt_PartUsagePerJob> qry = ctx.vRpt_PartUsagePerJob;
if (franchiseId.HasValue)
{
qry = qry.Where(c => c.ClientID == franchiseId);
}
if (callCompletedFromDateInclusive.HasValue)
{
qry = qry.Where(c => c.CallCompleted >= dtFrom);
}
if (callCompletedToDateExclusive.HasValue)
{
qry = qry.Where(c => c.CallCompleted < dtTo);
}
return(qry.ToArray());
}
}
private static IEnumerable <vRpt_Customer> GetCustomerData(int?franchiseId, DateTime?lastVisitFromDateInclusive, DateTime?lastVisitToDateExclusive)
{
DateTime dtFrom = lastVisitFromDateInclusive.Value;
DateTime dtTo = lastVisitToDateExclusive.Value.Date.AddDays(1);
using (var ctx = new ReportingEntities())
{
//ctx.CommandTimeout = 30; // defaulted to SQL Server Remote Query TimeOut. Make sense only for this instance.
IQueryable <vRpt_Customer> qry = ctx.vRpt_Customer;
if (franchiseId.HasValue)
{
qry = qry.Where(c => c.ClientID == franchiseId);
}
if (lastVisitFromDateInclusive.HasValue)
{
qry = qry.Where(c => c.LastVisit >= dtFrom);
}
if (lastVisitToDateExclusive.HasValue)
{
qry = qry.Where(c => c.LastVisit < dtTo);
}
return(qry.ToArray());
}
}
private static Dictionary <vRpt_Job, vRpt_JobDetail[]> GetJobData(int?franchiseId, int?techId, DateTime fromDateInclusive, DateTime toDateExclusive, int[] statuses, int[] excludeStatuses)
{
DateTime dtFrom = fromDateInclusive.Date;
DateTime dtTo = toDateExclusive;
var noStatuses = statuses == null || statuses.Length == 0;
var noExcludeStatuses = excludeStatuses == null || excludeStatuses.Length == 0;
if (noStatuses)
{
statuses = new[] { int.MinValue }
}
;
if (noExcludeStatuses)
{
excludeStatuses = new[] { int.MinValue }
}
;
using (var ctx = new ReportingEntities())
{
var qry = from ji in ctx.vRpt_Jobs
where ((ji.CallCompleted >= dtFrom && ji.CallCompleted < dtTo) ||
(ji.EstimateDate >= dtFrom && ji.EstimateDate < dtTo)
) &&
ji.ClientID == (franchiseId ?? ji.ClientID) &&
(noStatuses || statuses.Contains(ji.StatusID)) &&
(noExcludeStatuses || !excludeStatuses.Contains(ji.StatusID))
let tasks = from t in ctx.vRpt_JobDetails where t.JobID == ji.TicketNumber select t
select new { Job = ji, Tasks = tasks };
return(qry.ToDictionary(j => j.Job, j => j.Tasks.ToArray()));
}
}
public static IEnumerable <vRpt_Customer> getCustomerDetails(int CustomerId)
{
using (var ctx = new ReportingEntities())
{
IQueryable <vRpt_Customer> qry = ctx.vRpt_Customer;
qry = qry.Where(c => c.CustomerID == CustomerId);
return(qry.ToArray());
}
}
public PayrollSetup[] GetPayrollSetupData(int?franchiseId)
{
PayrollSetup[] toReturn = new PayrollSetup[0];
using (var ctx = new ReportingEntities())
{
// Query Payroll Setup Reporting Facade and inflate reporting entities
IQueryable <vRpt_PayrollSetup> qry = ctx.vRpt_PayrollSetup;
if (franchiseId.HasValue)
{
qry = qry.Where(payrollSetup => payrollSetup.FranchiseID == franchiseId);
}
var leftJoin = from vRpt_PayrollSetup ps in qry
join vRpt_PayrollSpiff psp in ctx.vRpt_PayrollSpiff
on ps.PayrollSetupID equals psp.PayrollSetupID into JOINPayrollSetupPayrollSpiff
from psp in JOINPayrollSetupPayrollSpiff.DefaultIfEmpty()
select new
{
xPayrollSetup = ps,
xPayrollSpiff = psp != null ? psp : null
};
var y = leftJoin.GroupBy((xPair) => xPair.xPayrollSetup, xPair => xPair)
.Select(xGroup => new PayrollSetup()
{
FranchiseID = xGroup.Key.FranchiseID,
PayrollSetupID = xGroup.Key.PayrollSetupID,
OvertimeMethod = xGroup.Key.OvertimeMethod,
OvertimeMethodID = xGroup.Key.OvertimeMethodID,
OvertimeStarts = xGroup.Key.OvertimeStarts,
OTMultiplier = xGroup.Key.OTMultiplier,
PayrollSpiffs = xGroup.Where(xPair => xPair.xPayrollSpiff != null)
.Select(xPair =>
new PayrollSpiff()
{
PayrollSpiffID = xPair.xPayrollSpiff.PayrollSpiffID,
PayrollSetupID = xGroup.Key.PayrollSetupID,
Active = xPair.xPayrollSpiff.ActiveYN,
AddOn = xPair.xPayrollSpiff.AddonYN,
Comments = xPair.xPayrollSpiff.Comments,
DateExpires = xPair.xPayrollSpiff.DateExpires,
JobCode = xPair.xPayrollSpiff.JobCode,
JobCodeDescription = xPair.xPayrollSpiff.JobCodeDescription,
JobCodeID = xPair.xPayrollSpiff.JobCodeID,
PayType = xPair.xPayrollSpiff.PayType,
PayTypeID = xPair.xPayrollSpiff.PayTypeID,
Rate = xPair.xPayrollSpiff.Rate,
ServiceProID = xPair.xPayrollSpiff.ServiceProID,
Employee = xPair.xPayrollSpiff.Employee
}
)
}
);
toReturn = y.ToArray <PayrollSetup>();
}
return(toReturn);
}
public DateTime[] GetWSRDates(int?franchiseId, DateTime?from, DateTime?to)
{
DateTime dtFrom = from.Value;
DateTime dtTo = to.Value.Date.AddHours(23);
using (var context = new ReportingEntities())
{
var query = context.vRpt_WSR_Dates.Where(q => q.ClientID == franchiseId && q.WSRCompletedDate != null && q.WSRCompletedDate >= dtFrom && q.WSRCompletedDate <= dtTo).Distinct();
return(query.OrderBy(q => q.WSRCompletedDate).Select(q => q.WSRCompletedDate.Value).ToArray());
}
}
public SpiffPayType[] GetSpiffPayTypes()
{
using (var ctx = new ReportingEntities())
{
var qry = (from vRpt_SpiffPayType payType in ctx.vRpt_SpiffPayType
select new SpiffPayType()
{
SpiffPayTypeID = payType.SpiffPayTypeID,
SpiffPayTypeName = payType.SpiffPayType
});
return(qry.ToArray <SpiffPayType>());;
}
}
public OvertimeMethod[] GetOvertimeMethods()
{
using (var ctx = new ReportingEntities())
{
var qry = (from vRpt_OvertimeMethod overtimeMethod in ctx.vRpt_OvertimeMethod
select new OvertimeMethod()
{
OvertimeMethodID = overtimeMethod.OvertimeMethodID,
OvertimeMethodName = overtimeMethod.OvertimeMethod
});
return(qry.ToArray <OvertimeMethod>());;
}
}
private static Dictionary <vRpt_CustomerLedger, vRpt_Customer> GetCustomerLedgerData(int?franchiseId, DateTime?fromDateInclusive, DateTime?toDateExclusive)
{
DateTime dtFrom = fromDateInclusive.Value;
DateTime dtTo = toDateExclusive.Value.Date.AddDays(1);
using (var ctx = new ReportingEntities())
{
var qry = from l in ctx.vRpt_CustomerLedger
where
l.ClientId == (franchiseId ?? l.ClientId) &&
l.RecordedDate >= dtFrom &&
l.RecordedDate < dtTo
let cust = (from c in ctx.vRpt_Customer
where c.CustomerID == l.CustomerId
select c).FirstOrDefault()
select new { Ledger = l, Customer = cust };
return(qry.ToDictionary(c => c.Ledger, c => c.Customer));
}
}
public static Dictionary <vRpt_CustomerLedgerSummary, string> getCustomerSummaryData(int FranchiseId)
{
using (var ctx = new ReportingEntities())
{
IQueryable <vRpt_CustomerLedgerSummary> qry = ctx.vRpt_CustomerLedgerSummary;
qry = qry.Where(c => c.ClientId == FranchiseId);
//var jqry = from l in qry.AsEnumerable()
// join c in ctx.vRpt_Customer on l.CustomerId equals c.CustomerID
// where l.ClientId == FranchiseId
// select new { CustomerLedgerSummary = l, c.CustomerName };
var jqry = from l in qry.AsEnumerable()
where l.ClientId == FranchiseId
let customerName = (from c in ctx.vRpt_Customer where c.CustomerID == l.CustomerId select c.CustomerName).FirstOrDefault()
select new { CustomerLedgerSummary = l, customerName };
return(jqry.ToDictionary(l => l.CustomerLedgerSummary, l => l.customerName));
}
}
