public static VolumeCollection GetCustomerVolumes(GetCustomerVolumesRequest request)
{
//2015-08-04
//Ivan Sanchez
//60
//Added the changes Travis and I did for PHP to avoid the dashboard error
var requestedSpecificVolumes = (request.VolumeIDs != null && request.VolumeIDs.Length > 0);
var baseQuery = Exigo.OData().PeriodVolumes.Expand("Period,Rank,PaidRank");
var query = baseQuery
.Where(c => c.CustomerID == request.CustomerID)
.Where(c => c.PeriodTypeID == request.PeriodTypeID);
// Determine which period ID to use
if (request.PeriodID != null)
{
query = query.Where(c => c.PeriodID == (int)request.PeriodID);
}
else
{
query = query.Where(c => c.Period.IsCurrentPeriod);
}
var data = query.FirstOrDefault();
var result = (VolumeCollection)data;
return(result);
}
public static VolumeCollection GetCustomerVolumes(GetCustomerVolumesRequest request)
{
var requestedSpecificVolumes = (request.VolumeIDs != null && request.VolumeIDs.Length > 0);
var baseQuery = Exigo.OData().PeriodVolumes;
var query = baseQuery
.Where(c => c.CustomerID == request.CustomerID)
.Where(c => c.PeriodTypeID == request.PeriodTypeID);
// Determine which period ID to use
if (request.PeriodID != null)
{
query = query.Where(c => c.PeriodID == (int)request.PeriodID);
}
else
{
query = query.Where(c => c.Period.IsCurrentPeriod);
}
PeriodVolume data;
if (!requestedSpecificVolumes)
{
data = query.Select(c => new Common.Api.ExigoOData.PeriodVolume()
{
CustomerID = c.CustomerID,
ModifiedDate = c.ModifiedDate,
PaidRankID = c.PaidRankID,
PaidRank = c.PaidRank,
RankID = c.RankID,
Rank = c.Rank,
PeriodID = c.PeriodID,
Period = c.Period,
PeriodTypeID = c.PeriodTypeID
}).FirstOrDefault();
}
else
{
var volumes = new List <string>();
foreach (var id in request.VolumeIDs)
{
volumes.Add("Volume" + id);
}
var select = string.Format("new({0},Period,Rank,PaidRank)", string.Join(",", volumes));
var finalQuery = query.Select(select);
var url = finalQuery.ToString();
data = Exigo.OData().Execute <Common.Api.ExigoOData.PeriodVolume>(new Uri(url)).FirstOrDefault();
}
return((VolumeCollection)data);
}
//This is currently being used for the Dashboard Widget
//public static VolumeCollection GetCustomerVolumes2(GetCustomerVolumesRequest request)
//{
// // Establish the query
// var query = Exigo.OData().PeriodVolumes
// .Where(c => c.CustomerID == request.CustomerID)
// .Where(c => c.PeriodTypeID == PeriodTypes.Monthly)
// .Where(c => c.Period.IsCurrentPeriod);
// //.Where(c => c.Period.StartDate <= DateTime.Now);
// // Fetch the data
// var data = query.Select(c => new PeriodVolume()
// {
// CustomerID = c.CustomerID,
// PeriodID = c.PeriodID,
// Period = c.Period,
// PeriodTypeID = c.PeriodTypeID,
// Volume1 = c.Volume1,
// Volume2 = c.Volume2,
// Volume3 = c.Volume3,
// Volume9 = c.Volume9,
// Volume10 = c.Volume10,
// Volume12 = c.Volume12,
// Volume13 = c.Volume13,
// Volume14 = c.Volume14
// }).FirstOrDefault();
// return (VolumeCollection)data;
//}
//This uses an api call rather then OData
public static VolumeCollection GetCustomerVolumes3(GetCustomerVolumesRequest request)
{
var vc = new VolumeCollection();
//var api = Exigo.WebService();
////Create Request
//var volumeRequest = new GetVolumesRequest
//{
// CustomerID = request.CustomerID,
// PeriodType = request.PeriodTypeID
//};
////Get Response
//var response = api.GetVolumes(volumeRequest).Volumes.FirstOrDefault();
//// here api call to SQl Server call
VolumeResponse response = new VolumeResponse();
using (var context = Exigo.Sql())
{
string sqlQuery = string.Format(@"GetCustomerVolumes3 {0},{1}", request.CustomerID, request.PeriodTypeID);
response = context.Query <VolumeResponse>(sqlQuery).FirstOrDefault();
}
if (response != null)
{
vc.CustomerID = response.CustomerID;
vc.PayableAsRank.RankID = response.RankID;
vc.Period.PeriodDescription = response.PeriodDescription;
vc.Volume1 = response.Volume1;
vc.Volume2 = response.Volume2;
vc.Volume3 = response.Volume3;
vc.Volume9 = response.Volume9;
vc.Volume10 = response.Volume10;
vc.Volume12 = response.Volume12;
vc.Volume13 = response.Volume13;
vc.Volume14 = response.Volume14;
vc.PaidAsRank.RankID = response.PaidRankID;
}
;
return(vc);
}
public static VolumeCollection GetCustomerVolumes(GetCustomerVolumesRequest request)
{
#region OData
//var requestedSpecificVolumes = (request.VolumeIDs != null && request.VolumeIDs.Length > 0);
// var baseQuery = Exigo.OData().PeriodVolumes;
// var query = baseQuery
// .Where(c => c.CustomerID == request.CustomerID)
// .Where(c => c.PeriodTypeID == request.PeriodTypeID);
//// Allows for a request for monthly volumes without knowing the exact period id for the month
//if (request.ReportForMonth.HasValue)
//{
// DateTime reportStart = new DateTime(request.ReportForMonth.Value.Year, request.ReportForMonth.Value.Month, 1);
// query = query.Where(c => c.Period.StartDate >= reportStart && c.Period.StartDate <= reportStart.AddDays(1));
// // Validation
// if (request.PeriodID != null)
// {
// throw new Exception("PeriodID and ReportForMonth parameters cannot be specified at the same time.");
// }
// if (request.PeriodTypeID != PeriodTypes.Monthly)
// {
// throw new Exception("PeriodType must be Monthly when ReportForMonth parameter is specified.");
// }
//}
//// Determine which period ID to use
//else if (request.PeriodID != null)
//{
// query = query.Where(c => c.PeriodID == (int)request.PeriodID);
//}
//else
//{
// query = query.Where(c => c.Period.IsCurrentPeriod);
//}
//PeriodVolume data;
//if (!requestedSpecificVolumes)
//{
// data = query.Select(c => new Common.Api.ExigoOData.PeriodVolume()
// {
// CustomerID = c.CustomerID,
// ModifiedDate = c.ModifiedDate,
// PaidRankID = c.PaidRankID,
// PaidRank = c.PaidRank,
// RankID = c.RankID,
// Rank = c.Rank,
// PeriodID = c.PeriodID,
// Period = c.Period,
// PeriodTypeID = c.PeriodTypeID
// }).FirstOrDefault();
//}
//else
//{
// var volumes = new List<string>();
// foreach (var id in request.VolumeIDs)
// {
// volumes.Add("Volume" + id);
// }
// var select = string.Format("new({0},Period,Rank,PaidRank)", string.Join(",", volumes));
// var finalQuery = query;
// var url = finalQuery.ToString();
// data = Exigo.OData().Execute<Common.Api.ExigoOData.PeriodVolume>(new Uri(url)).FirstOrDefault();
//}
//return (VolumeCollection)data;
#endregion
#region SQL Procedure Call
bool IsCurrentPeriod = false;
if (request.ReportForMonth.HasValue)
{
// Validation
if (request.PeriodID != null)
{
throw new Exception("PeriodID and ReportForMonth parameters cannot be specified at the same time.");
}
if (request.PeriodTypeID != PeriodTypes.Monthly)
{
throw new Exception("PeriodType must be Monthly when ReportForMonth parameter is specified.");
}
}
if (!request.ReportForMonth.HasValue && request.PeriodID == null)
{
IsCurrentPeriod = true;
}
var finalResult = new VolumeCollection();
using (var Context = Exigo.Sql())
{
string sqlProcedure = string.Format(@"GetCustomerPeriodVolumes {0},{1},{2},'{3}',{4}", request.CustomerID, request.PeriodTypeID, request.PeriodID != null ? request.PeriodID : 0, request.ReportForMonth.HasValue ? new DateTime(request.ReportForMonth.Value.Year, request.ReportForMonth.Value.Month, 1).ToString() : new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString(), IsCurrentPeriod == false ? 0 : 1);
var result = Context.Query <VolumeCollection>(sqlProcedure).ToList();
//Selecting Final result
if (result == null)
{
finalResult.HighestAchievedRankThisPeriod.RankDescription = string.Empty;
return(finalResult);
}
finalResult = result.Select(s => new VolumeCollection()
{
Rank = s.Rank,
HighestAchievedRankThisPeriod = s.Rank,
PaidAsRank = s.PaidAsRank1,
PayableAsRank = s.PaidAsRank1,
Period = s.Period1,
CustomerID = s.CustomerID,
RankID = s.RankID,
RankDescription = s.RankDescription,
PaidRankID = s.PaidRankID,
PaidRankDescription = s.PaidRankDescription,
ModifiedDate = s.ModifiedDate,
PeriodID = s.PeriodID,
Volume1 = s.Volume1,
Volume2 = s.Volume2,
Volume3 = s.Volume3,
Volume9 = s.Volume9,
Volume10 = s.Volume10,
Volume12 = s.Volume12,
Volume13 = s.Volume13,
Volume14 = s.Volume14,
Volume16 = s.Volume16,
Volume17 = s.Volume17,
Volume18 = s.Volume18,
Volume22 = s.Volume22,
PeriodTypeID = s.PeriodTypeID,
PeriodDescription = s.PeriodDescription,
StartDate = s.StartDate,
EndDate = s.EndDate,
}).FirstOrDefault();
}
if (finalResult == null)
{
finalResult = new VolumeCollection();
finalResult.HighestAchievedRankThisPeriod.RankID = 0;
finalResult.HighestAchievedRankThisPeriod.RankDescription = string.Empty;
return(finalResult);
}
return(finalResult);
#endregion
}
public static IEnumerable <RealTimeCommission> GetCustomerRealTimeCommissions(GetCustomerRealTimeCommissionsRequest request)
{
var results = new List <RealTimeCommission>();
// Get the commission record
var realtimeresponse = Exigo.WebService().GetRealTimeCommissions(new Common.Api.ExigoWebService.GetRealTimeCommissionsRequest
{
CustomerID = request.CustomerID
});
if (realtimeresponse.Commissions.Length == 0)
{
return(results);
}
// Get the unique periods for each of the commission results
var periods = new List <Period>();
var periodRequests = new List <GetPeriodsRequest>();
foreach (var commissionResponse in realtimeresponse.Commissions)
{
var periodID = commissionResponse.PeriodID;
var periodTypeID = commissionResponse.PeriodType;
var req = periodRequests.Where(c => c.PeriodTypeID == periodTypeID).FirstOrDefault();
if (req == null)
{
periodRequests.Add(new GetPeriodsRequest()
{
PeriodTypeID = periodTypeID,
PeriodIDs = new int[] { periodID }
});
}
else
{
var ids = req.PeriodIDs.ToList();
ids.Add(periodID);
req.PeriodIDs = ids.Distinct().ToArray();
}
}
periodRequests.ForEach(s => GetPeriods(s).ToList().ForEach(a => periods.Add(a)));
//replaced the below loops with above line.
//foreach (var req in periodRequests)
//{
// var responses = GetPeriods(req);
// foreach (var response in responses)
// {
// periods.Add(response);
// }
//}
// Process each commission response
try
{
foreach (var commission in realtimeresponse.Commissions)
{
var typedCommission = (RealTimeCommission)commission;
typedCommission.Period = periods
.Where(c => c.PeriodID == commission.PeriodID && c.PeriodTypeID == commission.PeriodType)
.FirstOrDefault();
//typedCommission.Period = periods
// .Where(c => c.PeriodTypeID == commission.PeriodType)
// .Where(c => c.PeriodID == commission.PeriodID)
// .FirstOrDefault();
bool isPeriodMonthly = typedCommission.Period.PeriodTypeID == PeriodTypes.Monthly;
GetCustomerVolumesRequest getCustomerVolumesRequest = new GetCustomerVolumesRequest
{
CustomerID = request.CustomerID,
PeriodID = typedCommission.Period.PeriodID,
PeriodTypeID = PeriodTypes.Monthly,
VolumeIDs = request.VolumeIDs
};
if (!isPeriodMonthly)
{
getCustomerVolumesRequest.PeriodID = null;
getCustomerVolumesRequest.ReportForMonth = typedCommission.Period.EndDate;// period.EndDate;
}
typedCommission.Volumes = GetCustomerVolumes(getCustomerVolumesRequest);
typedCommission.PaidRank = typedCommission.Volumes.PayableAsRank;
results.Add(typedCommission);
}
return(results.OrderByDescending(c => c.Period.StartDate));
}
catch { return(results); }
}
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 VolumeCollection GetCustomerVolumes(GetCustomerVolumesRequest request)
{
var periodID = request.PeriodID;
var periodTypeID = request.PeriodTypeID;
if (periodID == null)
{
periodID = ExigoDAL.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 = ExigoDAL.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 VolumeCollection GetCustomerVolumes(GetCustomerVolumesRequest request)
{
var requestedSpecificVolumes = (request.VolumeIDs != null && request.VolumeIDs.Length > 0);
var baseQuery = Exigo.OData().PeriodVolumes.Expand("Period,Rank,PaidRank");
var query = baseQuery
.Where(c => c.CustomerID == request.CustomerID)
.Where(c => c.PeriodTypeID == request.PeriodTypeID);
// Determine which period ID to use
if (request.PeriodID != null)
{
query = query.Where(c => c.PeriodID == (int)request.PeriodID);
}
else
{
query = query.Where(c => c.Period.IsCurrentPeriod);
}
var data = query.FirstOrDefault();
//2015-07-25
//Ivan S
//#68437
//I worked with Travis and I implemented a code he modified to fix the dashboard error changing
//the way the dashboards are loading the volume data
var result = (VolumeCollection)data;
return result;
//var requestedSpecificVolumes = (request.VolumeIDs != null && request.VolumeIDs.Length > 0);
//var baseQuery = Exigo.OData().PeriodVolumes;
//var query = baseQuery
// .Where(c => c.CustomerID == request.CustomerID)
// .Where(c => c.PeriodTypeID == request.PeriodTypeID);
//// Determine which period ID to use
//if (request.PeriodID != null)
//{
// query = query.Where(c => c.PeriodID == (int)request.PeriodID);
//}
//else
//{
// query = query.Where(c => c.Period.IsCurrentPeriod);
//}
//PeriodVolume data;
//if (!requestedSpecificVolumes)
//{
// data = query.Select(c => new Common.Api.ExigoOData.PeriodVolume()
// {
// CustomerID = c.CustomerID,
// ModifiedDate = c.ModifiedDate,
// PaidRankID = c.PaidRankID,
// PaidRank = c.PaidRank,
// RankID = c.RankID,
// Rank = c.Rank,
// PeriodID = c.PeriodID,
// Period = c.Period,
// PeriodTypeID = c.PeriodTypeID
// }).FirstOrDefault();
//}
//else
//{
// var volumes = new List<string>();
// foreach (var id in request.VolumeIDs)
// {
// volumes.Add("Volume" + id);
// }
// var select = string.Format("new({0},Period,Rank,PaidRank)", string.Join(",", volumes));
// var finalQuery = query.Select(select);
// var url = finalQuery.ToString();
// data = Exigo.OData().Execute<Common.Api.ExigoOData.PeriodVolume>(new Uri(url)).FirstOrDefault();
//}
//return (VolumeCollection)data;
}
