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