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  EmployeeBankAccountDA

    internal class EmployeeBankAccountDA
    {
        #region  Constructor

        private EmployeeBankAccountDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, EmployeeBankAccount item)
        {
            string sSQL = SQLParser.MakeSQL("INSERT INTO BANKACCOUNTHISTORY(EmpBankAccountID, employeeID, changeDate, accountNo,AccountType, branchID, CreatedBy, CreationDate)" +
            " VALUES(%n, %n, %D, %s,%n, %n, %n, %D)", item.ID.Integer, item.EmployeeID.Integer, item.EffectDate, item.AccountNo, (int)item.AccountType,  DataReader.GetNullValue(item.BranchID,IDType.Integer), DataReader.GetNullValue(item.CreatedBy.Integer), DataReader.GetNullValue(item.CreatedDate));
            tc.ExecuteNonQuery(sSQL);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, EmployeeBankAccount item)
        {
            string sSQL = SQLParser.MakeSQL(@"UPDATE BANKACCOUNTHISTORY SET employeeID=%n, changeDate=%D, accountNo=%s,AccountType=%n, branchID=%n, ModifiedBy=%n, ModifiedDate=%D
            WHERE EmpBankAccountID=%n", item.EmployeeID.Integer, item.EffectDate, item.AccountNo, (int)item.AccountType, item.BranchID.Integer, DataReader.GetNullValue(item.ModifiedBy.Integer), DataReader.GetNullValue(item.ModifiedDate), item.ID.Integer);
            tc.ExecuteNonQuery(sSQL);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM BANKACCOUNTHISTORY ORDER BY CreationDAte");
        }

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

        internal static IDataReader Get(TransactionContext tc, ID nEmpID, DateTime dEffectDate, int payrollTypeID)
        {
            return tc.ExecuteReader(@"Select * from BANKACCOUNTHISTORY where 
                                    CHANGEDATE=(Select MAX(CHANGEDATE) from BANKACCOUNTHISTORY where EMPLOYEEID=%n and CHANGEDATE<=%d) 
                                    AND EMPLOYEEID=(Select EMPLOYEEID from Employee where EMPLOYEEID=%n and PAYROLLTYPEID=%n) ", nEmpID.Integer, dEffectDate, nEmpID.Integer, payrollTypeID);
        }

        internal static IDataReader GetByEmployeeID(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader(@"Select * from BANKACCOUNTHISTORY Where EmployeeId = %n", nID.Integer);
        }

        internal static DataSet GetEmpBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                bool bfixedAmount = ConfigurationManager.GetBoolValue("costcenter", "manadatoryinsalary", EnumConfigurationType.Logic);
                if (!bfixedAmount)
                {
                    #region DeadQuery
                    //                    string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
                    //                                                SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,CC.DESCRIPTION AS CostCenter
                    //                                                FROM EMPLOYEE E,SalaryMonthly SM,SalaryMonthlyDetail SMD,
                    //                                                BRANCHES as BR,CRG CC,SALARYEMPCOSTCENTER sempcc
                    //                                                WHERE E.EMPLOYEEID=SM.EMPLOYEEID 
                    //                                                AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
                    //                                                AND BR.BranchID=SM.BranchID
                    //                                                AND SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
                    //                                                AND SMD.ITEMGROUP=%n 
                    //                                                AND SM.EMPLOYEEID = sempcc.EMPLOYEEID
                    //                                                AND SMD.SALARYMONTHLYID = sempcc.SALARYMONTHLYID
                    //                                                AND sempcc.COSTCENTERID = CC.CRGID
                    //                                                AND SM.EmployeeID IN(%q)
                    //                                                order by E.EmployeeNo",
                    //                                                     dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID); 
                    #endregion

                    string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
                                                    SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,CC.DESCRIPTION AS CostCenter
                                                    FROM SalaryMonthly SM
                                                    LEFT JOIN SALARYMONTHLYDETAIL SMD ON SMD.SALARYMONTHLYID = SM.SALARYMONTHLYID
                                                    LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = SM.EMPLOYEEID
                                                    LEFT JOIN BRANCHES BR ON BR.BRANCHID = SM.BRANCHID
                                                    LEFT JOIN SALARYEMPCOSTCENTER sempcc ON sempcc.SALARYMONTHLYID = SM.SALARYMONTHLYID
                                                    LEFT JOIN CRG CC ON CC.CRGID = sempcc.COSTCENTERID
                                                    WHERE SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
                                                    AND SMD.ITEMGROUP=%n AND SM.EmployeeID IN(%q)
                                                    order by E.EmployeeNo", dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID);

                    oBankAdvices = tc.ExecuteDataSet(sSQL);
                }
                else
                {
                    #region DeadQuery
                    //                    string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
                    //                                                SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,'' AS CostCenter
                    //                                                FROM EMPLOYEE E,SalaryMonthly SM,SalaryMonthlyDetail SMD,
                    //                                                BRANCHES as BR
                    //                                                WHERE E.EMPLOYEEID=SM.EMPLOYEEID 
                    //                                                AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
                    //                                                AND BR.BranchID=SM.BranchID
                    //                                                AND SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
                    //                                                AND SMD.ITEMGROUP=%n 
                    //                                                AND SM.EmployeeID IN(%q)
                    //                                                order by E.EmployeeNo",
                    //                                                 dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID); 
                    #endregion

                    string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
                                                    SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,'' AS CostCenter
                                                    FROM SalaryMonthly SM
                                                    LEFT JOIN SALARYMONTHLYDETAIL SMD ON SMD.SALARYMONTHLYID = SM.SALARYMONTHLYID
                                                    LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = SM.EMPLOYEEID
                                                    LEFT JOIN BRANCHES BR ON BR.BRANCHID = SM.BRANCHID
                                                    WHERE SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
                                                    AND SMD.ITEMGROUP=%n AND SM.EmployeeID IN(%q)
                                                    order by E.EmployeeNo", dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID);

                    oBankAdvices = tc.ExecuteDataSet(sSQL);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oBankAdvices;
        }

        internal static IDataReader GetNotYetProcessUptoToday(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL("Select * from BANKACCOUNTHISTORY where IsProcessed =%b and CHANGEDATE <=%d", false, DateTime.Today);
            return tc.ExecuteReader(sql);
        }

        internal static DataSet GetEmpEuroBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
        {
            DataSet oEuroBankAdvices = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT tab1.EMPLOYEENO, tab1.[NAME], tab1.OUTPAYACCOUNTNO EuroAccountNo, SUM(tab1.CHANGEDAMOUNT) Amount
                                                FROM  (SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.OUTPAYACCOUNTNO,SMD.ITEMCODE,
                                                SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,'' AS CostCenter
                                                FROM SalaryMonthly SM
                                                INNER JOIN SalaryMonthlyDetail SMD ON SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
                                                INNER JOIN Employee e ON e.EMPLOYEEID=SM.EMPLOYEEID
                                                LEFT JOIN BRANCHES BR ON BR.BranchID=SM.BranchID
                                                WHERE 
                                                 SM.SalaryMonth=%d 
                                                AND SMD.ITEMGROUP=%n AND smd.ITEMCODE IN (-101,-113) 
                                                AND SM.EmployeeID IN(%q)) tab1  
                                                GROUP BY tab1.EMPLOYEENO, tab1.[NAME], tab1.OUTPAYACCOUNTNO",
                                                dateTime, EnumSalaryGroup.Miscellaneous, sEmpID);

                oEuroBankAdvices = tc.ExecuteDataSet(sSQL);

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


        internal static DataSet GetEmpOPIBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID, int bankID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,Sum(odi.ChangeNetAmount) AS Amount 
                                                    FROM Employee e
                                                    Left Join OPIProcessDetail od on e.EmployeeID = od.EmployeeID 
                                                    LEFT JOIN OPIProcess o ON  od.OPIProcessID = o.OPIProcessID
                                                    LEFT JOIN OPIProcessDetailItem odi ON od.OPIProcessDetailID = odi.OPIProcessDetailID
                                                    WHERE o.OPIMonth = %d
                                                    AND odi.OPIType = %n
                                                  
                                                    AND od.EmployeeID IN(%q)
                                                    GROUP BY e.EmployeeNo,e.Name,od.AccountNo", dateTime, (int)EnumOpiType.Provision,  sEmpID);

                oBankAdvices = tc.ExecuteDataSet(sSQL);
                // Will Be Appended to Above Query
                //  AND od.BankID = %n And bankID,
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oBankAdvices;
        }
        internal static DataSet GetEmpCarFuelBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID, int bankID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,Sum(odi.ChangeNetAmount) AS Amount 
                                                    FROM Employee e
                                                    Left Join CarFuelProcessDetail od on e.EmployeeID = od.EmployeeID 
                                                    LEFT JOIN CarFuelProcess o ON  od.CarFuelProcessID = o.CarFuelProcessID
                                                    LEFT JOIN CarFuelProcessDetailItem odi ON od.CarFuelProcessDetailID = odi.CarFuelProcessDetailID
                                                    WHERE o.CarFuelMonth = %d
                                                    AND odi.CarFuelType = %n
                                                    AND od.BankID = %n
                                                    AND od.EmployeeID IN(%q)
                                                    GROUP BY e.EmployeeNo,e.Name,od.AccountNo", dateTime, (int)EnumCarFuelType.Payment, bankID, sEmpID);

                oBankAdvices = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oBankAdvices;
        }
        internal static DataSet GetHNMEmpOPIBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                //                string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,Sum(odi.ChangeNetAmount) AS Amount 
                //                                                    FROM Employee e,OPIProcess as o,
                //                                                    OPIProcessDetail AS od,OPIProcessDetailItem odi
                //                                                    WHERE o.OPIProcessID = od.OPIProcessID
                //                                                    AND od.OPIProcessDetailID = odi.OPIProcessDetailID
                //                                                    AND od.EmployeeID = e.EmployeeID
                //                                                    AND od.AccountNo IS NOT NULL
                //                                                    AND odi.OPIType =%n
                //                                                    AND o.OPIMonth = %d
                //                                                    AND od.EmployeeID IN(%q)
                //                                                    GROUP BY e.EmployeeNo,e.Name,e.EMAILADDRESS,od.AccountNo",
                //                                                    (int)EnumOpiType.Payment, dateTime, sEmpID);

                string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,cc.Code AS CostCenter,Sum(odi.ChangeNetAmount) AS Amount 
                                                    FROM Employee e 
                                                    INNER Join
                                                    OPIProcessDetail AS od
                                                    ON od.EmployeeID = e.EmployeeID
                                                    AND od.AccountNo IS NOT NULL
                                                    AND od.EmployeeID IN(%q)
                                                    INNER Join 
                                                    OPIProcessDetailItem odi
                                                    ON od.OPIProcessDetailID = odi.OPIProcessDetailID
                                                    AND odi.OPIType =%n
                                                    INNER Join 
                                                    OPIProcess as o
                                                    ON o.OPIProcessID = od.OPIProcessID
                                                    AND  o.OPIMonth = %d
                                                    LEFT JOIN EMPCOSTCENTER ecs
                                                    ON e.EMPLOYEEID = ecs.EMPLOYEEID
                                                    AND ecs.CurrentCC = 1
                                                    LEFT JOIN CRG AS cc
                                                    ON cc.CRGID = ecs.COSTCENTERID 
                                                    GROUP BY e.EmployeeNo,e.Name,e.EMAILADDRESS,od.AccountNo,cc.Code",
                                                    sEmpID, (int)EnumOpiType.Payment, dateTime);

                oBankAdvices = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oBankAdvices;
        }

        //internal static DataSet GetEmpExpenseAmount(TransactionContext tc, DateTime dateTime, string sEmpID)
        //{
        //    DataSet oEmpExpenseAmount = new DataSet();
        //    try
        //    {
        //        SELECT e.EmployeeNo,e.Name,e.EMAILADDRESS,od.AccountNo FROM Employee e,OPIProcess as o,
        //OPIProcessDetail AS od,OPIProcessDetailItem odi
        //WHERE o.OPIProcessID = od.OPIProcessID
        //AND od.OPIProcessDetailID = odi.OPIProcessDetailID
        //AND od.EmployeeID = e.EmployeeID
        //AND odi.OPIItemID=1
        //AND o.OPIMonth = '30 Apr 2014'

        //        oEmpExpenseAmount = tc.ExecuteDataSet("Select EMEx.EmployeeNo,SUM(Amount) as Amount from" 
        //                                             + " EmployeeMonthlyExpense as EMEx where"
        //                                             + " EMEx.EmployeeNo in(%q) "
        //                                             + " and EMEx.ExpenseMonth=%d"
        //                                             + " group by EMEx.EmployeeNo",
        //                                               sEmpID,dateTime);


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

        internal static DataSet GetEmpBankHistory(TransactionContext tc, DateTime dateTime, int payrollTypeID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"Select BA.EmpBankAccountID as AccountID,Emp.EMPLOYEEID as ID, Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,
                                   US.LoginID as CommitedBy,BA.CreationDate from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,
                                   BRANCHES as BR,USERS as US  
                                   where BA.EMPLOYEEID=Emp.EMPLOYEEID AND B.BANKID=BR.BANKID AND BA.BRANCHID=BR.BRANCHID AND US.UserID=BA.CreatedBy 
                                   AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId = %n order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dateTime),
                                   GlobalFunctions.LastDateOfMonth(dateTime), payrollTypeID);

                //oBankAdvices = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,US.LoginID as CommitedBy,BA.CreationDate" 
                //                                 + " from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,BRANCHES as BR,USERS as US "
                //                                 + " where BA.EMPLOYEEID=Emp.EMPLOYEEID"
                //                                 + " AND B.BANKID=BR.BANKID"
                //                                 + " AND BA.BRANCHID=BR.BRANCHID"
                //                                 + " AND US.UserID=BA.CreatedBy"
                //                                 + " AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId=%n" 
                //                                 + " order by Emp.EMPLOYEENO",GlobalFunctions.FirstDateOfMonth(dateTime),
                //                                 GlobalFunctions.LastDateOfMonth(dateTime), SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
                oBankAdvices = tc.ExecuteDataSet(sql);

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

        internal static DataSet GetEmpBankHistory(TransactionContext tc, DateTime dSFromDate, DateTime dSToDate, int payrollTypeID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"Select BA.EmpBankAccountID as AccountID,Emp.EMPLOYEEID as ID, Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,
                                   US.LoginID as CommitedBy,BA.CreationDate from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,
                                   BRANCHES as BR,USERS as US  
                                   where BA.EMPLOYEEID=Emp.EMPLOYEEID AND B.BANKID=BR.BANKID AND BA.BRANCHID=BR.BRANCHID AND US.UserID=BA.CreatedBy 
                                   AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId = %n order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dSFromDate),
                                   GlobalFunctions.LastDateOfMonth(dSToDate), payrollTypeID);

                //oBankAdvices = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,US.LoginID as CommitedBy,BA.CreationDate" 
                //                                 + " from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,BRANCHES as BR,USERS as US "
                //                                 + " where BA.EMPLOYEEID=Emp.EMPLOYEEID"
                //                                 + " AND B.BANKID=BR.BANKID"
                //                                 + " AND BA.BRANCHID=BR.BRANCHID"
                //                                 + " AND US.UserID=BA.CreatedBy"
                //                                 + " AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId=%n" 
                //                                 + " order by Emp.EMPLOYEENO",GlobalFunctions.FirstDateOfMonth(dateTime),
                //                                 GlobalFunctions.LastDateOfMonth(dateTime), SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
                oBankAdvices = tc.ExecuteDataSet(sql);

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


        internal static DataSet GetEmpPrvBankHistory(TransactionContext tc, DateTime dateTime, int payrollTypeID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                oBankAdvices = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,B.NAME as PBank,BA.ACCOUNTNO as PACCOUNTNO,BR.NAME as PBranch,BA.CHANGEDATE,US.UserName as CommitedBy,BA.CreationDate"
                                                 + " from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,BRANCHES as BR,USERS as US "
                                                 + " where BA.EMPLOYEEID=Emp.EMPLOYEEID"
                                                 + " AND B.BANKID=BR.BANKID"
                                                 + " AND BA.BRANCHID=BR.BRANCHID"
                                                 + " AND US.UserID=BA.CreatedBy"
                                                 + " AND BA.CHANGEDATE <%d ANd Emp.PayrollTypeiD=%n"
                                                 + " order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dateTime)
                                                 , payrollTypeID);

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

        internal static IDataReader GetByDate(TransactionContext tc, DateTime startDate, DateTime endDate, int payrollTypeID, string sEmpIDs)
        {
            try
            {
                string sql = "";
                IDataReader dr;
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL("Select * from BANKACCOUNTHISTORY where  CHANGEDATE between %d and %d AND EmployeeID in(select EmployeeID from EMPLOYEE where PAYROLLTYPEID=%n) order by CreationDate ", startDate, endDate, payrollTypeID);
                }
                else
                {                   
                    sql = SQLParser.MakeSQL("Select * from BANKACCOUNTHISTORY where  CHANGEDATE between %d and %d AND EmployeeID in(%q) order by CreationDate ", startDate, endDate, sEmpIDs);

                }
                dr = tc.ExecuteReader(sql);

               
                return dr;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region  Delete function

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

        internal static void DeleteByBankEffectDate(TransactionContext tc, DateTime effectDate, ID empID)
        {
            //string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d And CHANGEDATE <= (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,%d)+1,0))) and EMPLOYEEID=%n", effectDate, effectDate, empID.Integer);
            string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d and EMPLOYEEID=%n", effectDate, empID.Integer);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void DeleteByBankEffectDate(TransactionContext tc, DateTime effectDate, ID empID, EnumBankAccountType type)
        {
            //string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d And CHANGEDATE <= (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,%d)+1,0))) and EMPLOYEEID=%n", effectDate, effectDate, empID.Integer);
            string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d and EMPLOYEEID=%n and AccountType = %n", effectDate, empID.Integer, (int)type);
            tc.ExecuteNonQuery(sSQL);
        }
        internal static void DeleteAll(TransactionContext tc)
        {
            tc.ExecuteNonQuery("DELETE FROM [BANKACCOUNTHISTORY]");
        }


        #endregion

        internal static DataSet GetEmpBankAdvice(TransactionContext tc, DateTime salaryMonthDate, int bankID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,SM.AccountNo,SMD.CHANGEDAMOUNT,BR.Name as Branch,BR.Code
                                                FROM EMPLOYEE E,SalaryMonthly SM,SalaryMonthlyDetail SMD,BRANCHES as BR,Banks bn
                                                WHERE E.EMPLOYEEID=SM.EMPLOYEEID 
                                                AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
                                                AND BR.BranchID=SM.BranchID
                                                And bn.BankID = br.BankID
                                                AND SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
                                                AND SMD.ITEMGROUP=%n AND bn.BankId=%n
                                                order by E.EmployeeNo",
                                                salaryMonthDate, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, bankID);

                oBankAdvices = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oBankAdvices;
        }

        internal static DataSet GetEmpOPIBankAdvice(TransactionContext tc, DateTime salaryMonthDate, int bankID)
        {
            DataSet oBankAdvices = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,opd.AccountNo,Sum(opdi.ChangeNetAmount) AS CHANGEDAMOUNT,
                                                BR.Name as Branch,BR.Code
                                                FROM EMPLOYEE E, OPIProcess op,OPIProcessDetail opd,OPIProcessDetailItem opdi,
                                                BRANCHES as BR,Banks bn
                                                WHERE E.EMPLOYEEID = opd.EMPLOYEEID 
                                                AND opd.OPIProcessID = op.OPIProcessID
                                                AND opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                                AND BR.BranchID = opd.BranchID
                                                And bn.BankID = br.BankID
                                                AND op.OPIMonth = %d 
                                                AND bn.BankId = %n
                                                GROUP BY E.EMPLOYEENO,E.NAME,opd.AccountNo,BR.Name,BR.Code
                                                order by E.EmployeeNo",
                                                salaryMonthDate, bankID);

                oBankAdvices = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oBankAdvices;
        }

     
    }

    #endregion
}