public TransactionReportModel GenerateTransactionReport(int day)
        {
            TransactionReportModel result = new TransactionReportModel();

            result.Transactions = TransactionService.GetAllByDay(day);
            return(result);
        }
示例#2
0
        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;
        }