using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Ease.Core.DataAccess;
using Ease.Core.DataAccess.SQL;
using HRM.BO;
using iTextSharp.text.pdf.qrcode;
using Microsoft.Data.SqlClient;
using NPOI.SS.Formula.Functions;

namespace HRM.DA
{
    #region EmployeeDA

    public class EmployeeDA
    {
        #region Constructor

        private EmployeeDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, Employee item)
        {
            string sSql = SQLParser.MakeSQL(@"INSERT INTO Employee(EmployeeID, globalID, employeeNo, 
                    name, lastchangedate, gender, birthDate,
                    joiningDate, endOfContractDate, emailAddress, mobileNo, tinNo, categoryID, 
                    foreignExPat, taxCircle, isConfirmed, status,
                    pFMemberType, pfMemberShipDt, branchID, accountNo, outPayBranchID, outPayAccountNo,
                    departmentID, locationID, religionID, maritalStatusID, designationID, gradeID, 
                    basicSalary, EmpHistoryID, prevBasic, paymentMode, fatherName, isEligibleOT, 
                    desigDescription, desktopUserPass, payrollTypeID,  cardID, grossSalary, 
                    payScaleId, taxAmount, dateOfConfirmation,VendorCode, CreatedBy,OutPayPaymentMode, 
                    CreationDate,MonthStatusUpdate,PersonType, LineManagerID, SecondLineManagerID,ProfileStatus, geid, extrafield1, extrafield2,
                    extrafield3, extrafield4, extrafield5, InsuranceId, InclusionDate,nationalID, NATIONALITYID)
                    VALUES(%n, %s, %s, %s, %d, %n, %d, 
                    %d, %d, %s, %s, %s, %n, 
                    %n, %n, %b, %n,  
                    %n, %d, %n, %s, %n, %s, 
                    %n, %n, %n, %n, %n, %n, 
                    %n, %n, %n, %n, %s, %n, 
                    %s, %s, %n, %n, %n, 
                    %n, %n, %d, %s, %n,%n, 
                    %d,%n,%n,%n,%n,%n, %s, %s, %n, %s, %s, %s, %s, %d,%s, %n)", item.ID, item.GlobalID, item.EmployeeNo, item.Name, DateTime.Today, item.Gender,
                DataReader.GetNullValue(item.BirthDate),
                DataReader.GetNullValue(item.JoiningDate), DataReader.GetNullValue(item.EndOfContractDate),
                item.EmailAddress, item.MobileNo, item.TinNo, DataReader.GetNullValue(item.CategoryID, 0),
                item.ForeignExPat, (int)item.TaxCircle, item.IsConfirmed, item.Status,
                item.PFMemberType, DataReader.GetNullValue(item.PFMemberShiptDate),
                item.BranchID, item.AccountNo,
                item.OutPayBranchID, item.OutPayAccountNo,
                item.DepartmentID, item.LocationID,
                item.ReligionID, item.MaritalStatus,
                item.DesignationID, item.GradeID,
                item.BasicSalary, item.CurrentHistoryID, item.PrevBasic, item.PaymentMode,
                item.FatherName, item.IsEligibleOT,
                item.DescriptionText, item.DesktopUserPass, DataReader.GetNullValue(item.PayrollTypeID, 0),
                item.CardID, item.GrossSalary,
                item.PayScaleId, item.TaxAmount, item.ConfirDate,
                DataReader.GetNullValue(item.VendorCode), item.CreatedBy,
                (int)item.OutPayPaymentMode, item.CreatedDate, item.MonthStatusUpdate,
                (int)item.PersonType, item.LineManagerID,
                item.SecondLineManagerID, item.ProfileStatus, item.GeId, item.ExtraField1, item.ExtraField2, item.ExtraField3,
                item.ExtraField4, item.ExtraField5, item.InsuranceId, item.InclusionDate, item.NationalId, item.NationalityID);
            tc.ExecuteNonQuery(sSql);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, Employee item)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE Employee SET "
                                            + "globalID=%s, employeeNo=%s, name=%s, gender=%n, birthDate=%d, joiningDate=%d, "
                                            + "endOfContractDate=%d, emailAddress=%s, mobileNo=%s, tinNo=%s, categoryID=%n, foreignExPat=%n, "
                                            + "taxCircle=%s, isConfirmed=%b, status=%n, pFMemberType=%n, "
                                            + "pfMemberShipDt=%d, branchID=%n, accountNo=%s, OUTPAYBRANCHID=%n, outPayAccountNo=%s, departmentID=%n, "
                                            + "locationID=%n, religionID=%n, MARITALSTATUSID=%n, designationID=%n, gradeID=%n, basicSalary=%n, "
                                            + "EmpHistoryID=%n, prevBasic=%n, paymentMode=%n, fatherName=%s, isEligibleOT=%n, desigDescription=%s, "
                                            + "DESKTOPUSERPASS=%s, payrollTypeID=%n,  cardID=%n, grossSalary=%n,"
                                            + "payScaleId=%n, taxAmount=%n, dateOfConfirmation=%d,VendorCode=%s, ModifiedBy=%n, ModifiedDate=%d, "
                                            + "MonthStatusUpdate=%n,OutPayPaymentMode=%n ,PersonType=%n, LineManagerID=%n, SecondLineManagerID=%n,ProfileStatus = %n, geid=%s, extrafield1=%s, extrafield2=%n, "
                                            + "extrafield3=%s, extrafield4=%s, extrafield5=%s, InsuranceId=%s, InclusionDate=%d, IsFixedLocation=%b,nationalId=%s, NATIONALITYID =%n "
                                            + " WHERE EmployeeID=%n", item.GlobalID, item.EmployeeNo,
                DataReader.GetNullValue(item.Name), item.Gender, DataReader.GetNullValue(item.BirthDate),
                DataReader.GetNullValue(item.JoiningDate),
                item.EndOfContractDate, item.EmailAddress, item.MobileNo, item.TinNo,
                DataReader.GetNullValue(item.CategoryID, 0), item.ForeignExPat,
                (int)item.TaxCircle, item.IsConfirmed, item.Status, item.PFMemberType,
                item.PFMemberShiptDate, item.BranchID, item.AccountNo,
                item.OutPayBranchID, item.OutPayAccountNo,
                item.DepartmentID,
                item.LocationID, item.ReligionID,
                item.MaritalStatus, item.DesignationID,
                item.GradeID, item.BasicSalary,
                item.CurrentHistoryID, item.PrevBasic, item.PaymentMode, item.FatherName,
                item.IsEligibleOT, item.DescriptionText,
                DataReader.GetNullValue(item.DesktopUserPass), item.PayrollTypeID,
                item.CardID, item.GrossSalary,
                item.PayScaleId, item.TaxAmount, item.ConfirDate,
                item.VendorCode, item.ModifiedBy, item.ModifiedDate,
                item.MonthStatusUpdate, (int)item.OutPayPaymentMode, (int)item.PersonType,
                item.LineManagerID, item.SecondLineManagerID,
                item.ProfileStatus, item.GeId, item.ExtraField1, item.ExtraField2, item.ExtraField3,
                item.ExtraField4, item.ExtraField5, item.InsuranceId, item.InclusionDate, item.IsFixedLocation, item.NationalId, item.NationalityID, item.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void InsertEmpContact(TransactionContext tc, EmpContact item)
        {
            tc.ExecuteNonQuery("INSERT INTO EMPCONTACT(ContactID, EmployeeID, PARMANENTADDRESS, PARMANENTTELEPHONE, PRESENTADDRESS, Mobile, PERSONALEMAIL,OFFICIALEMAIL,FAX,EMERGENCYCONTACTADDRESS,EMERGENCYCONTACTPERSON,EMERGENCYTELEPHONE)" +
            " VALUES(%n,  %n,  %s,  %s,%s, %s,  %s, %s, %s, %s, %s,%s)",
            item.ID, item.EmployeeID, item.PermanentAddress, item.PermanentTelephone,
            item.PresentAddress, item.Mobile, item.PersonalEmail, item.OfficalEmail, item.Fax, item.EmergencyContactAddress,
            item.EmergencyContactPerson, item.EmergencyTelephone);
        }

        internal static void UpdateDepartment(TransactionContext tc, Employee item)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE Employee SET  departmentID=%n WHERE EmployeeID=%n",
                item.DepartmentID, item.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void UpdateProfile(TransactionContext tc, Employee item)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE Employee SET  ProfileStatus=%n WHERE EmployeeID=%n",
                item.ProfileStatus, item.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        public static void UpdateGross(TransactionContext tc, int EmployeeID, double? dGross)
        {
            if (dGross == null)
                dGross = 0;
            tc.ExecuteNonQuery("UPDATE Employee SET grossSalary=%n " +
                               " WHERE EmployeeID=%n", dGross, EmployeeID);
        }

        public static void UpdateGradeSalary(TransactionContext tc, int employeeid, int gradeId,
            double basicSalary, double grossSalary)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET gradeid=%n, BasicSalary=%n, GrossSalary=%n " +
                               " WHERE EmployeeID=%n", DataReader.GetNullValue(gradeId), basicSalary, grossSalary,
                employeeid);
        }

        public static void UpdateSalary(TransactionContext tc, int employeeid,
            double basicSalary, double grossSalary)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET BasicSalary=%n, GrossSalary=%n " +
                               " WHERE EmployeeID=%n", basicSalary, grossSalary, employeeid);
        }
        internal static void UpdatePayrollType(TransactionContext tc, int employeeID, int payrollTypeID)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET PAYROLLTYPEID = %n,LINEMANAGERID = null WHERE EMPLOYEEID = %n", payrollTypeID, employeeID);
        }
        public static void UpdateStatus(TransactionContext tc, int employeeID, int employeeHistoryId,
                DateTime endofContactDate, EnumEmployeeStatus status)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET Status= %n, EmpHistoryID=%n, endOfContractDate=%d WHERE EMPLOYEEID=%n",
                (int)status, employeeHistoryId, endofContactDate, employeeID);
        }

        public static void salaryWithHeld(TransactionContext tc, int employeeID, bool status)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET IsSalaryWithHeld= %n WHERE EMPLOYEEID=%n",
                 status, employeeID);
        }

        internal static IDataReader GetEmployeeByCoordinator(TransactionContext tc, int corID)
        {
            return tc.ExecuteReader(@"SELECT * FROM EMPLOYEE E JOIN employeeCordinator ECID ON
                                    E.EMPLOYEEID = ECID.EmployeeID
                                    WHERE ECID.CordinatorID =  %n", corID);
        }

        internal static DataTable GetNameAndMail(TransactionContext tc, int empid)
        {
            return tc.ExecuteDataTable(@"SELECT EmployeeID, EmployeeNo employeeno, name name , EmailAddress emailaddress, lineManagerid linemanagerid FROM EMPLOYEE E 
                                    WHERE E.EmployeeID =  %n", empid);
        }

        internal static DataTable GetNameAndMailWithNoLock(TransactionContext tc, int empid)
        {
            return tc.ExecuteDataTable(@"SELECT EmployeeID, EmployeeNo employeeno, name name, EmailAddress emailaddress, lineManagerid linemanagerid FROM EMPLOYEE With(NoLock)
                                    WHERE EmployeeID =  %n", empid);
        }

        internal static IDataReader GetAbsentEmployees(TransactionContext tc, DateTime fromDate, DateTime toDate)
        {
            string sql = SQLParser.MakeSQL(@"
            SELECT e.* FROM EMPLOYEE e
            LEFT JOIN DAILYATTNPROCESS d ON e.EMPLOYEEID = d.EMPLOYEEID
            WHERE d.AttenType = %n AND e.STATUS = %n AND e.EMAILADDRESS IS NOT NULL AND
            d.AttnDate BETWEEN %D AND %D",
            EnumAttendanceType.Absent, EnumEmployeeStatus.Live, fromDate, toDate);

            return tc.ExecuteReader(sql);
        }

        public static void UpdatemiddleOfMonthDiscontinue(TransactionContext tc, int employeeID,
            EnumEmployeeStatus status, DateTime endofContactDate)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET status=%n, MonthStatusUpdate= %n, endOfContractDate=%d WHERE EMPLOYEEID=%n",
                EnumEmployeeStatus.Live, (int)status, endofContactDate, employeeID);
        }

        public static void UpdateOTFlag(TransactionContext tc, int nEmployeeID)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET isEligibleOT= %b where EmployeeID=%n", true, nEmployeeID);
        }

        //for employee continue
        public static void DoContinue(TransactionContext tc, int employeeID)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET Status= %n, MonthStatusUpdate= %n, EmpHistoryID=null, endOfContractDate=null WHERE EMPLOYEEID=%n",
                (int)EnumEmployeeStatus.Live, (int)EnumEmployeeStatus.Live, employeeID);
        }

        public static void DoContinueForLifeCycle(TransactionContext tc, int employeeID, DateTime dtEffectDate)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET Status= %n, MonthStatusUpdate= %n, EmpHistoryID=null, endOfContractDate=null WHERE EMPLOYEEID=%n",
                (int)EnumEmployeeStatus.Live, (int)EnumEmployeeStatus.Live, employeeID);
        }

        internal static IDataReader GetByEmpNos(TransactionContext tc, string sEmpIDs)
        {
            string sql = SQLParser.MakeSQL(@"SELECT * FROM EMPLOYEE WHERE EMPLOYEEID IN (%q)", sEmpIDs);
            return tc.ExecuteReader(sql);
        }

        public static void DoContinueForLifeCycleForOldEmployee(TransactionContext tc, int employeeID)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET Status= %n, MonthStatusUpdate= %n, EmpHistoryID=null, endOfContractDate=null WHERE EMPLOYEEID=%n",
                (int)EnumEmployeeStatus.Live, (int)EnumEmployeeStatus.Live, employeeID);
        }

        //added by Hasib 04.05.10
        internal static void UpdateCardInformation(TransactionContext tc, int employeeID, int CardID,
            bool IsAutoProcess)
        {
            //tc.ExecuteNonQuery("UPDATE Employee SET CardID= %n, IsAutoProcess=%b WHERE EMPLOYEEID=%n",
            //    DataReader.GetNullValue(CardID, 0), IsAutoProcess, employeeID);
            tc.ExecuteNonQuery("UPDATE Employee SET CardID= %n, IsAutoProcess=%b WHERE EMPLOYEEID=%n",
                CardID, IsAutoProcess, employeeID);
        }

        internal static void UpdateEmpCardHistory(TransactionContext tc, int employeeID, int CardID, DateTime cardDate,
            string cardNumber)
        {
            if (CardID == 0)
            {
                tc.ExecuteNonQuery(
                    @"Update EmpCardHistory Set TillDate = %d Where EmployeeID = %n and TillDate is Null", cardDate,
                    employeeID);
            }
            else
            {
                tc.ExecuteNonQuery("INSERT INTO EmpCardHistory(EmployeeID,CardID,CardNumber,AssignDate)" +
                                   " VALUES(%n, %n, %s,%d)", employeeID, CardID, cardNumber, cardDate);
            }
        }

        internal static DataSet GetAbsentEmployeesForLM(TransactionContext tc, DateTime fromDate, DateTime toDate, EnumAttendanceType attnType)
        {
            string sql = SQLParser.MakeSQL(@"
            SELECT 
            lm.EMPLOYEENO LMNO, lm.NAME LMNAME, lm.EMAILADDRESS LMEMAILADDRESS,
            e.EMPLOYEENO EMPLOYEEID, e.NAME EMPNAME, d.NAME EMPDESIGNATION, e.JOININGDATE, e.MOBILENO, e.EMAILADDRESS EMPEMAILADDRESS
            FROM EMPLOYEE e
            LEFT JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID
            LEFT JOIN DAILYATTNPROCESS dap ON e.EMPLOYEEID = dap.EMPLOYEEID
            LEFT JOIN EMPLOYEE lm ON lm.EMPLOYEEID = e.LINEMANAGERID
            WHERE dap.AttenType = %n AND e.STATUS = %n AND lm.EMAILADDRESS IS NOT NULL AND dap.AttnDate BETWEEN %d AND %d
            GROUP BY lm.EMPLOYEENO, lm.NAME, lm.EMAILADDRESS, e.EMPLOYEENO, e.NAME, d.NAME, e.JOININGDATE, e.MOBILENO, e.EMAILADDRESS",
            attnType, EnumEmpStatus.Live, fromDate, toDate);

            return tc.ExecuteDataSet(sql);
        }

        internal static IDataReader GetAllHREmpsWorkAniversary(TransactionContext tc, string payrollTypeId)
        {
            string sql = SQLParser.MakeSQL(@"
            SELECT EMPLOYEEID, EMPLOYEENO, NAME, PAYROLLTYPEID, STATUS, LINEMANAGERID, SecondLineManagerID FROM EMPLOYEE  e
            WHERE MONTH(e.JOININGDATE) = MONTH(GETDATE()) AND
            DAY(e.JOININGDATE) = DAY(GETDATE()) AND 
            e.PAYROLLTYPEID in (%s) AND
            e.STATUS = 1", payrollTypeId);

            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllHREmpsBirthday(TransactionContext tc, string payrollTypeId)
        {
            string sql = SQLParser.MakeSQL(@"
            SELECT EMPLOYEEID, EMPLOYEENO, NAME, PAYROLLTYPEID, STATUS, LINEMANAGERID, SecondLineManagerID FROM EMPLOYEE  e
            WHERE MONTH(e.BIRTHDATE) = MONTH(GETDATE()) AND
            DAY(e.BIRTHDATE) = DAY(GETDATE()) AND 
            e.PAYROLLTYPEID in (%s) AND
            e.STATUS = 1", payrollTypeId);

            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllAbsentOnYesterdayEmps(TransactionContext tc, string payrollTypeId)
        {
            //string sql = SQLParser.MakeSQL(@"
            //SELECT e.EMPLOYEEID, e.EMPLOYEENO, e.NAME, e.PAYROLLTYPEID, e.STATUS, e.LINEMANAGERID, e.SecondLineManagerID, e.EMAILADDRESS FROM EMPLOYEE e
            //LEFT JOIN DAILYATTNPROCESS d ON e.EMPLOYEEID = d.EMPLOYEEID
            //WHERE d.AttenType = %n AND e.STATUS = %n AND e.EMAILADDRESS IS NOT NULL AND e.PAYROLLTYPEID = %n AND
            //d.AttnDate = %d", EnumAttendanceType.Absent, EnumEmployeeStatus.Live, payrollTypeId, DateTime.Today.AddDays(-1));

            string sql = SQLParser.MakeSQL(@"
            SELECT e.* FROM EMPLOYEE e
            LEFT JOIN DAILYATTNPROCESS d ON e.EMPLOYEEID = d.EMPLOYEEID
            WHERE d.AttenType = %n AND e.STATUS = %n AND e.EMAILADDRESS IS NOT NULL AND e.PAYROLLTYPEID IN ( %q ) AND
            d.AttnDate = %d", EnumAttendanceType.Absent, EnumEmployeeStatus.Live, payrollTypeId, DateTime.Today.AddDays(-1));

            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllAbsentOnYesterdayEmpLM(TransactionContext tc, int empId, int payrollTypeId)
        {
            string sql = SQLParser.MakeSQL(@"
            SELECT e.* FROM EMPLOYEE e
            WHERE e.EMPLOYEEID=%n AND e.STATUS = %n AND e.EMAILADDRESS IS NOT NULL 
            AND e.PAYROLLTYPEID = %n", empId, EnumEmployeeStatus.Live, payrollTypeId);

            return tc.ExecuteReader(sql);
        }
        internal static DataSet GetLinemanagerPendingList(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"
            WITH cte AS (
	            SELECT TOEMPLOYEEID EMPLOYEEID, 4 TYPE, count(*) TOTLALCOUNT
	            FROM DELEGATERESPONSIBILITY
	            WHERE ISACCEPTED=0 
	            AND Status=1
	            GROUP BY TOEMPLOYEEID
	            HAVING count(*) > 0
				
	            UNION
	
	            SELECT LINEMANAGERID EMPLOYEEID, 3 TYPE, count(*) TOTLALCOUNT 
	            FROM GATEPASS
	            WHERE CLAIMWFSTATUS=1
	            GROUP BY LINEMANAGERID
	            HAVING count(*) > 0
				
	            UNION
	
	            SELECT e.LINEMANAGERID EMPLOYEEID, 2 TYPE, count(*) TOTLALCOUNT
	            FROM DAILYATTNPROCESS d
	            JOIN EMPLOYEE e ON d.EMPLOYEEID = e.EMPLOYEEID
	            WHERE d.WFStatus = 1
	            GROUP BY e.LINEMANAGERID
	            HAVING count(*) > 0
				
	            UNION
	
	            SELECT WN.EmployeeId EMPLOYEEID, 1 TYPE, count(*) TOTLALCOUNT 
	            FROM WFMovementTran WT, WFMovementNext WN, Employee emp  
	            WHERE WN.Status=1
	            AND Emp.EMPLOYEEID=WT.FROMEMPLOYEEID
	            AND WT.WFMovementTranID = WN.WFMovementTranID
	            GROUP BY WN.EmployeeId
	            HAVING count(*) > 0
            )
            SELECT e.EMPLOYEENO, e.NAME, e.EMAILADDRESS, c.* FROM EMPLOYEE e
            INNER JOIN cte c ON e.EMPLOYEEID = c.EMPLOYEEID
            WHERE e.EMAILADDRESS IS NOT NULL AND e.EMAILADDRESS != ''");

            return tc.ExecuteDataSet(sql);
        }

        //For PF

        internal static void UpdatePFMemship(TransactionContext tc, int empID, EnumPFMembershipType PFType,
            DateTime dPFEffectDate)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET PFMEMBERTYPE= %n, PFMEMBERSHIPDT=%d WHERE EMPLOYEEID=%n", PFType,
                dPFEffectDate, empID);
        }

        internal static void UpdateGratuityContinuation(TransactionContext tc, int empID, bool GratuityContinue,
            DateTime dPFEffectDate)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET CONTINUEGRATUITY= %b WHERE EMPLOYEEID=%n", GratuityContinue, empID);
        }
        // For Confirm Employee

        //internal static void UpdateConfirmation(TransactionContext tc, int empID, EnumEmployeeOtherStatus confirmation, DateTime dConfirmEffectDate,bool confirmEmpPF)
        //{
        //    tc.ExecuteNonQuery("UPDATE Employee SET pFMemberType=%b,pfMemberShipDt=%d,IsConfirmed= %b,DateOfConfirmation=%d WHERE EMPLOYEEID=%n",confirmEmpPF,dConfirmEffectDate,confirmation,dConfirmEffectDate);
        //}

        // For Update Employee Posting
        internal static void UpdateEmpPosting(TransactionContext tc, EmployeePosting oEmpPosting)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET DEPARTMENTID= %n,LocationID=%n,DESIGNATIONID=%n WHERE EMPLOYEEID=%n",
                DataReader.GetNullValue(oEmpPosting.DepartmentID, 0),
                DataReader.GetNullValue(oEmpPosting.LocationID, 0),
                DataReader.GetNullValue(oEmpPosting.DesignationID, 0), oEmpPosting.EmployeeID);
        }

        // For Tax Amount
        public static void UpdateTaxAmount(TransactionContext tc, int employeeID, double TaxAmount)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET TAXAMOUNT= %n WHERE EMPLOYEEID=%n",
                TaxAmount, employeeID);
        }

        // For Employee Role
        public static void UpdateEmpRole(TransactionContext tc, Employee oEmp)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET Role= %n WHERE EMPLOYEEID=%n",
                oEmp.Role, oEmp.ID);
        }

        // For Employee Confirm

        public static void DoConfirm(TransactionContext tc, int employeeID, DateTime confirmDate, DateTime pfMShipDate,
            bool IsConfirm, bool IsPFMember)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET ISCONFIRMED= %b,DateOfConfirmation=%d,PFMEMBERTYPE=%b,PFMEMBERSHIPDT=%d WHERE EMPLOYEEID=%n",
                IsConfirm, confirmDate, IsPFMember, pfMShipDate, employeeID);
        }

        public static void DoConfirm(TransactionContext tc, int employeeID, DateTime confirmDate, bool IsConfirm)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET ISCONFIRMED= %b,DateOfConfirmation=%d WHERE EMPLOYEEID=%n",
                IsConfirm, confirmDate, employeeID);
        }

        //For Undo Employee
        public static void UndoConfirm(TransactionContext tc, int employeeID, bool IsConfirm)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET ISCONFIRMED= %b,DateOfConfirmation=null WHERE EMPLOYEEID=%n",
                IsConfirm, employeeID);
        }

        //For Transfer Employee
        public static void UpdatePayrollType(TransactionContext tc, int employeeID, int payrollTypeID,
            DateTime dEffectDate)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET PAYROLLTYPEID=%n,JoiningDate=%d,categoryID=%n WHERE EMPLOYEEID=%n",
                payrollTypeID, dEffectDate, payrollTypeID, employeeID);

            tc.ExecuteNonQuery("DELETE FROM IncomeTaxTemp WHERE EMPLOYEEID=%n", employeeID);
        }


        // For Employee Bank Accoutn

        public static void UpdateBankAcc(TransactionContext tc, int employeeID, int branchID, string accountNo,
            EnumPaymentMode paymentMode)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET paymentMode=%n, branchID=%n, accountNo=%s WHERE EMPLOYEEID=%n",
                paymentMode, DataReader.GetNullValue(branchID, 0), accountNo, employeeID);
        }

        public static void UpdateOPIBankAcc(TransactionContext tc, int employeeID, int branchID, string accountNo,
            EnumPaymentMode paymentMode)
        {
            tc.ExecuteNonQuery(
                "UPDATE Employee SET OutPayPaymentMode=%n, OutPayBranchID=%n, OutPayAccountNo=%s WHERE EMPLOYEEID=%n",
                paymentMode, DataReader.GetNullValue(branchID, 0), accountNo, employeeID);
        }

        internal static void UpdateContinueGratuity(TransactionContext tc, int employeeID, double continueGratuity)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET ContinueGratuity=%n " + " WHERE EmployeeID=%n", continueGratuity,
                employeeID);
        }

        public static void UpdateLineManager(TransactionContext tc, Employee employee)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET LineManagerID= %n WHERE EMPLOYEEID=%n",
                employee.LineManagerID, employee.ID);
        }
        public static void UpdateLineManager(TransactionContext tc, int EmployeeID, string LineManagerEmpNo)
        {
            tc.ExecuteNonQuery(@"UPDATE Employee SET LineManagerID= (select EmployeeID from Employee
                 where EmployeeNo=%s) WHERE EMPLOYEEID=%n", LineManagerEmpNo, EmployeeID);
        }
        #endregion

        #region Get Function

        internal static IDataReader GetForSuperUser(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n order by EmployeeNo",
                EnumEmployeeStatus.Live);
            return tc.ExecuteReader(sql);
        }

        internal static DataSet GetDepartmentName(TransactionContext tc, int empID, bool banglaDescriptionExits)
        {
            string sql = "";
            if (banglaDescriptionExits)
            {
                sql = SQLParser.MakeSQL(@"select  

                                            E.Employeeid,

                                            'Department' = case 
                                            when dept5.tire = 5 THEN  ISNULL(dept2.[DESCRIPTION],'')
                                            when dept5.tire = 4 then ISNULL(dept3.[DESCRIPTION],'')
                                            when dept5.tire = 3 then ISNULL(dept4.[DESCRIPTION],'')
                                            when dept5.tire = 2 then ISNULL(dept5.[DESCRIPTION],'')
                                            END,
                                            'DepartmentBangla' = case 
                                            when dept5.tire = 5 then ISNULL(dept2.BanglaDescription,'')
                                            when dept5.tire = 4 then ISNULL(dept3.BanglaDescription,'')
                                            when dept5.tire = 3 then ISNULL(dept4.BanglaDescription,'')
                                            when dept5.tire = 2 then ISNULL(dept5.BanglaDescription,'')
                                            END,
                                            'DepartmentID' = case 
                                            when dept5.tire = 5 then dept2.DepartmentID
                                            when dept5.tire = 4 then dept3.DepartmentID
                                            when dept5.tire = 3 then dept4.DepartmentID
                                            when dept5.tire = 2 then dept5.DepartmentID
                                            END
                                            from
                                            Employee E
		
      
                                            Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = E.DEPARTMENTID
                                            LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
                                            LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
                                            LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
                                            LEFT JOIN DEPARTMENT dept1 ON dept1.DEPARTMENTID = dept2.PARENTID
                                            WHERE E.EmployeeID = %n

                                            Group BY 
                                            E.EmployeeID
                                            ,dept1.TIRE,dept1.[DESCRIPTION],dept1.BANGLADescription,dept1.DepartmentID
                                            ,dept2.TIRE,dept2.[DESCRIPTION],dept2.BANGLADescription,dept2.DepartmentID
                                            ,dept3.TIRE,dept3.[DESCRIPTION],dept3.BANGLADescription,dept3.DepartmentID
                                            ,dept4.TIRE,dept4.[DESCRIPTION],dept4.BANGLADescription,dept4.DepartmentID
                                            ,dept5.TIRE,dept5.[DESCRIPTION],dept5.BANGLADescription,dept5.DepartmentID", empID);
            }
            else
            {
                sql = SQLParser.MakeSQL(@"select  

                                            E.Employeeid,

                                            'Department' = case 
                                            when dept5.tire = 5 THEN  ISNULL(dept2.[DESCRIPTION],'')
                                            when dept5.tire = 4 then ISNULL(dept3.[DESCRIPTION],'')
                                            when dept5.tire = 3 then ISNULL(dept4.[DESCRIPTION],'')
                                            when dept5.tire = 2 then ISNULL(dept5.[DESCRIPTION],'')
                                            END,
                                            'DepartmentID' = case 
                                            when dept5.tire = 5 then dept2.DepartmentID
                                            when dept5.tire = 4 then dept3.DepartmentID
                                            when dept5.tire = 3 then dept4.DepartmentID
                                            when dept5.tire = 2 then dept5.DepartmentID
                                            END
                                            from
                                            Employee E
                                            Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = E.DEPARTMENTID
                                            LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
                                            LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
                                            LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
                                            LEFT JOIN DEPARTMENT dept1 ON dept1.DEPARTMENTID = dept2.PARENTID
                                            WHERE E.EmployeeID = %n

                                            Group BY 
                                            E.EmployeeID
                                            ,dept1.TIRE,dept1.[DESCRIPTION],dept1.DepartmentID
                                            ,dept2.TIRE,dept2.[DESCRIPTION],dept2.DepartmentID
                                            ,dept3.TIRE,dept3.[DESCRIPTION],dept3.DepartmentID
                                            ,dept4.TIRE,dept4.[DESCRIPTION],dept4.DepartmentID
                                            ,dept5.TIRE,dept5.[DESCRIPTION],dept5.DepartmentID", empID);
            }
            return tc.ExecuteDataSet(sql);
        }

        //previoslyGet
        internal static IDataReader GetWithPayrollType(TransactionContext tc, int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n order by EmployeeNo",
                EnumEmployeeStatus.Live, payrollTypeID);
            return tc.ExecuteReader(sql);
        }


        internal static IDataReader GetWithDiscontinue(TransactionContext tc, int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n order by EmployeeNo",
                payrollTypeID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllSubordinatesNew(TransactionContext tc, int employeeID)
        {
            //For Oracle
            //            string sql = SQLParser.MakeSQL(@"SELECT emp.* FROM EMPLOYEE emp,
            //											(SELECT t1.ORGANOGRAMID
            //											FROM ORGANOGRAM t1
            //											LEFT JOIN ORGANOGRAM t2 ON t2.ORGANOGRAMID = t1.parentid
            //											START WITH t1.ORGANOGRAMID = (SELECT nodeid FROM ORGANEMPLOYEE WHERE EMPLOYEEID=%n)
            //											CONNECT BY PRIOR t1.ORGANOGRAMID = t1.parentid) nodes,
            //											ORGANEMPLOYEE orgemp
            //											WHERE emp.employeeid=orgemp.EMPLOYEEID
            //											AND nodes.ORGANOGRAMID=orgemp.NODEID",employeeID);
            //            return tc.ExecuteReader(sql);

            //For SqlServer
            string sql = SQLParser.MakeSQL(@"with cte as 
                                                (
                                                select 1 lvl, ParentId, organogramid from organogram where Parentid = (SELECT nodeid FROM organemployee WHERE employeeid=%n)
                                                union all
                                                select cte.lvl + 1, organogram.ParentId, organogram.organogramid from organogram join cte on cte.organogramid = organogram.ParentId
                                                )
                                                select emp.* from cte,organemployee oe,employee emp
                                                WHERE cte.organogramid=oe.NodeID 
                                                AND oe.EmployeeID=emp.EMPLOYEEID ",
                employeeID);
            return tc.ExecuteReader(sql);
        }

        public static IDataReader GetAllSubordinates(TransactionContext tc, string sql)
        {
            return tc.ExecuteReader(sql);
        }

        //for Attendance Process
        internal static IDataReader GetAbsentEmp(TransactionContext tc, DateTime attnDate, EnumWorkPlanGroup wpGroup,
            int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND JOININGDATE <=%d"
                + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID = %n) "
                + "AND EMPLOYEEID NOT IN(SELECT EMPLOYEEID FROM DailyAttnProcess WHERE AttnDate = %d) order by EmployeeNo",
                EnumEmployeeStatus.Live, payrollTypeID, attnDate, wpGroup, attnDate);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAttendaceEmp(TransactionContext tc, EnumWorkPlanGroup wpGroup, int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n "
                                           + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID = %n) order by EmployeeNo ",
                EnumEmployeeStatus.Live, payrollTypeID, wpGroup);
            return tc.ExecuteReader(sql);
        }


        internal static IDataReader GetMonthlyAttnEmployee(TransactionContext tc, DateTime fromDate, DateTime toDate,
            int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL(@"SELECT DISTINCT e.* FROM EMPLOYEE e 
											INNER JOIN DAILYATTNPROCESS dap ON dap.EMPLOYEEID=e.EMPLOYEEID
											WHERE dap.ATTNDATE BETWEEN %d AND %d AND e.PAYROLLTYPEID=%n AND e.STATUS=%n Order By e.EmployeeNo asc",
                fromDate, toDate, payrollTypeID, EnumEmployeeStatus.Live);
            return tc.ExecuteReader(sql);
        }


        internal static IDataReader GetEmpExceptAutoWP(TransactionContext tc, DateTime attnDate, int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND JOININGDATE <=%d"
                + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID NOT IN(%n)) order by EmployeeNo ",
                EnumEmployeeStatus.Live, payrollTypeID, attnDate, EnumWorkPlanGroup.Counter_Clock_1);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetDiscontinueEmp(TransactionContext tc, DateTime attnDate, DateTime currentDate,
            int payrollTypeID)
        {
            tc.CommandTimeOut = 6000;
            string sql = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE ENDOFCONTRACTDATE BETWEEN %d AND %d and PAYROLLTYPEID = %n "
                + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID NOT IN(%n)) order by EmployeeNo ",
                attnDate, currentDate, payrollTypeID, EnumWorkPlanGroup.Counter_Clock_1);
            return tc.ExecuteReader(sql);
        }

        //
        internal static IDataReader GetEmpsWithDiscontinue(TransactionContext tc, string empIDs, int payrollTypeID)
        {
            string sTableName;
            empIDs = IDHelper.GetIDs(tc, empIDs, out sTableName);

            IDataReader dr =
                tc.ExecuteReader(
                    "SELECT * FROM Employee WHERE EMPLOYEEID IN(%q) AND PAYROLLTYPEID = %n order by EmployeeNo",
                    empIDs, payrollTypeID);
            if (sTableName != "")
                tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            return dr;
        }

        internal static IDataReader GetByEmpIDs(TransactionContext tc, string empIDs, int payrollTypeID)
        {
            string sTableName;
            empIDs = IDHelper.GetIDs(tc, empIDs, out sTableName);
            IDataReader dr =
                tc.ExecuteReader(
                    "SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EMPLOYEEID IN(%q) order by EmployeeNo",
                    payrollTypeID, empIDs);
            if (sTableName != "")
                tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            return dr;
        }
        internal static void UpdateBanglaInformation(TransactionContext tc, List<object> employeBanglaInfo)
        {
            tc.ExecuteNonQuery("UPDATE EMPLOYEE SET BANGLANAME = %s, SPOUSENAMEBANGLA = %s, FatherNameBangla = %s, MotherNameBangla =%s, ModifiedDate =%s, ModifiedBy=%n WHERE EMPLOYEENO = %s", employeBanglaInfo[1], employeBanglaInfo[2], employeBanglaInfo[3], employeBanglaInfo[4], employeBanglaInfo[5], employeBanglaInfo[6], employeBanglaInfo[0]);

        }
        internal static void UpdateBanglaContactInformation(TransactionContext tc, List<object> employeBanglaInfo)
        {
            tc.ExecuteNonQuery("UPDATE EMPCONTACT SET presentPOInBangla = %s, presentAddressInBangla = %s, parmanentPOInBangla = %s, permanentAddressInBangla =%s WHERE EMPLOYEEID = %s", employeBanglaInfo[1], employeBanglaInfo[2], employeBanglaInfo[3], employeBanglaInfo[4], employeBanglaInfo[0]);

        }
        internal static IDataReader GetByEmpIDs(TransactionContext tc, string empIDs)
        {
            string sTableName;
            empIDs = IDHelper.GetIDs(tc, empIDs, out sTableName);
            IDataReader dr =
                tc.ExecuteReader(
                    "SELECT * FROM Employee WHERE EMPLOYEEID IN(%q) order by EmployeeNo", empIDs);
            if (sTableName != "")
                tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            return dr;
        }

        internal static IDataReader GetFssEmp(TransactionContext tc, string empIDs, int payrollTypeID)
        {
            string sTableName;
            empIDs = IDHelper.GetIDs(tc, empIDs, out sTableName);
            IDataReader dr =
                tc.ExecuteReader(
                    "SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EMPLOYEEID IN(%q) order by EmployeeNo",
                    payrollTypeID, empIDs);
            if (sTableName != "")
                tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            return dr;
        }

        internal static IDataReader GetByDeptIDs(TransactionContext tc, string sIDs, int payrollTypeID)
        {
            string sTableName;
            sIDs = IDHelper.GetIDs(tc, sIDs, out sTableName);
            IDataReader dr =
                tc.ExecuteReader(
                    "SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND DEPARTMENTID IN(%q) order by EmployeeNo",
                    EnumEmployeeStatus.Live, payrollTypeID, sIDs);
            if (sTableName != "")
                tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            return dr;
        }


        internal static IDataReader GetSubordinates(TransactionContext tc, int employeeID)
        {
            return tc.ExecuteReader(@"Select * From Employee Where EmployeeID IN(
				   Select EmployeeID from OrganEmployee Where NodeID IN(
				   Select OrganogramID From Organogram Where ParentID=(
				   Select NodeID from OrganEmployee Where EmployeeID=%n)))", employeeID);
        }

        internal static IDataReader GetTopMostEmployees(TransactionContext tc)
        {
            return tc.ExecuteReader(@"Select * From Employee Where EmployeeID IN(
				   Select EmployeeID from OrganEmployee Where NodeID IN(
				   Select OrganogramID From Organogram Where Tier=1))order by Employeeno");
        }

        public static IDataReader GetSubordinates(TransactionContext tc, string sql)
        {
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetbyInSQL(TransactionContext tc, string EmpIds, int payrollTypeID)
        {
            if (EmpIds == "")
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n ",
                    payrollTypeID);
                return tc.ExecuteReader(sql);
            }
            else
            {
                string sTableName;
                EmpIds = IDHelper.GetIDs(tc, EmpIds, out sTableName);

                IDataReader dr = tc.ExecuteReader(
                    "SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EmployeeID IN ( %q )",
                    payrollTypeID, EmpIds);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop TAble %q", sTableName);
                return dr;
            }
        }

        internal static IDataReader Get(TransactionContext tc, int status, DateTime lastDateOfYear)
        {
            string strSQLQuery = SQLParser.MakeSQL("SELECT * FROM Employee WHERE  Status = %n AND JoiningDate <= %d ",
                status, lastDateOfYear);
            return tc.ExecuteReader(strSQLQuery);
        }
        internal static IDataReader GetbyEmail(TransactionContext tc, string Email)
        {
            string strSQLQuery = SQLParser.MakeSQL("SELECT * FROM Employee WHERE  emailAddress = %s ", Email);
            return tc.ExecuteReader(strSQLQuery);
        }

        internal static IDataReader Get(TransactionContext tc, EnumEmployeeStatus status, int payrollTypeID)
        {
            string strSQLQuery = string.Empty;

            if (status == EnumEmployeeStatus.Regardless)
            {
                strSQLQuery = SQLParser.MakeSQL("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n ", payrollTypeID);
            }
            else
            {
                strSQLQuery = SQLParser.MakeSQL("SELECT * FROM Employee WHERE  Status = %n AND PAYROLLTYPEID = %n ",
                    (int)status, payrollTypeID);
            }
            return tc.ExecuteReader(strSQLQuery);
        }

        internal static DataSet GetEmpIDsOfManager(TransactionContext tc, int status, DateTime lastDateOfYear)
        {
            string strSQLQuery =
                SQLParser.MakeSQL(
                    "SELECT EMPLOYEEID FROM Employee WHERE IsManager = 1 AND  Status = %n AND JoiningDate <= %d ",
                    status, lastDateOfYear);
            return tc.ExecuteDataSet(strSQLQuery);
        }

        internal static DataSet GetEncashmentReport(TransactionContext tc, string sEmpIDs, string leaveYear)
        {
            DataSet oEmpDetails = new DataSet();
            string sql = string.Empty;
            try
            {
                sql = SQLParser.MakeSQL(@" SELECT e.EMPLOYEEID,e.EMPLOYEENO,e.[NAME] NAME,d2.[NAME] Designation,d.[DESCRIPTION] Department, ct.[DESCRIPTION] Category,g.[DESCRIPTION] Grade,c.[DESCRIPTION] CC,e.JOININGDATE, lc.[DESCRIPTION] Location,e2.GrossSalary, e2.LeaveBalance,
                                    e2.EncashmentDays ENCASHDAYS, e2.AMOUNT ENCASHAMOUNT, l.[DESCRIPTION] LeaveType, l2.[NAME] LeaveYear
                                    FROM EMPLOYEE e
                                    LEFT JOIN LeaveEncashment e2 ON e.EMPLOYEEID = e2.EMPLOYEEID
                                    LEFT JOIN LEAVE l on l.LEAVEID = e2.LEAVEID
                                    LEFT JOIN LEAVEYEAR l2 on  l2.LEAVEYEARID = e2.LEAVEYEARID
                                     LEFT JOIN DEPARTMENT d on e.DEPARTMENTID=d.DEPARTMENTID
                                    LEFT JOIN  DESIGNATION d2 on e.DESIGNATIONID=d2.DESIGNATIONID
                                     LEFT JOIN GRADES g on e.GRADEID=g.GRADEID
                                    LEFT JOIN LOCATION lc on e.LOCATIONID=lc.LOCATIONID
                                    LEFT JOIN EMPCOSTCENTER ec on e.EMPLOYEEID=ec.EMPLOYEEID
                                    LEFT JOIN  CRG c ON ec.COSTCENTERID=c.CRGID
                                    LEFT JOIN  CATEGORY ct ON ct.CATEGORYID=e.CATEGORYID
                                    WHERE e.EMPLOYEEID IN(%q) AND l2.[NAME] = '%q' AND e2.AMOUNT!=0 AND ec.CurrentCC=1
                                    ORDER BY c.[DESCRIPTION],e.EMPLOYEENO", sEmpIDs, leaveYear);

                //                sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID,e.EMPLOYEENO,e.[NAME] NAME, e2.EncashmentDays ENCASHDAYS, e2.AMOUNT ENCASHAMOUNT, l.[DESCRIPTION] LeaveType, l2.[NAME] LeaveYear
                //                                        FROM EMPLOYEE e, LeaveEncashment e2, LEAVE l, LEAVEYEAR l2
                //                                        WHERE e.EMPLOYEEID = e2.EMPLOYEEID AND l.LEAVEID = e2.LEAVEID AND l2.LEAVEYEARID = e2.LEAVEYEARID
                //                                        AND e.EMPLOYEEID IN(%q) AND l2.[NAME] = '%q' AND e2.AMOUNT!=0
                //                                        ORDER BY e.EMPLOYEEID", sEmpIDs, leaveYear);
                oEmpDetails = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oEmpDetails;
        }

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

        internal static bool GetIsFixedPositionForMobile(TransactionContext tc, int ID)
        {
            object IsFixedLocation = tc.ExecuteScalar("SELECT IsFixedLocation FROM Employee With(NoLock) WHERE EmployeeID=%n ", ID);

            if (IsFixedLocation == DBNull.Value)
            {
                return false;
            }
            else
            {
                return Convert.ToBoolean(IsFixedLocation);
            }
        }

        internal static IDataReader GetEmployeeByIDs(TransactionContext tc, string ID)
        {
            string str = SQLParser.MakeSQL("SELECT * FROM Employee WHERE EmployeeID in (%q) ", ID);


            return tc.ExecuteReader("SELECT * FROM Employee WHERE EmployeeID in (%q) ", ID);
        }

        internal static DataTable GetEmployeeForDashboard(TransactionContext tc, int ID)
        {
            string str = SQLParser.MakeSQL(
                        @"SELECT e.EmployeeId Id, e.EmployeeNo, e.name, lm.employeeNo+'-'+lm.NAME lineManager , e.JoiningDate, dept.DESCRIPTION Department, des.NAME Designation, e.ProfileComplitionPercent profileCompletionPercent
                            FROM EMPLOYEE e
                            LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID
                            LEFT JOIN DESIGNATION des ON e.DESIGNATIONID = des.DESIGNATIONID
                            LEFT JOIN EMPLOYEE lm ON lm.EMPLOYEEID = e.LinemanagerId
                            WHERE e.employeeId = %n", ID);
            return tc.ExecuteDataTable(str);
        }

        internal static DataTable GetUserForLandingPage(TransactionContext tc, int userId)
        {
            string str = SQLParser.MakeSQL(
                @"SELECT u.LoginId, u.UserName, e.name, lm.employeeNo+'-'+lm.NAME lineManager , e.JoiningDate, dept.DESCRIPTION Department, des.NAME Designation 
                    FROM USERS u
                    LEFT JOIN EMPLOYEE e ON isnull(u.EMPLOYEEID, 0) = e.EmployeeId
                    LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID
                    LEFT JOIN DESIGNATION des ON e.DESIGNATIONID = des.DESIGNATIONID
                    LEFT JOIN EMPLOYEE lm ON lm.EMPLOYEEID = e.LinemanagerId
                    WHERE u.userId = %n", userId);
            return tc.ExecuteDataTable(str);
        }
        internal static IDataReader GetAllLive(TransactionContext tc)
        {
            // get live employee
            tc.CommandTimeOut = 10000;
            return tc.ExecuteReader("SELECT * FROM Employee WHERE STATUS=1");
        }
        internal static IDataReader GetByCardID(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE CardID=%n ", ID);
        }

        internal static IDataReader GetParentEmployee(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader(@"Select * From Employee Where EmployeeID IN(
				   Select EMPLOYEEID from OrganEmployee Where NodeID IN(
				   Select OrganogramID From Organogram where OrganogramID in(
				   Select ParentID From Organogram where OrganogramID in(
				   Select nodeid from OrganEmployee Where EmployeeID=%n))))", ID);
        }

        internal static IDataReader GetLineManager(TransactionContext tc, int empId)
        {
            string sql = SQLParser.MakeSQL(@"select * from employee where employeeId = 
                             (select linemanagerId from employee where employeeid = %n)", empId);
            return tc.ExecuteReader(sql);
        }


        internal static IDataReader GetWithOutcheckPayrollType(TransactionContext tc, string employeeNo)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE EmployeeNo=%s AND status=%n ", employeeNo,
                (int)EnumEmployeeStatus.Live);
        }

        internal static IDataReader Get(TransactionContext tc, string employeeNo, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE employeeNo=%s AND  PayrollTypeID=%n AND status=%n  ",
                employeeNo, payrollTypeID, (int)EnumEmployeeStatus.Live);
        }

        internal static IDataReader Get(TransactionContext tc, string employeeNo)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE employeeNo= %s AND status= %n  ", employeeNo,
                (int)EnumEmployeeStatus.Live);
        }

        internal static IDataReader GetFromAll(TransactionContext tc, string employeeNo, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE employeeNo=%s AND  PayrollTypeID=%n   ", employeeNo,
                payrollTypeID);
        }


        internal static DataSet GetEmpBasicInfo(TransactionContext tc, string sEmpID)
        {
            DataSet oEmpBasicInfos = new DataSet();
            try
            {
                string sTableName;
                sEmpID = IDHelper.GetIDs(tc, sEmpID, out sTableName);

                oEmpBasicInfos = tc.ExecuteDataSet(
                    "SELECT Emp.EMPLOYEENO,Emp.NAME,Emp.GENDER,RE.NAME RName,Emp.BIRTHDATE,EMp.JOININGDATE,Emp.DATEOFCONFIRMATION,Emp.MARITALSTATUSID"
                    + " FROM EMPLOYEE  Emp,Religion  RE"
                    + " WHERE Emp.EMPLOYEEID IN(%q) AND emp.RELIGIONID=RE.RELIGIONID order by Emp.EmployeeNo", sEmpID);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpBasicInfos;
        }

        internal static IDataReader GetCurrentHeadCount(TransactionContext tc, int departmentId, int designationId, int gradeId)
        {
            string sSearch = string.Empty;
            if (departmentId > 0)
            {
                sSearch = SQLParser.TagSQL(sSearch) + SQLParser.MakeSQL("em.DEPARTMENTID=%n", departmentId);
            }
            if (designationId > 0)
            {
                sSearch = SQLParser.TagSQL(sSearch) + SQLParser.MakeSQL("em.DESIGNATIONID=%n", designationId);
            }
            if (gradeId > 0)
            {
                sSearch = SQLParser.TagSQL(sSearch) + SQLParser.MakeSQL("em.GRADEID=%n", gradeId);
            }
            return tc.ExecuteReader("SELECT COUNT(*) FROM Employee em %q", sSearch);
        }

        internal static IDataReader GetEmpForHNM(TransactionContext tc, int sEmpID)
        {
            string sql = SQLParser.MakeSQL(
                    @"SELECT e.*,e2.PARMANENTADDRESS Address FROM Employee e 
                    LEFT JOIN EMPCONTACT AS e2 ON e2.EMPLOYEEID = e.EMPLOYEEID
                            WHERE e.EMPLOYEEID=%n", sEmpID);

            return tc.ExecuteReader(sql);
        }

        internal static DataSet GetEmpBasicInfoForReport(TransactionContext tc, string sEmpID)
        {
            DataSet oEmpBasicInfos = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(
                    @"Select e.EMPLOYEENO, e.NAME, e.JOININGDATE JOININGDATE, CASE WHEN  e.PAYMENTMODE = 1 THEN 'Cash Payment'
                                                WHEN  e.PAYMENTMODE = 2 THEN 'Bank Transfer'
                                                WHEN  e.PAYMENTMODE = 3 THEN 'Cash Payment'
                                                END 
                                                PaymentMode , e.BASICSALARY, e.GROSSSALARY, dept.DESCRIPTION Department,
                                                g.DESCRIPTION Grade, desig.NAME Designation, cc.CODE CostCenter, e.ACCOUNTNO, br.NAME BranchName, b.NAME BankName, '' PaymentDate, '' PayPeriodFrom, '' PayPeriodTo, '' TotalDays, '' PayableDays, '' SalaryMonth
                                                 FROM EMPLOYEE e 
                                                LEFT JOIN Department dept ON dept.DEPARTMENTID=e.DEPARTMENTID  
                                                LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
                                                LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID = e.DESIGNATIONID
                                                LEFT JOIN EMPCOSTCENTER empcc ON empcc.EMPLOYEEID = e.EMPLOYEEID
                                                LEFT JOIN CRG cc ON cc.CRGID = empcc.COSTCENTERID
                                                LEFT JOIN BRANCHES br ON br.BRANCHID=e.BRANCHID
                                                LEFT JOIN BANKS b ON b.BANKID = br.BANKID
                                                WHERE e.EMPLOYEEID in (%q)", sEmpID);

                oEmpBasicInfos = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpBasicInfos;
        }

        internal static DataSet GetDivision(TransactionContext tc, int nEmpID)
        {
            DataSet oEmpBasicInfos = new DataSet();
            try
            {
                oEmpBasicInfos = tc.ExecuteDataSet("SELECT * from vw_EmpDivision Where EmployeeID=%n", nEmpID);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpBasicInfos;
        }

        internal static DataSet GetEmpDetails(TransactionContext tc, string sEmpID)
        {
            DataSet oEmpDetails = new DataSet();
            try
            {
                string sTableName;
                sEmpID = IDHelper.GetIDs(tc, sEmpID, out sTableName);

                //oEmpDetails = tc.ExecuteDataSet("SELECT Emp.EMPLOYEENO,Emp.Name,DG.NAME DegName,Dep.DESCRIPTION DepDes,GD.DESCRIPTION GDDes," 
                //                                   + " Emp.BIRTHDATE,Emp.JOININGDATE,Emp.DATEOFCONFIRMATION,Emp.GENDER,"
                //                                   + " Emp.MARITALSTATUSID,Emp.MOBILENO,Emp.EMAILADDRESS,BK.NAME Bank,BR.NAME Branch,"
                //                                   + " Emp.ACCOUNTNO,RG.NAME RegName,Emp.BASICSALARY,Emp.TAXAMOUNT" 
                //                                   + " FROM"
                //                                   + " Employee Emp,Designation DG,Department Dep,Grades GD,"
                //                                   + " Religion RG,Banks BK,Branches BR"
                //                                   + " WHERE Emp.DESIGNATIONID=DG.DESIGNATIONID"
                //                                   + " AND Emp.DEPARTMENTID=Dep.DEPARTMENTID"
                //                                   + " AND Emp.GRADEID=GD.GRADEID"
                //                                   + " AND Emp.RELIGIONID=RG.RELIGIONID"
                //                                   + " AND Emp.BRANCHID=BR.BRANCHID"
                //                                   + " AND BK.BANKID=Br.BANKID" 
                //                                   + " AND Emp.EMPLOYEEID IN(%q) order by Emp.EMPLOYEENO ", sEmpID);


                string sql =
                    SQLParser.MakeSQL(
                        @"SELECT Emp.EMPLOYEENO,Emp.Name,Emp.BIRTHDATE,Emp.JOININGDATE,Emp.DATEOFCONFIRMATION,
						Emp.GENDER,Emp.MARITALSTATUSID,Emp.MOBILENO,Emp.EMAILADDRESS,Emp.ACCOUNTNO,Emp.OutPayAccountNo,
						Emp.BASICSALARY,Emp.GrossSalary,Emp.VendorCode,Emp.TAXAMOUNT,Deg.NAME   DegName,Dep.DESCRIPTION   DepDes,
						GD.DESCRIPTION   GDDes,BK.NAME   Bank,BR.NAME   Branch,BKOUT.NAME   OUTPayBank,BROUT.NAME OUTPayBranch, RG.NAME   RegName,Loc.Description Locdes
						 FROM EMPLOYEE   Emp 
						 LEFT Outer JOIN DESIGNATION   Deg ON Emp.DESIGNATIONID=Deg.DESIGNATIONID
						LEFT OUTER JOIN Department   Dep ON Emp.DEPARTMENTID=Dep.DEPARTMENTID 
						 LEFT OUTER JOIN Grades   GD ON Emp.GRADEID=GD.GRADEID 
						LEFT OUTER JOIN Religion   RG ON Emp.RELIGIONID=RG.RELIGIONID
						LEFT OUTER JOIN Branches   BR ON Emp.BRANCHID=BR.BRANCHID
						LEFT OUTER JOIN BANKS   BK ON BK.BANKID=BR.BANKID
						LEFT OUTER JOIN Branches   BROUT ON Emp.OUTPayBRANCHID=BROUT.BRANCHID
						LEFT OUTER JOIN BANKS   BKOUT ON BKOUT.BANKID=BROUT.BANKID
						LEFT OUTER JOIN Location   Loc ON Emp.LocationID=Loc.LocationID
						WHERE Emp.EMPLOYEEID IN(%q) ORDER BY Emp.EMPLOYEENO", sEmpID);

                oEmpDetails = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop TAble %q", sTableName);

                //oEmpDetails = tc.ExecuteDataSet("SELECT Emp.EMPLOYEENO,Emp.Name,Emp.BIRTHDATE,Emp.JOININGDATE,Emp.DATEOFCONFIRMATION, "
                //                                + " Emp.GENDER,Emp.MARITALSTATUSID,Emp.MOBILENO,Emp.EMAILADDRESS,Emp.ACCOUNTNO,"
                //                                + " Emp.BASICSALARY,Emp.GrossSalary,Emp.VendorCode,Emp.TAXAMOUNT,Deg.NAME DegName,Dep.DESCRIPTION DepDes,"
                //                                + " GD.DESCRIPTION GDDes,BK.NAME Bank,BR.NAME Branch,RG.NAME RegName,Loc.Description Locdes"
                //                                + " FROM EMPLOYEE Emp "
                //                                + " LEFT Outer JOIN DESIGNATION Deg ON Emp.DESIGNATIONID=Deg.DESIGNATIONID"
                //                                + " LEFT OUTER JOIN Department Dep ON Emp.DEPARTMENTID=Dep.DEPARTMENTID "
                //                                + " LEFT OUTER JOIN Grades GD ON Emp.GRADEID=GD.GRADEID "
                //                                + " LEFT OUTER JOIN Religion RG ON Emp.RELIGIONID=RG.RELIGIONID"
                //                                + " LEFT OUTER JOIN Branches BR ON Emp.BRANCHID=BR.BRANCHID"
                //                                + " LEFT OUTER JOIN BANKS BK ON BK.BANKID=BR.BANKID"
                //                                + " LEFT OUTER JOIN Location Loc ON Emp.LocationID=Loc.LocationID"
                //                                + " WHERE Emp.EMPLOYEEID IN(%q) ORDER BY Emp.EMPLOYEENO", sEmpID);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpDetails;
        }

        internal static DataSet GetEmployeeGeneralData(TransactionContext tc)
        {
            //string sqlClause = string.Empty;
            //sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID = %n", payrollTypeID);
            //if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            //{
            //    sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("status = %n", status);
            //}

            //if (onlyActive == true)
            //{
            //    sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL(" status = %s", EnumEmployeeStatus.Live);
            //}


            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeGeneralData");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }

        internal static DataSet GetEmployeeContactData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeContactInfo");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeSpouseData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeSpouseInfo");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeChildrenData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeChildrenInfo");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeAcademicData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeAcademicInfo");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }

        internal static DataSet GetEmployeeTrainingData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeTrainingInfo");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeExperienceData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeExperienceData");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeNomineeData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT * FROM VW_EmployeeNomineeDetails");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetListOfPromotion(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID EmployeeId, e.EMPLOYEENO EmployeeNo, e.NAME EmployeeName, dept.DESCRIPTION Department, desg.NAME Designation,
                                e.JOININGDATE JoiningDate, e.GROSSSALARY GrossSalary, e.BASICSALARY BasicSalary, g.DESCRIPTION Grade,
                                t.effectDate EffectDate
                                FROM EMPLOYEE e 
                                LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID 
                                LEFT JOIN DESIGNATION desg ON e.DESIGNATIONID = desg.DESIGNATIONID 
                                LEFT JOIN GRADES g ON e.GRADEID = g.GRADEID
                                INNER JOIN (SELECT * FROM EMPLIFECYCLE WHERE STATUS = (SELECT statusId FROM EMPLOYEESTATUS WHERE isPromotion = 1)) t
                                ON t.employeeId = e.employeeId");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetListOfTransfer(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID EmployeeId, e.EMPLOYEENO EmployeeNo, e.NAME EmployeeName, dept.DESCRIPTION Department, desg.NAME Designation,
                                e.JOININGDATE JoiningDate, e.GROSSSALARY GrossSalary, e.BASICSALARY BasicSalary, g.DESCRIPTION Grade,
                                t.effectDate EffectDate, l.DESCRIPTION Location
                                FROM EMPLOYEE e 
                                LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID 
                                LEFT JOIN DESIGNATION desg ON e.DESIGNATIONID = desg.DESIGNATIONID 
                                LEFT JOIN GRADES g ON e.GRADEID = g.GRADEID
                                LEFT JOIN LOCATION l ON e.LOCATIONID = l.LOCATIONID
                                INNER JOIN (SELECT * FROM EMPLIFECYCLE WHERE LOCATIONID IS NOT NULL OR DEPARTMENTID IS NOT NULL) t
                                ON t.employeeId = e.employeeId");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetJobSeparationList(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID EmployeeId, e.EMPLOYEENO EmployeeNo, e.NAME EmployeeName, dept.DESCRIPTION Department, desg.NAME Designation,
                                e.JOININGDATE JoiningDate, e.ENDOFCONTRACTDATE DiscontinueDate
                                FROM EMPLOYEE e 
                                LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID 
                                LEFT JOIN DESIGNATION desg ON e.DESIGNATIONID = desg.DESIGNATIONID 
                                LEFT JOIN GRADES g ON e.GRADEID = g.GRADEID
                                WHERE e.STATUS <> 1 ");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetConfirmedEmployeeList(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID EmployeeId, e.EMPLOYEENO EmployeeNo, e.NAME EmployeeName, dept.DESCRIPTION Department, desg.NAME Designation,
                                            e.JOININGDATE JoiningDate, e.DATEOFCONFIRMATION ConfirmDate
                                            FROM EMPLOYEE e 
                                            LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID 
                                            LEFT JOIN DESIGNATION desg ON e.DESIGNATIONID = desg.DESIGNATIONID 
                                            LEFT JOIN GRADES g ON e.GRADEID = g.GRADEID
                                            WHERE e.DATEOFCONFIRMATION IS NOT NULL ");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmpDetailsView(TransactionContext tc, string sEmpID)
        {
            DataSet oEmpDetails = new DataSet();
            try
            {
                string sTableName;
                sEmpID = IDHelper.GetIDs(tc, sEmpID, out sTableName);

                string sql =
                    SQLParser.MakeSQL(@"Select * from VW_EMPLOYEEBASIC
						WHERE EMPLOYEEID IN(%q) ORDER BY EMPLOYEENO", sEmpID);

                oEmpDetails = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpDetails;
        }

        internal static DataSet GetEmpDetailsView(TransactionContext tc)
        {
            DataSet oEmpDetails = new DataSet();
            try
            {
                string sql =
                    SQLParser.MakeSQL(@"Select * from VW_EMPLOYEEBASIC ORDER BY EMPLOYEENO");

                oEmpDetails = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpDetails;
        }
        //        internal static DataSet GetEmployeeCV(TransactionContext tc, string sEmpID)
        //        {
        //            DataSet oEmpCV = new DataSet();
        //            try
        //            {
        //                string sql = SQLParser.MakeSQL(@"SELECT e.[NAME], e.EMPLOYEENO, e.FATHERNAME, e.MOTHERNAME, e.NationalID,n.[DESCRIPTION] Nationality, e.BIRTHDATE,e.GENDER,
        //                                                e.MARITALSTATUSID,r.[NAME] Religion,e2.PARMANENTTELEPHONE Telephone1, e2.PRESENTTELEPHONE Telephone2, e2.FAX, e.MOBILENO,
        //                                                e.EMAILADDRESS,e2.PARMANENTADDRESS, t.name Thana, d.[NAME] District,e2.PRESENTADDRESS, t.name Thana, d.[NAME] District, 
        //                                                c.[DESCRIPTION] Company, dpt.[DESCRIPTION] Department, dg.[NAME] designation, e.JOININGDATE Appointment,
        //                                                 e.[STATUS], g.[DESCRIPTION] Grade 
        //                                                  FROM EMPLOYEE e
        //                                                  LEFT JOIN EMPCONTACT e2 ON e2.EMPLOYEEID= e.EMPLOYEEID
        //                                                  INNER JOIN RELIGION r ON r.RELIGIONID = e.RELIGIONID
        //                                                  INNER JOIN NATIONALITY n ON n.NATIONALITYID=e.NATIONALITYID
        //                                                  INNER JOIN DISTRICT d ON d.DISTRICTID= e2.PARMANENTDISTRICTID
        //                                                  INNER JOIN THANA t ON t.THANAID=e2.PARMANENTTHANAID
        //                                                  LEFT JOIN COMPANY c ON c.COMPANYID=e.CompanyID
        //                                                  INNER JOIN Department dpt ON dpt.DEPARTMENTID= e.DEPARTMENTID
        //                                                  LEFT JOIN Designation dg ON dg.DESIGNATIONID= e.DESIGNATIONID
        //                                                  LEFT JOIN GRADES g ON g.GRADEID= e.GRADEID 
        //                                                WHERE e.EMPLOYEEID=%q",sEmpID);
        //                oEmpCV = tc.ExecuteDataSet(sql);

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

        internal static DataSet GetEmpForLetterOfIntroduction(TransactionContext tc, string sEmpID, DateTime month)
        {
            DataSet oEmpDetails = new DataSet();
            try
            {
                string sTableName;
                sEmpID = IDHelper.GetIDs(tc, sEmpID, out sTableName);

                string sql =
                    SQLParser.MakeSQL(
                        @"SELECT ISNULL(c.DESCRIPTION,'') CompanyName,ISNULL(ctry.Description,'') Country,e.EMPLOYEENO EmpNo,
						e.NAME EmpName,e.BIRTHDATE DOB, ISNULL(ec.PRESENTADDRESS,'') Address,
						e.JOININGDATE DOJ,ISNULL(des.NAME,'') Designation, '' EmpType, ISNULL(s.CHANGEDAMOUNT,0) Salary 
						FROM
						(SELECT * from Employee
						WHERE EmployeeID IN (%q))
						e
						LEFT JOIN COMPANY c
						ON e.CompanyID = c.COMPANYID
						LEFT JOIN NATIONALITY ctry
						ON ctry.NATIONALITYID = e.NATIONALITYID
						LEFT JOIN EMPCONTACT ec
						ON e.EMPLOYEEID = ec.EMPLOYEEID
						LEFT JOIN DESIGNATION des
						ON des.DESIGNATIONID = e.DESIGNATIONID
						LEFT JOIN 
						(SELECT sm.EmployeeID,sm.SalaryMonth,smd.CHANGEDAMOUNT FROM 
						SALARYMONTHLY sm, SALARYMONTHLYDETAIL smd
						WHERE sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
						AND smd.ITEMID = -101 AND smd.ItemGroup =  1
						AND sm.SalaryMonth = %d) s
						on e.EMPLOYEEID = s.EMPLOYEEID", sEmpID, month);

                oEmpDetails = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpDetails;
        }

        internal static IDataReader GetGrandChilds(TransactionContext tc, int employeeID)
        {
            return tc.ExecuteReader(@"Select * From Employee Where EmployeeID IN(
									Select EmployeeID from OrganEmployee Where NodeID IN(
									Select OrganogramID From Organogram Where ParentID in(
									Select NodeID from OrganEmployee Where EmployeeID in(
									Select EmployeeID from OrganEmployee Where NodeID IN(
									Select OrganogramID From Organogram Where ParentID in(
									Select NodeID from OrganEmployee Where EmployeeID=%n))))))", employeeID);
        }

        internal static IDataReader GetGrandChilds(TransactionContext tc, string sEmpNos)
        {
            return tc.ExecuteReader(@"SELECT * FROM EMPLOYEE e WHERE Status=%n AND e.EMPLOYEENO IN(
									SELECT o.PositionNo  FROM Organogram o WHERE o.ParentID IN(SELECT OrganogramID FROM organogram WHERE ParentID IN(
									SELECT OrganogramID FROM organogram WHERE PositionNo=%s)))",
                (int)EnumEmployeeStatus.Live, sEmpNos);
        }

        internal static IDataReader GetAssignedEmployees(TransactionContext tc, int PMPYearID, int EmpID)
        {
            return tc.ExecuteReader(
                "select * from employee where employeeid in(SELECT AssignedForEmployeeID FROM AssignProxyObjectiveSetting where PMPYearID=%n AND AssignedToEmployeeID=%n AND IsApproved=%b)",
                PMPYearID, EmpID, true);
        }

        internal static DataSet GetEmpPosting(TransactionContext tc, string sEmpID)
        {
            DataSet oEmpPostings = new DataSet();
            try
            {
                string sTableName;
                sEmpID = IDHelper.GetIDs(tc, sEmpID, out sTableName);

                //oEmpPostings = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,Dept.DESCRIPTION Department,Loc.DESCRIPTION Location,Desg.NAME Designation,HREmp.EFFECTIVEDATE from EMPLOYEE Emp,DEPARTMENT Dept,LOCATION Loc,DESIGNATION Desg, HREMPPOSTING HREmp"
                //                                + " where Emp.EMPLOYEEID=HREmp.EMPLOYEEID "
                //                                + " and Desg.DESIGNATIONID=HREmp.DESIGNATIONID"
                //                                + " and Dept.DEPARTMENTID=HREmp.FUNCTIONID "
                //                                + " and Loc.LOCATIONID=HREmp.FUNCTIONID "
                //                                + " and HREmp.EMPLOYEEID in(%q) "
                //                                + " order By Emp.EMPLOYEENO ", sEmpID);

                string sql = SQLParser.MakeSQL(
                    @"SELECT e.EmployeeNo, e.Name, d.DESCRIPTION Department,l.DESCRIPTION Location, d2.Name Designation,h.EFFECTIVEDATE, u.UserName CommitedBy, e.CreationDate CommitedOn
												FROM employee e
												INNER JOIN DEPARTMENT d ON d.DEPARTMENTID= e.DepartmentID
												INNER JOIN Designation d2 ON d2.DesignationID=e.DesignationID
												INNER JOIN LOCATION l ON l.LOCATIONID = e.LocationID
												INNER JOIN users u ON u.UserID=e.CreatedBy
												LEFT JOIN HREMPPOSTING h ON h.EMPLOYEEID=e.EmployeeID
												WHERE e.EmployeeID IN (%q)", sEmpID);

                oEmpPostings = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop TAble %q", sTableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpPostings;
        }


        internal static DataSet GetLineManagerFromOrg(TransactionContext tc, int sEmpID)
        {
            DataSet oLineManager = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT  Employee.Name FROM Employee, 
                                              (SELECT EmployeeID FROM OrganEmployee oe WHERE oe.NodeID =  
                                              (
                                              SELECT  ParentID FROM Organogram o WHERE o.OrganogramID=
                                              (SELECT NodeID FROM OrganEmployee oe WHERE EmployeeID=%n))) tab1
                                              WHERE Employee.EMPLOYEEID=tab1.EmployeeID", sEmpID);
                oLineManager = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oLineManager;
        }

        internal static DataSet GetEmpConfirmHis(TransactionContext tc, DateTime dEffectDate, DateTime dEffectDate2,
            int payrollTypeID)
        {
            DataSet oEmpConfirms = new DataSet();
            try
            {
                oEmpConfirms = tc.ExecuteDataSet(
                    "Select Emp.Employeeid,eh.emplifecycleid,Eh.status, Emp.EMPLOYEENO,Emp.NAME,Deg.NAME DESIGNATION,G.DESCRIPTION Grade, Emp.JOININGDATE,"
                    + " Emp.DATEOFCONFIRMATION DATEOFCONFIRMATION,EH.SalaryMonth EFFECTDATE,EH.REMARKS Comments,US.LoginID CommitedBy,EH.CreationDate"
                    + " from EmpLifeCycle EH, EMPLOYEE Emp ,USERS US,DESIGNATION Deg,GRADES G "
                    + " WHERE Emp.DESIGNATIONID=Deg.DESIGNATIONID AND Emp.GRADEID=G.GRADEID AND US.USERID=EH.CreatedBy AND "
                    + " Emp.DATEOFCONFIRMATION between %d and %d AND  eh.isconfirm=1 AND"
                    + " Emp.EMPLOYEEID=EH.EMPLOYEEID AND Emp.PayrollTypeID=%n order by Emp.EMPLOYEENO", dEffectDate,
                    dEffectDate2, payrollTypeID);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpConfirms;
        }

        internal static DataSet GetEmpPFHis(TransactionContext tc, DateTime dEffectDate)
        {
            DataSet oEmpConfirms = new DataSet();
            try
            {
                oEmpConfirms = tc.ExecuteDataSet(
                    "Select Emp.EMPLOYEENO,Emp.NAME,G.DESCRIPTION,Emp.PFMEMBERSHIPDT,US.UserID,Emp.CreationDate "
                    + " from EMPLOYEE Emp,GRADES G,Users US"
                    + " Where Emp.GRADEID=G.GRADEID "
                    + " and US.UserID=Emp.CreatedBy "
                    + " and Emp.DATEOFCONFIRMATION between %d and %d"
                    + " order by Emp.EMPLOYEENO", PayrollGlobalFunctions.PayrollFirstDateOfMonth(dEffectDate),
                    PayrollGlobalFunctions.PayrollLastDateOfMonth(dEffectDate));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpConfirms;
        }

        internal static DataSet GetEmpJoining(TransactionContext tc, DateTime dEffectDate, DateTime dEffectDate2)
        {
            DataSet oEmpConfirms = new DataSet();
            try
            {
                string sQL = SQLParser.MakeSQL(
                    @"Select emp.basicsalary,emp.maritalstatusid,emp.religionid,Users.UserName createduser,emp.creationdate,
												emp.employeeid,Emp.EMPLOYEENO,Emp.Gender,Emp.NAME,G.DESCRIPTION,Dg.Name   DegName,Emp.JoiningDate,Emp.Grosssalary,Emp.BIRTHDATE
												from EMPLOYEE   Emp,GRADES   G,Designation   Dg, Users  
												Where Emp.GRADEID=G.GRADEID and emp.DesignationID=Dg.DesignationID  
												and  Emp.JoiningDate between %d and %d  and Users.UserID=emp.createdby 

												AND Emp.JOININGDATE <= (SELECT MIN(elc.EffectDate) FROM EmpLifeCycle elc WHERE Emp.EMPLOYEEID = elc.EmployeeID)

												order by Emp.EMPLOYEENO", dEffectDate, dEffectDate2);

                #region OldCode

                //string sQL = SQLParser.MakeSQL("Select  emp.basicsalary,emp.maritalstatusid,emp.religionid,lc.emplifecycleid,emp.createdby createduser,lc.effectdate,emp.creationdate,emp.employeeid,Emp.EMPLOYEENO,Emp.Gender,Emp.NAME,G.DESCRIPTION,Dg.Name DegName,Emp.JoiningDate,Emp.Grosssalary,Emp.BIRTHDATE,lc.createdby  "
                //                                + " from EMPLOYEE Emp,GRADES G,Designation Dg ,emplifecycle lc"
                //                                + " Where Emp.GRADEID=G.GRADEID and emp.DesignationID=Dg.DesignationID  and "
                //                                + " Emp.JoiningDate between %d and %d AND"
                //                                + " Emp.status=1 AND"
                //                                + " emp.employeeid=lc.employeeID AND"
                //                                + " lc.iscontinue=1 AND"
                //                                + " Emp.JoiningDate=lc.effectdate"
                //                                + " order by Emp.EMPLOYEENO", PayrollGlobalFunctions.PayrollFirstDateOfMonth(dEffectDate), PayrollGlobalFunctions.PayrollLastDateOfMonth(dEffectDate));

                #endregion

                oEmpConfirms = tc.ExecuteDataSet(sQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpConfirms;
        }

        internal static DataSet GetEmpJoining4Novartis(TransactionContext tc, DateTime dEffectDate, int payrollTypeID)
        {
            DataSet oEmpJoining = new DataSet();
            try
            {
                oEmpJoining = tc.ExecuteDataSet(
                    "Select Emp.EMPLOYEENO,Emp.NAME,Dg.Name Designation,Emp.JoiningDate DOJ,G.DESCRIPTION Grade ,"
                    + " Emp.Basicsalary Basic,Emp.BIRTHDATE DOB,Emp.Gender Sex,Emp.MaritalStatusID,Rel.Name Religion,US.LoginID CommitedBy,Emp.creationdate CommitedOn "
                    + " from Employee Emp"
                    + " left outer JOIN Grades G ON Emp.GradeID=G.GradeID"
                    + " left outer Join Designation Dg on emp.DesignationID=Dg.DesignationID"
                    //+ " left outer Join EmpCostcenter EmpCC on EmpCC.EmployeeID=Emp.EmployeeID"
                    //+ " left outer Join CRG CC on CC.CRGID=EmpCC.CostCenterID"
                    + " INNER Join Religion Rel on Emp.ReligionId=Rel.ReligionId "
                    + " INNER Join USERS US on US.USERID=Emp.CreatedBy "
                    + " and Emp.creationdate between %d and %d ANd Emp.PayrollTypeID=%n"
                    + " order by Emp.EMPLOYEENO", PayrollGlobalFunctions.PayrollFirstDateOfMonth(dEffectDate),
                    PayrollGlobalFunctions.PayrollLastDateOfMonth(dEffectDate), payrollTypeID);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oEmpJoining;
        }

        internal static DataSet GetDesignationWiseEmployeeCountsByDepartmentID(TransactionContext tc, int departmentID)
        {
            DataSet designationWiseCountDS = new DataSet();
            try
            {
                designationWiseCountDS =
                    tc.ExecuteDataSet(@"Select '['+de.Code+']-'+de.Name GradeOrDesignation,empC.Count 
										From 
										(
										Select DesignationID,COUNT(*) Count 
										From Employee 
										Where DepartmentID=%n
										AND [Status] = %n 
										Group by DesignationID
										)empC ,Designation de
										Where empC.DesignationID = de.DesignationID", departmentID,
                        (int)EnumEmployeeStatus.Live);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return designationWiseCountDS;
        }

        internal static DataSet GetGradeWiseEmployeeCountsByDepartmentID(TransactionContext tc, int departmentID)
        {
            DataSet designationWiseCountDS = new DataSet();
            try
            {
                designationWiseCountDS =
                    tc.ExecuteDataSet(@"Select '['+gd.Code+']-'+gd.[Description] GradeOrDesignation,empC.Count 
										From 
										(
										Select GradeID,COUNT(*) Count 
										From Employee 
										Where DepartmentID=%n
										AND [Status] = %n 
										Group by GradeID
										)empC ,Grades gd
										where empC.GradeID = gd.GradeID", departmentID, (int)EnumEmployeeStatus.Live);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return designationWiseCountDS;
        }

        internal static int GetDays(TransactionContext tc, int nPayrollTypeID)
        {
            int nDays = 0;
            object obj = tc.ExecuteScalar("Select Days  from EarnedLeaveDays where PayrollType=%n", nPayrollTypeID);
            if (obj != null)
            {
                nDays = Convert.ToInt32(obj);
            }
            else
            {
                nDays = 0;
            }

            return nDays;
        }

        internal static IDataReader GetEmployeesYetNotAssigned(TransactionContext tc)
        {
            //string sql = SQLParser.MakeSQL(@"SELECT * FROM Employee WHERE EmployeeNo NOT IN (select  LOGINID From Users where UserType=%n) Order By EmployeeNo",
            //    EnumSystemType.Web);
            string SQL = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE EmployeeNo NOT IN (select  LOGINID From Users where UserType=%n) Order By EmployeeNo",
                EnumSystemType.Admin);

            //string no = "23";
            //string SQL = SQLParser.MakeSQL("SELECT * FROM Employee WHERE EmployeeNo = %s AND EmployeeNo NOT IN (select  LOGINID From Users where UserType=%n)",
            //    no,EnumSystemType.Web);


            return tc.ExecuteReader(SQL);
        }

        public static IDataReader GetEmployeesByJoiningMonth(TransactionContext tc, DateTime fromDate, DateTime toDate)
        {
            string SQL = SQLParser.MakeSQL(
                "SELECT * FROM EMPLOYEE Where JoiningDate Between %d and %d order by EMPLOYEEID",
                fromDate, toDate);
            return tc.ExecuteReader(SQL);
        }

        internal static DataSet GetCashAdvice(TransactionContext tc, string sEmpIDs, string dSalDate)
        {
            DataSet FinallSet = new DataSet();

            #region Old Code

            //            string ssql = SQLParser.MakeSQL(@"SELECT a.*,l.Employeeno,l.name,loc.description location
            //                                                        FROM 
            //                                                        (select tab1.Employeeid,'Gross' description,sum(changedamount) Amount from(
            //                                                        select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d 
            //                                                        AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1
            //                                                        where tab1.itemgroup=1
            //                                                        group by tab1.Employeeid
            //                                                        union 
            //                                                        select tab1.Employeeid,description,sum(changedamount) Amount from(
            //                                                        select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d
            //                                                        AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1
            //                                                        where tab1.itemgroup=3
            //                                                        group by tab1.Employeeid,tab1.itemid,tab1.description
            //                                                        union
            //                                                        select tab1.Employeeid,'Signature' description,0 Amount from(
            //                                                        select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d
            //                                                        AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1
            //                                                        where tab1.itemgroup=1 AND itemcode=-101 AND itemid=-101
            //                                                        group by tab1.Employeeid,tab1.itemid,tab1.description) a
            //                                                        JOIN
            //                                                        Employee l
            //                                                        ON l.Employeeid  = a.Employeeid 
            //                                                        join Location loc ON l.locationid=loc.locationid order by Employeeid", dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs);

            #endregion

            #region Old Code

            //            string sql = SQLParser.MakeSQL(@"SELECT a.*,l.Employeeno,l.name,loc.description location
            //                                            FROM 
            //                                            (
            //                                            select  tt.Employeeid,'Gross' description,(tt.Amount-ISNULL(ot.amount,0)) Amount  FROM(  
            //                                            (select ott.Employeeid,'Gross' description,sum(changedamount) Amount from(
            //                                            select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d 
            //                                            AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null
            //                                            )ott
            //                                            where ott.itemgroup in(1,8)
            //                                            group by ott.Employeeid) tt
            //                                            Left Outer join 
            //											( select  Sum(A.amount) amount,E.EmployeeID  from
            //											( select S.EmployeeID, Sum(sd.changedamount) Amount 
            //											from SALARYMONTHLY S,SalaryMonthlyDetail Sd 
            //											where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
            //											AND S.SalaryMonth=%d  and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q) 
            //											group by S.EmployeeID
            //											) A, Employee E where A.EmployeeID = E.EmployeeID group by E.EmployeeID)as ot on tt.Employeeid=ot.EmployeeID)
            //                                            union 
            //                                            select tab1.Employeeid,description,sum(changedamount) Amount from(
            //                                            select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d
            //                                            AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1
            //                                            where tab1.itemgroup=3
            //                                            group by tab1.Employeeid,tab1.itemid,tab1.description
            //                                            union
            //                                            select tab1.Employeeid,'Signature' description,0 Amount from(
            //                                            select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d
            //                                            AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1
            //                                            where tab1.itemgroup=1 AND itemcode=-101 AND itemid=-101
            //                                            group by tab1.Employeeid,tab1.itemid,tab1.description) a
            //                                            JOIN
            //                                            Employee l
            //                                            ON l.Employeeid  = a.Employeeid 
            //                                            join Location loc ON l.locationid=loc.locationid order by Employeeid", dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs);

            //union
            //                                select tab1.Employeeid,'Signature' description,0 Amount,tab1.CostCenter from(
            //                                select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter
            //                                from salarymonthlydetail smd,salarymonthly sm ,CRG CC,SALARYEMPCOSTCENTER empcc 
            //                                where salarymonth=%d
            //                                AND sm.salarymonthlyid=smd.salarymonthlyid 
            //                                AND sm.EMPLOYEEID IN(%q) 
            //                                AND sm.branchid is NULL
            //                                AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID
            //                                AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID
            //                                )tab1
            //                                where tab1.itemgroup=1 AND itemcode=-101 AND itemid=-101
            //                                group by tab1.Employeeid,tab1.itemid,tab1.description,tab1.CostCenter

            #endregion

            string sTableName;
            sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);

            string sql = SQLParser.MakeSQL(@"SELECT a.*,l.Employeeno,l.name,loc.description location
											FROM 
											(
											select  tt.Employeeid,'Gross' description,(tt.Amount-ISNULL(ot.amount,0)) Amount,tt.CostCenter  FROM(  
											(select ott.Employeeid,'Gross' description,sum(changedamount) Amount,ott.CostCenter from(
											select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter 
											from salarymonthlydetail smd,salarymonthly sm,CRG CC,SALARYEMPCOSTCENTER empcc
											where salarymonth=%d 
											AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) 
											AND sm.branchid is NULL AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID
											AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID
											)ott
											where ott.itemgroup in(1,8)
											group by ott.Employeeid,ott.CostCenter) tt
											Left Outer join 
											( select  Sum(A.amount) amount,E.EmployeeID,A.CostCenter  from
											( select S.EmployeeID, Sum(sd.changedamount) Amount,(CC.[DESCRIPTION])as CostCenter 
											from SALARYMONTHLY S,SalaryMonthlyDetail Sd,CRG CC,SALARYEMPCOSTCENTER empcc 
											where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
											AND S.SalaryMonth=%d  and Sd.ItemGroup IN (2) 
											AND S.EmployeeID IN(%q) 
											AND CC.CrgID=empcc.CostCenterID AND S.EmployeeID=empcc.EmployeeID
											AND sd.SALARYMONTHLYID=empcc.SALARYMONTHLYID
											group by S.EmployeeID,CC.[DESCRIPTION]
											) A, Employee E 
											where A.EmployeeID = E.EmployeeID 
											group by E.EmployeeID,A.CostCenter)as ot on tt.Employeeid=ot.EmployeeID)

											union 
											select tab1.Employeeid,description,sum(changedamount) Amount,tab1.CostCenter from(
											select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter 
											from salarymonthlydetail smd,salarymonthly sm ,CRG CC,SALARYEMPCOSTCENTER empcc 
											where salarymonth=%d
											AND sm.salarymonthlyid=smd.salarymonthlyid 
											AND sm.EMPLOYEEID IN(%q) 
											AND sm.branchid is NULL
											AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID
											AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID
											)tab1
											where tab1.itemgroup=3
											group by tab1.Employeeid,tab1.itemid,tab1.description,tab1.CostCenter

											UNION 
											SELECT tab1.Employeeid,'Net Pay' description,sum(changedamount) Amount,tab1.CostCenter FROM (
											select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter 
											from salarymonthlydetail smd,salarymonthly sm ,CRG CC,SALARYEMPCOSTCENTER empcc 
											where salarymonth=%d
											AND sm.salarymonthlyid=smd.salarymonthlyid 
											AND sm.EMPLOYEEID IN(%q) 
											AND sm.branchid is NULL
											AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID
											AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID
											)tab1
											where tab1.itemgroup=5 AND tab1.ITEMCODE=-132 AND tab1.ITEMID=-132
											group by tab1.Employeeid,tab1.itemid,tab1.description,tab1.CostCenter
											) a
											JOIN
											Employee l
											ON l.Employeeid  = a.Employeeid 
											join Location loc ON l.locationid=loc.locationid 
											order by Employeeid", dSalDate, sEmpIDs, dSalDate, sEmpIDs,
                dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs);
            FinallSet = tc.ExecuteDataSet(sql);
            if (sTableName != "")
                tc.ExecuteNonQuery("Drop TAble %q", sTableName);

            return FinallSet;
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int ID, int userID)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET CreatedBy=%n,ModifiedBy=%n  Where EmployeeID=%n", userID, userID,
                ID);
            tc.ExecuteNonQuery("DELETE FROM Employee WHERE EmployeeID=%n", ID);
        }

        internal static void DeleteAll(TransactionContext tc, int userID)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET CreatedBy=%n,ModifiedBy=%n", userID, userID);
            tc.ExecuteNonQuery("DELETE FROM Employee");
        }

        internal static void DeleteEmpContact(TransactionContext tc, int employeeId)
        {
            tc.ExecuteNonQuery("DELETE FROM EmpContact where employeeID=%n", employeeId);
        }

        #endregion

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

        internal static DataSet GetMaternityLeaveEmployeeBasicInfo(TransactionContext tc, int empId)
        {
            DataSet ds = new DataSet();
            string sql = string.Empty;
            try
            {
                sql = SQLParser.MakeSQL(@"SELECT v.EmployeeNo EmpNo,
                                    v.Name EmpName,
                                    v.Section Section,
                                    v.JoiningDate JoiningDate, 
                                    v.[Designation],
                                    v.GROSSSALARY GrossSalary, v.BasicSalary BasicSalary, l.APRFROMDATE LeavePeriodFrom, l.APRTODATE LeavePeriodTo, l.APRTODATE +1 AfterLeaveNextDate FROM vw_EmployeeImportantInfo v
                                    LEFT JOIN LEAVEENTRY l ON v.EmployeeID = l.EMPID
                                    WHERE v.EmployeeID = %n AND l.APRPARAMID = 3 ORDER BY l.APRFROMDATE DESC
                                    ", empId);
                ds = tc.ExecuteDataSet(sql);

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

        internal static IDataReader GetMaternityLeaveEmployee(TransactionContext tc, DateTime formDate, DateTime toDate)
        {
            string sSQL = SQLParser.MakeSQL(@"SELECT * FROM EMPLOYEE e
                                            LEFT JOIN LEAVEENTRY l ON e.EMPLOYEEID = l.EMPID
                                            WHERE l.APRPARAMID = 3
                                            AND (l.APRFROMDATE BETWEEN %d AND %d
                                            OR l.APRTODATE BETWEEN %d AND %d
                                            OR %d BETWEEN l.APRFROMDATE AND l.APRTODATE
                                            OR %d BETWEEN l.APRFROMDATE AND l.APRTODATE) ORDER BY l.APRFROMDATE, l.APRTODATE DESC, e.EMPLOYEENO ASC"
                                                , formDate, toDate,
                                                 formDate, toDate,
                                                 formDate, toDate
                                             );
            return tc.ExecuteReader(sSQL);
        }

        internal static IDataReader GetEmployeesForPicker(TransactionContext tc, string employeeCode,
            string employeeName)
        {
            string sqlClause = string.Empty;

            if (!string.IsNullOrWhiteSpace(employeeCode))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) +
                            SQLParser.MakeSQL("em.Code LIKE %s", ("%" + employeeCode + "%"));
            }

            if (!string.IsNullOrWhiteSpace(employeeName))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) +
                            SQLParser.MakeSQL("em.Name LIKE %s", ("%" + employeeName + "%"));
            }

            return tc.ExecuteReader("SELECT * FROM Employee em %q", sqlClause);
        }

        internal static DataSet GetByMonthStartMonthEndForContinueFromDiscontinue(TransactionContext tc,
            DateTime startmonth, DateTime endmonth, int payrollTypeID)
        {
            //            string sql = SQLParser.MakeSQL(@"select (Select Name From Employee Where Employee.EMPLOYEEID = TargetData.EmployeeId) EmployeeName,
            //                                            (Select EmployeeNo  From Employee Where Employee.EMPLOYEEID = TargetData.EmployeeId) EmployeeNo,
            //                                            TargetData.EffectDate  ContinueDate,
            //                                            (Select LOGINID From Users Where Users.UserID = TargetData.CreatedBy) UserIdWhoChangedIt,
            //                                            TargetData.creationDate ChangedDate
            //                                            from
            //                                            (select * from employeehistory  where (creationDate >= %d And creationDate <= %d) AND (employeehistory.EMPLOYEEID in 
            //                                            (select Em.EMPLOYEEID from Employee Em Where Em.EMPLOYEEID = employeehistory.EMPLOYEEID And 
            //                                            Em.STATUS = 1 And Em.MonthStatusUpdate = 1))) TargetData
            //                                            ", startmonth, endmonth);
            string sql = SQLParser.MakeSQL(
                "Select Emp.Employeeid,eh.emplifecycleid,Emp.EMPLOYEENO,Emp.NAME EmployeeName,eh.effectdate ContinueDate,"
                + " US.LoginID UserIdWhoChangedIt,EH.CreationDate ChangedDate"
                + " from EmpLifeCycle EH,EMPLOYEE Emp ,USERS US"
                + " WHERE US.USERID=EH.CreatedBy AND "
                + " eh.effectdate between %d and %d AND  eh.iscontinue=1 AND"
                + " Emp.EMPLOYEEID=EH.EMPLOYEEID AND Emp.PayrollTypeID=%n AND eh.EmployeeID IN (SELECT EMPLOYEEID FROM EmpLifeCycle elc WHERE elc.IsDiscontinue=1 AND elc.EffectDate<%d) order by Emp.EMPLOYEENO",
                startmonth, endmonth, payrollTypeID, startmonth);
            return
                tc.ExecuteDataSet(sql);
        }

        internal static IDataReader GetByDeptShiftGFType(TransactionContext tc, string deptIDs, int shiftID,
            string GFType, int payrollTypeID)
        {
            string subQuery = string.Empty;

            if (deptIDs != string.Empty)
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("e.DepartmentID in (%q)", deptIDs);
            }

            if (shiftID != 0)
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("ews.WorkPlanGroupID =%n", shiftID);
            }

            if (GFType != string.Empty)
            {
                subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("b." + "%q =%n", GFType, 1);
            }

            subQuery = SQLParser.TagSQL(subQuery) +
                       SQLParser.MakeSQL("e.PayrollTypeID =%n AND e.Status=1", payrollTypeID);

            return tc.ExecuteReader(
                "SELECT Distinct e.* FROM EMPLOYEE e Inner join EMPLOYEEWORKPLANSETUP ews on E.EmployeeID= ews.Employeeid INNER JOIN DEPARTMENT d ON d.DEPARTMENTID=e.DEPARTMENTID  %q Order by e.Employeeno asc",
                subQuery);
        }

        internal static DataSet GetITAndBasic(TransactionContext tc, DateTime startmonth, DateTime endmonth)
        {
            string sql = SQLParser.MakeSQL(
                @"select (Select Employee.Name From Employee Where Employee.EmployeeID = TEmp.empId) Name,

										  (Select Employee.EMPLOYEENO  From Employee Where Employee.EmployeeID = TEmp.empId) EmpNo,

										  (select DESIGNATION.NAME from DESIGNATION Where Designation.DESIGNATIONID in (select Employee.DESIGNATIONID From Employee Where Employee.EMPLOYEEID = Temp.empId)) Designation,

										  (select Grades.DESCRIPTION  from Grades Where Grades.GRADEID in (select Employee.GRADEID From Employee Where Employee.EMPLOYEEID = Temp.empId)) Grade,

										  (select Employee.GENDER From Employee Where Employee.EMPLOYEEID = Temp.empId) Gander,
										  TEmp.Amount From

										  (select EMPLOYEEID empId, CHANGEDAMOUNT Amount  from SALARYMONTHLY ,SALARYMONTHLYDETAIL where  SALARYMONTHLY.SALARYMONTHLYID =SALARYMONTHLYDETAIL.SALARYMONTHLYID 
										  and SALARYMONTHLY.salarymonth >= %d And SALARYMONTHLY.salarymonth <= %d and SALARYMONTHLYDETAIL.ITEMCODE =-129 
										  and SALARYMONTHLY.PAYROLLTYPEID =1) Temp", startmonth, endmonth);
            return tc.ExecuteDataSet(sql);
        }

        internal static DataSet GetEmployeeIncrement(TransactionContext tc, string empIDs)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(@" SELECT Sl,s.EmployeeID,SUM(Amount) Amount,MAX(EffectDate) EffectDate
										FROM
										(SELECT Row_number() OVER (PARTITION BY gs.EmployeeID order by EffectDate) Sl,gs.EmployeeID, -(gs.BASICSALARY) AS Amount,EffectDate
										from GRADESALARYASSIGNMENT AS gs
										WHERE gs.EMPLOYEEID  IN (%q)
										AND ARREARINFO = 1
										AND gs.BASICSALARY IS NOT NULL AND gs.BASICSALARY <> 0
										UNION 
										SELECT (Row_number() OVER (PARTITION BY gs.EmployeeID order by EffectDate))-1 Sl,gs.EmployeeID, gs.BASICSALARY AS Amount,EffectDate 
										from GRADESALARYASSIGNMENT AS gs 
										WHERE gs.EMPLOYEEID IN (%q)
										AND ARREARINFO = 1
										AND gs.BASICSALARY IS NOT NULL AND gs.BASICSALARY <> 0
										) s
                                        WHERE EffectDate > '31 Dec 2018'
										GROUP BY SL,s.EmployeeID
										HAVING Sum(Amount) > 0
										ORDER BY s.EmployeeID

										", empIDs, empIDs);
            return tc.ExecuteDataSet(sql);
        }

        internal static IDataReader GetAllEmps(TransactionContext tc)
        {
            return tc.ExecuteReader("Select * From Employee");
        }

        internal static IDataReader GetAllEmps(TransactionContext tc, int payrollTypeid)
        {
            return tc.ExecuteReader("Select * From Employee where payrolltypeid=%n", payrollTypeid);
        }

        internal static void UpdateCardInformation(TransactionContext tc, int employeeID, int CardID, DateTime cardDate,
            EnumCardStatus cardStatus, bool IsAutoProcess)
        {
            string sql =
                SQLParser.MakeSQL(
                    "UPDATE Employee SET CardID= %n,CardDate=%D,CardStatus=%n,IsAutoProcess=%b WHERE EMPLOYEEID=%n",
                    DataReader.GetNullValue(CardID), cardDate, (int)cardStatus, IsAutoProcess, employeeID);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetJoiningData(TransactionContext tc, DateTime formDate)
        {
            string sSQL = SQLParser.MakeSQL(
                "Select * From Employee where JOININGDATE between %d and %d order by EMPLOYEEID",
                PayrollGlobalFunctions.PayrollFirstDateOfMonth(formDate),
                PayrollGlobalFunctions.PayrollLastDateOfMonth(formDate));
            return tc.ExecuteReader(sSQL);
        }

        internal static DataSet GetShortInformation(TransactionContext tc, string empIDs)
        {
            return tc.ExecuteDataSet(@"select EmployeeID,EmployeeNo,NAME,
									(SELECT [NAME] FROM DESIGNATION d WHERE d.DESIGNATIONID=emp.DESIGNATIONID) Designation,
									(SELECT DESCRIPTION FROM DEPARTMENT d WHERE d.DEPARTMENTID=emp.DEPARTMENTID) Project ,
									(SELECT DESCRIPTION FROM DEPARTMENT d WHERE d.DEPARTMENTID=(SELECT Parentid FROM DEPARTMENT d WHERE d.DEPARTMENTID=emp.DEPARTMENTID)) Department,
									(SELECT DESCRIPTION FROM Location d WHERE d.LOCATIONID=emp.LOCATIONID) Location ,
									(SELECT DESCRIPTION FROM Grades d WHERE d.gradeid=emp.gradeid) Grade ,
									(SELECT DESCRIPTION FROM Category d WHERE d.categoryid=emp.categoryid) Category ,  
									(SELECT Code FROM Grades d WHERE d.gradeid=emp.gradeid) GradeCode ,
									ISNULL((SELECT el.[DESCRIPTION] +ea.INSTITUTENAME FROM EMPACADEMIC ea,EducationLevel el WHERE el.EDUCATIONLEVELID=ea.EDUCATIONLEVELID 
AND ea.EMPLOYEEID=emp.EMPLOYEEID AND ea.LASTLEVEL=1),'') LastAcademic ,                                  
									CONVERT(NVARCHAR,CAST(emp.JOININGDATE AS DATETIME), 106)JOININGDATE,emp.LastPromotionDate,
									CONVERT(NVARCHAR,CAST(emp.BIRTHDATE AS DATETIME), 106)BIRTHDATE,emp.MOBILENO,emp.EMAILADDRESS,
									datediff(yy,emp.BIRTHDATE,GETDATE()) AGE,
									CONVERT(NVARCHAR,CAST(emp.JOININGDATE AS DATETIME), 106)+' To '+CONVERT(NVARCHAR,CAST(GETDATE() AS DATETIME), 106) ServiceLength
									from employee emp
									WHERE emp.EMPLOYEENO=%s", empIDs);
        }

        internal static IDataReader Get4Web(TransactionContext tc, string employeeNo)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE employeeNo=%s", employeeNo);
        }

        internal static IDataReader GetDepartmentHead(TransactionContext tc, EnumOGPositionType positionType,
            int DepartmentID)
        {
            return tc.ExecuteReader(
                @"select * from OrganEmployee, Employee where OrganEmployee.EmployeeID= Employee.EmployeeID
				and NodeID IN (select organogramID from organogram where departmentid=%n and organogramid in (
				select nodeid from OrganPosition where positionTypeid=%n ))", DepartmentID, positionType);
        }

        internal static IDataReader GetNew(TransactionContext tc, DateTime dt, int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND joiningdate<=%d order by EmployeeNo",
                EnumEmployeeStatus.Live, payrollTypeID, dt);
            return tc.ExecuteReader(sql);
        }

        internal static DataSet GetGradeWiseEmployeeCounts(TransactionContext tc)
        {
            DataSet designationWiseCountDS = new DataSet();
            try
            {
                designationWiseCountDS =
                    tc.ExecuteDataSet(@"Select '['+gd.Code+']-'+gd.[Description] GradeOrDesignation,empC.Count 
										From 
										(
										Select GradeID,COUNT(*) Count 
										From Employee
										 Where Status = %n  
										Group by GradeID
										)empC ,Grades gd
										where empC.GradeID = gd.GradeID", (int)EnumEmployeeStatus.Live);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return designationWiseCountDS;
        }

        internal static DataSet GetAllGeneralInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                #region Gender Value Construction

                StringBuilder sbGender = new StringBuilder();
                sbGender.Append("Case e.Gender ");
                foreach (EnumGender enmGender in Enum.GetValues(typeof(EnumGender)).Cast<EnumGender>())
                {
                    sbGender.AppendFormat("when {0} Then '{1}' ", (int)enmGender, enmGender.ToString());
                }

                sbGender.Append("Else '' End ");

                #endregion

                #region BloodGroup Value Construction

                StringBuilder sbBloodGroup = new StringBuilder();
                sbBloodGroup.Append("Case e.BloodGroup ");
                foreach (EnumBloodGroup enmBG in Enum.GetValues(typeof(EnumBloodGroup)).Cast<EnumBloodGroup>())
                {
                    sbBloodGroup.AppendFormat("when {0} Then '{1}' ", (int)enmBG, enmBG.BloodGroupToFriendlyName());
                }

                sbBloodGroup.Append("Else '' End ");

                #endregion

                #region Marital Value Construction

                StringBuilder sbMaritalStatus = new StringBuilder();
                sbMaritalStatus.Append("Case e.MARITALSTATUSID ");
                foreach (EnumMaritalStatus enmMaritalStatus in Enum.GetValues(typeof(EnumMaritalStatus))
                    .Cast<EnumMaritalStatus>().Where(x => x != EnumMaritalStatus.None))
                {
                    sbMaritalStatus.AppendFormat("when {0} Then '{1}' ", (int)enmMaritalStatus,
                        enmMaritalStatus.ToString());
                }

                sbMaritalStatus.Append("Else '' End ");

                #endregion

                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);

                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(
                        @"Select e.EmployeeNo, ISNULL(e.FIRSTNAME,e.NAME) FIRSTNAME, e.MIDDLENAME,e.LASTNAME,
												 %q Gender, ISNULL(r.NAME,'') Religion,%q BloodGroup, %q MaritalStatus, PassportNo, TINNO TaxIdeentificationNO,
												 ISNULL(n.DESCRIPTION,'') Nationality, e.NATIONALID,CONVERT(VARCHAR(11),e.JOININGDATE,106) JOININGDATE, 
												 CONVERT(VARCHAR(11),e.BIRTHDATE,106) BIRTHDATE, ISNULL(c.DESCRIPTION,'') CATEGORY,e.FATHERNAME, 
												 e.MOTHERNAME, ISNULL(ftr.DESCRIPTION,'') FatherOccupation,
												 ISNULL(mtr.DESCRIPTION,'') MotherOccupation,e.BIRTHPLACE
												 from Employee e 
												 LEFT JOIN RELIGION r ON e.RELIGIONID = r.RELIGIONID 
												 LEFT JOIN NATIONALITY n ON e.NATIONALITYID = n.NATIONALITYID  
												 LEFT JOIN CATEGORY c ON e.CATEGORYID = c.CATEGORYID
												 LEFT JOIN OCCUPATION ftr ON e.FATHEROCCUPATIONID = ftr.OCCUPATIONID 
												 LEFT JOIN OCCUPATION mtr ON e.MOTHEROCCUPATIONID = mtr.OCCUPATIONID "
                        , sbGender.ToString(), sbBloodGroup.ToString(), sbMaritalStatus.ToString());
                }
                else
                {
                    sql = SQLParser.MakeSQL(
                        @"Select e.EmployeeNo, ISNULL(e.FIRSTNAME,e.NAME) FIRSTNAME, e.MIDDLENAME,e.LASTNAME,
												 %q Gender, ISNULL(r.NAME,'') Religion,%q BloodGroup, %q MaritalStatus, PassportNo, TINNO TaxIdeentificationNO,
												 ISNULL(n.DESCRIPTION,'') Nationality, e.NATIONALID,CONVERT(VARCHAR(11),e.JOININGDATE,106) JOININGDATE, 
												 CONVERT(VARCHAR(11),e.BIRTHDATE,106) BIRTHDATE, ISNULL(c.DESCRIPTION,'') CATEGORY,e.FATHERNAME, 
												 e.MOTHERNAME, ISNULL(ftr.DESCRIPTION,'') FatherOccupation,
												 ISNULL(mtr.DESCRIPTION,'') MotherOccupation,e.BIRTHPLACE
												 from Employee e 
												 LEFT JOIN RELIGION r ON e.RELIGIONID = r.RELIGIONID 
												 LEFT JOIN NATIONALITY n ON e.NATIONALITYID = n.NATIONALITYID  
												 LEFT JOIN CATEGORY c ON e.CATEGORYID = c.CATEGORYID
												 LEFT JOIN OCCUPATION ftr ON e.FATHEROCCUPATIONID = ftr.OCCUPATIONID 
												 LEFT JOIN OCCUPATION mtr ON e.MOTHEROCCUPATIONID = mtr.OCCUPATIONID 
												 where e.Employeeid in(%q)   "
                        , sbGender.ToString(), sbBloodGroup.ToString(), sbMaritalStatus.ToString(), sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetAllContactInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, ec.PARMANENTADDRESS PermanentAddress, d.NAME PermanentDistrict, t.NAME PermanentThana, ec.PARMANENTTELEPHONE PermanentTelephoneNo,
								ec.parmanentmobile PermanentMobileNo, ec.PRESENTADDRESS, pd.NAME PresentDistrict, pt.NAME PresentThana, ec.PRESENTTELEPHONE PresentTelephoneNo,
								ec.PresentMobile, ec.PERSONALTELEPHONE PersonalTelephoneNo, ec.mobile PersonalMobileNo, ec.PERSONALEMAIL, ec.OFFICIALEMAIL, ec.FAX,
								ec.EMERGENCYCONTACTADDRESS, ec.EMERGENCYCONTACTPERSON, ec.EMERGENCYMOBILE EmergencyMobileNo, pr.DESCRIPTION Relation
								FROM EMPCONTACT ec
								LEFT JOIN EMPLOYEE e ON ec.EMPLOYEEID = e.EMPLOYEEID 
								LEFT JOIN DISTRICT d ON ec.PARMANENTDISTRICTID = d.DISTRICTID
								LEFT JOIN THANA t ON ec.PARMANENTTHANAID = t.THANAID
								LEFT JOIN DISTRICT pd ON ec.PRESENTDISTRICTID = pd.DISTRICTID
								LEFT JOIN THANA pt ON ec.PRESENTTHANAID = pt.THANAID
								LEFT JOIN PERSONRELATION pr ON ec.CPRELATIONID = pr.RELATIONID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, ec.PARMANENTADDRESS PermanentAddress, d.NAME PermanentDistrict, t.NAME PermanentThana, ec.PARMANENTTELEPHONE PermanentTelephoneNo,
								ec.parmanentmobile PermanentMobileNo, ec.PRESENTADDRESS, pd.NAME PresentDistrict, pt.NAME PresentThana, ec.PRESENTTELEPHONE PresentTelephoneNo,
								ec.PresentMobile, ec.PERSONALTELEPHONE PersonalTelephoneNo, ec.mobile PersonalMobileNo, ec.PERSONALEMAIL, ec.OFFICIALEMAIL, ec.FAX,
								ec.EMERGENCYCONTACTADDRESS, ec.EMERGENCYCONTACTPERSON, ec.EMERGENCYMOBILE EmergencyMobileNo, pr.DESCRIPTION Relation
								FROM EMPCONTACT ec
								LEFT JOIN EMPLOYEE e ON ec.EMPLOYEEID = e.EMPLOYEEID 
								LEFT JOIN DISTRICT d ON ec.PARMANENTDISTRICTID = d.DISTRICTID
								LEFT JOIN THANA t ON ec.PARMANENTTHANAID = t.THANAID
								LEFT JOIN DISTRICT pd ON ec.PRESENTDISTRICTID = pd.DISTRICTID
								LEFT JOIN THANA pt ON ec.PRESENTTHANAID = pt.THANAID
								LEFT JOIN PERSONRELATION pr ON ec.CPRELATIONID = pr.RELATIONID where e.Employeeid in(%q)",
                        sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetSpouseInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(@"Select e.EMPLOYEENO, es.NAME SpouseName, el.DESCRIPTION SpouseEducation, 
								o.DESCRIPTION SpouseOccupation, CONVERT(VARCHAR(11),es.MARRIAGEDATE,106) MarriageDate
								FROM EMPSPOUSE es
								LEFT JOIN EMPLOYEE e ON es.EMPLOYEEID = e.EMPLOYEEID
								LEFT JOIN EDUCATIONLEVEL el ON es.EDUCATIONLEVELID = el.EDUCATIONLEVELID
								LEFT JOIN OCCUPATION o ON es.OCCUPATIONID = o.OCCUPATIONID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(@"Select e.EMPLOYEENO, es.NAME SpouseName, el.DESCRIPTION SpouseEducation, 
								o.DESCRIPTION SpouseOccupation, CONVERT(VARCHAR(11),es.MARRIAGEDATE,106) MarriageDate
								FROM EMPSPOUSE es
								LEFT JOIN EMPLOYEE e ON es.EMPLOYEEID = e.EMPLOYEEID
								LEFT JOIN EDUCATIONLEVEL el ON es.EDUCATIONLEVELID = el.EDUCATIONLEVELID
								LEFT JOIN OCCUPATION o ON es.OCCUPATIONID = o.OCCUPATIONID where e.Employeeid in(%q)",
                        sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetChildInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                #region Gender Value Construction

                StringBuilder sbGender = new StringBuilder();
                sbGender.Append("Case ec.Gender ");
                foreach (EnumGender enmGender in Enum.GetValues(typeof(EnumGender)).Cast<EnumGender>())
                {
                    sbGender.AppendFormat("when {0} Then '{1}' ", (int)enmGender, enmGender.ToString());
                }

                sbGender.Append("Else '' End ");

                #endregion

                #region Marital Value Construction

                StringBuilder sbMaritalStatus = new StringBuilder();
                sbMaritalStatus.Append("Case ec.MARITALSTATUS ");
                foreach (EnumMaritalStatus enmMaritalStatus in Enum.GetValues(typeof(EnumMaritalStatus))
                    .Cast<EnumMaritalStatus>().Where(x => x != EnumMaritalStatus.None))
                {
                    sbMaritalStatus.AppendFormat("when {0} Then '{1}' ", (int)enmMaritalStatus,
                        enmMaritalStatus.ToString());
                }

                sbMaritalStatus.Append("Else '' End ");

                #endregion

                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, ec.NAME ChildName, %q ChildGender, CONVERT(VARCHAR(11),e.BIRTHDATE,106) ChildBirthDate,
							o.DESCRIPTION ChildOccupation, %q ChildMaritalStatus
							FROM EMPCHILDREN ec
							LEFT JOIN EMPLOYEE e ON ec.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN OCCUPATION o ON ec.OCCUPATIONID = o.OCCUPATIONID", sbGender.ToString(),
                        sbMaritalStatus.ToString());
                }
                else
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, ec.NAME ChildName, %q ChildGender, CONVERT(VARCHAR(11),e.BIRTHDATE,106) ChildBirthDate,
							o.DESCRIPTION ChildOccupation, %q ChildMaritalStatus
							FROM EMPCHILDREN ec
							LEFT JOIN EMPLOYEE e ON ec.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN OCCUPATION o ON ec.OCCUPATIONID = o.OCCUPATIONID where e.Employeeid in(%q)",
                        sbGender.ToString(), sbMaritalStatus.ToString(), sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetEmpExperienceInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, eex.EMPLOYER Employer, eex.CONTACTPERSON, eex.ADDRESS, eex.TELEPHONE TelephoneNo, eex.EMPLOYERACTIVITY BussinessType, 
							eex.DESIGNATION, eex.JOBDESCRIPTION RoleDescription, 
							CONVERT(VARCHAR(11),eex.FROMDATE,106) EmployesFromDate,
							CONVERT(VARCHAR(11),eex.TODATE,106) EmployesToDate
							FROM EMPEXPERIENCE eex
							LEFT JOIN EMPLOYEE e ON eex.EMPLOYEEID = e.EMPLOYEEID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, eex.EMPLOYER Employer, eex.CONTACTPERSON, eex.ADDRESS, eex.TELEPHONE TelephoneNo, eex.EMPLOYERACTIVITY BussinessType, 
							eex.DESIGNATION, eex.JOBDESCRIPTION RoleDescription, 
							CONVERT(VARCHAR(11),eex.FROMDATE,106) EmployesFromDate,
							CONVERT(VARCHAR(11),eex.TODATE,106) EmployesToDate
							FROM EMPEXPERIENCE eex
							LEFT JOIN EMPLOYEE e ON eex.EMPLOYEEID = e.EMPLOYEEID where e.Employeeid in(%q)", sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetEmpTrainingInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                #region EnumTrainingCompletedFrom Construction

                StringBuilder sbSponsorType = new StringBuilder();
                sbSponsorType.Append("Case et.SPONSOREDTYPE ");
                foreach (EnumTrainingCompletedFrom enmGender in Enum.GetValues(typeof(EnumTrainingCompletedFrom))
                    .Cast<EnumTrainingCompletedFrom>())
                {
                    sbSponsorType.AppendFormat("when {0} Then '{1}' ", (int)enmGender, enmGender.ToString());
                }

                sbSponsorType.Append("Else '' End ");

                #endregion

                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, 
							t.NAME TrainingType, 
							et.NAME TrainingTitle, 
							nt.NAME NatureOfTraining,
							et.DESCRIPTION, 
							i.NAME Institution, 
							et.TRAININGPLACE TrainingPlace, 
							c.NAME Country, 
							et.ACHIEVEMENT Achievements,
							%q SponsorType,
							et.FEES TrainingFees, et.OTHERCOST OtherCost, 
							CONVERT(VARCHAR(11),et.FROMDATE,106) FromDate,
							CONVERT(VARCHAR(11),et.TODATE,106) ToDate
							FROM EMPTRAINING et
							LEFT JOIN EMPLOYEE e ON et.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN TRAINING t ON et.TRAININGTYPEID = t.TRAININGTYPEID
							LEFT JOIN NATUREOFTRAINING nt ON et.NATUREOFTRAININGID = nt.NATUREOFTRAININGID
							LEFT JOIN INSTITUTION i ON et.INSTITUTIONID = i.INSTITUTIONID
							LEFT JOIN COUNTRY c ON et.COUNTRYID = c.COUNTRYID", sbSponsorType.ToString());
                }
                else
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, 
							t.NAME TrainingType, 
							et.NAME TrainingTitle, 
							nt.NAME NatureOfTraining,
							et.DESCRIPTION, 
							i.NAME Institution, 
							et.TRAININGPLACE TrainingPlace, 
							c.NAME Country, 
							et.ACHIEVEMENT Achievements,
							%q SponsorType,
							et.FEES TrainingFees, et.OTHERCOST OtherCost, 
							CONVERT(VARCHAR(11),et.FROMDATE,106) FromDate,
							CONVERT(VARCHAR(11),et.TODATE,106) ToDate
							FROM EMPTRAINING et
							LEFT JOIN EMPLOYEE e ON et.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN TRAINING t ON et.TRAININGTYPEID = t.TRAININGTYPEID
							LEFT JOIN NATUREOFTRAINING nt ON et.NATUREOFTRAININGID = nt.NATUREOFTRAININGID
							LEFT JOIN INSTITUTION i ON et.INSTITUTIONID = i.INSTITUTIONID
							LEFT JOIN COUNTRY c ON et.COUNTRYID = c.COUNTRYID where e.Employeeid in(%q)",
                        sbSponsorType.ToString(), sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetEmpAcademicInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, et.DESCRIPTION EducationType, el.DESCRIPTION DegreeTitle,
							d.DESCRIPTION Discipline_Department, i.NAME Institution, ea.INSTITUTENAME InstitutionName,
							r.DESCRIPTION Result, ea.PASSINGYEAR, ea.GPAORMARKS,
							CASE ea.LASTLEVEL
							WHEN 1 THEN 'Yes'
							ELSE 'No' END HighestEducation
							FROM EMPACADEMIC ea
							LEFT JOIN EMPLOYEE e ON ea.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN EDUCATIONTYPE et ON ea.EDUCATIONTYPEID = et.EDUCATIONTYPEID
							LEFT JOIN EDUCATIONLEVEL el ON ea.EDUCATIONLEVELID = el.EDUCATIONLEVELID
							LEFT JOIN DISCIPLINE d ON ea.DISCIPLINEID = d.DISCIPLINEID
							LEFT JOIN INSTITUTION i ON ea.INSTITUTIONID = i.INSTITUTIONID
							LEFT JOIN RESULTTYPE r ON ea.RESULTTYPEID = r.RESULTTYPEID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(
                        @"SELECT e.EMPLOYEENO, et.DESCRIPTION EducationType, el.DESCRIPTION DegreeTitle,
							d.DESCRIPTION Discipline_Department, i.NAME Institution, ea.INSTITUTENAME InstitutionName,
							r.DESCRIPTION Result, ea.PASSINGYEAR, ea.GPAORMARKS,
							CASE ea.LASTLEVEL
							WHEN 1 THEN 'Yes'
							ELSE 'No' END HighestEducation
							FROM EMPACADEMIC ea
							LEFT JOIN EMPLOYEE e ON ea.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN EDUCATIONTYPE et ON ea.EDUCATIONTYPEID = et.EDUCATIONTYPEID
							LEFT JOIN EDUCATIONLEVEL el ON ea.EDUCATIONLEVELID = el.EDUCATIONLEVELID
							LEFT JOIN DISCIPLINE d ON ea.DISCIPLINEID = d.DISCIPLINEID
							LEFT JOIN INSTITUTION i ON ea.INSTITUTIONID = i.INSTITUTIONID
							LEFT JOIN RESULTTYPE r ON ea.RESULTTYPEID = r.RESULTTYPEID where e.Employeeid in(%q)",
                        sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetEmpReferenceInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO,
							er.NAME RefereeName,
							pr.DESCRIPTION Relation,
							o.DESCRIPTION RefereeOccupation,
							er.ADDRESS Address, er.EMAILADDRESS EmailAddress, er.TELEPHONE TelephoneNo, er.REFERENCEMOBILE MobileNo
							FROM EMPREFERENCE er
							LEFT JOIN EMPLOYEE e ON er.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN PERSONRELATION pr ON er.RELATIONID = pr.RELATIONID
							LEFT JOIN OCCUPATION o ON er.OCCUPATIONID = o.OCCUPATIONID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO,
							er.NAME RefereeName,
							pr.DESCRIPTION Relation,
							o.DESCRIPTION RefereeOccupation,
							er.ADDRESS Address, er.EMAILADDRESS EmailAddress, er.TELEPHONE TelephoneNo, er.REFERENCEMOBILE MobileNo
							FROM EMPREFERENCE er
							LEFT JOIN EMPLOYEE e ON er.EMPLOYEEID = e.EMPLOYEEID
							LEFT JOIN PERSONRELATION pr ON er.RELATIONID = pr.RELATIONID
							LEFT JOIN OCCUPATION o ON er.OCCUPATIONID = o.OCCUPATIONID where e.Employeeid in(%q)",
                        sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetEmpPublicationInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO EmployeeNo, ep.TITLE PublicationTitle,
							ep.PUBLICATIONTYPE PublicationType, ep.PUBLISHERSNAME PublicationName,
							ep.DESCRIPTION Description, ep.REMARKS Remarks,
							CONVERT(VARCHAR(11),ep.PUBLICATIONDATE,106) DateOfPublication
							FROM EMPPUBLICATION ep
							LEFT JOIN EMPLOYEE e ON ep.EMPLOYEEID = e.EMPLOYEEID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO EmployeeNo, ep.TITLE PublicationTitle,
							ep.PUBLICATIONTYPE PublicationType, ep.PUBLISHERSNAME PublicationName,
							ep.DESCRIPTION Description, ep.REMARKS Remarks,
							CONVERT(VARCHAR(11),ep.PUBLICATIONDATE,106) DateOfPublication
							FROM EMPPUBLICATION ep
							LEFT JOIN EMPLOYEE e ON ep.EMPLOYEEID = e.EMPLOYEEID where e.Employeeid in(%q)", sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataTable GetBasicSalary(TransactionContext tc)
        {
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT EMPLOYEEID, BASICSALARY from employee where status =1");


                DataTable ds = tc.ExecuteDataTable(sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataTable GetBasicSalaryWithDiscontinue(TransactionContext tc, DateTime salaryMonth)
        {
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT EMPLOYEEID, BASICSALARY, ENDOFCONTRACTDATE from employee where status =1
                                                    Union
                                                select EMPLOYEEID, BASICSALARY, ENDOFCONTRACTDATE from employee where status = 2 and ENDOFCONTRACTDATE between %d and %d"
                                    , GlobalExtensions.FirstDateOfMonth(salaryMonth), GlobalExtensions.LastDateOfMonth(salaryMonth));


                DataTable ds = tc.ExecuteDataTable(sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        internal static DataTable GetBasicSalary(TransactionContext tc, int id)
        {
            try
            {
                string sql = SQLParser.MakeSQL(@"SELECT BASICSALARY from employee where EmployeeID = %n", id);


                DataTable ds = tc.ExecuteDataTable(sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        internal static DataSet GetEmpNomineeInfo(TransactionContext tc, string sEmpIDs)
        {
            try
            {
                string sTableName;
                sEmpIDs = IDHelper.GetIDs(tc, sEmpIDs, out sTableName);
                string sql = "";
                if (sEmpIDs == "")
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO EmployeeNo,
						np.DESCRIPTION NominationPurpose,
						CONVERT(VARCHAR(11),en.NOMINATIONDATE,106) DateOfNomination, en.NAME NomineeName, en.PERCENTAGE,
						CONVERT(VARCHAR(11),en.BIRTHDATE,106) NomineesDateofBirth,
						o.DESCRIPTION NomineesOccupation, en.ADDRESS Adderess, en.TELEPHONE TelephoneNo, pr.DESCRIPTION Relation, en.EMAILADDRESS EmailAddress
						FROM EMPNOMINEE en
						LEFT JOIN EMPLOYEE e ON en.EMPLOYEEID = e.EMPLOYEEID
						LEFT JOIN NOMINATIONPURPOSE np ON en.NOMINATIONPURPOSEID = np.NOMINATIONPURPOSEID
						LEFT JOIN OCCUPATION o ON en.OCCUPATIONID = o.OCCUPATIONID
						LEFT JOIN PERSONRELATION pr ON en.RELATIONID = pr.RELATIONID");
                }
                else
                {
                    sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO EmployeeNo,
						np.DESCRIPTION NominationPurpose,
						CONVERT(VARCHAR(11),en.NOMINATIONDATE,106) DateOfNomination, en.NAME NomineeName, en.PERCENTAGE,
						CONVERT(VARCHAR(11),en.BIRTHDATE,106) NomineesDateofBirth,
						o.DESCRIPTION NomineesOccupation, en.ADDRESS Adderess, en.TELEPHONE TelephoneNo, pr.DESCRIPTION Relation, en.EMAILADDRESS EmailAddress
						FROM EMPNOMINEE en
						LEFT JOIN EMPLOYEE e ON en.EMPLOYEEID = e.EMPLOYEEID
						LEFT JOIN NOMINATIONPURPOSE np ON en.NOMINATIONPURPOSEID = np.NOMINATIONPURPOSEID
						LEFT JOIN OCCUPATION o ON en.OCCUPATIONID = o.OCCUPATIONID
						LEFT JOIN PERSONRELATION pr ON en.RELATIONID = pr.RELATIONID where e.Employeeid in(%q)",
                        sEmpIDs);
                }

                DataSet ds = tc.ExecuteDataSet(sql);
                if (sTableName != "")
                    tc.ExecuteNonQuery("Drop Table %q", sTableName);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static DataSet GetEmpFromView(TransactionContext tc, string empNo)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM VW_EMPLOYEEBASIC WHERE EMPLOYEENO = %s", empNo);
            return tc.ExecuteDataSet(sql);
        }

        internal static DataSet GetDFSL(TransactionContext tc)
        {
            DataSet designationWiseCountDS = new DataSet();
            try
            {
                tc.ExecuteNonQuery(@"DECLARE @CurrentLevel INT
									SET @CurrentLevel = 0    
									DELETE FROM Department2
									INSERT INTO Department2
									(ID, Parent_ID, Level, Hierarchy)
									SELECT DepartmentID,
									ParentID, 
									0 AS Level, 
									[DESCRIPTION] AS Hierarchy
									FROM Department
									WHERE ParentID IS NULL
									WHILE @@ROWCOUNT > 0
									BEGIN
										SET @CurrentLevel = @CurrentLevel + 1 --Started at 0
									
									 INSERT INTO Department2
											(ID, Parent_ID, Level, Hierarchy)
									 SELECT p.DEPARTMENTID,
											p.PARENTID, 
											@CurrentLevel AS Level, 
											h.Hierarchy + ',' + p.[DESCRIPTION]
									   FROM Department p 
									  INNER JOIN Department2 h
										 ON p.PARENTID = h.ID
										AND h.Level = @CurrentLevel - 1
								END");

                designationWiseCountDS =
                    tc.ExecuteDataSet(@"SELECT * FROM Department2");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return designationWiseCountDS;
        }

        internal static IDataReader GetbyDepartment(TransactionContext tc, int deptId)
        {
            string sql = SQLParser.MakeSQL(
                "SELECT * FROM Employee WHERE Status=%n and DepartmentID = %n order by EmployeeNo",
                EnumEmployeeStatus.Live, deptId);
            return tc.ExecuteReader(sql);
        }
        internal static IDataReader GetByPrimaryLMID(TransactionContext tc, int employeeID)
        {
            string strSQLQuery = SQLParser.MakeSQL(
                @"SELECT * FROM Employee WHERE (LINEMANAGERID=%n OR SecondLineManagerID=%n) and Status = %n",
                employeeID, employeeID, (int)EnumEmployeeStatus.Live);
            return tc.ExecuteReader(strSQLQuery);
        }
        internal static DataSet GetEmpBasicInfoForPSlip(TransactionContext tc, string empIDs, DateTime dMonth)
        {
            string sql = SQLParser.MakeSQL(
                @"SELECT Distinct E.NAME Name,E.EMPLOYEENO employeeNo,G.CODE Grade, DG.Name Designation,L.Description LName,
                                            D.DESCRIPTION Department,B.NAME  BName, SM.thismonthGross Gross, SM.THISMONTHBASIC ActualBasic,  
                                            CASE WHEN E.PAYMENTMODE = 1 THEN 'Cash Payment' WHEN E.PAYMENTMODE = 2 THEN 'Bank Transfer'
                                            WHEN E.PAYMENTMODE = 3 THEN 'Provision' ELSE '' END AS PaymentMode,
                                            SM.ACCOUNTNO,SM.SalaryMonth, cat.DESCRIPTION Category,E.JOININGDATE,E.TINNo,
                                            E.DEPARTMENTID FROM SALARYMONTHLY SM
                                            Inner Join EMPLOYEE E on SM.EMPLOYEEID=E.EMPLOYEEID 
                                            Inner Join Grades G on Sm.GRADEID=G.GRADEID
                                            Inner Join DEPARTMENT  D on SM.DEPARTMENTID=D.DEPARTMENTID
                                            Inner Join DESIGNATION  DG on SM.DESIGNATIONID =DG.DESIGNATIONID 
                                            Inner Join LOCATION  L on SM.LOCATIONID=L.LOCATIONID
                                            Left Outer Join BRANCHES  Br on SM.BRANCHID=Br.BRANCHID
                                            Left Outer Join BANKS  B on Br.BANKID=B.BANKID
                                            Inner Join CATEGORY cat on E.CATEGORYID=cat.CATEGORYID
                                            Inner Join SALARYMONTHLYDETAIL  SMD on SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
                                            AND SM.SalaryMonth=%d AND SMD.ITEMGROUP IN(1,2,8) 
                                            AND SM.EmployeeID IN(%q) ORDER BY E.EmployeeNo", dMonth, empIDs);

            return tc.ExecuteDataSet(sql);
        }

        internal static DataSet GetEmpDashBoardInfo(TransactionContext tc, int empID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string query1 = SQLParser.MakeSQL(
                    @"SELECT e.EMPLOYEENO, e.joiningDate, e.NAME, dept.DESCRIPTION DEPARTMENT, 
                                                desig.NAME DESIGNATION, g.DESCRIPTION GRADE, e.PHOTOPATH Photo  
                                                FROM EMPLOYEE e
                                                Left JOIN DEPARTMENT dept ON e.DEPARTMENTID=dept.DEPARTMENTID
                                                Left JOIN DESIGNATION desig ON e.DESIGNATIONID = desig.DESIGNATIONID
                                                Left JOIN GRADES g ON g.GRADEID=e.GRADEID
                                                WHERE e.employeeid=%n", empID);

                tempdataset = tc.ExecuteDataSet(query1);
                tempdataset.Tables[0].TableName = "EmployeeInfo";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());

                string query2 = SQLParser.MakeSQL(
                    @"SELECT a.ATTNNATIONALHOLIDAYID, a.DESCRIPTION HolidayType, a.FROMDATE, a.TODATE 
                                                    FROM AttnNationalHoliday a WHERE a.FromDate between %d and %d Order By a.FromDate asc",
                    DateTime.Today.Date, PayrollGlobalFunctions.LastDateOfYear(DateTime.Today));

                tempdataset = tc.ExecuteDataSet(query2);
                tempdataset.Tables[0].TableName = "HolidayInfo";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
                DateTime fromDate = DateTime.Today.AddMonths(-1);
                DateTime toDate = DateTime.Today;
                string query3 = SQLParser.MakeSQL(
                    @"SELECT dap.DAILYATTNPROCESSID, dap.ATTNDATE, dap.INTIME, dap.OUTTIME 
                                                    FROM DAILYATTNPROCESS dap WHERE ATTNDATE BETWEEN %d and %d AND dap.EMPLOYEEID=%n Order By dap.ATTNDATE DESC",
                    fromDate, toDate, empID);

                tempdataset = tc.ExecuteDataSet(query3);
                tempdataset.Tables[0].TableName = "AttendanceInfo";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());


                string query4 = SQLParser.MakeSQL(@" SELECT NULL as SentTime, E.Name, E.NAME +'['+ E.EMPLOYEENO +'] 
                                                    has delegated some responsibility to you.'  Description, d.DELEGATERESPONSIBILITYID
                                                    TranID, '/work-flow/pending-delegation/'+ Cast(d.DELEGATERESPONSIBILITYID as VarChar(500)) Link FROM DELEGATERESPONSIBILITY d
                                                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = d.FROMEMPLOYEEID
                                                    WHERE d.TOEMPLOYEEID = %n AND d.ISACCEPTED=0 AND d.Status=1
                                                union
                                                        SELECT WT.SENTTIME, emp.Name, ObjectDescription  Description,WT.WFMovementTranId TranId,'/work-flow/pending-job/'+ 
                                                    Cast(WT.WFMovementTranId as VarChar(500)) Link FROM WFMovementTran WT, WFMovementNext WN, Employee emp  
                                                    WHERE WN.Status=1 AND WN.EmployeeId=%n AND Emp.EMPLOYEEID=WT.FROMEMPLOYEEID
                                                    AND WT.WFMovementTranID = WN.WFMovementTranID
                                                Union                               
                                                
                                                        SELECT NULL as SentTime, E.Name NAME, E.NAME +'['+ 
                                                    E.EMPLOYEENO+'] has reverted your objective'  Description, OBJECTIVESET.employeeid TranID,
                                                    '/work-flow/submitted-objective' Link FROM OBJECTIVESET
                                                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
                                                    WHERE OBJECTIVESET.employeeid = %n and (OBLMComplete=3 AND OBEmpComplete=0)
                                                Union
                                                        SELECT NULL as SentTime, E.Name NAME, E.NAME +'['+ 
                                                    E.EMPLOYEENO +'] has submitted his objective' Description,
                                                    E.EmployeeID TranID,'/work-flow/team-submitted-objective' Link FROM OBJECTIVESET OS,
                                                    Employee E WHERE OS.EMPLOYEEID IN (
                                                        Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
                                                            Select OrganogramID From Organogram Where ParentID=(
                                                                Select NodeID from OrganEmployee Where EmployeeID=%n)))
                                                    AND OS.EmployeeID=E.EmployeeID and (OS.OBLMComplete=2 AND OS.OBEmpComplete=1)
                                                Union
                                                        SELECT NULL as SentTime, E.Name NAME,'Your line manager has assessed your mid-year appraisal. 
                                                    Please confirm if mid-year discussion has taken place.' Description, OBJECTIVESET.employeeid TranID,
                                                    '/pms/mid-year-review' Link FROM OBJECTIVESET
                                                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
                                                    WHERE OBJECTIVESET.employeeid = %n and (MYLMCOMPLETE=5 AND MYEMPCOMPLETE=5 AND (MYEMPAGREED=0 OR MYEMPAGREED IS null))
                                                Union
                                                        SELECT NULL as SentTime, E.Name NAME, 'Your line manager has reverted your mid year assessment'  
                                                    Description, OBJECTIVESET.employeeid TranID,
                                                    '/pms/mid-year-review' Link FROM OBJECTIVESET
                                                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
                                                    WHERE OBJECTIVESET.employeeid = %n and (MYLMCOMPLETE=3 AND MYEMPCOMPLETE=0)
                                                Union
                                                        SELECT NULL as SentTime, E.Name NAME, 'Your line manager has drafted your mid year assessment'  Description, 
                                                    OBJECTIVESET.employeeid TranID,
                                                    '/pms/mid-year-review' Link FROM OBJECTIVESET
                                                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
                                                    WHERE OBJECTIVESET.employeeid = %n and (MYLMCOMPLETE=6 AND MYEMPCOMPLETE=5)
                                                Union
                                                        SELECT  SentTime,Name, Description, TranID,Link  FROM (                          
                                                            SELECT NULL as SentTime,'' Name, 'You have ' + cast ( count(*) as VarChar) + ' mid year approval pending' Description,
                                                    0 TranID,'/pms/team-mid-year-review' Link ,count(*) count
                                                    ,(select datepart (dw,getdate()) AS Day) AS c
                                                    FROM OBJECTIVESET OS,
                                                    Employee E WHERE OS.EMPLOYEEID IN (
                                                        Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
                                                            Select OrganogramID From Organogram Where ParentID=(
                                                                Select NodeID from OrganEmployee Where EmployeeID=%n)))
                                                    AND OS.EmployeeID=E.EmployeeID and (OS.MYEMPCOMPLETE=5 AND OS.MYLMCOMPLETE=0) ) T WHERE count>0
                                                    ", empID, empID, empID, empID, empID, empID, empID, empID);

                tempdataset = tc.ExecuteDataSet(query4);
                tempdataset.Tables[0].TableName = "PendingJob";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());

                string query5 = SQLParser.MakeSQL(
                    @"SELECT w.DESCRIPTION NotificationType, w.SENTTIME NotificationDate FROM  WFMOVEMENTTASK w  
                                                WHERE  w.EMPLOYEEID=%n AND w.Status=%b AND w.TasksType=%n", empID,
                    false, EnumWFNotifyType.SysNotification);

                tempdataset = tc.ExecuteDataSet(query5);
                tempdataset.Tables[0].TableName = "PendingNotifications";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return rootDataset;
        }

        internal static bool IsCCApplicable(TransactionContext tc, int empid, string companyCode, string salesEmail)
        {
            object obj = null;
            string sql = SQLParser.MakeSQL(@"
            WITH cte AS (
                SELECT EMPLOYEEID
                FROM EMPLOYEE 
                WHERE LINEMANAGERID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EMAILADDRESS = %s)
                UNION ALL
                SELECT e.EMPLOYEEID
                FROM EMPLOYEE e
                INNER JOIN cte c
                ON e.LINEMANAGERID = c.EMPLOYEEID
            ),
            cte2 AS (
	            SELECT CAST(EMPLOYEEID AS VARCHAR(10)) AS STR FROM cte WHERE EMPLOYEEID = %n
	            UNION ALL
	            SELECT CompanyCode AS STR FROM SYSTEMINFORMATION WHERE CompanyCode = %s
            )
            SELECT COUNT(*) FROM cte2",
            salesEmail, empid, companyCode);

            obj = tc.ExecuteScalar(sql);

            return (obj != null && Convert.ToInt32(obj) > 1);
        }

        internal static DataSet GetBasic(TransactionContext tc, string sEmpIDs)
        {
            string sql = SQLParser.MakeSQL(@"SELECT e.employeeid, e.Name,a.EmployeeID approverID,  a.Name approverName
                                            FROM EMPLOYEE e 
                                            LEFT JOIN OrganEmployee oe ON oe.EmployeeID = e.EMPLOYEEID
                                            LEFT JOIN Organogram o ON oe.NodeID = o.OrganogramID
                                            LEFT JOIN OrganEmployee oep ON oep.NodeID = o.ParentID
                                            LEFT JOIN EMPLOYEE a ON a.EMPLOYEEID = oep.EmployeeID
                                            WHERE e.EMPLOYEEID IN (%q)", sEmpIDs);
            return tc.ExecuteDataSet(sql);
        }

        public static void UpdateLM(TransactionContext tc, int employeeID, int linemanagerId, int? SecondLineManagerId)
        {
            tc.ExecuteNonQuery("UPDATE Employee SET linemanagerId= %n , SecondLineManagerID=%n WHERE EMPLOYEEID=%n",
                              DataReader.GetNullValue(linemanagerId, 0), SecondLineManagerId, employeeID);
        }
        internal static DataSet GetEmployeeGenderRatio(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT CASE WHEN gender=1 THEN 'Male' WHEN gender=2 THEN 'Female' ELSE" +
                                               " 'Other' END Gender, count(1) Count FROM EMPLOYEE GROUP BY GENDER");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }

        internal static DataSet GetEmployeeSummaryData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("select * from VW_EmployeeSummaryData;");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeSummaryDataByDepartment(TransactionContext tc, Employee emp)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("select * from VW_EmployeeSummaryData where department = (select description from department where departmentid = %n)", emp.DepartmentID);
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetEmployeeAttritionData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = SQLParser.MakeSQL("SELECT employeeId, joiningDate, endOfContractDate FROM EMPLOYEE;");
                ds = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }
        internal static DataSet GetDashBoardGraphData(TransactionContext tc)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dt3 = new DataTable();
            DataTable dt4 = new DataTable();
            try
            {
                string sql = SQLParser.MakeSQL("select count(*) HeadCount from employee;");
                dt = tc.ExecuteDataTable(sql);

                string sql2 = SQLParser.MakeSQL("select religion.NAME Religion, count(religion.NAME) Count" +
                                                " from employee emp LEFT JOIN RELIGION religion " +
                                                "ON emp.RELIGIONID = RELIGION.RELIGIONID group by religion.NAME");
                dt2 = tc.ExecuteDataTable(sql2);

                string sql3 = SQLParser.MakeSQL(@"select (
	                                CASE WHEN BLOODGROUP = 1 THEN 'A+'
	                                WHEN BLOODGROUP = 2 THEN 'A-'
	                                WHEN BLOODGROUP = 3 THEN 'B+'
	                                WHEN BLOODGROUP = 4 THEN 'B-'
	                                WHEN BLOODGROUP = 5 THEN 'O+'
	                                WHEN BLOODGROUP = 6 THEN 'O-'
	                                WHEN BLOODGROUP = 7 THEN 'AB+'
	                                WHEN BLOODGROUP = 8 THEN 'AB-'
	                                ELSE ''
	                                END
                                ) 'BloodGroup', count(BLOODGROUP) Count from employee  group by BLOODGROUP");
                dt3 = tc.ExecuteDataTable(sql3);
                string sql4 = SQLParser.MakeSQL(@"select Y.RangeName, count(1) CountAge
                                                    from
                                                    (
	                                                    select
	                                                    (
		                                                    case
		                                                    when X.age >= 20 and X.age <= 30 then '20 - 30'
		                                                    when X.age >= 31 and X.age <= 40 then '31 - 40'
		                                                    when X.age >= 41 and X.age <= 50 then '41 - 50'
		                                                    when X.age >= 51 then '50+'
		                                                    else 'Others'
		                                                    end
	                                                    ) RangeName
	                                                    from
	                                                    (select DATEDIFF(MONTH, e.birthdate, GETDATE())/12 age from employee e) X
                                                    ) Y
                                                    group by Y.RangeName");
                dt4 = tc.ExecuteDataTable(sql4);

                ds.Tables.Add(dt);
                ds.Tables.Add(dt2);
                ds.Tables.Add(dt3);
                ds.Tables.Add(dt4);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;
        }

        internal static DataTable GetOtEligibleEmployees(TransactionContext tc, int payrolltypeid)
        {
            return tc.ExecuteDataTable(
                "SELECT e.employeeId, e.employeeNo, e.name FROM employee e WHERE e.status= %n and e.ISELIGIBLEOT = 1 and e.payrolltypeid=%n"
                , EnumEmployeeStatus.Live, payrolltypeid);
        }

        internal static DataSet GetEstimatedConfirmationReport(TransactionContext tc)
        {
            return tc.ExecuteDataSet("SELECT EmployeeId, EmployeeNo, Name, JoiningDate, CAST (DateDIFF(Month, JoiningDate, GetDate()) AS VARCHAR) + ' Months' ServiceLength, DateAdd(month, 6, joiningDate) EstimatedConfirmationDate FROM employee WHERE DATEOFCONFIRMATION IS NULL");
        }
        internal static DataSet GetEstimatedRetirementReport(TransactionContext tc)
        {
            return tc.ExecuteDataSet("SELECT EmployeeId, EmployeeNo, Name, JoiningDate, CAST (DateDIFF(Month, JoiningDate, GetDate())/12 AS VARCHAR) + ' Years' ServiceLength, DateAdd(year, 59, BIRTHDATE) EstimatedRetirementDate FROM employee WHERE DATEOFCONFIRMATION IS NOT NULL");
        }
        internal static DataSet GetSelfServiceBasicData(TransactionContext tc, int employeeId)
        {
            string sql = SQLParser.MakeSQL(@"SELECT TOP 1  e.EmployeeId, e.EmployeeNo, e.FirstName, e.LastName, e.name AS 'FullName', 
            e.ProfileComplitionPercent,
        e.ExtraField1 AS 'EmployeeDName', e.JoiningDate, e.dateofconfirmation ConfirmationDate,
        e.BirthDate, e.InsuranceId, e.InclusionDate,
        (SELECT name FROM DESIGNATION WHERE DESIGNATIONID = e.designationId) AS 'Designation',
        (SELECT DESCRIPTION FROM Department WHERE departmentId = e.departmentId) AS 'Department',
        (SELECT DESCRIPTION FROM Location WHERE locationId = e.locationId) AS 'Location',
         STUFF((SELECT ',' + DESCRIPTION FROM Crg WHERE crgId IN (SELECT costcenterId from EMPCOSTCENTER where employeeId = e.EMPLOYEEID) FOR XML PATH('')), 1, 1, '') as CostCenter,
        (SELECT DESCRIPTION FROM GRADES WHERE GRADEID = e.gradeId) AS 'Grade',e.GradeId,e.BasicSalary,e.GrossSalary,
        (
            CASE WHEN e.MARITALSTATUSID = 1 THEN 'Married'
            WHEN e.MARITALSTATUSID = 2 THEN 'Unmarried'
            WHEN e.MARITALSTATUSID = 3 THEN 'Divorced'
            WHEN e.MARITALSTATUSID = 4 THEN 'Widow'
            ELSE ''
            END
        ) 'MaritalStatus',
        (
            CASE WHEN e.gender = 1 THEN 'Male'
            WHEN e.gender = 2 THEN 'Female'
            WHEN e.gender = 3 THEN 'Other'
            ELSE ''
            END
        ) 'Gender',
        (
            CASE WHEN e.BLOODGROUP = 1 THEN 'A+'
            WHEN e.BLOODGROUP = 2 THEN 'A-'
            WHEN e.BLOODGROUP = 3 THEN 'B+'
            WHEN e.BLOODGROUP = 4 THEN 'B-'
            WHEN e.BLOODGROUP = 5 THEN 'O+'
            WHEN e.BLOODGROUP = 6 THEN 'O-'
            WHEN e.BLOODGROUP = 7 THEN 'AB+'
            WHEN e.BLOODGROUP = 8 THEN 'AB-'
            ELSE ''
            END
        ) 'BloodGroup',
        (
            CASE WHEN e.STATUS = 1 THEN 'Live'
            WHEN e.STATUS = 2 THEN 'Discontinued'
            WHEN e.STATUS = 3 THEN 'Secondy'
            WHEN e.STATUS = 4 THEN 'Suspend'
            WHEN e.STATUS = 5 THEN 'Withheld'
            WHEN e.STATUS = 6 THEN 'Waiting for join'
            WHEN e.STATUS = 7 THEN 'Did not join'
            WHEN e.STATUS = 8 THEN 'IA'
            WHEN e.STATUS = 8 THEN 'Regardless'
            ELSE ''
            END
        ) 'Status',

        (SELECT description FROM CATEGORY c WHERE c.CATEGORYID = e.categoryid ) AS 'Category',
        (SELECT employeeNo+'-'+name FROM EMPLOYEE WHERE employeeid = e.lineManagerId) AS 'LineManager',
        e.emailAddress, e.mobileNo,
        (SELECT name FROM RELIGION WHERE RELIGIONID = e.religionId) AS 'Religion',
        (SELECT description FROM NATIONALITY WHERE NATIONALITYID = e.nationalityId) AS Nationality,
        e.geid AS 'EmpCode', e.NATIONALID AS 'NID', e.TINNO AS 'TIN', e.DRIVINGLICENCENO AS 'DrivingLicenceNo', e.BirthPlace AS 'BirthPlace',
        e.passportNo AS 'PassportNo', e.PASSPORTISSUEDATE AS 'PassportIssueDate', e.PASSPORTEXPDATE AS 'PassportExpireDate',
        (
            CASE WHEN e.extraField2 = 1 THEN 'Dhaka'
            WHEN e.extraField2 = 2 THEN 'Other'
            ELSE ''
            END
        ) 'BaseStation',
        e.insuranceId AS 'InsuranceId', e.fatherName AS 'FatherName',
        (SELECT description FROM OCCUPATION WHERE OCCUPATIONID = e.fatherOccupationId) AS 'FatherOccupation',
         e.motherName AS 'MotherName',
         (SELECT description FROM OCCUPATION WHERE OCCUPATIONID = e.motherOccupationId) AS 'MotherOccupation',
         e.extraField3 AS 'TypeName', e.vendorCode AS 'VendorCode', e.extrafield4 AS 'MinValue', e.extrafield5 AS 'MaxValue',
        bah.AccountNo, es.NAME SpouseName, so.DESCRIPTION SpouseOccupation
         FROM EMPLOYEE e
        LEFT JOIN BANKACCOUNTHISTORY bah ON bah.EMPLOYEEID = e.EMPLOYEEID
        LEFT JOIN EMPSPOUSE es ON es.EMPLOYEEID = e.EMPLOYEEID
        LEFT JOIN OCCUPATION so ON so.OCCUPATIONID = es.OCCUPATIONID
         Where e.EmployeeID = %n", employeeId);
            return tc.ExecuteDataSet(sql);
        }
        internal static DataSet GetSelfServiceContactData(TransactionContext tc, int employeeId)
        {
            string sql = SQLParser.MakeSQL(@" SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEENO AS EmployeeNo, e.Name, c.PARMANENTADDRESS PermanentAddress, e.emailAddress, e.MobileNo, c.fax,
	                            permD.name PermanentDistrict, permT.name PermanentThana, presD.name PresentDistrict, presT.name PresentThana,
	                            c.PARMANENTTELEPHONE AS 'PermanentTelephone',
	                            c.PARMANENTMOBILE AS 'PermanentMobile',
	                            c.PRESENTADDRESS AS 'PresentAddress',
	                            c.PresentTELEPHONE AS 'PresentTelephone',
	                            c.PRESENTMOBILE AS 'PresentMobile',
	                            c.EMERGENCYCONTACTADDRESS AS 'EmergencyContactAddress',
	                            c.EMERGENCYCONTACTPERSON AS 'EmergencyContactPerson',
	                            c.EMERGENCYTELEPHONE AS 'EmergencyTelephone',
	                            c.emergencyMobile,PR.DESCRIPTION AS 'RelationDescription'
		                            FROM EMPCONTACT c 
		                            INNER JOIN EMPLOYEE e ON e.EMPLOYEEID = c.EMPLOYEEID
		                            Inner Join District permD on permD.DISTRICTID = c.PARMANENTDISTRICTID
		                            Left Join District presD on presD.DISTRICTID = c.PresentDISTRICTID
		                            Left Join Thana presT on presT.ThanaId = c.PresentTHANAID
		                            Inner Join Thana permT on permT.ThanaId = c.PARMANENTTHANAID
                                    Left Join PERSONRELATION PR on c.CPRELATIONID = PR.RELATIONID
	                            where c.EMPLOYEEID = %n", employeeId);
            return tc.ExecuteDataSet(sql);
        }
        internal static void CreateEmpBasicView(TransactionContext tc, string machineName)
        {

            string sql = SQLParser.MakeSQL(@"CREATE OR REPLACE VIEW VW_EMPLOYEEBASIC_%q AS Select E.EmployeeId,sg.get(E.EMPLOYEENO) EMPLOYEENO,sg.get(E.NAME) Name,E.BASICSALARY,E.GrossSalary,E.TAXAMOUNT, E.Fathername, occf.Description AS FatherOccupation, occm.Description AS MotherOccupation, E.MotherName, E.PhotoPath AS Photo, 
																			( case E.GENDER
																			 When 1 Then 'Male'
																			 When 2 Then 'Female'
																			 When 3 Then 'Both'
																			 Else 'None'
																			 End)As Gender,
										E.BIRTHDATE, E.BIRTHPlace, E.TINNO,E.JOININGDATE,E.LASTPROMOTIONDATE,sg.get(E.EMAILADDRESS) EMAILADDRESS, Ca.DESCRIPTION as Category,L.DESCRIPTION as Location,
																			(Case E.STATUS
																			When 1 Then 'Active'
																			When 2 Then 'InActive'
																			Else 'Regardless'
																			End) As Status,

										(case DEp.tire
										when 4 then dept1.DESCRIPTION
										when 3 then dept2.DESCRIPTION
										when 2 then dept3.DESCRIPTION
										when 1 then DEp.DESCRIPTION
										Else ''
										End) As Division, 

										(case DEp.tire
										when  4 THEN  dept1.DEPARTMENTID
										when  3 then  dept2.DEPARTMENTID
										when  2 THEN  dept3.DEPARTMENTID
										when  1 then  DEp.DEPARTMENTID
										End) As DivisionID,

										case DEp.tire
										when 4 THEN  dept2.DESCRIPTION
										when 3 then dept3.DESCRIPTION
										when 2 then DEp.DESCRIPTION
										Else ''
										End As Department,

										(case DEp.tire
										when  4 then dept2.DepartmentID
										when  3 then dept3.DepartmentID
										when  2 then DEp.DepartmentID
										End) As DepartmentID,

										(case DEp.tire
										when  4 then dept3.DESCRIPTION
										when  3 then DEp.DESCRIPTION
										when  2 then ''
										Else ''
										End) As Unit,
			
										(case DEp.tire
										when  4 then dept3.DepartmentID
										when  3 then DEp.DepartmentID
										End) As UnitID,
			
						
										(case DEp.tire
										when  4 then DEp.DESCRIPTION
										when  3 then ''
										when  2 then ''
										Else ''
										End) As SubUnit,
			
										(case DEp.tire
										when  4 then DEp.DepartmentID
										End) As SubUnitID,

										sg.get(Deg.NAME) as DESIGNATION,sg.get(Gr.DESCRIPTION) as GRADE, R.Name AS RELIGION,
																			(case E.MARITALSTATUSID
																			When 1 Then 'Married'
																			When 2 Then 'Unmarried'
																			When 3 Then 'Divorced'
																			When 4 Then 'Widow'
																			Else 'None'
																			End) As MaritalStatus ,
										sg.get(E.MOBILENO) MOBILENO,E.VendorCode,E.DATEOFCONFIRMATION,E.NATIONALID,E.PassPortNo,E.PhotoPath,E.bloodgroup, 

										CASE WHEN orgel.EMPLOYEEID IS NULL THEN authEmp.EmployeeID ELSE orgel.EMPLOYEEID END LineManagerID,
										CASE WHEN orgel.EMPLOYEEID IS NULL THEN sg.get(authEmp.EmployeeNo) ELSE sg.get(em.EmployeeNo) END LineManagerNo,
										CASE WHEN orgel.EMPLOYEEID IS NULL THEN sg.get(authEmp.Name) ELSE sg.get(em.Name) END LineManagerName,
										CASE WHEN orgel.EMPLOYEEID IS NULL THEN sg.get(authLmDeg.Name) ELSE sg.get(lmDeg.Name) END LineManagerDes,
										CASE WHEN orgel.EMPLOYEEID IS NULL THEN sg.get(authEmp.EMAILADDRESS) ELSE sg.get(em.EMAILADDRESS) END LMEMAILADDRESS,

										(case lmDEp.tire
										when 4 then lmdept1.DESCRIPTION
										when 3 then lmdept2.DESCRIPTION
										when 2 then lmdept3.DESCRIPTION
										when 1 then lmDEp.DESCRIPTION
										Else ''
										End) As lmDivision, 

										(case lmDEp.tire
										when  4 THEN  lmdept1.DEPARTMENTID
										when  3 then  lmdept2.DEPARTMENTID
										when  2 THEN  lmdept3.DEPARTMENTID
										when  1 then  lmDEp.DEPARTMENTID
										End) As lmDivisionID,

										case lmDEp.tire
										when 4 THEN  lmdept2.DESCRIPTION
										when 3 then lmdept3.DESCRIPTION
										when 2 then lmDEp.DESCRIPTION
										Else ''
										End As lmDepartment,

										(case lmDEp.tire
										when  4 then lmdept2.DepartmentID
										when  3 then lmdept3.DepartmentID
										when  2 then lmDEp.DepartmentID
										End) As lmDepartmentID,

										(case lmDEp.tire
										when  4 then lmdept3.DESCRIPTION
										when  3 then lmDEp.DESCRIPTION
										when  2 then ''
										Else ''
										End) As lmUnit,
			
										(case lmDEp.tire
										when  4 then lmdept3.DepartmentID
										when  3 then lmDEp.DepartmentID
										End) As lmUnitID,
			
						
										(case lmDEp.tire
										when  4 then lmDEp.DESCRIPTION
										when  3 then ''
										when  2 then ''
										Else ''
										End) As lmSubUnit,
			
										(case lmDEp.tire
										when  4 then lmDEp.DepartmentID
										End) As lmSubUnitID,

																		   (case E.IsConfirmed
																			When 0 Then 'Probetionary'
																			When 1 Then 'Confirmed'  
																			Else 'Regardless'
																			End)As EmpStatus,
										cg.Code AS costcenter, nt.DESCRIPTION AS NATIONALITY,BK.NAME   Bank,BR.NAME   Branch,BKOUT.NAME   OUTPayBank,BROUT.NAME OUTPayBranch, E.ACCOUNTNO,E.OutPayAccountNo
														   
										from Employee E
										Left outer join CATEGORY Ca on E.CATEGORYID = Ca.CATEGORYID
										Left JOIN DEPARTMENT DEp ON DEp.DEPARTMENTID = E.DEPARTMENTID
										LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = DEp.PARENTID
										LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
										LEFT JOIN DEPARTMENT dept1 ON dept1.DEPARTMENTID = dept2.PARENTID
										Left outer join DESIGNATION Deg on E.DESIGNATIONID = Deg.DESIGNATIONID
										Left outer join GRADES Gr on E.GRADEID = Gr.GRADEID	
										Left Outer Join LOCATION L on E.LOCATIONID = L.LOCATIONID
										Left Outer Join RELIGION R on E.RELIGIONID = R.RELIGIONID
										LEFT OUTER JOIN EMPCOSTCENTER ec ON ec.EMPLOYEEID = e.EMPLOYEEID AND ec.CURRENTCC = 1
										LEFT OUTER JOIN CRG cg ON cg.CRGID = ec.COSTCENTERID
										LEFT OUTER JOIN NATIONALITY nt ON nt.NATIONALITYID = e.NATIONALITYID
										LEFT OUTER JOIN OCCUPATION occf ON occf.OCCUPATIONID = e.FATHEROCCUPATIONID
										LEFT OUTER JOIN OCCUPATION occm ON occm.OCCUPATIONID = e.MOTHEROCCUPATIONID

										LEFT OUTER JOIN ORGANEMPLOYEE orge ON orge.EMPLOYEEID = e.EMPLOYEEID
										LEFT OUTER JOIN ORGANOGRAM org ON org.ORGANOGRAMID = orge.NODEID
										LEFT OUTER JOIN ORGANEMPLOYEE orgel ON orgel.NODEID = org.PARENTID
										LEFT OUTER JOIN EMPLOYEE em ON em.EMPLOYEEID = orgel.EMPLOYEEID
										LEFT OUTER JOIN ORGANAUTHORITY oAuth ON oAuth.NODEID = org.PARENTID
										LEFT OUTER JOIN ORGANEMPLOYEE oAuthOrgEmp ON oAuthOrgEmp.NODEID = oAuth.CHILDNODEID
										LEFT OUTER JOIN EMPLOYEE authEmp ON authEmp.EMPLOYEEID = oAuthOrgEmp.EMPLOYEEID

										Left outer join DESIGNATION lmDeg on em.DESIGNATIONID = lmDeg.DESIGNATIONID
										Left outer join DESIGNATION authLmDeg on authEmp.DESIGNATIONID = authLmDeg.DESIGNATIONID
										Left JOIN DEPARTMENT lmDEp ON lmDEp.DEPARTMENTID = em.DEPARTMENTID
										LEFT JOIN DEPARTMENT lmdept3 ON lmdept3.DEPARTMENTID = lmDEp.PARENTID
										LEFT JOIN DEPARTMENT lmdept2 ON lmdept2.DEPARTMENTID = lmdept3.PARENTID
										LEFT JOIN DEPARTMENT lmdept1 ON lmdept1.DEPARTMENTID = lmdept2.PARENTID
										LEFT OUTER JOIN Branches   BR ON E.BRANCHID=BR.BRANCHID
										LEFT OUTER JOIN BANKS   BK ON BK.BANKID=BR.BANKID
										LEFT OUTER JOIN Branches   BROUT ON E.OUTPayBRANCHID=BROUT.BRANCHID
										LEFT OUTER JOIN BANKS   BKOUT ON BKOUT.BANKID=BROUT.BANKID", machineName);

            tc.ExecuteNonQuery(sql);
        }

        internal static void DropEmpBasicView(TransactionContext tc, string machineName)
        {
            string sql = SQLParser.MakeSQL(@"declare
											   c int;
											begin
											   select count(*) into c from user_views where view_name = upper('VW_EMPLOYEEBASIC_%q');
											   if c = 1 then
												  execute immediate 'Drop VIEW VW_EMPLOYEEBASIC_%q';
											   end if;
											end;", machineName.ToUpper(), machineName.ToUpper());

            tc.ExecuteNonQuery(sql);
        }
        internal static IDataReader GetByName(TransactionContext tc, string EmpName)
        {
            return tc.ExecuteReader("SELECT * FROM Employee WHERE Name=%s ", EmpName);
        }
        internal static IDataReader GetFirstLMTeamMembers(TransactionContext tc, int employeeID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE LineManagerID = %n order by EmployeeNo", employeeID);
            return tc.ExecuteReader(sql);
        }

        internal static DataTable GetsubordinateforMobile(TransactionContext tc, int empId)
        {
            string sql = SQLParser.MakeSQL(@"select EmployeeID employeeid, EmployeeNo employeeno, name name from employee where linemanagerid = %n", empId);
            return tc.ExecuteDataTable(sql);
        }

        internal static DataTable GetSubordinatesByLineManagerForMobileWithNoLock(TransactionContext tc, int empId)
        {
            string sql = SQLParser.MakeSQL(@"select EmployeeID employeeid, EmployeeNo employeeno, name name from employee With(NoLock) where linemanagerid = %n", empId);
            return tc.ExecuteDataTable(sql);
        }

        internal static IDataReader GetSubordinatesByLineManager(TransactionContext tc, int employeeID)
        {
            return tc.ExecuteReader("Select * From Employee Where LINEMANAGERID = %n", employeeID);
        }

        internal static DataSet GetLandingPageData(TransactionContext tc, int isEmployee, int isLeave, int isAttendance, int payrollTypeId)
        {
            List<SqlParameter> p = new List<SqlParameter>
            {
                SqlHelperExtension.CreateInParam("@ModEmployee", SqlDbType.Int, isEmployee),
                SqlHelperExtension.CreateInParam("@ModLeave", SqlDbType.Int, isLeave),
                SqlHelperExtension.CreateInParam("@ModAttendance", SqlDbType.Int, isAttendance),
                SqlHelperExtension.CreateInParam("@PayrollTypeID", SqlDbType.Int, payrollTypeId)
            };
            return tc.ExecuteDataSet(CommandType.StoredProcedure, "sp_DashboardInformation", p.ToArray());
        }
        //Mobile Profile
        internal static DataSet GetMobileProfile(TransactionContext tc, int employeeID)
        {
            string query = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID EmployeeId, e.EMPLOYEENO EmployeeNo, e.NAME Name, e.JOININGDATE JoiningDate, e.IsFixedLocation,
                                            dp.DESCRIPTION Department, ds.NAME Designation, l.DESCRIPTION Location, l.LATITUDE Latitude, l.LONGITUDE Longitude
                                            FROM EMPLOYEE e With(NoLock)
                                            LEFT JOIN DEPARTMENT dp With(NoLock) ON e.DEPARTMENTID = dp.DEPARTMENTID
                                            LEFT JOIN DESIGNATION ds With(NoLock) ON e.DESIGNATIONID = ds.DESIGNATIONID
                                            LEFT JOIN LOCATION l With(NoLock) ON l.LOCATIONID = e.LOCATIONID
                                            WHERE e.EMPLOYEEID = %n", employeeID);
            return tc.ExecuteDataSet(query);
        }
        internal static DataSet GetMobileProfileNew(TransactionContext tc, int employeeID)
        {
            string query = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID EmployeeId, e.EMPLOYEENO EmployeeNo, e.NAME Name,e.emailaddress EmailAddress, e.JOININGDATE JoiningDate, e.IsFixedLocation,
                                            dp.DESCRIPTION Department, ds.NAME Designation, l.DESCRIPTION Location, l.LATITUDE Latitude, l.LONGITUDE Longitude
                                            FROM EMPLOYEE e
                                            LEFT JOIN DEPARTMENT dp ON e.DEPARTMENTID = dp.DEPARTMENTID
                                            LEFT JOIN DESIGNATION ds ON e.DESIGNATIONID = ds.DESIGNATIONID
                                            LEFT JOIN LOCATION l ON l.LOCATIONID = e.LOCATIONID
                                            WHERE e.EMPLOYEEID = %n", employeeID);
            return tc.ExecuteDataSet(query);
        }


        //Mobile Profile Dashboard
        internal static DataSet GetMobileDashboardInfo(TransactionContext tc, int EmployeeID, DateTime FromDate, DateTime ToDate)
        {
            List<SqlParameter> p = new List<SqlParameter>
            {
                SqlHelperExtension.CreateInParam("@EmployeeId", SqlDbType.Int, EmployeeID),
                SqlHelperExtension.CreateInParam("@FromDate", SqlDbType.DateTime, FromDate),
                SqlHelperExtension.CreateInParam("@ToDate", SqlDbType.DateTime, ToDate)
            };
            return tc.ExecuteDataSet(CommandType.StoredProcedure, "sp_MobileDashboardInformation", p.ToArray());
        }
        internal static DataSet GetHolidayInfoForDashboard(TransactionContext tc)
        {
            string query = SQLParser.MakeSQL(@"SELECT a.ATTNNATIONALHOLIDAYID, a.DESCRIPTION HolidayType, FORMAT (a.FROMDATE, 'dd MMM yyyy') FROMDATE, FORMAT (a.TODATE , 'dd MMM yyyy')  TODATE 
                                                    FROM AttnNationalHoliday a WHERE a.FromDate between %d and %d Order By a.FromDate desc", new DateTime(DateTime.Today.Date.Year, 1, 1), new DateTime(DateTime.Today.Date.Year, 12, 31));
            return tc.ExecuteDataSet(query);
        }

        internal static DataSet GetBankAdviceReport(TransactionContext tc, string sEmpIDs, string leaveYear)
        {
            DataSet oEmpDetails = new DataSet();
            string sql = string.Empty;
            try
            {
                sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID,e.EMPLOYEENO, e.[NAME] NAME, e.ACCOUNTNO, e2.AMOUNT ENCASHAMOUNT
                                            FROM EMPLOYEE e, LeaveEncashment e2, LEAVEYEAR l
                                            WHERE e.EMPLOYEEID = e2.EMPLOYEEID AND e.EMPLOYEEID IN(%q) AND l.LEAVEYEARID = e2.LEAVEYEARID 
                                            AND e2.AMOUNT!=0 AND l.[NAME] = '%q'
                                            GROUP BY e.EMPLOYEEID, e.[NAME], e.ACCOUNTNO, e2.AMOUNT,e.EMPLOYEENO
                                            ORDER BY e.EMPLOYEEID", sEmpIDs, leaveYear);
                oEmpDetails = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oEmpDetails;
        }

        internal static DataSet GetLeaveCashAdvice(TransactionContext tc, string sEmpIDs, string leaveYear)
        {
            DataSet oEmpDetails = new DataSet();
            string sql = string.Empty;
            try
            {
                sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEEID,e.EMPLOYEENO, e.NAME,e.GROSSSALARY, eLS.AMOUNT ENCASHAMOUNT
                                        FROM EMPLOYEE e, LeaveEncashment eLS, LEAVEYEAR l
                                        WHERE e.EMPLOYEEID = eLS.EMPLOYEEID AND e.EMPLOYEEID IN(%q) AND l.LEAVEYEARID = eLS.LEAVEYEARID 
                                        AND eLS.AMOUNT!=0 AND l.[NAME] = '%q' And e.PAYMENTMODE=%n
                                        GROUP BY e.EMPLOYEEID, e.[NAME], eLS.AMOUNT,e.GROSSSALARY,e.EMPLOYEENO
                                        ORDER BY e.EMPLOYEEID", sEmpIDs, leaveYear, (int)EnumPaymentMode.CashPayment);
                oEmpDetails = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oEmpDetails;
        }
        internal static DataSet GetEncashPayslip(TransactionContext tc, string sEmpIDs, string leaveYear)
        {
            DataSet oEmpDetails = new DataSet();
            string sql = string.Empty;
            try
            {
                sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO, e.[NAME] NAME,e2.grosssalary, '' PAYMENTMODE,Dept.DESCRIPTION as Department,Degs.NAME as Designation, e.BasicSalary, e2.AMOUNT ENCASHAMOUNT,e2.EncashmentDays TaxAmount
                                        FROM EMPLOYEE e, LeaveEncashment e2, LEAVEYEAR l,DEPARTMENT as Dept,DESIGNATION as Degs
                                        WHERE e.EMPLOYEEID = e2.EMPLOYEEID AND e.EMPLOYEEID IN(%q) AND l.LEAVEYEARID = e2.LEAVEYEARID 
                                        AND e2.AMOUNT!=0 AND l.[NAME] = '%q'
                                        AND e.DEPARTMENTID =Dept.DEPARTMENTID AND e.DESIGNATIONID =Degs.DESIGNATIONID
                                        ORDER BY e.EMPLOYEENO", sEmpIDs, leaveYear);
                oEmpDetails = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oEmpDetails;
        }

        internal static DataTable GetRegularizableEmail(TransactionContext tc, string regularizableEmployeeIds)
        {
            string sql = SQLParser.MakeSQL(@"
            SELECT emp.EMPLOYEEID AS EMPEMPLOYEEID, emp.EMPLOYEENO AS EMPEMPLOYEENO, emp.NAME AS EMPNAME, emp.EMAILADDRESS AS EMPEMAILADDRESS, lm.EMPLOYEEID AS LMEMPLOYEEID, lm.EMPLOYEENO AS LMEMPLOYEENO, lm.NAME AS LMNAME, lm.EMAILADDRESS AS LMEMAILADDRESS
            FROM EMPLOYEE emp
            LEFT JOIN EMPLOYEE lm ON lm.EMPLOYEEID = emp.LINEMANAGERID
            WHERE emp.EMPLOYEEID IN (%q) AND 
            emp.EMAILADDRESS IS NOT NULL AND emp.EMAILADDRESS <> ''
            AND lm.EMAILADDRESS IS NOT NULL AND lm.EMAILADDRESS <> ''",
            regularizableEmployeeIds);
            return tc.ExecuteDataTable(sql);
        }

        internal static DataSet GetEmpDigitalServiceBook(TransactionContext tc, int empID)
        {
            tc.CommandTimeOut = 60;
            string sSQL = SQLParser.MakeSQL(@"SELECT EMPLOYEEID, EMPLOYEENO, NAME, FATHERNAME, MOTHERNAME, SPOUSENAME, JOININGDATE, 
                                                DESIGNATION, BIRTHDATE, NIDNO, HEIGHT, BLOODGROUP, PARMANENTADDRESS, PARMANENTDISTRICT, 
                                                PARMANENTTHANA, PRESENTADDRESS, LMDESIGNATION, PHOTO, EDUCATIONLEVEL
                                                FROM dbo.VW_EMPDIGITALSERVICEBOOK 
                                                WHERE EMPLOYEEID = %n", empID);
            return tc.ExecuteDataSet(sSQL);
        }

        internal static DataTable GetCordinatorTeam(TransactionContext tc, Employee oEmp)
        {

            string sSQL = SQLParser.MakeSQL(@"DECLARE @lmid int;
                                                SET @lmid = %n
                                                SELECT E.EMPLOYEEID as EmployeeID,
                                                                                                    E.EMPLOYEENO AS EmpNo,
                                                                                                    E.NAME AS EmpName,
                                                                                                    E.GENDER AS EmpGender,
                                                                                                    COUNT(CASE WHEN E.Gender = 1 THEN 1 END) OVER () AS Male,
                                                                                                    COUNT(CASE WHEN E.Gender = 2 THEN 1 END) OVER () AS Female,
	                                                                                                E.LINEMANAGERID AS LinemanagerID,
                                                                                                    COUNT(CASE WHEN E.LinemanagerID = @lmid THEN 1 END) OVER () as EmpCount,
                                                                                                    D.DESCRIPTION AS Department,DG.NAME AS Designation,
                                                                                                    DG.NAME AS Designation,
                                                                                                    G.DESCRIPTION AS Grade,
                                                                                                    E.JOININGDATE AS EmpDOJ,
                                                                                                    L.DESCRIPTION AS EmpLoc,
                                                                                                    E.MOBILENO AS EmpMobileNo,
                                                                                                    E.EMAILADDRESS AS EmpEmail
                                                FROM EmployeeCordinator EC 
                                                JOIN Employee E ON EC.EmployeeID = E.EMPLOYEEID
                                                JOIN DEPARTMENT D ON E.DEPARTMENTID = D.DEPARTMENTID
                                                JOIN DESIGNATION DG ON E.DESIGNATIONID = DG.DESIGNATIONID
                                                JOIN GRADES G ON E.GRADEID = G.GRADEID
                                                JOIN LOCATION L ON L.LOCATIONID = E.LOCATIONID


                                                WHERE EC.CordinatorID = @lmid --AND E.status = %n
                                                group by E.EMPLOYEEID, E.EMPLOYEENO, E.NAME, E.GENDER, E.LINEMANAGERID, D.DESCRIPTION, DG.NAME, G.DESCRIPTION, E.JOININGDATE,
                                                L.DESCRIPTION,E.MOBILENO, E.EMAILADDRESS", oEmp.ID, EnumEmpStatus.Live);
            return tc.ExecuteDataTable(sSQL);
        }
        internal static DataTable GetMyTeam(TransactionContext tc, Employee oEmp)
        {

            string sSQL = SQLParser.MakeSQL(@"DECLARE @lmid int;
                                                SET @lmid = %n
                                                SELECT
                                                    E.EMPLOYEEID as EmployeeID,
                                                    E.EMPLOYEENO AS EmpNo,
                                                    E.NAME AS EmpName,
                                                    E.GENDER AS EmpGender,
                                                    COUNT(CASE WHEN E.Gender = 1 THEN 1 END) OVER () AS Male,
                                                    COUNT(CASE WHEN E.Gender = 2 THEN 1 END) OVER () AS Female,
	                                                E.LINEMANAGERID AS LinemanagerID,
                                                    COUNT(CASE WHEN E.LinemanagerID = @lmid THEN 1 END) OVER () as EmpCount,
                                                    D.DESCRIPTION AS Department,
                                                    DG.NAME AS Designation,
                                                    G.DESCRIPTION AS Grade,
                                                    E.JOININGDATE AS EmpDOJ,
                                                    L.DESCRIPTION AS EmpLoc,
                                                    E.MOBILENO AS EmpMobileNo,
                                                    E.EMAILADDRESS AS EmpEmail
	                                                --,EP.FILEDATA AS EmpPhoto
                                                FROM
                                                    EMPLOYEE E
                                                LEFT JOIN
                                                    DEPARTMENT D ON E.DEPARTMENTID = D.DEPARTMENTID
                                                LEFT JOIN
                                                    DESIGNATION DG ON E.DESIGNATIONID = DG.DESIGNATIONID
                                                LEFT JOIN
                                                    GRADES G ON E.GRADEID = G.GRADEID
                                                LEFT JOIN
                                                    LOCATION L ON L.LOCATIONID = E.LOCATIONID

                                                --LEFT JOIN(
                                                --SELECT FILEDATA,EMPID FROM EMPLOYEE EE
		                                                --JOIN EMPFILEUPLOAD EF ON EE.EMPLOYEEID = EF.EMPID WHERE EF.FILETYPE = 1 
		                                                --) EP ON E.EMPLOYEEID = EP.EMPID
                                                --WHERE E.EmployeeID IN(
		                                                --Select EmployeeID from OrganEmployee 
		                                                --Where NodeID IN(
			                                                --Select OrganogramID From Organogram 
			                                                --Where ParentID=(
				                                                --Select NodeID from OrganEmployee 
				                                                --Where EmployeeID = @lmid)))
                                                                Where E.LINEMANAGERID = @lmid
                                                                AND E.status = %n
                                                group by E.EMPLOYEEID, E.EMPLOYEENO, E.NAME, E.GENDER, E.LINEMANAGERID, D.DESCRIPTION, DG.NAME, G.DESCRIPTION, E.JOININGDATE,
                                                L.DESCRIPTION,E.MOBILENO, E.EMAILADDRESS --, EP.FILEDATA", oEmp.ID, EnumEmpStatus.Live);
            return tc.ExecuteDataTable(sSQL);
        }

        internal static DataTable GetEmployeeCount(TransactionContext tc, Employee emp)
        {
            string subSQL = "";
            string deptSQL = "";
            if (emp != null && emp.LineManagerID != null)
            {
                subSQL = SQLParser.MakeSQL(" and departmentid = %n", emp.DepartmentID);
                deptSQL = SQLParser.MakeSQL(", (select DESCRIPTION from DEPARTMENT where departmentid = %n) as department", emp.DepartmentID);
            }
            if (emp != null && emp.LineManagerID == null)
            {
                deptSQL = SQLParser.MakeSQL(", (select DESCRIPTION from DEPARTMENT where departmentid = %n) as department", emp.DepartmentID);
            }

            string sSQL = SQLParser.MakeSQL(@"DECLARE @status int;
                                            SET @status = %n
                                            select 
                                            (select COUNT(*) from EMPLOYEE where STATUS = @status %q) as TotalEmployee,
                                            (select COUNT(*) from EMPLOYEE where STATUS = @status and GENDER = 1 %q) as Male,
                                            (select COUNT(*) from EMPLOYEE where STATUS = @status and GENDER = 2 %q) as Female
                                            %q"
                                            , EnumEmployeeStatus.Live, subSQL, subSQL, subSQL, deptSQL);
            return tc.ExecuteDataTable(sSQL);
        }

        internal static DataTable GetEmpOrganogramInfo(TransactionContext tc, Employee emp)
        {
            string subSQL = "";

            if (emp != null && emp.LineManagerID != null)
            {
                subSQL = SQLParser.MakeSQL("AND e.Departmentid = %n", emp.DepartmentID);
            }

            string sSQL = SQLParser.MakeSQL(@"
                                            WITH EmployeeCTE AS (
                                                SELECT
                                                    employeeid,
                                                    designationid,
		                                            gradeid,
		                                            departmentid,
		                                            linemanagerid,
		                                            locationid,
		                                            employeeno,
		                                            name,
		                                            joiningdate,
		                                            mobileno,
		                                            emailaddress,
		                                            status
                                                FROM EMPLOYEE 
                                            )
                                            SELECT
                                                E.employeeid as employeeid,
                                                Lm.employeeid as linemanagerid,
	                                            E.employeeno as employeeno,
                                                --EP.filedata AS photo,
	                                            E.name as name,
	                                            DG.name as designation,
	                                            G.description as grade,
	                                            D.description as department,
                                                L.DESCRIPTION as location,
                                                E.JOININGDATE as doj,
                                                E.MOBILENO as mobile,
                                                E.EMAILADDRESS as email
                                            FROM EmployeeCTE E
                                            LEFT JOIN
                                                DESIGNATION DG ON E.DESIGNATIONID = DG.DESIGNATIONID
                                            LEFT JOIN
                                                GRADES G ON E.GRADEID = G.GRADEID
                                            LEFT JOIN
                                                DEPARTMENT D ON E.DEPARTMENTID = D.DEPARTMENTID
                                            LEFT JOIN
                                                LOCATION L ON L.LOCATIONID = E.LOCATIONID
                                            --LEFT JOIN(
                                                --SELECT FILEDATA,EMPID FROM EMPLOYEE EE
                                                    --JOIN EMPFILEUPLOAD EF ON EE.EMPLOYEEID = EF.EMPID WHERE EF.FILETYPE = 1 
                                                    --) EP ON E.EMPLOYEEID = EP.EMPID
                                            LEFT JOIN EMPLOYEE Lm ON e.linemanagerid = Lm.EmployeeID
                                            where e.status = %n %q
                                            order by e.linemanagerid, e.employeeid ", EnumEmployeeStatus.Live, subSQL);

            return tc.ExecuteDataTable(sSQL);
        }

        internal static DataTable getEmpInfoForGateman(TransactionContext tc, int empid)
        {
            string sql = SQLParser.MakeSQL(@"select e.EMPLOYEEID as employeeID, e.EMPLOYEENO, e.NAME, d.NAME as designation, dep.DESCRIPTION as department,
                                                l.DESCRIPTION as location from EMPLOYEE e 
                                                left join DESIGNATION d on d.DESIGNATIONID = e.DESIGNATIONID
                                                left join DEPARTMENT dep on dep.DEPARTMENTID = e.DEPARTMENTID
                                                left join LOCATION l on l.LOCATIONID = e.LOCATIONID
                                                WHERE e.EMPLOYEEID = %n", empid);
            DataTable dt = tc.ExecuteDataTable(sql);

            string sSQL = SQLParser.MakeSQL(@"select e.EMPLOYEEID as employeeID, e.EMPLOYEENO, e.NAME, d.NAME as designation, dep.DESCRIPTION as department,
                                                l.DESCRIPTION as location, ef.fileData as photo from EMPLOYEE e 
                                                left join DESIGNATION d on d.DESIGNATIONID = e.DESIGNATIONID
                                                left join DEPARTMENT dep on dep.DEPARTMENTID = e.DEPARTMENTID
                                                left join LOCATION l on l.LOCATIONID = e.LOCATIONID
				                                left join empFileupload ef on ef.empid = e.EMPLOYEEID 
				                                WHERE ef.fileType = %n and e.EMPLOYEEID = %n", enumEmpFileUploadType.photo, empid);
            DataTable dtwp = tc.ExecuteDataTable(sSQL);

            if (dtwp.Rows.Count > 0)
                return dtwp;
            else
                return dt;
        }
        internal static DataSet GetAllEmpBasicInfo(TransactionContext tc, string empIDs)
        {
            string sql = string.Empty;

            if (GlobalExtensions.IsNullOrWhiteSpace(empIDs))
            {
                sql = SQLParser.MakeSQL(@"select * from vw_EmployeeImportantInfo
										  ORDER BY EmployeeID");
            }
            else
            {
                sql = SQLParser.MakeSQL(@"select * from vw_EmployeeImportantInfo 
										  WHERE EmployeeID IN (%q)
										  ORDER BY EmployeeID", empIDs);
            }

            return tc.ExecuteDataSet(sql);
        }
        internal static DataSet GetCostCenterInfo(TransactionContext tc, string empId)
        {
            DataSet ds = new DataSet();
            string sql = string.Empty;
            try
            {
                sql = SQLParser.MakeSQL(@"SELECT EMPLOYEE.EMPLOYEEID, EmpView.DEPARTMENT AS CostCenter, dept.CODE as DepartmentCode, crg.Code as CostCenterCode
                                            FROM EMPLOYEE
                                            INNER JOIN vw_EmployeeImportantInfo as EmpView
                                            ON EMPLOYEE.EMPLOYEEID = EmpView.EMPLOYEEID
                                            INNER JOIN Department as Dept 
                                            ON EMPLOYEE.DepartmentID = Dept.DepartmentID
                                            Left JOIN  EmpCostCenter empCC
                                            ON EmpView.employeeID = empCC.employeeID
                                            LEFT join CRG
                                            on empCC.CostCenterID = CRG.CRGID
                                            where EMPLOYEE.EMPLOYEEID in (%q)
                                            ", empId);
                ds = tc.ExecuteDataSet(sql);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
        internal static DataSet GetPFLedger(TransactionContext tc, string sEmpID, DateTime FromDate, DateTime toDate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string query1 = SQLParser.MakeSQL(@"Select A.Description,A.PFAmount,B.CPFAmount from 
                                                    (Select 'Opening' Description,ISNULL(sum(TranAmount),0) PFAmount
                                                    from PFTransaction PFT where TranDate < %d
                                                    And TranType in(%n) and EmployeeID in (%q)) A
                                                    ,
                                                    (
                                                    Select 'Opening' Description, ISNULL(sum(TranAmount),0) CPFAmount 
                                                    from PFTransaction PFT where TranDate < %d
                                                    And TranType in(%n) and EmployeeID in (%q)) B
                                                    Where A.Description = B.Description",
                                                    FromDate, (int)EnumPFTranType.PFAmount, sEmpID,
                                                    FromDate, (int)EnumPFTranType.CPFAmount, sEmpID);


                //                string query1 = SQLParser.MakeSQL(@"Select 'Opening' Description,sum(TranAmount)as PFAmount,sum(TranAmount)as CPFAmount 
                //                                                    from PFTransaction PFT where TranDate < %d
                //                                                    And TranType in(1,2) and EmployeeID in (%q)",FromDate,sEmpID);

                tempdataset = tc.ExecuteDataSet(query1);

                tempdataset.Tables[0].TableName = "Opening";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());

                string query2 = SQLParser.MakeSQL(@"Select A.TranDate Description,A.PFAmount,B.CPFAmount from 
                                                    (Select TranDate,ISNULL(sum(TranAmount),0)as PFAmount
                                                    from PFTransaction PFT where TranDate  between  %d  and %d
                                                    And TranType in(%n) and EmployeeID in (%q)
                                                    group by TranDate) A
                                                    ,
                                                    (
                                                    Select TranDate, ISNULL(sum(TranAmount),0) CPFAmount 
                                                    from PFTransaction PFT where TranDate  between  %d  and %d
                                                    And TranType in(%n) and EmployeeID in (%q)
                                                    group by TranDate) B
                                                    Where A.TranDate = B.TranDate",
                                                    FromDate, toDate, (int)EnumPFTranType.PFAmount, sEmpID,
                                                    FromDate, toDate, (int)EnumPFTranType.CPFAmount, sEmpID);

                //              string query2 = SQLParser.MakeSQL(@"Select distinct TranDate Description,TranAmount PFAmount,TranAmount CPFAmount 
                //                                                    from PFTransaction PFT where TranDate between  %d  and %d
                //                                                    And TranType in(1,2) and EmployeeID in (%q)
                //                                                    order by TranDate",FromDate,toDate,sEmpID);

                tempdataset = tc.ExecuteDataSet(query2);

                tempdataset.Tables[0].TableName = "TranDate";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return rootDataset;
        }

        internal static void IntegrationUpdate(TransactionContext tc, Employee item)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE Employee SET Name=%s,"
                                            + "  gender=%n, "
                                            + "emailAddress=%s, mobileNo=%s, tinNo=%s"
                                            + " WHERE EmployeeID=%n", item.Name, item.Gender,
                item.EmailAddress, item.MobileNo, item.TinNo,
                    item.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static IDataReader GetEmployeeDataUsingExceptionType(TransactionContext tc, string ExceptionType)
        {
            string sql = "";
            if (ExceptionType == "Disable")
            {
                sql = SQLParser.MakeSQL(@"Select EMPLOYEEID,EMPLOYEENO,NAME,1 as ITEMVALUE,1 as TYPE From Employee where PersonType=1");
            }
            if (ExceptionType == "FreedomFighter")
            {
                sql = SQLParser.MakeSQL(@"Select EMPLOYEEID,EMPLOYEENO,NAME,1 as ITEMVALUE,2 as TYPE From Employee where PersonType=2");
            }
            if (ExceptionType == "OverAge")
            {
                sql = SQLParser.MakeSQL(@"Select EMPLOYEEID,EMPLOYEENO,NAME,1 as ITEMVALUE,3 as TYPE From Employee where FLOOR((CAST (GetDate() AS INTEGER) - CAST(birthDate AS INTEGER)) / 365.25)  > 65");
            };

            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetEmployeeDataUsingPersonType(TransactionContext tc, int PersonType)
        {
            string sql = SQLParser.MakeSQL(@"Select EMPLOYEEID,EMPLOYEENO,NAME,1 as ITEMVALUE,%n as TYPE From Employee where PersonType=%n", PersonType, PersonType);

            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetEmployeeDataUsingTaxCircle(TransactionContext tc, int TaxCircleType)
        {
            string sql = SQLParser.MakeSQL(@"Select EMPLOYEEID,EMPLOYEENO,NAME,1 as ITEMVALUE,%n as TYPE From Employee where TAXCIRCLE=%n", TaxCircleType, TaxCircleType);

            return tc.ExecuteReader(sql);
        }

        internal static void UpdateEmployeeByPersonType(TransactionContext tc, EmployeeTaxException item)
        {
            string sSQL;
            if (item.ItemValue == true)
            {
                sSQL = SQLParser.MakeSQL("UPDATE Employee SET  PERSONTYPE=%n WHERE EMPLOYEEID =%n", item.Type, item.EmployeeID);
            }
            else
            {
                sSQL = SQLParser.MakeSQL("UPDATE Employee SET  PERSONTYPE=%n WHERE EMPLOYEEID =%n", 0, item.EmployeeID);
            }
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void UpdateEmployeeByTaxCircle(TransactionContext tc, EmployeeTaxException item)
        {
            string sSQL;
            if (item.ItemValue == true)
            {
                sSQL = SQLParser.MakeSQL("UPDATE Employee SET  TAXCIRCLE=%n WHERE EMPLOYEEID =%n", item.Type, item.EmployeeID);
            }
            else
            {
                sSQL = SQLParser.MakeSQL("UPDATE Employee SET  TAXCIRCLE=%n WHERE EMPLOYEEID =%n", 0, item.EmployeeID);
            }
            tc.ExecuteNonQuery(sSQL);
        }
        internal static DataTable GetEmployeeNameAndNo(TransactionContext tc, int payrolltypeid)
        {
            return tc.ExecuteDataTable(
                "SELECT e.employeeId as id, e.employeeNo, e.name FROM employee e WHERE e.payrolltypeid=%n", payrolltypeid);
        }
    }

    #endregion
}