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