CEL_Payroll/Payroll.Service/Loan/DA/LoanScheduleDA.cs

using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;


namespace Payroll.Service
{
    #region  LoanScheduleDA

    internal class LoanScheduleDA
    {
        #region  Constructor

        private LoanScheduleDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, LoanSchedule item)
        {
            tc.ExecuteNonQuery("INSERT INTO LoanSchedule(LoanScheduleID, LoanIssueID, ScheduleNo, OpeningBalance,"+
                " INSTALLMENTPRINCIPAL, INSTALLMENTINTEREST, DUEINSTALLMENTDATE, PAYMENTDATE, DAYS, "+
                " CALCULATEDDATE, ACTUALINTEREST, ClosingBalance, PaymentMode, EmployeeID,InterestRate)" +
                " VALUES(%n, %n, %n, %n, %n, %n, %d, %d, %n, %d, %n, %n,%n,%n,%n)", item.ID.Integer, 
                item.LoanIssueID.Integer, item.ScheduleNo, item.OpeningBalance, 
                item.InstallmentPrincipal, item.InstallmentInterest, 
                item.DueInstallmentDate, DataReader.GetNullValue(item.PaymentDate), 
                item.Days, item.CalculatedDate, item.ActualInterest,
                item.ClosingBalance, item.PaymentMode, item.EmployeeID.Integer,item.InterestRate);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, LoanSchedule item)
        {
            tc.ExecuteNonQuery("UPDATE LoanSchedule SET LoanIssueID=%n, ScheduleNo=%n, OpeningBalance=%n, "+
            " InstallmentPrincipal=%n, InstallmentInterest=%n, DueInstallmentDate=%d, PaymentDate=%d, Days=%n,"+
            " CalculatedDate=%d, ActualInterest=%n, ClosingBalance=%n, paymentMode=%n,InterestRate=%n" +
            " WHERE LoanScheduleID=%n", item.LoanIssueID.Integer, item.ScheduleNo, 
            item.OpeningBalance, item.InstallmentPrincipal, item.InstallmentInterest, 
            item.DueInstallmentDate, DataReader.GetNullValue(item.PaymentDate), 
            item.Days, item.CalculatedDate, item.ActualInterest, 
            item.ClosingBalance, item.PaymentMode ,item.InterestRate, item.ID.Integer);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM LoanSchedule");
        }

        internal static IDataReader Get(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanSchedule WHERE LoanScheduleID=%n Order By DUEINSTALLMENTDATE", nID.Integer);
        }
        //internal static IDataReader Get(TransactionContext tc, int EmpID, int LoanID)
        //{
        //    return tc.ExecuteReader("SELECT LoanIssueID,sum(InstallmentPrincipal) as total FROM LoanSchedule WHERE LoanIssueID in(select LOANISSUEID from LOANISSUE where EMPLOYEEID=%n and LoanID=%n) AND PaymentDate is null group by LoanIssueID", EmpID,LoanID);
        //}

        internal static DataSet GetByEmpIDANDLoanID(TransactionContext tc, int EmpID, int LoanID)
        {
            DataSet oLoanAmounts = new DataSet();
            try
            {
                oLoanAmounts = tc.ExecuteDataSet("SELECT LoanIssueID,sum(InstallmentPrincipal) as total FROM LoanSchedule WHERE LoanIssueID in(select LOANISSUEID from LOANISSUE where EMPLOYEEID=%n and LoanID=%n) AND PaymentDate is null group by LoanIssueID", EmpID, LoanID);           

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oLoanAmounts;
        }

        internal static IDataReader GetByIssueID(TransactionContext tc, ID nIssueID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanSchedule WHERE LOANISSUEID=%n Order By DUEINSTALLMENTDATE", nIssueID.Integer);
        }

        internal static IDataReader GetByIssueIDAndMonth(TransactionContext tc, ID nIssueID, DateTime dateTime)
        {
            return tc.ExecuteReader("SELECT * FROM LoanSchedule WHERE LOANISSUEID=%n  AND DUEINSTALLMENTDATE = %d Order By DUEINSTALLMENTDATE", nIssueID.Integer, dateTime);
        }
        internal static DataSet GetScheduleForSalary(TransactionContext tc, DateTime monthDate, ID payrollTypeID)
        {
            DataSet oLoanPayments = new DataSet();
            try
            {
                oLoanPayments = tc.ExecuteDataSet(@"
                            SELECT   li.LOANISSUEID, li.EMPLOYEEID, l.loanid, l.DESCRIPTION, ls.*  FROM LoanSchedule ls,LoanIssue li, LOANBASIC l,Employee e WHERE PaymentDate IS NULL 
                            AND  ls.LOANISSUEID =li.LOANISSUEID AND l.LOANID = li.LOANID  AND DUEINSTALLMENTDATE BETWEEN %d AND %d and e.EmployeeID =li.EmployeeID and e.status =1 
                            and e.PayrollTypeId=%n order by li.EmployeeID, ScheduleNo
                            ", GlobalFunctions.FirstDateOfMonth(monthDate), GlobalFunctions.LastDateOfMonth(monthDate), payrollTypeID.Integer);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oLoanPayments;
        }
        internal static DataSet GetLoanPaymentDue(TransactionContext tc, DateTime dFromDate, DateTime dToDate)
        {
            DataSet oLoanPayments = new DataSet();
            try
            {
                oLoanPayments = tc.ExecuteDataSet("SELECT  DISTINCT E.EmployeeNo,E.NAME,LB.DESCRIPTION,LI.LOANAMOUNT,LS.INSTALLMENTPRINCIPAL,LS.INSTALLMENTINTEREST,Ls.ClosingBalance" 
                                                   + " FROM LoanIssue LI,LoanBasic LB, LoanSchedule LS, Employee E" 
                                                   + " WHERE LB.LOANID=LI.LOANID"
                                                   + " AND LI.LoanIssueID= LS.LoanIssueID" 
                                                   + " AND E.EmployeeID = LI.EmployeeID" 
                                                   + " AND LS.DueInstallmentDate BETWEEN %d AND %d" 
                                                   + " AND LS.PAYMENTDATE=%d"
                                                   + " ORDER BY E.EmployeeNo", dFromDate, dToDate,dToDate);
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oLoanPayments;
        }
        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nIssueID)
        {
            tc.ExecuteNonQuery("DELETE FROM LoanSchedule WHERE LOANISSUEID=%n", nIssueID.Integer);
        }
        internal static void Delete2(TransactionContext tc, ID nIssueID)
        {
            tc.ExecuteNonQuery("DELETE FROM LoanSchedule WHERE LOANISSUEID=%n AND paymentdate is null", nIssueID.Integer);
        }

        internal static void UpdatePaymentDate(TransactionContext tc, 
            ID loanSheduleId, DateTime paymentDate, EnumLoanPaymentMode mode)
        {
            
            tc.ExecuteNonQuery("Update LoanSchedule SET paymentDate=%d, PaymentMode=%n"
            + " WHERE LoanScheduleID=%n",  paymentDate, mode, loanSheduleId.Integer);
        }

        public static int GetNewID(TransactionContext tc, string tableName, string columnName)
        {
            return tc.GenerateID(tableName, columnName);
        }
        #endregion

        
    }

    #endregion
}