public ProfilesUpdateSummaryResultModel BatchUpdateProfiles(string currentInvestor)
{
// Partially updates all investors' asset Profiles (freq, months, & payday), based on *12 month* revenue history.
int updateCount = 0;
IncomeData.IncomeDataProcessing incomeDataAccessComponent = new IncomeData.IncomeDataProcessing(_ctx);
CommonSvc svc = new CommonSvc(_ctx);
ProfilesUpdateSummaryResultModel results = new ProfilesUpdateSummaryResultModel();
ProfileForUpdateVm processedResults = incomeDataAccessComponent.Process12MosRevenueHistory(svc.GetInvestorIdFromInvestor(currentInvestor));
if (!processedResults.UpdateHasErrors)
{
List <Data.Entities.Profile> profilesToUpdate = processedResults.BatchProfilesList;
try
{
_ctx.UpdateRange(profilesToUpdate);
updateCount = _ctx.SaveChanges();
}
catch (Exception ex)
{
Log.Error("Error updating Profile(s) via ProfileDataProcessing.BatchUpdateProfiles(), due to :{0} '", ex.InnerException + "'");
return(null);
}
}
else
{
Log.Error("Error processing revenue history for dividend payment data, recorded via ProfileDataProcessing.BatchUpdateProfiles().");
return(null);
}
// Initialize with just relevant info, for use by UI.
results.ProcessedTickersCount = updateCount > 0 ? updateCount : 0;
results.OmittedTickers = processedResults.ExceptionTickerSymbols != string.Empty ? processedResults.ExceptionTickerSymbols.Trim() : string.Empty;
Log.Information("Updated " + results.ProcessedTickersCount + " Profiles based on revenue history, for " + currentInvestor);
if (results.OmittedTickers != string.Empty)
{
Log.Information("Omitted Profile updates based on revenue history for " + results.OmittedTickers);
}
return(results);
}
public Dictionary <string, string> CalculateDivFreqAndDivMonths(string tickerSymbol, Data.PIMS3Context ctx)
{
StringBuilder oneYearDivPayMonths_1 = new StringBuilder();
string oneYearDivPayMonths_2 = string.Empty;
int divFreqCounter = 0;
string tempDate = "";
var profileDataAccessComponent = new ProfileDataProcessing(ctx);
JArray orderedJsonTickerPriceData = profileDataAccessComponent.FetchDividendSpecsForTicker(tickerSymbol);
if (orderedJsonTickerPriceData == null)
{
Log.Warning("ProfileProcessing.CalculateDivFreqAndDivMonths() - found unresolved ticker in Tiingo service, unable to update Profile price data for {0}: ", tickerSymbol);
return(null);
}
List <int> divPayoutDays = new List <int>();
foreach (JObject objChild in orderedJsonTickerPriceData.Children <JObject>())
{
// Loop will key in on "divCash" for gathering needed freq & month specs.
foreach (var property in objChild.Properties())
{
if (property.Name == "date")
{
tempDate = property.Value.ToString();
}
if (property.Name == "divCash")
{
if (decimal.Parse(property.Value.ToString()) > 0)
{
divFreqCounter += 1;
oneYearDivPayMonths_1.Append(ExtractMonthFromDivPayDate(tempDate));
divPayoutDays.Add(ExtractDivPayDay(tempDate));
oneYearDivPayMonths_1.Append(",");
}
}
}
}
int medianDivPayoutDay = CommonSvc.CalculateMedianValue(divPayoutDays);
if (oneYearDivPayMonths_1.Length == 0)
{
return(null);
}
// Strip trailing comma.
oneYearDivPayMonths_2 = oneYearDivPayMonths_1.ToString().Substring(0, oneYearDivPayMonths_1.Length - 1);
string[] oneYearDivPayMonths_3 = oneYearDivPayMonths_2.Split(',');
Dictionary <string, string> finalProfileSpecs = new Dictionary <string, string>();
int monthsCount = oneYearDivPayMonths_3.Length;
/* == finalProfileSpecs Keys legend: ==
* "DF"(dividend frequency)
* "DM"(dividend months)
* "DPD"(dividend payout day)
*/
if (monthsCount >= 3 && monthsCount <= 5)
{
// Due to possible inconsistent number of income receipts made within the last 12 month price history obtained
// from our 3rd party service (Tiingo), we'll account for this by designating as (Q)uarterly dividend frequency.
finalProfileSpecs.Add("DF", "Q");
finalProfileSpecs.Add("DM", oneYearDivPayMonths_2);
}
else if (monthsCount >= 5)
{
finalProfileSpecs.Add("DF", "M");
finalProfileSpecs.Add("DM", "-");
}
else if (monthsCount == 2)
{
finalProfileSpecs.Add("DF", "S");
finalProfileSpecs.Add("DM", oneYearDivPayMonths_2);
}
else
{
finalProfileSpecs.Add("DF", "A");
finalProfileSpecs.Add("DM", oneYearDivPayMonths_2);
}
if (medianDivPayoutDay > 0)
{
finalProfileSpecs.Add("DPD", medianDivPayoutDay.ToString());
}
return(finalProfileSpecs);
}
public IEnumerable <Data.Entities.Income> ParseRevenueSpreadsheetForIncomeRecords(string filePath, ImportFileDataProcessing dataAccessComponent, string loggedInvestorId)
{
List <Data.Entities.Income> newIncomeListing = new List <Data.Entities.Income>();
IncomeDataProcessing incomeDataAccessComponent = new IncomeDataProcessing(_ctx);
DataAccess.Asset.AssetData assetDataAccessComponent = new DataAccess.Asset.AssetData(_ctx);
IQueryable <string> fetchedPositionId;
try
{
FileInfo importFile = new FileInfo(filePath);
using (var package = new ExcelPackage(importFile))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
int totalRows = workSheet.Dimension.End.Row;
int totalColumns = workSheet.Dimension.End.Column;
_xlsTickerSymbolsOmitted = string.Empty;
for (var rowNum = 2; rowNum <= totalRows; rowNum++)
{
// Validate XLS
IEnumerable <string> headerRow = workSheet.Cells[1, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
if (!ValidateFileAttributes(true, headerRow) || !ValidateFileType(filePath))
{
return(null);
}
IEnumerable <string> row = workSheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
string[] enumerableCells = row as string[] ?? row.ToArray();
// 'totalRows' may yield inaccurate results; we'll test for last record, e.g., 'enumerableCells[0] ('Recvd Date').
if (!enumerableCells.Any() || enumerableCells[0] == "")
{
if (_xlsTickerSymbolsOmitted.Any())
{
dataAccessComponent._exceptionTickers = _xlsTickerSymbolsOmitted;
return(null);
}
return(newIncomeListing);
}
string xlsTicker = enumerableCells.ElementAt(3).Trim();
string xlsAccount = CommonSvc.ParseAccountTypeFromDescription(enumerableCells.ElementAt(1).Trim());
fetchedPositionId = assetDataAccessComponent.FetchPositionId(loggedInvestorId, xlsTicker, xlsAccount).AsQueryable();
// Checking PositionId rather than asset is sufficient.
// Validate either a bad ticker symbol, or that no account was affiliated with the position/asset in question.
if (!fetchedPositionId.Any())
{
if (_xlsTickerSymbolsOmitted == string.Empty)
{
_xlsTickerSymbolsOmitted += xlsTicker;
}
else
{
_xlsTickerSymbolsOmitted += ", " + xlsTicker;
}
continue;
}
duplicateResults = incomeDataAccessComponent.FindIncomeDuplicates(fetchedPositionId.First().ToString(), enumerableCells.ElementAt(0), enumerableCells.ElementAt(4));
if (duplicateResults.Any())
{
if (_xlsTickerSymbolsOmitted == string.Empty)
{
_xlsTickerSymbolsOmitted += xlsTicker;
}
else
{
_xlsTickerSymbolsOmitted += ", " + xlsTicker;
}
continue;
}
if (_xlsTickerSymbolsOmitted != string.Empty)
{
_viewModel.ExceptionTickers = _xlsTickerSymbolsOmitted;
}
Data.Entities.Income newIncomeRecord = new Data.Entities.Income
{
IncomeId = Guid.NewGuid().ToString(),
PositionId = fetchedPositionId.First().ToString(),
DateRecvd = DateTime.Parse(enumerableCells.ElementAt(0)),
AmountRecvd = decimal.Parse(enumerableCells.ElementAt(4)),
LastUpdate = DateTime.Now
};
newIncomeListing.Add(newIncomeRecord);
_totalXlsIncomeRecordsToSave += 1;
newIncomeRecord = null;
} // end 'for'
if (_xlsTickerSymbolsOmitted.Length > 0)
{
_viewModel.ExceptionTickers = _xlsTickerSymbolsOmitted;
Log.Warning("Invalid XLS/XLSX position(s) found, revenue import aborted for {0}.", _xlsTickerSymbolsOmitted);
}
return(newIncomeListing);
} // end 'using'
}
catch (Exception ex)
{
if (ex.Message.Length > 0)
{
Log.Error("Invalid xlsx format, or bad file path found within ImportFileProcessing.ParseRevenueSpreadsheetForIncomeRecords(), due to {0}.", ex.Message);
}
else
{
Log.Error("Error found within ImportFileProcessing.ParseRevenueSpreadsheetForIncomeRecords().");
}
return(null);
}
}
public ProfileForUpdateVm Process12MosRevenueHistory(string investorId)
{
// Revenue processing in response to investor-initiated Profile updating.
ProfileForUpdateVm vm = new ProfileForUpdateVm();
try
{
string exceptionTickers = string.Empty;
IQueryable <string> currentPositions = _ctx.Position.Where(p => p.PositionAsset.InvestorId == investorId && p.Status == "A")
.Select(p => p.PositionAsset.Profile.TickerSymbol)
.Distinct()
.AsQueryable();
IQueryable <PositionIncomeVm> joinedPositionIncome = _ctx.Position.Where(p => p.PositionAsset.InvestorId == investorId && p.Status == "A")
.Join(_ctx.Income, p => p.PositionId, i => i.PositionId,
(p, i) => new PositionIncomeVm
{
ProfileId = p.PositionAsset.Profile.ProfileId,
PositionId = p.PositionId,
TickerSymbol = p.PositionAsset.Profile.TickerSymbol,
Account = p.AccountType.AccountTypeDesc,
DateRecvd = i.DateRecvd,
DividendYield = Convert.ToDecimal(p.PositionAsset.Profile.DividendYield),
TickerDescription = p.PositionAsset.Profile.TickerDescription
})
.OrderBy(results => results.TickerSymbol)
.ThenBy(results => results.Account)
.AsQueryable();
joinedPositionIncome.OrderBy(pi => pi.DateRecvd);
// To be initialized return collection.
List <Data.Entities.Profile> updateProfileModelList = new List <Data.Entities.Profile>();
foreach (string currentTicker in currentPositions)
{
// Do we have income data for this position that is greater than or equal to a least a year old, as is necessary
// for accurate calculation of both 1) dividend distribution frequency, and 2) dividend payout months values.
// Back-dated info is always from the 1st of the calculated year ago month.
int olderThanOneYearRevenueCount = joinedPositionIncome
.Where(pi => pi.TickerSymbol == currentTicker &&
pi.DateRecvd <= DateTime.Now.AddMonths(-12).AddDays(-DateTime.Now.Day)).Count();
if (olderThanOneYearRevenueCount > 0)
{
Data.Entities.Profile updateProfileModel = new Data.Entities.Profile
{
TickerSymbol = currentTicker
};
IQueryable <PositionIncomeVm> withinLastYearData = joinedPositionIncome
.Where(pi => pi.TickerSymbol == currentTicker &&
pi.DateRecvd >= DateTime.Now.AddMonths(-12).AddDays(-DateTime.Now.Day))
.AsQueryable();
// Investor may have same position in multiple accounts, e.g. IRA, Roth-IRA.
int dupAcctCount = withinLastYearData.Select(pi => pi.Account).Distinct().Count();
if (dupAcctCount > 1)
{
IQueryable <PositionIncomeVm> withUniqueAcct = withinLastYearData
.Where(pi => pi.Account == withinLastYearData.First().Account).OrderBy(pi => pi.TickerSymbol);
updateProfileModel.DividendFreq = CalculateDividendFrequency(withUniqueAcct);
updateProfileModel.DividendMonths = updateProfileModel.DividendFreq != "M" ? CalculateDividendMonths(withUniqueAcct) : "N/A";
updateProfileModel.DividendPayDay = CommonSvc.CalculateMedianValue(BuildDivDaysList((withUniqueAcct)));
}
else
{
updateProfileModel.DividendFreq = CalculateDividendFrequency(withinLastYearData);
updateProfileModel.DividendMonths = updateProfileModel.DividendFreq != "M" ? CalculateDividendMonths(withinLastYearData) : "N/A";
updateProfileModel.DividendPayDay = CommonSvc.CalculateMedianValue(BuildDivDaysList((withinLastYearData)));
}
// Satisfy 'required' Profile attributes & ProfileId for updating.
updateProfileModel.ProfileId = joinedPositionIncome.Where(d => d.TickerSymbol == currentTicker).First().ProfileId;
updateProfileModel.DividendYield = joinedPositionIncome.Where(d => d.TickerSymbol == currentTicker).First().DividendYield;
updateProfileModel.TickerDescription = joinedPositionIncome.Where(d => d.TickerSymbol == currentTicker).First().TickerDescription;
updateProfileModel.LastUpdate = DateTime.Now;
updateProfileModelList.Add(updateProfileModel);
}
else
{
exceptionTickers += currentTicker + ", ";
}
}
if (exceptionTickers.Length > 0)
{
exceptionTickers.Trim();
exceptionTickers = exceptionTickers.Remove(exceptionTickers.LastIndexOf(','), 1);
}
vm.BatchProfilesList = updateProfileModelList;
vm.ExceptionTickerSymbols = (exceptionTickers.Length > 0 ? exceptionTickers : "");
vm.UpdateHasErrors = false;
}
catch (Exception)
{
vm.UpdateHasErrors = true;
}
return(vm);
}