Employee Loan Data Entry and Automate to Payroll Deduction

Most companies provide employee's loans for SSS and Pagibig member, Bank loan, Company salary loan or cash advance etc. Pinoy Web Application create a flexible loan data entry module that can connect to payroll process to deduct automatic to their salary for every pay period, until the loan is zero balance. The employee with salary loans or other deduction can also monitor the history of salary deduction to their dashboard account login.

Select Branch/Division

List of Employee Loans

Loan Data Entry Form

Loan Details and Deduction History

C# Code how to get employee total loan deduction

        //-Getting the Total Loan Deduction
        //=================================
         total_loan_deduction = LoanDeduction(Emp_ID, PeriodFrom, PeriodTo, PayPeriod, bcode, deptID);
        
        //-Process all kind of Loans
        //=================================
        decimal LoanDeduction(string EmployeeID, DateTime PeriodFrom, DateTime PeriodTo, DateTime PayPeriod, string BranchID, string DeptID)
        {
            var loan = db.Payroll_Emp_Loan_Main.Where(l => l.emp_id == EmployeeID && l.pymt_complete == "N").ToList();
            if (loan.Count > 0)
            {
                foreach(var item in loan)
                {
                    SaveLoans(EmployeeID, PeriodFrom, PeriodTo, item.loan_code, item.loan_type, (decimal)item.prev_loan_bal, (decimal)item.ded_amt, item.date_issued, PayPeriod, BranchID, DeptID);
                    CloseLoanDeduction(EmployeeID, item.loan_code, item.loan_type, item.date_issued, (decimal)item.prev_loan_bal);
                }
                return db.Payroll_Emp_Loan_Dtl.Where(a => a.emp_id == EmployeeID && a.period_from == PeriodFrom && a.period_to == PeriodTo).Sum(x => x.amt);
            }
            else
            {
                return 0;
            }
        }
        //-Verify the Loan if Zero balance then Closed the deduction
        //=================================
        public void CloseLoanDeduction(string EmployeeID, string LoanCode, string LoanType, DateTime DateLoanIssue, decimal LoanStartingBalance)
        {
            var TotalLoanDeduction = db.Payroll_Emp_Loan_Dtl.Where(a => a.emp_id == EmployeeID && a.loan_code == LoanCode && a.loan_type == LoanType && a.date_issued == DateLoanIssue).Sum(x => x.amt);
            if(LoanStartingBalance <= TotalLoanDeduction)
            {
                var loanHeader = db.Payroll_Emp_Loan_Main.Where(a => a.emp_id == EmployeeID && a.loan_code == LoanCode && a.loan_type == LoanType && a.date_issued == DateLoanIssue).FirstOrDefault();
                loanHeader.pymt_complete = "Y";
                db.Entry(loanHeader).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
            }
        }
        //-Save Loan Deduction
        //=================================
        public void SaveLoans(string EmployeeID, DateTime PeriodFrom, DateTime PeriodTo, string LoanCode, string LoanType, decimal LoanStartingBalance, decimal DeductAmount, DateTime DateLoanIssue, DateTime PayPeriod, string BranchID, string DeptID)
        {
            Payroll_Emp_Loan_Dtl loan = new Payroll_Emp_Loan_Dtl
            {
                emp_id = EmployeeID,
                loan_code = LoanCode,
                loan_type = LoanType,
                tran_date = DateTime.Now,
                month = null,
                period_id = null,
                year = null,
                amt = Loan_Deduction_Amount(EmployeeID, LoanCode, LoanType, DateLoanIssue, LoanStartingBalance, DeductAmount),
                date_issued = DateLoanIssue,
                period_from = PeriodFrom,
                period_to = PeriodTo,
                pay_period = PayPeriod,
                branch_id = BranchID,
                dept_id = DeptID
            };
            if (Convert.ToDouble(db.Payroll_Emp_Loan_Dtl.Where(x => x.emp_id == EmployeeID && x.loan_code == LoanCode && x.loan_type == LoanType && DbFunctions.TruncateTime(x.date_issued) == DateLoanIssue && DbFunctions.TruncateTime(x.period_from) == PeriodFrom && DbFunctions.TruncateTime(x.period_to) == PeriodTo).Count()) > 0)
            {
                if(loan.amt != 0)
                {
                    var loans = db.Payroll_Emp_Loan_Dtl.Where(x => x.emp_id == EmployeeID && x.loan_code == LoanCode && x.loan_type == LoanType && DbFunctions.TruncateTime(x.date_issued) == DateLoanIssue && DbFunctions.TruncateTime(x.period_from) == PeriodFrom && DbFunctions.TruncateTime(x.period_to) == PeriodTo).FirstOrDefault();
                    loans.amt = loan.amt;
                    db.Entry(loans).State = System.Data.Entity.EntityState.Modified;
                    db.SaveChanges();
                }
            }
            else
            {
                if(loan.amt != 0)
                {
                    db.Payroll_Emp_Loan_Dtl.Add(loan);
                    db.SaveChanges();
                }
            }
        }
        //-Verify the Remaining Loan Balance
        //=================================
        decimal Loan_Deduction_Amount(string EmpID, string LoanCode, string LoanType, DateTime DateLoanIssue, decimal LoanStartBalance, decimal DeductAmount)
        {
            if (Convert.ToDouble(db.Payroll_Emp_Loan_Dtl.Where(x => x.emp_id == EmpID && x.loan_code == LoanCode && x.loan_type == LoanType && DbFunctions.TruncateTime(x.date_issued) == DateLoanIssue).Count()) > 0)
            {
                var TotalLoanDeduction = db.Payroll_Emp_Loan_Dtl.Where(a => a.emp_id == EmpID && a.loan_code == LoanCode && a.loan_type == LoanType && a.date_issued == DateLoanIssue).Sum(x => x.amt);
                if (LoanStartBalance >= TotalLoanDeduction)
                {
                    return DeductAmount;
                }
                else
                {
                    if (LoanStartBalance - TotalLoanDeduction <= 0)
                    {
                        return 0;
                    }
                    else
                    {
                        return LoanStartBalance - TotalLoanDeduction;
                    }
                }
            }
            else
            {
                return DeductAmount;
            }
        }