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