private static TblBankStatDetail PrepareDetail(ccnewEntities entities, TblBankStatHeader header,
ImportedBankStatement transaction, List <string> errors)
{
string recInfo = string.Format(
"Doc Date:{0}, Transaction Type:{1}, Amount:{2}, ChequeNo:{3}, DepositNo:{4}, DepositNo:{5}"
, transaction.DocDate, transaction.TransactionType, transaction.Amount, transaction.ChequeNo, transaction.DepositNo, transaction.Description);
var BankTransactionType = entities.TblBankTransactionTypes.Where(i => i.Code == transaction.TransactionType);
if (BankTransactionType.Count() != 1)
{
if (BankTransactionType.Count() == 0)
{
errors.Add(string.Format("{1} -->> Bank transaction type not Found. More info -->> {0}",
recInfo, DateTime.Now));
}
else
{
errors.Add(string.Format("{1} -->> found more than one bank transaction type . More info -->> {0}",
recInfo, DateTime.Now));
}
return(null);
}
var detail = new TblBankStatDetail()
{
TblBankStatHeader = header.Iserial,
TblBankTransactionType = BankTransactionType.FirstOrDefault().Iserial,
DocDate = transaction.DocDate,
Description = transaction.Description,
Amount = transaction.Amount,
ChequeNo = transaction.ChequeNo,
DepositNo = transaction.DepositNo,
};
return(detail);
}
private static void FillList(List <ImportedBankStatement> importedList, Excel._Worksheet sheet, int startRow)// ExcelWorksheet sheet
{
int docDateIndex = 27;
int transactionTypeIndex = 28;
int descriptionIndex = 29;
int amountIndex = 30;
int chequeNoIndex = 31;
int depositNoIndex = 32;
for (int i = startRow; i < sheet.UsedRange.Rows.Count; i++)// // sheet.Cells.End.Row
{
try
{
var newemp = new ImportedBankStatement();
//sheet.Cells[i, docDateIndex].Calculate();
if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, docDateIndex]).Text))
//if (!string.IsNullOrWhiteSpace(sheet.Cells[i, docDateIndex].Text))
{
var docDate = ((Excel.Range)sheet.Cells[i, docDateIndex]).Text.ToUpper().Trim();
//var docDate = sheet.Cells[i, docDateIndex].Text.ToUpper().Trim();//.Replace("/", "").Replace("-", "");
//newemp.DocDate = DateTime.ParseExact(docDate, "dd-mm-yyyy",
// System.Globalization.CultureInfo.InvariantCulture,
// System.Globalization.DateTimeStyles.None);
if (((string)docDate).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries).Length == 3)
{
var y = ((Excel.Range)sheet.Cells[i, 3]).Text.ToUpper().Trim(); // sheet.Cells[i, 3].Text.ToUpper().Trim();
var m = ((Excel.Range)sheet.Cells[i, 2]).Text.ToUpper().Trim(); // sheet.Cells[i, 2].Text.ToUpper().Trim();
var d = ((Excel.Range)sheet.Cells[i, 1]).Text.ToUpper().Trim(); // sheet.Cells[i, 1].Text.ToUpper().Trim();
newemp.DocDate = new DateTime(int.Parse(y), int.Parse(m), int.Parse(d));
}
}
//sheet.Cells[i, transactionTypeIndex].Calculate();
if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, transactionTypeIndex]).Text))
{
var transactionType = //sheet.Cells[i, transactionTypeIndex].Text.ToUpper().Trim();
((Excel.Range)sheet.Cells[i, transactionTypeIndex]).Text.ToUpper().Trim();
newemp.TransactionType = transactionType;
}
//sheet.Cells[i, descriptionIndex].Calculate();
if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, descriptionIndex]).Text))
{
var description = //sheet.Cells[i, descriptionIndex].Text.ToUpper().Trim();
((Excel.Range)sheet.Cells[i, descriptionIndex]).Text.ToUpper().Trim();
newemp.Description = description;
}
//sheet.Cells[i, amountIndex].Calculate();
if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, amountIndex]).Text))
{
var amount = //sheet.Cells[i, amountIndex].Text.ToUpper().Trim();
((Excel.Range)sheet.Cells[i, amountIndex]).Text.ToUpper().Trim();
newemp.Amount = Convert.ToDecimal(amount);
}
//sheet.Cells[i, chequeNoIndex].Calculate();
if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, chequeNoIndex]).Text))
{
var chequeNo = //sheet.Cells[i, chequeNoIndex].Text.ToUpper().Trim();
((Excel.Range)sheet.Cells[i, chequeNoIndex]).Text.ToUpper().Trim();
if (chequeNo.Trim().Length > 0)
{
newemp.ChequeNo = Convert.ToInt64(chequeNo);
}
}
//sheet.Cells[i, depositNoIndex].Calculate();
if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, depositNoIndex]).Text))
{
var depositNo = //sheet.Cells[i, depositNoIndex].Text.ToUpper().Trim();
((Excel.Range)sheet.Cells[i, depositNoIndex]).Text.ToUpper().Trim();
newemp.DepositNo = depositNo;
}
importedList.Add(newemp);
}
catch (Exception ex) { throw ex; }
}
}
private static void FillList(ObservableCollection <ImportedBankStatement> importedList, Worksheet sheet, ref int docDateIndex, ref int transactionTypeIndex, ref int descriptionIndex, ref int amountIndex, ref int chequeNoIndex, ref int depositNoIndex)
{
for (int j = sheet.Cells.FirstColIndex; j < sheet.Cells.LastColIndex + 1; j++)
{
switch (sheet.Cells[0, j].StringValue.ToLower())
{
case "docdate":
docDateIndex = j;
break;
case "transactiontype":
transactionTypeIndex = j;
break;
case "description":
descriptionIndex = j;
break;
case "amount":
amountIndex = j;
break;
case "chequeno":
chequeNoIndex = j;
break;
case "depositno":
depositNoIndex = j;
break;
}
}
for (int i = sheet.Cells.FirstRowIndex + 1; i < sheet.Cells.LastRowIndex + 1; i++)
{
var newemp = new ImportedBankStatement();
if (sheet.Cells[i, docDateIndex].Value != null)
{
var docDate = sheet.Cells[i, docDateIndex].Value.ToString().ToUpper().Trim();
newemp.DocDate = Convert.ToDateTime(docDate);
}
if (sheet.Cells[i, transactionTypeIndex].Value != null)
{
var transactionType = sheet.Cells[i, transactionTypeIndex].Value.ToString().ToUpper().Trim();
newemp.TransactionType = transactionType;
}
if (sheet.Cells[i, descriptionIndex].Value != null)
{
var description = sheet.Cells[i, descriptionIndex].Value.ToString().ToUpper().Trim();
newemp.Description = description;
}
if (sheet.Cells[i, amountIndex].Value != null)
{
var amount = sheet.Cells[i, amountIndex].Value.ToString().ToUpper().Trim();
newemp.Amount = Convert.ToDecimal(amount);
}
if (sheet.Cells[i, chequeNoIndex].Value != null)
{
var chequeNo = sheet.Cells[i, chequeNoIndex].Value.ToString().ToUpper().Trim();
newemp.ChequeNo = Convert.ToInt64(chequeNo);
}
if (sheet.Cells[i, depositNoIndex].Value != null)
{
var depositNo = sheet.Cells[i, depositNoIndex].Value.ToString().ToUpper().Trim();
newemp.DepositNo = depositNo;
}
importedList.Add(newemp);
}
}
