using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ease.CoreV35.DataAccess;
using Payroll.BO;

namespace Payroll.Service
{
    public class MiscellaneousDatasetDA
    {
        internal static IDataReader GetByGlobalID(TransactionContext tc, string GPDPID)
        {
            string sql = SQLParser.MakeSQL(@"Select EmployeeID From Employee Where GlobalID = %s", GPDPID);
            return tc.ExecuteReader(sql);
        }

        internal static DataSet GetEmpGrossPay(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string queryGross = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,Sum(A.amount) as GrossAmount,a.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID 
                        from( select S.EmployeeID,  Sum(sd.changedamount) as Amount,S.LOCATIONID from SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                        where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d   
                        and Sd.ItemGroup IN (1, 8) AND S.EmployeeID IN(%q)  group by S.EmployeeID,S.LOCATIONID 
                        UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID 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,S.LOCATIONID) A, Employee E 
                        where A.EmployeeID = E.EmployeeID  
                        group by E.EmployeeNo, E.Name,a.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID",
                        dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryGross);

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

                string queryTotalDeduction = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name, Sum(A.amount) as DeductionAmount,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID  
                    from ( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd 
                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth=%d and Sd.ItemGroup IN (2,3) 
                    AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID)A, Employee E 
                    where A.EmployeeID = E.EmployeeID  
                    group by E.EmployeeNo, E.Name,A.LOCATIONID,E.DESIGNATIONID,  E.DEPARTMENTID  ", dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryTotalDeduction);

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

        internal static DataSet GetTotalOPI(TransactionContext tc, string oPIItem, DateTime _SalaryMonth, string sEmpID)
        {
            DataSet oTotalOPI = new DataSet();
            string sQuary = SQLParser.MakeSQL("SELECT  e.EmployeeNo,e.DEPARTMENTID, e.Name as EmpName,"
                         + " e.DESIGNATIONID,e.BRANCHID, Sum(opdi.NetAmount) as OPIAmount"
                         + " FROM Employee e, "
                         + " OPIItem oi, OPIProcess op, OPIProcessDetail opd, OPIProcessDetailItem opdi "
                         + " WHERE op.OPIMonth >= %d AND op.OPIMonth <= %d  "
                         + " AND e.EmployeeId IN(%q)  AND op.OPIProcessID = opd.OPIProcessID"
                         + " AND e.EmployeeId = opd.EmployeeId  AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId"
                         + " AND oi.OPIItemId = opdi.OPIItemId  "
                         + " GROUP BY  e.EmployeeId, e.EmployeeNo, e.Name,e.DEPARTMENTID,e.DESIGNATIONID,"
                         + " e.BRANCHID ORDER BY  e.EmployeeNo",
                GlobalFunctions.FirstDateOfMonth(_SalaryMonth), GlobalFunctions.LastDateOfMonth(_SalaryMonth), sEmpID, oPIItem);

            oTotalOPI = tc.ExecuteDataSet(sQuary);

            return oTotalOPI;
        }

