public TransactionReportModel GenerateTransactionReport(int day)
{
TransactionReportModel result = new TransactionReportModel();
result.Transactions = TransactionService.GetAllByDay(day);
return(result);
}
public static TransactionReportModel createTransRepObject(dynamic xdata)
{
TransactionReportModel tempData = new TransactionReportModel();
tempData.transId = xdata.TransID;
tempData.userName = xdata.UserName;
tempData.transDate = xdata.Date;
tempData.productName = xdata.ProductName;
tempData.quantity = xdata.Quantity;
tempData.subTotal = xdata.ProductPrice * xdata.Quantity;
return(tempData);
}
private List<TransactionReportModel> CreateQuery(DateTime startDate, DateTime endDate, int currencyId)
{
IEnumerable<Payment> Receipts;
IEnumerable<Payment> Disbursements;
var today = DateTime.Today;
//var tommorrow = DateTime.Today.AddDays(1);
//var yesterday = DateTime.Today.AddDays(-1);
//closed Cash on vaults
//var closedCashOnVaultToday = ObjectContext.CashOnVaults.SingleOrDefault(entity => entity.TransitionDateTime >= today && entity.TransitionDateTime <= tommorrow && entity.CurrencyId == currencyId);
//var closedCashOnVaultYesterday = ObjectContext.CashOnVaults.SingleOrDefault(entity => entity.TransitionDateTime >= yesterday && entity.TransitionDateTime <= today && entity.CurrencyId == currencyId);
//if (closedCashOnVaultYesterday != null)
//{
// startDate = closedCashOnVaultYesterday.TransitionDateTime;
//}
//if (closedCashOnVaultToday != null)
//{
// endDate = closedCashOnVaultToday.TransitionDateTime;
//}
#region Receipts Region
if (currencyId == Currency.GetCurrencyBySymbol("PHP").Id)
{
var allPayments = from pymnt in ObjectContext.Payments
join rpa in ObjectContext.ReceiptPaymentAssocs on pymnt.Id equals rpa.PaymentId
join rc in ObjectContext.Receipts on rpa.ReceiptId equals rc.Id
where pymnt.EntryDate >= startDate && pymnt.EntryDate <= endDate
&& pymnt.PaymentTypeId == PaymentType.Receipt.Id
&& pymnt.ParentPaymentId == null
select pymnt;
var foreignPayments = from pymnt in ObjectContext.Payments
join rpa in ObjectContext.ReceiptPaymentAssocs on pymnt.Id equals rpa.PaymentId
join rc in ObjectContext.Receipts on rpa.ReceiptId equals rc.Id
join pca in ObjectContext.PaymentCurrencyAssocs on pymnt.Id equals pca.PaymentId
where pymnt.EntryDate >= startDate && pymnt.EntryDate <= endDate
&& pymnt.PaymentTypeId == PaymentType.Receipt.Id
&& pymnt.ParentPaymentId == null
select pymnt;
//Payments in Peso
Receipts = allPayments.Except(foreignPayments);
}
else
{
//DAPAT CASH RANI
Receipts = from pymnt in ObjectContext.Payments
join rpa in ObjectContext.ReceiptPaymentAssocs on pymnt.Id equals rpa.PaymentId
join rc in ObjectContext.Receipts on rpa.ReceiptId equals rc.Id
join pca in ObjectContext.PaymentCurrencyAssocs on pymnt.Id equals pca.PaymentId
where pymnt.EntryDate >= startDate.Date && pymnt.EntryDate <= endDate.Date
&& pymnt.PaymentTypeId == PaymentType.Receipt.Id
&& pymnt.ParentPaymentId == null
&& pca.CurrencyId == currencyId
select pymnt;
}
var cancelledpostdatedcheques = from chq in ObjectContext.Cheques
join pymnt in ObjectContext.Payments.Where(entity => entity.PaymentTypeId == PaymentType.Receipt.Id)
on chq.PaymentId equals pymnt.Id
join chqassoc in ObjectContext.ChequeApplicationAssocs on chq.Id equals chqassoc.ChequeId
join las in ObjectContext.LoanApplicationStatus on chqassoc.ApplicationId equals las.ApplicationId
where las.IsActive == true && (las.LoanApplicationStatusType.Id
== LoanApplicationStatusType.RejectedType.Id
|| las.LoanApplicationStatusType.Id == LoanApplicationStatusType.CancelledType.Id)
select pymnt;
Receipts = Receipts.Except(cancelledpostdatedcheques);
var receiptPayments = from pymnt in Receipts
where pymnt.PaymentMethodTypeId == PaymentMethodType.CashType.Id
select new TransactionReportModel()
{
Payment = pymnt,
ForExDetail = null,
COVTransaction = null,
_PaymentsCash = pymnt.TotalAmount,
_PaymentsCheck = 0,
_ReleasedCash = 0,
_ReleasedCheck = 0,
Remarks= string.Empty
};
var receiptPaymentsCheck = from pymnt in Receipts
where (pymnt.PaymentMethodTypeId == PaymentMethodType.PersonalCheckType.Id || pymnt.PaymentMethodTypeId == PaymentMethodType.PayCheckType.Id)
select new TransactionReportModel()
{
Payment = pymnt,
ForExDetail = null,
COVTransaction = null,
_PaymentsCash = 0,
_PaymentsCheck = pymnt.TotalAmount,
_ReleasedCash = 0,
_ReleasedCheck = 0,
Remarks = string.Empty
};
#endregion
#region Disbursements Region
if (currencyId == Currency.GetCurrencyBySymbol("PHP").Id)
{
var allDisbursements = from pymnt in ObjectContext.Payments
join dis in ObjectContext.Disbursements on pymnt.Id equals dis.PaymentId
where pymnt.EntryDate >= startDate && pymnt.EntryDate <= endDate
&& pymnt.ParentPaymentId != null
&& pymnt.PaymentTypeId == PaymentType.Disbursement.Id
select pymnt;
var foreignDisbursements = from pymnt in ObjectContext.Payments
join pca in ObjectContext.PaymentCurrencyAssocs on pymnt.Id equals pca.PaymentId
join dis in ObjectContext.Disbursements on pymnt.Id equals dis.PaymentId
where pymnt.EntryDate >= startDate && pymnt.EntryDate <= endDate
&& pymnt.ParentPaymentId != null
&& pymnt.PaymentTypeId == PaymentType.Disbursement.Id
select pymnt;
Disbursements = allDisbursements.Except(foreignDisbursements);
}
else
{
//DAPAT CASH RANI
Disbursements = from pymnt in ObjectContext.Payments
join pca in ObjectContext.PaymentCurrencyAssocs on pymnt.Id equals pca.PaymentId
join dis in ObjectContext.Disbursements on pymnt.Id equals dis.PaymentId
where pymnt.EntryDate >= startDate.Date && pymnt.EntryDate <= endDate.Date
&& pymnt.ParentPaymentId != null
&& pca.CurrencyId == currencyId
&& pymnt.PaymentTypeId == PaymentType.Disbursement.Id
select pymnt;
}
var disbursementPayments = from pymnt in Disbursements
where pymnt.PaymentMethodTypeId == PaymentMethodType.CashType.Id
select new TransactionReportModel()
{
Payment = pymnt,
ForExDetail = null,
COVTransaction = null,
_PaymentsCash = 0,
_PaymentsCheck = 0,
_ReleasedCash = pymnt.TotalAmount,
_ReleasedCheck = 0,
Remarks= string.Empty
};
var disbursementPaymentsCheck = from pymnt in Disbursements
where (pymnt.PaymentMethodTypeId == PaymentMethodType.PersonalCheckType.Id || pymnt.PaymentMethodTypeId == PaymentMethodType.PayCheckType.Id)
select new TransactionReportModel()
{
Payment = pymnt,
ForExDetail = null,
COVTransaction = null,
_PaymentsCash = 0,
_PaymentsCheck = 0,
_ReleasedCash = 0,
_ReleasedCheck = pymnt.TotalAmount,
Remarks = string.Empty
};
#endregion
#region Cash On Vault
// cash on vault transactions
var covDeposited = from covt in ObjectContext.COVTransactions
where covt.CurrencyId == currencyId
&& covt.EntryDate >= startDate && covt.EntryDate <= endDate
&& covt.COVTransTypeId == COVTransactionType.DepositToVaultType.Id
select new TransactionReportModel()
{
Payment = null,
ForExDetail = null,
COVTransaction = covt,
_PaymentsCash = covt.Amount,
_PaymentsCheck = 0,
_ReleasedCash = 0,
_ReleasedCheck = 0,
Remarks = covt.Remarks
};
var covWithdrawn = from covt in ObjectContext.COVTransactions
where covt.CurrencyId == currencyId
&& covt.EntryDate >= startDate && covt.EntryDate <= endDate
&& covt.COVTransTypeId == COVTransactionType.WithdrawFromVaultType.Id
select new TransactionReportModel()
{
Payment = null,
ForExDetail = null,
COVTransaction = covt,
_PaymentsCash = 0,
_PaymentsCheck = 0,
_ReleasedCash = covt.Amount,
_ReleasedCheck = 0,
Remarks = covt.Remarks
};
#endregion
#region Foreign Exchange Transactions
//foreign exchange
var forExCashReceived = from fed in ObjectContext.ForExDetails
join fda in ObjectContext.ForeignExchangeDetailAssocs on fed.Id equals fda.ForExDetailId
join fe in ObjectContext.ForeignExchanges on fda.ForExId equals fe.Id
where fed.ParentForExDetailId == null && fed.PaymentMethodTypeId == PaymentMethodType.CashType.Id
&& fe.EntryDate >= startDate && fe.EntryDate <= endDate
&& fed.CurrencyId == currencyId
select new TransactionReportModel()
{
Payment = null,
ForExDetail = fed,
COVTransaction = null,
_PaymentsCash = fed.Amount,
_PaymentsCheck = 0,
_ReleasedCash = 0,
_ReleasedCheck = 0,
Remarks = string.Empty
};
var forExCashReleased = from fed in ObjectContext.ForExDetails
join fda in ObjectContext.ForeignExchangeDetailAssocs on fed.ParentForExDetailId equals fda.ForExDetailId
join fe in ObjectContext.ForeignExchanges on fda.ForExId equals fe.Id
where fed.ParentForExDetailId != null && fed.PaymentMethodTypeId == PaymentMethodType.CashType.Id
&& fe.EntryDate >= startDate && fe.EntryDate <= endDate
&& fed.CurrencyId == currencyId
select new TransactionReportModel()
{
Payment = null,
ForExDetail = fed,
COVTransaction = null,
_PaymentsCash = 0,
_PaymentsCheck = 0,
_ReleasedCash = fed.Amount,
_ReleasedCheck = 0,
Remarks = string.Empty
};
var forExCheckReleased = from fed in ObjectContext.ForExDetails
join fda in ObjectContext.ForeignExchangeDetailAssocs on fed.ParentForExDetailId equals fda.ForExDetailId
join fe in ObjectContext.ForeignExchanges on fda.ForExId equals fe.Id
where fed.ParentForExDetailId != null && fed.PaymentMethodTypeId == PaymentMethodType.PersonalCheckType.Id
&& fe.EntryDate >= startDate && fe.EntryDate <= endDate
&& fed.CurrencyId == currencyId
select new TransactionReportModel()
{
Payment = null,
ForExDetail = fed,
COVTransaction = null,
_PaymentsCash = 0,
_PaymentsCheck = 0,
_ReleasedCash = 0,
_ReleasedCheck = fed.Amount,
Remarks = string.Empty
};
#endregion
List<TransactionReportModel> transactions = new List<TransactionReportModel>();
decimal vaultamount = 0;
var cashOnVault = from c in ObjectContext.CashOnVaults
where c.TransitionDateTime < today
&& c.CurrencyId == currencyId
orderby c.TransitionDateTime descending
select c;
TransactionReportModel vault = new TransactionReportModel();
if (cashOnVault.Count() != 0)
{
vault.CashOnVault = cashOnVault.FirstOrDefault().Amount;
vault.Payment = null;
vault.ForExDetail = null;
vault.COVTransaction = null;
vault._PaymentsCash = 0;
vault._PaymentsCheck = 0;
vault._ReleasedCash = 0;
vault._ReleasedCheck = 0;
vaultamount = cashOnVault.FirstOrDefault().Amount;
}
transactions.Add(vault);
var receivedAndReleased = receiptPayments.Concat(receiptPaymentsCheck).Concat(disbursementPayments).Concat(disbursementPaymentsCheck).Concat(forExCashReceived).Concat(forExCashReleased).Concat(forExCheckReleased)
.Concat(covDeposited).Concat(covWithdrawn).ToList();
transactions.AddRange(receivedAndReleased);
var DRCash = transactions.Sum(entity => entity._PaymentsCash);
var CRCash = transactions.Sum(entity => entity._ReleasedCash);
var DRCheck = transactions.Sum(entity => entity._PaymentsCheck);
var CRCheck = transactions.Sum(entity => entity._ReleasedCheck);
var TotalCash = vaultamount + (DRCash - CRCash);
var TotalCheck = DRCheck - CRCheck;
txtDRCash.Text = DRCash.ToString("N");
txtCRCash.Text = CRCash.ToString("N");
txtDRCheck.Text = DRCheck.ToString("N");
txtCRCheck.Text = CRCheck.ToString("N");
txtTotalCash.Text = TotalCash.ToString("N");
txtTotalCheck.Text = TotalCheck.ToString("N");
return transactions;
}