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