        internal static DataSet GetEmpDeduction(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string deductItem = SQLParser.MakeSQL("select  A.EmployeeNo,A.CIFNo, A.Name,A.GName,A.GCode,A.SequenceNo,A.DName,A.Description, "
                                + " SUM(A.changedamount) Amount,A.Department,A.Branch,A.DEPARTMENTID,A.ITEMCODE,A.ITEMGROUP,A.ITEMID  "
                                + " from ("
                                + " select S.EmployeeID, E.EmployeeNo, E.Name,E.GLOBALID as CIFNo, "
                                + " sd.ItemGroup,sd.ItemCode,  sd.ItemID, sd.Description,sd.Position, "
                                + " sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName ,"
                                + " DE.DESCRIPTION as Department,L.DESCRIPTION as Branch,DE.DEPARTMENTID"

                                + " from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd,DEPARTMENT as DE,"
                                + " LOCATION as L  "
                                + " where G.GradeID= E.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID "
                                + " AND E.employeeID=S.EmployeeID AND  S.SalaryMonthlyID=Sd.SalaryMonthlyID "
                                + " AND S.SalaryMonth=%d and Sd.ItemGroup=3 "
                                + " AND L.LOCATIONID=S.LOCATIONID and DE.DEPARTMENTID=S.DEPARTMENTID"
                                + " AND S.EmployeeID IN(%q)  "
                                + " union "
                                + " select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode,E.GLOBALID as CIFNo, "
                                + " sd.ItemID, sd.Description,sd.Position, -sd.changedamount,G.DESCRIPTION as GName,"
                                + " G.Code as GCode,G.sequenceNo,D.Name as DName,DE.DESCRIPTION as Department,L.DESCRIPTION as Branch,DE.DEPARTMENTID "
                                + " from Grades G,DESIGNATION D,Employee E, "
                                + " SALARYMONTHLY S, SalaryMonthlyDetail Sd,DEPARTMENT as DE,LOCATION as L "
                                + " where G.GradeID= E.GradeID "
                                + " AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID "
                                + " AND DE.DEPARTMENTID=S.DEPARTMENTID AND L.LOCATIONID=S.LOCATIONID "
                                + " AND  S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d"
                                + " and Sd.ItemGroup=2 AND S.EmployeeID IN(%q)  "
                                + " ) A "
                                + " group by  A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.SequenceNo,A.GCode,A.CIFNo,"
                                + " A.DName,  A.ItemCode, A.ItemID,A.ITEMGROUP, A.Description,A.Position ,A.Department,A.Branch,A.DEPARTMENTID "
                                + " order by A.EmployeeNo", dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(deductItem);

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

                string query6 = SQLParser.MakeSQL("select E.EmployeeNo, E.Name,A.GName,A.GCode,A.SequenceNo,A.DName,A.Department,"
                               + " 'Total Deduction' as Description, Sum(A.amount) as amount,A.Branch,A.DEPARTMENTID  "
                               + " ,E.GLOBALID as CIFNo from"
                               + " ( select S.EmployeeID, Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,"
                               + " G.Code as GCode,G.SequenceNo,D.Name as DName,DE.DEPARTMENTID,"
                               + " DE.DESCRIPTION as Department,L.DESCRIPTION as Branch  "
                               + " from DEPARTMENT DE,LOCATION L,"
                               + " GRADES G,DESIGNATION D,SALARYMONTHLY S,SalaryMonthlyDetail Sd"
                               + " where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth=%d"
                               + " AND S.DEPARTMENTID=DE.DEPARTMENTID AND S.LOCATIONID = L.LOCATIONID"
                               + " AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID "
                               + " and Sd.ItemGroup IN (2,3) AND S.EmployeeID IN(%q)"
                               + " group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,DE.DESCRIPTION,"
                               + " L.DESCRIPTION,DE.DEPARTMENTID ) A, Employee E "
                               + " where A.EmployeeID = E.EmployeeID "
                               + " group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.GCode,A.DName ,A.Department,"
                              + "  A.Branch,A.DEPARTMENTID,E.GLOBALID order by E.EmployeeNo", dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(query6);

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

        internal static DataSet GetCashChequePaymentReport(TransactionContext tc, DateTime SMonth, string PaymentMode)
        {
            DataSet paymentDataset = new DataSet();
            try
            {
                if (PaymentMode == "Cash Payment")
                {
                    string queryCashPayment = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.[NAME], dept.[DESCRIPTION] Department, desig.[NAME] Designation, smd.changedAmount Amount 
                                            FROM SALARYMONTHLY s
                                            INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=s.EMPLOYEEID
                                            INNER JOIN SALARYMONTHLYDETAIL smd ON s.SALARYMONTHLYID=smd.SALARYMONTHLYID
                                            LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                            LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID 
                                            WHERE s.SalaryMonth=%d AND smd.ITEMGROUP=5 AND smd.ITEMID=-132 AND s.ACCOUNTNO<>'' Order BY  e.EMPLOYEENO ASC", SMonth);
                    
                    paymentDataset = tc.ExecuteDataSet(queryCashPayment);
                }
                else
                {
                    string queryChequePayment = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.[NAME], dept.[DESCRIPTION] Department, s.rccode Designation, smd.changedAmount Amount 
                                            FROM SALARYMONTHLY s
                                            INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=s.EMPLOYEEID
                                            INNER JOIN SALARYMONTHLYDETAIL smd ON s.SALARYMONTHLYID=smd.SALARYMONTHLYID
                                            LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                            WHERE s.SalaryMonth=%d AND smd.ITEMGROUP=5 AND smd.ITEMID=-132 AND s.ACCOUNTNO='' Order BY  e.EMPLOYEENO ASC", SMonth);

                    paymentDataset = tc.ExecuteDataSet(queryChequePayment);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return paymentDataset;
        }
        internal static DataSet GetCashChequePaymentReportBonus(TransactionContext tc, DateTime SMonth, string PaymentMode)
        {
            DataSet paymentDataset = new DataSet();
            try
            {

                string queryChequePayment = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.[NAME], dept.[DESCRIPTION] Department, '' Designation, bpd.BonusAmount-bpd.TaxAmount Amount 
                                                            FROM BONUSPROCESS bp
                                                            INNER JOIN bonusprocessdetail  bpd ON bp.BONUSPROCESSID=bpd.BonusProcessID
                                                            INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=bpd.EMPLOYEEID
                                                            LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                                            WHERE bp.BONUSID=1 AND bp.DISBURSEDATE BETWEEN %d AND %d AND e.ACCOUNTNO='' Order BY  e.EMPLOYEENO ASC", GlobalFunctions.FirstDateOfMonth(SMonth), GlobalFunctions.LastDateOfMonth(SMonth));

                paymentDataset = tc.ExecuteDataSet(queryChequePayment);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return paymentDataset;
        }
        internal static DataSet GetBranchWiseSalary(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string queryGross = SQLParser.MakeSQL(@"Select Sum(A.amount) as GrossAmount,E.DEPARTMENTID ,
                        A.LOCATIONID from( select S.EmployeeID,Sum(sd.changedamount) as Amount,S.LOCATIONID  
                        from SALARYMONTHLY S, SalaryMonthlyDetail Sd   where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                        AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8)  
                        AND S.EmployeeID IN(%q) 
                        group by S.EmployeeID,S.LOCATIONID UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID 
                        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,S.LOCATIONID) A, Employee E  where A.EmployeeID = E.EmployeeID   
                        group by A.LOCATIONID,E.DEPARTMENTID 
                        order by A.LocationID,E.DEPARTMENTID",
                        dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryGross);

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

                string sFestiveBonus = SQLParser.MakeSQL(@"Select Sum(A.amount) as FestivalBonus, E.DEPARTMENTID,
                            A.LOCATIONID from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID  
                            from SALARYMONTHLY S, SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) and ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID  IN(%q) 
                            group by S.EmployeeID,S.LOCATIONID UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID 
                            from SALARYMONTHLY S,SalaryMonthlyDetail Sd  where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d  and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID IN(%q) 
                            group by S.EmployeeID,S.LOCATIONID) A, Employee E  where A.EmployeeID = E.EmployeeID   
                            group by A.LOCATIONID,E.DEPARTMENTID
                            order by A.LOCATIONID,E.DEPARTMENTID",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sFestiveBonus);

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

                string sPerformanceBonus = SQLParser.MakeSQL(@"Select Sum(A.amount) as PerformBonus,E.DEPARTMENTID,
                            A.LOCATIONID from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID  
                            from SALARYMONTHLY S, SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) and ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID  IN(%q) group by S.EmployeeID,S.LOCATIONID 
                            UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID 
                            from SALARYMONTHLY S,SalaryMonthlyDetail Sd  where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID IN(%q) 
                            group by S.EmployeeID,S.LOCATIONID) A, Employee E  where A.EmployeeID = E.EmployeeID   
                            group by A.LOCATIONID,E.DEPARTMENTID 
                            order by A.LOCATIONID,E.DEPARTMENTID",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sPerformanceBonus);

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

                string queryTotalDeduction = SQLParser.MakeSQL(@"select A.LOCATIONID, Sum(A.amount) as DeductionAmount,
                E.DEPARTMENTID  from ( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID 
                from SALARYMONTHLY S,SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID  
                AND S.SalaryMonth=%d and Sd.ItemGroup IN (2,3) 
                AND S.EmployeeID IN(%q) 
                group by S.EmployeeID,S.LOCATIONID)A, Employee E where A.EmployeeID = E.EmployeeID  
                group by A.LOCATIONID,E.DEPARTMENTID 
                order by A.LOCATIONID,E.DEPARTMENTID", dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryTotalDeduction);

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

        internal static DataSet GetCCWiseSalaryDetails(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"select  A.EmployeeNo, A.Name,A.GName,A.DName,A.GCode,A.CRGDes,A.SequenceNO,
                                A.Description, SUM(A.amonut) amount,A.PERCENTAGE,A.DEPARTMENTID,A.LocationID 
                                from (select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode, 
                                sd.ItemID, sd.Description,sd.Position, sd.changedamount*SEMCC.PERCENTAGE/100 as amonut,
                                SEMCC.PERCENTAGE, G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,G.SequenceNo,
                                E.DEPARTMENTID,E.LocationID, CC.DESCRIPTION as CRGDes 
                                from Grades G,CRG as CC,SALARYEMPCOSTCENTER as SEMCC , DESIGNATION D,Employee E, 
                                SALARYMONTHLY S, SalaryMonthlyDetail Sd   where G.GradeID= E.GradeID 
                                AND S.EMPLOYEEID=SEMCC.EMPLOYEEID  AND CC.CRGID=SEMCC.COSTCENTERID 
                                AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND SD.SALARYMONTHLYID=SEMCC.SALARYMONTHLYID 
                                AND S.SalaryMonth=%d and Sd.ItemGroup in(8,4)  
                                AND sd.ItemCode not in(-133)  AND S.EmployeeID IN(%q) 
                                union select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode, sd.ItemID,
                                sd.Description,sd.Position,sd.changedamount*SEMCC.PERCENTAGE/100 as amonut,
                                SEMCC.PERCENTAGE, G.DESCRIPTION as GName, D.Name as DName,G.Code as GCode,G.SequenceNo,
                                E.DEPARTMENTID,E.LocationID, CC.DESCRIPTION as CRGDes
                                from Grades G,CRG as CC,
                                SALARYEMPCOSTCENTER as SEMCC, DESIGNATION D,Employee E,  SALARYMONTHLY S, 
                                SalaryMonthlyDetail Sd   where G.GradeID= E.GradeID  AND S.EMPLOYEEID=SEMCC.EMPLOYEEID  
                                AND CC.CRGID=SEMCC.COSTCENTERID AND D.DESIGNATIONID=E.DESIGNATIONID 
                                AND E.employeeID=S.EmployeeID  AND SD.SALARYMONTHLYID=SEMCC.SALARYMONTHLYID 
                                AND S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth=%d 
                                and Sd.ItemGroup in(1,4) AND sd.ItemCode not in(-133) AND S.EmployeeID 
                                IN(%q)  ) A  
                                group by  A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.DName,A.SequenceNo,A.GCode, 
                                A.CRGDes, A.ItemCode, A.ItemID, A.Description,A.Position ,A.PERCENTAGE,A.DEPARTMENTID,
                                A.LocationID  order by A.EmployeeNo,A.CRGDes,A.Position,A.SequenceNo,A.GCode",
                                dSMonthDate, sEmpID, dSMonthDate, sEmpID, dSMonthDate, sEmpID);
                tempdataset = tc.ExecuteDataSet(sSQL);

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

                string queryGross = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,A.GName,A.GCode,A.SequenceNo,A.DName,'Gross Pay' as Description,  
                                      Sum(A.amount) as amount,A.CRGDes,A.PERCENTAGE,E.DEPARTMENTID,E.LocationID  from
                                      ( select S.EmployeeID, Sum(sd.changedamount)*EMPC.PERCENTAGE/100 as Amount,
                                      G.DESCRIPTION as GName,
                                      G.Code as GCode,G.SequenceNo,D.Name as DName,CC.DESCRIPTION as CRGDes,EMPC.PERCENTAGE 
                                      from SALARYMONTHLY S,Grades G,DESIGNATION D, SalaryMonthlyDetail Sd,
                                      CRG CC,EMPCOSTCENTER EMPC 
                                      where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                                      AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID AND S.SalaryMonth=%d
                                      and Sd.ItemGroup IN (1, 8) AND S.EmployeeID IN(%q)
                                      And S.EMPLOYEEID=EMPC.EMPLOYEEID AND EMPC.COSTCENTERID=CC.CRGID
                                      group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name 
                                      ,CC.DESCRIPTION ,EMPC.PERCENTAGE
                                      UNION
                                      select S.EmployeeID, -Sum(sd.changedamount)* EMPC.PERCENTAGE/100 as Amount,G.DESCRIPTION as GName,
                                      G.Code as GCode,G.sequenceNo,D.Name as DName,CC.DESCRIPTION as CRGDes,EMPC.PERCENTAGE
                                      from GRADES G,DESIGNATION D,SALARYMONTHLY S, SalaryMonthlyDetail Sd,CRG CC,EMPCOSTCENTER EMPC 
                                      where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                                      AND S.SalaryMonth=%d AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID 
                                      and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q) 
                                      And S.EMPLOYEEID=EMPC.EMPLOYEEID AND EMPC.COSTCENTERID=CC.CRGID
                                      group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,CC.DESCRIPTION,EMPC.PERCENTAGE 
                                      ) A, Employee E where A.EmployeeID = E.EmployeeID 
                                      group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.GCode,A.DName,A.CRGDes,A.PERCENTAGE,E.DEPARTMENTID,E.LocationID
                                      order by E.EmployeeNo",
                                       dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryGross);

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

        internal static DataSet GetDividionWiseReport(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string queryGross = SQLParser.MakeSQL(@"Select E.EMPLOYEEID,E.EmployeeNo, E.Name,Sum(A.amount) 
                        as GrossAmount,A.LocationID,E.BRANCHID,E.DESIGNATIONID, 
                        E.DEPARTMENTID  from( select S.EmployeeID,Sum(sd.changedamount) as Amount,S.LOCATIONID  from SALARYMONTHLY S, 
                        SalaryMonthlyDetail Sd   where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d 
                        and Sd.ItemGroup IN (1, 8) AND S.EmployeeID IN(%q) 
                        group by S.EmployeeID,S.LOCATIONID UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID 
                        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,S.LOCATIONID) A, Employee E  
                        where A.EmployeeID = E.EmployeeID   group by E.EMPLOYEEID,E.EmployeeNo, E.Name,A.LocationID,E.BRANCHID,
                        E.DESIGNATIONID,E.DEPARTMENTID order by E.EmployeeNo",
                        dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryGross);

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

                string sFestiveBonus = SQLParser.MakeSQL(@"Select E.EmployeeNo, E.Name,Sum(A.amount) as FestivalBonus,
                            A.LocationID , E.BRANCHID,E.DESIGNATIONID, 
                            E.DEPARTMENTID from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID  
                            from SALARYMONTHLY S, SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) and ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID  IN(%q) group by S.EmployeeID,S.LOCATIONID 
                            UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d  
                            and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID ) A, Employee E  
                            where A.EmployeeID = E.EmployeeID   group by E.EmployeeNo, E.Name,E.BRANCHID,E.DESIGNATIONID,
                            E.DEPARTMENTID,A.LocationID order by E.EmployeeNo",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sFestiveBonus);

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

                string sPerformanceBonus = SQLParser.MakeSQL(@"Select E.EmployeeNo, E.Name,Sum(A.amount) 
                            as PerformBonus,A.LocationID , E.BRANCHID,E.DESIGNATIONID, 
                            E.DEPARTMENTID from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID  
                            from SALARYMONTHLY S, SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) and ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID  IN(%q) group by S.EmployeeID,S.LOCATIONID 
                            UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d  
                            and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID ) A, Employee E  
                            where A.EmployeeID = E.EmployeeID   group by E.EmployeeNo, E.Name,A.LocationID , E.BRANCHID,E.DESIGNATIONID,
                            E.DEPARTMENTID order by E.EmployeeNo",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sPerformanceBonus);

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

                string SCoPFContri = SQLParser.MakeSQL(@"Select E.EmployeeNo, E.Name,Sum(A.changedamount) as CoPFContri,
                            A.LOCATIONID , E.BRANCHID,E.DESIGNATIONID,
                            E.DEPARTMENTID from( select S.EmployeeID,sd.changedamount,S.LOCATIONID    
                            from SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                            where  S.SalaryMonthlyID=Sd.SalaryMonthlyID
                            AND S.SalaryMonth=%d  and Sd.ItemGroup=3 
                            AND S.EmployeeID IN(%q) AND sd.ITEMCODE  in(-128)
                            ) A, Employee E 
                            where A.EmployeeID = E.EmployeeID  
                            group by E.EmployeeNo, E.Name,A.LocationID , E.BRANCHID,E.DESIGNATIONID,
                            E.DEPARTMENTID 
                            order by E.EmployeeNo",
                                dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(SCoPFContri);

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

                string queryTotalDeduction = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name, 
                Sum(A.amount) as DeductionAmount,A.LocationID ,E.BRANCHID,E.DESIGNATIONID,
                E.DEPARTMENTID  from ( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID 
                from SALARYMONTHLY S,SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID  
                AND S.SalaryMonth=%d and Sd.ItemGroup IN (2,3) 
                AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID)A, Employee E 
                where A.EmployeeID = E.EmployeeID  group by E.EmployeeNo, E.Name,A.LocationID ,E.BRANCHID,E.DESIGNATIONID , 
                E.DEPARTMENTID order by E.EmployeeNo ", dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryTotalDeduction);

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

        internal static DataSet GetSalaryPaySlipData(TransactionContext tc, DateTime dMonth, string sEmpID)
        {
            DataSet tempDataSet = new DataSet();
            try
            {
                string query = SQLParser.MakeSQL(@"select *
                                        from 
                                        (
                                            SELECT e.EMPLOYEENO, e.[NAME], desig.[NAME] Designation, dept.[DESCRIPTION] Department, smd.[DESCRIPTION], ISNULL(Sum(CONVERT(bigint, smd.CHANGEDAMOUNT)),0) AS Amount
                                            FROM SALARYMONTHLYDETAIL smd
                                            LEFT JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID= smd.SALARYMONTHLYID
                                            LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID= sm.EMPLOYEEID
                                            LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID = e.DEPARTMENTID
                                            LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID = e.DESIGNATIONID
                                            WHERE sm.SalaryMonth = %d AND sm.EMPLOYEEID IN (%q)
                                            GROUP BY e.EMPLOYEENO, E.[NAME], desig.[NAME], Dept.[DESCRIPTION], smd.[DESCRIPTION]
                                        ) src
                                        pivot
                                        (
                                          SUM(Amount)
                                          for Description in ( [Children Education Allowances], [Medical Allowances], [UnAuthorized Leave Days],[Other Allowances], [Manager's Incentive], [Entertainment Allowances],
                                          [Conveyance Allowances],[Basic],[Officer's Leave Fare Allowances],[Motor Bike (Penalty) Deduction],
                                           [PF], [Provident Fund Loan],[Provident Fund Loan balance], [Provident Fund Loan interest],
                                          [House Rent Allowances], [Other Deductions],[Special Allowance],[Gift for Anniversary],[Article Loan],[Manager's Medical Allowances], [Expiry Product], [Income Tax])
                                        ) piv;", dMonth,sEmpID);
               tempDataSet = tc.ExecuteDataSet(query);
                                                    
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return tempDataSet;
        }

        internal static DataSet GetExpensesPaySlipData(TransactionContext tc, DateTime fromDate, DateTime toDate, string sEmpID)
        {
            DataSet tempDataSet = new DataSet();
            try
            {
                string query = SQLParser.MakeSQL(@"SELECT * from (SELECT e.EmployeeNo, e.Name as EmpName,e.Tinno,e.JoiningDate,e.OUTPAYACCOUNTNO,e.PaymentMode, d.[DESCRIPTION] AS Department, desig.[NAME] AS Designation,g.Code Grade, oi.Name as OPIItem, 
                                        ISNULL(Sum(CONVERT(bigint, opdi.NetAmount)),0) AS Amount  FROM  OPIItem oi
                                        LEFT JOIN OPIProcessDetailItem opdi ON oi.OPIItemId = opdi.OPIItemId
                                        LEFT JOIN OPIProcessDetail opd ON opd.OPIProcessDetailId = opdi.OPIProcessDetailId  
                                        LEFT JOIN OPIProcess op ON op.OPIProcessID = opd.OPIProcessID   
                                        LEFT JOIN Employee e ON e.EMPLOYEEID = opd.EmployeeId
                                        LEFT JOIN DEPARTMENT d ON d.DEPARTMENTID = e.DEPARTMENTID
                                        LEFT JOIN Designation desig ON desig.DESIGNATIONID = e.DESIGNATIONID 
                                        LEFT JOIN Grades g on g.GradeId=e.GradeId
                                        WHERE op.OPIMonth >= %d AND op.OPIMonth <= %d  AND e.EmployeeId IN(%q)  
                                        GROUP BY e.EmployeeNo, e.Name, d.[DESCRIPTION], desig.[NAME], g.Code,oi.[Name],e.Tinno,e.JoiningDate,e.OUTPAYACCOUNTNO,e.PaymentMode  
                                        ) src
                                        pivot
	                                        (
	                                          SUM(Amount)
	                                          for OPIItem in ( [Utility Expenses], [Leave Fare Assistance Expense], [Medical Expenses],[Servant Allowances], [Car Allowances/ Expenses], [Driver Allowances],
	                                          [Security Allowances],[Worker’s Profit Participation Fund],[Manager’s Telephone Expenses],[Telephone Expenses (Mobile Bill)],
	                                           [Mobile Set], [Group Insurance],[Other Expenses])
	                                        ) piv;", fromDate, toDate, sEmpID);
                tempDataSet = tc.ExecuteDataSet(query);

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

        internal static DataSet GetCTCDetail(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string queryGross = SQLParser.MakeSQL(@"select E.EmployeeID, E.EmployeeNo,E.GLOBALID as CIFNo, E.Name,A.GName,A.GCode,A.SequenceNo,
                    E.DESIGNATIONID,'GrossPay' as Description,  
                    Sum(A.amount) as amount,A.CRGDes,A.PERCENTAGE,E.DEPARTMENTID,A.LOCATIONID  from
                    ( select S.EmployeeID, Sum(sd.changedamount)*EMPC.PERCENTAGE/100 as Amount,S.LOCATIONID,
                    G.DESCRIPTION as GName,
                    G.Code as GCode,G.SequenceNo,D.Name as DName,CC.DESCRIPTION as CRGDes,EMPC.PERCENTAGE 
                    from SALARYMONTHLY S,Grades G,DESIGNATION D, SalaryMonthlyDetail Sd,
                    CRG CC,EMPCOSTCENTER EMPC 
                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                    AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID AND S.SalaryMonth=%d
                    and Sd.ItemGroup IN (1, 8) AND S.EmployeeID IN(%q)
                    And S.EMPLOYEEID=EMPC.EMPLOYEEID AND EMPC.COSTCENTERID=CC.CRGID
                    group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name 
                    ,CC.DESCRIPTION ,EMPC.PERCENTAGE,S.LOCATIONID
                    UNION
                    select S.EmployeeID, -Sum(sd.changedamount)* EMPC.PERCENTAGE/100 as Amount,S.LOCATIONID,G.DESCRIPTION as GName,
                    G.Code as GCode,G.sequenceNo,D.Name as DName,CC.DESCRIPTION as CRGDes,EMPC.PERCENTAGE
                    from GRADES G,DESIGNATION D,SALARYMONTHLY S, SalaryMonthlyDetail Sd,CRG CC,EMPCOSTCENTER EMPC 
                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                    AND S.SalaryMonth=%d AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID 
                    and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q) 
                    And S.EMPLOYEEID=EMPC.EMPLOYEEID AND EMPC.COSTCENTERID=CC.CRGID
                    group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,CC.DESCRIPTION,EMPC.PERCENTAGE,S.LOCATIONID 
                    ) A, Employee E where A.EmployeeID = E.EmployeeID 
                    group by E.EmployeeID,E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.GCode,E.DESIGNATIONID,A.CRGDes,A.LOCATIONID,
                    A.PERCENTAGE,E.DEPARTMENTID,E.GLOBALID order by E.EmployeeNo", dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryGross);

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

                string sFestiveBonus = SQLParser.MakeSQL(@"Select E.EmployeeNo, E.Name,Sum(A.amount) as FestivalBonus,A.LOCATIONID, E.BRANCHID,E.DESIGNATIONID, E.DEPARTMENTID 
                            from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID  from SALARYMONTHLY S, SalaryMonthlyDetail Sd 
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d 
                            and Sd.ItemGroup IN (1, 8) and ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID  IN(%q) group by S.EmployeeID,S.LOCATIONID 
                            UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d  
                            and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID) A, Employee E  
                            where A.EmployeeID = E.EmployeeID   group by E.EmployeeNo, E.Name,A.LOCATIONID, E.BRANCHID,E.DESIGNATIONID,E.DEPARTMENTID order by E.EmployeeNo",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sFestiveBonus);

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

                string sPerformanceBonus = SQLParser.MakeSQL(@"Select E.EmployeeNo, E.Name,Sum(A.amount) as PerformBonus,A.LOCATIONID,E.DESIGNATIONID, E.DEPARTMENTID 
                            from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID  
                            from SALARYMONTHLY S, SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) and ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID  IN(%q) group by S.EmployeeID,S.LOCATIONID 
                            UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d  
                            and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113) 
                            AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID) A, Employee E  
                            where A.EmployeeID = E.EmployeeID   group by E.EmployeeNo, E.Name,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID order by E.EmployeeNo",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sPerformanceBonus);

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

                string SCoPFContri = SQLParser.MakeSQL(@"Select E.EmployeeNo, E.Name,Sum(A.amount) as CoPFContri,A.LOCATIONID,E.DESIGNATIONID,
                                E.DEPARTMENTID from( select S.EmployeeID,
                                Sum(sd.changedamount) as Amount,S.LOCATIONID 
                                from SALARYMONTHLY S, SalaryMonthlyDetail Sd
                                where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d
                                and Sd.ItemGroup IN (3) AND S.EmployeeID IN(%q) AND Sd.ITEMCODE  in(-128)
                                group by S.EmployeeID,S.LOCATIONID
                                ) A, Employee E 
                                where A.EmployeeID = E.EmployeeID  
                                group by E.EmployeeNo, E.Name,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID order by E.EmployeeNo",
                                dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(SCoPFContri);

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

                string queryTotalDeduction = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name, Sum(A.amount) 
                as DeductionAmount,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID  
                from ( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.LOCATIONID 
                from SALARYMONTHLY S,SalaryMonthlyDetail Sd where S.SalaryMonthlyID=Sd.SalaryMonthlyID  
                AND S.SalaryMonth=%d and Sd.ItemGroup IN (2,3) 
                AND S.EmployeeID IN(%q) group by S.EmployeeID,S.LOCATIONID)A, Employee E 
                where A.EmployeeID = E.EmployeeID  
                group by E.EmployeeNo, E.Name,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID order by E.EmployeeNo", dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryTotalDeduction);

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

        internal static DataSet GetIncrement(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string queryGross = SQLParser.MakeSQL(@"Select distinct E.EMPLOYEEID,E.EmployeeNo, E.Name,Sum(A.amount) as GrossAmount,A.LOCATIONID,
                        E.DESIGNATIONID, E.DEPARTMENTID,A.CRGDes,A.CRGID,A.PERCENTAGE  
                        from( select S.EmployeeID,Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as amount,
                        CC.DESCRIPTION as CRGDes,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID
                        from SALARYMONTHLY S, SalaryMonthlyDetail Sd,SALARYEMPCOSTCENTER SEMPCC,CRG CC
                        where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) 
                        And S.EMPLOYEEID=SEMPCC.EMPLOYEEID AND CC.CRGID=SEMPCC.COSTCENTERID
                        AND S.EmployeeID IN(%q)
                        group by S.EmployeeID,CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID 
                        UNION select S.EmployeeID, -Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as amount,CC.DESCRIPTION as CRGDes,CC.CRGID, 
                        SEMPCC.PERCENTAGE,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd,SALARYEMPCOSTCENTER SEMPCC,CRG CC  
                        where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup IN (2) 
                        And S.EMPLOYEEID=SEMPCC.EMPLOYEEID AND CC.CRGID=SEMPCC.COSTCENTERID
                        AND S.EmployeeID IN(%q)
                        group by S.EmployeeID,CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID) A, 
                        Employee E  where A.EmployeeID = E.EmployeeID   
                        group by E.EMPLOYEEID,E.EmployeeNo, E.Name,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID,
                        A.CRGDes,A.PERCENTAGE,A.CRGID order by E.EmployeeNo",
                        dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryGross);

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

                string queryBasic = SQLParser.MakeSQL(@"Select E.EMPLOYEEID,E.EmployeeNo, E.Name,A.amount as BasicAmount,
                                    A.LOCATIONID,E.DESIGNATIONID, E.DEPARTMENTID ,A.CRGDes,A.CRGID,A.PERCENTAGE 
                                    from( select S.EmployeeID,Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as Amount,CC.DESCRIPTION as CRGDes,CC.CRGID , 
                                    SEMPCC.PERCENTAGE,S.LOCATIONID from SALARYMONTHLY S, SalaryMonthlyDetail Sd ,SALARYEMPCOSTCENTER SEMPCC,CRG CC  
                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d 
                                    and Sd.ItemGroup IN (1, 8) and ITEMID in (-101) And S.EMPLOYEEID=SEMPCC.EMPLOYEEID 
                                    AND CC.CRGID=SEMPCC.COSTCENTERID AND S.EmployeeID IN(%q) 
                                    group by S.EmployeeID, CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID UNION select S.EmployeeID, 
                                    -Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as amount,CC.DESCRIPTION as CRGDes,CC.CRGID,
                                    SEMPCC.PERCENTAGE,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd ,SALARYEMPCOSTCENTER SEMPCC,CRG CC 
                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d 
                                    and Sd.ItemGroup IN (2) AND ITEMID  in (-101) AND S.EmployeeID IN(%q) 
                                    group by S.EmployeeID,CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID)A,
                                    Employee E  where A.EmployeeID = E.EmployeeID   
                                    group by E.EMPLOYEEID,E.EmployeeNo, E.Name,A.LOCATIONID,A.amount,
                                    E.DESIGNATIONID,E.DEPARTMENTID ,A.CRGDes,A.PERCENTAGE,A.CRGID
                                    order by E.EmployeeNo",
                                    dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(queryBasic);

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

                string sFestiveBonus = SQLParser.MakeSQL(@" Select E.EmployeeNo, E.Name,Sum(A.amount) as FestivalBonus,
                            A.LOCATIONID,E.DESIGNATIONID, E.DEPARTMENTID,A.CRGDes,A.CRGID,A.PERCENTAGE 
                            from( select S.EmployeeID, Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as Amount,CC.DESCRIPTION as CRGDes,CC.CRGID,
                            SEMPCC.PERCENTAGE,S.LOCATIONID from SALARYMONTHLY S, SalaryMonthlyDetail Sd ,SALARYEMPCOSTCENTER SEMPCC,CRG CC
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) 
                            and ITEMID in (4) AND ITEMCODE in(-113) And S.EMPLOYEEID=SEMPCC.EMPLOYEEID AND CC.CRGID=SEMPCC.COSTCENTERID
                            AND S.EmployeeID  IN(%q)
                            group by S.EmployeeID,CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID UNION select S.EmployeeID, 
                            -Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as Amount,CC.DESCRIPTION as CRGDes,CC.CRGID,SEMPCC.PERCENTAGE 
                            ,S.LOCATIONID from SALARYMONTHLY S,SalaryMonthlyDetail Sd,SALARYEMPCOSTCENTER SEMPCC,CRG CC  
                            where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                            AND S.SalaryMonth=%d  and Sd.ItemGroup IN (2) AND ITEMID in (4) AND ITEMCODE in(-113)
                            And S.EMPLOYEEID=SEMPCC.EMPLOYEEID AND CC.CRGID=SEMPCC.COSTCENTERID 
                            AND S.EmployeeID IN(%q) 
                            group by S.EmployeeID,CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID ) A, Employee E  where A.EmployeeID = E.EmployeeID   group by E.EmployeeNo, E.Name, 
                            A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID,A.CRGDes,A.PERCENTAGE,A.CRGID 
                            order by E.EmployeeNo",
                            dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sFestiveBonus);

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

                string SCoPFContri = SQLParser.MakeSQL(@" Select E.EmployeeNo, E.Name,Sum(A.amount) as CoPFContri,A.LOCATIONID,E.DESIGNATIONID,
                    E.DEPARTMENTID,A.CRGDes,A.CRGID,A.PERCENTAGE from( select S.EmployeeID,
                    Sum(sd.changedamount)*SEMPCC.PERCENTAGE/100 as Amount,CC.DESCRIPTION as CRGDes,
                    CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID 
                    from SALARYMONTHLY S, SalaryMonthlyDetail Sd,SALARYEMPCOSTCENTER SEMPCC,CRG CC
                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d
                    and Sd.ItemGroup IN (3) And S.EMPLOYEEID=SEMPCC.EMPLOYEEID AND CC.CRGID=SEMPCC.COSTCENTERID
                    AND S.EmployeeID IN(%q)
                    group by S.EmployeeID,CC.DESCRIPTION,CC.CRGID,SEMPCC.PERCENTAGE,S.LOCATIONID
                    ) A, Employee E 
                    where A.EmployeeID = E.EmployeeID  
                    group by E.EmployeeNo, E.Name,A.LOCATIONID,E.DESIGNATIONID,E.DEPARTMENTID,
                    A.CRGDes,A.PERCENTAGE,A.CRGID order by E.EmployeeNo",
                    dSMonthDate, sEmpID, dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(SCoPFContri);

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

        internal static DataSet GetADHeadReport(TransactionContext tc, DateTime dSMonthDate, string checkItem, string itemcode)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string sAllowanceDeduct = SQLParser.MakeSQL("select S.EMPLOYEEID,S.LOCATIONID,Sd.DESCRIPTION,Sum(Sd.CHANGEDAMOUNT) as Amount"
                                  + " from SALARYMONTHLY S,SALARYMONTHLYDETAIL Sd"
                                  + " where  S.SALARYMONTHLYID=SD.SALARYMONTHLYID AND S.SalaryMonth=%d %q) %q)"
                                  + " group by S.EMPLOYEEID,S.LOCATIONID,Sd.DESCRIPTION",
                                  dSMonthDate, checkItem, itemcode);

                tempdataset = tc.ExecuteDataSet(sAllowanceDeduct);

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

        internal static DataSet GetBEFTNform(TransactionContext tc, DateTime dmonth, string ids)
        {
            DataSet oBEFTNdataSet = new DataSet();
            try
            {
                string query = SQLParser.MakeSQL(@"SELECT s.SalaryMonth, br.CODE BranchCode, e.ACCOUNTNO, sd.CHANGEDAMOUNT AS Amount, e.[NAME]
                                                FROM SALARYMONTHLYDETAIL sd 
                                                LEFT JOIN SALARYMONTHLY s ON s.SALARYMONTHLYID=sd.SALARYMONTHLYID
                                                LEFT JOIN Employee e ON e.EMPLOYEEID= s.EMPLOYEEID
                                                LEFT JOIN BRANCHES br ON br.BRANCHID = s.BRANCHID
                                                WHERE sd.ITEMGROUP=5 AND s.SalaryMonth = %d AND s.ACCOUNTNO<>'' AND br.BRANCHID IN (%q) ", dmonth, ids);
                
                oBEFTNdataSet = tc.ExecuteDataSet(query);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return oBEFTNdataSet;
        }

        internal static DataSet GetOPIHeadReport(TransactionContext tc, DateTime dSMonthDate, string checkItem)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                //string sOPI = SQLParser.MakeSQL("Select D.EmployeeID,D.LocationID,I.Description,(I.ChangeNetAmount)as Amount from OPIProcess O,"
                //           + " OPIProcessDetail D, OPIProcessDetailItem I Where "
                //           + " O.OPIMonth=%d AND I.OPIProcessDetailID=D.OPIProcessDetailID"
                //           + " AND  O.OPIProcessID= D.OPIProcessID %q)"
                //           + " ORDER BY  D.OPIProcessDetailID, OPIItemID", dSMonthDate, checkItem);

                string sOPI = SQLParser.MakeSQL("Select D.EmployeeID,D.LocationID,I.Description,(I.ChangeNetAmount)as Amount,oi.SequenceNO" +
                " from OPIProcess O, OPIProcessDetail D, OPIProcessDetailItem I,OpiItem oi " +
                " Where  O.OPIMonth=%d AND I.OPIProcessDetailID=D.OPIProcessDetailID " +
                " AND  O.OPIProcessID= D.OPIProcessID %q)" +
                " AND I.OPIItemID=oi.OpiItemID " +
                " ORDER BY  D.OPIProcessDetailID, I.OPIItemID", dSMonthDate, checkItem);

                tempdataset = tc.ExecuteDataSet(sOPI);

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

        internal static DataSet GetBranchWiseSalaryDetails(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"select sd.Description,SUM(sd.changedamount) Amount,S.DEPARTMENTID,S.LOCATIONID,
                                sd.ItemGroup,sd.ITEMCODE,sd.ITEMID
                                from SALARYMONTHLYDETAIL sd
                                inner join SALARYMONTHLY S on sd.SALARYMONTHLYID=S.SALARYMONTHLYID
                                where S.SalaryMonth=%d and sd.ITEMGROUP in(1,2,8)
                                AND sd.ItemCode not in(-133)
                                AND S.EmployeeID IN(%q)
                                group by sd.DESCRIPTION,S.DEPARTMENTID,S.LOCATIONID,sd.ITEMCODE,sd.ITEMGROUP,sd.ITEMID
                                order by S.LOCATIONID,S.DEPARTMENTID",
                                dSMonthDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sSQL);

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

        internal static DataSet GetTotalBranchWiseOPI(TransactionContext tc, string str, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@" SELECT opd.DEPARTMENTID,opd.LOCATIONID,oi.OPIItemId, 
                oi.Name as OPIItem, Sum(opdi.NetAmount) as Amount  
                FROM OPIItem oi, OPIProcess op, OPIProcessDetail opd, OPIProcessDetailItem opdi  
                WHERE op.OPIMonth >= %d AND op.OPIMonth <=%d  
                AND opd.EmployeeId IN(%q)  
                AND op.OPIProcessID = opd.OPIProcessID  
                AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId  AND oi.OPIItemId = opdi.OPIItemId  
                GROUP BY opd.LOCATIONID,opd.DEPARTMENTID, oi.OPIItemId, oi.Name
                ORDER BY opd.LOCATIONID,opd.DEPARTMENTID",
                GlobalFunctions.FirstDateOfMonth(dSMonthDate), GlobalFunctions.LastDateOfMonth(dSMonthDate), sEmpID);
                tempdataset = tc.ExecuteDataSet(sSQL);

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

        internal static DataSet GetTotalOPIForBranch(TransactionContext tc, string oPIItem, DateTime _SalaryMonth, string sEmpID)
        {
            DataSet oTotalOPI = new DataSet();
            string sQuary = SQLParser.MakeSQL(@"SELECT  opd.DEPARTMENTID,opd.LocationID, 
                Sum(opdi.NetAmount) as OPIAmount FROM   OPIItem oi, OPIProcess op, OPIProcessDetail opd, 
                OPIProcessDetailItem opdi  WHERE op.OPIMonth >= %d AND op.OPIMonth <= %d   
                AND opd.EmployeeId IN(%q)  
                AND op.OPIProcessID = opd.OPIProcessID  
                AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId AND oi.OPIItemId = opdi.OPIItemId   
                GROUP BY opd.LocationID,opd.DEPARTMENTID 
                ORDER BY  opd.LocationID,opd.DEPARTMENTID",
                GlobalFunctions.FirstDateOfMonth(_SalaryMonth), GlobalFunctions.LastDateOfMonth(_SalaryMonth), sEmpID, oPIItem);

            oTotalOPI = tc.ExecuteDataSet(sQuary);

            return oTotalOPI;
        }

        internal static DataSet GetBranchWiseEmpDeduction(TransactionContext tc, DateTime _SalaryMonth, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"Select SMD.DESCRIPTION,SUM(SMD.CHANGEDAMOUNT) as Amount ,
                SMD.ITEMGROUP,SM.DEPARTMENTID,SM.LOCATIONID,SMD.ITEMID,SMD.ITEMCODE 
                from SALARYMONTHLYDETAIL SMD,SALARYMONTHLY SM 
                where SM.EMPLOYEEID in(%q) AND SMD.ITEMGROUP IN(3)
                AND SM.SALARYMONTH=%d AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID 
                AND SMD.CHANGEDAMOUNT<>0 GROUP by SMD.ITEMGROUP,SMD.ITEMID,SMD.ITEMCODE,
                SMD.DESCRIPTION, SM.DEPARTMENTID,SM.LOCATIONID
                order by SM.LOCATIONID,SM.DEPARTMENTID asc",
                sEmpID, _SalaryMonth);
                tempdataset = tc.ExecuteDataSet(sSQL);

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

        internal static DataSet GetLineManager(TransactionContext tc, string empId)
        {
            DataSet dSet = new DataSet();

            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", empId);
            dSet = tc.ExecuteDataSet(sql);

            return dSet;
        }

        internal static DataSet GetCCWiseFinance(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string GrossItem = SQLParser.MakeSQL(@"select A.Description,SUM(A.amount) as Amount,A.DEPARTMENTID,
                A.LOCATIONID,A.ItemGroup,A.ITEMCODE,A.ITEMID,A.CRGID,A.CostCenter
                from (Select S.LOCATIONID,S.DEPARTMENTID,sd.Description,(sd.CHANGEDAMOUNT*EMPCC.PERCENTAGE/100)as amount,
                sd.ItemGroup,sd.ITEMCODE,sd.ITEMID,CC.CRGID,CC.Description as CostCenter
                from SALARYMONTHLYDETAIL sd ,SALARYMONTHLY S,SALARYEMPCOSTCENTER EMPCC,CRG CC
                Where sd.SALARYMONTHLYID=S.SALARYMONTHLYID
                AND S.SALARYMONTHLYID=EMPCC.SALARYMONTHLYID
                AND EMPCC.COSTCENTERID=CC.CRGID
                AND S.SalaryMonth=%d and sd.ITEMGROUP in(1,8)
                AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q))A
                group by A.Description,A.DEPARTMENTID,A.LOCATIONID,A.ItemGroup,A.ITEMCODE,
                A.ITEMID,A.CRGID,A.CostCenter
                order by A.LOCATIONID,A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

                string DeductItem = SQLParser.MakeSQL(@"select A.Description,SUM(A.amount) as Amount,A.DEPARTMENTID,
                A.LOCATIONID,A.ItemGroup,A.ITEMCODE,A.ITEMID,A.CRGID,A.CostCenter
                from (Select S.LOCATIONID,S.DEPARTMENTID,sd.Description,(sd.CHANGEDAMOUNT*EMPCC.PERCENTAGE/100)as amount,
                sd.ItemGroup,sd.ITEMCODE,sd.ITEMID,CC.CRGID,CC.Description as CostCenter
                from SALARYMONTHLYDETAIL sd ,SALARYMONTHLY S,SALARYEMPCOSTCENTER EMPCC,CRG CC
                Where sd.SALARYMONTHLYID=S.SALARYMONTHLYID
                AND S.SALARYMONTHLYID=EMPCC.SALARYMONTHLYID
                AND EMPCC.COSTCENTERID=CC.CRGID AND sd.CHANGEDAMOUNT<>0
                AND S.SalaryMonth=%d and sd.ITEMGROUP in(3) AND S.EmployeeID IN(%q))A
                group by A.Description,A.DEPARTMENTID,A.LOCATIONID,A.ItemGroup,A.ITEMCODE,
                A.ITEMID,A.CRGID,A.CostCenter
                order by A.LOCATIONID,A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

                string opiItem = SQLParser.MakeSQL(@"select A.Description,SUM(A.amount) as Amount,A.DEPARTMENTID,
                A.LOCATIONID,A.CRGID,A.CostCenter,A.SequenceNO
                from (Select opd.LOCATIONID,opd.DEPARTMENTID,opdi.Description,(opdi.NetAmount*EMPCC.PERCENTAGE/100)as amount,
                CC.CRGID,CC.Description as CostCenter,oi.SequenceNO
                from OPIItem oi, OPIProcess op, OPIProcessDetail opd,SALARYEMPCOSTCENTER EMPCC,
                CRG CC,OPIProcessDetailItem opdi
                Where  op.OPIMonth == %d AND opd.EmployeeId IN(%q)
                AND op.OPIProcessID = opd.OPIProcessID 
                AND opd.EmployeeId  = EMPCC.EMPLOYEEID AND EMPCC.COSTCENTERID=CC.CRGID
                AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId 
                AND oi.OPIItemId = opdi.OPIItemId )A  
                group by A.Description,A.DEPARTMENTID,A.LOCATIONID,A.CRGID,A.CostCenter,A.SequenceNO
                order by A.LOCATIONID,A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

        internal static DataSet GetNewCCWiseSalaryDetails(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string GrossItem = SQLParser.MakeSQL(@"select  A.EmployeeNo, A.Name,A.Designation,A.CRGDes,A.CRGID,A.ItemCode, A.ItemID,A.ItemGroup,
                A.Description, SUM(A.amonut) Amount,A.PERCENTAGE,A.DEPARTMENTID,A.Location 
                from (select  E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode, 
                sd.ItemID, sd.Description, sd.changedamount*SEMCC.PERCENTAGE/100 as amonut,
                SEMCC.PERCENTAGE,D.Name as Designation,L.DESCRIPTION as Location,
                S.DEPARTMENTID, CC.DESCRIPTION as CRGDes,CC.CRGID 
                from CRG as CC,LOCATION as L,SALARYEMPCOSTCENTER as SEMCC , DESIGNATION D,Employee E, 
                SALARYMONTHLY S, SalaryMonthlyDetail Sd   where  
                S.EMPLOYEEID=SEMCC.EMPLOYEEID  AND CC.CRGID=SEMCC.COSTCENTERID 
                AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND SD.SALARYMONTHLYID=SEMCC.SALARYMONTHLYID 
                AND S.SalaryMonth=%d and Sd.ItemGroup in(8) AND S.LOCATIONID=L.LOCATIONID 
                AND S.EmployeeID IN(%q) 
                union select  E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode, sd.ItemID,
                sd.Description,sd.changedamount*SEMCC.PERCENTAGE/100 as amonut,
                SEMCC.PERCENTAGE, D.Name as Designation,L.DESCRIPTION as Location,
                S.DEPARTMENTID,CC.DESCRIPTION as CRGDes,CC.CRGID
                from CRG as CC,LOCATION as L,
                SALARYEMPCOSTCENTER as SEMCC,DESIGNATION D,Employee E,SALARYMONTHLY S, 
                SalaryMonthlyDetail Sd   where S.EMPLOYEEID=SEMCC.EMPLOYEEID  
                AND CC.CRGID=SEMCC.COSTCENTERID AND D.DESIGNATIONID=E.DESIGNATIONID 
                AND E.employeeID=S.EmployeeID  AND SD.SALARYMONTHLYID=SEMCC.SALARYMONTHLYID 
                AND S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth=%d 
                and Sd.ItemGroup in(1) AND S.LOCATIONID=L.LOCATIONID  
                AND S.EmployeeID IN(%q)  ) A  
                group by  A.EmployeeNo, A.Name,A.Designation,A.ItemGroup,A.Location,A.CRGID,
                A.CRGDes, A.ItemCode, A.ItemID, A.Description ,A.PERCENTAGE,A.DEPARTMENTID,A.Location 
                order by A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID, dSMonthDate, sEmpID);

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

        internal static DataSet GetCCWiseOPIDetails(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string opiItem = SQLParser.MakeSQL(@"select distinct A.Description,A.amount as Amount,A.DEPARTMENTID,A.Designation,
                A.LOCATION,A.CRGID,A.CostCenter,A.SequenceNO,A.EMPLOYEENO,A.NAME,A.PERCENTAGE
                from (Select opd.DEPARTMENTID,opdi.Description,(opdi.NetAmount*EMPCC.PERCENTAGE/100)as amount,
                CC.CRGID,CC.Description as CostCenter,oi.SequenceNO,L.DESCRIPTION as LOCATION,
                E.EMPLOYEENO,E.NAME,D.NAME as Designation,EMPCC.PERCENTAGE
                from OPIItem oi, OPIProcess op, OPIProcessDetail opd,SALARYEMPCOSTCENTER EMPCC,
                CRG CC,OPIProcessDetailItem opdi,EMPLOYEE E,LOCATION L,DESIGNATION D
                Where  op.OPIMonth = %d AND opd.EmployeeId IN(%q)
                AND op.OPIProcessID = opd.OPIProcessID AND opd.EmployeeId=E.EmployeeId
                AND opd.EmployeeId  = EMPCC.EMPLOYEEID AND EMPCC.COSTCENTERID=CC.CRGID
                AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId AND E.LOCATIONID=L.LOCATIONID
                AND oi.OPIItemId = opdi.OPIItemId AND opd.DESIGNATIONID=D.DESIGNATIONID)A
                order by A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

        internal static DataSet GetCCWiseSummary(TransactionContext tc, DateTime dSMonthDate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string totalGross = SQLParser.MakeSQL(@"Select A.CostCenter,A.CRGID,A.DEPARTMENTID,E.Role,sum(A.amount)as Amount
                        from( select S.EmployeeID,Sum(sd.changedamount)*EMPCC.PERCENTAGE/100 as amount,
                        S.DEPARTMENTID,CC.DESCRIPTION as CostCenter,CC.CRGID
                        from SALARYMONTHLY S,SalaryMonthlyDetail Sd,SALARYEMPCOSTCENTER EMPCC,CRG CC   
                        where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d 
                        and Sd.ItemGroup IN (1, 8) AND S.SALARYMONTHLYID=EMPCC.SALARYMONTHLYID
                        AND EMPCC.COSTCENTERID=CC.CRGID AND S.EmployeeID IN(%q)
                        group by S.EmployeeID,S.DEPARTMENTID,CC.DESCRIPTION,CC.CRGID,EMPCC.PERCENTAGE)A,Employee E  
                        where A.EmployeeID = E.EmployeeID   
                        group by A.CostCenter,A.CRGID,A.DEPARTMENTID,E.Role
                        order by A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

                string totalOPI = SQLParser.MakeSQL(@"select A.CostCenter,A.CRGID,A.DEPARTMENTID,A.Role,sum(A.amount)as Amount
                        from (select opd.EmployeeID,Sum(opdi.NetAmount)*EMPCC.PERCENTAGE/100 as amount,
                        opd.DEPARTMENTID,CC.DESCRIPTION as CostCenter,CC.CRGID,E.Role
                        from OPIItem oi, OPIProcess op, OPIProcessDetail opd,SALARYEMPCOSTCENTER EMPCC,
                        CRG CC,OPIProcessDetailItem opdi,EMPLOYEE E
                        Where  op.OPIMonth =%d AND opd.EmployeeId IN(%q)
                        AND op.OPIProcessID = opd.OPIProcessID AND opd.EmployeeId=E.EmployeeId
                        AND opd.EmployeeId  = EMPCC.EMPLOYEEID AND EMPCC.COSTCENTERID=CC.CRGID
                        AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId 
                        AND oi.OPIItemId = opdi.OPIItemId
                        group by opd.EmployeeID,EMPCC.PERCENTAGE,opd.DEPARTMENTID,CC.DESCRIPTION,CC.CRGID,E.Role)A
                        group by A.CostCenter,A.CRGID,A.DEPARTMENTID,A.Role
                        order by A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

                string coContri = SQLParser.MakeSQL(@"Select A.CostCenter,A.CRGID,A.DEPARTMENTID,E.Role,sum(A.amount)as Amount
                        from( select S.EmployeeID,Sum(sd.changedamount)*EMPCC.PERCENTAGE/100 as amount,
                        S.DEPARTMENTID,CC.DESCRIPTION as CostCenter,CC.CRGID
                        from SALARYMONTHLY S,SalaryMonthlyDetail Sd,SALARYEMPCOSTCENTER EMPCC,CRG CC   
                        where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d 
                        and Sd.ItemGroup IN(3)AND sd.ITEMCODE  in(-128)
                        AND S.SALARYMONTHLYID=EMPCC.SALARYMONTHLYID
                        AND EMPCC.COSTCENTERID=CC.CRGID AND S.EmployeeID IN(%q)
                        group by S.EmployeeID,S.DEPARTMENTID,CC.DESCRIPTION,CC.CRGID,EMPCC.PERCENTAGE)A,Employee E 
                        where A.EmployeeID = E.EmployeeID  
                        group by A.CostCenter,A.CRGID,A.DEPARTMENTID,E.Role 
                        order by A.CRGID,A.DEPARTMENTID", dSMonthDate, sEmpID);

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

        internal static DataSet GetItemWiseSalary(TransactionContext tc, string sEmpID, DateTime _SalaryMonth, DateTime toDate, string sSQL)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string SSalary = SQLParser.MakeSQL(@"select E.EmployeeNo,E.Name,S.SalaryMonth,sum(smd.ChangedAmount) as Amount
                                                    from SalaryMonthly s
                                                    inner join Employee E on S.EmployeeID=E.EmployeeID
                                                    inner join SalaryMonthlyDetail smd on S.SalaryMonthlyID=smd.SalaryMonthlyID
                                                    where S.EmployeeID in(%q) %q
                                                    And S.SalaryMonth between %d and %d
                                                    group by E.EmployeeNo,E.Name,S.SalaryMonth
                                                    order by E.EmployeeNo,S.SalaryMonth", sEmpID, sSQL, _SalaryMonth, toDate);

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

        internal static DataSet GetEmpIndSalarySheet(TransactionContext tc, string sEmpID, string _months)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string query1 = SQLParser.MakeSQL(@"select  A.EmployeeNo, A.Name,A.GName,A.DName,A.GCode,A.JoiningDate,A.SequenceNo, A.Description,A.SalaryMonth, 
                                                    SUM(A.changedamount) Amount from(select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate, sd.ItemGroup,sd.ItemCode,S.SalaryMonth,  
                                                    sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,G.SequenceNo 
                                                    from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                                    AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth  in(%q) 
                                                    and Sd.ItemGroup in(8) AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q)  
                                                    UNION  select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate, sd.ItemGroup,sd.ItemCode,S.SalaryMonth,sd.ItemID,
                                                    sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,G.SequenceNo 
                                                    from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                                    AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth in(%q) and Sd.ItemGroup in(1) 
                                                    AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q)  ) A 
                                                    group by  A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.DName,A.SequenceNo,A.GCode, A.ItemCode,A.SalaryMonth,
                                                    A.ItemID, A.Description,A.Position,A.JoiningDate order by A.SalaryMonth,A.Position,A.EmployeeNo,A.SequenceNo,A.GCode",
                                                   _months, sEmpID, _months, sEmpID);

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

                string query2 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName,'Total Gross' as Description,
                                                    Sum(A.amount) as amount,A.SalaryMonth  from( select S.EmployeeID, Sum(sd.changedamount) as Amount,
                                                    G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName,S.SalaryMonth  
                                                    from SALARYMONTHLY S,Grades G,DESIGNATION D, SalaryMonthlyDetail Sd  
                                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID 
                                                    AND S.SalaryMonth in(%q) and Sd.ItemGroup IN (1, 8) AND S.EmployeeID IN(%q)  
                                                    group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,S.SalaryMonth  
                                                    UNION select S.EmployeeID, -Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,
                                                    G.Code as GCode,G.sequenceNo,D.Name as DName,S.SalaryMonth from GRADES G,DESIGNATION D,SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth in(%q) AND G.GRADEID=S.GRADEID 
                                                    AND D.DESIGNATIONID=S.DESIGNATIONID  and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q)  
                                                    group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,S.SalaryMonth  ) A, Employee E 
                                                    where A.EmployeeID = E.EmployeeID  
                                                    group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.GCode,A.DName,E.JoiningDate,A.SalaryMonth",
                                                    _months, sEmpID, _months, sEmpID);

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

                string query3 = SQLParser.MakeSQL(@"select  A.EmployeeNo, A.Name,A.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName,  A.Description, 
                                                    SUM(A.changedamount) Amount,A.ItemCode,A.ItemID ,A.SalaryMonth 
                                                    from (select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate,sd.ItemGroup,sd.ItemCode,S.SalaryMonth,  
                                                    sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName  
                                                    from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                                    AND  S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth in(%q) and Sd.ItemGroup=3 AND S.EmployeeID IN(%q)  
                                                    union select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate,sd.ItemGroup,sd.ItemCode,S.SalaryMonth,  
                                                    sd.ItemID, sd.Description,sd.Position, -sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.sequenceNo,D.Name as DName 
                                                    from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                                    AND  S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth in(%q) and Sd.ItemGroup=2 AND S.EmployeeID IN(%q)  ) A 
                                                    group by  A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.SequenceNo,A.GCode,A.DName,A.SalaryMonth, 
                                                    A.ItemCode, A.ItemID, A.Description,A.Position,A.JoiningDate 
                                                   order by A.SalaryMonth,A.EmployeeNo,A.Position,A.SequenceNo,A.GCode",
                                                 _months, sEmpID, _months, sEmpID);

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

                string query4 = SQLParser.MakeSQL(@"select  A.EmployeeNo, A.Name,A.GName,A.GCode,A.SequenceNo,
                                                    A.DName, A.Description, SUM(A.changedamount) Amount,A.SalaryMonth 
                                                    from ( select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode,  
                                                    sd.ItemID, sd.Description,sd.Position, sd.changedamount,S.SalaryMonth,
                                                    G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName 
                                                    from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID 
                                                    AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                                                    AND S.SalaryMonth in(%q) and Sd.ITEMCODE =-133 AND S.EmployeeID IN(%q)  
                                                    UNION  select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,
                                                    sd.ItemCode,  sd.ItemID, sd.Description,sd.Position, sd.changedamount,S.SalaryMonth,
                                                    G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName 
                                                    from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                                    where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID 
                                                    AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                                                    AND S.SalaryMonth in(%q) and Sd.ITEMCODE =-133 AND S.EmployeeID IN(%q)  ) A 
                                                    group by  A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.SequenceNo,A.GCode,A.DName, A.ItemCode, 
                                                    A.ItemID, A.Description,A.Position,A.SalaryMonth 
                                                    order by A.SalaryMonth,A.Position,A.SequenceNo,A.GCode,A.EmployeeNo",
                                                    _months, sEmpID, _months, sEmpID);

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

                string query5 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,A.GName,A.GCode,A.sequenceNo,A.DName, 
                                                    'UnAuthorized' as Description, Sum(A.amount) as amount,A.SalaryMonth  
                                                    from ( select S.EmployeeID, Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,
                                                    G.Code as GCode,G.SequenceNo,D.Name as DName,S.SalaryMonth  
                                                    from GRADES G,DESIGNATION D,SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth in(%q) 
                                                    AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID 
                                                    and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q)  
                                                    group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,S.SalaryMonth ) A, Employee E 
                                                    where A.EmployeeID = E.EmployeeID 
                                                    group by E.EmployeeNo,E.Name,A.GName,A.SequenceNo,A.GCode,A.DName,A.SalaryMonth", _months, sEmpID);

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

                string query6 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName, 
                                                'Total Deduction' as Description, Sum(A.amount) as amount,A.SalaryMonth  
                                                from( select S.EmployeeID, Sum(sd.changedamount) as Amount,S.SalaryMonth,
                                                G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName  
                                                from GRADES G,DESIGNATION D,SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                                                where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth in(%q) 
                                                AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID 
                                                and Sd.ItemGroup IN (2,3) AND S.EmployeeID IN(%q)  
                                                group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name,S.SalaryMonth ) A, Employee E 
                                                where A.EmployeeID = E.EmployeeID group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.SalaryMonth,
                                                A.GCode,A.DName,E.JoiningDate 
                                               order by A.SalaryMonth,E.EmployeeNo,A.sequenceno,A.GCode", _months, sEmpID);

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

                string query7 = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,E.JoiningDate,G.DESCRIPTION as GName,G.Code as GCode,S.SalaryMonth,
                                                    G.SequenceNo,D.Name as DName ,'Net Pay' as Description,SD.CHANGEDAMOUNT as Amount 
                                                    from EMPLOYEE as E,GRADES as G,DESIGNATION D,SALARYMONTHLY as S,SALARYMONTHLYDETAIL as Sd 
                                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth in(%q) 
                                                    AND G.GRADEID=S.GRADEID  AND D.DESIGNATIONID=S.DESIGNATIONID  
                                                    AND E.EMPLOYEEID =S.EMPLOYEEID  and Sd.ItemGroup IN (5) AND Sd.ItemCode IN(-132) 
                                                    AND S.EmployeeID IN(%q) order by S.SalaryMonth,E.EMPLOYEENO,G.SequenceNo,G.Code ", _months, sEmpID);

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

                string query8 = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,E.JoiningDate,G.DESCRIPTION as GName,S.SalaryMonth,
                                                    G.Code as GCode,G.SequenceNo,D.Name as DName ,'CPF' as Description,SD.CHANGEDAMOUNT as Amount
                                                    from EMPLOYEE as E,GRADES as G,DESIGNATION D,SALARYMONTHLY as S,SALARYMONTHLYDETAIL as Sd
                                                    where S.SalaryMonthlyID=Sd.SalaryMonthlyID 
                                                    AND S.SalaryMonth in(%q)
                                                    AND G.GRADEID=S.GRADEID 
                                                    AND D.DESIGNATIONID=S.DESIGNATIONID 
                                                    AND E.EMPLOYEEID =S.EMPLOYEEID 
                                                    AND Sd.ItemGroup IN (3) AND Sd.ItemCode IN(-128) 
                                                    AND S.EmployeeID IN(%q) order by S.SalaryMonth,E.EMPLOYEENO,G.SequenceNo,G.Code ", _months, sEmpID);

                tempdataset = tc.ExecuteDataSet(query8);
                tempdataset.Tables[0].TableName = "CPF";

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

        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' as Description,IsNull(sum(TranAmount),0) as PFAmount
                                                    from PFTransaction PFT where TranDate < %d
                                                    And TranType in(%n) and EmployeeID in (%q)) A
                                                    ,
                                                    (
                                                    Select 'Opening' as Description, IsNull(sum(TranAmount),0) as 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' as 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 as 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) as 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 as Description,TranAmount as PFAmount,TranAmount as 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 DataSet PrintEnvelop(TransactionContext tc, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string query1 = SQLParser.MakeSQL(@"Select Distinct E.EmployeeNo,E.Name,D.Name as Designation,DE.DESCRIPTION as Department 
                                                    from Employee E,Designation D,DEPARTMENT DE
                                                    where E.DesignationID= D.DesignationID and DE.DepartmentID = DE.DEPARTMENTID and E.EmployeeID in(%q)
                                                    order by E.EmployeeNo", sEmpID);

                tempdataset = tc.ExecuteDataSet(query1);

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

        internal static DataSet GetSalaryData(TransactionContext tc, string sEmpID, DateTime fromMonth, DateTime toMonth)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string query1 = SQLParser.MakeSQL(@"select sum(changedAmount) as Amount,ItemGroup, ItemCode, ItemID from salaryMonthly, salaryMonthlyDetail where salaryMonthly.salaryMonthlyid= salaryMonthlyDetail.salarymonthlyid"
                                        + " and salarymonth between %d and %d AND EmployeeID IN(%q) group by ItemGroup, ItemCode, ItemiD ",
                                            fromMonth, toMonth, sEmpID);

                tempdataset = tc.ExecuteDataSet(query1);

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

                string query2 = SQLParser.MakeSQL(@"select sum(changedAmount) as Amount,ItemGroup, ItemCode, ItemID from salaryMonthly, salaryMonthlyDetail where salaryMonthly.salaryMonthlyid= salaryMonthlyDetail.salarymonthlyid"
                                       + " and salarymonth between %d and %d AND EmployeeID IN(%q) AND ItemCode=-129 AND   ItemID=-129     group by ItemGroup, ItemCode, ItemiD ",
                                           fromMonth, toMonth, sEmpID);

                tempdataset = tc.ExecuteDataSet(query2);

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

                string query6 = SQLParser.MakeSQL(@"select sum(changedAmount) as Amount,ItemGroup, ItemCode, ItemID from salaryMonthly, salaryMonthlyDetail where salaryMonthly.salaryMonthlyid= salaryMonthlyDetail.salarymonthlyid"
                                       + " and salarymonth between %d and %d AND EmployeeID IN(%q) AND ItemCode=-129 AND    ItemID=0   group by ItemGroup, ItemCode, ItemiD ",
                                           fromMonth, toMonth, sEmpID);

                tempdataset = tc.ExecuteDataSet(query6);

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

                string query3 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate, 
                                'Bonus' as Description, Sum(A.Amount) as amount,Sum(A.taxamount) as taxamount,A.BonusID  
                                from( select B.EmployeeID, Sum(B.BonusAmount) as Amount,SUM(taxamount) as taxamount,B.BonusID 
                                from  BONUSPROCESSDETAIL B 
                                where B.DisburseDate between %d and %d 
                                AND B.EmployeeID IN(%q)  
                                group by B.EmployeeID,B.BonusID ) A, Employee E 
                                where A.EmployeeID = E.EmployeeID group by E.EmployeeNo, E.Name,
                                A.BonusID,E.JoiningDate 
                                order by E.EmployeeNo,A.BonusID", fromMonth, toMonth, sEmpID);

                tempdataset = tc.ExecuteDataSet(query3);

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

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

        internal static DataSet GetSalarySheet(TransactionContext tc, string sEmpID, DateTime _SalaryMonth, DateTime toDate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string query1 = SQLParser.MakeSQL(@"select  A.EmployeeNo, A.Name,A.GName,A.DName,A.GCode,A.JoiningDate,A.SequenceNo, A.Description,A.ItemGroup,A.ItemCode,A.ItemID, 
                                            SUM(A.changedamount) Amount from(select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate, sd.ItemGroup,sd.ItemCode,  
                                            sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,G.SequenceNo 
                                            from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                            where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                            AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth between %d and %d and Sd.ItemGroup in(8) 
                                            AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q)  
                                            UNION  select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate, sd.ItemGroup,sd.ItemCode,  sd.ItemID, 
                                            sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,
                                            G.SequenceNo from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd  
                                            where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID 
                                            AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth between %d and %d and Sd.ItemGroup in(1) 
                                            AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q)  ) A 
                                            group by  A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.DName,A.SequenceNo,A.GCode, A.ItemCode, A.ItemID, 
                                            A.Description,A.Position,A.JoiningDate,A.ItemGroup,A.ItemCode,A.ItemID order by A.EmployeeNo,A.SequenceNo,A.GCode",
                                            _SalaryMonth, toDate, sEmpID, _SalaryMonth, toDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(query1);

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

                string query2 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName, 
                                'IncomeTax' as Description, Sum(A.amount) as amount  
                                from( select S.EmployeeID, Sum(sd.changedamount) as Amount,
                                G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName  
                                from GRADES G,DESIGNATION D,SALARYMONTHLY S,SalaryMonthlyDetail Sd  
                                where S.SalaryMonthlyID=Sd.SalaryMonthlyID  AND S.SalaryMonth between %d and %d 
                                AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID 
                                and Sd.ItemGroup IN (2,3) AND S.EmployeeID IN(%q)  
                                group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name ) A, Employee E 
                                where A.EmployeeID = E.EmployeeID group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,
                                A.GCode,A.DName,E.JoiningDate 
                                order by E.EmployeeNo,A.sequenceno,A.GCode", _SalaryMonth, toDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(query2);

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

                string query3 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate, 
                                'Bonus' as Description, Sum(A.Amount) as amount,A.BonusID  
                                from( select B.EmployeeID, Sum(B.BonusAmount) as Amount,B.BonusID 
                                from  BONUSPROCESSDETAIL B 
                                where B.DisburseDate between %d and %d 
                                AND B.EmployeeID IN(%q)  
                                group by B.EmployeeID,B.BonusID ) A, Employee E 
                                where A.EmployeeID = E.EmployeeID group by E.EmployeeNo, E.Name,
                                A.BonusID,E.JoiningDate 
                                order by E.EmployeeNo,A.BonusID", _SalaryMonth, toDate, sEmpID);

                tempdataset = tc.ExecuteDataSet(query3);

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

        internal static DataSet GetEmpsFiscalYeartax(TransactionContext tc, string sEmpIDs, DateTime fromdate, DateTime toDate, int taxParamID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string sSQuery = SQLParser.MakeSQL(@"Select E.EMPLOYEEID,sm.SalaryMonth as Fiscalmonth,smd.DESCRIPTION,smd.CHANGEDAMOUNT as Amount
                                                    from dbo.SALARYMONTHLY sm
                                                    inner join Employee E on E.EMPLOYEEID = sm.EmployeeId
                                                    inner join dbo.SALARYMONTHLYDETAIL smd on sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                    where sm.SalaryMonth between %d and %d
                                                    and sm.EMPLOYEEID in(%q) and ItemGroup = 3 And ItemCode =-129
                                                    order by sm.EmployeeID,sm.SalaryMonth", fromdate, toDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sSQuery);

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

                string sBonustax = SQLParser.MakeSQL(@"Select bpd.EmployeeID ,bpd.Disbursedate as Fiscalmonth,'Bonus tax' as description,bpd.taxamount as Amount
                                                    from dbo.BONUSPROCESSDETAIL bpd 
                                                    where bpd.DisburseDate between %d and %d
                                                    and bpd.EmployeeID in(%q) 
                                                    order by bpd.Employeeid,bpd.DisburseDate", fromdate, toDate, sEmpIDs);

                tempdataset = tc.ExecuteDataSet(sBonustax);

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

                string sSettlementTax = SQLParser.MakeSQL(@"select fs.EmployeeID,E.ENDOFCONTRACTDATE as Fiscalmonth,'Settlement Tax' as Description,
                                                    fs.TaxAmount as Amount
                                                    from dbo.FSTran fs
                                                    inner join Employee E on fs.EmployeeiD = E.EMPLOYEEID
                                                    where E.ENDOFCONTRACTDATE between %d and %d
                                                    and fs.EmployeeID in(%q)
                                                    order by fs.Employeeid", fromdate, toDate, sEmpIDs);

                tempdataset = tc.ExecuteDataSet(sSettlementTax);

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

                if (taxParamID == 0)
                {
                    string sDeductedTax = SQLParser.MakeSQL(@"Select EmployeeID,ModifiedBy as FiscalMonth,'Tax Deducted' as Description,ThisYearTotal as Amount
                                                    from INCOMETAXTEMP where Employeeid in(%q) and ItemCode=%n and ItemID=%n
                                                    order by EmployeeID", sEmpIDs, EnumIncomeTaxItemGroup.Tax_Deducted,
                                                    EnumIncomeTaxItemGroup.Tax_Deducted);

                    tempdataset = tc.ExecuteDataSet(sDeductedTax);

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

                    string sIncreaseGrosstax = SQLParser.MakeSQL(@"Select EmployeeID,ModifiedBy as FiscalMonth,'Increase Gross Tax' as Description,ThisYearTotal as Amount
                                                    from INCOMETAXTEMP where Employeeid in(%q) and ItemCode=%n and ItemID=%n
                                                    order by EmployeeID", sEmpIDs, EnumIncomeTaxItemGroup.Tax_Gross_OtherAddable, EnumAllowOrDeduct.Allowance);

                    tempdataset = tc.ExecuteDataSet(sIncreaseGrosstax);

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

                    string sDecreaseGrossTax = SQLParser.MakeSQL(@"Select EmployeeID,ModifiedBy as FiscalMonth,'Decrease Gross Tax' as Description,ThisYearTotal as Amount
                                                    from INCOMETAXTEMP where Employeeid in(%q) and ItemCode=%n and ItemID=%n
                                                    order by EmployeeID", sEmpIDs, EnumIncomeTaxItemGroup.Tax_Gross_OtherRebate, EnumAllowOrDeduct.Deduction);

                    tempdataset = tc.ExecuteDataSet(sDecreaseGrossTax);

                    tempdataset.Tables[0].TableName = "TaxDecreaseGross";
                    rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
                }
                else if (taxParamID > 0)
                {
                    string sDeductedTax = SQLParser.MakeSQL(@"Select EmployeeID,ModifiedBy as FiscalMonth,'Tax Deducted' as Description,TotalAmount as Amount
                                                    from INCOMETAXYEARLY where Employeeid in(%q) and ItemCode=%n and ItemID=%n
                                                    and TaxParamID=%n
                                                    order by EmployeeID", sEmpIDs, EnumIncomeTaxItemGroup.Tax_Deducted,
                                                    EnumIncomeTaxItemGroup.Tax_Deducted, taxParamID);

                    tempdataset = tc.ExecuteDataSet(sDeductedTax);

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

                    string sIncreaseGrosstax = SQLParser.MakeSQL(@"Select EmployeeID,ModifiedBy as FiscalMonth,'Increase Gross Tax' as Description,TotalAmount as Amount
                                                    from INCOMETAXYEARLY where Employeeid in(%q) and ItemCode=%n and ItemID=%n
                                                    and TaxParamID=%n
                                                    order by EmployeeID", sEmpIDs, EnumIncomeTaxItemGroup.Tax_Gross_OtherAddable,
                                                    EnumAllowOrDeduct.Allowance, taxParamID);

                    tempdataset = tc.ExecuteDataSet(sIncreaseGrosstax);

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

                    string sDecreaseGrossTax = SQLParser.MakeSQL(@"Select EmployeeID,ModifiedBy as FiscalMonth,'Decrease Gross Tax' as Description,TotalAmount as Amount
                                                    from INCOMETAXYEARLY where Employeeid in(%q) and ItemCode=%n and ItemID=%n
                                                    and TaxParamID=%n
                                                    order by EmployeeID", sEmpIDs, EnumIncomeTaxItemGroup.Tax_Gross_OtherRebate,
                                                    EnumAllowOrDeduct.Deduction, taxParamID);

                    tempdataset = tc.ExecuteDataSet(sDecreaseGrossTax);

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

        internal static DataSet GetEmpstaxInvestment(TransactionContext tc, string sEmpIDs, int taxParamID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            string sItemCode = Convert.ToString((int)EnumIncomeTaxItemGroup.Company_Contri_PF) + "," + Convert.ToString((int)EnumIncomeTaxItemGroup.Annual_Salary_Income) + "," + Convert.ToString((int)EnumIncomeTaxItemGroup.Investment_Allowed);
            try
            {
                string sQuery = string.Empty;

                if (taxParamID == 0)
                {
                    sQuery = SQLParser.MakeSQL(@"Select EmployeeID,
					                                                sum(case When ItemCode = %n Then ThisYearTotal
					                                                End)'Provident Fund',
					                                                sum(case When ItemCode = %n Then ThisYearTotal
					                                                End)'Salary Income',
					                                                sum(case When ItemCode = %n Then ThisYearTotal
					                                                End)'Investment Required'    
                                                from Incometaxtemp 
                                                Where EmployeeID in(%q)
                                                group by EmployeeID
                                                order by EmployeeID", (int)EnumIncomeTaxItemGroup.Company_Contri_PF,
                    (int)EnumIncomeTaxItemGroup.Annual_Salary_Income, (int)EnumIncomeTaxItemGroup.Investment_Allowed, sEmpIDs);
                }
                else if (taxParamID > 0)
                {
                    sQuery = SQLParser.MakeSQL(@"Select EmployeeID,
					                                                sum(case When ItemCode = %n Then ThisYearTotal
					                                                End)'Provident Fund',
					                                                sum(case When ItemCode = %n Then ThisYearTotal
					                                                End)'Salary Income',
					                                                sum(case When ItemCode = %n Then ThisYearTotal
					                                                End)'Investment Required' 
                                                        from IncometaxYearly 
                                                        Where EmployeeID in(%q)
                                                        group by EmployeeID
                                                        order by EmployeeID", (int)EnumIncomeTaxItemGroup.Company_Contri_PF,
                    (int)EnumIncomeTaxItemGroup.Annual_Salary_Income, (int)EnumIncomeTaxItemGroup.Investment_Allowed, sEmpIDs);
                }
                tempdataset = tc.ExecuteDataSet(sQuery);

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

        internal static DataSet GetEmpstaxChallan(TransactionContext tc, string sEmpIDs, int taxParamId)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sTaxChallan = SQLParser.MakeSQL(@"Select E.EmployeeNo,E.Name,D.Name as Designation,tc.ChallenNo,tc.DepositDate,tc.Amount
                                                        from TaxChallan tc
                                                        inner join Employee E on tc.EmployeeID = E.EmployeeiD
                                                        inner join Designation D on E.DesignationID = D.DesignationID
                                                        where tc.taxparamid=%n
                                                        and tc.EmployeeiD in(%q)
                                                        order by tc.EmployeeiD,tc.DepositDate,tc.ChallenNo", taxParamId, sEmpIDs);

                tempdataset = tc.ExecuteDataSet(sTaxChallan);

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

        internal static DataSet GetEmployeesMasterData(TransactionContext tc, DateTime Fromdate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                //Previous
//                string sSQLGrossItem = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,d.ParentID,
//                                        des.NAME as Designation,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
//                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, smd.Description, 
//                                        SUM(smd.changedamount) Amount,'Salaries & Allowances(Taxable)' as GroupDescription,smd.POSITION
//                                        from Employee E
//                                        inner join Grades G on E.GradeID = G.GradeID
//                                        inner join Department d on E.DepartmentID = d.DepartmentID
//                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
//                                        inner join Designation des on E.DesignationID = des.DesignationID
//                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
//                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
//                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
//                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
//                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
//                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
//                                        and smd.ItemGroup in(1,8)
//                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
//                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name, smd.Description,smd.Position,d.ParentID
//                                        ORDER BY P.CODE", Fromdate, sEmpID);
                //new
                string sSQLGrossItem = SQLParser.MakeSQL(@"SELECT * from (Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,d.ParentID,
                                                        des.NAME as Designation,
                                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, smd.Description, 
                                                        SUM(smd.changedamount) Amount,'Salaries & Allowances(Taxable)' as GroupDescription,smd.POSITION
                                                        from Employee E
                                                        inner join Grades G on E.GradeID = G.GradeID
                                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                                        inner join Designation des on E.DesignationID = des.DesignationID
                                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                                        and smd.ItemGroup in(1,8)
                                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
                                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name, smd.Description,smd.Position,d.ParentID
                                                        UNION
                                                        Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,d.ParentID,
                                                        des.NAME as Designation,
                                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, CASE  WHEN smd.ITEMID=-128 THEN  'Provident Fund' ELSE  smd.[DESCRIPTION]
                                                        END  [DESCRIPTION], 
                                                        SUM(smd.changedamount) Amount,'Salaries & Allowances(Taxable)' as GroupDescription,smd.POSITION
                                                        from Employee E
                                                        inner join Grades G on E.GradeID = G.GradeID
                                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                                        inner join Designation des on E.DesignationID = des.DesignationID
                                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                                        and smd.ITEMID IN (-128)
                                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
                                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name, smd.Description,smd.Position,d.ParentID, smd.ITEMID) Tab1 
                                                        ", Fromdate, sEmpID, Fromdate, sEmpID);


                tempdataset = tc.ExecuteDataSet(sSQLGrossItem);

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

                //Previous
//                string sTotalGross = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
//                                        des.NAME as Designation,d.ParentID,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
//                                        Max(smd.Position)as Position,E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,
//                                        'Total Gross'as Description, SUM(smd.changedamount) Amount,'Salaries & Allowances(Taxable)' as GroupDescription
//                                        from Employee E
//                                        inner join Grades G on E.GradeID = G.GradeID
//                                        inner join Department d on E.DepartmentID = d.DepartmentID
//                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
//                                        inner join Designation des on E.DesignationID = des.DesignationID
//                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
//                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
//                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
//                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
//                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
//                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
//                                        and smd.ItemGroup in(1,8)
//                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
//                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID ORDER BY P.CODE", Fromdate, sEmpID);

                //New
                string sTotalGross = SQLParser.MakeSQL(@"SELECT tab1.EMPLOYEENO, tab1.[NAME], tab1.Unit,tab1.Grade, tab1.Department, tab1.Designation, tab1.ParentID, tab1.BIRTHDATE,
                                                tab1.JOININGDATE, tab1.DATEOFCONFIRMATION, tab1.GENDER, tab1.ENDOFCONTRACTDATE, tab1.EMAILADDRESS, tab1.PRESENTMOBILE, 
                                                tab1.EMERGENCYTELEPHONE, tab1.PARMANENTADDRESS, tab1.PRESENTADDRESS, tab1.FATHERNAME,tab1.MOTHERNAME,tab1.SpouseName,
                                                tab1.NationalID,tab1.ACCOUNTNO,tab1.NomineeName1,tab1.NomineeName2,
                                                tab1.Description, SUM(tab1.Amount) Amount,tab1.GroupDescription
                                                  From (Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
                                                des.NAME as Designation,d.ParentID,
                                                E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                                Max(smd.Position)as Position,E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,
                                                'Total Gross'as Description, SUM(smd.changedamount) Amount,'Salaries & Allowances(Taxable)' as GroupDescription
                                                from Employee E
                                                inner join Grades G on E.GradeID = G.GradeID
                                                inner join Department d on E.DepartmentID = d.DepartmentID
                                                LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                                inner join Designation des on E.DesignationID = des.DesignationID
                                                left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                                left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                                left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                                inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                                inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                                where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                                and smd.ItemGroup in(1,8)
                                                group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
                                                E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                                E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID 
                                                UNION
                                                Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
                                                des.NAME as Designation,d.ParentID,
                                                E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                                Max(smd.Position)as Position,E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,
                                                'Total Gross'as Description, SUM(smd.changedamount) Amount,'Salaries & Allowances(Taxable)' as GroupDescription
                                                from Employee E
                                                inner join Grades G on E.GradeID = G.GradeID
                                                inner join Department d on E.DepartmentID = d.DepartmentID
                                                LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                                inner join Designation des on E.DesignationID = des.DesignationID
                                                left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                                left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                                left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                                inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                                inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                                where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                                and smd.ITEMID in(-128)
                                                group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
                                                E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                                E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID) Tab1 
                                                GROUP BY tab1.EMPLOYEENO, tab1.[NAME], tab1.Unit,tab1.Grade, tab1.Department, tab1.Designation, tab1.ParentID, tab1.BIRTHDATE,
                                                tab1.JOININGDATE, tab1.DATEOFCONFIRMATION, tab1.GENDER, tab1.ENDOFCONTRACTDATE, tab1.EMAILADDRESS, tab1.PRESENTMOBILE, 
                                                tab1.EMERGENCYTELEPHONE, tab1.PARMANENTADDRESS, tab1.PRESENTADDRESS, tab1.FATHERNAME,tab1.MOTHERNAME,tab1.SpouseName,
                                                tab1.NationalID,tab1.ACCOUNTNO,tab1.NomineeName1,tab1.NomineeName2,
                                                tab1.Description, tab1.GroupDescription
                                                ", Fromdate, sEmpID, Fromdate, sEmpID);


                tempdataset = tc.ExecuteDataSet(sTotalGross);

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

                string sOPIItem = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME, p.Code as Unit, G.Code as Grade,d.DESCRIPTION as Department,
                                        des.NAME as Designation,d.ParentID,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, opdi.Description,
                                        opdi.ChangeNetAmount as Amount,'Salaries Expenses' as GroupDescription
                                        from Employee E
                                        inner join Grades G on E.GradeID = G.GradeID
                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                        inner join Designation des on E.DesignationID = des.DesignationID
                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                        inner join OPIProcessDetail opd on E.EMPLOYEEID = opd.EmployeeID
                                        inner join OPIProcess op on op.OPIProcessID = opd.OPIProcessID
                                        inner join OPIProcessDetailItem opdi on opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                        where op.OPIMonth=%d and opd.EmployeeID in(%q)
                                        group by E.EMPLOYEENO,E.NAME, P.Code,G.Code,d.DESCRIPTION, des.NAME,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,opdi.Description,opdi.ChangeNetAmount,d.ParentID ORDER BY p.CODE ", Fromdate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sOPIItem);

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

                string sTotalOPI = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,p.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
                                        des.NAME as Designation,d.ParentID,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, 
                                        'Total OPI' as Description,Sum(opdi.ChangeNetAmount) as Amount,'Salaries Expenses' as GroupDescription
                                        from Employee E
                                        inner join Grades G on E.GradeID = G.GradeID
                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                        inner join Designation des on E.DesignationID = des.DesignationID
                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                        inner join OPIProcessDetail opd on E.EMPLOYEEID = opd.EmployeeID
                                        inner join OPIProcess op on op.OPIProcessID = opd.OPIProcessID
                                        inner join OPIProcessDetailItem opdi on opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                        where op.OPIMonth=%d and opd.EmployeeID in(%q)
                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION,des.NAME,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,opdi.Description,d.ParentID ORDER BY p.CODE", Fromdate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sTotalOPI);

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

                //Previous
//                string sDeductedItem = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,p.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
//                                        des.NAME as Designation,d.ParentID,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
//                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,smd.Description, 
//                                        smd.changedamount as Amount,'Deduction from Salary' as GroupDescription,smd.POSITION
//                                        from Employee E
//                                        inner join Grades G on E.GradeID = G.GradeID
//                                        inner join Department d on E.DepartmentID = d.DepartmentID
//                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
//                                        inner join Designation des on E.DesignationID = des.DesignationID
//                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
//                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
//                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
//                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
//                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
//                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
//                                        and smd.ItemGroup in(3)
//                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION,des.NAME,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
//                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,smd.Description, smd.changedamount,smd.POSITION,d.ParentID
//                                        ORDER BY P.CODE ", Fromdate, sEmpID);

                //New
                string sDeductedItem = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,p.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
                                                        des.NAME as Designation,d.ParentID,
                                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,CASE  WHEN  smd.ITEMID=-128 THEN  'Provident Fund-Both Contribution' ELSE  smd.[DESCRIPTION]
                                                        END  [DESCRIPTION], 
                                                        CASE  WHEN  smd.ITEMID=-128 THEN  SUM(smd.ChangedAmount*2) ELSE  SUM(smd.ChangedAmount) END  Amount,'Deduction from Salary' as GroupDescription,smd.POSITION
                                                        from Employee E
                                                        inner join Grades G on E.GradeID = G.GradeID
                                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                                        inner join Designation des on E.DesignationID = des.DesignationID
                                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                                        and smd.ItemGroup in(3)
                                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION,des.NAME,
                                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,smd.Description, smd.changedamount,smd.POSITION,d.ParentID, smd.ITEMID
                                                        ORDER BY P.CODE 
                                                        ", Fromdate, sEmpID);


                tempdataset = tc.ExecuteDataSet(sDeductedItem);

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

                //Pervious
//                string sTotalDeduction = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
//                                        des.NAME as Designation,d.ParentID,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
//                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,'Total Deduction' as Description, 
//                                        Sum(smd.changedamount) as Amount,'Deduction from Salary' as GroupDescription
//                                        from Employee E
//                                        inner join Grades G on E.GradeID = G.GradeID
//                                        inner join Department d on E.DepartmentID = d.DepartmentID
//                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
//                                        inner join Designation des on E.DesignationID = des.DesignationID
//                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
//                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
//                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
//                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
//                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
//                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
//                                        and smd.ItemGroup in(3)
//                                        group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
//                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
//                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
//                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID
//                                        ORDER BY P.CODE", Fromdate, sEmpID);

                string sTotalDeduction = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,P.Code as Unit,G.Code as Grade,d.DESCRIPTION as Department,
                                                            des.NAME as Designation,d.ParentID,
                                                            E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                            EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                                            E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,'Total Deduction' as Description, 
                                                            CASE  WHEN  smd.ITEMID=-128 THEN  SUM(smd.ChangedAmount*2) ELSE  SUM(smd.ChangedAmount) END  Amount,'Deduction from Salary' as GroupDescription
                                                            from Employee E
                                                            inner join Grades G on E.GradeID = G.GradeID
                                                            inner join Department d on E.DepartmentID = d.DepartmentID
                                                            LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                                            inner join Designation des on E.DesignationID = des.DesignationID
                                                            left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                                            left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                                            left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                                            inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                                            inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                                            where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                                            and smd.ItemGroup in(3)
                                                            group by E.EMPLOYEENO,E.NAME,P.Code,G.Code,d.DESCRIPTION, des.NAME,
                                                            E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                                            EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                                            E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID, smd.ITEMID
                                                            ORDER BY P.CODE", Fromdate, sEmpID);


                tempdataset = tc.ExecuteDataSet(sTotalDeduction);

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

                string sNetPay = SQLParser.MakeSQL(@"Select tbGross.*,IsNull((IsNull(tbOPI.Amount,0)+tbGross.PreAmount - tbDeduction.Amount),0)as Amount from (
                                        Select E.EMPLOYEENO,E.NAME,P.Code as Unit, G.Code as Grade,d.DESCRIPTION as Department,
                                        des.NAME as Designation,d.ParentID,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,Max(smd.Position)as Position,
                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, 'Net Salary'as Description, 
                                        SUM(smd.changedamount) PreAmount,'' as GroupDescription
                                        from Employee E
                                        inner join Grades G on E.GradeID = G.GradeID
                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                        inner join Designation des on E.DesignationID = des.DesignationID
                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                        and smd.ItemGroup in(1,8)
                                        group by E.EMPLOYEENO,E.NAME,P.Code, G.Code,d.DESCRIPTION,des.NAME,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID)tbGross
                                        left join 
                                        (
                                        Select E.EMPLOYEENO,E.NAME,P.Code as Unit, G.Code as Grade,d.DESCRIPTION as Department,
                                        des.NAME as Designation,d.ParentID,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2, 'Total OPI' as Description,
                                        Sum(opdi.ChangeNetAmount) as Amount,'' as GroupDescription
                                        from Employee E
                                        inner join Grades G on E.GradeID = G.GradeID
                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                        inner join Designation des on E.DesignationID = des.DesignationID
                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                        inner join OPIProcessDetail opd on E.EMPLOYEEID = opd.EmployeeID
                                        inner join OPIProcess op on op.OPIProcessID = opd.OPIProcessID
                                        inner join OPIProcessDetailItem opdi on opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                        where op.OPIMonth=%d and opd.EmployeeID in(%q)
                                        group by E.EMPLOYEENO,E.NAME, P.Code,G.Code,d.DESCRIPTION,des.NAME,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,opdi.Description,d.ParentID)tbOPI on tbGross.EmployeeNo = tbOPI.EmployeeNo
                                        left join
                                        (
                                        Select E.EMPLOYEENO,E.NAME,P.Code as Unit, G.Code as Grade,d.DESCRIPTION as Department,
                                        des.NAME as Designation,d.ParentID,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME as SpouseName,
                                        E.NationalID,E.ACCOUNTNO,EN.NAME as NomineeName1,EN.Name as NomineeName2,'Total Deduction' as Description, 
                                        Sum(smd.changedamount) as Amount,'' as GroupDescription
                                        from Employee E
                                        inner join Grades G on E.GradeID = G.GradeID
                                        inner join Department d on E.DepartmentID = d.DepartmentID
                                        LEFT JOIN Department p ON p.DEPARTMENTID = ISNULL(d.PARENTID,d.DEPARTMENTID)
                                        inner join Designation des on E.DesignationID = des.DesignationID
                                        left Outer join EMPCONTACT EC on E.EMPLOYEEID = EC.EMPLOYEEID
                                        left Outer join EMPSPOUSE ES on E.EMPLOYEEID = ES.EMPLOYEEID
                                        left Outer Join EMPNOMINEE EN on E.EMPLOYEEID = EN.EMPLOYEEID
                                        inner join SalaryMonthly sm on E.EMPLOYEEID = sm.EmployeeID
                                        inner join SalaryMonthlyDetail smd on smd.SalaryMonthlyID = sm.SalaryMonthlyID
                                        where sm.SalaryMonth=%d and sm.EmployeeID in(%q)
                                        and smd.ItemGroup in(3)
                                        group by E.EMPLOYEENO,E.NAME,P.Code, G.Code,d.DESCRIPTION,des.NAME,
                                        E.BIRTHDATE,E.JOININGDATE,E.DATEOFCONFIRMATION,E.GENDER,E.ENDOFCONTRACTDATE,E.EMAILADDRESS,EC.PRESENTMOBILE,
                                        EC.EMERGENCYTELEPHONE,EC.PARMANENTADDRESS,EC.PRESENTADDRESS,E.FATHERNAME,E.MOTHERNAME,ES.NAME,
                                        E.NationalID,E.ACCOUNTNO,EN.Name,EN.Name,d.ParentID) tbDeduction on tbGross.EMPLOYEENO = tbDeduction.EMPLOYEENO
                                        ", Fromdate, sEmpID, Fromdate, sEmpID, Fromdate, sEmpID);

                tempdataset = tc.ExecuteDataSet(sNetPay);

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

        internal static DataSet GetEmployeesPF(TransactionContext tc, DateTime dateTime, string sUnitID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLPF = SQLParser.MakeSQL(@"Select distinct tb1.Department,tb1.EPF,tb1.CPF,tb1.ParentID,IsNull(tb3.PFLoan,0)PFLoan,
                                                IsNull(tb3.PFLoanInterest,0)PFLoanInterest,tb2.Description as Unit from (
                                                SELECT Max(E.EMPLOYEEID)EmployeeID, 
                                                Sum(Case WHEN   pft.TranType= %n  THEN pft.TRANAMOUNT ELSE 0 END)as EPF,
                                                Sum(Case WHEN   pft.TranType= %n  THEN pft.TRANAMOUNT ELSE 0 END)as CPF,
                                                D.DESCRIPTION as Department,D.ParentID
                                                from Department D
                                                Left Outer join Employee E on E.DepartmentID = D.DepartmentID
                                                inner join PFTransaction pft on e.EmployeeID = pft.EmployeeiD
                                                where pft.TranDate = %d 
                                                group by D.DESCRIPTION,D.ParentID)tb1
                                                left JOIN
                                                (
                                                SELECT Max(sm.EMPLOYEEID)EmployeeID,
                                                SUM(CASE WHEN   smd.ITEMCODE = %n AND smd.ITEMGROUP=%n AND smd.ITEMID = 1 THEN smd.CHANGEDAMOUNT ELSE 0 END) AS PFLoan,
                                                SUM(CASE WHEN smd.ITEMCODE=%n AND smd.ITEMGROUP=%n AND smd.ITEMID=1 THEN smd.CHANGEDAMOUNT ELSE 0 END) AS PFLoanInterest,
                                                D.DESCRIPTION as Department,D.ParentID
	                                            from  SalaryMonthly sm 
	                                            Left Outer join Department D on D.DepartmentID = sm.DepartmentID
	                                            inner join Employee e on e.EmployeeID = sm.EmployeeiD
	                                            INNER JOIN SALARYMONTHLYDETAIL smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
	                                            where sm.SalaryMonth = %d 
	                                            group by D.DESCRIPTION,D.ParentID
                                                 )AS tb3 On tb1.Department=tb3.Department AND tb1.ParentID = tb3.ParentID
                                                inner join (
                                                Select DepartmentID,Code,Description from Department
                                                where ParentID is Null)tb2 on tb1.ParentID=tb2.DepartmentID
                                                where tb1.ParentID in(%q)",
                                                (int)EnumPFTranType.PFAmount, (int)EnumPFTranType.CPFAmount, dateTime, (int)EnumSalaryItemCode.Loan_Monthly_Installment,
                                                (int)EnumSalaryGroup.Deductions, (int)EnumSalaryItemCode.Loan_Monthly_Interest, (int)EnumSalaryGroup.Deductions, dateTime,
                                                 sUnitID);


                tempdataset = tc.ExecuteDataSet(sSQLPF);

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

        internal static DataSet GetStuffListWithoutSalary(TransactionContext tc, DateTime fromdate, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLStuffList = SQLParser.MakeSQL(@"SELECT E.EmployeeNo,E.[NAME],(D.[NAME])AS Designation,
                                                (De.[DESCRIPTION])AS Department,E.ACCOUNTNO,E.JOININGDATE,CC.DESCRIPTION AS CostCenter
                                                FROM EMPLOYEE E
                                                left JOIN Designation D ON E.DESIGNATIONID = D.DESIGNATIONID
                                                LEFT JOIN Department De ON E.DEPARTMENTID = De.DEPARTMENTID
                                                Left JOIN EMPCOSTCENTER empcc ON E.EMPLOYEEID = empcc.EMPLOYEEID
                                                INNER JOIN CRG CC ON CC.CRGID = empcc.COSTCENTERID
                                                WHERE  e.EMPLOYEEID  IN(%q) AND empcc.CurrentCC=1", sEmpID);

                tempdataset = tc.ExecuteDataSet(sSQLStuffList);

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

        internal static DataSet GetEmployeesPF(TransactionContext tc, DateTime dateTime)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sTranType = Convert.ToString((int)EnumPFTranType.PFAmount) + "," + Convert.ToString((int)EnumPFTranType.CPFAmount);

            try
            {
                string sSQLMonthlyPF = SQLParser.MakeSQL(@"SELECT t.EmployeeNo, t.[Name], t.THISMONTHBASIC, t.TotalGross, SUM(t.EPF) EPF, 
                                                            SUM(t.CPF) CPF,t.CostCenter,t.Designation,t.Department,t.JOININGDATE
                                                            FROM 
	                                                            (
	                                                              SELECT e.EmployeeNo,e.Name,e.JOININGDATE,D.NAME AS Designation,Dep.DESCRIPTION AS Department,
	                                                              ISNULL(sm.THISMONTHBASIC,0) THISMONTHBASIC,
	                                                              IsNull(sm.ThisMonthBasic,0) as TotalGross,
	                                                              CASE WHEN pt.TRANTYPE = %n THEN pt.TRANAMOUNT ELSE 0 END AS EPF,
	                                                              CASE WHEN pt.TRANTYPE = %n THEN pt.TRANAMOUNT ELSE 0 END AS CPF,
	                                                              CC.DESCRIPTION AS CostCenter
	                                                              FROM PFTRANSACTION pt 
	                                                              LEFT JOIN Employee e ON pt.EMPLOYEEID = e.EMPLOYEEID AND pt.TRANDATE = %d 
	                                                              LEFT JOIN DESIGNATION D ON e.DESIGNATIONID = D.DESIGNATIONID
	                                                              LEFT JOIN DEPARTMENT Dep ON e.DEPARTMENTID = Dep.DEPARTMENTID
	                                                              LEFT JOIN EMPCOSTCENTER cmpcc ON e.EMPLOYEEID=cmpcc.EMPLOYEEID AND cmpcc.CurrentCC=1
	                                                              INNER JOIN CRG CC ON cmpcc.CostCenterID = CC.CRGID 
	                                                              LEFT JOIN SALARYMONTHLY sm ON sm.EMPLOYEEID = e.EMPLOYEEID AND sm.SalaryMonth = %d 
	                                                            )t 
                                                            GROUP BY t.EmployeeNo, t.[Name], t.THISMONTHBASIC, t.TotalGross, t.CostCenter,
                                                            t.JOININGDATE,t.Designation,t.Department"
                                                            , (int)EnumPFTranType.PFAmount, (int)EnumPFTranType.CPFAmount, dateTime, dateTime);

                tempdataset = tc.ExecuteDataSet(sSQLMonthlyPF);

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

        internal static DataSet GetEmployeesPFNew(TransactionContext tc, DateTime Fromdate, DateTime Todate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sTranType = Convert.ToString((int)EnumPFTranType.PFAmount) + "," + Convert.ToString((int)EnumPFTranType.CPFAmount);
            try
            {
                //                string sqlPFexception = SQLParser.MakeSQL(@"SELECT t.[Name],t.EmployeeNo,t.JOININGDATE, t.Designation,t.Department, t.TotalGross AS GrossSalary,ROUND (SUM(t.EPF),0)as EPF, Round(SUM(t.CPF),0) AS CPF,  t.BIRTHDATE
                //                    FROM 
                //                    (
                //                    SELECT e.EmployeeNo,e.Name,e.JOININGDATE,e.BIRTHDATE, D.NAME AS Designation,Dep.DESCRIPTION AS Department,
                //                    ISNULL(sm.THISMONTHBASIC,0) THISMONTHBASIC,
                //                    IsNull(sm.ThisMonthBasic,0) as TotalGross,
                //                    CASE WHEN pt.TRANTYPE = %n THEN pt.TRANAMOUNT ELSE 0 END AS EPF,
                //                    CASE WHEN pt.TRANTYPE = %n THEN pt.TRANAMOUNT ELSE 0 END AS CPF,
                //
                //                    CC.DESCRIPTION AS CostCenter
                //                    FROM PFTRANSACTION pt 
                //                    LEFT JOIN Employee e ON pt.EMPLOYEEID = e.EMPLOYEEID AND pt.TRANDATE BETWEEN %d AND %d 
                //                    LEFT JOIN DESIGNATION D ON e.DESIGNATIONID = D.DESIGNATIONID
                //                    LEFT JOIN DEPARTMENT Dep ON e.DEPARTMENTID = Dep.DEPARTMENTID
                //                    LEFT JOIN EMPCOSTCENTER cmpcc ON e.EMPLOYEEID=cmpcc.EMPLOYEEID AND cmpcc.CurrentCC=1
                //                    INNER JOIN CRG CC ON cmpcc.CostCenterID = CC.CRGID 
                //                    LEFT JOIN SALARYMONTHLY sm ON sm.EMPLOYEEID = e.EMPLOYEEID AND sm.SalaryMonth = %d
                //                    Left JOIN SALARYMONTHLYDETAIL smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID AND smd.ITEMGROUP IN(1,8)
                //
                //                    )t 
                //                    GROUP BY t.EmployeeNo, t.[Name], t.THISMONTHBASIC, t.TotalGross, t.CostCenter,
                //                    t.JOININGDATE,t.Designation,t.Department,t.BirthDate", (int)EnumPFTranType.PFAmount, (int)EnumPFTranType.CPFAmount, Fromdate, Todate, Todate);


                string sqlPfException = SQLParser.MakeSQL(@" SELECT t.[Name],t.EmployeeNo,t.JOININGDATE, t.Designation,t.Department, t.TotalGross AS GrossSalary,ROUND (SUM(t.EPF),0)as EPF, Round(SUM(t.CPF),0) AS CPF,  t.BIRTHDATE
                            FROM
                            (SELECT e.EmployeeNo,e.Name,e.BIRTHDATE, e.JOININGDATE, D.NAME AS Designation,Dept.DESCRIPTION AS Department,
                            CASE WHEN pt.TRANTYPE = %n THEN pt.TRANAMOUNT ELSE 0 END AS EPF,
                            CASE WHEN pt.TRANTYPE = %n THEN pt.TRANAMOUNT ELSE 0 END AS CPF,
                            ISNULL(s.THISMONTHBASIC,0) THISMONTHBASIC,
                            IsNull(s.ThisMonthBasic,0) as TotalGross

                            FROM SALARYMONTHLY s
                            INNER JOIN EMPLOYEE e ON e.EMPLOYEEID= s.EMPLOYEEID
                            INNER JOIN DESIGNATION d ON d.DESIGNATIONID= e.DESIGNATIONID
                            INNER JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID
                            INNER JOIN EmpLifeCycle elc ON elc.EmployeeID=e.EMPLOYEEID
                            INNER JOIN EMPCOSTCENTER cmpcc ON e.EMPLOYEEID=cmpcc.EMPLOYEEID AND cmpcc.CurrentCC=1
                            INNER JOIN CRG CC ON cmpcc.CostCenterID = CC.CRGID 
                            INNER JOIN SALARYMONTHLYDETAIL smd ON s.SALARYMONTHLYID = smd.SALARYMONTHLYID AND smd.ITEMGROUP IN(1,8)
                            LEFT JOIN PFTRANSACTION pt ON pt.EMPLOYEEID=e.EMPLOYEEID 
                            WHERE s.SalaryMonth=%d AND elc.IsConfirm=1 AND elc.EffectDate BETWEEN %d AND %d)t
                            GROUP BY t.EmployeeNo, t.[Name], t.THISMONTHBASIC, t.TotalGross, t.JOININGDATE,t.Designation,t.Department,t.BirthDate", (int)EnumPFTranType.PFAmount, (int)EnumPFTranType.CPFAmount, Todate, Fromdate, Todate);

                tempdataset = tc.ExecuteDataSet(sqlPfException);

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

            return rootDataset;
        }

        internal static DataSet GetMonthlySalaryRevision(TransactionContext tc, DateTime Fromdate, DateTime Todate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string mSalaryRevision = SQLParser.MakeSQL(@"Select Emp.NAME, Emp.EMPLOYEENO, Emp.JOININGDATE,DES.[NAME] AS Designation,Dep.[DESCRIPTION] AS department,gsa.BASICSALARY Present,ISNULL(gsat.BASICSALARY,0) as Prev
                                                from GRADESALARYASSIGNMENT as GSA
                                                LEFT JOIN GRADESALARYASSIGNMENT as GSAt ON gsat.EMPLOYEEID=gsa.EMPLOYEEID AND gsat.GRADESALARYID=gsa.GRADESALARYID-1 AND gsat.BASICSALARY<>gsa.BASICSALARY
                                                INNER JOIN EMPLOYEE Emp On GSA.EMPLOYEEID=Emp.EMPLOYEEID 
                                                LEFT JOIN Designation DES On Emp.DESIGNATIONID = DES.DESIGNATIONID
                                                LEFT JOIN Department Dep ON Emp.DEPARTMENTID = Dep.DEPARTMENTID
                                                Where Emp.PayrollTypeId=1  AND GSA.EFFECTDATE  between %d and %d AND gsat.BASICSALARY>0 ORDER BY emp.JOININGDATE DESC", Fromdate, Todate);

                tempdataset = tc.ExecuteDataSet(mSalaryRevision);
                tempdataset.Tables[0].TableName = "MonthlyPF";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            return rootDataset;
        }

        internal static DataSet GetEmployeesPF(TransactionContext tc, DateTime Fromdate, DateTime Todate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sTranType = Convert.ToString((int)EnumPFTranType.PFAmount) + "," + Convert.ToString((int)EnumPFTranType.CPFAmount);

            try
            {
                string sSQLMonthlyPF = SQLParser.MakeSQL(@"SELECT t.EmployeeNo, t.[Name], t.THISMONTHBASIC, t.TotalGross, SUM(t.EPF)EPF, 
                                                       SUM(t.CPF)CPF,t.CostCenter,t.Designation,t.Department,t.JOININGDATE
                                        FROM (
                                        SELECT e.EmployeeNo,e.Name,e.JOININGDATE,D.NAME AS Designation,Dep.DESCRIPTION AS Department,
                                        ISNULL(sm.THISMONTHBASIC,0)THISMONTHBASIC,
                                        IsNull(SUM(smd.ChangedAmount),0)as TotalGross,
                                        CASE WHEN pt.TRANTYPE = %n  THEN pt.TRANAMOUNT ELSE 0 END AS EPF,
                                        CASE WHEN pt.TRANTYPE = %n  THEN pt.TRANAMOUNT ELSE 0 END AS CPF,
                                        CC.DESCRIPTION AS CostCenter
                                        FROM PFTRANSACTION pt 
                                        LEFT JOIN Employee e ON pt.EMPLOYEEID = e.EMPLOYEEID AND e.PFMEMBERSHIPDT BETWEEN %d AND %d 
                                        LEFT JOIN DESIGNATION D ON e.DESIGNATIONID = D.DESIGNATIONID
                                        LEFT JOIN DEPARTMENT Dep ON e.DEPARTMENTID = Dep.DEPARTMENTID
                                        LEFT JOIN EMPCOSTCENTER cmpcc ON e.EMPLOYEEID=cmpcc.EMPLOYEEID AND cmpcc.CurrentCC=1
                                        INNER JOIN CRG CC ON cmpcc.CostCenterID = CC.CRGID 
                                        LEFT JOIN SALARYMONTHLY sm ON sm.EMPLOYEEID = e.EMPLOYEEID AND sm.SalaryMonth = %d
                                        Left JOIN SALARYMONTHLYDETAIL smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID AND smd.ITEMGROUP IN(1,8)
                                        GROUP BY e.EMPLOYEENO,e.[NAME],ISNULL(sm.THISMONTHBASIC,0),CC.DESCRIPTION,pt.TRANTYPE,pt.TRANDATE,
                                        pt.TRANAMOUNT,e.JOININGDATE,D.NAME,Dep.DESCRIPTION
                                        )t 
                                        GROUP BY t.EmployeeNo, t.[Name], t.THISMONTHBASIC, t.TotalGross, t.CostCenter,
                                        t.JOININGDATE,t.Designation,t.Department", (int)EnumPFTranType.PFAmount, (int)EnumPFTranType.CPFAmount,
                                                                                         Fromdate, Todate, Todate);

                tempdataset = tc.ExecuteDataSet(sSQLMonthlyPF);

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

        internal static DataSet GetSalarySummaryData(TransactionContext tc, DateTime dateTime, int deptId)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
//                string sSQLSalaryGross = SQLParser.MakeSQL(@"SELECT smd.Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,P.CODE AS Unit
//                                                            FROM SALARYMONTHLYDETAIL smd
//                                                            left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
//                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
//                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
//                                                            WHERE sm.SalaryMonth = %d AND smd.ItemGroup IN(1,8) AND D.PARENTID=%n
//                                                            GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION, P.CODE
//                                                            ORDER BY D.[DESCRIPTION],smd.POSITION", dateTime, deptId);

                string sSQLSalaryGross = SQLParser.MakeSQL(@"Select * from (
                                                            SELECT smd.Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ItemGroup IN(8) AND smd.ITEMCODE NOT IN(-133) AND (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION, P.CODE
                                                            UNION
                                                            SELECT smd.Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ItemGroup IN(1) AND smd.ITEMCODE NOT IN(-133) AND (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION, P.CODE
                                                            UNION
                                                            SELECT Tab2.[Description], Tab2.Amount, Tab2.Department, Tab2.Unit from
                                                            (SELECT DISTINCT tab1.Department, SUM(tab1.Amount) Amount,  tab1.[Description], tab1.Unit 
                                                            from(SELECT CASE  WHEN  smd.ITEMID=-128 THEN  'Provident Fund' ELSE  smd.[DESCRIPTION]
                                                                END  [DESCRIPTION] ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,P.CODE AS Unit FROM SALARYMONTHLYDETAIL smd 
                                                            left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE smd.ITEMID=-128 AND (d.PARENTID=%n OR d.DEPARTMENTID=%n) AND sm.SalaryMonth = %d
                                                            GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION, smd.ItemID, P.CODE)Tab1                                               
                                                            GROUP BY tab1.[Description], tab1.Department, tab1.Unit) Tab2)Tab3
                                                            Order by Tab3.Department", dateTime, deptId, deptId, dateTime, deptId, deptId, deptId, deptId, dateTime);


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

//                string sSQLGrossTotal = SQLParser.MakeSQL(@"SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
//                                                            FROM SALARYMONTHLYDETAIL smd
//                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
//                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
//                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
//                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) and D.PARENTID=%n
//                                                            GROUP BY D.[DESCRIPTION], P.CODE
//                                                            ORDER BY D.[DESCRIPTION]", dateTime, deptId);

                string sSQLGrossTotal = SQLParser.MakeSQL(@"SELECT Tab2.[Description], Tab2.Amount, Tab2.Department, Tab2.Unit
                                                            from (SELECT DISTINCT Tab1.Department, tab1.Unit, Tab1.[Description], SUM(Tab1.Amount) Amount FROM
                                                            (SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE
                                                            UNION
                                                            SELECT 'Total Gross' AS Description ,-SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(2) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE
                                                            UNION
                                                            SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMID =-128 and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE) Tab1
                                                            GROUP BY Tab1.[Description], Tab1.Department, Tab1.Unit) Tab2 
                                                            ORDER BY Tab2.[DESCRIPTION]", dateTime, deptId, deptId, dateTime, deptId, deptId, dateTime, deptId, deptId);

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

                string sSQLOPIItems = SQLParser.MakeSQL(@"SELECT op.[Name] AS DESCRIPTION,Sum(opdi.ChangeNetAmount) AS Amount,D.[DESCRIPTION] AS Department,  P.CODE AS Unit
                                                        FROM OPIProcessDetailItem opdi
                                                        LEFT JOIN OpiItem op ON opdi.OPIItemID = op.OpiItemID
                                                        LEFT JOIN OPIProcessDetail opd ON opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                                        LEFT JOIN OPIProcess opp ON opd.OPIProcessID = opp.OPIProcessID
                                                        LEFT JOIN Department D ON opd.DepartmentID = D.DEPARTMENTID
                                                        LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                        WHERE opp.OPIMonth = %d and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                        GROUP BY op.[Name],D.[DESCRIPTION],op.SequenceNO, P.CODE
                                                        ORDER BY D.[DESCRIPTION],op.SequenceNO", dateTime, deptId, deptId);

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


                string sSQLUnAuthor = SQLParser.MakeSQL(@"SELECT 'Unauthorized' AS Description, Sum(tab1.Amount) Amount, tab1.Department, tab1.Unit
                                                        FROM  (SELECT smd.Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,P.CODE AS Unit
                                                        FROM SALARYMONTHLYDETAIL smd
                                                        left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                        LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                        LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                        WHERE sm.SalaryMonth = %d AND smd.ItemGroup IN(2) AND (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                        GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION, P.CODE) Tab1  GROUP BY tab1.Department, tab1.Unit", dateTime, deptId, deptId);

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


                string sSQLTotalOPI = SQLParser.MakeSQL(@"SELECT  'Total OPI' AS Description,SUM(opdi.ChangeNetAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM OPIProcessDetailItem opdi
                                                            LEFT JOIN OpiItem op ON opdi.OPIItemID = op.OpiItemID
                                                            LEFT JOIN OPIProcessDetail opd ON opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                                            LEFT JOIN OPIProcess opp ON opd.OPIProcessID = opp.OPIProcessID
                                                            LEFT JOIN Department D ON opd.DepartmentID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE opp.OPIMonth = %d and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE
                                                            ORDER BY D.[DESCRIPTION]", dateTime, deptId, deptId);

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

//                string sSQLGrandTotal = SQLParser.MakeSQL(@"SELECT 'Grand Total' AS DESCRIPTION,SUM(tab1.Amount)AS Amount,tab1.Department, tab1.Unit
//                                                    FROM
//                                                    (SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,
//                                                    D.Description AS Department,  P.CODE AS Unit
//                                                    FROM SALARYMONTHLYDETAIL smd
//                                                    INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
//                                                    LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
//                                                    LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
//                                                    WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) and D.PARENTID=%n
//                                                    GROUP BY D.[DESCRIPTION], P.CODE
//                                                    Union
//                                                    SELECT  'Total OPI' AS Description,SUM(opdi.ChangeNetAmount)AS Amount,
//                                                    D.Description AS Department,  P.CODE AS Unit
//                                                    FROM OPIProcessDetailItem opdi
//                                                    LEFT JOIN OpiItem op ON opdi.OPIItemID = op.OpiItemID
//                                                    LEFT JOIN OPIProcessDetail opd ON opd.OPIProcessDetailID = opdi.OPIProcessDetailID
//                                                    LEFT JOIN OPIProcess opp ON opd.OPIProcessID = opp.OPIProcessID
//                                                    LEFT JOIN Department D ON opd.DepartmentID = D.DEPARTMENTID
//                                                    LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
//                                                    WHERE opp.OPIMonth = %d and D.PARENTID=%n
//                                                    GROUP BY D.[DESCRIPTION],P.CODE)tab1
//                                                    GROUP BY tab1.Department, tab1.Unit
//                                                    Order By tab1.Department", dateTime, deptId, dateTime, deptId);

                string sSQLGrandTotal = SQLParser.MakeSQL(@"SELECT 'Grand Total' AS DESCRIPTION,SUM(tab1.Amount)AS Amount,tab1.Department, tab1.Unit
                                                            FROM
                                                            (SELECT Tab2.[Description], Tab2.Amount, Tab2.Department, Tab2.Unit
                                                            from (SELECT DISTINCT Tab1.Department, tab1.Unit, Tab1.[Description], SUM(Tab1.Amount) Amount FROM
                                                            (SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE
                                                            UNION
                                                            SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(2) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE
                                                            UNION
                                                            SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                            FROM SALARYMONTHLYDETAIL smd
                                                            INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                            LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE sm.SalaryMonth = %d AND smd.ITEMID =-128 and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION], P.CODE) Tab1
                                                            GROUP BY Tab1.[Description], Tab1.Department, Tab1.Unit) Tab2
                                                            Union
                                                            SELECT  'Total OPI' AS Description,SUM(opdi.ChangeNetAmount)AS Amount,
                                                            D.Description AS Department,  P.CODE AS Unit
                                                            FROM OPIProcessDetailItem opdi
                                                            LEFT JOIN OpiItem op ON opdi.OPIItemID = op.OpiItemID
                                                            LEFT JOIN OPIProcessDetail opd ON opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                                            LEFT JOIN OPIProcess opp ON opd.OPIProcessID = opp.OPIProcessID
                                                            LEFT JOIN Department D ON opd.DepartmentID = D.DEPARTMENTID
                                                            LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                            WHERE opp.OPIMonth = %d and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                            GROUP BY D.[DESCRIPTION],P.CODE)tab1
                                                            GROUP BY tab1.Department, tab1.Unit
                                                            Order By tab1.Department", dateTime, deptId, deptId, dateTime, deptId, deptId, dateTime, deptId, deptId, dateTime, deptId, deptId);


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

//                string sSQLDeductedItems = SQLParser.MakeSQL(@"SELECT smd.Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
//                                                                FROM SALARYMONTHLYDETAIL smd
//                                                                left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
//                                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
//                                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
//                                                                WHERE sm.SalaryMonth = %d AND smd.ItemGroup IN(3) and D.PARENTID=%n
//                                                                GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION,P.CODE
//                                                                ORDER BY D.[DESCRIPTION],smd.POSITION", dateTime, deptId);

                string sSQLDeductedItems = SQLParser.MakeSQL(@"SELECT CASE  WHEN  smd.ITEMID=-128 THEN  'Provident Fund-Both Contribution' ELSE  smd.[DESCRIPTION]
                                                                END  [DESCRIPTION], 
                                                                CASE  WHEN  smd.ITEMID=-128 THEN  SUM(smd.ChangedAmount*2) ELSE  SUM(smd.ChangedAmount) END  Amount,
                                                                D.Description AS Department,  P.CODE AS Unit
                                                                FROM SALARYMONTHLYDETAIL smd
                                                                left JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                                WHERE sm.SalaryMonth = %d AND smd.ItemGroup IN(3) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                                GROUP BY smd.[DESCRIPTION],D.[DESCRIPTION],smd.POSITION,P.CODE, smd.ITEMID",
                                                                dateTime, deptId, deptId);


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

//                string sSQLTotalDeduction = SQLParser.MakeSQL(@"SELECT 'Total Deduction' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
//                                                                FROM SALARYMONTHLYDETAIL smd
//                                                                INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
//                                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
//                                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
//                                                                WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(3) and D.PARENTID=%n
//                                                                --AND smd.ITEMID NOT IN (-128)
//                                                                GROUP BY D.[DESCRIPTION],P.CODE
//                                                                ORDER BY D.[DESCRIPTION]", dateTime, deptId);

                string sSQLTotalDeduction = SQLParser.MakeSQL(@"SELECT 'Total Deduction' AS Description , 
                                                                CASE  WHEN  smd.ITEMID=-128 THEN  SUM(smd.ChangedAmount*2) ELSE  SUM(smd.ChangedAmount) END  Amount,
                                                                D.Description AS Department,  P.CODE AS Unit
                                                                FROM SALARYMONTHLYDETAIL smd
                                                                INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                                WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(3) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                                GROUP BY D.[DESCRIPTION],P.CODE, smd.ITEMID
                                                                ORDER BY D.[DESCRIPTION]", dateTime, deptId, deptId);


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

                string sNetSalary = SQLParser.MakeSQL(@"SELECT 'Net Salary'AS Description,(t1.Amount - ISNULL(t2.Amount,0))AS Amount,t1.Department,t1.Unit
                                                FROM
                                                (SELECT 'Grand Total' AS DESCRIPTION,SUM(tab1.Amount)AS Amount,tab1.Department, tab1.Unit
                                                FROM
                                                (SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department, P.CODE AS Unit
                                                FROM SALARYMONTHLYDETAIL smd
                                                INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                GROUP BY D.[DESCRIPTION], P.CODE
                                                UNION
                                                SELECT 'Total Gross' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department, P.CODE AS Unit
                                                FROM SALARYMONTHLYDETAIL smd
                                                INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(2) and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                GROUP BY D.[DESCRIPTION], P.CODE
                                                Union
                                                SELECT  'Total OPI' AS Description,SUM(opdi.ChangeNetAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                FROM OPIProcessDetailItem opdi
                                                LEFT JOIN OpiItem op ON opdi.OPIItemID = op.OpiItemID
                                                LEFT JOIN OPIProcessDetail opd ON opd.OPIProcessDetailID = opdi.OPIProcessDetailID
                                                LEFT JOIN OPIProcess opp ON opd.OPIProcessID = opp.OPIProcessID
                                                LEFT JOIN Department D ON opd.DepartmentID = D.DEPARTMENTID
                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                WHERE opp.OPIMonth = %d and (d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                GROUP BY D.[DESCRIPTION], P.CODE)tab1
                                                GROUP BY tab1.Department,tab1.Unit)t1
                                                LEFT OUTER join
                                                (SELECT 'Total Deduction' AS Description ,SUM(smd.ChangedAmount)AS Amount,D.Description AS Department,  P.CODE AS Unit
                                                FROM SALARYMONTHLYDETAIL smd
                                                INNER JOIN SALARYMONTHLY sm ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                LEFT JOIN DEPARTMENT D ON sm.DEPARTMENTID = D.DEPARTMENTID
                                                LEFT JOIN DEPARTMENT P ON D.PARENTID = P.DEPARTMENTID
                                                WHERE sm.SalaryMonth = %d AND smd.ITEMGROUP IN(3) and(d.PARENTID=%n OR d.DEPARTMENTID=%n)
                                                GROUP BY D.[DESCRIPTION], P.Code)t2
                                                ON  t1.Department = t2.Department
                                                ORDER BY t1.Department", dateTime, deptId, deptId, dateTime, deptId, deptId, dateTime, deptId, deptId, dateTime, deptId, deptId);

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

        internal static DataSet GetSalaryComparisonData(TransactionContext tc, DateTime _SalaryMonth, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLSalaryComparison = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.[NAME],(G.[DESCRIPTION]) AS Grade,
                                                                (D.[DESCRIPTION])AS Department,DES.[NAME] AS Designation,
                                                                SUM(Case When sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) 
		                                                                 THEN smd.CHANGEDAMOUNT ELSE 0 END)AS CurAmount,
                                                                SUM(Case When sm.SalaryMonth = %d AND smd.ITEMGROUP IN(1,8) 
		                                                                 THEN smd.CHANGEDAMOUNT ELSE 0 END)AS PrvAmount
                                                                FROM EMPLOYEE E
                                                                LEFT JOIN Grades G ON E.GRADEID = G.GRADEID
                                                                LEFT JOIN Department D ON E.DEPARTMENTID = D.DEPARTMENTID
                                                                Left Join Designation DES ON E.DESIGNATIONID = DES.DESIGNATIONID
                                                                LEFT JOIN SalaryMonthly sm ON E.EMPLOYEEID = sm.EMPLOYEEID
                                                                LEFT JOIN SalaryMonthlyDetail smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                                WHERE sm.EMPLOYEEID IN (%q)
                                                                GROUP BY E.EMPLOYEENO,E.[NAME],G.[DESCRIPTION],D.[DESCRIPTION],
                                                                DES.[NAME]", _SalaryMonth, GlobalFunctions.LastDateOfMonth(_SalaryMonth.AddMonths(-1)),
                                                                           sEmpID);

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

        internal static DataSet GetEmpExperience(TransactionContext tc, int empid)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLExperience = SQLParser.MakeSQL(@"SELECT E.[NAME],E.FATHERNAME,E.MOTHERNAME,E.JOININGDATE,E.ENDOFCONTRACTDATE,
                                                            E.Gender,D.[DESCRIPTION] AS Department,DES.[NAME] AS Designation
                                                            FROM EMPLOYEE e
                                                            Left Outer Join Department D ON E.DEPARTMENTID = D.DEPARTMENTID
                                                            LEFT OUTER JOIN Designation DES ON E.DESIGNATIONID = DES.DESIGNATIONID
                                                            WHERE E.EMPLOYEEID = %n", empid);

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

        internal static DataSet GetEmpsAuditReport(TransactionContext tc, DateTime _SalaryMonth, string sEmpID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLAudit = SQLParser.MakeSQL(@"SELECT E.EMPLOYEEID,E.EMPLOYEENO,E.[NAME],D.[DESCRIPTION] AS Department,DES.[NAME] AS Designation,G.[DESCRIPTION] AS Grade,
                                                    smd.[DESCRIPTION],smd.CHANGEDAMOUNT AS Amount,smd.ITEMCODE,smd.ITEMGROUP,smd.ITEMID,E.JOININGDATE,E.BIRTHDATE
                                                    FROM EMPLOYEE E 
                                                    LEFT JOIN Department D ON E.DEPARTMENTID = D.DEPARTMENTID
                                                    LEFT JOIN Designation DES ON E.DESIGNATIONID = DES.DESIGNATIONID
                                                    LEFT JOIN Grades G ON E.GRADEID = G.GRADEID
                                                    LEFT JOIN SalaryMonthly sm On E.EMPLOYEEID = sm.EMPLOYEEID
                                                    LEFT JOIN SalaryMonthlyDetail smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                    WHERE sm.SalaryMonth = %d AND E.EMPLOYEEID IN(%q)
                                                    ORDER BY E.EMPLOYEEID", _SalaryMonth, sEmpID);

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

        internal static DataSet GetSalaryCertificateData(TransactionContext tc, int EmpID, int bankID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sItemCode = Convert.ToString((int)EnumSalaryItemCode.Loan_Monthly_Installment) + "," + Convert.ToString((int)EnumSalaryItemCode.Loan_Monthly_Interest);

            try
            {
                string sSQLAudit = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.[NAME],E.JOININGDATE,E.ENDOFCONTRACTDATE,Br.[ADDRESS],
                        DES.[NAME] AS Designation,Dep.[DESCRIPTION] AS Division,b.[NAME]AS Bank,
                        Sum(CASE WHEN smd.ITEMGROUP=%n THEN smd.CHANGEDAMOUNT ELSE 0 END) AS GrossAmount,
                        SUM(CASE WHEN smd.ITEMGROUP=%n AND smd.ITEMCODE IN(%q) THEN smd.CHANGEDAMOUNT ELSE 0 END)AS LoanAmount,
                        SUM(CASE WHEN smd.ITEMGROUP=%n AND smd.ITEMCODE NOT IN (%q) THEN smd.CHANGEDAMOUNT ELSE 0 END) AS DeductAmount
                        FROM Employee E
                        INNER JOIN BRANCHES Br ON E.BRANCHID = Br.BRANCHID
                        INNER JOIN BANKS b ON Br.BANKID = b.BANKID
                        INNER JOIN DESIGNATION DES ON E.DESIGNATIONID = DES.DESIGNATIONID
                        INNER JOIN DEPARTMENT Dep ON E.DEPARTMENTID = Dep.DEPARTMENTID 
                        INNER JOIN SALARYMONTHLY sm ON E.EMPLOYEEID = sm.EMPLOYEEID
                        INNER JOIN SALARYMONTHLYDETAIL smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                        WHERE sm.SalaryMonth=(SELECT MAX(SalaryMonth) FROM SALARYMONTHLY) 
                        AND E.EMPLOYEEID = %n AND B.BANKID = %n 
                        GROUP BY E.EMPLOYEENO,E.[NAME],E.JOININGDATE,E.ENDOFCONTRACTDATE,
                        DES.[NAME],Dep.[DESCRIPTION],b.[NAME],Br.[ADDRESS]", (int)EnumSalaryGroup.Gross, (int)EnumSalaryGroup.Deductions, sItemCode,
                        (int)EnumSalaryGroup.Deductions, sItemCode, EmpID, bankID);

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

        internal static DataSet GetSalaryCertificateDataforOtherBank(TransactionContext tc, int EmpID, int bankID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLAudit = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.[NAME],E.JOININGDATE,E.ENDOFCONTRACTDATE,Br.[ADDRESS]AS BankAddress,
                                                                DES.[NAME] AS Designation,b.[NAME]AS Bank,smd.ITEMGROUP,
                                                                smd.CHANGEDAMOUNT AS Amount,smd.[DESCRIPTION]
                                                        FROM Employee E
                                                                INNER JOIN BRANCHES Br ON E.BRANCHID = Br.BRANCHID
                                                                INNER JOIN BANKS b ON Br.BANKID = b.BANKID
                                                                INNER JOIN DESIGNATION DES ON E.DESIGNATIONID = DES.DESIGNATIONID 
                                                                INNER JOIN SALARYMONTHLY sm ON E.EMPLOYEEID = sm.EMPLOYEEID
                                                                INNER JOIN SALARYMONTHLYDETAIL smd ON sm.SALARYMONTHLYID = smd.SALARYMONTHLYID
                                                        WHERE sm.SalaryMonth=(SELECT MAX(SalaryMonth) FROM SALARYMONTHLY) 
                                                                AND E.EMPLOYEEID = %n AND B.BANKID = %n 
                                                        GROUP BY E.EMPLOYEENO,E.[NAME],E.JOININGDATE,E.ENDOFCONTRACTDATE,smd.[DESCRIPTION],
                                                                DES.[NAME],b.[NAME],Br.[ADDRESS],smd.CHANGEDAMOUNT,smd.POSITION,smd.ITEMGROUP
                                                        ORDER BY smd.POSITION", EmpID, bankID);

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

        internal static DataSet GetExceptionEmployeesPF(TransactionContext tc, DateTime fromdate, DateTime todate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();

            try
            {
                string sSQLAudit = SQLParser.MakeSQL(@"SELECT t.EmployeeNo, t.[Name],sum(t.TRANAMOUNT)AS PFAmount,t.Country,
                                                    t.CostCenter,t.Designation,t.Department
                                                    FROM 
                                                        (
                                                          SELECT e.EmployeeNo,e.Name,e.JOININGDATE,D.NAME AS Designation,Dep.DESCRIPTION AS Department,
                                                          pt.TRANAMOUNT, CC.DESCRIPTION AS CostCenter,e2.PRESENTADDRESS AS Country
                                                          FROM PFTRANSACTION pt 
                                                          LEFT JOIN PFException pfEx ON pt.EMPLOYEEID = pfex.EmployeeID 
                                                          LEFT JOIN Employee e ON pfex.EmployeeID = e.EMPLOYEEID AND pt.TRANDATE BETWEEN %d AND %d 
                                                          LEFT JOIN DESIGNATION D ON e.DESIGNATIONID = D.DESIGNATIONID
                                                          INNER JOIN EMPCONTACT e2 ON e2.EMPLOYEEID=e.EMPLOYEEID
                                                          LEFT JOIN DEPARTMENT Dep ON e.DEPARTMENTID = Dep.DEPARTMENTID
                                                          LEFT JOIN EMPCOSTCENTER cmpcc ON e.EMPLOYEEID=cmpcc.EMPLOYEEID AND cmpcc.CurrentCC=1
                                                          INNER JOIN CRG CC ON cmpcc.CostCenterID = CC.CRGID  
                                                        )t 
                                                    GROUP BY t.EmployeeNo, t.[Name],t.CostCenter,t.Country,
                                                    t.JOININGDATE,t.Designation,t.Department", fromdate, todate);

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

        internal static DataSet GetBankPaymentSummaryData(TransactionContext tc, DateTime fromdate)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sqlID = Convert.ToString((int)EnumSalaryItemCode.PF_Contribution)+","+ Convert.ToString(1);
            string sqlCode = Convert.ToString((int)EnumSalaryItemCode.PF_Contribution)+","+
            Convert.ToString((int)EnumSalaryItemCode.Loan_Monthly_Installment)+","+Convert.ToString((int)EnumSalaryItemCode.Loan_Monthly_Interest);

            string groupID = Convert.ToString((int)EnumSalaryGroup.Gross) + "," + Convert.ToString((int)EnumSalaryGroup.Arrear);

            try
            {
                string sSQLBankPayment = SQLParser.MakeSQL(@"DECLARE @cols AS NVARCHAR(MAX),
                            @colNames AS NVARCHAR(MAX),
                            @query AS NVARCHAR(MAX)

                            select @cols = STUFF((SELECT ',' + QUOTENAME(d.CODE) 
                                                FROM department d WHERE d.PARENTID IS NULL
                                                                    
                                        FOR XML PATH(''), TYPE
                                        ).value('.', 'NVARCHAR(MAX)') 
                                    ,1,1,'')
                                    
                            select @colNames = STUFF((SELECT ', isnull(' + QUOTENAME(d.CODE) +', 0) as '+QUOTENAME(d.CODE)
                                                FROM department d WHERE d.PARENTID IS NULL
                                                                    
                                        FOR XML PATH(''), TYPE
                                        ).value('.', 'NVARCHAR(MAX)') 
                                    ,1,1,'')
                               
                            set @query = 'SELECT Bank, ' + @colNames + ' 
                                         from 
                                         (  
	                        SELECT P.Bank,P.Unit,IsNull(P.Amount,0)AS Amount From
							(SELECT bank ,unit ,unitdescription,sum(amount)AS Amount FROM 
							(SELECT b.[NAME] Bank,pd.CODE Unit,pd.description UnitDescription, SUM(smd.CHANGEDAMOUNT) Amount 
							from salarymonthly sm,department d,salarymonthlydetail smd, DEPARTMENT pd,branches br, BANKS b
							WHERE sm.DEPARTMENTID=d.departmentid
							AND sm.SALARYMONTHLYID=smd.SALARYMONTHLYID
							AND d.PARENTID=pd.DEPARTMENTID
							AND sm.BRANCHID=br.BRANCHID
							AND br.BANKID=b.BANKID
							And sm.SalaryMonth = '%d' and smd.ItemGroup IN (%q) 
							GROUP BY b.[NAME],pd.CODE,pd.description
							union
							SELECT b.[NAME] Bank,pd.CODE Unit,pd.description UnitDescription, SUM(opdi.ChangeNetAmount) Amount 
							from OPIProcess op,department d,opiprocessdetail opd, opiprocessdetailItem  opdi,DEPARTMENT pd,branches br, BANKS b
							WHERE opd.DEPARTMENTID=d.departmentid
							AND op.OPIProcessID=opd.OPIProcessID
							AND opd.OPIProcessDetailID=opdi.OPIProcessDetailID
							AND d.PARENTID=pd.DEPARTMENTID
							AND opd.BRANCHID=br.BRANCHID
							AND br.BANKID=b.BANKID
							And op.OPIMonth = '%d' 
							GROUP BY b.[NAME],pd.CODE,pd.description) SM 
							GROUP BY bank ,unit ,unitdescription
							)AS P
                        ) x
                    pivot 
                    (
                        max(x.Amount)
                        for x.Unit in (' + @cols + ')
                    ) p '

                        execute(@query)", 
                        fromdate, groupID, fromdate);

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

                string sSQLBankPayment1 = SQLParser.MakeSQL(@"     DECLARE @cols AS NVARCHAR(MAX),
                                @colNames AS NVARCHAR(MAX),
                                @query AS NVARCHAR(MAX)

                                select @cols = STUFF((SELECT ',' + QUOTENAME(d.CODE) 
                                                    FROM department d WHERE d.PARENTID IS NULL
                                                                        
                                            FOR XML PATH(''), TYPE
                                            ).value('.', 'NVARCHAR(MAX)') 
                                        ,1,1,'')
                                        
                                select @colNames = STUFF((SELECT ', isnull(' + QUOTENAME(d.CODE) +', 0) as '+QUOTENAME(d.CODE)
                                                    FROM department d WHERE d.PARENTID IS NULL
                                                                        
                                            FOR XML PATH(''), TYPE
                                            ).value('.', 'NVARCHAR(MAX)') 
                                        ,1,1,'')
                         
                         
                                set @query = 'SELECT Description, ' + @colNames + ' 
                                             from 
                                             ( 
                                            SELECT P.Description,P.Unit,IsNull(P.Amount,0)AS Amount From
                                            (
                                            SELECT pd.CODE Unit,''Advance Income Tax'' AS Description, SUM(smd.CHANGEDAMOUNT) Amount  
                                            from salarymonthly sm,department d,salarymonthlydetail smd, DEPARTMENT pd
                                            WHERE sm.DEPARTMENTID=d.departmentid
                                            AND sm.SALARYMONTHLYID=smd.SALARYMONTHLYID
                                            AND d.PARENTID=pd.DEPARTMENTID
                                            And sm.SalaryMonth='%d' and smd.ItemGroup = %n 
                                            AND smd.ItemID = %n AND smd.ITEMCODE = %n 
                                            GROUP BY pd.CODE
                                            UNION
                                            SELECT pd.CODE Unit,''Employees Provident Fund'' AS Description, SUM(smd.CHANGEDAMOUNT) Amount  
                                            from salarymonthly sm,department d,salarymonthlydetail smd, DEPARTMENT pd
                                            WHERE sm.DEPARTMENTID=d.departmentid
                                            AND sm.SALARYMONTHLYID=smd.SALARYMONTHLYID
                                            AND d.PARENTID=pd.DEPARTMENTID
                                            And sm.SalaryMonth='%d' and smd.ItemGroup = %n 
                                            AND smd.ItemID IN(%q) AND smd.ITEMCODE IN(%q) 
                                            GROUP BY pd.CODE
                                            UNION
                                            SELECT pd.CODE Unit,''Article Loan'' AS Description, SUM(smd.CHANGEDAMOUNT) Amount  
                                            from salarymonthly sm,department d,salarymonthlydetail smd, DEPARTMENT pd
                                            WHERE sm.DEPARTMENTID=d.departmentid
                                            AND sm.SALARYMONTHLYID=smd.SALARYMONTHLYID
                                            AND d.PARENTID=pd.DEPARTMENTID
                                            And sm.SalaryMonth='%d' and smd.ItemGroup = %n 
                                            AND smd.ItemID = %n AND smd.ITEMCODE = %n 
                                            GROUP BY pd.CODE
                                            )AS P
                                            ) d
                                            pivot
                                            (
                                              max(d.Amount)
                                              for d.Unit IN(' + @cols + ')
                                            ) piv'
                                                         execute(@query) 
                                            ",fromdate,(int)EnumSalaryGroup.Deductions,(int)EnumSalaryItemCode.Inc_Tax_Deduction,(int)EnumSalaryItemCode.Inc_Tax_Deduction,
                                             fromdate, (int)EnumSalaryGroup.Deductions, sqlID, sqlCode,fromdate,(int)EnumSalaryGroup.Deductions,
                                             19,(int)EnumSalaryItemCode.Deduction);

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

        internal static DataSet GetCashPaymentSummaryData(TransactionContext tc, DateTime fromdate)
        {
            DataSet cashPayDset = new DataSet();
            string groupID = Convert.ToString((int)EnumSalaryGroup.Gross) + "," + Convert.ToString((int)EnumSalaryGroup.Arrear);

            string query = SQLParser.MakeSQL(@"SELECT EmployeeNo,NAME,Unit,UnitName,Department,Designation,SUM(Amount) AS Amount FROM
                                            (SELECT E.EMPLOYEENO,E.Name,pd.CODE Unit,pd.description UnitName,d.[DESCRIPTION] AS Department,
                                            DES.[NAME] AS Designation, Sum(smd.CHANGEDAMOUNT) AS Amount
                                            from salarymonthly sm,department d,Designation Des,salarymonthlydetail smd, DEPARTMENT pd,Employee E
                                            WHERE sm.DEPARTMENTID=d.departmentid
                                            AND sm.SALARYMONTHLYID=smd.SALARYMONTHLYID
                                            AND sm.EMPLOYEEID = E.EMPLOYEEID
                                            AND sm.DESIGNATIONID = DES.DESIGNATIONID
                                            AND d.PARENTID=pd.DEPARTMENTID
                                            And sm.SalaryMonth = %d and smd.ItemGroup IN (%q) 
                                            AND sm.BRANCHID IS NULL
                                            GROUP BY E.EMPLOYEENO,E.Name,pd.CODE ,pd.description ,d.[DESCRIPTION],DES.[NAME] 
                                            union
                                            SELECT E.EMPLOYEENO,E.Name,pd.CODE Unit,pd.description UnitName,d.[DESCRIPTION] AS Department,
                                            DES.[NAME] AS Designation, SUM(opdi.ChangeNetAmount) Amount 
                                            from OPIProcess op,department d,opiprocessdetail opd, opiprocessdetailItem  opdi,
                                            DEPARTMENT pd,Employee E,Designation Des
                                            WHERE opd.DEPARTMENTID=d.departmentid
                                            AND op.OPIProcessID=opd.OPIProcessID
                                            AND opd.OPIProcessDetailID=opdi.OPIProcessDetailID
                                            AND opd.EmployeeID = E.EMPLOYEEID
                                            AND opd.DesignationID = DES.DESIGNATIONID
                                            AND d.PARENTID=pd.DEPARTMENTID
                                            And op.OPIMonth = %d AND opd.BranchID IS NULL 
                                            GROUP BY E.EMPLOYEENO,E.Name,pd.CODE ,pd.description ,d.[DESCRIPTION],DES.[NAME])t1
                                            GROUP BY t1.EMPLOYEENO,t1.[Name],t1.Unit,t1.UnitName,t1.Department,t1.Designation
                                            ORDER BY t1.Unit", fromdate,groupID, fromdate);

            cashPayDset = tc.ExecuteDataSet(query);

            return cashPayDset;
        }
    }
}