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