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