Пример #1
0
        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);
        }
Пример #2
0
        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);
            }
        }
Пример #3
0
        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;
            //    }
            //}
        }
Пример #4
0
        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;
            //    }
            //}
        }