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