public static IEnumerable<CustomerRankScore> GetDownlineUpcomingPromotions(GetDownlineUpcomingPromotionsRequest request)
{
var results = new List<CustomerRankScore>();
var context = Exigo.Sql();
context.Open();
results = context.Query<CustomerRankScore, Rank, CustomerRankScore>(@"
SELECT c.CustomerID,
c.FirstName,
c.LastName,
c.Company,
RankScore = Score,
TotalScore = Score * prso.PaidRankID,
RankID = prso.PaidRankID,
r.RankDescription
FROM (SELECT prs.PeriodTypeID,
prs.PeriodID,
uld.CustomerID,
PaidRankID=Min(PaidRankID)
FROM UniLevelDownline uld
INNER JOIN PeriodRankScores prs
ON prs.PeriodTypeID = @periodtypeid
AND prs.PeriodID = (SELECT periodid
FROM periods
WHERE PeriodTypeID = @periodtypeid
AND Getdate() >= StartDate
AND Getdate() < EndDate + 1
)
AND prs.CustomerID = uld.CustomerID
WHERE uld.DownlineCustomerID = @downlinecustomerid
AND prs.Score < 100
" + ((request.RankID != null) ? "AND PaidRankID = @rankid" : "") + @"
GROUP BY prs.PeriodTypeID,
prs.PeriodID,
uld.CustomerID) i
INNER JOIN Customers c
ON i.CustomerID = c.CustomerID
INNER JOIN PeriodRankScores prso
ON i.PeriodTypeID = prso.PeriodTypeID
AND i.PeriodID = prso.PeriodID
AND i.CustomerID = prso.CustomerID
AND i.PaidRankID = prso.PaidRankID
INNER JOIN Ranks r
ON prso.PaidRankID = r.RankID
ORDER BY
Score * prso.PaidRankID DESC,
c.CreatedDate ASC
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY
", (customer, rank) => {
customer.Rank = rank;
return customer;
},
new
{
downlinecustomerid = request.DownlineCustomerID,
periodtypeid = request.PeriodTypeID,
rankid = request.RankID,
skip = request.Skip,
take = request.Take
}, splitOn: "RankID").ToList();
context.Close();
foreach (var result in results)
{
yield return result;
}
}
public static IEnumerable <CustomerRankScore> GetDownlineUpcomingPromotions(GetDownlineUpcomingPromotionsRequest request)
{
var results = new List <CustomerRankScore>();
var context = Exigo.Sql();
context.Open();
results = context.Query <CustomerRankScore, Rank, CustomerRankScore>(@"
SELECT c.CustomerID,
c.FirstName,
c.LastName,
c.Company,
RankScore = Score,
TotalScore = Score * prso.PaidRankID,
RankID = prso.PaidRankID,
r.RankDescription
FROM (SELECT prs.PeriodTypeID,
prs.PeriodID,
uld.CustomerID,
PaidRankID=Min(PaidRankID)
FROM UniLevelDownline uld
INNER JOIN PeriodRankScores prs
ON prs.PeriodTypeID = @periodtypeid
AND prs.PeriodID = (SELECT periodid
FROM periods
WHERE PeriodTypeID = @periodtypeid
AND Getdate() >= StartDate
AND Getdate() < EndDate + 1
)
AND prs.CustomerID = uld.CustomerID
WHERE uld.DownlineCustomerID = @downlinecustomerid
AND prs.Score < 100
" + ((request.RankID != null) ? "AND PaidRankID = @rankid" : "") + @"
GROUP BY prs.PeriodTypeID,
prs.PeriodID,
uld.CustomerID) i
INNER JOIN Customers c
ON i.CustomerID = c.CustomerID
INNER JOIN PeriodRankScores prso
ON i.PeriodTypeID = prso.PeriodTypeID
AND i.PeriodID = prso.PeriodID
AND i.CustomerID = prso.CustomerID
AND i.PaidRankID = prso.PaidRankID
INNER JOIN Ranks r
ON prso.PaidRankID = r.RankID
ORDER BY
Score * prso.PaidRankID DESC,
c.CreatedDate ASC
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY
", (customer, rank) => {
customer.Rank = rank;
return(customer);
},
new
{
downlinecustomerid = request.DownlineCustomerID,
periodtypeid = request.PeriodTypeID,
rankid = request.RankID,
skip = request.Skip,
take = request.Take
}, splitOn: "RankID").ToList();
context.Close();
foreach (var result in results)
{
yield return(result);
}
}
public static GetDownlineUpcomingPromotionsResponse GetDownlineUpcomingPromotions(GetDownlineUpcomingPromotionsRequest request)
{
var response = new GetDownlineUpcomingPromotionsResponse();
// Determine if we passed a KendoGridRequest.
// If we did, use the page and pagesize from the request instead, as it takes priority due to it's unique implications.
var whereClause = string.Empty;
if (request.KendoGridRequest != null)
{
request.Page = request.KendoGridRequest.Page;
request.RowCount = request.KendoGridRequest.PageSize;
request.TotalRowCount = request.KendoGridRequest.Total;
whereClause = request.KendoGridRequest.SqlWhereClause;
whereClause = whereClause.Replace("RankScore", "Score");
whereClause = whereClause.Replace("CustomerID", "c.CustomerID");
}
var results = new List <CustomerRankScore>();
//calling store Procedure "GetDownlineUpcomingPromotions"
int periodTypeID = request.PeriodTypeID;
int downlineCustomerID = request.DownlineCustomerID;
string strRankID = (request.RankID != null) ? "AND PaidRankID = " + request.RankID + @"" : "0";
int skip = request.Skip;
int take = request.Take;
string sortingOrder = " ORDER BY ";
//need to append defualt sorting in query
sortingOrder += KendoUtilities.GetSqlOrderByClause((request.KendoGridRequest != null) ? request.KendoGridRequest.SortObjects :
new List <SortObject>(),
new SortObject("TotalScore", "DESC"),
new SortObject("c.CreatedDate", "ASC"));
using (var sqlcontext = Exigo.Sql())
{
sqlcontext.Open();
string sqlProcedure = string.Format(@"GetDownlineUpcomingPromotions {0},{1},'{2}','{3}','{4}',{5},{6}", periodTypeID, downlineCustomerID, strRankID, whereClause.Replace("'", "''"), sortingOrder, skip, take);
results = sqlcontext.Query <CustomerRankScore, Rank, CustomerRankScore>(sqlProcedure, (customer, rank) =>
{
customer.Rank = rank;
return(customer);
}, splitOn: "RankID").ToList();
sqlcontext.Close();
response.CustomerRankScores = results;
}
if (request.KendoGridRequest != null && response.CustomerRankScores != null && response.CustomerRankScores.Count() > 0)
{
response.TotalCount = response.CustomerRankScores.FirstOrDefault().TotalRows;
}
else
{
response.TotalCount = response.CustomerRankScores.Count();
}
return(response);
}
