public ActionResult GetActivitiesList(JQDTblParamModel dTblParams)
{
string countSQL = "";
string selectSQL = "";
string sqlWhere = " WHERE 1=1 ";
string sqlOrder = " ORDER BY ";
string sqlOffSetLimit = string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY;", dTblParams.iDisplayStart, dTblParams.iDisplayLength);
countSQL = "select count(*) " +
" from ActivityHistory a " +
" inner join Contact c on c.ID = a.ContactID" +
" inner join CompanyContact cc on cc.ContactID = c.ID " +
" inner join Company co on co.ID = cc.companyID ";
selectSQL = "select a.ID, a.StartDate, a.EndDate, a.ActivityType, a.ActivityStatus, " +
" co.Name CompanyName, c.LastName + ', ' + c.FirstName ContactName, " +
" a.ActivityStatus, a.Subject, c.LastName, c.FirstName " +
" from ActivityHistory a " +
" inner join Contact c on c.ID = a.ContactID " +
" inner join CompanyContact cc on c.ID = cc.contactID" +
" inner join Company co on co.ID = cc.companyID ";
var sVal = "";
if (!string.IsNullOrEmpty(dTblParams.sSearch))
{
sVal = string.Format("%{0}%", dTblParams.sSearch);
sqlWhere += " AND (a.ActivityType like @p0 or a.ActivityStatus like @p0 " +
" or a.Subject like @p0 " +
" or c.LastName like @p0 or c.FirstName like @p0 " +
" or co.Name like @p0) ";
}
var sortColIndex = Convert.ToInt32(Request["iSortCol_0"]);
var sortDir = Request["sSortDir_0"];
sqlOrder += (sortColIndex == 0 ? string.Format("c.ID {0}", sortDir) :
sortColIndex == 1 ? string.Format("a.StartDate {0}", sortDir) :
sortColIndex == 2 ? string.Format("a.EndDate {0}", sortDir) :
sortColIndex == 3 ? string.Format("co.Name {0}", sortDir) :
sortColIndex == 4 ? string.Format("c.LastName {0}, c.FirstName {0}", sortDir) :
sortColIndex == 5 ? string.Format("a.ActivityStatus {0}", sortDir) :
string.Format("a.StartDate {0}", sortDir));
string countSQLFinal = string.Format("{0} {1}", countSQL.ToString(), sqlWhere);
string selectSQLFinal = string.Format("{0} {1} {2} {3}", selectSQL.ToString(), sqlWhere, sqlOrder, sqlOffSetLimit);
var totalRecs = db.Database.SqlQuery <Int32>(countSQLFinal, sVal);
IEnumerable <ActivityListViewModel> displayedActivities;
if (!string.IsNullOrEmpty(sVal))
{
displayedActivities = db.Database.SqlQuery <ActivityListViewModel>(selectSQLFinal, sVal).ToList();
}
else
{
displayedActivities = db.Database.SqlQuery <ActivityListViewModel>(selectSQLFinal).ToList();
}
var dispRecs = displayedActivities.Count();
return(Json(new
{
sEcho = dTblParams.sEcho,
iTotalRecords = totalRecs.ElementAt(0),
iTotalDisplayRecords = totalRecs.ElementAt(0),
data = Json(displayedActivities).Data
},
"application/json",
JsonRequestBehavior.AllowGet));
}
public ActionResult GetContactsList(JQDTblParamModel dTblParams)
{
string countSQL = "";
string selectSQL = "";
string sqlWhere = " WHERE 1=1 ";
string sqlOrder = " ORDER BY ";
string sqlOffSetLimit = string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY;", dTblParams.iDisplayStart, dTblParams.iDisplayLength);
countSQL = "select count(*) " +
" from Contact c " +
" inner join CompanyContact cc on c.ID = cc.contactID" +
" inner join Company co on co.ID = cc.companyID ";
selectSQL = "select c.ID, c.Honorific, c.FirstName, c.MiddleName, c.LastName, " +
" c.Suffix, c.Title, c.Phone, c.Email, co.Name CompanyName " +
" from Contact c " +
" inner join CompanyContact cc on c.ID = cc.contactID" +
" inner join Company co on co.ID = cc.companyID ";
var sVal = "";
if (!string.IsNullOrEmpty(dTblParams.sSearch))
{
sVal = string.Format("%{0}%", dTblParams.sSearch);
// sqlWhere += " AND (c.Honorific like '%@p0%' or c.FirstName like '%@p0%' " +
// " or c.MiddleName like '%@p0%' or c.LastName like '%@p0%' " +
// " or c.Suffix like '%@p0%' or c.Phone like '%@p0%' " +
// " or c.Email like '%@p0%' or co.Name like '%@p0%')";
sqlWhere += " AND (c.Honorific like @p0 or c.FirstName like @p0 " +
" or c.MiddleName like @p0 or c.LastName like @p0 " +
" or c.Suffix like @p0 or c.Phone like @p0 " +
" or c.Email like @p0 or co.Name like @p0)";
}
var sortColIndex = Convert.ToInt32(Request["iSortCol_0"]);
var sortDir = Request["sSortDir_0"];
sqlOrder += (sortColIndex == 0 ? string.Format("c.ID {0}", sortDir) :
sortColIndex == 1 ? string.Format("c.LastName {0}, c.FirstName {0}", sortDir) :
sortColIndex == 2 ? string.Format("c.Title {0}", sortDir) :
sortColIndex == 3 ? string.Format("c.Phone {0}", sortDir) :
sortColIndex == 4 ? string.Format("c.Email {0}", sortDir) :
sortColIndex == 5 ? string.Format("co.Name {0}", sortDir) :
string.Format("c.LastName {0}, c.FirstName {0}", sortDir));
string countSQLFinal = string.Format("{0} {1}", countSQL.ToString(), sqlWhere);
string selectSQLFinal = string.Format("{0} {1} {2} {3}", selectSQL.ToString(), sqlWhere, sqlOrder, sqlOffSetLimit);
var totalRecs = db.Database.SqlQuery <Int32>(countSQLFinal, sVal);
IEnumerable <ContactListViewModel> displayedContacts;
if (!string.IsNullOrEmpty(sVal))
{
displayedContacts = db.Database.SqlQuery <ContactListViewModel>(selectSQLFinal, sVal).ToList();
}
else
{
displayedContacts = db.Database.SqlQuery <ContactListViewModel>(selectSQLFinal, sVal).ToList();
}
var dispRecs = displayedContacts.Count();
// generate array appropriate for dataTables.net javascript handlers
/*
* var q = from c in displayedContacts
* select new[]
* {
* c.ID.ToString(),
* c.FullName.ToString(),
* c.Title.ToString(),
* c.Phone.ToString(),
* c.Email.ToString(),
* c.CompanyName.ToString(),
* c.Honorific.ToString(),
* c.FirstName.ToString(),
* c.MiddleName.ToString(),
* c.LastName.ToString()
* };
*/
return(Json(new
{
sEcho = dTblParams.sEcho,
iTotalRecords = totalRecs.ElementAt(0),
iTotalDisplayRecords = totalRecs.ElementAt(0),
data = Json(displayedContacts).Data
},
"application/json",
JsonRequestBehavior.AllowGet));
}
public ActionResult GetCompaniesList(JQDTblParamModel dTblParams)
{
var allCompanies = db.Companies;
IEnumerable <Company> filteredCompanies;
if (!string.IsNullOrEmpty(dTblParams.sSearch))
{
filteredCompanies = db.Companies
.Where(c => c.Name.Contains(dTblParams.sSearch) ||
c.Address1.Contains(dTblParams.sSearch) ||
c.Address2.Contains(dTblParams.sSearch) ||
c.CityRegion.Contains(dTblParams.sSearch) ||
c.StateProvince.Contains(dTblParams.sSearch) ||
c.PostalCode.Contains(dTblParams.sSearch) ||
c.Country.Contains(dTblParams.sSearch) ||
c.Phone.Contains(dTblParams.sSearch) ||
c.Fax.Contains(dTblParams.sSearch));
}
else
{
filteredCompanies = allCompanies;
}
var sortColIndex = Convert.ToInt32(Request["iSortCol_0"]);
var sortDir = Request["sSortDir_0"];
Func <Company, string> sortOrder = (cSort => sortColIndex == 0 ? cSort.ID.ToString() :
sortColIndex == 1 ? cSort.Name :
sortColIndex == 2 ? cSort.Address1 :
sortColIndex == 3 ? cSort.CityRegion :
sortColIndex == 4 ? cSort.StateProvince :
sortColIndex == 5 ? cSort.PostalCode :
sortColIndex == 6 ? cSort.Phone :
sortColIndex == 7 ? cSort.Fax :
cSort.Name);
if (sortDir == "asc")
{
filteredCompanies = filteredCompanies.OrderBy(sortOrder);
}
else
{
filteredCompanies = filteredCompanies.OrderByDescending(sortOrder);
}
var displayedCompanies = filteredCompanies.Skip(dTblParams.iDisplayStart).Take(dTblParams.iDisplayLength);
var totalRecs = allCompanies.Count();
// TODO: sorting / filtering can get a little kooky (especially with multi-column sorts)
// explore using the dbContext SQL queries directly
var q = from c in displayedCompanies.AsEnumerable()
select new []
{
c.ID.ToString(),
c.Name.ToString(),
c.Address1.ToString(),
c.CityRegion.ToString(),
c.StateProvince.ToString(),
c.PostalCode.ToString(),
c.Phone.ToString(),
c.Fax.ToString(),
};
var dispRecs = q.Count();
return(Json(new
{
sEcho = dTblParams.sEcho,
iTotalRecords = totalRecs,
iTotalDisplayRecords = dispRecs,
aaData = q
},
JsonRequestBehavior.AllowGet));
}
