/// <summary> /// Based on requested Enroller, we need to find the next sponsor that falls within their placement preference tree /// </summary> /// <param name="enrollerID">Enroller to look under to find our Sponsor ID</param> /// <returns>Sponsor ID</returns> public static int GetCustomersSponsorPreference(int enrollerID) { var sponsorID = 0; try { // Try to use the team query to pull the customer's preferred placement Team var preferredTeamID = 0; using (var context = Exigo.Sql()) { preferredTeamID = context.Query <int>(@" select top 1 COALESCE(NULLIF(Field1,''), '0') from Customers where CustomerID = @enrollerID ", new { enrollerID = enrollerID }).FirstOrDefault(); } // In case this has not been set, we default to Team 1 if (preferredTeamID == 0) { preferredTeamID = 1; } var periodID = Exigo.GetCurrentPeriod((int)PeriodTypes.Monthly).PeriodID; // Now we run our query, if it fails then we return enrollerID vs. 0, which would fail placement using (var context = Exigo.Sql()) { var query = context.Query <CustomerNode>(@" Declare @PeriodTy int = 1, @CustomerID int = @enrollerID ;with cte_Primary as ( Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 0 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Where c.CustomerID = @CustomerID ), cte_Team1 as ( Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 1 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Primary team on team.Volume50 = c.CustomerID UNION ALL Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 1 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Team1 team on team.Volume50 = c.CustomerID ), cte_Team2 as ( Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 2 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Primary team on team.Volume51 = c.CustomerID UNION ALL Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 2 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Team2 team on team.Volume50 = c.CustomerID ), cte_Team3 as ( Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 3 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Primary team on team.Volume52 = c.CustomerID UNION ALL Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 3 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Team3 team on Team.Volume50 = c.CustomerID ), cte_Team4 as ( Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 4 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Primary team on team.Volume53 = c.CustomerID UNION ALL Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 4 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Team4 team on team.Volume50 = c.CustomerID ), cte_Team5 as ( Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 5 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Primary team on team.Volume54 = c.CustomerID UNION ALL Select c.CustomerID, pv.Volume50, pv.Volume51, pv.Volume52, pv.Volume53, pv.Volume54, 5 as 'team' From Customers c Inner Join PeriodVolumes pv on pv.CustomerID = c.CustomerID and pv.PeriodID = @PeriodID and pv.PeriodTypeID = @PeriodTy Inner Join cte_Team5 team on team.Volume50 = c.CustomerID ), cte_combine as ( Select Team, CustomerID, Volume50, Volume51, Volume52, Volume53, Volume54 From cte_Primary UNION Select Team, CustomerID, Volume50, Volume51, Volume52, Volume53, Volume54 From cte_Team1 UNION Select Team, CustomerID, Volume50, Volume51, Volume52, Volume53, Volume54 From cte_Team2 UNION Select Team, CustomerID, Volume50, Volume51, Volume52, Volume53, Volume54 From cte_Team3 UNION Select Team, CustomerID, Volume50, Volume51, Volume52, Volume53, Volume54 From cte_Team4 UNION Select Team, CustomerID, Volume50, Volume51, Volume52, Volume53, Volume54 From cte_Team5 ) select Team, CustomerID From cte_combine where (Team = @preferredTeamID and Volume50 = 0) Order By team, CustomerID option (maxrecursion 0)", new { enrollerID = enrollerID, PeriodID = periodID, preferredTeamID = preferredTeamID }).FirstOrDefault(); if (query != null && query.CustomerID != 0) { sponsorID = query.CustomerID; } else { sponsorID = enrollerID; } } } catch (Exception ex) { // All else fails, return the enroller id instead of the sponsor id - Mike M. sponsorID = enrollerID; Console.Write(ex); } return(sponsorID); }
public static VolumeCollection GetCustomerVolumes(GetCustomerVolumesRequest request) { var periodID = request.PeriodID; var periodTypeID = request.PeriodTypeID; if (periodID == null) { periodID = Exigo.GetCurrentPeriod(periodTypeID).PeriodID; } VolumeCollection volumes = new VolumeCollection(); // Determine if we need to pull all Period Volumes or if we are passing in our own list of Volumes to fetch int totalVolumeBuckets = 200; string volumeSelectQuery = ""; if (request.VolumesToFetch == null || request.VolumesToFetch.Count() == 0) { request.VolumesToFetch = new List <int>(); for (int i = 1; i <= totalVolumeBuckets; i++) { request.VolumesToFetch.Add(i); } } for (int i = 0, length = request.VolumesToFetch.Count(); i < length; i++) { var volumeBucket = request.VolumesToFetch[i]; volumeSelectQuery = volumeSelectQuery + " , Volume{0} = isnull(pv.Volume{0}, 0)".FormatWith(volumeBucket); } using (var context = Exigo.Sql()) { volumes = context.Query <VolumeCollection, Period, Rank, Rank, VolumeCollection>(@" Select c.CustomerID , ModifiedDate = isnull(pv.ModifiedDate, '01/01/1900') " + volumeSelectQuery + @" , PeriodID = p.PeriodID , PeriodTypeID = p.PeriodTypeID , PeriodDescription = p.PeriodDescription , StartDate = p.StartDate , EndDate = p.EndDate , RankID = isnull(pv.RankID,0) , RankDescription = isnull(r.RankDescription, '') , RankID = isnull(pv.PaidRankID,0) , RankDescription = isnull(pr.RankDescription, '') FROM Customers c LEFT JOIN PeriodVolumes pv ON pv.CustomerID = c.CustomerID LEFT JOIN Periods p ON pv.PeriodID = p.PeriodID AND pv.PeriodTypeID = p.PeriodTypeID LEFT JOIN Ranks r ON r.RankID = c.RankID LEFT JOIN Ranks pr ON pr.RankID = pv.PaidRankID WHERE pv.CustomerID = @CustomerID AND p.PeriodTypeID = @PeriodTypeID AND p.PeriodID = @PeriodID ", (vc, p, hr, pr) => { vc.Period = p; vc.HighestAchievedRankThisPeriod = hr; vc.PayableAsRank = pr; return(vc); } , param: new { CustomerID = request.CustomerID, PeriodTypeID = request.PeriodTypeID, PeriodID = periodID } , splitOn: "PeriodID, RankID, RankID" ).FirstOrDefault(); } return((volumes != null) ? volumes : new VolumeCollection()); }
public static CustomerRankCollection GetCustomerRanks(GetCustomerRanksRequest request) { var result = new CustomerRankCollection(); var periodID = (request.PeriodID != null) ? request.PeriodID : Exigo.GetCurrentPeriod(request.PeriodTypeID).PeriodID; //Get the highest paid rank in any period from the customer record var highestRankAchieved = new Rank(); using (var context = Exigo.Sql()) { highestRankAchieved = context.Query <Rank>(@" SELECT c.RankID ,r.RankDescription FROM Customers c INNER JOIN Ranks r ON r.RankID = c.RankID WHERE c.CustomerID = @customerid ", new { customerid = request.CustomerID }).FirstOrDefault(); if (highestRankAchieved != null) { result.HighestPaidRankInAnyPeriod = highestRankAchieved; } } //Get the current period rank for the period/period type specified var currentPeriodRank = new Rank(); using (var context = Exigo.Sql()) { currentPeriodRank = context.Query <Rank>(@" SELECT RankID = pv.PaidRankID ,r.RankDescription FROM PeriodVolumes pv INNER JOIN Ranks r ON r.RankID = pv.PaidRankID WHERE pv.CustomerID = @customerid AND pv.PeriodTypeID = @periodtypeid AND pv.PeriodID = @periodid ", new { customerid = request.CustomerID, periodtypeid = request.PeriodTypeID, periodid = periodID }).FirstOrDefault(); if (currentPeriodRank != null) { result.CurrentPeriodRank = currentPeriodRank; } } //Get the highest paid rank up to the specified period var highestPaidRankUpToPeriod = new Rank(); using (var context = Exigo.Sql()) { highestPaidRankUpToPeriod = context.Query <Rank>(@" SELECT pv.RankID ,r.RankDescription FROM PeriodVolumes pv INNER JOIN Ranks r ON r.RankID = pv.RankID WHERE pv.CustomerID = @customerid AND pv.PeriodTypeID = @periodtypeid AND pv.PeriodID = @periodid ", new { customerid = request.CustomerID, periodtypeid = request.PeriodTypeID, periodid = periodID }).FirstOrDefault(); if (highestPaidRankUpToPeriod != null) { result.HighestPaidRankUpToPeriod = highestPaidRankUpToPeriod; } } return(result); }
public static CustomerRankCollection GetCustomerRanks(GetCustomerRanksRequest request) { var result = new CustomerRankCollection(); int?periodId; int periodtypeId; int customerId; if (request.PeriodID != null) { periodId = request.PeriodID; } else { periodId = Exigo.GetCurrentPeriod(PeriodTypes.Monthly).PeriodID; } periodtypeId = request.PeriodTypeID; customerId = request.CustomerID; using (SqlConnection cnn = Exigo.Sql()) { cnn.Open(); SqlCommand cmd = new SqlCommand("dbo.GetCustomerRankCollection", cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@customerid", request.CustomerID)); cmd.Parameters.Add(new SqlParameter("@periodtypeid", periodtypeId)); cmd.Parameters.Add(new SqlParameter("@periodid", periodId)); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { result.HighestPaidRankInAnyPeriod.RankID = (int)rdr["RankID"]; result.HighestPaidRankInAnyPeriod.RankDescription = (string)rdr["RankDescription"]; result.CurrentPeriodRank.RankID = (int)rdr["PeriodPaidRankId"]; result.CurrentPeriodRank.RankDescription = (string)rdr["PeriodPaidRankDescription"]; result.HighestPaidRankUpToPeriod.RankID = (int)rdr["PeriodRankId"]; result.HighestPaidRankUpToPeriod.RankDescription = (string)rdr["PeriodRankDescription"]; } } } Rank highestRankAchieved = null; using (var context2 = Exigo.Sql()) { string sqlProcedure = string.Format("GetCustomerRank {0}", request.CustomerID); highestRankAchieved = context2.Query <Rank>(sqlProcedure).FirstOrDefault(); context2.Close(); } if (highestRankAchieved != null) { result.HighestPaidRankInAnyPeriod = (Rank)highestRankAchieved; } List <VolumeCollection> query = null; using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("GetPeriodVolumes {0},{1},'{2}'", request.CustomerID, request.PeriodTypeID, DateTime.Now); query = context.Query <VolumeCollection>(SqlProcedure).ToList(); } if (request.PeriodID != null) { query = query.Where(c => c.PeriodID == request.PeriodID).ToList(); } else { query = query.Where(c => c.Period.PeriodID == GetCurrentPeriod(request.PeriodTypeID).PeriodID).ToList(); } var periodRanks = query.Select(c => new { c.Rank, c.PaidAsRank }).FirstOrDefault(); if (periodRanks != null) { if (periodRanks.PaidAsRank != null) { result.CurrentPeriodRank = (Rank)periodRanks.PaidAsRank; } if (periodRanks.Rank != null) { result.HighestPaidRankUpToPeriod = (Rank)periodRanks.Rank; } } return(result); }