public ActionResult BankTransactionList(DataTablesViewModel param, string fromDate, string toDate)
{
try
{
if (!PermissionControl.CheckPermission(UserAppPermissions.BankTransaction_View))
{
return(RedirectToAction("Restricted", "Home"));
}
DateTime frdate = DateTime.Now;
if (!string.IsNullOrWhiteSpace(fromDate))
{
frdate = DateTime.Parse(fromDate);
}
DateTime tdate = DateTime.Now;
if (!string.IsNullOrWhiteSpace(toDate))
{
tdate = DateTime.Parse(toDate);
}
logger.DebugFormat("Getting Bank Trasaction List with From Date [{0}] and To Date [{1}]", frdate.ToShortDateString(), tdate.ToShortDateString());
CompanyBankTransactionVM transaction = new CompanyBankTransactionVM();
transaction.DTObject = param;
var list = bankTransactionManagement.GetAllBankTransactionByParam(transaction, frdate, tdate);
logger.DebugFormat("Successfully Retrieve Bank Trasaction List Records [{2}] with From Date [{0}] and To Ddate [{1}]", frdate.ToShortDateString(), tdate.ToShortDateString(), list.Count());
return(Json(new
{
sEcho = param.draw,
iTotalRecords = list.Select(i => i.DTObject.TotalRecordsCount).FirstOrDefault(),
iTotalDisplayRecords = list.Select(i => i.DTObject.TotalRecordsCount).FirstOrDefault(), // Filtered Count
aaData = list
}));
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
public IEnumerable <CompanyBankTransactionVM> GetAllBankTransactionByParam(CompanyBankTransactionVM param, DateTime fromDate, DateTime toDate)
{
try
{
IQueryable <CompanyBankTransactionVM> query = (from CBT in DbContext.CompanyBankTransactions.AsNoTracking()
join CB in DbContext.CompanyBanks.AsNoTracking() on CBT.CompanyBankID equals CB.CompanyBankID
join Comp in DbContext.Companies.AsNoTracking() on CB.CompanyID equals Comp.CompanyID
join Bnk in DbContext.Banks.AsNoTracking() on CB.BankID equals Bnk.BankID
where !CBT.IsDeleted
orderby CBT.TransactionDate ascending
where (DbFunctions.TruncateTime(CBT.TransactionDate) >= DbFunctions.TruncateTime(fromDate) && DbFunctions.TruncateTime(CBT.TransactionDate) <= DbFunctions.TruncateTime(toDate))
select new CompanyBankTransactionVM
{
CompanyBankTransactionID = CBT.CompanyBankTransactionID,
Description = CBT.Description,
Amount = CBT.Amount,
TransactionType = CBT.TransactionType,
TransactionDate = CBT.TransactionDate,
TransactionStatus = CBT.TransactionStatus,
PaymentType = CBT.PaymentType,
CategoryType = CBT.CategoryType,
ReceiptNumber = CBT.ReceiptNumber,
AccountNumber = CB.AccountNumber,
CompanyName = Comp.Name,
BankName = Bnk.Name,
});
query = GetBankTransactionFiltersOrderQuery(query, param);
int totalRecord = query.Count();
var transaction = query.Skip(param.DTObject.start).Take(param.DTObject.length).ToList().Select(index => new CompanyBankTransactionVM
{
CompanyBankTransactionID = index.CompanyBankTransactionID,
CompanyName = index.CompanyName,
BankName = index.BankName,
Description = index.Description,
Amount = index.Amount,
TransactionType = index.TransactionType,
TransactionDate = index.TransactionDate,
PaymentType = index.PaymentType,
CategoryType = index.CategoryType,
ReceiptNumber = index.ReceiptNumber,
AccountNumber = index.AccountNumber,
TransactionStatus = index.TransactionStatus,
DTObject = new DataTablesViewModel()
{
TotalRecordsCount = totalRecord
}
}).ToList();
return(transaction);
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
private IQueryable <CompanyBankTransactionVM> GetBankTransactionFiltersOrderQuery(IQueryable <CompanyBankTransactionVM> query, CompanyBankTransactionVM param, bool forAll = false)
{
try
{
if (param == null)
{
return(query);
}
int index = -1;
foreach (var columnData in param.DTObject.columns)
{
index += 1;
if (columnData.orderable)
{
foreach (var row in param.DTObject.order.Where(i => i.column == index))
{
if (row.dir == "asc")
{
query = query.OrderBy(columnData.data);
}
else
{
query = query.OrderByDescending(columnData.data);
}
}
}
if (columnData.search.value == null || string.IsNullOrEmpty(columnData.search.value.Trim()))
{
continue;
}
switch (columnData.data)
{
case "Description":
if (!forAll)
{
query = query.Where(col => col.Description.ToUpper().Contains(columnData.search.value.ToUpper()));
}
break;
}
}
if (param.DTObject.search.value != null && !string.IsNullOrEmpty(param.DTObject.search.value))
{
query = query.Where(col => (
col.Description.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.TransactionType.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.PaymentType.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.CategoryType.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.ReceiptNumber.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.AccountNumber.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.Amount.ToString().Contains(param.DTObject.search.value.ToUpper())));
}
return(query);
return(query);
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
public IEnumerable <CompanyBankTransactionVM> GetAllBankTransactionByParam(CompanyBankTransactionVM transaction, DateTime frdate, DateTime tdate)
{
return(bankTransactionRepository.GetAllBankTransactionByParam(transaction, frdate, tdate));
}
