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