protected IEnumerable <RegionalDirectorRateInfoRevisedDataModel> LoadRegionalDirectorRateInfoRevised()
{
var dal = new DapperDatabaseAccess <RegionalDirectorRateInfoRevisedDataModel>(_connectionString);
var result = dal.SqlServerFetch("dbo.prc_RegionalDirector_RateInfo_Revised_Fetch_All", null);
return(result);
}
public void ProcessInboundAumFile()
{
try
{
using (var reader = new StreamReader(_path))
using (var csv = new CsvReader(reader))
{
//Truncate inbound sales staging file
var dal = new DapperDatabaseAccess <GenevaAumInbound>(_connectionString);
dal.SqlExecute("dbo.prc_tbl_GenevaAum_Staging_Truncate", null);
//Ingest sales into staging table
List <GenevaAumInbound> inboundSales = csv.GetRecords <GenevaAumInbound>().ToList();
DbHandlers.BulkInsertToDatabase(ListDataTable.ListToDataTable(inboundSales), _connectionString, 1000, _metaData.stagingTable);
//Transform from staging to production
var parms = new DynamicParameters();
parms.Add("@ReportEndDate", _reportingDate);
dal.SqlExecute("dbo.prc_GenevaAum_Transform", parms);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
protected IEnumerable <AristotleExcelStyle> ExcelColumnProperties(string reportName)
{
var dal = new DapperDatabaseAccess <AristotleExcelStyle>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ReportName", reportName);
return(dal.SqlServerFetch("dbo.prc_ColumnStyles_Fetch", parms));
}
private async Task <bool> HealthCheck()
{
var env = CustomBootstrapper.Configuration["RuntimeEnvironment:Environment"];
var dal = new DapperDatabaseAccess <R2D2HealthcheckPortfolioSwapCounterpartyActivity>(CustomBootstrapper.Configuration[$"{env}:R2D2:ConnectionString"]);
var rowcount = dal.SqlQuery("select count(CounterpartyActivityIid) from dbo.PortfolioSwapCounterpartyActivity").FirstOrDefault();
return(rowcount != null && rowcount.ActivityCount > 0);
}
protected void InitializeSalesData(string endDate)
{
var dal = new DapperDatabaseAccess <GenevaSmaAssetsDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ReportingDate", endDate);
_genevaSales = dal.SqlServerFetch("dbo.prc_GenevaSmaAssets_DetailRows", parms).Distinct();
}
private RegionalDirectorRateInfoDataModel RegionalDirectorRateInfo(string rd, int rateType, int commissionType)
{
var dal = new DapperDatabaseAccess <RegionalDirectorRateInfoDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@RegionalDirectorKey", rd);
parms.Add("@RateTypeIid", rateType);
parms.Add("@CommissionTypeIid", commissionType);
return(dal.SqlServerFetch("dbo.prc_RegionalDirector_RateInfo_Fetch", parms).FirstOrDefault());
}
protected void InitializeAumData(string endDate)
{
var dal = new DapperDatabaseAccess <GenevaSmaAumDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ReportingDate", endDate);
_aumItems = dal.SqlServerFetch("dbo.prc_GenevaAum", parms);
}
private IEnumerable <GenevaFlows> InitNewAssetsFlows()
{
var dal = new DapperDatabaseAccess <GenevaFlows>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ReportEndDate", _endDate, DbType.Date, ParameterDirection.Input);
return(dal.SqlServerFetch("dbo.prc_GenevaFlows", parms));
}
private IEnumerable <UmaMerrillDataModel> InitMerrillData()
{
var dal = new DapperDatabaseAccess <UmaMerrillDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@StartDate", _startDate, DbType.Date, ParameterDirection.Input);
parms.Add("@EndDate", _endDate, DbType.Date, ParameterDirection.Input);
return(dal.SqlServerFetch("dbo.prc_Uma_Merrill_Data_Fetch", parms));
}
protected void InitializeNewAssetsData(string endDate)
{
var dal = new DapperDatabaseAccess <BroadridgeAssets>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ReportingDate", endDate);
_broadridgeNewAssets = dal.SqlServerFetch("dbo.prc_BroadridgeAssets_MorganMerrill_Fetch", parms);
}
protected void InitializeSalesData(string endDate)
{
var dal = new DapperDatabaseAccess <BroadridgeSales>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ReportingDate", endDate);
parms.Add("@IsUma", _isUma);
_broadridgeSales = dal.SqlServerFetch("dbo.prc_BroadridgeSales_Fetch", parms);
}
private IEnumerable <GenevaSmaAssetsDataModel> InitNewAssetsItems()
{
var dal = new DapperDatabaseAccess <GenevaSmaAssetsDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@StartDate", _startDate, DbType.Date, ParameterDirection.Input);
parms.Add("@EndDate", _endDate, DbType.Date, ParameterDirection.Input);
return(dal.SqlServerFetch("dbo.prc_GenevaSmaAssets_DetailRows", parms).Distinct());
}
private IEnumerable <GenevaSmaFlowsDataModel> BuildFlowsDetail()
{
var dal = new DapperDatabaseAccess <GenevaSmaFlowsDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@StartDate", _startDate, DbType.Date, ParameterDirection.Input);
parms.Add("@EndDate", _endDate, DbType.Date, ParameterDirection.Input);
return(dal.SqlServerFetch("dbo.prc_GenevaSma_Ongoing_Flows", parms));
}
protected RegionalDirectorRateInfoDataModel RegionalDirectorRateInfo(string rd, int rateType, int commissionType)
{
var dal = new DapperDatabaseAccess <RegionalDirectorRateInfoDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@RegionalDirectorKey", rd);
parms.Add("@RateTypeIid", rateType);
parms.Add("@CommissionTypeIid", commissionType);
var result = dal.SqlServerFetch("dbo.prc_RegionalDirector_RateInfo_Revised_Fetch_All", null).FirstOrDefault() ?? new RegionalDirectorRateInfoDataModel();
return(result);
}
protected IEnumerable <RegionalDirectorRateInfoRevisedDataModel> InitRateInfo()
{
try
{
var dal = new DapperDatabaseAccess <RegionalDirectorRateInfoRevisedDataModel>(_connectionString);
return(dal.SqlServerFetch("dbo.prc_RegionalDirector_RateInfo_Revised_Fetch_All", null));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
private static int Main(string[] args)
{
IConfigurationBuilder builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
_configuration = builder.Build();
_connectionString = _configuration["Database:ConnectionString"];
try
{
//Load task metadata
_dal = new DapperDatabaseAccess <TaskMetaDataDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ObjectCode", args[0]);
_metaData = _dal.SqlServerFetch("dbo.prc_TaskMetaData_Fetch_By_ObjectCode", parms).FirstOrDefault();
switch (_metaData.objectCode)
{
case "ETL.BROADRIDGE.SALES":
var broadridgeSales = new Etl_Broadridge(_metaData, _configuration, _connectionString, args[1]);
broadridgeSales.ProcessInboundSalesFile();
break;
case "ETL.BROADRIDGE.ASSETS":
var broadridgeAssets = new Etl_Broadridge(_metaData, _configuration, _connectionString, args[1]);
broadridgeAssets.ProcessInboundAssetsFile();
break;
case "ETL.GENEVA.SMA":
var genevaSales = new Etl_Geneva(_metaData, _configuration, _connectionString, args[1]);
genevaSales.ProcessInboundSalesFile();
break;
case "ETL.GENEVA.AUM":
var genevaAum = new Etl_Geneva(_metaData, _configuration, _connectionString, args[1]);
genevaAum.ProcessInboundAumFile();
break;
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
Console.WriteLine("Closing the application");
Console.WriteLine("Hit enter...");
Console.ReadLine();
return((int)ExeRunStatus.Success);
}
private IEnumerable <GenevaSmaAumDataModel> InitAumItems()
{
try
{
var dal = new DapperDatabaseAccess <GenevaSmaAumDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@EndDate", _endDate, DbType.Date, ParameterDirection.Input);
return(dal.SqlServerFetch("dbo.prc_GenevaAum_DetailRows", parms));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
private static int Main(string[] args)
{
//Initialize system from appsettings.json
IConfigurationBuilder builder = new ConfigurationBuilder().AddJsonFile("appsettings.json");
_configuration = builder.Build();
_connectionString = _configuration["Database:ConnectionString"];
//Load task metadata
_dal = new DapperDatabaseAccess <TaskMetaDataDataModel>(_connectionString);
var parms = new DynamicParameters();
parms.Add("@ObjectCode", args[0]);
_metaData = _dal.SqlServerFetch("dbo.prc_TaskMetaData_Fetch_By_ObjectCode", parms).FirstOrDefault();
switch (_metaData.objectCode)
{
case "COMM.GENEVA.SMA":
var smaReport = new GenevaSmaStatement(args[1], args[2], _connectionString, _metaData);
smaReport.ProduceReport();
break;
case "COMM.BROADRIDGE.UMA":
var broadRidgeStatement = new BroadridgeStatement(args[1], args[2], _connectionString, _metaData, true);
broadRidgeStatement.ProduceNonMerrillMorganReport();
break;
case "COMM.BROADRIDGE.MERRILL_MORGAN.UMA":
var broadRidgeMerrillMorganStatement = new BroadridgeMerrillMorganStatement(args[1], _connectionString, _metaData);
broadRidgeMerrillMorganStatement.ProduceReport();
break;
case "COMM.BROADRIDGE.MUTUALFUNDS":
// var broadRidgeMfStatement = new BroadridgeStatement(args[1], args[2], _connectionString, _metaData, false);
// broadRidgeMfStatement.ProduceReport();
break;
}
return(0);
}
[Fact] public void StoredProcErrorTest()
{
using (var db = new DapperDatabaseAccess <Customer>(ConnectionString))
{
try
{
var parms = new DynamicParameters();
parms.Add("@name", "SuperCalifragilisticexpialidocious");
parms.Add("@totalPurchases", 1200.00m);
parms.Add("@totalReturns", 200.00m);
db.SqlExecute("UnitTestCreateCustomer", parms);
}
catch (Exception ex)
{
var efd = GetAcmLoggingDetail("", ex);
AcmLogger.WriteError(efd);
}
}
Assert.True(true);
}
public void ProcessInboundSalesFile()
{
try
{
using (var reader = new StreamReader(_path))
using (var csv = new CsvReader(reader))
{
//Truncate inbound sales staging file
var dal = new DapperDatabaseAccess <BroadridgeSalesInbound>(_connectionString);
dal.SqlExecute("dbo.prc_BroadridgeSales_Staging_Truncate", null);
//Ingest sales into staging table
List <BroadridgeSalesInbound> inboundSales = csv.GetRecords <BroadridgeSalesInbound>().ToList();
foreach (var item in inboundSales)
{
var p = new DynamicParameters();
p.Add("@TradeID", item.TradeID);
p.Add("@TransactionCodeOverrideDescription", item.TransactionCodeOverrideDescription);
p.Add("@TradeDate", item.TradeDate);
p.Add("@SettledDate", item.SettledDate);
p.Add("@SuperSheetDate", item.SuperSheetDate);
p.Add("@TradeAmount", item.TradeAmount);
p.Add("@System", item.System);
p.Add("@DealerNum", item.DealerNum);
p.Add("@DealerBranchBranchCode", item.DealerBranchBranchCode);
p.Add("@RepCode", item.RepCode);
p.Add("@FirmId", item.FirmId);
p.Add("@FirmName", item.FirmName);
p.Add("@OfficeAddressLine1", item.OfficeAddressLine1);
p.Add("@OfficeCity", item.OfficeCity);
p.Add("@OfficeRegionRefCode", item.OfficeRegionRefCode);
p.Add("@OfficePostalCode", item.OfficePostalCode);
p.Add("@PersonFirstName", item.PersonFirstName);
p.Add("@PersonLastName", item.PersonLastName);
p.Add("@LineOfBusiness", item.LineOfBusiness);
p.Add("@Channel", item.Channel);
p.Add("@Region", item.Region);
p.Add("@Territory", item.Territory);
p.Add("@ProductNasdaqSymbol", item.ProductNasdaqSymbol);
p.Add("@ProductName", item.ProductName);
p.Add("@AccountTANumber", item.AccountTANumber);
p.Add("@AccountId", item.AccountId);
p.Add("@ExternalAccountNumber", item.ExternalAccountNumber);
p.Add("@HoldingId", item.HoldingId);
p.Add("@HoldingExternalAccountNumber", item.HoldingExternalAccountNumber);
p.Add("@HoldingName", item.HoldingName);
dal.SqlExecute("dbo.prc_BroadridgeSales_Staging_Save", p);
}
//Transform from staging to production
var parms = new DynamicParameters();
parms.Add("@ReportingDate", DateTimeHandlers.GetQuarterEndDateFromYearQuarter(_reportingDate).ToString("yyyyMMdd"));
// parms.Add("@ReportingDate", _reportingDate);
dal.SqlExecute("dbo.prc_BroadridgeSales_Transform", parms);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public void ProcessInboundAssetsFile()
{
try
{
using (var reader = new StreamReader(_path))
using (var csv = new CsvReader(reader))
{
//Truncate inbound sales staging file
var dal = new DapperDatabaseAccess <BroadridgeAssetsInbound>(_connectionString);
dal.SqlExecute("dbo.prc_BroadridgeAssets_Staging_Truncate", null);
//Ingest sales into staging table
List <BroadridgeAssetsInbound> inboundAssets = csv.GetRecords <BroadridgeAssetsInbound>().ToList();
// DbHandlers.BulkInsertToDatabase(ListDataTable.ListToDataTable(inboundAssets), _connectionString, 1000, _metaData.stagingTable);
foreach (var item in inboundAssets)
{
var p = new DynamicParameters();
p.Add("@System", item.System);
p.Add("@FirmName", item.FirmName);
p.Add("@FirmId", item.FirmId);
p.Add("@FirmCRDNumber", item.FirmCRDNumber);
p.Add("@HoldingId", item.HoldingId);
p.Add("@HoldingExternalAccountNumber", item.HoldingExternalAccountNumber);
p.Add("@HoldingName", item.HoldingName);
p.Add("@HoldingStartDate", item.HoldingStartDate);
p.Add("@HoldingCreateDate", item.HoldingCreateDate);
p.Add("@MostRecentMonthAssetBalance", item.MostRecentMonthAssetBalance);
p.Add("@Month1AgoAssetBalance", item.Month1AgoAssetBalance);
p.Add("@Month2AgoAssetBalance", item.Month2AgoAssetBalance);
p.Add("@Month3AgoAssetBalance", item.Month3AgoAssetBalance);
p.Add("@ProductName", item.ProductName);
p.Add("@ProductType", item.ProductType);
p.Add("@Channel", item.Channel);
p.Add("@Region", item.Region);
p.Add("@Territory", item.Territory);
p.Add("@PersonCRDNumber", item.PersonCRDNumber);
p.Add("@PersonFirstName", item.PersonFirstName);
p.Add("@PersonLastName", item.PersonLastName);
p.Add("@PersonId", item.PersonId);
p.Add("@OfficeAddressLine1", item.OfficeAddressLine1);
p.Add("@OfficeAddressLine2", item.OfficeAddressLine2);
p.Add("@OfficeCity", item.OfficeCity);
p.Add("@OfficeRegionRefCode", item.OfficeRegionRefCode);
p.Add("@OfficePostalCode", item.OfficePostalCode);
p.Add("@PersonBrokerTeamFlag", item.PersonBrokerTeamFlag);
p.Add("@Month4AgoAssetBalance", item.Month4AgoAssetBalance);
p.Add("@Month5AgoAssetBalance", item.Month5AgoAssetBalance);
p.Add("@Month6AgoAssetBalance", item.Month6AgoAssetBalance);
p.Add("@Month7AgoAssetBalance", item.Month7AgoAssetBalance);
p.Add("@Month8AgoAssetBalance", item.Month8AgoAssetBalance);
p.Add("@Month9AgoAssetBalance", item.Month9AgoAssetBalance);
p.Add("@Month10AgoAssetBalance", item.Month10AgoAssetBalance);
p.Add("@Month11AgoAssetBalance", item.Month11AgoAssetBalance);
p.Add("@Month12AgoAssetBalance", item.Month12AgoAssetBalance);
p.Add("@HoldingAddressLine1", item.HoldingAddressLine1);
p.Add("@AccountTANumber", item.AccountTANumber);
p.Add("@ExternalAccountNumber", item.ExternalAccountNumber);
p.Add("@AccountId", item.AccountId);
dal.SqlExecute("dbo.prc_BroadridgeAssets_Staging_Save", p);
}
//Transform from staging to production
var parms = new DynamicParameters();
parms.Add("@ReportingDate", DateTimeHandlers.GetQuarterEndDateFromYearQuarter(_reportingDate).ToString("yyyyMMdd"));
dal.SqlExecute("dbo.prc_BroadridgeAssets_Transform", parms);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
