public int UpdateAllowance(BrokerCalculationResult rec)
{
int affected = 0;
string query = @"
UPDATE BrokerCalculationResult
SET OtherPayment = @OtherPayment
WHERE BrokerID = @BrokerID ";
((SqlCommand)DataAccess.Command).Parameters.Add("@BrokerID", SqlDbType.VarChar);
((SqlCommand)DataAccess.Command).Parameters.Add("@OtherPayment", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters["@BrokerID"].Value = rec.BrokerId;
((SqlCommand)DataAccess.Command).Parameters["@OtherPayment"].Value = rec.OtherPayment;
affected = DataAccess.ExecuteNonQuery(query);
return affected;
}
public void Calculate(string calculPeriodId, DateTime calculPeriod, UserDTO byUser)
{
_logger.Info("Calculate - ENTER");
// Load all master data();
BrokerCommissionModal brokerDataModal = new BrokerCommissionModal();
brokerDataModal.LoadData();
//Get average 3 months commision
//Hashtable hash3MonthAvgResult = GetAvgNetRevenue(calculPeriod);
//_logger.Info("3 Month AvgNetRevenue data load completed. Count=" + hash3MonthAvgResult.Count);
List<ClientTradeData> listClientTradeData = ClientTradeDataAccess.GetAllClientTradeData(calculPeriodId);
_logger.Info("ClientTradeData data load completed. Count=" + listClientTradeData.Count);
// Link client trade data and their brokers together
for (int i = 0; i < listClientTradeData.Count; i++)
{
ClientTradeData clientTradeData = listClientTradeData[i];
if (clientTradeData.BrokerId != null)
{
if (brokerDataModal.HashBroker[clientTradeData.BrokerId] != null)
{
Broker brk = (Broker)brokerDataModal.HashBroker[clientTradeData.BrokerId];
brk.ClientTradeData.Add(clientTradeData);
}
}
}
foreach (string brkID in brokerDataModal.HashBroker.Keys)
{
Broker broker = (Broker)brokerDataModal.HashBroker[brkID];
// Create the ResultData object to hold calculation data for the broker (brk)
BrokerCalculationResult result = new BrokerCalculationResult();
result.BrokerId = broker.Id;
result.PeriodId = calculPeriodId;
result.CalculatePeriod = calculPeriod;
broker.PaymentResult = result;
//// check the last 3 month commmision average to identify if this broker have allowance or not
//if (hash3MonthAvgResult[broker.Id] != null)
//{
// result.AverageNetRevenue = (decimal)hash3MonthAvgResult[broker.Id]; ;
//}
//else
//{
// // Mark this to know that there is NO DATA about Average Net Revenue
// result.AverageNetRevenue = -1;
//}
CalculateAE(broker, calculPeriodId, brokerDataModal);
}
//Supervisor
foreach (string brkID in brokerDataModal.HashBroker.Keys)
{
Broker broker = (Broker)brokerDataModal.HashBroker[brkID];
if (broker.IsSupervisor())
{
CalculateSupervisor(broker, brokerDataModal);
}
}
//Supporter
foreach (string brkID in brokerDataModal.HashBroker.Keys)
{
Broker broker = (Broker)brokerDataModal.HashBroker[brkID];
if (broker.IsSupporter())
{
CalculateSupporter(broker, brokerDataModal);
}
}
AccumulateOfficeRevenue(brokerDataModal, calculPeriodId, calculPeriod);
foreach (string brkID in brokerDataModal.HashBroker.Keys)
{
Broker brk = (Broker)brokerDataModal.HashBroker[brkID];
if (brk.IsManager())
{
CalculateManager(brk,brokerDataModal);
}
}
StoreCalculationData(brokerDataModal,calculPeriodId);
_logger.Info("Calculate - LEAVE");
}
public int InsertBrokerCalculationResult(BrokerCalculationResult rec)
{
int affected = 0;
string query = @"
INSERT INTO BrokerCalculationResult
(PeriodId,
BrokerID,
CalculatePeriod,
Payment,
TradingValue,
InheritedTradingValue,
PrivateTradingValue,
GrossRevenue,
InheritedGrossRevenue,
PrivateGrossRevenue,
NetRevenue,
InheritedNetRevenue,
PrivateNetRevenue,
AverageNetRevenue,
InheritedCommissionRate,
InheritedCommissionPayment,
PrivateCommissionRate,
PrivateCommissionPayment,
ManagementBonusRate,
SupervisorPayment,
OtherPayment,
SubtotalNetRevenue,
SubtotalInheritedNetRevenue,
SubtotalPrivateNetRevenue,
SubtotalTradingValue,
SubtotalInheritedTradingValue,
SubtotalPrivateTradingValue,
PrivateCommissionRate1,
PrivateCommissionRate2)
VALUES
(@PeriodId,
@BrokerID,
@CalculatePeriod,
@Payment,
@TradingValue,
@InheritedTradingValue,
@PrivateTradingValue,
@GrossRevenue,
@InheritedGrossRevenue,
@PrivateGrossRevenue,
@NetRevenue,
@InheritedNetRevenue,
@PrivateNetRevenue,
@AverageNetRevenue,
@InheritedCommissionRate,
@InheritedCommissionPayment,
@PrivateCommissionRate,
@PrivateCommissionPayment,
@ManagementBonusRate,
@SupervisorPayment,
@OtherPayment,
@SubtotalNetRevenue,
@SubtotalInheritedNetRevenue,
@SubtotalPrivateNetRevenue,
@SubtotalTradingValue,
@SubtotalInheritedTradingValue,
@SubtotalPrivateTradingValue,
@PrivateCommissionRate1,
@PrivateCommissionRate2)";
((SqlCommand)DataAccess.Command).Parameters.Clear();
((SqlCommand)DataAccess.Command).Parameters.Add("@PeriodId", SqlDbType.Char, 6);
((SqlCommand)DataAccess.Command).Parameters.Add("@BrokerID", SqlDbType.VarChar);
((SqlCommand)DataAccess.Command).Parameters.Add("@CalculatePeriod", SqlDbType.DateTime);
((SqlCommand)DataAccess.Command).Parameters.Add("@Payment", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@TradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@InheritedTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@GrossRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@InheritedGrossRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateGrossRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@NetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@InheritedNetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateNetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@AverageNetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@InheritedCommissionRate", SqlDbType.Float);
((SqlCommand)DataAccess.Command).Parameters.Add("@InheritedCommissionPayment", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateCommissionRate", SqlDbType.Float);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateCommissionPayment", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@ManagementBonusRate", SqlDbType.Float);
((SqlCommand)DataAccess.Command).Parameters.Add("@SupervisorPayment", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@OtherPayment", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@SubtotalNetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@SubtotalInheritedNetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@SubtotalPrivateNetRevenue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@SubtotalTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@SubtotalInheritedTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@SubtotalPrivateTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateCommissionRate1", SqlDbType.Float);
((SqlCommand)DataAccess.Command).Parameters.Add("@PrivateCommissionRate2", SqlDbType.Float);
((SqlCommand)DataAccess.Command).Parameters["@PeriodId"].Value = rec.PeriodId;
((SqlCommand)DataAccess.Command).Parameters["@BrokerID"].Value = rec.BrokerId;
((SqlCommand)DataAccess.Command).Parameters["@CalculatePeriod"].Value = rec.CalculatePeriod;
((SqlCommand)DataAccess.Command).Parameters["@Payment"].Value = rec.Payment;
((SqlCommand)DataAccess.Command).Parameters["@TradingValue"].Value = rec.TradingValue;
((SqlCommand)DataAccess.Command).Parameters["@InheritedTradingValue"].Value = rec.InheritedTradingValue;
((SqlCommand)DataAccess.Command).Parameters["@PrivateTradingValue"].Value = rec.PrivateTradingValue;
((SqlCommand)DataAccess.Command).Parameters["@GrossRevenue"].Value = rec.GrossRevenue;
((SqlCommand)DataAccess.Command).Parameters["@InheritedGrossRevenue"].Value = rec.InheritedGrossRevenue;
((SqlCommand)DataAccess.Command).Parameters["@PrivateGrossRevenue"].Value = rec.PrivateGrossRevenue;
((SqlCommand)DataAccess.Command).Parameters["@NetRevenue"].Value = rec.NetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@InheritedNetRevenue"].Value = rec.InheritedNetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@PrivateNetRevenue"].Value = rec.PrivateNetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@AverageNetRevenue"].Value = rec.AverageNetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@InheritedCommissionRate"].Value = rec.InheritedCommissionRate;
((SqlCommand)DataAccess.Command).Parameters["@InheritedCommissionPayment"].Value = rec.InheritedCommissionPayment;
((SqlCommand)DataAccess.Command).Parameters["@PrivateCommissionRate"].Value = rec.PrivateCommissionRate;
((SqlCommand)DataAccess.Command).Parameters["@PrivateCommissionPayment"].Value = rec.PrivateCommissionPayment;
((SqlCommand)DataAccess.Command).Parameters["@ManagementBonusRate"].Value = rec.ManagementBonusRate;
((SqlCommand)DataAccess.Command).Parameters["@SupervisorPayment"].Value = rec.SupervisorPayment;
((SqlCommand)DataAccess.Command).Parameters["@OtherPayment"].Value = rec.OtherPayment;
((SqlCommand)DataAccess.Command).Parameters["@SubtotalNetRevenue"].Value = rec.SubtotalNetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@SubtotalInheritedNetRevenue"].Value = rec.SubtotalInheritedNetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@SubtotalPrivateNetRevenue"].Value = rec.SubtotalPrivateNetRevenue;
((SqlCommand)DataAccess.Command).Parameters["@SubtotalTradingValue"].Value = rec.SubtotalTradingValue;
((SqlCommand)DataAccess.Command).Parameters["@SubtotalInheritedTradingValue"].Value = rec.SubtotalInheritedTradingValue;
((SqlCommand)DataAccess.Command).Parameters["@SubtotalPrivateTradingValue"].Value = rec.SubtotalPrivateTradingValue;
((SqlCommand)DataAccess.Command).Parameters["@PrivateCommissionRate1"].Value = rec.PrivateCommissionRate1;
((SqlCommand)DataAccess.Command).Parameters["@PrivateCommissionRate2"].Value = rec.PrivateCommissionRate2;
affected = DataAccess.ExecuteNonQuery(query);
return affected;
}