public List <AccountDefintionStatement> GetAccountDefinitionStatements(ILogger logger, string sheetName)
{
using (ExcelReader reader = new ExcelReader(_inputFile, sheetName, logger))
{
SheetHeadingVerifier.VerifyHeadingNames(logger, reader, _headings);
var accountDefintionStatements = reader.ReadAllLines(1, (r) =>
{
var isRecipientAccountAvailable = r.IsValueAvailable(RecipientAccount);
var accountTypeString = r.ReadString(Type);
if (!Enum.TryParse <AccountType>(accountTypeString, true, out var accountType))
{
accountType = AccountType.Equity;
logger.Log(MessageType.Warning, $"In file{r.FileName}, ",
$"in sheet{r.SheetName}, ",
$"in line no. {r.LineNumber}, ",
"could not read the account type. Taken it as Equity");
}
return(new AccountDefintionStatement()
{
AccountType = accountType,
Account = r.ReadString(Account),
RecipientAccount = isRecipientAccountAvailable? r.ReadString(RecipientAccount): string.Empty,
});
}).ToList();
return(accountDefintionStatements);
}
}
public List <JournalStatement> GetJournalStatements(ILogger logger, string sheetName)
{
using (ExcelReader reader = new ExcelReader(_inputFile, sheetName, logger))
{
SheetHeadingVerifier.VerifyHeadingNames(logger, reader, _headings);
var journalStatements = reader.ReadAllLines(1, (r) =>
{
if (!r.IsValueAvailable(SerialNumber))
{
return(Tuple.Create(new JournalStatement(), false));
}
var isCreditAvailable = r.IsValueAvailable(Credit);
var credit = isCreditAvailable ? r.ReadDouble(Credit) : 0;
var isDebitAvailable = r.IsValueAvailable(Debit);
var debit = isDebitAvailable ? r.ReadDouble(Debit) : 0;
if (isCreditAvailable && isDebitAvailable)
{
if (!credit.IsZero() && !debit.IsZero())
{
logger.Log(MessageType.Warning, $"In file {r.FileName}, ",
$"in sheet {r.SheetName}, ",
$"in line no. {r.LineNumber}, ",
"both credit and debit is having non zero values. Taking the difference as value");
}
}
if (!isCreditAvailable && !isDebitAvailable)
{
logger.Log(MessageType.Warning, $"In file {r.FileName}, ",
$"in sheet {r.SheetName}, ",
$"in line no. {r.LineNumber}, ",
"both credit and debit is not mentioned. Taking the value as 0");
}
var journalStatement = new JournalStatement()
{
Date = r.ReadDate(Date),
Account = r.ReadString(LedgerName),
Tag = r.ReadString(Tag),
Description = r.ReadString(Description),
Value = credit - debit,
};
return(Tuple.Create(journalStatement, true));
}).ToList();
return(journalStatements.Where(x => x.Item2).Select(x => x.Item1).ToList());
}
}
public List <AdjustedTradeStatement> GetTradeStatements(ILogger logger, string sheetName)
{
using (ExcelReader reader = new ExcelReader(_inputFile, sheetName, logger))
{
SheetHeadingVerifier.VerifyHeadingNames(logger, reader, _headings);
var tradeStatements = reader.ReadAllLines(1, (r) =>
{
if (!r.IsValueAvailable(SerialNumber))
{
return(Tuple.Create(new AdjustedTradeStatement(), false));
}
var isSaleAvailable = r.IsValueAvailable(Sale);
var sale = isSaleAvailable ? r.ReadDouble(Sale) : 0;
var isCostAvailable = r.IsValueAvailable(Cost);
var cost = isCostAvailable ? r.ReadDouble(Cost) : 0;
var reason = string.Empty;
var value = sale - cost;
if (isSaleAvailable && isCostAvailable)
{
if (!sale.IsZero() && !cost.IsZero())
{
if (sale > cost)
{
reason = $"Both Sale(={sale}) and Cost(={cost}) has value. Setting Sale as {value}.";
}
else
{
reason = $"Both Sale(={sale}) and Cost(={cost}) has value. Setting Cost as {value * -1 }.";
}
}
}
var tradeStatement = new AdjustedTradeStatement()
{
Date = r.ReadDate(Date),
Name = r.ReadString(Name),
TransactionTax = r.ReadString(TransactionTax),
TransactionDetail = r.ReadString(TransactionDetail),
Quanity = r.ReadDouble(Quantity),
Value = value,
Reason = reason,
};
return(Tuple.Create(tradeStatement, true));
}).ToList();
return(tradeStatements.Where(x => x.Item2).Select(x => x.Item1).ToList());
}
}
public List <BalanceSheetStatement> GetBalanceSheet(ILogger logger, string sheetName)
{
using (ExcelReader reader = new ExcelReader(_excelFileName, sheetName, logger))
{
SheetHeadingVerifier.VerifyHeadingNames(logger, reader, headings);
var balanceSheetStatements = reader.ReadAllLines(1, (r) =>
{
var isValid = r.IsValueAvailable(SerialNumber);
if (!isValid)
{
return(Tuple.Create(new BalanceSheetStatement(), false));
}
var isCreditAvailable = r.IsValueAvailable(Credit);
var credit = isCreditAvailable ? r.ReadDouble(Credit) : 0;
var isDebitAvailable = r.IsValueAvailable(Debit);
var debit = isDebitAvailable ? r.ReadDouble(Debit) : 0;
if (!credit.IsZero() && !debit.IsZero())
{
logger.Log(MessageType.Warning, $"In file {r.FileName}, ",
$"in sheet {r.SheetName}, ",
$"in line no. {r.LineNumber}, ",
"both credit and debit is mentioned. Taking the difference as value");
}
if (!isCreditAvailable && !isDebitAvailable)
{
logger.Log(MessageType.Warning, $"In file {r.FileName}, ",
$"in sheet {r.SheetName}, ",
$"in line no. {r.LineNumber}, ",
"both credit and debit is not mentioned. Taking the value as 0");
}
return(Tuple.Create(new BalanceSheetStatement()
{
Account = r.ReadString(Ledger),
Value = credit - debit,
}, true));
}).ToList();
return(balanceSheetStatements.Where(x => x.Item2).Select(y => y.Item1).ToList());
}
}