EchoTex_Payroll/HRM.DA/DA/Fund/Loan/FmLoanDA.cs

using System;
using System.Collections.Generic;
using System;
using System.Data;
using System.Linq;
using System.Text;
using Ease.Core.DataAccess;
using HRM.BO;

namespace HRM.DA
{
    internal class FmLoanDA
    {
        #region Constructor

        public FmLoanDA()
        {
        }

        #endregion

        #region Insert Function

        internal static void Insert(TransactionContext tc, FmLoan loan)
        {
            string sql = SQLParser.MakeSQL(
                "INSERT INTO FmLoan(LoanID,ActivityID,CustomerID,Description,InstallmentNo,InterestRate,IssueDate,LoanCategoryID,LoanAccNo,LoanNo,MemberID,PrincipalAmount,Status,Version,CreatedBy,CreatedDate,ProjectID,StartPayBackMonth,TransferLogID, LoanIssueStatus, Remarks)" +
                " VALUES(%n,%n,%n,%s,%n,%n,%d,%n,%s,%s,%n,%n,%n,%n,%n,%D,%n,%d,%n,%n,%s )", loan.ID,
                DataReader.GetNullValue(loan.ActivityID), loan.CustomerID, loan.Description, loan.InstallmentNo,
                loan.InterestRate, loan.IssueDate, loan.LoanCategoryID, loan.LoanAccNo, loan.LoanNo, loan.MemberID,
                loan.PrincipalAmount, loan.Status, loan.Version, loan.CreatedBy, loan.CreatedDate, loan.ProjectID,
                loan.StartPayBackMonth, loan.TransferLogIDinInt, loan.LoanIssueStatus, loan.Remarks);
            tc.ExecuteNonQuery(sql);
        }

        internal static void Insert(TransactionContext tc, FmLoanSchedule loanSchedule)
        {
            string sql = SQLParser.MakeSQL(
                "INSERT INTO FmLoanSchedule(LoanScheduleID,ActivityID,CustomerID,EffectiveBalance,InstallmentAmount,InstallmentInterest,InstallmentPrincipal,LoanCategoryID,LoanID,PayDate,ScheduledPayDate,SerialNo,CreatedBy,CreatedDate,ProjectID)" +
                " VALUES(%n,%n,%n,%n,%n,%n,%n,%n,%n,%d,%d,%n,%n,%D,%n)", loanSchedule.ID,
                DataReader.GetNullValue(loanSchedule.ActivityID),
                DataReader.GetNullValue(loanSchedule.CustomerID), loanSchedule.EffectiveBalance,
                loanSchedule.InstallmentAmount, loanSchedule.InstallmentInterest,
                loanSchedule.InstallmentPrincipal, loanSchedule.LoanCategoryID, loanSchedule.LoanID,
                DataReader.GetNullValue(loanSchedule.PayDate),
                loanSchedule.ScheduledPayDate, loanSchedule.SerialNo, loanSchedule.CreatedBy, loanSchedule.CreatedDate,
                loanSchedule.ProjectID);

            tc.ExecuteNonQuery(sql);
        }

        //internal static void Insert(TransactionContext tc, LoanCustomer loanCustomer)
        //{
        //    string sql = SQLParser.MakeSQL("INSERT INTO LoanCustomer(CustomerID, AccNo,MembershipDate,Address,DateOfBirth,DateOfJoining,EmpCode,IsMember,MemberShipNo,Name,CreatedBy,CreatedDate,ProjectID,MemberID, Email)" +
        //    " VALUES(%n,%s,%d,%s,%d,%d,%s,%n,%s,%s,%n,%D,%n,%n, %s)", loanCustomer.ID.Integer, DataReader.GetNullValue(loanCustomer.AccNo), loanCustomer.MembershipDate, DataReader.GetNullValue(loanCustomer.Address), loanCustomer.DateOfBirth, loanCustomer.DateOfJoining, loanCustomer.EmpCode, loanCustomer.IsMember, loanCustomer.MemberShipNo, loanCustomer.Name, loanCustomer.CreatedBy.Integer, loanCustomer.CreatedDate, loanCustomer.ProjectID.Integer, DataReader.GetNullValue(loanCustomer.MemberID.Integer), DataReader.GetNullValue(loanCustomer.Email));
        //    tc.ExecuteNonQuery(sql);
        //}

