public int Insert(Broker rec) { int affected = 0; string query = @" INSERT INTO Broker (ID, BrokerTypeID, OfficeID, SupervisorBrokerID, Name, EntranceDate, LeaveDate, IsActive, Email, AECommissionCode, SUPCommissionCode, ManCommissionCode, UpdateBy, UpdateTime, SupporterId) VALUES (@ID, @BrokerTypeID, @OfficeID, @SupervisorBrokerID, @Name, @EntranceDate, @LeaveDate, @IsActive, @Email, @AECommissionCode, @SUPCommissionCode, @ManCommissionCode, @UpdateBy, getdate(), @SupporterId)"; ((SqlCommand)DataAccess.Command).Parameters.Clear(); ((SqlCommand)DataAccess.Command).Parameters.Add("@ID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@BrokerTypeID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@OfficeID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@SupervisorBrokerID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@Name", SqlDbType.NVarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@EntranceDate", SqlDbType.DateTime); ((SqlCommand)DataAccess.Command).Parameters.Add("@LeaveDate", SqlDbType.DateTime); ((SqlCommand)DataAccess.Command).Parameters.Add("@IsActive", SqlDbType.Bit); ((SqlCommand)DataAccess.Command).Parameters.Add("@Email", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@AECommissionCode", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@SUPCommissionCode", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@ManCommissionCode", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@UpdateBy", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@SupporterId", SqlDbType.VarChar); // Parameters' values ((SqlCommand)DataAccess.Command).Parameters["@ID"].Value = rec.Id; ((SqlCommand)DataAccess.Command).Parameters["@BrokerTypeID"].Value = rec.BrokerTypeId; ((SqlCommand)DataAccess.Command).Parameters["@OfficeID"].Value = rec.Office.Id; if (!Utils.IsNullOrEmpty(rec.SuppervisorId)) { ((SqlCommand)DataAccess.Command).Parameters["@SupervisorBrokerID"].Value = rec.SuppervisorId; } else { ((SqlCommand)DataAccess.Command).Parameters["@SupervisorBrokerID"].Value = DBNull.Value; } if (!Utils.IsNullOrEmpty(rec.SupporterId)) { ((SqlCommand)DataAccess.Command).Parameters["@SupporterId"].Value = rec.SupporterId; } else { ((SqlCommand)DataAccess.Command).Parameters["@SupporterId"].Value = DBNull.Value; } if (rec.Name == null) { ((SqlCommand)DataAccess.Command).Parameters["@Name"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@Name"].Value = rec.Name; } if (rec.EntranceDate > DateTime.MinValue) { ((SqlCommand)DataAccess.Command).Parameters["@EntranceDate"].Value = rec.EntranceDate; } else { ((SqlCommand)DataAccess.Command).Parameters["@EntranceDate"].Value = DBNull.Value; } if (rec.LeaveDate > DateTime.MinValue) { ((SqlCommand)DataAccess.Command).Parameters["@LeaveDate"].Value = rec.LeaveDate; } else { ((SqlCommand)DataAccess.Command).Parameters["@LeaveDate"].Value = DBNull.Value; } ((SqlCommand)DataAccess.Command).Parameters["@IsActive"].Value = rec.IsActive; if (rec.Email == null) { ((SqlCommand)DataAccess.Command).Parameters["@Email"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@Email"].Value = rec.Email; } if (rec.AECommissionCode == null) { ((SqlCommand)DataAccess.Command).Parameters["@AECommissionCode"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@AECommissionCode"].Value = rec.AECommissionCode; } if (rec.SUPCommissionCode == null) { ((SqlCommand)DataAccess.Command).Parameters["@SUPCommissionCode"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@SUPCommissionCode"].Value = rec.SUPCommissionCode; } if (rec.MANCommissionCode == null) { ((SqlCommand)DataAccess.Command).Parameters["@MANCommissionCode"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@MANCommissionCode"].Value = rec.MANCommissionCode; } if (rec.UpdateBy == null) { ((SqlCommand)DataAccess.Command).Parameters["@UpdateBy"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@UpdateBy"].Value = rec.UpdateBy; } affected = DataAccess.ExecuteNonQuery(query); return affected; }
public int Update(Broker rec) { int affected = 0; string query = @" UPDATE Broker SET BrokerTypeID = @BrokerTypeID, OfficeID = @OfficeID, SupervisorBrokerID = @SupervisorBrokerID, Name = @Name, EntranceDate = @EntranceDate, LeaveDate = @LeaveDate, IsActive = @IsActive, Email = @Email, UpdateBy = @UpdateBy, UpdateTime = getdate(), SupporterId = @SupporterId WHERE (ID = @ID)"; ((SqlCommand)DataAccess.Command).Parameters.Clear(); ((SqlCommand)DataAccess.Command).Parameters.Add("@ID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@BrokerTypeID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@OfficeID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@SupervisorBrokerID", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@Name", SqlDbType.NVarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@EntranceDate", SqlDbType.DateTime); ((SqlCommand)DataAccess.Command).Parameters.Add("@LeaveDate", SqlDbType.DateTime); ((SqlCommand)DataAccess.Command).Parameters.Add("@IsActive", SqlDbType.Bit); ((SqlCommand)DataAccess.Command).Parameters.Add("@Email", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@UpdateBy", SqlDbType.VarChar); ((SqlCommand)DataAccess.Command).Parameters.Add("@SupporterId", SqlDbType.VarChar); // Parameters' values ((SqlCommand)DataAccess.Command).Parameters["@ID"].Value = rec.Id; ((SqlCommand)DataAccess.Command).Parameters["@BrokerTypeID"].Value = rec.BrokerTypeId; ((SqlCommand)DataAccess.Command).Parameters["@OfficeID"].Value = rec.Office.Id; if (!Utils.IsNullOrEmpty(rec.SuppervisorId)) { ((SqlCommand)DataAccess.Command).Parameters["@SupervisorBrokerID"].Value = rec.SuppervisorId; } else { ((SqlCommand)DataAccess.Command).Parameters["@SupervisorBrokerID"].Value = DBNull.Value; } if (!Utils.IsNullOrEmpty(rec.SupporterId)) { ((SqlCommand)DataAccess.Command).Parameters["@SupporterId"].Value = rec.SupporterId; } else { ((SqlCommand)DataAccess.Command).Parameters["@SupporterId"].Value = DBNull.Value; } if (rec.Name == null) { ((SqlCommand)DataAccess.Command).Parameters["@Name"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@Name"].Value = rec.Name; } if (rec.EntranceDate > DateTime.MinValue) { ((SqlCommand)DataAccess.Command).Parameters["@EntranceDate"].Value = rec.EntranceDate; } else { ((SqlCommand)DataAccess.Command).Parameters["@EntranceDate"].Value = DBNull.Value; } if (rec.LeaveDate > DateTime.MinValue) { ((SqlCommand)DataAccess.Command).Parameters["@LeaveDate"].Value = rec.LeaveDate; } else { ((SqlCommand)DataAccess.Command).Parameters["@LeaveDate"].Value = DBNull.Value; } ((SqlCommand)DataAccess.Command).Parameters["@IsActive"].Value = rec.IsActive; if (rec.Email == null) { ((SqlCommand)DataAccess.Command).Parameters["@Email"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@Email"].Value = rec.Email; } if (rec.UpdateBy == null) { ((SqlCommand)DataAccess.Command).Parameters["@UpdateBy"].Value = DBNull.Value; } else { ((SqlCommand)DataAccess.Command).Parameters["@UpdateBy"].Value = rec.UpdateBy; } affected = DataAccess.ExecuteNonQuery(query); return affected; }
public Hashtable GetAllBroker() { Hashtable retData = new Hashtable(); string query = @"SELECT a.[ID], a.[BrokerTypeID], a.[OfficeID], a.[SupervisorBrokerID], b.[Name] as SupervisorName, a.[Name], a.[EntranceDate], a.[LeaveDate], a.[IsActive], a.[UpdateBy], a.[UpdateTime], a.[Email], a.[AECommissionCode],a.[SUPCommissionCode],a.[MANCommissionCode], c.AdjustRate , a.[SupporterID] FROM [Broker] a left outer join [Broker] b on a.SupervisorBrokerID = b.ID left outer join [BrokerRate] c on a.ID = c.BrokerID WHERE a.IsActive = 1"; DataSet dataSet = DataAccess.ExecuteQuery(query); if ((dataSet != null) && (dataSet.Tables.Count > 0) && (dataSet.Tables[0].Rows.Count > 0)) { for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { Broker broker = new Broker(dataSet.Tables[0].Rows[i][0].ToString()); // Broker Type broker.BrokerTypeId = dataSet.Tables[0].Rows[i][1].ToString(); // Office if (dataSet.Tables[0].Rows[i][2] != DBNull.Value) { // Dummy office to hold the OfficeID string officeID = dataSet.Tables[0].Rows[i][2].ToString(); Office office = new Office(officeID); broker.Office = office; } if (dataSet.Tables[0].Rows[i][3] != DBNull.Value) { // Dummy broker to store only Supervisor Broker ID Broker supervisor = new Broker(dataSet.Tables[0].Rows[i][3].ToString()); broker.Supervisor = supervisor; } broker.Name = dataSet.Tables[0].Rows[i][5].ToString(); broker.IsActive = true; if (dataSet.Tables[0].Rows[i][12] != DBNull.Value) { broker.AECommissionCode = dataSet.Tables[0].Rows[i][12].ToString().Trim(); } if (dataSet.Tables[0].Rows[i][13] != DBNull.Value) { broker.SUPCommissionCode = dataSet.Tables[0].Rows[i][13].ToString().Trim(); } if (dataSet.Tables[0].Rows[i][14] != DBNull.Value) { broker.MANCommissionCode = dataSet.Tables[0].Rows[i][14].ToString().Trim(); } if (dataSet.Tables[0].Rows[i][15] != DBNull.Value) { broker.AdjustRate = new BrokerRate(broker); broker.AdjustRate.AdjustRate = (double)dataSet.Tables[0].Rows[i][15]; } if (dataSet.Tables[0].Rows[i][16] != DBNull.Value) { // Dummy broker to store only Supporter Broker ID Broker supporter = new Broker(dataSet.Tables[0].Rows[i][16].ToString()); broker.Supporter = supporter; } else { broker.AdjustRate = null; } retData[broker.Id] = broker; } // Re iterate the broker list to link to the real supervisor objects (instead of dummies which only store the id) for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { string brokerID = dataSet.Tables[0].Rows[i][0].ToString(); Broker broker = (Broker)retData[brokerID]; if ((broker != null) && (broker.Supervisor != null)) { Broker supervisor = (Broker)retData[broker.Supervisor.Id]; if (supervisor != null) { //broker.Supervisor = supervisor; broker.JoinGroup(supervisor); } else { broker.Supervisor = null; } } } } return retData; }
private void CalculateSupporter(Broker brk, BrokerCommissionModal masterData) { // Calculate the total of all supported foreach (string key in brk.HashSupporting.Keys) { Broker subBrk = (Broker)brk.HashSupporting[key]; brk.PaymentResult.SubtotalNetRevenue += subBrk.PaymentResult.NetRevenue; brk.PaymentResult.SubtotalInheritedNetRevenue += subBrk.PaymentResult.InheritedNetRevenue; brk.PaymentResult.SubtotalPrivateNetRevenue += subBrk.PaymentResult.PrivateNetRevenue; brk.PaymentResult.SubtotalTradingValue += subBrk.PaymentResult.TradingValue; brk.PaymentResult.SubtotalInheritedTradingValue += subBrk.PaymentResult.InheritedTradingValue; brk.PaymentResult.SubtotalPrivateTradingValue += subBrk.PaymentResult.PrivateTradingValue; } // Find the Commission Rates // Find the Commission Rate for ClientType = 1 --> Client from HSC //CommissionRate rate4OldClient = masterData.FindCommissionRate(brk.SUPCommissionCode, // ClientType.CLIENTTYPE_ID_FROMHSC, brk.PaymentResult.SubtotalNetRevenue); // Find the Commission Rate for ClientType = 2 --> Client found by broker // CommissionRate rate4NewClient = masterData.FindCommissionRate(brk.SUPCommissionCode, // ClientType.CLIENTTYPE_ID_BYBROKER, brk.PaymentResult.SubtotalNetRevenue); //if (rate4NewClient == null) //{ // _logger.Error("CalculateSupervisor - rate4NewClient not found. BrokerID=" + brk.Id); // rate4NewClient = new CommissionRate(ClientType.CLIENTTYPE_ID_BYBROKER, Constants.BROKERTYPE_SUP); //} //if (rate4OldClient == null) //{ // _logger.Error("CalculateSupervisor - rate4OldClient not found. BrokerID=" + brk.Id); // rate4OldClient = new CommissionRate(ClientType.CLIENTTYPE_ID_FROMHSC, Constants.BROKERTYPE_SUP); //} Decimal a = (brk.PaymentResult.SubtotalInheritedNetRevenue * (decimal)0.01); // A Decimal b = (brk.PaymentResult.SubtotalPrivateNetRevenue * (decimal)0.02); // B1 or B2 depends on the matched rate brk.PaymentResult.SupervisorPayment = a + b; brk.PaymentResult.Payment += brk.PaymentResult.SupervisorPayment; //_logger.Debug("CalculateSupervisor - LEAVE"); }
private void CalculateManager(Broker brk, BrokerCommissionModal masterData) { //_logger.Debug("CalculateManager - ENTER"); if ((brk.Office == null) || (brk.Office.Id == null) || (brk.Office.Branch == null)) return; decimal revenue = 0; decimal value = 0; decimal value4FindingRate = 0; if (brk.Office.IsBranch(Office.BRANCH_ID_HN)) { revenue = brk.Office.Branch.Result.NetRevenue; value = brk.Office.Branch.Result.TradingValue; value4FindingRate = value; } else if (brk.Office.IsBranch(Office.BRANCH_ID_HCM)) { revenue = brk.Office.Result.NetRevenue; value = brk.Office.Result.TradingValue; value4FindingRate = (value - 1000000000000); // 1K Bil if (value4FindingRate < 0) value4FindingRate = 0; } CommissionRate rate4Manager = masterData.FindCommissionRate(brk.MANCommissionCode, ClientType.CLIENTTYPE_ID_FROMHSC, value4FindingRate); if (rate4Manager == null) { _logger.Error("CalculateManager - rate4OldClient not found. BrokerID=" + brk.Id); rate4Manager = new CommissionRate(ClientType.CLIENTTYPE_ID_FROMHSC, Constants.BROKERTYPE_MAN); } brk.PaymentResult.ManagementBonusRate = rate4Manager.Commissionrate; brk.PaymentResult.SupervisorPayment = (revenue * (decimal)rate4Manager.Commissionrate); if (brk.AdjustRate != null) { brk.PaymentResult.SupervisorPayment = (brk.PaymentResult.SupervisorPayment * ((decimal)brk.AdjustRate.AdjustRate)); } brk.PaymentResult.Payment += brk.PaymentResult.SupervisorPayment; //_logger.Debug("CalculateManager - LEAVE"); }
private void CalculateAE(Broker brk, string calculPeriodId, BrokerCommissionModal masterData) { // prepare data for Calculation PrepareTradingData(brk); BrokerCalculationResult calculResult = brk.PaymentResult; if (calculResult.NetRevenue < NEW_CALCULATION_LIMIT) //using OLD METHOD #region OLD METHOD { // Find the Commission Rate for OLD CLIENTS CommissionRate hscClientRate = masterData.FindCommissionRate(brk.AECommissionCode, ClientType.CLIENTTYPE_ID_FROMHSC, calculResult.NetRevenue); if (hscClientRate != null) { calculResult.InheritedCommissionRate = hscClientRate.Commissionrate; } else { calculResult.InheritedCommissionRate = 0; } // Commission payment for hsc clients calculResult.InheritedCommissionPayment = (calculResult.InheritedNetRevenue * (decimal)calculResult.InheritedCommissionRate); // Find the Commission Rate for New clients CommissionRate aeClientRate = masterData.FindCommissionRate(brk.AECommissionCode, ClientType.CLIENTTYPE_ID_BYBROKER, calculResult.NetRevenue); if (aeClientRate != null) { calculResult.PrivateCommissionRate = aeClientRate.Commissionrate; calculResult.PrivateCommissionRate1 = aeClientRate.Commissionrate; calculResult.PrivateCommissionRate2 = 0; } else { calculResult.PrivateCommissionRate = 0; calculResult.PrivateCommissionRate1 = 0; calculResult.PrivateCommissionRate2 = 0; } // Commission payment for hsc clients calculResult.PrivateCommissionPayment = (calculResult.PrivateNetRevenue * (decimal)calculResult.PrivateCommissionRate); calculResult.OtherPayment = 0; if (brk.IsAccountExecutive()) { AllowanceDTO allowanceRate = masterData.GetAllowance(brk.AECommissionCode, calculResult.NetRevenue); if (allowanceRate != null) { calculResult.OtherPayment = allowanceRate.Allowance; } } // Total payment calculResult.Payment += calculResult.InheritedCommissionPayment + calculResult.PrivateCommissionPayment + calculResult.OtherPayment; } #endregion else //using NEW METHOD #region NEW METHOD { // Find the Commission Rate for OLD CLIENTS CommissionRate1 hscClientRate = masterData.FindCommissionRate1(brk.AECommissionCode, ClientType.CLIENTTYPE_ID_FROMHSC, calculResult.NetRevenue); if (hscClientRate != null) { calculResult.InheritedCommissionRate = hscClientRate.LowerRate; } else { calculResult.InheritedCommissionRate = 0; } // Commission payment for hsc clients calculResult.InheritedCommissionPayment = (calculResult.InheritedNetRevenue * (decimal)calculResult.InheritedCommissionRate); // Find the Commission Rate for New clients CommissionRate1 aeClientRate = masterData.FindCommissionRate1(brk.AECommissionCode, ClientType.CLIENTTYPE_ID_BYBROKER, calculResult.NetRevenue); calculResult.PrivateCommissionPayment = 0; if (aeClientRate != null) { calculResult.PrivateCommissionRate = aeClientRate.LowerRate; calculResult.PrivateCommissionRate1 = aeClientRate.LowerRate; calculResult.PrivateCommissionRate2 = aeClientRate.UpperRate; if (calculResult.PrivateNetRevenue < aeClientRate.Limit) { calculResult.PrivateCommissionPayment = (decimal)aeClientRate.LowerRate * calculResult.PrivateNetRevenue; } else { calculResult.PrivateCommissionPayment = (decimal)aeClientRate.LowerRate * aeClientRate.Limit + (calculResult.PrivateNetRevenue - aeClientRate.Limit) * (decimal)aeClientRate.UpperRate; } } else { calculResult.PrivateCommissionRate = 0; calculResult.PrivateCommissionRate1 = 0; calculResult.PrivateCommissionRate2 = 0; } calculResult.OtherPayment = 0; if (brk.IsAccountExecutive()) { AllowanceDTO allowanceRate = masterData.GetAllowance(brk.AECommissionCode, calculResult.NetRevenue); if (allowanceRate != null) { calculResult.OtherPayment = allowanceRate.Allowance; } } // Total payment calculResult.Payment += calculResult.InheritedCommissionPayment + calculResult.PrivateCommissionPayment + calculResult.OtherPayment; } #endregion }
/// <summary> /// Prepare data for calculating commission /// </summary> /// <param name="brk"></param> private void PrepareTradingData(Broker brk) { BrokerCalculationResult result = brk.PaymentResult; // Collect Revenue data for (int i = 0; i < brk.ClientTradeData.Count; i++) { ClientTradeData clientTradeData = brk.ClientTradeData[i]; if (clientTradeData != null) { // Trading Value result.TradingValue += clientTradeData.TradingValue; // Gross Revenue result.GrossRevenue += clientTradeData.Commission; // HSC Clients or old clients if (clientTradeData.ClientTypeId.Equals(ClientType.CLIENTTYPE_ID_FROMHSC)) { // Inherited Trading Value result.InheritedTradingValue += clientTradeData.TradingValue; // Inherited Gross Revenue result.InheritedGrossRevenue += clientTradeData.Commission; // Inherited Net Revenue result.InheritedNetRevenue += (clientTradeData.Commission - (clientTradeData.TradingValue * (decimal)MARKET_FEE_RATIO)); if (result.InheritedNetRevenue < 0) result.InheritedNetRevenue = 0; } // New Clients or client from AE/BR if (clientTradeData.ClientTypeId.Equals(ClientType.CLIENTTYPE_ID_BYBROKER)) { // Private Trading Value result.PrivateTradingValue += clientTradeData.TradingValue; // Private Gross Revenue result.PrivateGrossRevenue += clientTradeData.Commission; // Private Net Revenue result.PrivateNetRevenue += (clientTradeData.Commission - (clientTradeData.TradingValue * (decimal)MARKET_FEE_RATIO)); if (result.PrivateNetRevenue < 0) result.PrivateNetRevenue = 0; } } } result.NetRevenue = result.InheritedNetRevenue + result.PrivateNetRevenue; }
public BrokerRate(Broker broker) { this.Broker = broker; }
public void JoinSupporterGroup(Broker groupSupporter) { if (groupSupporter != null) { this.Supporter = groupSupporter; groupSupporter.HashSupporting[this.Id] = this; } }
public void JoinGroup(Broker groupSupervisor) { if (groupSupervisor != null) { this.Supervisor = groupSupervisor; groupSupervisor.HashSubordinate[this.Id] = this; } }