using System;
using System.Data;
using Ease.Core.DataAccess;
using HRM.BO;


namespace HRM.DA
{
    public class ArrearProcessDA
    {
        #region Constructor

        private ArrearProcessDA()
        {
        }

        #endregion

        #region Get

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

        internal static IDataReader GetWithPayrollType(TransactionContext tc, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcess where payrolltypeid=%n", payrollTypeID);
        }

        internal static IDataReader Get(TransactionContext tc, DateTime processMonth, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcess Where ProcessMonth=%d AND payrolltypeid=%n",
                processMonth, payrollTypeID);
        }

        internal static IDataReader GetDetailItems(TransactionContext tc, int nEmpID, DateTime processMonth,
            int payrollTypeID)
        {
            return tc.ExecuteReader("select * from arrearprocessdetailitem where arrearprocessdetailid= " +
                                    "(select arrearprocessdetailid from arrearprocessdetail where employeeid=%n AND arrearprocessid=(select arrearprocessid from arrearprocess " +
                                    " where processmonth=%d and payrolltypeid=%n)) order by serialno", nEmpID,
                processMonth, payrollTypeID);
        }

        internal static DataSet GetDetailItems(TransactionContext tc, string sEmpIDs, DateTime processMonth,
            int ItemType, int ItemID, int payrollTypeID)
        {
            return tc.ExecuteDataSet(@"
SELECT ad.EMPLOYEEID, adi.* from arrearprocessdetailitem adi,arrearprocessdetail ad WHERE 
adi.ARREARPROCESSDETAILID=ad.ARREARPROCESSDETAILID AND  employeeid IN (%q)
AND ITEMTYPE=%n AND ItemID=%n 
AND ad.ARREARPROCESSID IN(SELECT MAX(arrearprocessid) from arrearprocess  where payrolltypeid=%n)", sEmpIDs, ItemType,
                ItemID, payrollTypeID);
        }

        internal static IDataReader GetDetailItems(TransactionContext tc, int arrearProcessId, int nEmpID)
        {
            return tc.ExecuteReader("select * from arrearprocessdetailitem where arrearprocessdetailid= " +
                                    "(select arrearprocessdetailid from arrearprocessdetail where employeeid=%n AND arrearprocessid=%n" +
                                    " ) order by serialno", nEmpID, arrearProcessId);
        }

        internal static IDataReader GetArrearProcessDetails(TransactionContext tc, int ArrearProcessID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcessDetail WHERE ArrearProcessID=%n", ArrearProcessID);
        }

