public ActionResult GetPositions(bool includeInactiveStatus, string investorId)
{
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
IQueryable <PositionsForEditVm> positionInfo = positionDataAccessComponent.GetPositions(investorId, includeInactiveStatus);
return(Ok(positionInfo));
}
public List <DelinquentIncome> CaptureDelinquentPositions(string investor)
{
// At months' end, any Positions in arrears, will be returned for persistence into 'DelinquentIncome' table.
List <DelinquentIncome> delinquentIncomeCandidates = new List <DelinquentIncome>();
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
IQueryable <PositionsForEditVm> availablePositions = positionDataAccessComponent.GetPositions(investor, false);
return(delinquentIncomeCandidates);
}
public ActionResult UpdatePymtDueFlags([FromBody] List <PositionsForPaymentDueVm> positionsToUpdate)
{
// Pending updates for user-selected Position ids marked as having received income.
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
bool updatesAreValid = positionDataAccessComponent.UpdatePositionPymtDueFlags(positionsToUpdate);
if (updatesAreValid)
{
Log.Information("Payment(s) received & marked as paid ('PymtDue': False) for {0} position(s).", positionsToUpdate.Count());
}
else
{
Log.Warning("'Income due' payment processing aborted; one or more delinquent positions found among current selections. " +
"Processing via PositionController.UpdatePymtDueFlags().");
}
return(Ok(updatesAreValid));
}
public ActionResult UpdateEditedPositions([FromBody] dynamic[] positionEdits)
{
int recordsUpdatedCount = 0;
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
int positionsUpdated = positionDataAccessComponent.UpdatePositions(MapToVm(positionEdits));
string positionTickerSymbolsUpdated = string.Empty;
if (positionsUpdated == 0)
{
return(Ok(recordsUpdatedCount));
}
else
{
for (var i = 0; i < positionEdits.Length; i++)
{
if (positionTickerSymbolsUpdated.Length == 0)
{
positionTickerSymbolsUpdated = positionEdits[i].tickerSymbol.Value;
}
else
{
positionTickerSymbolsUpdated += ", " + positionEdits[i].tickerSymbol.Value;
}
}
Log.Information("Position(s) successfuly updated for {0}", positionTickerSymbolsUpdated);
}
// Update referenced 'Asset' table should asset class have been modified.
string fetchedAssetId = positionDataAccessComponent.FetchAssetId(positionEdits.First().positionId.Value);
AssetData assetDataAccessComponent = new AssetData(_ctx);
bool assetClassUpdated = assetDataAccessComponent.UpdateAssetClass(fetchedAssetId, positionEdits.First().assetClass.Value);
if (positionEdits.Length > 0)
{
recordsUpdatedCount = positionEdits.Length;
}
return(Ok(recordsUpdatedCount));
}
public IQueryable <IncomeReceivablesVm> GetPositionsWithIncomeDue(string investor)
{
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
// Get eligible Positions with currently due income, i.e., status = "A" & pymtDue.
IQueryable <IncomeReceivablesVm> filteredJoinedPositionProfileData = positionDataAccessComponent.GetPositionsForIncomeReceivables(investor);
// 'tickersWithIncomeDue' - for read/write UI only.
// 'currentOverduePositionIncomes' - for Db I/O only.
int currentMonth = DateTime.Now.Month;
if (!filteredJoinedPositionProfileData.Any())
{
return(null);
}
;
// Filter Positions for applicable dividend frequency distribution.
foreach (IncomeReceivablesVm position in filteredJoinedPositionProfileData)
{
if (position.DividendFreq == "M")
{
tickersWithIncomeDue.Add(new IncomeReceivablesVm
{
PositionId = position.PositionId,
TickerSymbol = position.TickerSymbol,
AccountTypeDesc = position.AccountTypeDesc,
DividendPayDay = position.DividendPayDay,
DividendFreq = position.DividendFreq,
MonthDue = DateTime.Now.Month
});
}
if (position.DividendFreq == "Q" || position.DividendFreq == "S" ||
position.DividendFreq == "A")
{
if (CurrentMonthIsApplicable(position.DividendMonths, currentMonth.ToString()))
{
tickersWithIncomeDue.Add(new IncomeReceivablesVm
{
PositionId = position.PositionId,
TickerSymbol = position.TickerSymbol,
AccountTypeDesc = position.AccountTypeDesc,
DividendPayDay = position.DividendPayDay,
DividendFreq = position.DividendFreq,
MonthDue = DateTime.Now.Month
});
}
}
}
// Do we have any persisted delinquencies to add ?
savedDelinquencies = positionDataAccessComponent.GetSavedDelinquentRecords(investor, "").AsQueryable();
if (savedDelinquencies.Any())
{
foreach (DelinquentIncome delinquentIncome in savedDelinquencies)
{
tickersWithIncomeDue.Add(new IncomeReceivablesVm
{
PositionId = delinquentIncome.PositionId,
TickerSymbol = delinquentIncome.TickerSymbol,
DividendFreq = delinquentIncome.DividendFreq,
AccountTypeDesc = _ctx.Position.Where(x => x.PositionId == delinquentIncome.PositionId)
.Select(y => y.AccountType.AccountTypeDesc)
.First()
.ToString(),
MonthDue = int.Parse(delinquentIncome.MonthDue)
});
}
}
return(tickersWithIncomeDue.OrderByDescending(r => r.MonthDue)
.ThenBy(r => r.DividendFreq)
.ThenBy(r => r.TickerSymbol)
.AsQueryable());
}
public IEnumerable <AssetCreationVm> ParsePortfolioSpreadsheetForAssetRecords(string filePath, ImportFileDataProcessing dataAccessComponent, string id)
{
List <AssetCreationVm> assetsToCreateList = new List <AssetCreationVm>();
var profileDataAccessComponent = new ProfileDataProcessing(_ctx);
var existingProfileId = string.Empty;
var existingAssetClassId = "6215631D-5788-4718-A1D0-A2FC00A5B1A7";;
var newAssetId = Guid.NewGuid().ToString();
List <Position> positionsToBeSaved = null;
try
{
string lastTickerProcessed = string.Empty;
var importFile = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(importFile))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
int totalRows = workSheet.Dimension.End.Row;
int totalColumns = workSheet.Dimension.End.Column;
AssetCreationVm newAsset = new AssetCreationVm();
// Iterate XLS/CSV, ignoring column headings (row 1).
for (var rowNum = 2; rowNum <= totalRows; rowNum++)
{
// Validate XLS
var headerRow = workSheet.Cells[1, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
if (!ValidateFileType(filePath))
{
return(null);
}
// Args: Cells[fromRow, fromCol, toRow, toCol]
var row = workSheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
var enumerableCells = row as string[] ?? row.ToArray();
var positionDataAccessComponent = new PositionDataProcessing(_ctx);
// Existing Position-Account implies Profile existence.
IQueryable <Asset> positionAccount = positionDataAccessComponent.GetPositionAssetByTickerAndAccount(enumerableCells.ElementAt(1).Trim(),
enumerableCells.ElementAt(0).Trim(),
id);
if (!positionAccount.Any()) // No Position-Account found.
{
IQueryable <Profile> profilePersisted = null;
// Are we processing a different ticker symbol?
if (lastTickerProcessed.Trim().ToUpper() != enumerableCells.ElementAt(1).Trim().ToUpper())
{
lastTickerProcessed = enumerableCells.ElementAt(1).Trim().ToUpper();
// Do we first have a standard web-derived Profile (via 3rd party) record in our database?
profilePersisted = profileDataAccessComponent.FetchDbProfile(enumerableCells.ElementAt(1).Trim(), "");
if (profilePersisted == null)
{
// Do we secondly have a Customized Profile (via 3rd party) record in our database?
// Check for lost _investorSvc reference.
if (_investorSvc == null)
{
_investorSvc = new InvestorSvc(_ctx);
}
;
Investor currentInvestor = _investorSvc.GetById(id);
profilePersisted = profileDataAccessComponent.FetchDbProfile(enumerableCells.ElementAt(1).Trim(), currentInvestor.LoginName);
}
if (profilePersisted != null)
{
// Bypassing Profile creation for new Position.
existingProfileId = profilePersisted.First().ProfileId;
if (assetIdForPosition == string.Empty)
{
assetIdForPosition = newAssetId;
}
// Are we processing our first XLSX Position record?
if (positionsToBeSaved == null)
{
positionsToBeSaved = InitializePositions(new List <Position>(), enumerableCells);
}
else
{
positionsToBeSaved = InitializePositions(positionsToBeSaved, enumerableCells);
}
// Error seeding collection.
if (positionsToBeSaved == null)
{
return(null);
}
assetsToCreateList.Add(new AssetCreationVm
{
AssetId = Guid.NewGuid().ToString(),
AssetClassId = existingAssetClassId,
InvestorId = id,
ProfileId = existingProfileId,
LastUpdate = DateTime.Now,
Positions = positionsToBeSaved
});
}
else
{
// Obtain a new Profile via Tiingo API.
var webProfileData = profileDataAccessComponent.BuildProfile(enumerableCells.ElementAt(1).Trim().ToUpper());
if (webProfileData == null)
{
dataAccessComponent._exceptionTickers = enumerableCells.ElementAt(1).Trim().ToUpper();
return(null); // any necessary logging done via component.
}
else
{
// Dividend freq, months, & payDay all may be edited via 'Asset Profile'
// functionality for any created *customized* Profile only. Any standard
// Profile may NOT be edited this way, as this would impact many investors.
Profile newProfile = new Profile
{
ProfileId = webProfileData.ProfileId,
DividendYield = webProfileData.DividendYield > 0
? webProfileData.DividendYield
: 0,
CreatedBy = null,
DividendRate = webProfileData.DividendRate > 0 ? webProfileData.DividendRate : 0,
ExDividendDate = webProfileData.ExDividendDate ?? new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day),
DividendFreq = webProfileData.DividendFreq ?? "M",
DividendMonths = null,
DividendPayDay = 15,
EarningsPerShare = webProfileData.EarningsPerShare > 0
? webProfileData.EarningsPerShare
: 0,
LastUpdate = DateTime.Now,
PERatio = webProfileData.PERatio > 0
? webProfileData.PERatio
: 0,
TickerDescription = webProfileData.TickerDescription.Trim(),
TickerSymbol = webProfileData.TickerSymbol.ToUpper().Trim(),
UnitPrice = webProfileData.UnitPrice
};
assetIdForPosition = newAssetId;
assetsToCreateList.Add(new AssetCreationVm
{
AssetId = Guid.NewGuid().ToString(),
AssetClassId = existingAssetClassId,
InvestorId = id, //INVESTORID,
ProfileId = newProfile.ProfileId,
LastUpdate = DateTime.Now,
Positions = positionsToBeSaved == null
? InitializePositions(new List <Position>(), enumerableCells)
: InitializePositions(positionsToBeSaved, enumerableCells),
Profile = newProfile
});
foreach (var asset in assetsToCreateList)
{
if (asset.Positions.Count == 0)
{
Log.Error("Error creating new Position(s) for assetsToCreateList in ImportFileProcessing.ParsePortfolioSpreadsheetForAssetRecords().");
return(null);
}
}
;
}
}
}
else
{
// Asset header initialization & Profile check bypassed; processing SAME ticker - DIFFERENT account.
// Adding to existing AssetCreationVm.Positions.
var updatedPositions = InitializePositions(assetsToCreateList.Last().Positions.ToList(), enumerableCells);
assetsToCreateList.Last().Positions.Add(updatedPositions.Last());
}
}
else
{
// Attempted duplicate Position-Account insertion.
lastTickerProcessed = enumerableCells.ElementAt(1).Trim();
}
} // end 'for'
} // end 'using'
}
catch (Exception ex)
{
if (ex.Message.Length > 0)
{
Log.Error("Error found within ImportFileProcessing.ParsePortfolioSpreadsheetForAssetRecords(), due to {0}.", ex.Message);
}
else
{
Log.Error("Error found within ImportFileProcessing.ParsePortfolioSpreadsheetForAssetRecords().");
}
return(null);
}
return(assetsToCreateList);
}
public ActionResult <AssetClassesVm> GetAssetClass()
{
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_context);
return(Ok(positionDataAccessComponent.GetAssetClassDescriptions()));
}
public DataImportVm SaveRevenue(DataImportVm importVmToUpdate, PIMS3Context _ctx, string investorId)
{
// Processing includes:
// 1. persisting revenue,
// 2. persisting revenue delinquencies, and
// 3. updating Positions ("PymtDue") where applicable.
ImportFileProcessing busLayerComponent = new ImportFileProcessing(importVmToUpdate, _ctx, null);
IEnumerable <Income> revenueListingToSave;
PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
if (busLayerComponent.ValidateVm())
{
revenueListingToSave = busLayerComponent.ParseRevenueSpreadsheetForIncomeRecords(importVmToUpdate.ImportFilePath.Trim(), this, investorId);
if (revenueListingToSave == null || revenueListingToSave.Count() == 0)
{
if (!string.IsNullOrEmpty(importVmToUpdate.ExceptionTickers))
{
importVmToUpdate.MiscMessage = "Error saving revenue for " + importVmToUpdate.ExceptionTickers + ". Check position(s).";
}
else
{
importVmToUpdate.MiscMessage = BuildLogMessage("revenue");
}
return(importVmToUpdate);
}
else
{
// Persist to 'Income'. Deferring use of using{}: ctx scope needed.
_ctx.AddRange(revenueListingToSave);
recordsSaved = _ctx.SaveChanges();
}
if (recordsSaved > 0)
{
totalAmtSaved = 0M;
foreach (var record in revenueListingToSave)
{
totalAmtSaved += record.AmountRecvd;
}
// Returned values to UI.
importVmToUpdate.AmountSaved = totalAmtSaved;
importVmToUpdate.RecordsSaved = recordsSaved;
// Update Positions.
positionDataAccessComponent.UpdatePositionPymtDueFlags(ExtractPositionIdsForPymtDueProcessing(revenueListingToSave), true);
/* === Month-end delinquent revenue processing === */
// If at month end/beginning, we'll query Positions for any now delinquent income receivables via "PymtDue" flag.
// Any unpaid income receivables, those not marked as received via 'Income due', will STILL have their 'PymtDue' flag set as
// True, and hence will now be considered a delinquent Position, resulting in persistence to 'DelinquentIncome' table.
// Delinquent position searches may ONLY be performed during month-end xLSX processing, as income receipts are still being
// accepted during the month via 'Income due'. During 'Income due' payments, flags are set accordingly: [PymtDue : True -> False].
// Any delinquent Positions will automatically be added to the 'Income due' collection that is broadcast via the UI schedule,
// for necessary action.
if (DateTime.Now.Day <= 3 && DateTime.Now.DayOfWeek.ToString() != "Saturday" && DateTime.Now.DayOfWeek.ToString() != "Sunday")
{
//PositionDataProcessing positionDataAccessComponent = new PositionDataProcessing(_ctx);
IQueryable <dynamic> filteredPositionAssetJoinData = positionDataAccessComponent.GetCandidateDelinquentPositions(investorId);
IList <DelinquentIncome> pastDueListing = new List <DelinquentIncome>();
string delinquentMonth = DateTime.Now.AddMonths(-1).Month.ToString().Trim();
int matchingPositionDelinquencyCount = 0;
IList <DelinquentIncome> existingDelinqencies = positionDataAccessComponent.GetSavedDelinquentRecords(investorId, "");
int duplicateDelinquencyCount = 0;
foreach (dynamic joinData in filteredPositionAssetJoinData)
{
// 'DelinquentIncome' PKs: InvestorId, MonthDue, PositionId
// Do we have a match based on PositionId & InvestorId ? If so, Position is eligible for omitting setting 'PymtDue' to True.
matchingPositionDelinquencyCount = existingDelinqencies.Where(d => d.PositionId == joinData.PositionId &&
d.InvestorId == joinData.InvestorId).Count();
if (matchingPositionDelinquencyCount >= 1)
{
// Do we have a duplicate entry ?
duplicateDelinquencyCount = existingDelinqencies.Where(d => d.PositionId == joinData.PositionId &&
d.InvestorId == joinData.InvestorId &&
d.MonthDue == delinquentMonth).Count();
}
if (joinData.DividendFreq == "M")
{
if (matchingPositionDelinquencyCount > 0 && duplicateDelinquencyCount == 0)
{
pastDueListing.Add(InitializeDelinquentIncome(joinData));
}
else
{
continue;
}
}
else
{
string[] divMonths = joinData.DividendMonths.Split(',');
for (var i = 0; i < divMonths.Length; i++)
{
if (divMonths[i].Trim() == delinquentMonth)
{
if (matchingPositionDelinquencyCount > 0 && duplicateDelinquencyCount == 0)
{
pastDueListing.Add(InitializeDelinquentIncome(joinData));
}
else
{
continue;
}
}
}
}
matchingPositionDelinquencyCount = 0;
}
// Persist to 'DelinquentIncome' as needed.
if (pastDueListing.Any())
{
bool pastDueSaved = positionDataAccessComponent.SaveDelinquencies(pastDueListing.ToList());
if (pastDueSaved)
{
importVmToUpdate.MiscMessage = "Found & stored " + pastDueListing.Count() + " Position(s) with delinquent revenue.";
Log.Information("Saved {0} delinquent position(s) to 'DelinquentIncome' via ImportFileDataProcessing.SaveRevenue()", pastDueListing.Count());
}
}
// Finally, update PymtDue flags on received XLSX positions.
positionDataAccessComponent.UpdatePositionPymtDueFlags(ExtractPositionIdsForPymtDueProcessing(revenueListingToSave), true);
}
}
_ctx.Dispose();
}
// Missing amount & record count reflects error condition.
return(importVmToUpdate);
}