public static CustomerRankCollection GetCustomerRanks(GetCustomerRanksRequest request)
{
var result = new CustomerRankCollection();
var context = Exigo.OData();
// Get the highest rank achieved in the customer table
var highestRankAchieved = context.Customers
.Where(c => c.CustomerID == request.CustomerID)
.Select(c => new
{
c.Rank
}).FirstOrDefault();
if (highestRankAchieved != null)
{
result.HighestPaidRankInAnyPeriod = (Rank)highestRankAchieved.Rank;
}
// Get the period ranks
var query = context.PeriodVolumes
.Where(c => c.CustomerID == request.CustomerID)
.Where(c => c.PeriodTypeID == request.PeriodTypeID);
if (request.PeriodID != null)
{
query = query.Where(c => c.PeriodID == (int)request.PeriodID);
}
else
{
query = query.Where(c => c.Period.IsCurrentPeriod);
}
var periodRanks = query.Select(c => new
{
c.Rank,
c.PaidRank
}).FirstOrDefault();
if (periodRanks != null)
{
if (periodRanks.PaidRank != null)
{
result.CurrentPeriodRank = (Rank)periodRanks.PaidRank;
}
if (periodRanks.Rank != null)
{
result.HighestPaidRankUpToPeriod = (Rank)periodRanks.Rank;
}
}
return(result);
}
public static CustomerRankCollection GetCustomerRanks(GetCustomerRanksRequest request)
{
var result = new CustomerRankCollection();
var context = Exigo.OData();
// Get the highest rank achieved in the customer table
var highestRankAchieved = context.Customers
.Where(c => c.CustomerID == request.CustomerID)
.Select(c => new
{
c.Rank
}).FirstOrDefault();
if (highestRankAchieved != null)
{
result.HighestPaidRankInAnyPeriod = (Rank)highestRankAchieved.Rank;
}
// Get the period ranks
var query = context.PeriodVolumes
.Where(c => c.CustomerID == request.CustomerID)
.Where(c => c.PeriodTypeID == request.PeriodTypeID);
if (request.PeriodID != null)
{
query = query.Where(c => c.PeriodID == (int)request.PeriodID);
}
else
{
query = query.Where(c => c.Period.IsCurrentPeriod);
}
var periodRanks = query.Select(c => new
{
c.Rank,
c.PaidRank
}).FirstOrDefault();
if (periodRanks != null)
{
if (periodRanks.PaidRank != null)
{
result.CurrentPeriodRank = (Rank)periodRanks.PaidRank;
}
if (periodRanks.Rank != null)
{
result.HighestPaidRankUpToPeriod = (Rank)periodRanks.Rank;
}
}
return result;
}
public static CustomerRankCollection GetCustomerRanks(GetCustomerRanksRequest request)
{
var result = new CustomerRankCollection();
var periodID = (request.PeriodID != null) ? request.PeriodID : ExigoDAL.GetCurrentPeriod(request.PeriodTypeID).PeriodID;
//Get the highest paid rank in any period from the customer record
var highestRankAchieved = new Rank();
using (var context = ExigoDAL.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 = ExigoDAL.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 = ExigoDAL.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);
}