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