public Rank GetRank(int RankID)
{
var result = new Rank();
using (var reader = GetContext().GetReader(@"
SELECT
*
FROM Ranks
WHERE RankID = {0}
", RankID))
{
if(!reader.Read()) return null;
result.RankID = reader.GetInt32("RankID");
result.RankDescription = reader.GetString("RankDescription");
}
return result;
}
public Rank GetHighestRankAchievedInCurrentPeriod(int CustomerID, int PeriodTypeID)
{
var result = new Rank();
using (var reader = GetContext().GetReader(@"
SELECT
RankID = COALESCE(pv.RankID, 1),
RankDescription = COALESCE(r.RankDescription, 'Unknown')
FROM PeriodVolumes pv
LEFT JOIN Ranks r
ON r.RankID = pv.RankID
LEFT JOIN Periods p
ON p.PeriodID = pv.PeriodID
AND p.PeriodTypeID = pv.PeriodTypeID
WHERE pv.CustomerID = {0}
AND pv.PeriodTypeID = {1}
AND p.StartDate < {2}
AND p.EndDate >= {2}
", CustomerID, PeriodTypeID, DateTime.Now))
{
if(!reader.Read()) return null;
result.RankID = reader.GetInt32("RankID");
result.RankDescription = reader.GetString("RankDescription");
}
return result;
}
public Rank GetRank(int RankID)
{
var result = new Rank();
var response = GetContext().Ranks
.Where(c => c.RankID == RankID)
.FirstOrDefault();
if(response == null) return null;
result.RankID = response.RankID;
result.RankDescription = response.RankDescription;
return result;
}
public Rank GetHighestRankAchieved(int CustomerID)
{
var result = new Rank();
using (var reader = GetContext().GetReader(@"
SELECT
RankID = COALESCE(c.RankID, 1),
RankDescription = COALESCE(r.RankDescription, 'Unknown')
FROM Customers c
LEFT JOIN Ranks r
ON r.RankID = c.RankID
WHERE c.CustomerID = {0}
", CustomerID))
{
if(!reader.Read()) return null;
result.RankID = reader.GetInt32("RankID");
result.RankDescription = reader.GetString("RankDescription");
}
return result;
}
public Rank GetHighestRankAchievedInCurrentPeriod(int CustomerID, int PeriodTypeID)
{
var result = new Rank();
var response = GetContext().PeriodVolumes
.Where(c => c.CustomerID == CustomerID)
.Where(c => c.PeriodTypeID == PeriodTypeID)
.Where(c => c.Period.IsCurrentPeriod)
.Select(c => new
{
RankID = c.RankID,
RankDescription = c.Rank.RankDescription
})
.FirstOrDefault();
if(response == null) return null;
result.RankID = response.RankID;
result.RankDescription = GlobalUtilities.Coalesce(response.RankDescription, "Unknown");
return result;
}