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