// 2. works public ReportSearchFilterVm categorySatisfactionByDate(ReportSearchFilterVm input) { var fromDate = input.fromDate.AddDays(-1); var toDate = input.toDate.AddDays(1); using (var db = new MainDb()) { var query = db.companyCategoryTypes .Include(i => i.referralses) .Include(k => k.referralses.Select(v => v.surveyAnswerses)) .OrderByDescending(b => b.created) .Where(e => (input.isActive == null || e.isActive == input.isActive) && (e.referralses.Any(v => v.created > fromDate) && e.referralses.Any(v => v.created < toDate)) ); input.result = query.ToList <object>(); if (input.result.Count() != 0) { var items = new List <object> { new { PERIOD = fromDate.ToShortDateString() + " thru " + toDate.ToShortDateString() } }; items.Add( new { h1 = "Provider Category", h2 = "Referrals", h3 = "Very Good", h4 = "Good", h5 = "Average", h6 = "Bad", h7 = "Very Bad" }); var item2 = query.Select(i => new { ProviderCategory = i.name, Referrals = i.referralses.Distinct().Count(), VeryGood = i.referralses.Sum(r => r.surveyAnswerses.Count(e => e.answer == 5)), Good = i.referralses.Sum(r => r.surveyAnswerses.Count(e => e.answer == 4)), Average = i.referralses.Sum(r => r.surveyAnswerses.Count(e => e.answer == 3)), Bad = i.referralses.Sum(r => r.surveyAnswerses.Count(e => e.answer == 2)), VeryBad = i.referralses.Sum(r => r.surveyAnswerses.Count(e => e.answer == 1)) }).ToList <object>(); foreach (var i in item2) { items.Add(i); } input.result = items; } return(input); } }
public ActionResult Index(ReportSearchFilterVm input = null, Paging paging = null) { if (input == null) { input = new ReportSearchFilterVm(); } input.paging = paging; return(View(input)); }
// 1. works public ReportSearchFilterVm categoryReferralByDate(ReportSearchFilterVm input) { var fromDate = input.fromDate.AddDays(-1); var toDate = input.toDate.AddDays(1); using (var db = new MainDb()) { var query = db.companyCategoryTypes .Include(i => i.referralses) .Include(k => k.referralses.Select(v => v.surveyAnswerses)) .OrderByDescending(b => b.created) .Where(e => (input.isActive == null || e.isActive == input.isActive) && (e.referralses.Any(v => v.created > fromDate) && e.referralses.Any(v => v.created < toDate)) ); input.result = query.ToList <object>(); if (input.result.Count() != 0) { var items = new List <object> { new { PERIOD = fromDate.ToShortDateString() + " thru " + toDate.ToShortDateString() } }; items.Add( new { h1 = "Provider Category", h2 = "Referrals", h3 = "Quotes", h4 = "Accepts", h5 = "Quotes Total", h6 = "Final Quotes Total" }); var item2 = query.Select(i => new { Provider_Category = i.name, Referrals = i.referralses.Distinct().Count(), Quotes = i.referralses.Where(d => d.quote != null).Count(), Accepts = i.referralses.Where(d => d.accepted == true).Count(), Quotes_Total = (i.referralses.Sum(d => d.quote) != null ? i.referralses.Sum(d => d.quote) : 0), Final_Quotes_Total = (i.referralses.Sum(d => d.finalQuote) != null ? i.referralses.Sum(d => d.finalQuote) : 0) }).ToList <object>(); foreach (var i in item2) { items.Add(i); } input.result = items; } return(input); } }
// working public FileResult ClientReferralByDate(ReportSearchFilterVm input = null) { if (this.ModelState.IsValid) { input.paging = null; input = reportManager.clientReferralByDate(input); var file = ImportExportHelper.exportToCsv(input.result, false); if (input.result.Count() != 0) { return(File(file.FullName, "Application/octet-stream", file.Name)); } else { this.Response.Write("<script> alert('No Data found based on the criteria!'); window.history.back(); </script>"); } } return(null); }
// 4. add Dues calculation if applicable, and double check Score calculation/float public ReportSearchFilterVm clientReferralByDate(ReportSearchFilterVm input) { var fromDate = input.fromDate.AddDays(-1); var toDate = input.toDate.AddDays(1); using (var db = new MainDb()) { var query = db.referrals .Include(i => i.client) .Include(k => k.company) .Include(n => n.surveyAnswerses) .OrderByDescending(b => b.created) .Where(e => (input.isActive == null || e.isActive == input.isActive) && (e.clientId == input.clientId) && (e.created > fromDate && e.created < toDate) ); input.result = query.ToList <object>(); var query2 = db.clients .Include(i => i.contactInfo) .Include(i => i.clientOrganizationLookupses.Select(c => c.organization)) .Where(e => (e.clientId == input.clientId) ); if (input.result.Count() != 0) { var cl = query2.FirstOrDefault(); var memStartDate = query2.FirstOrDefault().startingDate.ToString().Split(" ")[0]; var memEndDate = query2.FirstOrDefault().endDate.ToString().Split(" ")[0]; var items = new List <object> { new { h1 = cl.contactInfo.firstName + " " + cl.contactInfo.lastName, h2 = "Home:", h3 = cl.contactInfo.homePhone } }; items.Add((object) new { h1 = cl.contactInfo.address + ", " + cl.contactInfo.city + " " + cl.contactInfo.state + " " + cl.contactInfo.zip, h2 = "Cell:", h3 = cl.contactInfo.cell, }); items.Add((object) new { h1 = "", h2 = "Email:", h3 = cl.contactInfo.email, }); items.Add((object) new { h1 = "Organization:", h2 = cl.clientOrganizationLookupses.FirstOrDefault().organization.name, }); items.Add((object) new { h1 = "Membership:", h2 = (cl.startingDate == null && cl.endDate == null ? "" : memStartDate + " thru " + memEndDate), }); items.Add((object) new { h1 = "Dues:", h2 = "", // ??? }); items.Add( new { h1 = "ID", h2 = "Contractor", h3 = "Job", h4 = "Referral Date", h5 = "Status", h6 = "Score" }); var item2 = query.Select(i => new { ID = i.referralId, Contractor = i.company.companyName, Job = i.description, Ref_Date = SqlFunctions.DateName("day", i.referralDate).Trim() + "/" + SqlFunctions.StringConvert((double)i.referralDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", i.referralDate), Status = (i.accepted == true ? "accepted" : "pending"), Score = (i.surveyAnswerses.Any(t => t.answer != null) ? (decimal?)i.surveyAnswerses.Sum(v => v.answer) * 20 / i.surveyAnswerses.Count() : null) }).ToList <object>(); foreach (var i in item2) { items.Add(i); } input.result = items; } return(input); } }
// 3. just double check Score calculation/float public ReportSearchFilterVm prodiverReferralByDate(ReportSearchFilterVm input) { var fromDate = input.fromDate.AddDays(-1); var toDate = input.toDate.AddDays(1); using (var db = new MainDb()) { var query = db.referrals .Include(i => i.company) .Include(k => k.client) .Include(k => k.client.contactInfo) .Include(v => v.surveyAnswerses) .OrderByDescending(b => b.created) .Where(e => (input.isActive == null || e.isActive == input.isActive) && (e.companyId == input.companyId) && (e.created > fromDate) && (e.created < toDate) ); input.result = query.ToList <object>(); if (input.result.Count() != 0) { var first = query.FirstOrDefault(); var signedDate = first.company.agreementSigned.ToString().Split(" ")[0]; var items = new List <object> { new { h1 = first.company.companyName, h2 = "Work:", h3 = first.client.contactInfo.workPhone } }; items.Add((object) new { h1 = first.client.contactInfo.fullname, h2 = "Cell:", h3 = first.client.contactInfo.cell, }); items.Add((object) new { h1 = first.client.contactInfo.address + ", " + first.client.contactInfo.city + " " + first.client.contactInfo.state + " " + first.client.contactInfo.zip, h2 = "Fax:", h3 = first.client.contactInfo.fax, }); items.Add((object) new { h1 = "ID: " + first.companyId, h2 = "Email:", h3 = first.client.contactInfo.email, }); items.Add((object) new { h1 = "Lisence Number: " + first.company.license, h2 = "Agreement Signed:", h3 = signedDate }); items.Add(new { h1 = "ID", h2 = "Homeowner", h3 = "Job", h4 = "Referral Date", h5 = "Status", h6 = "Score" }); var item3 = query.Select(i => new { ID = i.referralId, Homeowner = i.client.contactInfo.firstName + " " + i.client.contactInfo.lastName, Job = i.description, Ref_Date = SqlFunctions.DateName("day", i.referralDate).Trim() + "/" + SqlFunctions.StringConvert((double)i.referralDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", i.referralDate), Status = (i.accepted == true ? "accepted" : "pending"), Score = (i.surveyAnswerses.Any(t => t.answer != null) ? (decimal?)i.surveyAnswerses.Sum(v => v.answer) * 20 / i.surveyAnswerses.Count() : null) }).ToList <object>(); foreach (var i in item3) { items.Add(i); } input.result = items; } return(input); } }