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