public JsonResult AjaxBinding(KendoGridRequest request, string templateType)
{
var data = GetGridData(templateType);
var result = new KendoGrid <TemplateVO>(request, data.Skip((request.Page - 1) * request.PageSize).Take(request.PageSize), data.Count());
return(Json(result));
}
public async Task <KendoGridResponse <TDto> > GetByPageAsync <TDto>(
KendoGridRequest request,
string defaultSortExpression,
Expression <Func <TEntity, bool> > filter)
{
return(await GetByPageAsync <TDto>(request, defaultSortExpression, filter, null));
}
public virtual IQueryable GetAutoComplete(KendoGridRequest request)
{
// Get filter from KendoGridRequest (in case of kendoAutoComplete there is only 1 filter)
var filter = request.FilterObjectWrapper.FilterObjects.First();
// Change the field-name in the filter from ViewModel to Entity
string fieldOriginal = filter.Field1;
filter.Field1 = MapFieldfromViewModeltoEntity(filter.Field1);
// Query the database with the filter
var query = Service.AsQueryable().Where(filter.GetExpression1 <TEntity>());
// Apply paging if needed
if (request.PageSize != null)
{
query = query.Take(request.PageSize.Value);
}
// Do a linq dynamic query GroupBy to get only unique results
var groupingQuery = query.GroupBy(string.Format("it.{0}", filter.Field1), string.Format("new (it.{0} as Key)", filter.Field1));
// Make sure to return new objects which are defined as { "FieldName" : "Value" }, { "FieldName" : "Value" } else the kendoAutoComplete will not display search results.
return(groupingQuery.Select(string.Format("new (Key as {0})", fieldOriginal)));
}
public KendoGridResponse <TDto> GetByPage <TDto>(KendoGridRequest request, string defaultSortExpression, Expression <Func <TEntity, bool> > filter, IDictionary <string, object> projectionParameters)
{
var task = GetByPageAsync <TDto>(request, defaultSortExpression, filter, projectionParameters);
task.Wait();
return(task.Result);
}
public JsonResult Grid(KendoGridRequest request)
{
var employees = _employeeRepository.GetAll();
var data = new KendoGrid <Employee>(request, employees);
return(Json(data));
}
public ActionResult OrdersList(int id, KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(PartialView("Partials/OrdersList"));
}
// Establish the query
using (var context = new KendoGridDataContext(Exigo.Sql()))
{
return(context.Query <OrdersViewModel>(request, @"
Select
o.OrderDate
, CountryCode = o.Country
, o.CurrencyCode
, o.OrderID
, o.SubTotal
, o.BusinessVolumeTotal
, o.CommissionableVolumeTotal
FROM Orders o
WHERE o.CustomerID = @customerid
AND o.OrderStatusID >= @orderstatus",
new
{
customerid = id,
orderstatus = OrderStatuses.Accepted
}));
}
}
public ActionResult AutoOrdersList(int id, KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(PartialView("Partials/AutoOrdersList"));
}
using (var context = new KendoGridDataContext(Exigo.Sql()))
{
return(context.Query <OrdersViewModel>(request, @"
Select ao.AutoOrderID
, CountryCode = ao.Country
, ao.CurrencyCode
, ao.LastRunDate
, ao.NextRunDate
, ao.SubTotal
, ao.BusinessVolumeTotal
, ao.CommissionableVolumeTotal
From AutoOrders ao
Where ao.CustomerID = @customerid
and ao.AutoOrderStatusID = @autoorderstatus
", new
{
customerid = id,
autoorderstatus = 0
}));
}
}
public JsonResult Export(KendoGridFilter filter, string guid)
{
var gridRequest = new KendoGridRequest();
if (filter != null)
{
gridRequest.FilterObjectWrapper = filter.Filters != null?filter.ToFilterObjectWrapper() : null;
gridRequest.Logic = filter.Logic;
}
var query = GetQueryable().AsNoTracking();
var results = query.FilterBy <Employee, EmployeeVM>(gridRequest);
using (var stream = new MemoryStream())
{
using (var excel = new ExcelPackage(stream))
{
excel.Workbook.Worksheets.Add("Employees");
var ws = excel.Workbook.Worksheets[1];
ws.Cells.LoadFromCollection(results);
ws.Cells.AutoFitColumns();
excel.Save();
Session[guid] = stream.ToArray();
return(Json(new { success = true }));
}
}
}
public JsonResult Export(KendoGridRequest request)
{
var employees = GetEmployees();
var grid = new KendoGrid<Employee>(request, employees);
return Json(grid);
}
public ActionResult AutoOrdersList(int id, KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(PartialView("Partials/AutoOrdersList"));
}
//TODO Convert to SQL
// Establish the query
var query = Exigo.OData().AutoOrders
.Where(c => c.CustomerID == id)
.Where(c => c.AutoOrderStatusID == 0);
var context = new KendoGridDataContext();
return(context.Query(request, query, c => new
{
AutoOrderID = c.AutoOrderID,
LastRunDate = c.LastRunDate,
NextRunDate = c.NextRunDate,
SubTotal = c.SubTotal,
// the naming here is misleading BV is actually stored in the CV table, but has been left as is for sorting purposes
CommissionableVolumeTotal = c.CommissionableVolumeTotal
}));
}
public JsonNetResult GetHistoricalBonusDetails(KendoGridRequest request, int runid)
{
// Recursively get the details
dynamic data = new List <dynamic>();
int lastResultCount = request.PageSize;
int callsMade = 0;
while (lastResultCount == request.PageSize)
{
// Establish the query
var query = Exigo.OData().CommissionDetails
.Where(c => c.CustomerID == Identity.Current.CustomerID)
.Where(c => c.CommissionRunID == runid);
// Filtering
foreach (var filter in request.FilterObjectWrapper.FilterObjects)
{
query = query.Where(filter.Field1, filter.Operator1, filter.Value1);
}
// Sorting
foreach (var sort in request.SortObjects)
{
query = query.OrderBy(sort.Field, sort.Direction);
}
// Fetch the data
var results = query
.Select(c => new
{
c.BonusID,
Bonus_BonusDescription = c.Bonus.BonusDescription,
c.FromCustomerID,
FromCustomer_LastName = c.FromCustomer.FirstName + " " + c.FromCustomer.LastName,
c.Level,
c.PaidLevel,
c.SourceAmount,
c.Percentage,
c.CommissionAmount
})
.Skip(callsMade * request.PageSize)
.Take(request.PageSize)
.ToList();
data.AddRange(results);
callsMade++;
lastResultCount = results.Count;
}
// Return the data
return(new JsonNetResult(new
{
data = data
}));
}
public JsonResult Grid(KendoGridRequest request)
{
Mapper.CreateMap<Employee, EmployeeDto>();
var employees = _employeeRepository.All;
var dto = Mapper.Map<IEnumerable<Employee>, IEnumerable<EmployeeDto>>(employees);
var grid = new KendoGrid<EmployeeDto>(request, dto);
return Json(grid);
}
public JsonResult AjaxBinding(KendoGridRequest request, string keyWord, string downloadClassId)
{
SetConditions(keyWord, downloadClassId);
var data = GetGridData();
var result = new KendoGrid <DownloadRecord>(request, data.Skip((request.Page - 1) * request.PageSize).Take(request.PageSize), data.Count());
return(Json(result));
}
public JsonResult Grid2(KendoGridRequest request)
{
var sort = request.GetSorting();
var filter = request.GetFiltering <Employee>();
var employees = _employeeRepository.GetPaged(request.Skip, request.Take, sort, filter);
var count = _employeeRepository.GetCount(filter);
return(Json(new KendoGrid <Employee>(employees, count)));
}
public JsonResult Grid2(KendoGridRequest request)
{
var sort = request.GetSorting();
var filter = request.GetFiltering<Employee>();
var employees = _employeeRepository.GetPaged(request.Skip, request.Take, sort, filter);
var count = _employeeRepository.GetCount(filter);
return Json(new KendoGrid<Employee>(employees, count));
}
private void AppendSortingCondition(KendoGridRequest request)
{
if (request.SortObjects.Count() == 1)
{
SortObject sortObject = request.SortObjects.FirstOrDefault();
string field = sortObject.Field.Replace("GetStr_", "");
string direction = sortObject.Direction;
m_Conditions.Add("Order", string.Format("order by d.{0} {1}", field, direction));
}
}
public JsonNetResult GetHistoricalBonusDetails(KendoGridRequest request, int runid)
{
try
{
// Fetch the data
using (var context = new KendoGridDataContext(ExigoDAL.Sql()))
{
var data = context.Query(request, @"
SELECT
cd.BonusID
,b.BonusDescription
,cd.FromCustomerID
,FromCustomerName = c.FirstName + ' ' + c.LastName
,cd.Level
,cd.PaidLevel
,cd.SourceAmount
,cd.Percentage
,cd.CommissionAmount
FROM
CommissionDetails cd
INNER JOIN Customers c
ON c.CustomerID = cd.FromCustomerID
INNER JOIN Bonuses b
ON b.BonusID = cd.BonusID
WHERE
cd.CustomerID = @customerid
AND cd.CommissionRunID = @runid
", new
{
customerid = Identity.Current.CustomerID,
runid = runid
});
// Return the data
return(new JsonNetResult(new
{
data = data.Data
}));
}
}
catch (Exception ex)
{
return(new JsonNetResult(new
{
success = false,
message = ex.Message
}));
}
}
protected IQueryable <TDto> ApplyFiltering <TDto>(KendoGridRequest request, IQueryable <TDto> collection)
{
var filterCount = request.Filter?.Filters?.Count;
if (filterCount == null ||
filterCount == 0)
{
return(collection);
}
var filterExpressionBuilder = (FilterExpressionBuilder <TDto>)_serviceLocator.GetService(typeof(IFilterExpressionBuilder <TDto>));
return(filterExpressionBuilder.CreatedFilteredCollection(collection, request.Filter));
}
public ActionResult VolumeList(KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(View());
}
// Fetch the data
using (var context = new KendoGridDataContext(ExigoDAL.Sql()))
{
var results = context.Query(request, @"
SELECT
pv.PeriodID
,p.StartDate
,p.EndDate
,p.PeriodDescription
,pv.PaidRankID
,PaidRankDescription = ''
,pv.Volume1
,pv.Volume2
,pv.Volume3
FROM
PeriodVolumes pv
INNER JOIN Periods p
ON p.PeriodID = pv.PeriodID
INNER JOIN Ranks r
ON r.RankID = pv.PaidRankID
WHERE
pv.CustomerID = @customerid
AND pv.PeriodTypeID = @periodtypeid
AND p.StartDate <= @startdate
", new
{
customerid = Identity.Current.CustomerID,
periodtypeid = PeriodTypes.Default,
startdate = DateTime.Now.ToCST()
});
// get the translated paid rank description
foreach (var item in results.Data)
{
item.PaidRankDescription = CommonResources.Ranks(item.PaidRankID, CommonResourceFormat.Default);
}
return(results);
}
}
public JsonResult AjaxBinding(KendoGridRequest request, string enterTimeFrom, string enterTimeTo)
{
SetConditions(enterTimeFrom, enterTimeTo);
int total = GetGridTotal();
int pageIndex = (request.Page - 1);
m_Conditions.Add("PageIndex", pageIndex.ToString());
m_Conditions.Add("PageSize", request.PageSize.ToString());
AppendSortingCondition(request);
var data = GetGridData();
var result = new KendoGrid <MasterLog>(request, data, total);
return(Json(result));
}
public JsonResult AjaxBinding(KendoGridRequest request, string keyWord, string nodeId)
{
SetConditions(keyWord, nodeId);
int total = GetGridTotal();
int pageIndex = (request.Page - 1);
m_Conditions.Add("PageIndex", pageIndex.ToString());
m_Conditions.Add("PageSize", request.PageSize.ToString());
AppendSortingCondition(request);
var data = GetGridData();
var result = new KendoGrid <Post>(request, data, total);
return(Json(result));
}
public JsonResult AjaxBinding(KendoGridRequest request, string keyWord, string downloadClassId, string memberLoginId)
{
SetConditions(keyWord, downloadClassId, memberLoginId);
int total = GetGridTotal();
int pageIndex = (request.Page - 1);
m_Conditions.Add("PageIndex", pageIndex.ToString());
m_Conditions.Add("PageSize", request.PageSize.ToString());
AppendSortingCondition(request);
var data = GetGridData();
var result = new KendoGrid <DownloadRecord>(request, data, total);
return(Json(result));
}
public JsonResult Grid2(KendoGridRequest request)
{
var employees = new List<Employee>
{
new Employee { EmployeeId = 1, HireDate = new DateTime(2012, 1, 13), FirstName = "Bill", LastName = "Jones", Email = "*****@*****.**" },
new Employee { EmployeeId = 2, HireDate = new DateTime(2012, 2, 16), FirstName = "Rob", LastName = "aaaaaa", Email = "*****@*****.**" },
new Employee { EmployeeId = 3, HireDate = new DateTime(2012, 3, 14), FirstName = "Jane", LastName = "ssss", Email = "*****@*****.**" },
new Employee { EmployeeId = 4, HireDate = new DateTime(2012, 4, 12), FirstName = "bbb", LastName = "ggg", Email = "*****@*****.**" },
new Employee { EmployeeId = 5, HireDate = new DateTime(2012, 5, 1), FirstName = "cccc", LastName = "ff", Email = "*****@*****.**" },
new Employee { EmployeeId = 6, HireDate = new DateTime(2012, 7, 12), FirstName = "dddd", LastName = "eeee", Email = "*****@*****.**" },
};
var grid = new KendoGrid<Employee>(request, employees);
return Json(grid);
}
/// <summary>
/// Get Events for the Kendo Scheduler
/// </summary>
/// <returns>JSON Net Result</returns>
public ActionResult GetEvents(KendoGridRequest request)
{
// Establish the query
var query = Exigo.GetCalendarEvents(new GetCalendarEventsRequest()
{
CustomerID = Identity.Current.CustomerID,
IncludeCalendarSubscriptions = true
}).AsQueryable();
// Fetch the data
var context = new KendoGridDataContext();
return(context.Query(request, query, c => new
{
c.ID,
c.Title,
c.Description,
Start = c.Start.ToString("o"),
End = c.End.ToString("o"),
c.StartTimezone,
c.EndTimezone,
c.RecurrenceID,
c.RecurrenceRule,
c.RecurrenceException,
c.IsAllDay,
c.CalendarID,
c.CalendarEventTypeID,
c.CalendarEventPrivacyTypeID,
c.CreatedBy,
CreatedDate = c.CreatedDate.ToString("s"),
Tags = string.Join(", ", c.Tags),
Location_Address1 = c.Location.Address1,
Location_Address2 = c.Location.Address2,
Location_City = c.Location.City,
Location_State = c.Location.State,
Location_Zip = c.Location.Zip,
Location_Country = c.Location.Country,
c.SpeakerID,
c.Phone,
c.Flyer,
c.Cost,
c.ConferenceNumber,
c.ConferencePIN,
c.Url
}));
}
protected IQueryable <TDto> ApplySorting <TDto>(KendoGridRequest request, IQueryable <TDto> collection, string defaultSortExpression)
{
if (request.Sort == null || request.Sort.Count <= 0)
{
return(collection.OrderBy(defaultSortExpression));
}
var sortableExpressionMapper = (IDynamicSortingService <TDto>)_serviceLocator.GetService(typeof(IDynamicSortingService <TDto>));
var result = request.Sort.Aggregate(collection,
(current, sortItem) =>
{
return(sortableExpressionMapper.OrderBy(collection, sortItem.Field, sortItem.Dir));
});
return(result);
}
public async Task <KendoGridResponse <TDto> > GetByPageAsync <TDto>(
KendoGridRequest request,
string defaultSortExpression,
Expression <Func <TEntity, bool> > filter,
IDictionary <string, object> projectionParameters)
{
var query = EntityRepository.Get(filter);
var dtoQuery = query.ProjectTo <TDto>(projectionParameters);
dtoQuery = ApplyFiltering(request, dtoQuery);
dtoQuery = ApplySorting(request, dtoQuery, defaultSortExpression);
return
(await KendoGridResponse <TDto> .
GenerateResponseAsync(dtoQuery, request.Page, request.PageSize));
}
public JsonResult AjaxBinding(KendoGridRequest request, string keyWord, string company, string companyTrade,
string companyNationality, string companyScale, string companyType, string isAA1000, string isGRI,
string postYearFrom, string postYearTo, string reportYearFrom, string reportYearTo)
{
SetConditions(keyWord, company, companyTrade, companyNationality, companyScale, companyType, isAA1000,
isGRI, postYearFrom, postYearTo, reportYearFrom, reportYearTo);
int total = GetGridTotal();
int pageIndex = (request.Page - 1);
m_Conditions.Add("PageIndex", pageIndex.ToString());
m_Conditions.Add("PageSize", request.PageSize.ToString());
AppendSortingCondition(request);
var data = GetGridData();
var result = new KendoGrid <Report>(request, data, total);
return(Json(result));
}
public ActionResult VolumeList(KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(View());
}
// Establish the query
using (var context = new KendoGridDataContext(Exigo.Sql()))
{
return(context.Query(request, @"
SELECT
pv.PeriodID,
p.StartDate,
p.EndDate,
p.PeriodDescription,
pv.PaidRankID,
r.RankDescription,
Team1TGBV = pv.Volume55,
Team2TGBV = pv.Volume56,
Team3TGBV = pv.Volume57,
Team4TGBV = pv.Volume58,
Team5TGBV = pv.Volume59,
Team6TGBV = pv.Volume91
FROM
PeriodVolumes pv
inner join Periods p
on p.PeriodID = pv.PeriodID
and p.PeriodTypeID = pv.PeriodTypeID
left join Ranks r
on r.RankID = pv.PaidRankID
WHERE
p.StartDate <= GetDate() + 1
AND pv.PeriodTypeID = @periodtypeid
and pv.CustomerID = @id
", new
{
id = Identity.Current.CustomerID,
periodtypeid = PeriodTypes.Default
}).Tokenize("CustomerID"));
}
}
public ActionResult VolumesList(int id, KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(PartialView("Partials/VolumesList"));
}
using (var context = new KendoGridDataContext(Exigo.Sql()))
{
return(context.Query(request, @"
SELECT
p.PeriodID
, p.StartDate
, p.EndDate
, p.PeriodDescription
, r.RankDescription
, pv.PaidRankID
, pv.Volume1
, pv.Volume2
, pv.Volume3
, pv.Volume4
, pv.Volume5
FROM Customers c
INNER JOIN Periods p
ON p.EndDate > c.CreatedDate
AND p.PeriodTypeID = @periodtype
AND p.StartDate <= GETDATE()
INNER JOIN PeriodVolumes pv
ON pv.PeriodID = p.PeriodID
AND pv.PeriodTypeID = p.PeriodTypeID
AND pv.CustomerID = c.CustomerID
LEFT JOIN Ranks r
ON r.RankID = pv.PaidRankID
WHERE c.CustomerID = @customerid
", new
{
customerid = id,
periodtype = PeriodTypes.Default
}).Tokenize("CustomerID"));
}
}
public ActionResult VolumesList(int id, KendoGridRequest request = null)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
return(PartialView("Partials/VolumesList"));
}
//TODO Convert to SQL
// Get the customer's start date
var customerStartDate = Exigo.OData().Customers
.Where(c => c.CustomerID == id)
.Select(c => c.CreatedDate)
.Single();
// Establish the query
var query = Exigo.OData().PeriodVolumes
.Where(c => c.CustomerID == id)
.Where(c => c.PeriodTypeID == PeriodTypes.Default)
.Where(c => c.Period.StartDate <= DateTime.Now)
.Where(c => c.Period.EndDate > customerStartDate);
// Fetch the data
var context = new KendoGridDataContext();
return(context.Query(request, query, c => new
{
PeriodID = c.PeriodID,
Period_EndDate = c.Period.EndDate,
PaidRank_RankDescription = c.PaidRank.RankDescription,
Volume55 = c.Volume55,
Volume56 = c.Volume56,
Volume57 = c.Volume57,
Volume58 = c.Volume58,
Volume59 = c.Volume59
}));
}
public async Task <KendoGridResponse <TDto> > GetByPageAsync <TDto>(KendoGridRequest request,
string defaultSortExpression)
{
return(await GetByPageAsync <TDto>(request, defaultSortExpression, null));
}
public JsonResult Grid(KendoGridRequest request)
{
var employees = _employeeRepository.GetAll();
var data = new KendoGrid<Employee>(request, employees);
return Json(data);
}
public JsonNetResult GetRealTimeBonusDetails(KendoGridRequest request)
{
var results = new List <RealTimeCommissionBonusDetail>();
// Get the commission record(s)
var context = Exigo.WebService();
var realtimeresponse = context.GetRealTimeCommissions(new Common.Api.ExigoWebService.GetRealTimeCommissionsRequest
{
CustomerID = Identity.Current.CustomerID
});
if (realtimeresponse.Commissions.Length == 0)
{
return(new JsonNetResult());
}
// Get the bonuses (I know, this is brutal, but our UI depends on it)
foreach (var commission in realtimeresponse.Commissions)
{
var bonuses = commission.Bonuses
.Select(c => new CommissionBonus()
{
BonusID = c.BonusID,
BonusDescription = c.Description
}).Distinct();
foreach (var bonusID in commission.Bonuses.Select(c => c.BonusID).Distinct())
{
var bonus = bonuses.Where(c => c.BonusID == bonusID).FirstOrDefault();
// Get the details for this bonus
var details = context.GetRealTimeCommissionDetail(new Common.Api.ExigoWebService.GetRealTimeCommissionDetailRequest
{
CustomerID = commission.CustomerID,
PeriodType = commission.PeriodType,
PeriodID = commission.PeriodID,
BonusID = bonusID
}).CommissionDetails;
// Get the period details for this period
var period = Exigo.GetPeriods(new GetPeriodsRequest
{
PeriodTypeID = commission.PeriodType,
PeriodIDs = new int[] { commission.PeriodID }
}).FirstOrDefault();
// Format and save each bonus
foreach (var detail in details)
{
var typedDetail = (CommissionBonusDetail)detail;
var result = GlobalUtilities.Extend(typedDetail, new RealTimeCommissionBonusDetail());
result.BonusID = bonus.BonusID;
result.BonusDescription = bonus.BonusDescription;
result.PeriodDescription = period.PeriodDescription;
results.Add(result);
}
}
}
// Filtering
foreach (var filter in request.FilterObjectWrapper.FilterObjects)
{
results = results.AsQueryable().Where(filter.Field1, filter.Operator1, filter.Value1).ToList();
}
// Sorting
foreach (var sort in request.SortObjects)
{
results = results.AsQueryable().OrderBy(sort.Field, sort.Direction).ToList();
}
// Return the data
return(new JsonNetResult(new
{
data = results
}));
}
public JsonResult GridTop3(KendoGridRequest request)
{
var entities = _productService.GetTop3Products();
return(GetKendoGridAsJson(request, entities));
}
public KendoGridResponse <TDto> GetByPage <TDto>(KendoGridRequest request, string defaultSortExpression)
{
return(GetByPage <TDto>(request, defaultSortExpression, null));
}
