CEL_Payroll/Payroll.Service/Loan/DA/LoanIssueDA.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  LoanIssueDA

    internal class LoanIssueDA
    {
        #region  Constructor

        private LoanIssueDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, LoanIssue item)
        {
            tc.ExecuteNonQuery("INSERT INTO LoanIssue(LoanIssueID, loanID, loanNo, employeeID, loanAmount, issueDate, noOfInstallments, installmentPrincipal, interestRate, startPaybackMonthDate, CreatedBy, CreationDate, WFStatus,AverageInterestRate)" +
            " VALUES(%n, %n, %s, %n, %n, %d, %n, %n, %n, %d, %n, %d, %n,%b)", item.ID.Integer, item.LoanID.Integer, item.LoanNo,
            item.EmployeeID.Integer, item.LoanAmount, item.IssueDate, item.NoOfInstallments, item.InstallmentPrincipal,
            item.InterestRate, item.StartPaybackMonthDate, item.CreatedBy.Integer, item.CreatedDate, item.WFStatus, item.AverageInterestRate);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, LoanIssue item)
        {
            tc.ExecuteNonQuery("UPDATE LoanIssue SET loanID=%n, loanNo=%s, employeeID=%n, loanAmount=%n, issueDate=%d, noOfInstallments=%n, installmentPrincipal=%n, interestRate=%n, startPaybackMonthDate=%d, ModifiedBy=%n, ModifiedDate=%d, WFStatus=%n,AverageInterestRate=%b" +
            " WHERE LoanIssueID=%n", item.LoanID.Integer, item.LoanNo, item.EmployeeID.Integer, item.LoanAmount, item.IssueDate, item.NoOfInstallments,
            item.InstallmentPrincipal, item.InterestRate, item.StartPaybackMonthDate, item.ModifiedBy.Integer,
            item.ModifiedDate, item.WFStatus, item.AverageInterestRate, item.ID.Integer);
        }
        internal static void UpdateAvgIntStatus(TransactionContext tc, LoanIssue item)
        {
            tc.ExecuteNonQuery("UPDATE LoanIssue SET AverageInterestRate=%b" +
            " WHERE LoanIssueID=%n", item.AverageInterestRate, item.ID.Integer);
        }
        #endregion

        #region Get Function

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

        internal static IDataReader Get(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanIssueID=%n", nID.Integer);
        }

        internal static IDataReader GetByLoanID(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanID=%n", nID.Integer);
        }

        internal static IDataReader GetMaxLoanIssueID(TransactionContext tc, ID nEmpID)
        {
            return tc.ExecuteReader("SELECT MAX(LoanIssueID) as MaxLoanID FROM LoanIssue WHERE EmployeeID=%n", nEmpID.Integer);
        }

        internal static IDataReader GetLoanNo(TransactionContext tc, ID nEmpID, int? nLoanIssueID)
        {
            return tc.ExecuteReader("SELECT LoanNo FROM LoanIssue WHERE EmployeeID=%n AND LoanIssueID=%n", nEmpID.Integer, nLoanIssueID);
        }

        internal static IDataReader GetExistingLoan(TransactionContext tc, ID nLoanID, ID nEmpID)
        {
            string sQL = SQLParser.MakeSQL("SELECT * FROM LoanIssue WHERE LoanID=%n AND LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE EmployeeID=%n " +
                                    "AND PaymentDate IS NULL)", nLoanID.Integer, nEmpID.Integer);
            return tc.ExecuteReader(sQL);

        }
        internal static IDataReader GetExistingLoan(TransactionContext tc, ID nEmpID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE EmployeeID=%n " +
                                    "AND PaymentDate IS NULL)", nEmpID.Integer);
        }

        internal static IDataReader Get(TransactionContext tc, DateTime dateTime)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE PaymentDate IS NULL AND DUEINSTALLMENTDATE BETWEEN %d AND %d)", GlobalFunctions.FirstDateOfMonth(dateTime), GlobalFunctions.LastDateOfMonth(dateTime));
        }
        internal static IDataReader Get(TransactionContext tc, DateTime dateTime, DateTime dateTime2)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE issueDate between %d AND %d ", GlobalFunctions.FirstDateOfMonth(dateTime), GlobalFunctions.LastDateOfMonth(dateTime2));
        }

        internal static IDataReader GetByDueInstallmentDate(TransactionContext tc, DateTime dateTime, string sEmpID)
        {
            string sQL = SQLParser.MakeSQL("SELECT * FROM LoanIssue WHERE LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule " +
                                    " WHERE PaymentMode IN (%n, %n) AND DUEINSTALLMENTDATE BETWEEN %d AND %d)" +
                                    " AND EmployeeID in (%q) order by EmployeeID", EnumLoanPaymentMode.NotYetpayment,
                                    EnumLoanPaymentMode.Salary, GlobalFunctions.FirstDateOfMonth(dateTime),
                                    GlobalFunctions.LastDateOfMonth(dateTime), sEmpID);
            return tc.ExecuteReader(sQL);
        }

        internal static IDataReader GetByDueInstallmentDate(TransactionContext tc, DateTime dateTime)
        {
            string sQL = SQLParser.MakeSQL("SELECT * FROM LoanIssue WHERE LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule " +
                                    " WHERE PaymentMode IN (%n, %n) AND DUEINSTALLMENTDATE BETWEEN %d AND %d)"
                                    , EnumLoanPaymentMode.NotYetpayment,
                                    EnumLoanPaymentMode.Salary, GlobalFunctions.FirstDateOfMonth(dateTime),
                                    GlobalFunctions.LastDateOfMonth(dateTime));
            return tc.ExecuteReader(sQL);
        }

        internal static IDataReader Get(TransactionContext tc, string employeeNo)
        {
            string sql = SQLParser.MakeSQL("Select * from LoanIssue where EmployeeId IN (Select EmployeeID from Employee where EmployeeNo=%s)", employeeNo);
            return tc.ExecuteReader(sql);

        }
        internal static DataSet GetIssuedLoanIDs(TransactionContext tc, int EmpID)
        {
            DataSet oIDs = new DataSet();
            try
            {
                oIDs = tc.ExecuteDataSet("SELECT distinct loanid,interestrate,installmentprincipal FROM  LOANISSUE where EMPLOYEEID=%n ", EmpID);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oIDs;
        }




        internal static DataSet GetIssuedLoans(TransactionContext tc, int EmpID)
        {
            DataSet oIDs = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT distinct loanno FROM  LOANISSUE where EMPLOYEEID=%n", EmpID);
                //string sql = SQLParser.MakeSQL(@"SELECT distinct Description as LoanName from LoanBasic where LoanID in(select LoanID FROM  LOANISSUE where EMPLOYEEID=%n)", EmpID);
                oIDs = tc.ExecuteDataSet(sql);
                //oIDs = tc.ExecuteDataSet("SELECT distinct loanno FROM  LOANISSUE where EMPLOYEEID=%n ", EmpID);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oIDs;
        }





        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM [LoanIssue] WHERE LoanIssueID=%n", nID.Integer);
        }

        #endregion

        internal static IDataReader GetByEmployeeIdAndLoanNumber(ID id, string loanNumber, TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE EmployeeId = %n AND LoanNo = %s", id.Integer, loanNumber);
        }

        internal static IDataReader GetExistingAllLoan(TransactionContext tc, ID nLoanID, ID nEmpID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM LoanIssue WHERE LoanID=%n AND LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE EmployeeID=%n)", nLoanID.Integer, nEmpID.Integer);
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanID=%n AND LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE EmployeeID=%n)", nLoanID.Integer, nEmpID.Integer);
        }

        internal static IDataReader GetByLoanAndEmployeeIdAndLoanNumber(TransactionContext tc, ID LoanId, ID EmployeeId, string loanNumber)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM LoanIssue WHERE LoanID=%n AND LoanIssueID IN " +
                                   "(SELECT LoanIssueID FROM LoanSchedule WHERE EmployeeID=%n) AND LoanNo = %s", LoanId.Integer, EmployeeId.Integer, loanNumber);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllExistingLoan(TransactionContext tc, string LoanIDs)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanID IN(%q) AND LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE PaymentDate IS NULL)", LoanIDs);
        }

        internal static IDataReader GetAllExistingLoan(TransactionContext tc, int EmpID, string sLaonIDs)
        {
            DataSet oIDs = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT * 
                                                 FROM  LOANISSUE 
                                                 where EMPLOYEEID=%n
                                                 AND LoanID IN(%q)", EmpID, sLaonIDs);
                return tc.ExecuteReader(sql);

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

        internal static IDataReader GetAllExistingLoan(TransactionContext tc, string LoanIDs, DateTime month)
        {
            return tc.ExecuteReader("SELECT * FROM LoanIssue WHERE LoanID IN(%q) AND LoanIssueID IN " +
                                    "(SELECT LoanIssueID FROM LoanSchedule WHERE PaymentDate>=%d )", LoanIDs, month);
        }

    }

    #endregion
}