public int Update(ClientTradeData rec)
{
int affected = 0;
string query = @"
UPDATE ClientTradeData
SET
TotalTradingValue = @TotalTradingValue,
TotalCommission = @TotalCommission
WHERE
(ClientTypeID = @ClientTypeID) AND (BrokerID = @BrokerID) AND (datediff(month, [Date], getdate()) = 0)";
((SqlCommand)DataAccess.Command).Parameters.Clear();
((SqlCommand)DataAccess.Command).Parameters.Add("@ClientTypeID", SqlDbType.Int);
((SqlCommand)DataAccess.Command).Parameters.Add("@BrokerID", SqlDbType.VarChar);
((SqlCommand)DataAccess.Command).Parameters.Add("@TotalTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@TotalCommission", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters["@ClientTypeID"].Value = rec.ClientType.Id;
((SqlCommand)DataAccess.Command).Parameters["@BrokerID"].Value = rec.Broker.Id;
((SqlCommand)DataAccess.Command).Parameters["@TotalTradingValue"].Value = rec.TradingValue;
((SqlCommand)DataAccess.Command).Parameters["@TotalCommission"].Value = rec.Commission;
affected = DataAccess.ExecuteNonQuery(query);
return affected;
}
public List<ClientTradeData> GetAllClientTradeData(string calculPeriodId)
{
List<ClientTradeData> retData = new List<ClientTradeData>();
string query = @"SELECT [ClientTypeID], [BrokerID], [TotalTradingValue], [TotalCommission] FROM [ClientTradeData] WHERE calculationPeriodId = $CALCULATIONID$";
query = query.Replace("$CALCULATIONID$", calculPeriodId);
DataSet dataSet = DataAccess.ExecuteQuery(query);
if ((dataSet != null) && (dataSet.Tables.Count > 0) && (dataSet.Tables[0].Rows.Count > 0))
{
ClientTradeData tradeData = null;
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
tradeData = new ClientTradeData();
// Client Type
tradeData.ClientTypeId = (int)dataSet.Tables[0].Rows[i][0];
// Broker
tradeData.BrokerId = dataSet.Tables[0].Rows[i][1].ToString();
// TradingValue
tradeData.TradingValue = (decimal) dataSet.Tables[0].Rows[i][2];
// Gross Revenue
tradeData.Commission = (decimal) dataSet.Tables[0].Rows[i][3];
retData.Add(tradeData);
}
}
return retData;
}
public int InsertClientTradeData(ClientTradeData rec)
{
int affected = 0;
string query = @"
INSERT INTO ClientTradeData
(ClientTypeID,
BrokerID,
TotalTradingValue,
TotalCommission,
calculationPeriodId,
updatedTime)
VALUES
(@ClientTypeID,
@BrokerID,
@TotalTradingValue,
@TotalCommission,
@CalculationPeriodId,
getdate())";
((SqlCommand)DataAccess.Command).Parameters.Clear();
((SqlCommand)DataAccess.Command).Parameters.Add("@ClientTypeID", SqlDbType.Int);
((SqlCommand)DataAccess.Command).Parameters.Add("@BrokerID", SqlDbType.VarChar);
((SqlCommand)DataAccess.Command).Parameters.Add("@TotalTradingValue", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@TotalCommission", SqlDbType.Money);
((SqlCommand)DataAccess.Command).Parameters.Add("@CalculationPeriodId", SqlDbType.NChar);
((SqlCommand)DataAccess.Command).Parameters["@ClientTypeID"].Value = rec.ClientTypeId;
((SqlCommand)DataAccess.Command).Parameters["@BrokerID"].Value = rec.BrokerId;
((SqlCommand)DataAccess.Command).Parameters["@TotalTradingValue"].Value = rec.TradingValue;
((SqlCommand)DataAccess.Command).Parameters["@TotalCommission"].Value = rec.Commission;
((SqlCommand)DataAccess.Command).Parameters["@CalculationPeriodId"].Value = rec.CalculPeriodId;
affected = DataAccess.ExecuteNonQuery(query);
return affected;
}
public void ImportTradeData(DataTable dataTable, string calculPeriodId)
{
if (dataTable.Columns.Count < 5)
{
throw new InvalidDataException("Imported data column count missmatched! Required 5 columns but available " + dataTable.Columns.Count);
}
List<ClientTradeData> dataList = new List<ClientTradeData>();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (dataTable.Rows[i][0] != DBNull.Value)
{
string brokerId = dataTable.Rows[i][0].ToString().Trim();
if (brokerId.Length > 0)
{
ClientTradeData oldClientTrade = new ClientTradeData();
oldClientTrade.CalculPeriodId = calculPeriodId;
oldClientTrade.BrokerId = brokerId;
oldClientTrade.ClientTypeId = ClientType.CLIENTTYPE_ID_FROMHSC;
decimal oldTrdValueHN = 0;
decimal oldCommissionHN = 0;
bool isHN = false;
if (dataTable.Rows[i][5] != DBNull.Value && Convert.ToString(dataTable.Rows[i][5]).Trim().ToUpper() == "NORTHERN")
{
isHN = true;
}
if (dataTable.Rows[i][1] != DBNull.Value)
{
if (isHN)
{
oldClientTrade.TradingValue = 0;
oldTrdValueHN = Convert.ToDecimal(dataTable.Rows[i][1]);
}
else oldClientTrade.TradingValue = Convert.ToDecimal(dataTable.Rows[i][1]);
}
else
{
oldClientTrade.TradingValue = 0;
}
if (dataTable.Rows[i][2] != DBNull.Value)
{
if (isHN)
{
oldClientTrade.Commission = 0;
oldCommissionHN = Convert.ToDecimal(dataTable.Rows[i][2]);
}
else oldClientTrade.Commission = Convert.ToDecimal(dataTable.Rows[i][2]);
}
else
{
oldClientTrade.Commission = 0;
}
oldClientTrade.Date = DateTime.Now;
dataList.Add(oldClientTrade);
ClientTradeData newClientTrade = new ClientTradeData();
newClientTrade.BrokerId = brokerId;
newClientTrade.CalculPeriodId = calculPeriodId;
newClientTrade.ClientTypeId = ClientType.CLIENTTYPE_ID_BYBROKER;
if (dataTable.Rows[i][3] != DBNull.Value)
{
newClientTrade.TradingValue = Convert.ToDecimal(dataTable.Rows[i][3]);
if (isHN) newClientTrade.TradingValue += oldTrdValueHN;
}
else
{
newClientTrade.TradingValue = 0;
if (isHN) newClientTrade.TradingValue += oldTrdValueHN;
}
if (dataTable.Rows[i][4] != DBNull.Value)
{
newClientTrade.Commission = Convert.ToDecimal(dataTable.Rows[i][4]);
if (isHN) newClientTrade.Commission += oldCommissionHN;
}
else
{
newClientTrade.Commission = 0;
if (isHN) newClientTrade.Commission += oldCommissionHN;
}
newClientTrade.Date = DateTime.Now;
dataList.Add(newClientTrade);
}
}
}
for (int i = 0; i < dataList.Count; i++)
{
int inserted = ClientTradeDataAccess.InsertClientTradeData(dataList[i]);
_logger.Debug("Inserted=" + inserted + " - " + dataList[i].ToString());
}
}