public static HistoricalCommission GetCustomerHistoricalCommission(int customerID, int commissionRunID, bool includeVolumes = true)
{
// Get the commission record
var commission = new HistoricalCommission();
using (var context = ExigoDAL.Sql())
{
commission = context.Query <HistoricalCommission, Rank, Period, HistoricalCommission>(@"
SELECT c.CommissionRunID
,c.CustomerID
,c.CurrencyCode
,c.Earnings
,c.PreviousBalance
,c.BalanceForward
,c.Fee
,c.Total
,cr.CommissionRunDescription
,cr.PeriodTypeID
,cr.RunDate
,cr.CommissionRunStatusID
,cr.HideFromWeb
,cr.PlanID
,RankID = pv.PaidRankID
,r.RankDescription
,cr.PeriodID
,p.PeriodDescription
,p.PeriodTypeID
,p.StartDate
,p.EndDate
,cr.AcceptedDate
FROM Commissions c
LEFT JOIN CommissionRuns cr
ON c.CommissionRunID = cr.CommissionRunID
LEFT JOIN Periods p
ON cr.periodid = p.periodid
and cr.periodtypeid = p.periodtypeid
LEFT JOIN PeriodVolumes pv
ON pv.periodid = p.periodid
and pv.periodtypeid = p.periodtypeid
and pv.customerid = c.customerid
LEFT JOIN Ranks r
ON r.RankID = pv.PaidRankID
WHERE c.CustomerID = @CustomerID
AND c.CommissionRunID = @CommissionRunID
ORDER BY cr.CommissionRunID DESC
", (hc, r, p) =>
{
hc.Period = p;
hc.PaidRank = r;
return(hc);
}, splitOn: "RankID,PeriodID",
param: new
{
CustomerID = customerID,
CommissionRunID = commissionRunID
}).FirstOrDefault();
}
if (commission == null)
{
return(null);
}
var result = commission;
// Get the volumes
if (includeVolumes)
{
result.Volumes = GetCustomerVolumes(new GetCustomerVolumesRequest
{
CustomerID = customerID,
PeriodID = result.Period.PeriodID,
PeriodTypeID = result.Period.PeriodTypeID
});
}
else
{
result.Volumes = new VolumeCollection();
}
return(result);
}
public static HistoricalCommission GetCustomerHistoricalCommission(int customerID, int commissionRunID)
{
List <HistoricalCommission> result = new List <HistoricalCommission>();
using (SqlConnection cnn = Exigo.Sql())
{
cnn.Open();
SqlCommand cmd = new SqlCommand("dbo.HistoricalCommission", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@customerid", customerID));
cmd.Parameters.Add(new SqlParameter("@commissionRunId", commissionRunID));
HistoricalCommission data = new HistoricalCommission();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
data.CustomerID = (int)rdr["CustomerID"];
data.CurrencyCode = (string)rdr["CurrencyCode"];
data.Total = (decimal)rdr["Total"];
data.Period.PeriodID = (int)rdr["PeriodID"];
data.Period.PeriodTypeID = (int)rdr["PeriodTypeID"];
data.Period.PeriodDescription = (string)rdr["PeriodDescription"];
data.Period.StartDate = (DateTime)rdr["StartDate"];
data.Period.EndDate = (DateTime)rdr["EndDate"];
data.CommissionRunID = (int)rdr["CommissionRunID"];
data.Earnings = (decimal)rdr["Earnings"];
data.PreviousBalance = (decimal)rdr["PreviousBalance"];
data.BalanceForward = (decimal)rdr["BalanceForward"];
data.Fee = (decimal)rdr["Fee"];
data.PaidRank.RankID = (int)rdr["PaidRankID"];
data.PaidRank.RankDescription = (string)rdr["PaidRankDescription"];
for (int i = 1; i <= 200; i++)
{
PropertyInfo Volume = data.Volumes.GetType().GetProperty("Volume" + i);
object p = (object)data.Volumes;
Volume.SetValue(p, rdr["Volume" + i]);
}
data.Volumes.CustomerID = (int)rdr["CustomerID"];
data.Volumes.EndDate = (DateTime)rdr["EndDate"];
data.Volumes.StartDate = (DateTime)rdr["StartDate"];
data.Volumes.PeriodTypeID = (int)rdr["PeriodTypeID"];
data.Volumes.PeriodID = (int)rdr["PeriodID"];
data.Volumes.PeriodDescription = (string)rdr["PeriodDescription"];
data.Volumes.PaidRankID = (int)rdr["PaidRankID"];
data.Volumes.PaidRankDescription = (string)rdr["PaidRankDescription"];
}
}
//return result;
//// Get the commission record
//var commission = Exigo.OData().Commissions.Expand("CommissionRun/Period").Expand("PaidRank")
// .Where(c => c.CustomerID == customerID)
// .Where(c => c.CommissionRunID == commissionRunID)
// .FirstOrDefault();
//if (commission == null) return null;
//var result = (HistoricalCommission)commission;
bool isPeriodMonthly = data.Period.PeriodTypeID == PeriodTypes.Monthly;
GetCustomerVolumesRequest getCustomerVolumesRequest = new GetCustomerVolumesRequest
{
CustomerID = customerID,
PeriodID = data.Period.PeriodID,
PeriodTypeID = PeriodTypes.Monthly,
VolumeIDs = new int[] { 3, 9, 10, 12, 13, 14 }
};
if (!isPeriodMonthly)
{
// Override the result volumes to ensure the numbers are always monthly
getCustomerVolumesRequest.PeriodID = null;
getCustomerVolumesRequest.ReportForMonth = data.Period.EndDate;
}
data.Volumes = GetCustomerVolumes(getCustomerVolumesRequest);
return(data);
}
}
public static IEnumerable <ICommission> GetHistoricalCommissionList(int customerID)
{
List <HistoricalCommission> result = new List <HistoricalCommission>();
using (SqlConnection cnn = Exigo.Sql())
{
cnn.Open();
SqlCommand cmd = new SqlCommand("dbo.HistoricalCommission", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@customerid", customerID));
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
HistoricalCommission data = new HistoricalCommission();
data.CustomerID = (int)rdr["CustomerID"];
data.CurrencyCode = (string)rdr["CurrencyCode"];
data.Total = (decimal)rdr["Total"];
data.Period.PeriodID = (int)rdr["PeriodID"];
data.Period.PeriodTypeID = (int)rdr["PeriodTypeID"];
data.Period.PeriodDescription = (string)rdr["PeriodDescription"];
data.Period.StartDate = (DateTime)rdr["StartDate"];
data.Period.EndDate = (DateTime)rdr["EndDate"];
data.CommissionRunID = (int)rdr["CommissionRunID"];
data.Earnings = (decimal)rdr["Earnings"];
data.PreviousBalance = (decimal)rdr["PreviousBalance"];
data.BalanceForward = (decimal)rdr["BalanceForward"];
data.Fee = (decimal)rdr["Fee"];
data.PaidRank.RankID = (int)rdr["PaidRankID"];
data.PaidRank.RankDescription = (string)rdr["PaidRankDescription"];
for (int i = 1; i <= 200; i++)
{
PropertyInfo Volume = data.Volumes.GetType().GetProperty("Volume" + i);
object p = (object)data.Volumes;
Volume.SetValue(p, rdr["Volume" + i]);
}
data.Volumes.CustomerID = (int)rdr["CustomerID"];
data.Volumes.EndDate = (DateTime)rdr["EndDate"];
data.Volumes.StartDate = (DateTime)rdr["StartDate"];
data.Volumes.PeriodTypeID = (int)rdr["PeriodTypeID"];
data.Volumes.PeriodID = (int)rdr["PeriodID"];
data.Volumes.PeriodDescription = (string)rdr["PeriodDescription"];
data.Volumes.PaidRankID = (int)rdr["PaidRankID"];
data.Volumes.PaidRankDescription = (string)rdr["PaidRankDescription"];
result.Add(data);
}
}
return(result);
}
//// Historical Commissions
//var commissions = Exigo.OData().Commissions.Expand("CommissionRun/Period").Expand("PaidRank")
// .Where(c => c.CustomerID == customerID)
// .OrderByDescending(c => c.CommissionRunID);
//if (commissions != null)
//{
// foreach (var commission in commissions)
// {
// yield return (HistoricalCommission)commission;
// }
//}
}
public static IEnumerable <ICommission> GetCommissionPeriodList(int customerID)
{
List <HistoricalCommission> result = new List <HistoricalCommission>();
using (SqlConnection cnn = Exigo.Sql())
{
cnn.Open();
SqlCommand cmd = new SqlCommand("dbo.HistoricalCommission", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@customerid", customerID));
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
HistoricalCommission data = new HistoricalCommission();
data.CustomerID = (int)rdr["CustomerID"];
data.CurrencyCode = (string)rdr["CurrencyCode"];
data.Total = (decimal)rdr["Total"];
data.Period.PeriodID = (int)rdr["PeriodID"];
data.Period.PeriodTypeID = (int)rdr["PeriodTypeID"];
data.Period.PeriodDescription = (string)rdr["PeriodDescription"];
data.Period.StartDate = (DateTime)rdr["StartDate"];
data.Period.EndDate = (DateTime)rdr["EndDate"];
data.CommissionRunID = (int)rdr["CommissionRunID"];
data.Earnings = (decimal)rdr["Earnings"];
data.PreviousBalance = (decimal)rdr["PreviousBalance"];
data.BalanceForward = (decimal)rdr["BalanceForward"];
data.Fee = (decimal)rdr["Fee"];
data.PaidRank.RankID = (int)rdr["PaidRankID"];
data.PaidRank.RankDescription = (string)rdr["PaidRankDescription"];
for (int i = 1; i <= 200; i++)
{
PropertyInfo Volume = data.Volumes.GetType().GetProperty("Volume" + i);
object p = (object)data.Volumes;
Volume.SetValue(p, rdr["Volume" + i]);
}
data.Volumes.CustomerID = (int)rdr["CustomerID"];
data.Volumes.EndDate = (DateTime)rdr["EndDate"];
data.Volumes.StartDate = (DateTime)rdr["StartDate"];
data.Volumes.PeriodTypeID = (int)rdr["PeriodTypeID"];
data.Volumes.PeriodID = (int)rdr["PeriodID"];
data.Volumes.PeriodDescription = (string)rdr["PeriodDescription"];
data.Volumes.PaidRankID = (int)rdr["PaidRankID"];
data.Volumes.PaidRankDescription = (string)rdr["PaidRankDescription"];
result.Add(data);
}
}
return(result);
}
//// Historical Commissions
//var commissions = Exigo.OData().Commissions.Expand("CommissionRun/Period")
// .Where(c => c.CustomerID == customerID)
// .OrderByDescending(c => c.CommissionRunID);
//var maxRankID = commissions.ToList().Max(i => i.PaidRankID);
//TODO: following procedure is working fine except PaidAsRankID need to get
//List<Common.Api.ExigoOData.Commission> lstCommision;
//using (var sqlcontext = Exigo.Sql())
//{
// sqlcontext.Open();
// string sqlProcedure = string.Format(@"GetCommissionPeriodList {0}",customerID);
// lstCommision = sqlcontext.Query<Common.Api.ExigoOData.Commission, Common.Api.ExigoOData.CommissionRun, Common.Api.ExigoOData.Period, Common.Api.ExigoOData.Commission>(sqlProcedure, (commission, commissionRun,period) =>
// {
// commission.CommissionRun = commissionRun;
// commission.CommissionRun.Period = period;
// return commission;
// }, splitOn: "CommissionRunID,PeriodID").ToList();
// sqlcontext.Close();
//}
//if (commissions != null)
//{
// foreach (var commission in commissions)
// {
// yield return (HistoricalCommission)commission;
// }
//}
}