        internal static void Insert(TransactionContext tc, LoanLog log)
        {
            string sql = SQLParser.MakeSQL(
                "INSERT INTO LoanLog(LogID,LoanCategoryID,LoanID,LoanNo,LoanCustomerID,EmpCode,Activity,ActivityDate,Rate,FlatRate,FromMonth,NoOfMonth,ProjectID,CreatedBy,CreatedDate)" +
                " VALUES(%n,%n,%n,%s,%n,%s,%n,%d,%n,%n,%d,%n,%n,%n,%D)", log.ID, log.LoanCategoryID, log.LoanID,
                log.LoanNo, log.LoanCustomerID, log.EmpCode, log.Activity, log.ActivityDate,
                DataReader.GetNullValue(log.Rate), DataReader.GetNullValue(log.FlatRate),
                DataReader.GetNullValue(log.FromMonth), DataReader.GetNullValue(log.NoOfMonth), log.ProjectID,
                log.CreatedBy, log.CreatedDate);

            tc.ExecuteNonQuery(sql);
        }

        #endregion Insert Function

        #region Update function

        internal static void Update(TransactionContext tc, FmLoan loan)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(
                "UPDATE FmLoan Set ActivityID = %n,CustomerID = %n,Description = %s,InstallmentNo = %n,"
                + "InterestRate = %n,IssueDate = %d,LoanCategoryID = %n,LoanAccNo = %s,LoanNo = %s,MemberID = %n,"
                + "PrincipalAmount = %n,Status = %n,Version = %n,	ModifiedBy = %n,ModifiedDate = %D,"
                + "StartPayBackMonth = %d,TransferLogID = %n, LoanIssueStatus=%n "
                + "WHERE LoanID = %n ", DataReader.GetNullValue(loan.ActivityID), loan.CustomerID, loan.Description,
                loan.InstallmentNo, loan.InterestRate, loan.IssueDate, loan.LoanCategoryID, loan.LoanAccNo, loan.LoanNo,
                loan.MemberID, loan.PrincipalAmount, loan.Status, loan.Version, loan.ModifiedBy, loan.ModifiedDate,
                loan.StartPayBackMonth, loan.TransferLogIDinInt, loan.LoanIssueStatus, loan.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateLoanEarlySettle(TransactionContext tc, FmLoan loan)
        {
            string sql = string.Empty;

            sql = SQLParser.MakeSQL(
                "UPDATE FmLoan Set InstallmentNo = %n, ActivityID= %n, Status = %n,	ModifiedBy = %n,ModifiedDate = %D, TransferLogID = %n "
                + "WHERE LoanID = %n ", loan.InstallmentNo, loan.Activity, loan.Status, loan.ModifiedBy,
                loan.ModifiedDate, loan.TransferLogIDinInt, loan.ID);
            tc.ExecuteNonQuery(sql);
        }

        //internal static void Update(TransactionContext tc, LoanCustomer loanCustomer)
        //{
        //    string sql = SQLParser.MakeSQL("UPDATE LoanCustomer Set  AccNo = %s, MembershipDate = %d, Address= %s, DateOfBirth= %d, DateOfJoining =%d, EmpCode = %s, IsMember =%n, MemberShipNo =%s, Name =%s, ModifiedBy = %n, ModifiedDate = %D,ProjectID = %n,MemberID = %n, Email = %s " +
        //    "WHERE CustomerID = %n ", loanCustomer.AccNo, DataReader.GetNullValue(loanCustomer.MembershipDate), DataReader.GetNullValue(loanCustomer.Address), 
        //    loanCustomer.DateOfBirth, loanCustomer.DateOfJoining, loanCustomer.EmpCode, loanCustomer.IsMember, loanCustomer.MemberShipNo, loanCustomer.Name, 
        //    loanCustomer.ModifiedBy.Integer, loanCustomer.ModifiedDate, loanCustomer.ProjectID.Integer, loanCustomer.MemberID.Integer, DataReader.GetNullValue(loanCustomer.Email), 
        //    loanCustomer.ID.Integer);
        //    tc.ExecuteNonQuery(sql);
        //}

        internal static void UpdateLoanSchedule(TransactionContext tc, FmLoanSchedule loanSchedule)
        {
            string sql = SQLParser.MakeSQL(
                "UPDATE FmLoanSchedule Set InstallmentAmount =%n, InstallmentInterest=%n, InstallmentPrincipal=%n, PayDate=%d, ModifiedBy = %n, ModifiedDate = %D WHERE LoanScheduleID=%n ",
                loanSchedule.InstallmentAmount, loanSchedule.InstallmentInterest, loanSchedule.InstallmentPrincipal,
                loanSchedule.PayDate.Value,
                loanSchedule.ModifiedBy, loanSchedule.ModifiedDate, loanSchedule.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateLoanIssueStatus(TransactionContext tc, FmLoan oLoan)
        {
            string sql = SQLParser.MakeSQL("UPDATE FmLoan Set LoanIssueStatus =%n, Remarks=%s WHERE LoanID=%n ",
                oLoan.LoanIssueStatus, oLoan.Remarks, oLoan.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void SetLoanStatus(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"UPDATE FmLoan SET Status = 3, LoanIssueStatus = 8 WHERE LoanID IN 
                            (
                            SELECT tab.LoanID FROM
                            (
                            SELECT issue.LoanID,issue.InstallmentNo 'Installmentsrecovered', count(lsc.LoanScheduleid) 'Installments'
                            FROM LoanCategory cat, Loan issue, MemberDetail m, LoanSchedule lsc
                            WHERE  issue.LoanIssueStatus = 7 AND issue.LoanCategoryID = cat.LoanCategoryID AND lsc.LoanID = issue.LoanID
                            AND issue.MemberID=m.MemberID
                            GROUP BY issue.LoanID, issue.InstallmentNo
                            ) tab WHERE tab.Installmentsrecovered = tab.Installments
                            )");
            tc.ExecuteNonQuery(sql);
        }

        //add LoanDA	
        internal static void UpdateLoanInfo(TransactionContext tc, FmLoan loan)
        {
            string sql =
                SQLParser.MakeSQL("UPDATE FmLoan Set Status =%n, ModifiedBy = %n, ModifiedDate = %D WHERE LoanID=%n ",
                    loan.Status, loan.ModifiedBy, loan.ModifiedDate, loan.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateLoanTransferID(TransactionContext tc, FmLoan loan)
        {
            string sql = SQLParser.MakeSQL(
                "UPDATE FmLoan Set TransferLogID =%n, ModifiedBy = %n, ModifiedDate = %d WHERE LoanID=%n ", +
                    DataReader.GetNullValue(loan.TransferLogIDinInt), loan.ModifiedBy, loan.ModifiedDate, loan.ID);
            tc.ExecuteNonQuery(sql);
        }

        #endregion Update function

        #region Delete Function

        internal static void DeleteLoanSchedule(TransactionContext tc, int loanID)
        {
            tc.ExecuteNonQuery("DELETE FROM FmLoanSchedule WHERE LoanID=%n AND ProjectID=%n",
                loanID); //, User.CurrentUser.ProjectID.Integer
        }

        //DeleteLoanScheduleByID
        internal static void DeleteLoanScheduleByID(TransactionContext tc, int loanScheduleID)
        {
            tc.ExecuteNonQuery("DELETE FROM FmLoanSchedule WHERE LoanScheduleID=%n", loanScheduleID);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, int loanID)
        {
            return tc.ExecuteReader("SELECT * FROM FmLoan WHERE LoanID=%n", loanID);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM FmLoan Where ProjectID=%n"); //, User.CurrentUser.ProjectID.Integer
        }

        internal static IDataReader Get(TransactionContext tc, string LoanNo)
        {
            string sSQL =
                SQLParser.MakeSQL("SELECT * FROM FmLoan Where ProjectID=%n AND LoanNo=%s",
                    LoanNo); //, User.CurrentUser.ProjectID.Integer
            return tc.ExecuteReader(sSQL);
        }

        //internal static IDataReader Get(TransactionContext tc, int runningLoan)
        //{
        //    return tc.ExecuteReader("SELECT * FROM Loan Where Status = %n AND ProjectID=%n", runningLoan);//, User.CurrentUser.ProjectID.Integer
        //}

        internal static IDataReader GetNotSendLoan(TransactionContext tc, int runningLoan)
        {
            //return tc.ExecuteReader("SELECT * FROM Loan Where Status = %n AND TransferLogID IS NOT NULL ", runningLoan);
            return tc.ExecuteReader(
                "SELECT * FROM FmLoan Where TransferLogID IS NOT NULL AND ProjectID=%n"); //, User.CurrentUser.ProjectID.Integer
        }
        //internal static IDataReader GetBySearch(TransactionContext tc, string sSearch)
        //{
        //    string sql = string.Empty;
        //    sql = SQLParser.MakeSQL("SELECT * FROM Loan %q", sSearch);
        //    return tc.ExecuteReader(sql);
        //}

        internal static IDataReader GetSchedules(TransactionContext tc, int loanID)
        {
            return tc.ExecuteReader("SELECT * FROM FmLoanSchedule WHERE LoanID=%n", loanID);
        }

        internal static IDataReader GetCategory(TransactionContext tc, int loanCategoryID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanCategory WHERE LoanCategoryID=%n AND Version = %n",
                loanCategoryID, 0);
        }

        internal static IDataReader GetCustomer(TransactionContext tc, int CustomerID)
        {
            string Ssql = SQLParser.MakeSQL("SELECT * FROM LoanCustomer WHERE CustomerID=%n AND ProjectID=%n",
                CustomerID); //, User.CurrentUser.ProjectID.Integer
            return tc.ExecuteReader(Ssql);
        }

        #endregion

        internal static IDataReader Get(TransactionContext tc, LoanCategory loanCategory)
        {
            return tc.ExecuteReader("SELECT * FROM FmLoan where LoanCategoryID = %n AND ProjectID=%n",
                loanCategory.ID); //, User.CurrentUser.ProjectID.Integer
        }

        #region LoanList get function

        internal static DataSet GetLoanList(TransactionContext tc, string query)
        {
            string sql = string.Empty;
            query = SQLParser.TagSQL(query) +
                    SQLParser.MakeSQL("issue.LoanCategoryID = cat.LoanCategoryID AND issue.MemberID=m.MemberID",
                        0); //, User.CurrentUser.ProjectID.Integer
            sql = SQLParser.MakeSQL(
                "SELECT m.EmpCode, m.MemberID, m.Name, issue.LoanID, issue.LoanNo, issue.IssueDate, issue.InterestRate, issue.Status, issue.LoanAccNo, " +
                "issue.PrincipalAmount,issue.InstallmentNo, issue.LoanIssueStatus, cat.LoanCategoryCode " +
                "FROM LoanCategory cat, Loan issue, MemberDetail m %q", query);
            return tc.ExecuteDataSet(sql);
        }

        #endregion LoanList get function

        #region GetLoanMember

        internal static DataSet GetLoanMember(TransactionContext tc, string query, int custID)
        {
            string sql = string.Empty;
            query = SQLParser.TagSQL(query) + SQLParser.MakeSQL("issue.LoanCategoryID = cat.LoanCategoryID AND "
                                                                + "issue.CustomerID = cust.CustomerID AND cat.Version = %n AND issue.ProjectID=%n",
                0); //, User.CurrentUser.ProjectID.Integer
            sql = SQLParser.MakeSQL(
                "SELECT issue.LoanID, issue.LoanAccNo, issue.IssueDate, issue.InterestRate,issue.PrincipalAmount,(SELECT max(SerialNo) FROM FmLoanSchedule WHERE "
                + "issue.LoanID = LoanSchedule.LoanID) AS InstallmentNo, cat.LoanCategoryCode, cust.Name, cust.EmpCode ,schedule.ScheduledPayDate, schedule.PayDate,"
                + " schedule.InstallmentAmount, schedule.EffectiveBalance,schedule.InstallmentInterest,schedule.InstallmentPrincipal"
                + " FROM FmLoanSchedule schedule, LoanCategory cat, LoanCustomer cust, FmLoan issue %q "
                + "AND issue.LoanID = schedule.LoanID AND cust.CustomerID = %n ", query, custID);
            return tc.ExecuteDataSet(sql);
        }

        internal static DataTable GetTable(TransactionContext tc, DateTime fromDate, DateTime toDate)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(
                "SELECT C.EmpCode, C.Name, L.PrincipalAmount, (SELECT max(serialNo) FROM FmLoanSchedule WHERE L.LoanID = LoanID)AS InstallmentNo,"
                + "(SELECT Count(LoanSchedule.LoanID) FROM FmLoanSchedule WHERE L.LoanID = LoanID AND Paydate IS NOT Null)AS PaidInstallmentNo,"
                + "(SELECT TOP 1 InstallmentAmount FROM FmLoanSchedule WHERE L.LoanID = LoanID) AS InstallmentAmount,"
                + "(SELECT Sum(InstallmentPrincipal) FROM FmLoanSchedule WHERE L.LoanID = LoanID AND Paydate IS NOT Null)AS LoanRecovered,"
                + "(SELECT Max(PayDate) FROM FmLoanSchedule WHERE L.LoanID = LoanID AND Paydate IS NOT Null)AS LastInstallmentPaidMonth,"
                + " L.IssueDate from FmLoan L, LoanCustomer C WHERE L.CustomerID = C.CustomerID AND L.ProjectID =%n AND ("
                + "("
                + " SELECT Max(PayDate) FROM FmLoanSchedule WHERE L.LoanID = LoanID  ) BETWEEN  %d AND %d)"
                + " AND L.[Status] <> 3", fromDate, toDate); //User.CurrentUser.ProjectID.Integer, 
            DataSet ds = tc.ExecuteDataSet(sql);
            return ds.Tables[0];
        }

        internal static DataTable GetTable(TransactionContext tc, string smemberId, DateTime fromDate, DateTime toDate)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(@"SELECT C.EmpCode,L.PrincipalAmount,L.InterestRate,L.IssueDate,C.DateOfJoining   
                                ,(SELECT TOP 1 InstallmentAmount FROM FmLoanSchedule 
                                WHERE L.LoanID = LoanID) AS InstallmentAmount,
                                (SELECT Top 1 InstallmentInterest FROM FmLoanSchedule 
                                WHERE L.LoanID = LoanID )AS InterestAmount,
                                (SELECT sum(InstallmentAmount) FROM FmLoanSchedule WHERE L.LoanID = LoanID 
                                AND Paydate IS NOT Null)AS PaidAmount, 
                                L.StartPayBackMonth as FirstDeductionDate,
                                (Select Min(ScheduledPayDate) from FmLoanSchedule 
                                Where  L.LoanID = LoanID)As LastDeductionDate
                                from FmLoan L, LoanCustomer C WHERE L.CustomerID = C.CustomerID 
                                AND L.ProjectID =%n And C.EmpCode in(%q) And(L.Status = 2 OR 
                                ((SELECT Max(PayDate) FROM FmLoanSchedule WHERE L.LoanID = LoanID AND L.Status = 3 ) 
                                BETWEEN  %d AND %d))", smemberId, fromDate,
                toDate); //, User.CurrentUser.ProjectID.Integer

            DataSet ds = tc.ExecuteDataSet(sql);
            return ds.Tables[0];
        }

        internal static DataTable GetLoanInfoByMonth(TransactionContext tc, DateTime scheduleMonth)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(
                @"SELECT m.EmpCode, m.Name, lc.Description LoanName, lc.LoanCategoryCode LoanCategory, l.LoanNo, ls.SerialNo ScheduleNO,
                                    ls.ScheduledPayDate ScheduleDate, ls.InstallmentPrincipal, ls.InstallmentInterest,ls.InstallmentAmount,ls.PayDate 
                                     FROM Loan l
                                    INNER JOIN FmLoanSchedule ls ON ls.LoanID=l.LoanID
                                    INNER JOIN LoanCategory lc ON lc.LoanCategoryID =l.LoanCategoryID
                                    INNER JOIN MemberDetail m ON m.MemberID=l.MemberID
                                    WHERE ls.ScheduledPayDate=%d and l.ActivityID <> %n", scheduleMonth,
                (int)EnumLoanActivity.EarlySettlement); //AND ls.PayDate IS null 

            DataSet ds = tc.ExecuteDataSet(sql);
            return ds.Tables[0];
        }

        #endregion

        #region GenerateLoanNo

        internal static int GenerateLoanNo(TransactionContext tc, int memberID, int loanCategoryID)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(
                "SELECT Max(substring(LoanNo,(len(LoanNo)-2),len(LoanNo)))FROM FmLoan WHERE Loan.LoanCategoryID = %n AND Loan.MemberID = %n",
                loanCategoryID, memberID);
            object ob = tc.ExecuteScalar(sql);
            if (ob != DBNull.Value)
            {
                return Convert.ToInt32(ob);
            }

            return 0;
        }

        #endregion

        //#region ISValidMOnth

        //internal bool ISValidMonth(TransactionContext tc, Loan loan, DateTime dt)
        //{
        //    object ob = tc.ExecuteScalar();
        //    return Convert.ToInt32(ob) > 0;
        //}
        //#endregion

        internal static DataSet GetLoans(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(
                "SELECT DISTINCT * FROM %q "); //, ConfigurationSettings.AppSettings["vwLoanCollect"]
            ds = tc.ExecuteDataSet(sql);
            ds.Tables[0].TableName = "AllMember";
            return ds;
        }

        internal static void UpdateGLTranID(TransactionContext tc, int loanID, int glTranID)
        {
            string sUpdate = SQLParser.MakeSQL("UPDATE FmLoan SET GLTranID = %n where LoanID = %n", glTranID, loanID);
            tc.ExecuteNonQuery(sUpdate);
        }

        internal static void DeleteLoanLog(TransactionContext tc, int iD)
        {
            string sDelete = SQLParser.MakeSQL("Delete LoanLog where LoanID = %n", iD);
            tc.ExecuteNonQuery(sDelete);
        }
    }
}