        internal static IDataReader GetDetails(TransactionContext tc, int arrearProcessId, string nEmpID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcessDetail WHERE ArrearProcessID=%n AND EmployeeID in (%q)",
                arrearProcessId, nEmpID);
        }

        internal static IDataReader GetDetails(TransactionContext tc, int arrearProcessId, int EmpID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcessDetail WHERE ArrearProcessID=%n AND EmployeeID = %n",
                arrearProcessId, EmpID);
        }

        internal static IDataReader GetArrearProcessDetails(TransactionContext tc, string sEmpID, DateTime startDate,
            DateTime endDate, int payrolltypeID)
        {
            string sql = string.Empty;

            sql = SQLParser.MakeSQL(@"SELECT * FROM ArrearProcessDetail 
                                          WHERE EmployeeID IN(%q) 
                                          AND ArrearProcessID IN
                                          (SELECT ArrearProcessID 
                                          FROM ArrearProcess 
                                          WHERE ProcessMonth BETWEEN %d AND %d
                                          AND PayrollTypeID=%n)", sEmpID, startDate, endDate, payrolltypeID);

            return tc.ExecuteReader(sql);
        }

        internal static DataSet GetArrearProcessDetailsByCCIDAndEmpID(TransactionContext tc, string sCCID,
            DateTime startDate, DateTime endDate, string sEmpIDs, int payrollTypeID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sQuery = string.Empty;
            if (sCCID == "")
            {
                sQuery = SQLParser.MakeSQL(
                    @" select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
                                                select distinct b.employeeid,c.description,c.calculatedamount
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt   
                                                ,employee emp  
                                                where ttttt.employeeid=emp.employeeid 
                                                group by costcenterid,description,emp.departmentid                                             
                                                ", startDate, endDate, payrollTypeID,
                    (int)EnumArearProcessItemType.Gross, sEmpIDs, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @"  select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
                                                select distinct b.employeeid,c.description,c.calculatedamount,itemid
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt   
                                                ,employee emp  
                                                where ttttt.employeeid=emp.employeeid 
                                                group by costcenterid,description,emp.departmentid,itemid", startDate,
                    endDate, payrollTypeID, (int)EnumArearProcessItemType.OPI, sEmpIDs, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
                                                select distinct b.employeeid,c.description,c.calculatedamount
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt   
                                                ,employee emp  
                                                where ttttt.employeeid=emp.employeeid 
                                                group by costcenterid,description,emp.departmentid", startDate,
                    endDate, payrollTypeID, (int)EnumArearProcessItemType.PF, sEmpIDs, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @"  select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
                                                select distinct b.employeeid,c.description,c.calculatedamount,itemid
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt    ,employee emp  
                                                where ttttt.employeeid=emp.employeeid  
                                                group by costcenterid,description,emp.departmentid,itemid", startDate,
                    endDate, payrollTypeID, (int)EnumArearProcessItemType.Bonus, sEmpIDs, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

            else
            {
                sQuery = SQLParser.MakeSQL(
                    @" select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
                                                    select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
                                                    select distinct b.employeeid,c.description,c.calculatedamount
                                                    from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid AND EMPCC.costcenterid in(%q) )ttttt   
                                                ,employee emp  
                                                where ttttt.employeeid=emp.employeeid 
                                                group by costcenterid,description,emp.departmentid                                             
                                                ", startDate, endDate, payrollTypeID,
                    (int)EnumArearProcessItemType.Gross, sEmpIDs, startDate, endDate, sEmpIDs, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @"  select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
                                                select distinct b.employeeid,c.description,c.calculatedamount,itemid
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid  AND EMPCC.costcenterid in(%q) )ttttt   
                                                ,employee emp  
                                                where ttttt.employeeid=emp.employeeid 
                                                group by costcenterid,description,emp.departmentid,itemid", startDate,
                    endDate, payrollTypeID, (int)EnumArearProcessItemType.OPI, sEmpIDs, startDate, endDate, sEmpIDs,
                    sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @"  select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
                                                select distinct b.employeeid,c.description,c.calculatedamount
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid  AND EMPCC.costcenterid in(%q) )ttttt   
                                                ,employee emp  
                                                where ttttt.employeeid=emp.employeeid 
                                                group by costcenterid,description,emp.departmentid", startDate,
                    endDate, payrollTypeID, (int)EnumArearProcessItemType.PF, sEmpIDs, startDate, endDate, sEmpIDs,
                    sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @"  select  costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
                                                select distinct b.employeeid,c.description,c.calculatedamount,itemid
                                                from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                b.employeeid in (%q)) tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND tab2.employeeid= EMPCC.employeeid  AND EMPCC.costcenterid in(%q) )ttttt    ,employee emp  
                                                where ttttt.employeeid=emp.employeeid  
                                                group by costcenterid,description,emp.departmentid,itemid", startDate,
                    endDate, payrollTypeID, (int)EnumArearProcessItemType.Bonus, sEmpIDs, startDate, endDate, sEmpIDs,
                    sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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


            return rootDataset;
        }

        internal static DataSet GetArrearProcessDetailsByCCIDAndEmpID2(TransactionContext tc, string sCCID,
            DateTime startDate, DateTime endDate, string sEmpIDs)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sQuery = string.Empty;
            if (sCCID == "")
            {
                sQuery = SQLParser.MakeSQL(@"select ttttt.EmployeeID,itemtype,sum(Amount) as Amount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid, itemtype,tab1.calculatedamount*percentage/100 as amount from (
                                                select S.EmployeeID, DESCRIPTION,itemtype,CalculatedAmount from ArrearProcessDetail S,
                                                ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and 
                                                S.ArrearEffectFromDate between %d and %d AND 
                                                S.ArrearprocessDetailID=b.ArrearprocessDetailID AND b.itemtype in(2,3,5,6,8,11,13,12))tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid                                               
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,itemtype                                             
                                                ", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

            else
            {
                sQuery = SQLParser.MakeSQL(@" select ttttt.EmployeeID,itemtype,sum(Amount) as Amount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid, itemtype,tab1.calculatedamount*percentage/100 as amount from (
                                                select S.EmployeeID, DESCRIPTION,itemtype,CalculatedAmount from ArrearProcessDetail S,
                                                ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and 
                                                S.ArrearEffectFromDate between %d and %d AND 
                                                S.ArrearprocessDetailID=b.ArrearprocessDetailID AND b.itemtype in(2,3,5,6,8,11,13,12))tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND empcc.costcenterid in(%q)
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,itemtype                                             
                                                ", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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


            return rootDataset;
        }

        internal static DataSet GetArrearProcessDetailsByCCIDAndEmpID3(TransactionContext tc, string sCCID,
            DateTime startDate, DateTime endDate, string sEmpIDs)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sQuery = string.Empty;
            if (sCCID == "")
            {
                sQuery = SQLParser.MakeSQL(
                    @" select ttttt.EmployeeID,Description,itemid,itemtype,sum(Amount) as Amount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid, Description,itemid,itemtype,tab1.calculatedamount*percentage/100 as amount from (
                                                select S.EmployeeID, DESCRIPTION,itemid,itemtype,CalculatedAmount from ArrearProcessDetail S,
                                                ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and 
                                                S.ArrearEffectFromDate between %d and %d AND 
                                                S.ArrearprocessDetailID=b.ArrearprocessDetailID )tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid                                               
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,Description,itemid,itemtype                                            
                                                ", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

            else
            {
                sQuery = SQLParser.MakeSQL(
                    @" select ttttt.EmployeeID,Description,itemid,itemtype,sum(Amount) as Amount from(
                                                select  distinct tab1.employeeid,EMPCC.costcenterid, Description,itemid,itemtype,tab1.calculatedamount*percentage/100 as amount from (
                                                select S.EmployeeID, DESCRIPTION,itemid,itemtype,CalculatedAmount from ArrearProcessDetail S,
                                                ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and 
                                                S.ArrearEffectFromDate between %d and %d AND 
                                                S.ArrearprocessDetailID=b.ArrearprocessDetailID )tab1,
                                                (
                                                select employeeid,salarymonthlyid from salarymonthly where  salarymonth between %d and %d
                                                AND employeeid in (%q)
                                                ) tab2,SALARYEMPCOSTCENTER EMPCC
                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND 
                                                tab1.employeeid= tab2.employeeid
                                                AND empcc.costcenterid in(%q)
                                                AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,Description,itemid,itemtype                                           
                                                ", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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


            return rootDataset;
        }

        internal static DataSet GetArrPrssDetailsByCstIDEmpIDLocAndDeptWise(TransactionContext tc, string sCCID,
            DateTime startDate, DateTime endDate, string sEmpIDs)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sQuery = string.Empty;
            if (sCCID == "")
            {
                sQuery = SQLParser.MakeSQL(
                    @"select ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype,sum(Amount) as Amount 
                                                FROM
		                                                (select  distinct tab1.employeeid,tab1.LocationID,tab1.DepartmentID
			                                                ,EMPCC.costcenterid,tab1.ItemID,itemtype,tab1.calculatedamount*percentage/100 as amount 
  		                                                  from 
	  		                                                  (select S.EmployeeID,S.LocationID,S.DepartmentID,DESCRIPTION,b.ItemID,
	  		  		                                                itemtype,CalculatedAmount
  			                                                  from ArrearProcessDetail S,ArrearProcessDetailItem b
  			                                                  where S.EMPLOYEEID in(%q) 
  			                                                  and S.ArrearEffectFromDate between %d and %d
  			                                                  AND S.ArrearprocessDetailID=b.ArrearprocessDetailID 
  			                                                  )tab1,
			                                                 (select employeeid,salarymonthlyid 
			                                                   from salarymonthly 
			                                                   where  salarymonth between %d and %d
			                                                   AND employeeid in (%q))
			                                                  tab2,
		  	                                                  SALARYEMPCOSTCENTER EMPCC
				                                                where tab2.salarymonthlyid=EMPCC.salarymonthlyid 
				                                                AND tab1.employeeid= tab2.employeeid
				                                                AND tab2.employeeid= EMPCC.employeeid
		                                                )ttttt 
                                                group by ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype"
                    , sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

            else
            {
                sQuery = SQLParser.MakeSQL(
                    @"select ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype,sum(Amount) as Amount 
                                                FROM
		                                            (select  distinct tab1.employeeid,tab1.LocationID,tab1.DepartmentID
			                                            ,EMPCC.costcenterid,tab1.ItemID,itemtype,tab1.calculatedamount*percentage/100 as amount 
  		                                              from 
	  		                                              (select S.EmployeeID,S.LocationID,S.DepartmentID,DESCRIPTION,b.ItemID,
	  		  		                                            itemtype,CalculatedAmount
  			                                              from ArrearProcessDetail S,ArrearProcessDetailItem b
  			                                              where S.EMPLOYEEID in(%q) 
  			                                              and S.ArrearEffectFromDate between %d and %d
  			                                              AND S.ArrearprocessDetailID=b.ArrearprocessDetailID 
  			                                              )tab1,
			                                             (select employeeid,salarymonthlyid 
			                                               from salarymonthly 
			                                               where  salarymonth between %d and %d
			                                               AND employeeid in (%q))
			                                              tab2,
		  	                                              SALARYEMPCOSTCENTER EMPCC
				                                            where tab2.salarymonthlyid=EMPCC.salarymonthlyid 
				                                            AND tab1.employeeid= tab2.employeeid
				                                            AND empcc.costcenterid in(%q)
				                                            AND tab2.employeeid= EMPCC.employeeid
		                                            )ttttt 
                                            group by ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype                                            
                                            ", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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


            return rootDataset;
        }

        internal static DataSet GetArrearProcessDetailsByCCID(TransactionContext tc, string sCCID, DateTime startDate,
            DateTime endDate, int payrollTypeID)
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            string sQuery = string.Empty;
            if (sCCID == "")
            {
                sQuery = SQLParser.MakeSQL(
                    @" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid
                                                group by d.costcenterid,c.description", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.Gross);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid
                                                group by d.costcenterid,c.description", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.OPI);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid
                                                group by d.costcenterid,c.description", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.PF);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select c.itemid,d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount 
                                                from  arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND 
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid
                                                group by d.costcenterid,c.description,c.itemid", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.Bonus);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

            else
            {
                sQuery = SQLParser.MakeSQL(
                    @" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid AND d.costcenterid in(%q)
                                                group by d.costcenterid,c.description", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.Gross, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid  AND d.costcenterid in(%q)
                                                group by d.costcenterid,c.description", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.OPI, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid  AND d.costcenterid in(%q)
                                                group by d.costcenterid,c.description", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.PF, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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

                sQuery = SQLParser.MakeSQL(
                    @" select c.itemid,d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount 
                                                from  arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
                                                where a.arrearprocessid=b.arrearprocessid AND
                                                b.arrearprocessdetailid=c.arrearprocessdetailid AND
                                                b.ProcessMonth between %d and %d AND 
                                                a.payrollTypeid=%n AND
                                                c.Itemtype=%n AND
                                                d.employeeid=d.employeeid   AND d.costcenterid in(%q) 
                                                group by d.costcenterid,c.description,c.itemid", startDate, endDate,
                    payrollTypeID, (int)EnumArearProcessItemType.Bonus, sCCID);
                tempdataset = tc.ExecuteDataSet(sQuery);

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


            return rootDataset;
        }

        internal static IDataReader GetArrearProcessDetailItems(TransactionContext tc, int ArrearProcessDetailID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID=%n",
                ArrearProcessDetailID);
        }


        internal static IDataReader GetArrearProcessDetailItemMonthlys(TransactionContext tc,
            int ArrearProcessDetailItemID)
        {
            return tc.ExecuteReader("SELECT * FROM ARRPROSDETAILITEMMONTHLY WHERE ArrearProcessDetailItemID=%n",
                ArrearProcessDetailItemID);
        }

        internal static IDataReader GetArrearProcessCostDists(TransactionContext tc, int ArrearProcessDetailID)
        {
            return tc.ExecuteReader("SELECT * FROM ArrearProcessCostDist WHERE ArrearprocessDetailID=%n",
                ArrearProcessDetailID);
        }

        #endregion

        #region IsExist

        internal static bool IsExist(TransactionContext tc, DateTime dt, int payrollTypeID)
        {
            bool isExist = false;
            object obj =
                tc.ExecuteScalar("SELECT Count(*) from ArrearProcess Where ProcessMonth=%d AND PayrollTypeID=%n",
                    Ease.Core.Utility.Global.DateFunctions.LastDateOfMonth(dt), payrollTypeID);
            isExist = Convert.ToInt32(obj) > 0 ? true : false;
            return isExist;
        }

        #endregion

        #region Insert

        internal static void Insert(TransactionContext tc, ArrearProcess oArrearProcess)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ArrearProcess(ArrearProcessID, ProcessDescription, ProcessMonth, ProcessDate," +
                " IsApproved, ApprovedBy, PayrollTypeID,CreatedBy,CreationDate)" +
                " VALUES(%n, %s, %d, %d, %b, %n, %n,%n,%d)",
                oArrearProcess.ID, oArrearProcess.ProcessDescription, oArrearProcess.ProcessMonth,
                oArrearProcess.ProcessDate, oArrearProcess.IsApproved,
                DataReader.GetNullValue(oArrearProcess.ApprovedBy, 0), oArrearProcess.PayrollTypeID,
                DataReader.GetNullValue(oArrearProcess.CreatedBy, 0),
                DataReader.GetNullValue(oArrearProcess.CreatedDate));
        }

        internal static void Insert(TransactionContext tc, ArrearProcessDetail oArrearProcessDetail)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ArrearProcessDetail(ArrearProcessDetailID,ArrearProcessID, EmployeeID, ProcessMonth, DepartmentID," +
                " DesingnationID, LocationID, BranchID,AccountNo,GradeID,ScaleID,BasicSalary,GrossSalary,IncrementPercent,ArrearEffectFromDate)" +
                " VALUES(%n,%n,%n, %d,%n,%n,%n,%n,%s,%n,%n,%n,%n,%n,%d)",
                oArrearProcessDetail.ID, oArrearProcessDetail.ArrearProcessID, oArrearProcessDetail.EmployeeID,
                oArrearProcessDetail.ProcessMonth, oArrearProcessDetail.DepartmentID,
                oArrearProcessDetail.DesingnationID,
                oArrearProcessDetail.LocationID, oArrearProcessDetail.BranchID, oArrearProcessDetail.AccountNo,
                oArrearProcessDetail.GradeID, oArrearProcessDetail.ScaleID, oArrearProcessDetail.BasicSalary,
                oArrearProcessDetail.GrossSalary, oArrearProcessDetail.IncrementPercent,
                oArrearProcessDetail.ArrearEffectFromDate);
        }

        internal static void Insert(TransactionContext tc, ArrearProcessDetailItem oArrearProcessDetailItem)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ArrearProcessDetailItem(ArrearProcessDetailItemID,ArrearProcessDetailID,ItemType,ItemID, Side, Description, CalculatedAmount," +
                " ChangedAmount, SerialNo, IsUploaded)" +
                " VALUES(%n,%n,%n, %n,%n,%s,%n,%n,%n, %n)",
                oArrearProcessDetailItem.ID, oArrearProcessDetailItem.ArrearProcessDetailID,
                (int)oArrearProcessDetailItem.ItemType, oArrearProcessDetailItem.ItemID,
                (int)oArrearProcessDetailItem.Side, oArrearProcessDetailItem.Description,
                oArrearProcessDetailItem.CalculatedAmount, oArrearProcessDetailItem.ChangedAmount,
                oArrearProcessDetailItem.SeriialNo, oArrearProcessDetailItem.IsUploaded);
        }

        internal static void Insert(TransactionContext tc,
            ArrearProcessDetailItemMonthly oArrearProcessDetailItemMonthly)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ARRPROSDETAILITEMMONTHLY(ARRPROSDETAILITEMMONTHLYID,ArrearProcessDetailItemID,ItemType,ItemID, Side, Description, CalculatedAmount," +
                " ChangedAmount, SerialNo,SalaryMonth, IsUploaded)" +
                " VALUES(%n,%n,%n, %n,%n,%s,%n,%n,%n,%d, %n)",
                oArrearProcessDetailItemMonthly.ID, oArrearProcessDetailItemMonthly.ArrearProcessDetailItemID,
                (int)oArrearProcessDetailItemMonthly.ItemType, oArrearProcessDetailItemMonthly.ItemID,
                (int)oArrearProcessDetailItemMonthly.Side, oArrearProcessDetailItemMonthly.Description,
                oArrearProcessDetailItemMonthly.CalculatedAmount, oArrearProcessDetailItemMonthly.ChangedAmount,
                oArrearProcessDetailItemMonthly.SeriialNo, oArrearProcessDetailItemMonthly.SalaryMonth,
                oArrearProcessDetailItemMonthly.IsUploaded);
        }

        internal static void UpdateDetailItem(TransactionContext tc, ArrearProcessDetailItem oArrearProcessDetailItem)
        {
            tc.ExecuteNonQuery(
                "Update ArrearProcessDetailItem SET CalculatedAmount=%n,ChangedAmount=%n Where ArrearProcessDetailItemID=%n",
                oArrearProcessDetailItem.CalculatedAmount, oArrearProcessDetailItem.ChangedAmount,
                oArrearProcessDetailItem.ID);
        }

        internal static void Insert(TransactionContext tc, ArrearProcessCostDist oArrearProcessCostDist)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ArrearProcessCostDist(ArrearProcessCostDistID,ArrearProcessDetailID,CostcenterID,InvolvementPercent, Amount)" +
                " VALUES(%n,%n,%n, %n,%n)",
                oArrearProcessCostDist.ID, oArrearProcessCostDist.ArrearProcessDetailID,
                oArrearProcessCostDist.CostcenterID, oArrearProcessCostDist.InvolvementPercent,
                oArrearProcessCostDist.Amount);
        }

        #endregion

        #region Update

        internal static void Update(TransactionContext tc, ArrearProcess oAsset)
        {
            //string sSQL = SQLParser.MakeSQL("UPDATE Asset SET [Code]=%s,[Name]=%s,[Description]=%s," +
            //    " [BuyTime]=%d,[Remarks]=%s,[IsUsing]=%n,[BuyPrice]=%n," +
            //    " [ModifiedBy]=%n,[ModifiedDate]=%d WHERE [AssetID]=%n",
            //    oAsset.Code, oAsset.Name,oAsset.Description,oAsset.BuyTime,
            //    oAsset.Remarks, oAsset.IsUsing, oAsset.BuyPrice,
            //    DataReader.GetNullValue(oAsset.ModifiedBy), 
            //    DataReader.GetNullValue(oAsset.ModifiedDate),
            //    oAsset.ID);
            //tc.ExecuteNonQuery(sSQL);
        }

        #endregion

        #region Delete

        internal static void Approve(TransactionContext tc, ArrearProcess oAP, int payrollTypeID,
            DateTime nextPayProcessDate)
        {
            tc.ExecuteNonQuery("DELETE FROM INCOMETAXTEMP WHERE  INCOMETAXTEMP.EMPLOYEEID IN "
                               + " (SELECT AP.EMPLOYEEID FROM  ArrearProcessDetail AP"
                               + " WHERE "
                               + " AP.ArrearProcessID= %n)", oAP.ID);

            tc.ExecuteNonQuery("INSERT INTO  INCOMETAXTEMP(EMPLOYEEID, ITEMCODE, ITEMID, DESCRIPTION,"
                               + " THISYEARTOTAL, PREVIOUSAMOUNT, THISMONTHAMOUNT, PROJECTEDAMOUNT,"
                               + " SIDE, POSITION)"
                               + " (SELECT SALARYTEMPIT.EMPLOYEEID, SALARYTEMPIT.ITEMCODE, SALARYTEMPIT.ITEMID,"
                               + " SALARYTEMPIT.DESCRIPTION, SALARYTEMPIT.THISYEARTOTAL, "
                               + " SALARYTEMPIT.PREVIOUSAMOUNT, SALARYTEMPIT.THISMONTHAMOUNT, "
                               + " SALARYTEMPIT.PROJECTEDAMOUNT, SALARYTEMPIT.SIDE, SALARYTEMPIT.POSITION "
                               + " "
                               + " FROM  SALARYTEMPIT WHERE  SALARYTEMPIT.EMPLOYEEID IN "
                               + " (SELECT DISTINCT AP.EMPLOYEEID FROM ArrearProcessDetail AP "
                               + " WHERE "
                               + " AP.ArrearProcessID= %n ))", oAP.ID);

            tc.ExecuteNonQuery("DELETE FROM SALARYTEMPIT WHERE  SALARYTEMPIT.EMPLOYEEID IN "
                               + " (SELECT AP.EMPLOYEEID FROM  ArrearProcessDetail AP "
                               + " WHERE "
                               + " AP.ArrearProcessID= %n)", oAP.ID);

            tc.ExecuteNonQuery(
                "Update GradeSalaryAssignment Set ArrearInfo = %n Where ArrearInfo = %n AND GradeSalaryAssignment.EmployeeID IN "
                + " (SELECT Employee.EmployeeID FROM Employee WHERE PayrollTypeID=%n)",
                EnumArrearType.Paid, EnumArrearType.ToCalculate,
                payrollTypeID);

            tc.ExecuteNonQuery(
                "Update ADParameterEmployee SET ArrearInfo=%n  Where ARREARINFO=%n AND TillDate <= %d AND ADParameterEmployee.EmployeeID IN"
                + " (SELECT Employee.EmployeeID FROM Employee WHERE Employee.PayrollTypeID=%n)", EnumArrearType.Paid,
                EnumArrearType.ToCalculate,
                nextPayProcessDate, payrollTypeID);


            tc.ExecuteNonQuery(
                "UPDATE ArrearProcess set IsApproved=%b, ApprovedBy=%n,ModifiedBy=%n,ModifiedDate=%d  WHERE ArrearProcessID=%n",
                oAP.IsApproved, oAP.ApprovedBy, DataReader.GetNullValue(oAP.ModifiedBy),
                DataReader.GetNullValue(oAP.ModifiedDate), oAP.ID);
        }

        internal static void Delete(TransactionContext tc, ArrearProcess oAP)
        {
            //tc.ExecuteNonQuery("DELETE FROM SalaryTempIT WHERE EmployeeID in (Select EmployeeID from ArrearProcessDetail Where ArrearProcessID=%n)", oAP.ID);
            //tc.ExecuteNonQuery("DELETE FROM ArrearProcessCostDist WHERE ArrearprocessDetailID IN (select ArrearProcessDetailID from ArrearProcessDetail WHERE ArrearProcessID=%n)", oAP.ID);
            tc.ExecuteNonQuery(
                "DELETE FROM ARRPROSDETAILITEMMONTHLY WHERE ArrearProcessDetailItemID IN(SELECT ArrearProcessDetailItemID from ArrearProcessDetailItem WHERE ArrearprocessDetailID IN (select ArrearProcessDetailID from ArrearProcessDetail WHERE ArrearProcessID=%n))",
                oAP.ID);
            tc.ExecuteNonQuery(
                "DELETE FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID IN (select ArrearProcessDetailID from ArrearProcessDetail WHERE ArrearProcessID=%n)",
                oAP.ID);
            tc.ExecuteNonQuery("DELETE FROM ArrearProcessDetail WHERE ArrearProcessID=%n", oAP.ID);
            tc.ExecuteNonQuery("DELETE FROM ArrearProcess WHERE ArrearProcessID=%n", oAP.ID);
        }

        internal static void DeleteDetailItem(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID=%n", nID);
        }

        internal static void DeleteDetailItemByDetailID(TransactionContext tc, int arrearProcessDetailID,
            enumPayrollComponentType itemtype, int itemid)
        {
            tc.ExecuteNonQuery(
                "DELETE FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID=%n AND ItemType=%n AND itemID=%n",
                arrearProcessDetailID, itemtype, itemid);
        }

        internal static void DeleteDetailItemMonthlyByDetailID(TransactionContext tc, int arrearProcessDetailID,
            enumPayrollComponentType itemtype, int itemid)
        {
//            tc.ExecuteNonQuery(@"WITH cte AS (
//	                                        SELECT m.* FROM ARREARPROCESSDETAIL d 
//	                                        INNER JOIN ARREARPROCESSDETAILITEM a ON d.ARREARPROCESSDETAILID = a.ARREARPROCESSDETAILID
//	                                        INNER JOIN ARRPROSDETAILITEMMONTHLY m ON a.ARREARPROCESSDETAILITEMID = m.ARREARPROCESSDETAILITEMID
//	                                        WHERE d.ARREARPROCESSDETAILID = %n AND m.ITEMTYPE=%n AND m.ITEMID=%n
//                                            )
//                                        DELETE FROM cte
//                                        ", arrearProcessDetailID, itemtype, itemid);

            tc.ExecuteNonQuery(@"DELETE FROM ARRPROSDETAILITEMMONTHLY WHERE ARRPROSDETAILITEMMONTHLYID IN(

	                            SELECT m.ARRPROSDETAILITEMMONTHLYID FROM ARREARPROCESSDETAIL d 
	                            INNER JOIN ARREARPROCESSDETAILITEM a ON d.ARREARPROCESSDETAILID = a.ARREARPROCESSDETAILID
	                            INNER JOIN ARRPROSDETAILITEMMONTHLY m ON a.ARREARPROCESSDETAILITEMID = m.ARREARPROCESSDETAILITEMID
	                            WHERE d.ARREARPROCESSDETAILID = %n AND m.ITEMTYPE=%n AND m.ITEMID=%n
                            )", arrearProcessDetailID, itemtype, itemid);
        }

        internal static void DeleteTaxDetailItem(TransactionContext tc, int nID, enumPayrollComponentType type)
        {
            tc.ExecuteNonQuery("DELETE FROM ARREARPROCESSDETAILITEM WHERE ArrearprocessDetailID=%n AND ITEMTYPE", nID,
                type);
        }

        internal static void DeleteDetailItemMonthly(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM ARRPROSDETAILITEMMONTHLY WHERE ArrearprocessDetailItemID=%n", nID);
        }

        #endregion
    }
}