public int Insert_Record(byte[] byteCompressedDataSet)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);

            StringBuilder strQry      = new StringBuilder();
            int           intDeviceNo = 0;

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" MAX(DEVICE_NO) AS MAX_NO");
            strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");

            if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
            {
                intDeviceNo = 1;
            }
            else
            {
                intDeviceNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
            }

            strQry.Clear();
            strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE");
            strQry.AppendLine("(DEVICE_NO");
            strQry.AppendLine(",DEVICE_DESC");
            strQry.AppendLine(",DEVICE_USAGE");
            strQry.AppendLine(",TIME_ATTEND_CLOCK_FIRST_LAST_IND");
            strQry.AppendLine(",LOCK_OUT_MINUTES");
            strQry.AppendLine(",CLOCK_IN_OUT_PARM");
            strQry.AppendLine(",CLOCK_IN_RANGE_FROM");
            strQry.AppendLine(",CLOCK_IN_RANGE_TO");
            strQry.AppendLine(",COMPANY_NO");
            strQry.AppendLine(",LAN_WAN_IND");
            strQry.AppendLine(",FAR_REQUESTED)");

            strQry.AppendLine(" VALUES");

            strQry.AppendLine("(" + intDeviceNo);
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["DEVICE_DESC"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["DEVICE_USAGE"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["TIME_ATTEND_CLOCK_FIRST_LAST_IND"].ToString()));
            strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["LOCK_OUT_MINUTES"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_OUT_PARM"].ToString()));
            strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_RANGE_FROM"].ToString());
            strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_RANGE_TO"].ToString());
            strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["LAN_WAN_IND"].ToString()));
            strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["FAR_REQUESTED"].ToString() + ")");

            clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

            return(intDeviceNo);
        }
示例#2
0
        public byte[] Get_Form_Records(int parintCurrentUserNo, string parstrAccessInd)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            DataSet       DataSet = new DataSet();
            StringBuilder strQry  = new StringBuilder();

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" DEVICE_NO");
            strQry.AppendLine(",DEVICE_DESC");
            strQry.AppendLine(",DEVICE_USAGE");

            strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE");

            strQry.AppendLine(" ORDER BY ");
            strQry.AppendLine(" DEVICE_DESC");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Device");

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" C.COMPANY_NO");
            strQry.AppendLine(",C.COMPANY_DESC");
            strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");

            if (parstrAccessInd != "S")
            {
                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS  UC ");
                strQry.AppendLine(" ON UC.USER_NO = " + parintCurrentUserNo);
                strQry.AppendLine(" AND UC.COMPANY_NO = C.COMPANY_NO ");

                //2013-07-10
                strQry.AppendLine(" AND UC.COMPANY_ACCESS_IND = 'A'");
            }

            strQry.AppendLine(" ORDER BY C.COMPANY_DESC");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");

            if (DataSet.Tables["Company"].Rows.Count > 0)
            {
                byte[] bytTempCompress = Get_Company_Records(Convert.ToInt64(DataSet.Tables["Company"].Rows[0]["COMPANY_NO"]));

                DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
                DataSet.Merge(TempDataSet);
            }

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
示例#3
0
        public void Update_Record(Int64 parint64CompanyNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry = new StringBuilder();

            for (int intRow = 0; intRow < parDataSet.Tables["Employee"].Rows.Count; intRow++)
            {
                strQry.Clear();
                strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON");
                strQry.AppendLine(" SET ");
                strQry.AppendLine(" PREV_NORMAL_LEAVE_DAYS = " + Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["PREV_NORMAL_LEAVE_DAYS"]));
                strQry.AppendLine(",NORMAL_LEAVE_DAYS = " + Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["NORMAL_LEAVE_DAYS"]));
                strQry.AppendLine(",SICK_LEAVE_DAYS = " + Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["SICK_LEAVE_DAYS"]));
                strQry.AppendLine(",LEAVE_EFFECTIVE_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Employee"].Rows[intRow]["LEAVE_EFFECTIVE_DATE"]).ToString("yyyy-MM-dd") + "'");

                strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Employee"].Rows[intRow]["COMPANY_NO"]));
                strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"]));

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
        }
        public void Cleanup_Client_DataBase_Files(byte[] parByteArrayDataSet)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            DataSet parClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parByteArrayDataSet);

            StringBuilder strQry = new StringBuilder();

            for (int intRow = 0; intRow < parClientDataSet.Tables["FileToDelete"].Rows.Count; intRow++)
            {
                strQry.Clear();
                strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
                strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
                strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString()));

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                strQry.Clear();
                strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
                strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
                strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString()));

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
            }
        }
示例#5
0
        public void Update_PayCategory_Last_Upload_DateTime(Int64 parint64CompanyNo, string strUploadDateTime, byte[] parbyteDataSet)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            StringBuilder strQry        = new StringBuilder();
            DataSet       ClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            for (int intPayCategoryRow = 0; intPayCategoryRow < ClientDataSet.Tables["PayCategoryUpload"].Rows.Count; intPayCategoryRow++)
            {
                strQry.Clear();
                strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.PAY_CATEGORY");
                strQry.AppendLine(" SET LAST_UPLOAD_DATETIME = '" + strUploadDateTime + "'");
                strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);

                strQry.AppendLine(" AND PAY_CATEGORY_NO = " + ClientDataSet.Tables["PayCategoryUpload"].Rows[intPayCategoryRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(ClientDataSet.Tables["PayCategoryUpload"].Rows[intPayCategoryRow]["PAY_CATEGORY_TYPE"].ToString()));

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
            }
        }
        public byte[] Insert_Eti_Records(Int64 parInt64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo, string parstrDateTime, byte[] parbyteDataSet)
        {
            DataSet       parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
            StringBuilder strQry     = new StringBuilder();

            for (int intRow = 0; intRow < parDataSet.Tables["EtiEmployeeSave"].Rows.Count; intRow++)
            {
                strQry.Clear();

                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_ETI");

                strQry.AppendLine("(ETI_RUN_DATE");
                strQry.AppendLine(",COMPANY_NO");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",ETI_MONTH");
                strQry.AppendLine(",TOTAL_HOURS");
                strQry.AppendLine(",FACTOR");
                strQry.AppendLine(",TOTAL_EARNINGS");
                strQry.AppendLine(",ETI_EARNINGS");
                strQry.AppendLine(",ETI_VALUE)");

                strQry.AppendLine(" VALUES");

                strQry.AppendLine("('" + parstrDateTime + "'");
                strQry.AppendLine("," + parInt64CompanyNo);
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["ETI_MONTH"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["TOTAL_HOURS"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["FACTOR"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["TOTAL_EARNINGS"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["ETI_EARNINGS"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["ETI_VALUE"].ToString() + ")");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            byte[] bytCompress = Get_Form_Records(parInt64CompanyNo, parstrCurrentUserAccess, parint64CurrentUserNo);

            return(bytCompress);
        }
        public void Update_Employee_Link(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrMenuId, byte[] bytCompressedDataSet)
        {
            DataSet       DataSet      = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompressedDataSet);
            StringBuilder strQry       = new StringBuilder();
            string        strTableName = "";

            if (parstrMenuId == "3E")
            {
                strTableName = "DEPARTMENT";
            }
            else
            {
                strTableName = "OCCUPATION";
            }

            for (int intRow = 0; intRow < DataSet.Tables["Employee"].Rows.Count; intRow++)
            {
                strQry.Clear();
                strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE");
                strQry.AppendLine(" SET " + strTableName + "_NO = " + DataSet.Tables["Employee"].Rows[intRow][strTableName + "_NO"].ToString());
                strQry.AppendLine(",USER_NO_RECORD = " + parint64CurrentUserNo);
                strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND EMPLOYEE_NO = " + DataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine(" AND  DATETIME_DELETE_RECORD IS NULL ");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
        }
        public byte[] Get_Users(string parstrProgramFromInd, Int64 parCurrentUserNo, string parstrCurrentUserAccessInd)
        {
            StringBuilder strQry  = new StringBuilder();
            DataSet       DataSet = new DataSet();

            strQry.Clear();

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" U.USER_NO");
            strQry.AppendLine(",U.USER_ID");
            strQry.AppendLine(",U.FIRSTNAME");
            strQry.AppendLine(",U.SURNAME");

            strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");

            strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
            strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
            strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' ");
            strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");

            if (parstrCurrentUserAccessInd != "S")
            {
                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(SELECT ");
                strQry.AppendLine(" UCA.COMPANY_NO");

                strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");

                strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
                strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
                strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
                strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");

                strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
                strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");

                strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
            }

            strQry.AppendLine(" GROUP BY ");
            strQry.AppendLine(" U.USER_NO");
            strQry.AppendLine(",U.USER_ID");
            strQry.AppendLine(",U.FIRSTNAME");
            strQry.AppendLine(",U.SURNAME");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" U.USER_NO");
            strQry.AppendLine(",UCA.COMPANY_NO");

            strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");

            strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
            strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
            strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' ");
            strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");

            if (parstrCurrentUserAccessInd != "S")
            {
                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(SELECT ");
                strQry.AppendLine(" UCA.COMPANY_NO");

                strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");

                strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
                strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
                strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
                strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");

                strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
                strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");

                strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
            }

            strQry.AppendLine(" GROUP BY ");
            strQry.AppendLine(" U.USER_NO");
            strQry.AppendLine(",UCA.COMPANY_NO");

            strQry.AppendLine(" ORDER BY");
            strQry.AppendLine(" U.USER_NO");
            strQry.AppendLine(",UCA.COMPANY_NO");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserCompany", -1);

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" CL.COMPANY_NO");
            strQry.AppendLine(",CL.COMPANY_DESC");

            strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK CL");

            if (parstrCurrentUserAccessInd != "S")
            {
                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(SELECT ");
                strQry.AppendLine(" UCA.COMPANY_NO");

                strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");

                strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
                strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
                strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
                strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");

                strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
                strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");

                strQry.AppendLine(" ON CL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
            }

            strQry.AppendLine(" GROUP BY");
            strQry.AppendLine(" CL.COMPANY_NO");
            strQry.AppendLine(",CL.COMPANY_DESC");

            strQry.AppendLine(" ORDER BY");
            strQry.AppendLine(" CL.COMPANY_DESC");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1);

            if (DataSet.Tables["User"].Rows.Count > 0)
            {
                byte[]  bytTempCompress = Get_User_Menus(parstrProgramFromInd, Convert.ToInt32(DataSet.Tables["User"].Rows[0]["USER_NO"]));
                DataSet TempDataSet     = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
                DataSet.Merge(TempDataSet);
            }

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
示例#9
0
        public byte[] Update_Records(Int64 parInt64CompanyNo, byte[] parbyteDataSet, string parstrFromProgram)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
            DataSet DataSet    = new DataSet();

            StringBuilder strQry = new StringBuilder();

            if (parInt64CompanyNo == 999999)
            {
                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" COMPANY_NO");

                strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK ");

                clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Companies", -1);
            }

            strQry.Clear();

            for (int intRow = 0; intRow < parDataSet.Tables["PaidHoliday"].Rows.Count; intRow++)
            {
                if (parDataSet.Tables["PaidHoliday"].Rows[intRow].RowState == DataRowState.Added)
                {
                    strQry.Clear();

                    if (parInt64CompanyNo == 999999)
                    {
                        strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.PUBLIC_HOLIDAY");
                    }
                    else
                    {
                        strQry.AppendLine(" INSERT INTO  InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY ");
                    }

                    strQry.AppendLine("(PUBLIC_HOLIDAY_DATE");
                    strQry.AppendLine(",PUBLIC_HOLIDAY_DESC)");
                    strQry.AppendLine(" VALUES ");
                    strQry.AppendLine("('" + Convert.ToDateTime(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DATE"]).ToString("yyyy-MM-dd") + "'");
                    strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DESC"].ToString()) + ")");

                    clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

                    if (parInt64CompanyNo == 999999)
                    {
                        for (int intCompanyRow = 0; intCompanyRow < DataSet.Tables["Companies"].Rows.Count; intCompanyRow++)
                        {
                            Int64 Int64CompanyNo = Convert.ToInt64(DataSet.Tables["Companies"].Rows[intCompanyRow]["COMPANY_NO"]);

                            strQry.Clear();

                            strQry.AppendLine(" INSERT INTO  InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY ");

                            strQry.AppendLine("(PUBLIC_HOLIDAY_DATE");
                            strQry.AppendLine(",PUBLIC_HOLIDAY_DESC)");
                            strQry.AppendLine(" VALUES ");
                            strQry.AppendLine("('" + Convert.ToDateTime(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DESC"].ToString()) + ")");

                            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
                        }
                    }
                }
                else
                {
                    if (parDataSet.Tables["PaidHoliday"].Rows[intRow].RowState == DataRowState.Modified)
                    {
                        strQry.Clear();

                        if (parInt64CompanyNo == 999999)
                        {
                            strQry.AppendLine(" UPDATE InteractPayroll.dbo.PUBLIC_HOLIDAY");
                        }
                        else
                        {
                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY");
                        }

                        strQry.AppendLine(" SET ");
                        strQry.AppendLine(" PUBLIC_HOLIDAY_DESC = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DESC"].ToString()));
                        strQry.AppendLine(",PUBLIC_HOLIDAY_DATE = '" + Convert.ToDateTime(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DATE"]).ToString("yyyy-MM-dd") + "'");
                        strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_NO = " + parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_NO"].ToString());

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
                    }
                    else
                    {
                        //Deleted
                        strQry.Clear();

                        if (parInt64CompanyNo == 999999)
                        {
                            strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.PUBLIC_HOLIDAY");
                        }
                        else
                        {
                            strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY");
                        }

                        strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_NO = " + parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_NO", System.Data.DataRowVersion.Original].ToString());

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
                    }
                }
            }

            byte[] bytCompress = Get_Form_Records(parInt64CompanyNo, parstrFromProgram);
            DataSet.Dispose();
            DataSet = null;

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            return(bytCompress);
        }
        public byte[] Logon_User(string parstrUserInformation)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            bool          blnDebug = false;
            StringBuilder strQry   = new StringBuilder();

            string[] strPieces = parstrUserInformation.Split('|');

            string strUserId   = strPieces[0];
            string strPassword = strPieces[1];

            byte[] bytCompress;

            StreamWriter swStreamWriter;

            DataSet DataSet = new DataSet();

            DataTable DataTable = new DataTable("ReturnValues");

            DataTable.Columns.Add("USER_NO", typeof(Int64));
            DataTable.Columns.Add("RESET", typeof(String));
            DataTable.Columns.Add("ACCESS_IND", typeof(String));
            DataTable.Columns.Add("REBOOT_IND", typeof(String));
            DataTable.Columns.Add("MACHINE_NAME", typeof(String));
            DataTable.Columns.Add("MACHINE_IP", typeof(String));
            DataTable.Columns.Add("NO_USERS_IND", typeof(String));
            DataTable.Columns.Add("DOWNLOAD_IND", typeof(String));
            DataTable.Columns.Add("DB_CREATE_FOR_ENGINE", typeof(String));

            DataSet.Tables.Add(DataTable);

            DataRow myDataRow = DataSet.Tables["ReturnValues"].NewRow();

            myDataRow["USER_NO"]    = -1;
            myDataRow["RESET"]      = "N";
            myDataRow["ACCESS_IND"] = "";

            DataSet.Tables["ReturnValues"].Rows.Add(myDataRow);

            FileInfo fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Debug.txt");

            if (fiFile.Exists == true)
            {
                blnDebug = true;
            }

            fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");

            if (fiFile.Exists == true)
            {
                File.Delete(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
            }

            if (blnDebug == true)
            {
                swStreamWriter = fiFile.AppendText();

                swStreamWriter.WriteLine("Call clsDBConnectionObjects.GetSQLConnectionString");

                swStreamWriter.Close();
            }

            DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"] = clsDBConnectionObjects.GetSQLConnectionString();

            if (blnDebug == true)
            {
                swStreamWriter = fiFile.AppendText();

                swStreamWriter.WriteLine("After clsDBConnectionObjects.GetSQLConnectionString = " + DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"].ToString());

                swStreamWriter.Close();
            }

            if (clsDBConnectionObjects.GetSQLConnectionString() != "")
            {
                clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase();
                clsFixInteractPayrollClientDatabase.Fix_Client_Database();

                strQry.Clear();

                strQry.AppendLine(" SELECT");
                strQry.AppendLine(" UI.USER_NO");
                strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
                strQry.AppendLine(",UI.RESET");
                //A = Administrator, U = User
                strQry.AppendLine(",MIN(UC.COMPANY_ACCESS_IND) AS MIN_COMPANY_ACCESS_IND");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC");
                strQry.AppendLine(" ON UI.USER_NO = UC.USER_NO");

                strQry.AppendLine(" WHERE UI.USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(strUserId));
                strQry.AppendLine(" AND UI.PASSWORD = "******" GROUP BY ");
                strQry.AppendLine(" UI.USER_NO");
                strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
                strQry.AppendLine(",UI.RESET");

                try
                {
                    clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
                }
                catch (Exception ex)
                {
                    swStreamWriter = fiFile.AppendText();

                    swStreamWriter.WriteLine("Exception = " + ex.Message);

                    swStreamWriter.Close();
                }

                if (blnDebug == true)
                {
                    swStreamWriter = fiFile.AppendText();

                    swStreamWriter.WriteLine("After Temp = Count = " + DataSet.Tables["Temp"].Rows.Count.ToString());

                    swStreamWriter.Close();
                }

                if (DataSet.Tables["Temp"].Rows.Count == 0)
                {
                    DataSet.Tables.Remove(DataSet.Tables["Temp"]);

                    strQry.Clear();
                    strQry.AppendLine(" SELECT");
                    strQry.AppendLine(" USER_NO");

                    strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID");

                    clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserTemp");

                    if (DataSet.Tables["UserTemp"].Rows.Count > 0)
                    {
                        DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);

                        if ((strUserId == "INTERACT" &&
                             strPassword == "TCARETNI") ||
                            (strUserId == "HELENALR" &&
                             strPassword == "MONSTER"))
                        {
                            DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"]    = 0;
                            DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
                        }
                        else
                        {
                            DataSet.AcceptChanges();

                            //Not Found
                            bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
                            DataSet.Dispose();
                            DataSet = null;

                            return(bytCompress);
                        }
                    }
                    else
                    {
                        DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);

                        if ((strUserId == "INTERACT" &&
                             strPassword == "TCARETNI") ||
                            (strUserId == "HELENALR" &&
                             strPassword == "MONSTER"))
                        {
                            DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"]    = 0;
                            DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
                        }
                        else
                        {
                            DataSet.Tables["ReturnValues"].Rows[0]["NO_USERS_IND"] = "Y";

                            DataSet.AcceptChanges();

                            bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
                            DataSet.Dispose();
                            DataSet = null;

                            return(bytCompress);
                        }
                    }
                }
                else
                {
                    if (DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString() == "S")
                    {
                        DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString();
                    }
                    else
                    {
                        DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["MIN_COMPANY_ACCESS_IND"].ToString();
                    }

                    if (DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"].ToString() != "S")
                    {
                        //Create Temp Table for Joins
                        strQry.Clear();
                        strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP");
                        strQry.AppendLine(" WHERE USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());

                        clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                        strQry.Clear();
                        strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
                        strQry.AppendLine("(USER_NO");
                        strQry.AppendLine(",COMPANY_NO");
                        strQry.AppendLine(",EMPLOYEE_NO");
                        strQry.AppendLine(",PAY_CATEGORY_NO");
                        strQry.AppendLine(",PAY_CATEGORY_TYPE)");

                        strQry.AppendLine(" SELECT ");

                        strQry.AppendLine(" " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
                        strQry.AppendLine(",USER_TABLE.COMPANY_NO");
                        strQry.AppendLine(",USER_TABLE.EMPLOYEE_NO");
                        strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_NO");
                        strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_TYPE");

                        strQry.AppendLine(" FROM ");

                        strQry.AppendLine("(SELECT ");
                        strQry.AppendLine(" E.COMPANY_NO");
                        strQry.AppendLine(",E.EMPLOYEE_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");

                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
                        strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
                        strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
                        strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY_DEPARTMENT UPCD");
                        strQry.AppendLine(" ON E.COMPANY_NO = UPCD.COMPANY_NO");
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPCD.PAY_CATEGORY_NO ");
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPCD.PAY_CATEGORY_TYPE ");
                        strQry.AppendLine(" AND E.DEPARTMENT_NO = UPCD.DEPARTMENT_NO ");
                        strQry.AppendLine(" AND UPCD.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());

                        strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");

                        strQry.AppendLine(" UNION ");

                        strQry.AppendLine(" SELECT ");
                        strQry.AppendLine(" E.COMPANY_NO");
                        strQry.AppendLine(",E.EMPLOYEE_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");

                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
                        strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
                        strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
                        strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_EMPLOYEE UE");
                        strQry.AppendLine(" ON E.COMPANY_NO = UE.COMPANY_NO");
                        strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UE.EMPLOYEE_NO ");
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UE.PAY_CATEGORY_TYPE ");
                        strQry.AppendLine(" AND UE.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());

                        strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");

                        strQry.AppendLine(" UNION ");

                        strQry.AppendLine(" SELECT ");
                        strQry.AppendLine(" E.COMPANY_NO");
                        strQry.AppendLine(",E.EMPLOYEE_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");

                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
                        strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
                        strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
                        strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY UPC");
                        strQry.AppendLine(" ON E.COMPANY_NO = UPC.COMPANY_NO");
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPC.PAY_CATEGORY_NO ");
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPC.PAY_CATEGORY_TYPE ");
                        strQry.AppendLine(" AND UPC.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());

                        strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL ");

                        strQry.AppendLine(" UNION ");

                        strQry.AppendLine(" SELECT ");
                        strQry.AppendLine(" E.COMPANY_NO");
                        strQry.AppendLine(",E.EMPLOYEE_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
                        strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");

                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
                        strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
                        strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
                        strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                        strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
                        strQry.AppendLine(" ON UCA.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
                        strQry.AppendLine(" AND E.COMPANY_NO = UCA.COMPANY_NO");
                        //2013-07-10
                        strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A'");

                        strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL) AS USER_TABLE");

                        clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
                    }

                    DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]);
                    DataSet.Tables["ReturnValues"].Rows[0]["RESET"]   = DataSet.Tables["Temp"].Rows[0]["RESET"].ToString();

                    //Set Time Logged On
                    strQry.Clear();
                    strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.USER_ID");
                    strQry.AppendLine(" SET LAST_TIME_ON = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");

                    strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]));

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
                }

                //2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
                strQry.Clear();
                strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");

                strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                //2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
                strQry.Clear();
                strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");

                strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                //2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
                strQry.Clear();
                strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS_TEMP");

                strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                strQry.Clear();
                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" FCDD.FILE_LAYER_IND");
                strQry.AppendLine(",FCDD.FILE_NAME");
                strQry.AppendLine(",FCDD.FILE_SIZE");
                strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
                strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
                strQry.AppendLine(",FCDD.FILE_CRC_VALUE");

                strQry.AppendLine(",ISNULL(MAX(FCDC.FILE_CHUNK_NO),0) AS MAX_FILE_CHUNK_NO");

                strQry.AppendLine(",'' AS DOWNLOAD_IND");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS FCDD");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS FCDC");
                strQry.AppendLine(" ON FCDD.FILE_LAYER_IND = FCDC.FILE_LAYER_IND");
                strQry.AppendLine(" AND  FCDD.FILE_NAME = FCDC.FILE_NAME");

                strQry.AppendLine(" WHERE FCDD.FILE_LAYER_IND = 'P'");

                strQry.AppendLine(" GROUP BY ");
                strQry.AppendLine(" FCDD.FILE_LAYER_IND");
                strQry.AppendLine(",FCDD.FILE_NAME");
                strQry.AppendLine(",FCDD.FILE_SIZE");
                strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
                strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
                strQry.AppendLine(",FCDD.FILE_CRC_VALUE");

                strQry.AppendLine(" ORDER BY ");
                strQry.AppendLine(" 2");

                clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");

                bytCompress = (byte[])Check_Reboot_Server();

                DataSet DataSetTemp = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompress);

                if (DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString() == "Y")
                {
                    DataSet.Tables["ReturnValues"].Rows[0]["REBOOT_IND"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString();

                    DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"].ToString();
                    DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_IP"]   = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_IP"].ToString();
                }
            }

            bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
示例#11
0
        public byte[] Insert_New_Record(Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
        {
            StringBuilder strQry = new StringBuilder();

            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
            DataSet DataSet    = new DataSet();

            //Reset Value
            parDataSet.Tables["User"].Rows[0]["RETURN_CODE"] = "";

            strQry.Clear();

            strQry.AppendLine(" SELECT");

            strQry.AppendLine(" USER_ID");

            strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");

            strQry.AppendLine(" WHERE USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_ID"].ToString()));

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserId", -1);

            if (DataSet.Tables["UserId"].Rows.Count > 0)
            {
                parDataSet.Tables["User"].Rows[0]["RETURN_CODE"] = 9999;

                goto Insert_New_Record_Continue;
            }

            strQry.Clear();

            strQry.AppendLine(" SELECT ");

            strQry.AppendLine(" USER_NO");

            strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");

            strQry.AppendLine(" WHERE USER_CLOCK_PIN = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_CLOCK_PIN"].ToString()));

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), parDataSet, "UserPinCheck", -1);

            if (parDataSet.Tables["UserPinCheck"].Rows.Count > 0)
            {
                goto Insert_New_Record_Continue;
            }

            strQry.Clear();

            strQry.AppendLine(" SELECT");

            strQry.AppendLine(" MAX(USER_NO) AS MAX_USER_NO");

            strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", -1);

            if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_USER_NO") == true)
            {
                parDataSet.Tables["User"].Rows[0]["USER_NO"] = 1;
            }
            else
            {
                parDataSet.Tables["User"].Rows[0]["USER_NO"] = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_USER_NO"]) + 1;
            }

            System.Random RandomNumber = new System.Random();

            parDataSet.Tables["User"].Rows[0]["PASSWORD"] = RandomNumber.Next(1000, 999999).ToString();

            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_ID ");
            strQry.AppendLine("(USER_NO");
            strQry.AppendLine(",USER_ID");
            strQry.AppendLine(",FIRSTNAME");
            strQry.AppendLine(",SURNAME");
            strQry.AppendLine(",PASSWORD");
            strQry.AppendLine(",RESET");
            strQry.AppendLine(",LAST_TIME_ON");
            strQry.AppendLine(",EMAIL");
            //2017-05-09
            strQry.AppendLine(",USER_CLOCK_PIN");
            strQry.AppendLine(",SYSTEM_ADMINISTRATOR_IND");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",USER_NO_NEW_RECORD)");

            strQry.AppendLine(" VALUES");

            strQry.AppendLine("(" + parDataSet.Tables["User"].Rows[0]["USER_NO"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_ID"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["FIRSTNAME"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["SURNAME"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["PASSWORD"].ToString()));
            strQry.AppendLine(",'Y'");
            strQry.AppendLine(",Null");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["EMAIL"].ToString()));
            //2017-05-09
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_CLOCK_PIN"].ToString()));
            strQry.AppendLine(",'N'");
            strQry.AppendLine(",GETDATE()");
            strQry.AppendLine("," + parint64CurrentUserNo + ")");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            for (int intRow = 0; intRow < parDataSet.Tables["CompanyAccess"].Rows.Count; intRow++)
            {
                parDataSet.Tables["CompanyAccess"].Rows[intRow]["USER_NO"] = parDataSet.Tables["User"].Rows[0]["USER_NO"].ToString();

                strQry.Clear();

                strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_COMPANY_ACCESS ");
                strQry.AppendLine("(USER_NO");
                strQry.AppendLine(",COMPANY_NO");
                strQry.AppendLine(",TIE_BREAKER");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",COMPANY_ACCESS_IND");
                strQry.AppendLine(",ACCESS_LAYER_IND");
                strQry.AppendLine(",DATETIME_NEW_RECORD");
                strQry.AppendLine(",USER_NO_NEW_RECORD)");
                strQry.AppendLine(" VALUES");
                strQry.AppendLine("(" + parDataSet.Tables["User"].Rows[0]["USER_NO"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["CompanyAccess"].Rows[intRow]["COMPANY_NO"].ToString());
                strQry.AppendLine(",1");
                //No Link to An Employee (Used For User Number = Employee Number)
                strQry.AppendLine(",-1");
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["CompanyAccess"].Rows[intRow]["COMPANY_ACCESS_IND"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["CompanyAccess"].Rows[intRow]["ACCESS_LAYER_IND"].ToString()));
                strQry.AppendLine(",GETDATE()");
                strQry.AppendLine("," + parint64CurrentUserNo + ")");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
            }

            if (parDataSet.Tables["UserFingerTemplate"].Rows.Count > 0)
            {
                Save_User_FingerTemplate(parDataSet.Tables["UserFingerTemplate"], Convert.ToInt64(parDataSet.Tables["User"].Rows[0]["USER_NO"]));
            }

Insert_New_Record_Continue:

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(parDataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
        public int Insert_New_Record(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry     = new StringBuilder();
            int           intLeaveNo = -1;

            DataSet DataSet = new DataSet();

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" MAX(LEAVE_SHIFT_NO) AS MAX_NO");
            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables[0].Rows[0]["COMPANY_NO"].ToString());

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);

            if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
            {
                intLeaveNo = 1;
            }
            else
            {
                intLeaveNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
            }

            DataSet.Dispose();
            DataSet = null;

            strQry.Clear();
            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",LEAVE_SHIFT_NO");
            strQry.AppendLine(",LEAVE_SHIFT_DESC");
            strQry.AppendLine(",MIN_VALID_SHIFT_MINUTES");
            strQry.AppendLine(",MAX_SHIFTS_YEAR");
            strQry.AppendLine(",NORM_PAID_DAYS");
            strQry.AppendLine(",SICK_PAID_DAYS");
            strQry.AppendLine(",NORM_PAID_PER_PERIOD");
            strQry.AppendLine(",SICK_PAID_PER_PERIOD");
            strQry.AppendLine(",LEAVE_PAID_ACCUMULATOR_IND");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",LEAVE_SHIFT_DEL_IND)");
            strQry.AppendLine(" VALUES ");
            strQry.AppendLine("(" + parDataSet.Tables[0].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
            strQry.AppendLine("," + intLeaveNo);
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["LEAVE_SHIFT_DESC"].ToString()));
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["MIN_VALID_SHIFT_MINUTES"].ToString());
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["MAX_SHIFTS_YEAR"].ToString());
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["NORM_PAID_DAYS"].ToString());
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["SICK_PAID_DAYS"].ToString());
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["NORM_PAID_PER_PERIOD"].ToString());
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["SICK_PAID_PER_PERIOD"].ToString());
            strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["LEAVE_PAID_ACCUMULATOR_IND"].ToString());
            strQry.AppendLine(",GETDATE()");
            strQry.AppendLine("," + parint64CurrentUserNo);
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["LEAVE_SHIFT_DEL_IND"].ToString()) + ")");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            parDataSet.Dispose();
            parDataSet = null;

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            return(intLeaveNo);
        }
        public int Update_Record(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo, string parstrPayrollType, byte[] parbyteDataSet, Int64 parint64CompanyNo)
        {
            int     intReturnCode = 0;
            DataSet DataSet       = new DataSet();
            DataSet parDataSet    = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry = new StringBuilder();

            strQry.Clear();
            strQry.AppendLine(" SELECT");

            if (parstrPayrollType == "W")
            {
                strQry.AppendLine(" WAGE_RUN_IND AS RUN_IND");
            }
            else
            {
                if (parstrPayrollType == "S")
                {
                    strQry.AppendLine(" SALARY_RUN_IND AS RUN_IND ");
                }
            }

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
            strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "CompanyCheck", parint64CompanyNo);

            if (DataSet.Tables["CompanyCheck"].Rows[0]["RUN_IND"].ToString() == "")
            {
                for (int intRow = 0; intRow < parDataSet.Tables["Leave"].Rows.Count; intRow++)
                {
                    if (parDataSet.Tables["Leave"].Rows[intRow].RowState == DataRowState.Deleted)
                    {
                        strQry.Clear();
                        strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT");
                        strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO", System.Data.DataRowVersion.Original]));
                        strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO", System.Data.DataRowVersion.Original]));
                        strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE", System.Data.DataRowVersion.Original].ToString()));
                        strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO", System.Data.DataRowVersion.Original]));
                        strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO", System.Data.DataRowVersion.Original]));

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);

                        strQry.Clear();
                        strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT");
                        strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO", System.Data.DataRowVersion.Original]));
                        strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO", System.Data.DataRowVersion.Original]));
                        strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE", System.Data.DataRowVersion.Original].ToString()));
                        strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO", System.Data.DataRowVersion.Original]));
                        strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO", System.Data.DataRowVersion.Original]));

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
                    }
                    else
                    {
                        strQry.Clear();
                        strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT");
                        strQry.AppendLine(" SET ");
                        strQry.AppendLine(" PROCESS_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["PROCESS_NO"]));
                        strQry.AppendLine(",LEAVE_DESC = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_DESC"].ToString()));
                        strQry.AppendLine(",LEAVE_FROM_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_FROM_DATE"]).ToString("yyyy-MM-dd") + "'");
                        strQry.AppendLine(",LEAVE_TO_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_TO_DATE"]).ToString("yyyy-MM-dd") + "'");
                        strQry.AppendLine(",LEAVE_DAYS_DECIMAL = " + Convert.ToDouble(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_DAYS_DECIMAL"]));
                        strQry.AppendLine(",LEAVE_OPTION = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_OPTION"].ToString()));

                        if (parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_HOURS_DECIMAL"] != System.DBNull.Value)
                        {
                            strQry.AppendLine(",LEAVE_HOURS_DECIMAL = " + Convert.ToDouble(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_HOURS_DECIMAL"]));
                        }

                        strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO"]));
                        strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO"]));
                        strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                        strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
                        strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);

                        if (Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["PROCESS_NO"]) == 0)
                        {
                            //Insert
                            strQry.Clear();
                            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT");
                            strQry.AppendLine("(COMPANY_NO");
                            strQry.AppendLine(",EMPLOYEE_NO");
                            strQry.AppendLine(",PAY_CATEGORY_NO");
                            strQry.AppendLine(",PAY_CATEGORY_TYPE");
                            strQry.AppendLine(",EARNING_NO");
                            strQry.AppendLine(",LEVEL_NO");
                            strQry.AppendLine(",LEAVE_REC_NO");
                            strQry.AppendLine(",USER_NO");
                            strQry.AppendLine(",AUTHORISED_IND)");

                            strQry.AppendLine(" SELECT DISTINCT");
                            strQry.AppendLine(" EPC.COMPANY_NO");
                            strQry.AppendLine(",EPC.EMPLOYEE_NO");
                            strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
                            strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
                            strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
                            strQry.AppendLine(",PCA.LEVEL_NO");
                            strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));
                            strQry.AppendLine(",PCA.USER_NO");

                            strQry.AppendLine(",'N'");

                            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");

                            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
                            strQry.AppendLine(" ON EPC.COMPANY_NO = E.COMPANY_NO");
                            strQry.AppendLine(" AND EPC.EMPLOYEE_NO = E.EMPLOYEE_NO");
                            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
                            //Employee Has Been Activates (Taken-On)
                            strQry.AppendLine(" AND E.EMPLOYEE_TAKEON_IND = 'Y'");
                            //Employee NOT Closed
                            strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
                            strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");

                            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_AUTHORISE PCA");
                            strQry.AppendLine(" ON EPC.COMPANY_NO = PCA.COMPANY_NO");
                            strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PCA.PAY_CATEGORY_NO");
                            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PCA.PAY_CATEGORY_TYPE");
                            //T = Timesheet, L = Leave
                            strQry.AppendLine(" AND PCA.AUTHORISE_TYPE_IND = 'L'");
                            strQry.AppendLine(" AND PCA.DATETIME_DELETE_RECORD IS NULL");

                            if (parstrCurrentUserAccess == "U")
                            {
                                strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
                                strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
                                strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO");
                                strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
                                strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
                                strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
                            }

                            strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT EPCLAC");
                            strQry.AppendLine(" ON EPC.COMPANY_NO = EPCLAC.COMPANY_NO");
                            strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = EPCLAC.PAY_CATEGORY_NO");
                            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = EPCLAC.PAY_CATEGORY_TYPE");
                            strQry.AppendLine(" AND EPCLAC.EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
                            strQry.AppendLine(" AND PCA.LEVEL_NO = EPCLAC.LEVEL_NO");
                            strQry.AppendLine(" AND EPCLAC.LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));
                            strQry.AppendLine(" AND EPCLAC.USER_NO = " + parint64CurrentUserNo.ToString());

                            strQry.AppendLine(" WHERE EPC.COMPANY_NO = " + parint64CompanyNo);
                            strQry.AppendLine(" AND EPC.EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO"]));

                            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
                            //Default is Link for LEAVE
                            strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
                            strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");

                            //No Record Currently Exists
                            strQry.AppendLine(" AND EPCLAC.COMPANY_NO IS NULL");

                            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
                        }
                        else
                        {
                            strQry.Clear();
                            strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT");
                            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO"].ToString());
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
                            strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));

                            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
                        }
                    }
                }
            }
            else
            {
                intReturnCode = 1;
            }

            strQry.Clear();
            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            DataSet.Dispose();
            DataSet = null;

            return(intReturnCode);
        }
        public int Insert_New_Record(Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry         = new StringBuilder();
            int           intDeductionNo = -1;
            Int64         Int64CompanyNo = Convert.ToInt64(parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"]);

            DataSet DataSet = new DataSet();

            strQry.Clear();

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" MAX(DEDUCTION_NO) AS MAX_NO");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION");

            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", Int64CompanyNo);

            if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
            {
                intDeductionNo = 200;
            }
            else
            {
                if (Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) < 199)
                {
                    intDeductionNo = 200;
                }
                else
                {
                    intDeductionNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
                }
            }

            for (int intRow = 0; intRow < parDataSet.Tables["EarningPercentage"].Rows.Count; intRow++)
            {
                parDataSet.Tables["EarningPercentage"].Rows[intRow]["DEDUCTION_NO"] = intDeductionNo;
            }

            DataSet.Dispose();
            DataSet = null;

            for (int intRow = 1; intRow <= Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]); intRow++)
            {
                strQry.Clear();

                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",DEDUCTION_NO");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                strQry.AppendLine(",DEDUCTION_DESC");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
                strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
                strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
                strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
                strQry.AppendLine(",USER_NO_NEW_RECORD");
                strQry.AppendLine(",DATETIME_NEW_RECORD");
                strQry.AppendLine(",DEDUCTION_DEL_IND)");
                strQry.AppendLine(" VALUES");
                strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
                strQry.AppendLine("," + intDeductionNo);
                strQry.AppendLine("," + intRow);
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_DESC"].ToString()));
                strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_LOAN_TYPE_IND"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER1"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER2"].ToString()));
                strQry.AppendLine("," + parint64CurrentUserNo);
                strQry.AppendLine(",GETDATE()");
                strQry.AppendLine(",'Y')");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

                strQry.Clear();

                //2017-02-24 Insert Record for opposite PAY_CATEGORY_TYPE
                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",DEDUCTION_NO");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                strQry.AppendLine(",DEDUCTION_DESC");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
                strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
                strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
                strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
                strQry.AppendLine(",USER_NO_NEW_RECORD");
                strQry.AppendLine(",DATETIME_NEW_RECORD");
                strQry.AppendLine(",DEDUCTION_DEL_IND)");
                strQry.AppendLine(" VALUES");
                strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());

                if (parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    strQry.AppendLine(",'S'");
                }
                else
                {
                    strQry.AppendLine(",'W'");
                }

                strQry.AppendLine("," + intDeductionNo);
                strQry.AppendLine("," + intRow);
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_DESC"].ToString()));
                strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_LOAN_TYPE_IND"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER1"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER2"].ToString()));
                strQry.AppendLine("," + parint64CurrentUserNo);
                strQry.AppendLine(",GETDATE()");
                strQry.AppendLine(",'Y')");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

                strQry.Clear();

                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",DEDUCTION_NO");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                strQry.AppendLine(",TIE_BREAKER");
                strQry.AppendLine(",DEDUCTION_TYPE_IND");
                strQry.AppendLine(",DEDUCTION_VALUE");
                strQry.AppendLine(",DEDUCTION_PERIOD_IND");
                strQry.AppendLine(",DEDUCTION_DAY_VALUE");
                strQry.AppendLine(",DEDUCTION_MIN_VALUE");
                strQry.AppendLine(",DEDUCTION_MAX_VALUE");
                strQry.AppendLine(",DATETIME_NEW_RECORD");
                strQry.AppendLine(",USER_NO_NEW_RECORD)");
                strQry.AppendLine(" VALUES");
                strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
                strQry.AppendLine(",0");
                strQry.AppendLine("," + intDeductionNo);
                strQry.AppendLine("," + intRow);
                strQry.AppendLine(",1");
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_TYPE_IND"].ToString()));
                strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_VALUE"].ToString());
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_PERIOD_IND"].ToString()));
                strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_DAY_VALUE"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_MIN_VALUE"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_MAX_VALUE"].ToString());
                strQry.AppendLine(",GETDATE()");
                strQry.AppendLine("," + parint64CurrentUserNo + ")");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

                strQry.Clear();

                //2017-02-24 Insert Record for opposite PAY_CATEGORY_TYPE
                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",DEDUCTION_NO");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                strQry.AppendLine(",TIE_BREAKER");
                strQry.AppendLine(",DEDUCTION_TYPE_IND");
                strQry.AppendLine(",DEDUCTION_VALUE");
                strQry.AppendLine(",DEDUCTION_PERIOD_IND");
                strQry.AppendLine(",DEDUCTION_DAY_VALUE");
                strQry.AppendLine(",DEDUCTION_MIN_VALUE");
                strQry.AppendLine(",DEDUCTION_MAX_VALUE");
                strQry.AppendLine(",DATETIME_NEW_RECORD");
                strQry.AppendLine(",USER_NO_NEW_RECORD)");
                strQry.AppendLine(" VALUES");
                strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());

                if (parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    strQry.AppendLine(",'S'");
                }
                else
                {
                    strQry.AppendLine(",'W'");
                }

                strQry.AppendLine(",0");
                strQry.AppendLine("," + intDeductionNo);
                strQry.AppendLine("," + intRow);
                strQry.AppendLine(",1");
                strQry.AppendLine(",'U'");
                strQry.AppendLine(",0");
                strQry.AppendLine(",'E'");
                strQry.AppendLine(",0");
                strQry.AppendLine(",0");
                strQry.AppendLine(",0");
                strQry.AppendLine(",GETDATE()");
                strQry.AppendLine("," + parint64CurrentUserNo + ")");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
            }

            if (parDataSet.Tables["EarningPercentage"].Rows.Count > 0)
            {
                Save_Deduction_Earning_Link(Int64CompanyNo, parint64CurrentUserNo, parDataSet.Tables["EarningPercentage"], Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]));
            }

            parDataSet.Dispose();
            parDataSet = null;

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + Int64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            return(intDeductionNo);
        }
        public byte[] Get_Form_Records(Int64 parInt64CompanyNo)
        {
            DataSet DataSet = new DataSet();
            DataSet TempDataSet;

            string        strEmployeeNoIn          = "-1";
            StringBuilder strFieldNamesInitialised = new StringBuilder();
            StringBuilder strQry = new StringBuilder();

            //Insert Records
            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EDH.EMPLOYEE_NO");
            strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
            strQry.AppendLine(",SUM(EDH.TOTAL)");
            strQry.AppendLine(",SUM(EEH.TOTAL)");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY EDH ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY EEH ");
            strQry.AppendLine(" ON EDH.COMPANY_NO = EEH.COMPANY_NO ");
            strQry.AppendLine(" AND EDH.PAY_PERIOD_DATE = EEH.PAY_PERIOD_DATE ");
            strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = EEH.PAY_CATEGORY_TYPE ");
            strQry.AppendLine(" AND EDH.EMPLOYEE_NO = EEH.EMPLOYEE_NO ");
            strQry.AppendLine(" AND EDH.RUN_TYPE = EEH.RUN_TYPE ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
            strQry.AppendLine(" ON EDH.COMPANY_NO = E.COMPANY_NO ");
            strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");

            //2013-06-21 Exclude T=Time Attendance
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S') ");

            strQry.AppendLine(" AND EDH.EMPLOYEE_NO = E.EMPLOYEE_NO ");
            strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL ");

            strQry.AppendLine(" INNER JOIN ");

            strQry.AppendLine("(SELECT ");
            strQry.AppendLine(" PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EMPLOYEE_NO");
            strQry.AppendLine(",MAX(PAY_PERIOD_DATE) AS MAX_PAY_PERIOD_DATE");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY ");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            //2013-06-21 Exclude T=Time Attendance
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S') ");

            strQry.AppendLine(" GROUP BY ");
            strQry.AppendLine(" PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EMPLOYEE_NO) AS TEMP_EMPLOYEE_DATE ");

            strQry.AppendLine(" ON EDH.PAY_CATEGORY_TYPE = TEMP_EMPLOYEE_DATE.PAY_CATEGORY_TYPE ");
            strQry.AppendLine(" AND EDH.EMPLOYEE_NO = TEMP_EMPLOYEE_DATE.EMPLOYEE_NO ");

            //Add 180 Days (Withinn 6 Months)
            strQry.AppendLine(" AND DATEADD(DD,180,EDH.PAY_PERIOD_DATE) >= MAX_PAY_PERIOD_DATE ");

            strQry.AppendLine(" WHERE EDH.COMPANY_NO = " + parInt64CompanyNo);

            //2013-06-21 Exclude T=Time Attendance
            strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE IN ('W','S') ");

            strQry.AppendLine(" AND EDH.RUN_TYPE = 'T'");

            strQry.AppendLine(" GROUP BY ");
            strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE ");
            strQry.AppendLine(",EDH.EMPLOYEE_NO ");
            strQry.AppendLine(",EDH.PAY_PERIOD_DATE");

            //Exclude where Already Have Take-On Values
            strQry.AppendLine(" HAVING SUM(EDH.TOTAL) = 0");
            strQry.AppendLine(" AND SUM(EEH.TOTAL) = 0 ");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeTemp", parInt64CompanyNo);

            for (int intRow = 0; intRow < DataSet.Tables["EmployeeTemp"].Rows.Count; intRow++)
            {
                if (intRow == 0)
                {
                    strEmployeeNoIn = DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
                }
                else
                {
                    strEmployeeNoIn += "," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
                }

                strQry.Clear();
                strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",RUN_TYPE");
                strQry.AppendLine(",DEDUCTION_NO");
                strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                strQry.AppendLine(",RUN_NO");

                strFieldNamesInitialised.Clear();

                //Return strings for field that need to be Initialised to Zero
                clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_DEDUCTION_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);

                strQry.AppendLine(")");

                strQry.AppendLine(" SELECT");
                strQry.AppendLine(" ED.COMPANY_NO");
                strQry.AppendLine(",ED.PAY_CATEGORY_TYPE");
                strQry.AppendLine(",ED.EMPLOYEE_NO");
                strQry.AppendLine(",'T'");
                strQry.AppendLine(",ED.DEDUCTION_NO ");
                strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
                strQry.AppendLine(",1");

                strQry.Append(strFieldNamesInitialised);

                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");

                strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT EDC");
                strQry.AppendLine(" ON ED.COMPANY_NO = EDC.COMPANY_NO ");
                strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = EDC.PAY_CATEGORY_TYPE ");
                strQry.AppendLine(" AND ED.EMPLOYEE_NO = EDC.EMPLOYEE_NO ");
                strQry.AppendLine(" AND ED.DEDUCTION_NO = EDC.DEDUCTION_NO ");
                strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = EDC.DEDUCTION_SUB_ACCOUNT_NO ");
                strQry.AppendLine(" AND EDC.RUN_TYPE = 'T' ");

                strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
                strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
                strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");

                //No EMPLOYEE_DEDUCTION_HISTORY Record Exists
                strQry.AppendLine(" AND EDC.COMPANY_NO IS NULL ");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

                strQry.Clear();
                strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                //NB PAY_CATEGORY_NO will be Set to Zero
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",RUN_TYPE");
                strQry.AppendLine(",EARNING_NO");

                //ELR 2014-05-24
                strQry.AppendLine(",EARNING_TYPE_IND");

                strQry.AppendLine(",RUN_NO");

                strFieldNamesInitialised.Clear();

                //Return strings for field that need to be Initialised to Zero
                clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);

                strQry.AppendLine(")");

                strQry.AppendLine(" SELECT");
                strQry.AppendLine(" EN.COMPANY_NO");
                strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
                strQry.AppendLine("," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine(",'T'");
                strQry.AppendLine(",EN.EARNING_NO ");

                //ELR 2014-05-24
                strQry.AppendLine(",'U'");
                strQry.AppendLine(",1");

                //Append Initialised Numeric Fields Names
                strQry.Append(strFieldNamesInitialised);

                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");

                strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C ");
                strQry.AppendLine(" ON EN.COMPANY_NO = C.COMPANY_NO ");
                strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");

                strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
                strQry.AppendLine(" ON EN.COMPANY_NO = EEC.COMPANY_NO ");
                strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
                strQry.AppendLine(" AND EN.EARNING_NO = EEC.EARNING_NO ");
                strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");

                strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parInt64CompanyNo);
                strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");

                strQry.AppendLine(" AND (EN.EARNING_NO IN (1,2,7,8,9)");

                if (DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    //Normal Leave / Sick Leave
                    strQry.AppendLine(" OR (EN.EARNING_NO IN (200,201))");
                    strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.OVERTIME1_RATE > 0)");
                    strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.OVERTIME2_RATE > 0)");
                    strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.OVERTIME3_RATE > 0))");
                }
                else
                {
                    strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.SALARY_OVERTIME1_RATE > 0)");
                    strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.SALARY_OVERTIME2_RATE > 0)");
                    strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.SALARY_OVERTIME3_RATE > 0))");
                }

                strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");

                //No EMPLOYEE_EARNING_HISTORY Record Exists
                strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

                strQry.Clear();
                strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",RUN_TYPE");
                strQry.AppendLine(",EARNING_NO");

                //ELR 2014-05-24
                strQry.AppendLine(",EARNING_TYPE_IND");

                strQry.AppendLine(",RUN_NO");

                strFieldNamesInitialised.Clear();

                //Return strings for field that need to be Initialised to Zero
                clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);

                strQry.AppendLine(")");

                strQry.AppendLine(" SELECT");
                strQry.AppendLine(" EE.COMPANY_NO");
                strQry.AppendLine(",EE.PAY_CATEGORY_TYPE");
                strQry.AppendLine(",EE.EMPLOYEE_NO");
                strQry.AppendLine(",'T'");
                strQry.AppendLine(",EE.EARNING_NO ");

                //ELR 2014-05-24
                strQry.AppendLine(",EE.EARNING_TYPE_IND");

                strQry.AppendLine(",1");

                //Append Initialised Numeric Fields Names
                strQry.Append(strFieldNamesInitialised);

                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");

                strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
                strQry.AppendLine(" ON EE.COMPANY_NO = EEC.COMPANY_NO ");
                strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
                strQry.AppendLine(" AND EE.EMPLOYEE_NO = EEC.EMPLOYEE_NO ");
                strQry.AppendLine(" AND EE.EARNING_NO = EEC.EARNING_NO ");
                strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");

                strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
                strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
                strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");

                //No EMPLOYEE_EARNING_HISTORY Record Exists
                strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }

            //First Delete Records That are older than 180 Days
            strQry.Clear();
            strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND RUN_TYPE = 'T' ");
            strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            //First Delete Records That are Older than 180 Days
            strQry.Clear();
            strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND RUN_TYPE = 'T' ");
            strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            //EERORL CARRY HERE
            DataSet.Tables.Add("PayrollType");
            DataTable PayrollTypeDataTable = new DataTable("PayrollType");

            DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));

            if (DataSet.Tables["EmployeeTemp"].Rows.Count > 0)
            {
                DataView PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
                                                            "PAY_CATEGORY_TYPE = 'W'",
                                                            "",
                                                            DataViewRowState.CurrentRows);

                if (PayrollTypeDataView.Count > 0)
                {
                    DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                    drDataRow["PAYROLL_TYPE_DESC"] = "Wages";

                    DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
                }

                PayrollTypeDataView = null;
                PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
                                                   "PAY_CATEGORY_TYPE = 'S'",
                                                   "",
                                                   DataViewRowState.CurrentRows);

                if (PayrollTypeDataView.Count > 0)
                {
                    DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                    drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";

                    DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
                }

                DataSet.Tables.Remove("EmployeeTemp");

                DataSet.AcceptChanges();

                byte[] bytTempCompress = Get_Company_Records(parInt64CompanyNo, DataSet.Tables["PayrollType"].Rows[0]["PAYROLL_TYPE_DESC"].ToString().Substring(0, 1));
                TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
                DataSet.Merge(TempDataSet);
            }

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
        public byte[] Get_Local_Current_Download_Files(byte[] parbyteDataSet)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            StringBuilder strQry        = new StringBuilder();
            DataSet       ClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            DataView ClientDataSetDataview = new DataView(ClientDataSet.Tables["Files"]
                                                          , ""
                                                          , "FILE_LAYER_IND,FILE_NAME"
                                                          , DataViewRowState.CurrentRows);

            int intFindRow = -1;

            object[] objFind = new object[2];

            DataSet DataSet = new DataSet();

            strQry.Append(clsDBConnectionObjects.Get_Local_Client_Download_SQL());

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");

            for (int intRow = 0; intRow < DataSet.Tables["Files"].Rows.Count; intRow++)
            {
                objFind[0] = DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString();
                objFind[1] = DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString();

                intFindRow = ClientDataSetDataview.Find(objFind);

                if (intFindRow == -1)
                {
                    //DELETE Records on InteractPayrollClient IF Not in Internet Download DataSet
                    strQry.Clear();
                    strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");

                    strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
                    strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString()));

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                    strQry.Clear();
                    strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");

                    strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
                    strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString()));

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                    DataSet.Tables["Files"].Rows.RemoveAt(intRow);

                    intRow -= 1;
                }
            }

            DataSet.AcceptChanges();

            DataView DataSetDataview = new DataView(DataSet.Tables["Files"]
                                                    , ""
                                                    , "FILE_LAYER_IND,FILE_NAME"
                                                    , DataViewRowState.CurrentRows);

            //Go Through Internet Download to see If Any Files Must be Downloaded
            for (int intRow = 0; intRow < ClientDataSet.Tables["Files"].Rows.Count; intRow++)
            {
                objFind[0] = ClientDataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString();
                objFind[1] = ClientDataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString();

                intFindRow = DataSetDataview.Find(objFind);

                if (intFindRow > -1)
                {
                    if (Convert.ToDateTime(ClientDataSet.Tables["Files"].Rows[intRow]["FILE_LAST_UPDATED_DATE"]) == Convert.ToDateTime(DataSetDataview[intFindRow]["FILE_LAST_UPDATED_DATE"]))
                    {
                        ClientDataSet.Tables["Files"].Rows.RemoveAt(intRow);

                        intRow -= 1;
                    }
                }
            }

            ClientDataSet.AcceptChanges();

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(ClientDataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
        public byte[] Get_Form_Records(int parintCurrentUserNo, string parstrAccessInd)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            DataSet       DataSet          = new DataSet();
            StringBuilder strQry           = new StringBuilder();
            string        strSoftwareToUse = "D";

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" IDENTIFY_THRESHOLD_VALUE");
            strQry.AppendLine(",VERIFY_THRESHOLD_VALUE");

            strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FingerprintThreshold");

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" FINGERPRINT_SOFTWARE_IND");

            strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "SoftwareToUse");

            if (DataSet.Tables["SoftwareToUse"].Rows.Count > 0)
            {
                strSoftwareToUse = DataSet.Tables["SoftwareToUse"].Rows[0]["FINGERPRINT_SOFTWARE_IND"].ToString();
            }

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" READ_OPTION_NO");
            strQry.AppendLine(",READ_OPTION_DESC");

            strQry.AppendLine(" FROM InteractPayrollClient.dbo.READ_OPTION");

            strQry.AppendLine(" ORDER BY ");
            strQry.AppendLine(" READ_OPTION_NO");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ReadOption");

            strQry.Clear();
            strQry.AppendLine(" SELECT ");

            strQry.AppendLine("'" + clsDBConnectionObjects.Get_ClientConnectionString().Replace("InteractPayrollClient_Debug", "InteractPayrollClient") + "' AS DB_CONNECTION_STRING");

            //Will Always be Only 1 Row
            strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS ");
            strQry.AppendLine(" WHERE TABLE_NAME = 'COMPANY'");
            strQry.AppendLine(" AND COLUMN_NAME = 'COMPANY_NO'");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Connection");

            strQry.Clear();
            strQry.AppendLine(" SELECT DISTINCT");
            strQry.AppendLine(" PC.PAY_CATEGORY_TYPE");

            strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");

            strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC");
            strQry.AppendLine(" ON C.COMPANY_NO = PC.COMPANY_NO");

            if (parstrAccessInd != "S")
            {
                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
                strQry.AppendLine(" ON UCA.USER_NO = " + parintCurrentUserNo);
                strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO");
            }

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");

            DataSet.Tables.Add("PayrollType");
            DataTable PayrollTypeDataTable = new DataTable("PayrollType");

            DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));

            DataView PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
                                                        "PAY_CATEGORY_TYPE = 'W'",
                                                        "",
                                                        DataViewRowState.CurrentRows);

            if (PayrollTypeDataView.Count > 0)
            {
                DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                drDataRow["PAYROLL_TYPE_DESC"] = "Wages";

                DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
            }

            PayrollTypeDataView = null;
            PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
                                               "PAY_CATEGORY_TYPE = 'S'",
                                               "",
                                               DataViewRowState.CurrentRows);

            if (PayrollTypeDataView.Count > 0)
            {
                DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";

                DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
            }

            PayrollTypeDataView = null;
            PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
                                               "PAY_CATEGORY_TYPE = 'T'",
                                               "",
                                               DataViewRowState.CurrentRows);

            if (PayrollTypeDataView.Count > 0)
            {
                DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance";

                DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
            }

            DataSet.AcceptChanges();

            strQry.Clear();
            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" C.COMPANY_NO");
            strQry.AppendLine(",C.COMPANY_DESC");

            strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");

            if (parstrAccessInd != "S")
            {
                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS  UC ");
                strQry.AppendLine(" ON UC.USER_NO = " + parintCurrentUserNo);
                strQry.AppendLine(" AND UC.COMPANY_NO = C.COMPANY_NO ");
                //2013-07-10
                strQry.AppendLine(" AND UC.COMPANY_ACCESS_IND = 'A'");
            }

            strQry.AppendLine(" ORDER BY C.COMPANY_DESC");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");

            if (DataSet.Tables["Company"].Rows.Count > 0)
            {
                byte[] bytTempCompress = Get_Company_Records_New(Convert.ToInt64(DataSet.Tables["Company"].Rows[0]["COMPANY_NO"]), strSoftwareToUse);

                DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
                DataSet.Merge(TempDataSet);
            }

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
        public byte[] Insert_New_Record(Int64 parInt64CurrentUserNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry = new StringBuilder();

            byte[] bytCompress;
            int    intEarningNo   = -1;
            Int64  Int64CompanyNo = Convert.ToInt64(parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"]);

            DataSet DataSet = new DataSet();

            strQry.Clear();

            strQry.AppendLine(" SELECT DISTINCT ");
            strQry.AppendLine(" 0 AS RETURN_CODE ");
            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY ");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Check", Int64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" SELECT DISTINCT ");
            strQry.AppendLine(" EARNING_NO ");
            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT ");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables[0].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
            //Not a Take-On
            strQry.AppendLine(" AND PAY_CATEGORY_NO > 0 ");
            strQry.AppendLine(" AND RUN_TYPE = 'P'");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", Int64CompanyNo);

            if (DataSet.Tables["Temp"].Rows.Count > 0)
            {
                DataSet.Tables["Check"].Rows[0]["RETURN_CODE"] = 9999;
                goto Insert_New_Record_Continue;
            }

            DataSet.Tables.Remove("Temp");

            strQry.Clear();

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" MAX(EARNING_NO) AS MAX_NO");
            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine(" AND EARNING_NO > 149");
            //Leave Range
            strQry.AppendLine(" AND EARNING_NO < 200");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", Int64CompanyNo);

            if (DataSet.Tables["Temp"].Rows[0]["MAX_NO"] == System.DBNull.Value)
            {
                intEarningNo = 150;
            }
            else
            {
                intEarningNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
            }

            parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"]           = intEarningNo;
            parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_TYPE_DEFAULT"] = "Y";

            //Returned DataTable
            DataSet.Tables.Add(parDataSet.Tables["EarningSelected"].Clone());

            DataSet.Tables["EarningSelected"].ImportRow(parDataSet.Tables["EarningSelected"].Rows[0]);

            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EARNING_NO");
            strQry.AppendLine(",TIE_BREAKER");
            strQry.AppendLine(",EARNING_DESC");
            strQry.AppendLine(",IRP5_CODE");
            strQry.AppendLine(",LEAVE_PERCENTAGE");
            strQry.AppendLine(",EARNING_REPORT_HEADER1");
            strQry.AppendLine(",EARNING_REPORT_HEADER2");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",EARNING_DEL_IND)");
            strQry.AppendLine(" VALUES");
            strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
            strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
            strQry.AppendLine(",1");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DESC"].ToString()));
            strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["EarningSelected"].Rows[0]["IRP5_CODE"]));
            strQry.AppendLine(",100");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER1"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER2"].ToString()));
            strQry.AppendLine("," + parInt64CurrentUserNo);
            strQry.AppendLine(",GETDATE()");
            strQry.AppendLine(",'Y')");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

            //Create For Opposite PAY_CATEGORY_TYPE
            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EARNING_NO");
            strQry.AppendLine(",TIE_BREAKER");
            strQry.AppendLine(",EARNING_DESC");
            strQry.AppendLine(",IRP5_CODE");
            strQry.AppendLine(",LEAVE_PERCENTAGE");
            strQry.AppendLine(",EARNING_REPORT_HEADER1");
            strQry.AppendLine(",EARNING_REPORT_HEADER2");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",EARNING_DEL_IND)");
            strQry.AppendLine(" VALUES");
            strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());

            if (parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
            {
                strQry.AppendLine(",'S'");
            }
            else
            {
                strQry.AppendLine(",'W'");
            }

            strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
            strQry.AppendLine(",1");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DESC"].ToString()));
            strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["EarningSelected"].Rows[0]["IRP5_CODE"]));
            strQry.AppendLine(",100");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER1"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER2"].ToString()));
            strQry.AppendLine("," + parInt64CurrentUserNo);
            strQry.AppendLine(",GETDATE()");
            strQry.AppendLine(",'Y')");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EMPLOYEE_NO");
            strQry.AppendLine(",EARNING_NO");
            strQry.AppendLine(",TIE_BREAKER");

            strQry.AppendLine(",AMOUNT");
            strQry.AppendLine(",EARNING_TYPE_IND");
            strQry.AppendLine(",EARNING_PERIOD_IND");
            strQry.AppendLine(",EARNING_DAY_VALUE");

            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD)");

            strQry.AppendLine(" VALUES");
            strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));

            //Default EMPLOYEE_NO
            strQry.AppendLine(",0");
            strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
            strQry.AppendLine(",1");

            strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["AMOUNT"].ToString());

            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_TYPE_IND"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_PERIOD_IND"].ToString()));
            strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DAY_VALUE"].ToString());

            strQry.AppendLine("," + parInt64CurrentUserNo);
            strQry.AppendLine(",GETDATE())");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

            strQry.Clear();

            //Create For Opposite PAY_CATEGORY_TYPE
            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EMPLOYEE_NO");
            strQry.AppendLine(",EARNING_NO");
            strQry.AppendLine(",TIE_BREAKER");

            strQry.AppendLine(",AMOUNT");
            strQry.AppendLine(",EARNING_TYPE_IND");
            strQry.AppendLine(",EARNING_PERIOD_IND");
            strQry.AppendLine(",EARNING_DAY_VALUE");

            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD)");

            strQry.AppendLine(" VALUES");
            strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());

            if (parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
            {
                strQry.AppendLine(",'S'");
            }
            else
            {
                strQry.AppendLine(",'W'");
            }

            //Default EMPLOYEE_NO
            strQry.AppendLine(",0");
            strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
            strQry.AppendLine(",1");

            strQry.AppendLine(",0");
            strQry.AppendLine(",'U'");
            strQry.AppendLine(",'E'");
            strQry.AppendLine(",0");

            strQry.AppendLine("," + parInt64CurrentUserNo);
            strQry.AppendLine(",GETDATE())");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);

            //Create Record for Opposite PAY_CATEGORY_TYPE
            DataRow myDataRow = DataSet.Tables["EarningSelected"].NewRow();

            myDataRow["COMPANY_NO"] = parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString();

            if (parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
            {
                myDataRow["PAY_CATEGORY_TYPE"] = "S";
            }
            else
            {
                myDataRow["PAY_CATEGORY_TYPE"] = "W";
            }

            myDataRow["EMPLOYEE_NO"]  = 0;
            myDataRow["EARNING_NO"]   = Convert.ToInt16(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"]);
            myDataRow["EARNING_DESC"] = parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DESC"].ToString();
            myDataRow["AMOUNT"]       = 0;

            myDataRow["IRP5_CODE"] = parDataSet.Tables["EarningSelected"].Rows[0]["IRP5_CODE"].ToString();
            myDataRow["EARNING_REPORT_HEADER1"] = parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER1"].ToString();
            myDataRow["EARNING_REPORT_HEADER2"] = parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER2"].ToString();
            myDataRow["EARNING_TYPE_IND"]       = "U";

            myDataRow["EARNING_TYPE_DEFAULT"] = "Y";
            myDataRow["EARNING_PERIOD_IND"]   = "E";
            myDataRow["EARNING_DAY_VALUE"]    = 0;
            myDataRow["EARNING_DEL_IND"]      = "Y";
            myDataRow["TIE_BREAKER"]          = 1;

            DataSet.Tables["EarningSelected"].Rows.Add(myDataRow);

Insert_New_Record_Continue:

            DataSet.Tables.Remove("Temp");

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + Int64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            DataSet.AcceptChanges();

            bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;
            parDataSet.Dispose();
            parDataSet = null;

            return(bytCompress);
        }
示例#19
0
        public void Update_Records(Int64 parint64UserNo, byte[] byteCompressedDataSet)
        {
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
            //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes

            DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);

            StringBuilder strQry = new StringBuilder();

            for (int intRow = 0; intRow < DataSet.Tables["UserFingerTemplate"].Rows.Count; intRow++)
            {
                if (DataSet.Tables["UserFingerTemplate"].Rows[intRow].RowState == DataRowState.Added)
                {
                    strQry.Clear();

                    strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");
                    strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString());
                    strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString());

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                    //There Should be only One - otherwise Error
                    strQry.Clear();

                    strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");
                    strQry.AppendLine("(USER_NO");
                    strQry.AppendLine(",FINGER_NO");
                    strQry.AppendLine(",FINGER_TEMPLATE)");
                    strQry.AppendLine(" VALUES ");
                    strQry.AppendLine("(" + parint64UserNo);
                    strQry.AppendLine("," + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString());
                    strQry.AppendLine(",@FINGER_TEMPLATE) ");

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), (byte[])DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_TEMPLATE"], "@FINGER_TEMPLATE");
                }
                else
                {
                    strQry.Clear();

                    strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE_DELETE");
                    strQry.AppendLine("(USER_NO");
                    strQry.AppendLine(",FINGER_NO");
                    strQry.AppendLine(",CREATION_DATETIME)");

                    strQry.AppendLine(" SELECT ");
                    strQry.AppendLine(" USER_NO");
                    strQry.AppendLine(",FINGER_NO");
                    strQry.AppendLine(",CREATION_DATETIME ");

                    strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");

                    strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString());
                    strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString());

                    strQry.AppendLine(" AND NOT CREATION_DATETIME IS NULL ");

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                    strQry.Clear();

                    strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");

                    strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString());
                    strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString());

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
                }
            }
        }
        public void Upload_Records(Int64 parInt64CompanyNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry  = new StringBuilder();
            DataSet       DataSet = new DataSet();

            if (parDataSet.Tables["Employee"].Rows.Count > 0)
            {
                for (int intRow = 0; intRow < parDataSet.Tables["Employee"].Rows.Count; intRow++)
                {
                    if (DataSet.Tables["Employee"] != null)
                    {
                        DataSet.Tables.Remove("Employee");
                    }

                    strQry.Clear();
                    strQry.AppendLine(" SELECT ");
                    strQry.AppendLine(" EMPLOYEE_NO");

                    strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE");

                    strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                    strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());

                    clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);

                    if (DataSet.Tables["Employee"].Rows.Count == 0)
                    {
                        //Insert
                        strQry.Clear();

                        strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE ");
                        strQry.AppendLine("(COMPANY_NO");
                        strQry.AppendLine(",EMPLOYEE_NO");
                        strQry.AppendLine(",PAY_CATEGORY_TYPE");
                        strQry.AppendLine(",EMPLOYEE_CODE");
                        strQry.AppendLine(",EMPLOYEE_NAME");
                        strQry.AppendLine(",EMPLOYEE_SURNAME");
                        strQry.AppendLine(",EMPLOYEE_LAST_RUNDATE");
                        strQry.AppendLine(",EMPLOYEE_TAX_STARTDATE");
                        strQry.AppendLine(",DEPARTMENT_NO");
                        strQry.AppendLine(",FIRST_RUN_COMPLETED_IND");
                        strQry.AppendLine(",EMPLOYEE_TAKEON_IND");
                        strQry.AppendLine(",UPLOAD_FROM_CLIENT_IND");

                        strQry.AppendLine(",ANNUAL_SALARY");
                        strQry.AppendLine(",EMPLOYEE_TAX_OFFICE_NO");
                        strQry.AppendLine(",TAX_DIRECTIVE_PERCENTAGE");
                        strQry.AppendLine(",LEAVE_SHIFT_NO");
                        strQry.AppendLine(",EMPLOYEE_NUMBER_CHEQUES");
                        strQry.AppendLine(",NUMBER_MEDICAL_AID_DEPENDENTS");
                        strQry.AppendLine(",OCCUPATION_NO");
                        strQry.AppendLine(",BANK_ACCOUNT_TYPE_NO");
                        strQry.AppendLine(",BRANCH_CODE");
                        strQry.AppendLine(",ACCOUNT_NO");
                        strQry.AppendLine(",MARITAL_STATUS_NO");
                        strQry.AppendLine(",NATURE_PERSON_NO");
                        strQry.AppendLine(",RACE_NO");
                        strQry.AppendLine(",OCCUPATION_LEVEL_NO");
                        strQry.AppendLine(",OCCUPATION_CATEGORY_NO");
                        strQry.AppendLine(",OCCUPATION_FUNCTION_NO");
                        strQry.AppendLine(",SKILLS_EQUITY_PROVINCE_NO");
                        strQry.AppendLine(",USER_NO_ACTIVATE_RECORD");
                        strQry.AppendLine(",BANK_NO)");

                        strQry.AppendLine(" VALUES ");

                        strQry.AppendLine("(" + parDataSet.Tables["Employee"].Rows[intRow]["COMPANY_NO"].ToString());
                        strQry.AppendLine("," + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());

                        strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                        strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_CODE"].ToString()));
                        strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NAME"].ToString()));
                        strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_SURNAME"].ToString()));

                        if (parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_LAST_RUNDATE"] != System.DBNull.Value)
                        {
                            strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_LAST_RUNDATE"]).ToString("yyyy-MM-dd") + "'");
                        }
                        else
                        {
                            strQry.AppendLine(",'2015-02-06'");
                        }

                        strQry.AppendLine(",'2015-02-06'");

                        if (parDataSet.Tables["Employee"].Rows[intRow]["DEPARTMENT_NO"] != System.DBNull.Value)
                        {
                            strQry.AppendLine("," + parDataSet.Tables["Employee"].Rows[intRow]["DEPARTMENT_NO"].ToString());
                        }
                        else
                        {
                            strQry.AppendLine(",NULL");
                        }

                        strQry.AppendLine(",'Y'");
                        strQry.AppendLine(",'Y'");
                        strQry.AppendLine(",'Y'");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0)");

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Convert.ToInt64(parDataSet.Tables["Employee"].Rows[intRow]["COMPANY_NO"]));
                    }
                }

                for (int intRow = 0; intRow < parDataSet.Tables["EmployeePayCategory"].Rows.Count; intRow++)
                {
                    if (DataSet.Tables["EmployeePayCategory"] != null)
                    {
                        DataSet.Tables.Remove("EmployeePayCategory");
                    }

                    strQry.Clear();
                    strQry.AppendLine(" SELECT ");
                    strQry.AppendLine(" EMPLOYEE_NO");

                    strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");

                    strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                    strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                    strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                    strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");

                    clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeePayCategory", parInt64CompanyNo);

                    if (DataSet.Tables["EmployeePayCategory"].Rows.Count == 0)
                    {
                        //Insert
                        strQry.Clear();

                        strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY ");
                        strQry.AppendLine("(COMPANY_NO");
                        strQry.AppendLine(",EMPLOYEE_NO");
                        strQry.AppendLine(",PAY_CATEGORY_NO");
                        strQry.AppendLine(",PAY_CATEGORY_TYPE");
                        strQry.AppendLine(",TIE_BREAKER");
                        strQry.AppendLine(",DEFAULT_IND");
                        strQry.AppendLine(",HOURLY_RATE");
                        strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL)");

                        strQry.AppendLine(" SELECT ");

                        strQry.AppendLine(" C.COMPANY_NO");
                        strQry.AppendLine("," + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                        strQry.AppendLine("," + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());

                        strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                        strQry.AppendLine(",ISNULL(EPC.TIE_BREAKER,0) + 1");

                        strQry.AppendLine(",'Y'");
                        strQry.AppendLine(",0");
                        strQry.AppendLine(",0");

                        strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");

                        strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                        strQry.AppendLine(" ON C.COMPANY_NO = EPC.COMPANY_NO ");
                        strQry.AppendLine(" AND EPC.EMPLOYEE_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                        strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                        strQry.AppendLine(" WHERE C.COMPANY_NO = " + parInt64CompanyNo);

                        clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Convert.ToInt64(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["COMPANY_NO"]));
                    }
                }
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
        }
示例#21
0
        public byte[] Update_Record(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, byte[] parbyteDataSet, string parstrPayCategoryType)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry = new StringBuilder();

            for (int intRow = 0; intRow < parDataSet.Tables["EmployeeShiftSchedule"].Rows.Count; intRow++)
            {
                if (intRow == 0)
                {
                    //Delete all Records For PAY_CATEGORY_SHIFT_SCHEDULE_NO
                    strQry.Clear();

                    strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_SHIFT_SCHEDULE");

                    strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                    strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                    strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(" AND PAY_CATEGORY_SHIFT_SCHEDULE_NO = " + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_SHIFT_SCHEDULE_NO"].ToString());

                    clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
                }

                strQry.Clear();

                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_SHIFT_SCHEDULE ");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",PAY_CATEGORY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",PAY_CATEGORY_SHIFT_SCHEDULE_NO");
                strQry.AppendLine(",SHIFT_SCHEDULE_DATETIME");
                strQry.AppendLine(",FROM_HHMM");
                strQry.AppendLine(",TO_HHMM");
                strQry.AppendLine(",FORECOLOR_HEX");
                strQry.AppendLine(",BACKCOLOR_HEX)");

                strQry.AppendLine(" VALUES ");

                strQry.AppendLine("(" + parInt64CompanyNo.ToString());
                strQry.AppendLine("," + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine("," + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                strQry.AppendLine("," + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_SHIFT_SCHEDULE_NO"].ToString());
                strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["SHIFT_SCHEDULE_DATETIME"]).ToString("yyyy-MM-dd") + "'");
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["FROM_HHMM"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["TO_HHMM"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["FORECOLOR_HEX"].ToString()));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["BACKCOLOR_HEX"].ToString()) + ")");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(parDataSet);
            parDataSet.Dispose();
            parDataSet = null;

            return(bytCompress);
        }
        public byte[] Update_Employee(Int64 parInt64CompanyNo, string parstrPayrollType, string parstrFromPayrollType, int parintEmployeeNo, int parintLeaveShiftNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            StringBuilder strQry = new StringBuilder();

            DataSet DataSet = new DataSet();

            //Remove Current EMPLOYEE_PAY_CATEGORY Link
            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY ");

            strQry.AppendLine(" SET DATETIME_DELETE_RECORD = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            //Relink LEAVE_SHIFT_NO
            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE ");

            strQry.AppendLine(" SET ");
            strQry.AppendLine(" PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
            strQry.AppendLine(",LEAVE_SHIFT_NO = " + parintLeaveShiftNo);

            if (parstrPayrollType == "S")
            {
                strQry.AppendLine(",EMPLOYEE_NUMBER_CHEQUES = 12 ");
            }

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
            strQry.AppendLine(" AND EMPLOYEE_ENDDATE IS NULL");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            for (int intRow = 0; intRow < parDataSet.Tables["PayCategory"].Rows.Count; intRow++)
            {
                if (DataSet.Tables["Temp"] != null)
                {
                    DataSet.Tables.Remove("Temp");
                }

                strQry.Clear();
                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" ISNULL(MAX(TIE_BREAKER),0) + 1 AS MAX_NO");
                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
                strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
                strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

                clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);

                strQry.Clear();
                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
                strQry.AppendLine("(COMPANY_NO");
                strQry.AppendLine(",EMPLOYEE_NO");
                strQry.AppendLine(",PAY_CATEGORY_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");
                strQry.AppendLine(",TIE_BREAKER");
                strQry.AppendLine(",HOURLY_RATE");
                strQry.AppendLine(",DEFAULT_IND");
                strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL");
                strQry.AppendLine(",DATETIME_NEW_RECORD");
                strQry.AppendLine(",USER_NO_NEW_RECORD)");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(parInt64CompanyNo.ToString());
                strQry.AppendLine("," + parintEmployeeNo);
                strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"]));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
                strQry.AppendLine("," + Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]));
                strQry.AppendLine("," + Convert.ToDouble(parDataSet.Tables["PayCategory"].Rows[intRow]["REC_RATE"]));
                strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PayCategory"].Rows[intRow]["DEFAULT_IND"].ToString()));

                strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL = ");

                strQry.AppendLine(" CASE ");

                strQry.AppendLine(" WHEN PC.PAY_CATEGORY_TYPE = 'W' THEN");

                strQry.AppendLine(" ROUND(SUM(PCTD.TIME_DECIMAL) / COUNT(*),2)");

                strQry.AppendLine(" ELSE ROUND(PC.SALARY_MINUTES_PAID_PER_DAY / 60,2)");

                strQry.AppendLine(" END ");

                strQry.AppendLine(",GETDATE()");
                //Current User
                strQry.AppendLine(",0");

                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC");

                strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL PCTD");
                strQry.AppendLine(" ON PC.COMPANY_NO = PCTD.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = PCTD.PAY_CATEGORY_NO ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parInt64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
                strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL");

                strQry.AppendLine(" GROUP BY ");
                strQry.AppendLine(" PC.PAY_CATEGORY_TYPE ");
                strQry.AppendLine(",PC.SALARY_MINUTES_PAID_PER_DAY");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }

            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING ");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",EARNING_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",TIE_BREAKER");
            strQry.AppendLine(",EARNING_DESC");
            strQry.AppendLine(",SPREAD_SHEET_TYPE_IND");
            strQry.AppendLine(",LEAVE_PERCENTAGE");
            strQry.AppendLine(",EARNING_REPORT_HEADER1");
            strQry.AppendLine(",EARNING_REPORT_HEADER2");
            strQry.AppendLine(",IRP5_CODE");
            strQry.AppendLine(",TAX_PERCENTAGE");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",USER_NO_RECORD");
            strQry.AppendLine(",DATETIME_DELETE_RECORD");
            strQry.AppendLine(",EARNING_DEL_IND)");

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" E.COMPANY_NO");
            strQry.AppendLine(",E.EARNING_NO");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
            strQry.AppendLine(",E.TIE_BREAKER");
            strQry.AppendLine(",E.EARNING_DESC");
            strQry.AppendLine(",E.SPREAD_SHEET_TYPE_IND");
            strQry.AppendLine(",E.LEAVE_PERCENTAGE");
            strQry.AppendLine(",E.EARNING_REPORT_HEADER1");
            strQry.AppendLine(",E.EARNING_REPORT_HEADER2");
            strQry.AppendLine(",E.IRP5_CODE");
            strQry.AppendLine(",E.TAX_PERCENTAGE");
            strQry.AppendLine(",E.USER_NO_NEW_RECORD");
            strQry.AppendLine(",E.DATETIME_NEW_RECORD");
            strQry.AppendLine(",E.USER_NO_RECORD");
            strQry.AppendLine(",E.DATETIME_DELETE_RECORD");
            strQry.AppendLine(",E.EARNING_DEL_IND");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
            strQry.AppendLine(" ON E.COMPANY_NO = EE.COMPANY_NO ");
            strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND E.EARNING_NO = EE.EARNING_NO ");
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EE.PAY_CATEGORY_TYPE ");
            strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");

            strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EARNING EARN");
            strQry.AppendLine(" ON E.COMPANY_NO = EARN.COMPANY_NO ");
            strQry.AppendLine(" AND E.EARNING_DESC = EARN.EARNING_DESC ");
            strQry.AppendLine(" AND EARN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");

            //Not Defaults and Not Leave
            strQry.AppendLine(" AND E.EARNING_NO > 9");
            strQry.AppendLine(" AND E.EARNING_NO < 200");

            //Record Does NOT Exist
            strQry.AppendLine(" AND EARN.COMPANY_NO IS NULL ");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" UPDATE EE ");

            strQry.AppendLine(" SET ");
            strQry.AppendLine(" PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");

            strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION ");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",DEDUCTION_NO");
            strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",DEDUCTION_DESC");
            strQry.AppendLine(",IRP5_CODE");
            strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
            strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
            strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
            strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",USER_NO_RECORD");
            strQry.AppendLine(",DATETIME_DELETE_RECORD)");

            strQry.AppendLine(" SELECT DISTINCT ");
            strQry.AppendLine(" D.COMPANY_NO");
            strQry.AppendLine(",D.DEDUCTION_NO");
            strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_NO");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
            strQry.AppendLine(",D.DEDUCTION_DESC");
            strQry.AppendLine(",D.IRP5_CODE");
            strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_COUNT");
            strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER1");
            strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER2");
            strQry.AppendLine(",D.DEDUCTION_LOAN_TYPE_IND");
            strQry.AppendLine(",D.USER_NO_NEW_RECORD");
            strQry.AppendLine(",D.DATETIME_NEW_RECORD");
            strQry.AppendLine(",D.USER_NO_RECORD");
            strQry.AppendLine(",D.DATETIME_DELETE_RECORD");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION D");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
            strQry.AppendLine(" ON D.COMPANY_NO = ED.COMPANY_NO ");
            strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND D.DEDUCTION_NO = ED.DEDUCTION_NO ");
            strQry.AppendLine(" AND D.DEDUCTION_SUB_ACCOUNT_NO = ED.DEDUCTION_SUB_ACCOUNT_NO ");
            strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = ED.PAY_CATEGORY_TYPE ");
            strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");

            strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
            strQry.AppendLine(" ON D.COMPANY_NO = DED.COMPANY_NO ");
            strQry.AppendLine(" AND D.DEDUCTION_NO = DED.DEDUCTION_NO ");
            strQry.AppendLine(" AND DED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE D.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND D.DATETIME_DELETE_RECORD IS NULL");
            //Record Does NOT Exist
            strQry.AppendLine(" AND DED.COMPANY_NO IS NULL ");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",EMPLOYEE_NO");
            strQry.AppendLine(",DEDUCTION_NO");
            strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",TIE_BREAKER");
            strQry.AppendLine(",DEDUCTION_TYPE_IND");
            strQry.AppendLine(",DEDUCTION_VALUE");
            strQry.AppendLine(",DEDUCTION_MIN_VALUE");
            strQry.AppendLine(",DEDUCTION_MAX_VALUE");
            strQry.AppendLine(",DEDUCTION_PERIOD_IND");
            strQry.AppendLine(",DEDUCTION_DAY_VALUE");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",USER_NO_RECORD");
            strQry.AppendLine(",DATETIME_DELETE_RECORD)");

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" ED.COMPANY_NO");
            strQry.AppendLine(",ED.EMPLOYEE_NO");
            strQry.AppendLine(",ED.DEDUCTION_NO");
            strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
            strQry.AppendLine(",ED.TIE_BREAKER");
            strQry.AppendLine(",ED.DEDUCTION_TYPE_IND");
            strQry.AppendLine(",ED.DEDUCTION_VALUE");
            strQry.AppendLine(",ED.DEDUCTION_MIN_VALUE");
            strQry.AppendLine(",ED.DEDUCTION_MAX_VALUE");
            strQry.AppendLine(",ED.DEDUCTION_PERIOD_IND");
            strQry.AppendLine(",ED.DEDUCTION_DAY_VALUE");
            strQry.AppendLine(",ED.USER_NO_NEW_RECORD");
            strQry.AppendLine(",ED.DATETIME_NEW_RECORD");
            strQry.AppendLine(",ED.USER_NO_RECORD");
            strQry.AppendLine(",ED.DATETIME_DELETE_RECORD");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED");

            strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED_Compare");
            strQry.AppendLine(" ON ED.COMPANY_NO = ED_Compare.COMPANY_NO");
            strQry.AppendLine(" AND ED.EMPLOYEE_NO = ED_Compare.EMPLOYEE_NO");
            strQry.AppendLine(" AND ED.DEDUCTION_NO = ED_Compare.DEDUCTION_NO");
            strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = ED_Compare.DEDUCTION_SUB_ACCOUNT_NO");
            strQry.AppendLine(" AND ED_Compare.PAY_CATEGORY_TYPE  = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
            //Default
            strQry.AppendLine(" AND ED.EMPLOYEE_NO = 0");
            strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");

            //Record Does NOT Exist
            strQry.AppendLine(" AND ED_Compare.COMPANY_NO IS NULL ");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" UPDATE ED ");

            strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
            strQry.AppendLine(" ON ED.COMPANY_NO = DED.COMPANY_NO ");
            strQry.AppendLine(" AND ED.DEDUCTION_NO = DED.DEDUCTION_NO ");
            strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
            strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");

            strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" UPDATE EDEP ");

            strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EARNING EARN");
            strQry.AppendLine(" ON EDEP.COMPANY_NO = EARN.COMPANY_NO ");
            strQry.AppendLine(" AND EDEP.EARNING_NO = EARN.EARNING_NO ");
            strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = EARN.PAY_CATEGORY_TYPE ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
            strQry.AppendLine(" ON EDEP.COMPANY_NO = DED.COMPANY_NO ");
            strQry.AppendLine(" AND EDEP.DEDUCTION_NO = DED.DEDUCTION_NO ");
            strQry.AppendLine(" AND EDEP.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
            strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");

            strQry.AppendLine(" WHERE EDEP.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND EDEP.DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            if (parstrPayrollType == "W")
            {
                strQry.Clear();

                strQry.AppendLine(" UPDATE EDEP ");

                //2=Normal Time
                strQry.AppendLine(" SET EARNING_NO = 2 ");

                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");

                strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP_Check ");
                strQry.AppendLine(" ON EDEP.COMPANY_NO = EDEP_Check.COMPANY_NO  ");
                strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = EDEP_Check.EMPLOYEE_NO  ");
                strQry.AppendLine(" AND EDEP_Check.EARNING_NO = 2  ");
                strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = EDEP_Check.PAY_CATEGORY_TYPE  ");

                strQry.AppendLine(" WHERE EDEP.COMPANY_NO = " + parInt64CompanyNo);
                strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = " + parintEmployeeNo);
                //PAY_CATEGORY_TYPE already Changed
                strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
                //1=Income
                strQry.AppendLine(" AND EDEP.EARNING_NO = 1 ");

                strQry.AppendLine(" AND EDEP.DATETIME_DELETE_RECORD IS NULL");

                //Records do NOT already Exist
                strQry.AppendLine(" AND EDEP_Check.COMPANY_NO IS NULL  ");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }
            else
            {
                if (parstrPayrollType == "S")
                {
                    strQry.Clear();

                    strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE ");
                    strQry.AppendLine("(COMPANY_NO");
                    strQry.AppendLine(",EMPLOYEE_NO");
                    strQry.AppendLine(",DEDUCTION_NO");
                    strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                    strQry.AppendLine(",EARNING_NO");
                    strQry.AppendLine(",PAY_CATEGORY_TYPE");
                    strQry.AppendLine(",TIE_BREAKER");
                    strQry.AppendLine(",USER_NO_NEW_RECORD");
                    strQry.AppendLine(",DATETIME_NEW_RECORD");
                    strQry.AppendLine(",USER_NO_RECORD");
                    strQry.AppendLine(",DATETIME_DELETE_RECORD)");

                    strQry.AppendLine(" SELECT ");

                    strQry.AppendLine(" COMPANY_NO");
                    strQry.AppendLine(",EMPLOYEE_NO");
                    strQry.AppendLine(",DEDUCTION_NO");
                    strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                    //Income
                    strQry.AppendLine(",1");
                    strQry.AppendLine(",PAY_CATEGORY_TYPE");
                    strQry.AppendLine(",TIE_BREAKER");
                    strQry.AppendLine(",USER_NO_NEW_RECORD");
                    strQry.AppendLine(",DATETIME_NEW_RECORD");
                    strQry.AppendLine(",USER_NO_RECORD");
                    strQry.AppendLine(",DATETIME_DELETE_RECORD");

                    strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE  ");

                    strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                    strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
                    //PAY_CATEGORY_TYPE already Changed
                    strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
                    //2=Normal Time
                    strQry.AppendLine(" AND EARNING_NO = 2 ");

                    strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");

                    clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
                }
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE L ");

            strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LOANS L ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
            strQry.AppendLine(" ON L.COMPANY_NO = DED.COMPANY_NO ");
            strQry.AppendLine(" AND L.DEDUCTION_NO = DED.DEDUCTION_NO ");
            strQry.AppendLine(" AND L.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
            strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");

            strQry.AppendLine(" WHERE L.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND L.EMPLOYEE_NO = " + parintEmployeeNo);
            strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
            strQry.AppendLine(" AND L.DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            if (parstrPayrollType == "W")
            {
                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
            }
            else
            {
                if (parstrPayrollType == "S")
                {
                    strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
                }
                else
                {
                    if (parstrPayrollType == "T")
                    {
                        strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
                    }
                }
            }

            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",EMPLOYEE_NO");
            strQry.AppendLine(",PAY_CATEGORY_NO");
            strQry.AppendLine(",TIMESHEET_DATE");
            strQry.AppendLine(",TIMESHEET_SEQ");
            strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES");
            strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES");
            strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES");
            strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES");
            strQry.AppendLine(",INDICATOR");
            strQry.AppendLine(",TIMESHEET_ACCUM_MINUTES");
            strQry.AppendLine(",USER_NO_TIME_IN");
            strQry.AppendLine(",USER_NO_TIME_OUT)");

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" EBC.COMPANY_NO");
            strQry.AppendLine(",EBC.EMPLOYEE_NO");
            //New PAY_CATEGORY_NO
            strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
            strQry.AppendLine(",EBC.TIMESHEET_DATE");
            strQry.AppendLine(",EBC.TIMESHEET_SEQ");
            strQry.AppendLine(",EBC.TIMESHEET_TIME_IN_MINUTES");
            strQry.AppendLine(",EBC.TIMESHEET_TIME_OUT_MINUTES");
            strQry.AppendLine(",EBC.CLOCKED_TIME_IN_MINUTES");
            strQry.AppendLine(",EBC.CLOCKED_TIME_OUT_MINUTES");
            strQry.AppendLine(",EBC.INDICATOR");
            strQry.AppendLine(",EBC.TIMESHEET_ACCUM_MINUTES");
            strQry.AppendLine(",EBC.USER_NO_TIME_IN");
            strQry.AppendLine(",EBC.USER_NO_TIME_OUT");

            if (parstrFromPayrollType == "W")
            {
                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT EBC ");
            }
            else
            {
                if (parstrFromPayrollType == "S")
                {
                    strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT EBC ");
                }
                else
                {
                    if (parstrFromPayrollType == "T")
                    {
                        strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT EBC ");
                    }
                }
            }

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
            strQry.AppendLine(" ON EBC.COMPANY_NO = EPC.COMPANY_NO ");
            strQry.AppendLine(" AND EBC.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
            //New EMPLOYEE_PAY_CATEGORY
            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE EBC.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EBC.EMPLOYEE_NO = " + parintEmployeeNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            if (parstrFromPayrollType == "W")
            {
                strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
            }
            else
            {
                if (parstrFromPayrollType == "S")
                {
                    strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
                }
                else
                {
                    if (parstrFromPayrollType == "T")
                    {
                        strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
                    }
                }
            }

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            if (parstrPayrollType == "W")
            {
                strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
            }
            else
            {
                if (parstrPayrollType == "S")
                {
                    strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
                }
                else
                {
                    if (parstrPayrollType == "T")
                    {
                        strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
                    }
                }
            }

            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",EMPLOYEE_NO");
            strQry.AppendLine(",PAY_CATEGORY_NO");
            strQry.AppendLine(",BREAK_DATE");
            strQry.AppendLine(",BREAK_SEQ");
            strQry.AppendLine(",BREAK_TIME_IN_MINUTES");
            strQry.AppendLine(",BREAK_TIME_OUT_MINUTES");
            strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES");
            strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES");
            strQry.AppendLine(",INDICATOR");
            strQry.AppendLine(",BREAK_ACCUM_MINUTES");
            strQry.AppendLine(",USER_NO_TIME_IN");
            strQry.AppendLine(",USER_NO_TIME_OUT)");

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" EBC.COMPANY_NO");
            strQry.AppendLine(",EBC.EMPLOYEE_NO");
            //New PAY_CATEGORY_NO
            strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
            strQry.AppendLine(",EBC.BREAK_DATE");
            strQry.AppendLine(",EBC.BREAK_SEQ");
            strQry.AppendLine(",EBC.BREAK_TIME_IN_MINUTES");
            strQry.AppendLine(",EBC.BREAK_TIME_OUT_MINUTES");
            strQry.AppendLine(",EBC.CLOCKED_TIME_IN_MINUTES");
            strQry.AppendLine(",EBC.CLOCKED_TIME_OUT_MINUTES");
            strQry.AppendLine(",EBC.INDICATOR");
            strQry.AppendLine(",EBC.BREAK_ACCUM_MINUTES");
            strQry.AppendLine(",EBC.USER_NO_TIME_IN");
            strQry.AppendLine(",EBC.USER_NO_TIME_OUT");

            if (parstrFromPayrollType == "W")
            {
                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT EBC ");
            }
            else
            {
                if (parstrFromPayrollType == "S")
                {
                    strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT EBC ");
                }
                else
                {
                    if (parstrFromPayrollType == "T")
                    {
                        strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT EBC ");
                    }
                }
            }

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
            strQry.AppendLine(" ON EBC.COMPANY_NO = EPC.COMPANY_NO ");
            strQry.AppendLine(" AND EBC.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
            //New EMPLOYEE_PAY_CATEGORY
            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE EBC.COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EBC.EMPLOYEE_NO = " + parintEmployeeNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            if (parstrFromPayrollType == "W")
            {
                strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
            }
            else
            {
                if (parstrFromPayrollType == "S")
                {
                    strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
                }
                else
                {
                    if (parstrFromPayrollType == "T")
                    {
                        strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
                    }
                }
            }

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT ");

            //1=Income
            strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
            //PAY_CATEGORY_TYPE already Changed
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            //Batch Processing Earning
            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_BATCH_TEMP ");

            //1=Income
            strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
            //PAY_CATEGORY_TYPE already Changed
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            strQry.Clear();

            //Batch Processing Deduction
            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_BATCH_TEMP ");

            //1=Income
            strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));

            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
            strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
            //PAY_CATEGORY_TYPE already Changed
            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            Get_Employee_DataSet(DataSet, parInt64CompanyNo, parintEmployeeNo);

            Get_CurrentPayCategory_DataSet(DataSet, parInt64CompanyNo, parintEmployeeNo);

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
示例#23
0
        public int Check_Run(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet)
        {
            int intReturnCode = 0;

            DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet);
            StringBuilder strQry = new StringBuilder();

            strQry.Clear();

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" COMPANY_NO");

            strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK ");

            strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
            strQry.AppendLine(" AND ISNULL(ALLOW_COST_CENTRE_CONVERT_IND,0) = 1");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "AllowConvertCheck", parint64CompanyNo);

            if (DataSet.Tables["AllowConvertCheck"].Rows.Count == 0)
            {
                intReturnCode = 9;
            }
            else
            {
                //Check That there is No Current Payroll Run on the Go
                for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
                {
                    if (DataSet.Tables["RunCheck"] != null)
                    {
                        DataSet.Tables.Remove("RunCheck");
                    }

                    strQry.Clear();

                    strQry.AppendLine(" SELECT ");
                    strQry.AppendLine(" COMPANY_NO");

                    strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT ");

                    strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
                    strQry.AppendLine(" AND PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                    strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(" AND RUN_TYPE = 'P' ");

                    clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "RunCheck", parint64CompanyNo);

                    if (DataSet.Tables["RunCheck"].Rows.Count > 0)
                    {
                        intReturnCode = 1;

                        break;
                    }
                }
            }

            DataSet.Dispose();
            DataSet = null;

            return intReturnCode;
        }
        public byte[] Get_Form_Records(Int64 parint64CurrentUserNo, string parstrAccessInd)
        {
            StringBuilder strQry  = new StringBuilder();
            DataSet       DataSet = new DataSet();

            //DISTINCT Because bug caused Duplicates
            strQry.Clear();
            strQry.AppendLine(" SELECT DISTINCT");
            strQry.AppendLine(" U.USER_NO");
            strQry.AppendLine(",UCA.COMPANY_NO");
            strQry.AppendLine(",U.USER_ID");
            strQry.AppendLine(",U.FIRSTNAME");
            strQry.AppendLine(",U.SURNAME");

            strQry.AppendLine(" FROM ");
            strQry.AppendLine(" InteractPayroll.dbo.USER_ID U");
            strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");

            if (parstrAccessInd == "A")
            {
                strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA1");
            }

            strQry.AppendLine(" WHERE UCA.COMPANY_ACCESS_IND <> 'A' ");
            strQry.AppendLine(" AND U.USER_NO = UCA.USER_NO");
            strQry.AppendLine(" AND U.USER_NO <> " + parint64CurrentUserNo);
            strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL ");
            strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");

            if (parstrAccessInd == "A")
            {
                strQry.AppendLine(" AND UCA1.USER_NO = " + parint64CurrentUserNo);
                strQry.AppendLine(" AND UCA1.COMPANY_NO = UCA.COMPANY_NO ");
                strQry.AppendLine(" AND UCA1.DATETIME_DELETE_RECORD IS NULL ");
            }

            strQry.AppendLine(" ORDER BY");
            strQry.AppendLine(" USER_ID");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" C.COMPANY_NO");
            strQry.AppendLine(",C.COMPANY_DESC");
            strQry.AppendLine(" FROM ");
            strQry.AppendLine(" InteractPayroll.dbo.COMPANY_LINK C");

            if (parstrAccessInd == "A")
            {
                strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");

                strQry.AppendLine(" WHERE UCA.COMPANY_ACCESS_IND = 'A' ");
                strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo);
                strQry.AppendLine(" AND UCA.COMPANY_NO = C.COMPANY_NO ");
            }

            strQry.AppendLine(" ORDER BY");
            strQry.AppendLine(" C.COMPANY_DESC");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1);

            if (DataSet.Tables["User"].Rows.Count > 0)
            {
                byte[]  bytTempCompress = Get_Company_CostCentres_Departments_Employees(Convert.ToInt32(DataSet.Tables["User"].Rows[0]["COMPANY_NO"]));
                DataSet pvtTempDataSet  = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
                DataSet.Merge(pvtTempDataSet);
            }

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
示例#25
0
        public void Update_Employee_Earnings_Deductions(Int64 parInt64CompanyNo, int parPayCategoryNo, string parNormPaidPerPeriod, string parSickPaidPerPeriod, byte[] patDataSetArray)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(patDataSetArray);

            StringBuilder strQry = new StringBuilder();

            for (int intRow = 0; intRow < parDataSet.Tables["Earning"].Rows.Count; intRow++)
            {
                if (parDataSet.Tables["Earning"].Rows[intRow].RowState == DataRowState.Added)
                {
                    strQry.Clear();

                    strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY");
                    strQry.AppendLine("(COMPANY_NO ");
                    strQry.AppendLine(",PAY_PERIOD_DATE");
                    strQry.AppendLine(",EMPLOYEE_NO");
                    strQry.AppendLine(",RUN_TYPE");
                    strQry.AppendLine(",EARNING_NO");
                    strQry.AppendLine(",PAY_CATEGORY_NO");
                    strQry.AppendLine(",PAY_CATEGORY_TYPE");
                    strQry.AppendLine(",RUN_NO");

                    strQry.AppendLine(",MINUTES");
                    strQry.AppendLine(",MINUTES_ROUNDED");
                    strQry.AppendLine(",HOURS_DECIMAL");
                    strQry.AppendLine(",HOURS_DECIMAL_OTHER_VALUE");
                    strQry.AppendLine(",HOURS_DECIMAL_ORIGINAL");
                    strQry.AppendLine(",DAY_DECIMAL_OTHER_VALUE");

                    strQry.AppendLine(",TOTAL)");

                    strQry.AppendLine(" VALUES ");

                    strQry.AppendLine("(" + parInt64CompanyNo);
                    strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["Earning"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                    strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                    strQry.AppendLine(",'P'");
                    strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["EARNING_NO"].ToString());
                    strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                    strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(",1");

                    strQry.AppendLine(",0");
                    strQry.AppendLine(",0");
                    strQry.AppendLine(",0");
                    strQry.AppendLine(",0");
                    strQry.AppendLine(",0");
                    strQry.AppendLine(",0");

                    strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["TOTAL"].ToString() + ")");
                }
                else
                {
                    //Updated
                    strQry.Clear();

                    strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY");

                    strQry.AppendLine(" SET ");

                    strQry.AppendLine(" TOTAL = " + parDataSet.Tables["Earning"].Rows[intRow]["TOTAL"].ToString());

                    strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                    strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Earning"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                    strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Earning"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                    strQry.AppendLine(" AND RUN_TYPE = 'P'");
                    strQry.AppendLine(" AND EARNING_NO = " + parDataSet.Tables["Earning"].Rows[intRow]["EARNING_NO"].ToString());
                    strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                    strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(" AND RUN_NO = 1");
                }

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }

            for (int intRow = 0; intRow < parDataSet.Tables["Deduction"].Rows.Count; intRow++)
            {
                strQry.Clear();

                if (parDataSet.Tables["Deduction"].Rows[intRow].RowState == DataRowState.Added)
                {
                    strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY");
                    strQry.AppendLine("(COMPANY_NO ");
                    strQry.AppendLine(",PAY_PERIOD_DATE");
                    strQry.AppendLine(",EMPLOYEE_NO");
                    strQry.AppendLine(",RUN_TYPE");
                    strQry.AppendLine(",DEDUCTION_NO");
                    strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
                    strQry.AppendLine(",PAY_CATEGORY_TYPE");
                    strQry.AppendLine(",RUN_NO");

                    strQry.AppendLine(",TOTAL");
                    strQry.AppendLine(",TOTAL_ORIGINAL)");

                    strQry.AppendLine(" VALUES ");

                    strQry.AppendLine("(" + parInt64CompanyNo);
                    strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                    strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                    strQry.AppendLine(",'P'");
                    strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString());
                    strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_SUB_ACCOUNT_NO"].ToString());
                    strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(",1");

                    strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
                    strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString() + ")");
                }
                else
                {
                    //Updated
                    if (parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString() == "500")
                    {
                        if (parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
                        {
                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY");

                            strQry.AppendLine(" SET HOURLY_RATE = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());

                            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                            strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            strQry.AppendLine(" AND RUN_TYPE = 'P'");
                            strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parPayCategoryNo);
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND RUN_NO = 1");
                        }
                        else
                        {
                            //Salaries
                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY");

                            strQry.AppendLine(" SET SALARY_MONTH_PAYMENT = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());

                            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                            strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            strQry.AppendLine(" AND RUN_TYPE = 'P'");
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND RUN_NO = 1");
                        }
                    }
                    else
                    {
                        if (parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString() == "600")
                        {
                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY");

                            strQry.AppendLine(" SET LEAVE_ACCUM_DAYS = ROUND(" + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString() + " * " + parNormPaidPerPeriod + ",2)");

                            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                            strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            //Normal Leave
                            strQry.AppendLine(" AND EARNING_NO = 200 ");
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND PROCESS_NO = 98");
                            strQry.AppendLine(" AND LEAVE_FROM_DATE > = '2015-03-01'");

                            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

                            strQry.Clear();

                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY");

                            strQry.AppendLine(" SET LEAVE_ACCUM_DAYS = ROUND(" + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString() + " * " + parSickPaidPerPeriod + ",2)");

                            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                            strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            //Sick Leave
                            strQry.AppendLine(" AND EARNING_NO = 201 ");
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND PROCESS_NO = 98");
                            strQry.AppendLine(" AND LEAVE_FROM_DATE > = '2015-03-01'");

                            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

                            strQry.Clear();

                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY");

                            strQry.AppendLine(" SET CURRENT_YEAR_LEAVE_SHIFTS_PER_RUN = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());

                            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                            strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            strQry.AppendLine(" AND RUN_TYPE = 'P'");
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND RUN_NO = 1");
                        }
                        else
                        {
                            strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY");

                            strQry.AppendLine(" SET ");

                            strQry.AppendLine(" TOTAL = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
                            strQry.AppendLine(",TOTAL_ORIGINAL = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());

                            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
                            strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
                            strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                            strQry.AppendLine(" AND RUN_TYPE = 'P'");
                            strQry.AppendLine(" AND DEDUCTION_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString());
                            strQry.AppendLine(" AND DEDUCTION_SUB_ACCOUNT_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_SUB_ACCOUNT_NO"].ToString());
                            strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                            strQry.AppendLine(" AND RUN_NO = 1");
                        }
                    }
                }

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
            }

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
        }
        public byte[] Get_Form_Records(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccessInd, string parstrFromProgram)
        {
            StringBuilder strQry  = new StringBuilder();
            DataSet       DataSet = new DataSet();

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" C.WAGE_RUN_IND");
            strQry.AppendLine(",C.SALARY_RUN_IND");
            strQry.AppendLine(",C.TIME_ATTENDANCE_RUN_IND");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");

            strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo);
            strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo);

            byte[] byteArrayDataset = Get_User_Level_Records(parint64CompanyNo, parint64CurrentUserNo, parstrCurrentUserAccessInd, parstrFromProgram);

            DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteArrayDataset);

            DataSet.Merge(TempDataSet);

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" LC.COMPANY_NO");
            strQry.AppendLine(",LC.EMPLOYEE_NO");
            strQry.AppendLine(",LC.EARNING_NO");
            strQry.AppendLine(",LC.LEAVE_REC_NO");
            strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
            strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
            strQry.AppendLine(",LC.LEAVE_DESC");
            strQry.AppendLine(",LC.PROCESS_NO");
            strQry.AppendLine(",LC.LEAVE_FROM_DATE");
            strQry.AppendLine(",LC.LEAVE_TO_DATE");

            strQry.AppendLine(",ISNULL(LC.LEAVE_OPTION,'D') AS LEAVE_OPTION");
            strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_HOURS_DECIMAL,0),2) AS LEAVE_HOURS_DECIMAL");
            strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_DAYS_DECIMAL,0),2) AS LEAVE_DAYS_DECIMAL");
            strQry.AppendLine(",DATEDIFF(d,LEAVE_FROM_DATE,LEAVE_TO_DATE ) + 1 AS DATE_DIFF_NO_DAYS");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");

            //2013-09-10
            if (parstrCurrentUserAccessInd == "U")
            {
                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(SELECT DISTINCT");
                strQry.AppendLine(" EMPLOYEE_NO");
                strQry.AppendLine(",PAY_CATEGORY_TYPE");

                strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
                strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
                strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo + ") AS USER_TABLE");

                strQry.AppendLine(" ON LC.EMPLOYEE_NO = USER_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = USER_TABLE.PAY_CATEGORY_TYPE");
            }

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
            strQry.AppendLine(" ON LC.COMPANY_NO = E.COMPANY_NO ");
            strQry.AppendLine(" AND LC.EMPLOYEE_NO = E.EMPLOYEE_NO ");
            strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
            strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
            strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
            strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
            //Default Pay Category Parameters Apply to Leave
            strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
            strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");

            strQry.AppendLine(" WHERE LC.COMPANY_NO = " + parint64CompanyNo);

            //Next Run
            strQry.AppendLine(" AND LC.PROCESS_NO = 0");


            if (parstrFromProgram == "X")
            {
                strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = 'T'");
            }
            else
            {
                strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE IN ('W','S')");
            }

            strQry.AppendLine(" ORDER BY");
            strQry.AppendLine(" LEAVE_FROM_DATE");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo);

            DataSet.Tables.Add("PayrollType");
            DataTable PayrollTypeDataTable = new DataTable("PayrollType");

            DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));

            if (DataSet.Tables["AuthorsiseLevel"].Rows.Count > 0)
            {
                DataView PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
                                                            "PAY_CATEGORY_TYPE = 'W'",
                                                            "",
                                                            DataViewRowState.CurrentRows);

                if (PayrollTypeDataView.Count > 0)
                {
                    DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                    drDataRow["PAYROLL_TYPE_DESC"] = "Wages";

                    DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
                }

                PayrollTypeDataView = null;
                PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
                                                   "PAY_CATEGORY_TYPE = 'S'",
                                                   "",
                                                   DataViewRowState.CurrentRows);

                if (PayrollTypeDataView.Count > 0)
                {
                    DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                    drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";

                    DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
                }

                PayrollTypeDataView = null;
                PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
                                                   "PAY_CATEGORY_TYPE = 'T'",
                                                   "",
                                                   DataViewRowState.CurrentRows);

                if (PayrollTypeDataView.Count > 0)
                {
                    DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();

                    drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance";

                    DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
                }

                DataSet.AcceptChanges();
            }

            strQry.Clear();
            strQry.AppendLine(" SELECT DISTINCT ");
            strQry.AppendLine(" EN.COMPANY_NO");
            strQry.AppendLine(",EN.EARNING_NO");
            strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EN.EARNING_DESC");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
            strQry.AppendLine(" ON EN.COMPANY_NO = LC.COMPANY_NO ");
            strQry.AppendLine(" AND EN.EARNING_NO = LC.EARNING_NO ");
            strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");

            strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parint64CompanyNo);
            strQry.AppendLine(" AND EN.EARNING_NO >= 200");
            strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");
            strQry.AppendLine(" ORDER BY ");
            strQry.AppendLine(" EN.EARNING_DESC");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo);

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" E.COMPANY_NO");
            strQry.AppendLine(",E.EMPLOYEE_NO");
            strQry.AppendLine(",E.EMPLOYEE_CODE");
            strQry.AppendLine(",E.EMPLOYEE_NAME");
            strQry.AppendLine(",E.EMPLOYEE_SURNAME");
            strQry.AppendLine(",E.PAY_CATEGORY_TYPE");

            //Used For Leave Totals
            strQry.AppendLine(",EPC.PAY_CATEGORY_NO");

            strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
            strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
            strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
            //Default Pay Category Parameters Apply to Leave
            strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
            strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");

            if (parstrCurrentUserAccessInd == "U")
            {
                strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT ");

                strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
                strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO");
                strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
                strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
                strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
            }

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS");
            strQry.AppendLine(" ON E.COMPANY_NO = LS.COMPANY_NO");
            strQry.AppendLine(" AND E.LEAVE_SHIFT_NO = LS.LEAVE_SHIFT_NO");
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LS.PAY_CATEGORY_TYPE");
            strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL");

            strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
            strQry.AppendLine(" ON E.COMPANY_NO = LC.COMPANY_NO ");
            strQry.AppendLine(" AND E.EMPLOYEE_NO = LC.EMPLOYEE_NO ");
            strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");
            //Next Run
            strQry.AppendLine(" AND LC.PROCESS_NO = 0");

            strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
            //Removed 2012-04-06 - Allow For Employees Clocked on Client That have Not been Activated to be seen
            //strQry.AppendLine(" AND EMPLOYEE_TAKEON_IND = 'Y'");
            strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
            strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
            strQry.AppendLine(" ORDER BY ");
            strQry.AppendLine(" E.EMPLOYEE_CODE");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo);

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" PAY_CATEGORY_NO");
            strQry.AppendLine(",DAY_NO");
            strQry.AppendLine(",TIME_DECIMAL");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL");

            strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);

            strQry.AppendLine(" ORDER BY");
            strQry.AppendLine(" PAY_CATEGORY_NO");
            strQry.AppendLine(",DAY_NO");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategoryTimeDecimal", parint64CompanyNo);

            DateTime dtDateNowAYearAgo = DateTime.Now.AddYears(-1);

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" PUBLIC_HOLIDAY_DATE");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY PH ");

            strQry.AppendLine(" WHERE  PUBLIC_HOLIDAY_DATE > '" + dtDateNowAYearAgo.ToString("yyyy-MM-dd") + "'");

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PublicHoliday", parint64CompanyNo);

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);

            DataSet.Dispose();
            DataSet = null;

            return(bytCompress);
        }
示例#27
0
        public void Convert_Client_Records(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet)
        {
            DataSet       DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet);
            StringBuilder strQry  = new StringBuilder();

            strQry.Clear();

            strQry.AppendLine(" SELECT ");
            strQry.AppendLine(" A.TABLE_NAME");

            strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS A ");

            strQry.AppendLine(" INNER JOIN InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS B");
            strQry.AppendLine(" ON B.TABLE_NAME = A.TABLE_NAME ");
            strQry.AppendLine(" AND B.COLUMN_NAME = 'PAY_CATEGORY_NO' ");

            strQry.AppendLine(" WHERE A.COLUMN_NAME = 'PAY_CATEGORY_TYPE'");

            clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "TablesToFix");

            for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
            {
                strQry.Clear();

                strQry.AppendLine(" UPDATE UE ");

                strQry.AppendLine(" SET UE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_EMPLOYEE UE ");

                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" PC.COMPANY_NO");
                strQry.AppendLine(",EPC.EMPLOYEE_NO");
                strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                strQry.AppendLine(") AS JOIN_TABLE");

                strQry.AppendLine(" ON UE.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
                strQry.AppendLine(" AND UE.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND UE.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                strQry.Clear();

                strQry.AppendLine(" UPDATE E ");

                strQry.AppendLine(" SET E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");

                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" PC.COMPANY_NO");
                strQry.AppendLine(",EPC.EMPLOYEE_NO");
                strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                strQry.AppendLine(") AS JOIN_TABLE");

                strQry.AppendLine(" ON E.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
                strQry.AppendLine(" AND E.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                //Break
                strQry.Clear();

                if (parstrToPayCategoryType == "W")
                {
                    strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT ");
                }
                else
                {
                    if (parstrToPayCategoryType == "S")
                    {
                        strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
                    }
                    else
                    {
                        //Time Attend
                        strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
                    }
                }

                strQry.AppendLine("(COMPANY_NO ");
                strQry.AppendLine(",EMPLOYEE_NO ");
                strQry.AppendLine(",PAY_CATEGORY_NO ");
                strQry.AppendLine(",BREAK_DATE ");
                strQry.AppendLine(",BREAK_SEQ ");
                strQry.AppendLine(",BREAK_TIME_IN_MINUTES ");
                strQry.AppendLine(",BREAK_TIME_OUT_MINUTES ");
                strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES ");
                strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) ");

                strQry.AppendLine(" SELECT ");

                strQry.AppendLine(" A.COMPANY_NO ");
                strQry.AppendLine(",A.EMPLOYEE_NO ");
                strQry.AppendLine(",A.PAY_CATEGORY_NO ");
                strQry.AppendLine(",A.BREAK_DATE ");
                strQry.AppendLine(",A.BREAK_SEQ ");
                strQry.AppendLine(",A.BREAK_TIME_IN_MINUTES ");
                strQry.AppendLine(",A.BREAK_TIME_OUT_MINUTES ");
                strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES ");
                strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES ");

                if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A");
                }
                else
                {
                    if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
                    {
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A");
                    }
                    else
                    {
                        //Time Attend
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A");
                    }
                }

                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" PC.COMPANY_NO");
                strQry.AppendLine(",EPC.EMPLOYEE_NO");
                strQry.AppendLine(",PC.PAY_CATEGORY_NO");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                strQry.AppendLine(") AS JOIN_TABLE");

                strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
                strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                strQry.Clear();

                strQry.AppendLine(" DELETE A ");

                if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A ");
                }
                else
                {
                    if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
                    {
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A ");
                    }
                    else
                    {
                        //Time Attend
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A ");
                    }
                }

                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" PC.COMPANY_NO");
                strQry.AppendLine(",EPC.EMPLOYEE_NO");
                strQry.AppendLine(",PC.PAY_CATEGORY_NO");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                strQry.AppendLine(") AS JOIN_TABLE");

                strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
                strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                //Timesheets
                strQry.Clear();

                if (parstrToPayCategoryType == "W")
                {
                    strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
                }
                else
                {
                    if (parstrToPayCategoryType == "S")
                    {
                        strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
                    }
                    else
                    {
                        //Time Attend
                        strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
                    }
                }

                strQry.AppendLine("(COMPANY_NO ");
                strQry.AppendLine(",EMPLOYEE_NO ");
                strQry.AppendLine(",PAY_CATEGORY_NO ");
                strQry.AppendLine(",TIMESHEET_DATE ");
                strQry.AppendLine(",TIMESHEET_SEQ ");
                strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES ");
                strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES ");
                strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES ");
                strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) ");

                strQry.AppendLine(" SELECT ");

                strQry.AppendLine(" A.COMPANY_NO ");
                strQry.AppendLine(",A.EMPLOYEE_NO ");
                strQry.AppendLine(",A.PAY_CATEGORY_NO ");
                strQry.AppendLine(",A.TIMESHEET_DATE ");
                strQry.AppendLine(",A.TIMESHEET_SEQ ");
                strQry.AppendLine(",A.TIMESHEET_TIME_IN_MINUTES ");
                strQry.AppendLine(",A.TIMESHEET_TIME_OUT_MINUTES ");
                strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES ");
                strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES ");

                if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
                }
                else
                {
                    if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
                    {
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
                    }
                    else
                    {
                        //Time Attend
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A ");
                    }
                }

                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" PC.COMPANY_NO");
                strQry.AppendLine(",EPC.EMPLOYEE_NO");
                strQry.AppendLine(",PC.PAY_CATEGORY_NO");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                strQry.AppendLine(") AS JOIN_TABLE");

                strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
                strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                strQry.Clear();

                strQry.AppendLine(" DELETE A ");

                if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
                {
                    strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
                }
                else
                {
                    if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
                    {
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
                    }
                    else
                    {
                        //Time Attend
                        strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A ");
                    }
                }

                strQry.AppendLine(" INNER JOIN ");

                strQry.AppendLine("(");

                strQry.AppendLine(" SELECT ");
                strQry.AppendLine(" PC.COMPANY_NO");
                strQry.AppendLine(",EPC.EMPLOYEE_NO");
                strQry.AppendLine(",PC.PAY_CATEGORY_NO");

                strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");

                strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
                strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");

                strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
                strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
                strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));

                strQry.AppendLine(") AS JOIN_TABLE");

                strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
                strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
                strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");

                clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());

                for (int intTableRow = 0; intTableRow < DataSet.Tables["TablesToFix"].Rows.Count; intTableRow++)
                {
                    strQry.Clear();

                    strQry.AppendLine(" UPDATE A ");

                    strQry.AppendLine(" SET A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));

                    strQry.AppendLine(" FROM InteractPayrollClient.dbo." + DataSet.Tables["TablesToFix"].Rows[intTableRow]["TABLE_NAME"].ToString() + " A ");

                    strQry.AppendLine(" WHERE A.COMPANY_NO = " + parint64CompanyNo);
                    strQry.AppendLine(" AND A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
                    strQry.AppendLine(" AND A.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());

                    clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
                }
            }
        }
示例#28
0
        public byte[] Update_Records(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess, int parintPaycategoryNo, string parstrPayrollType, string parstrType, double pardblIncrease, byte[] parbyteDataSet)
        {
            DataSet       parDataSet      = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
            StringBuilder strQry          = new StringBuilder();
            double        dblCurrentValue = 0;
            double        dblNewValue     = 0;
            string        strEmployeeNos  = "(";

            //Remove Rows from DataTable
            for (int intRow = 0; intRow < parDataSet.Tables["Employee"].Rows.Count; intRow++)
            {
                if (parstrType == "P")
                {
                    dblCurrentValue = Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["HOURLY_RATE"]);
                    dblNewValue     = dblCurrentValue + Math.Round((dblCurrentValue * pardblIncrease) / 100, 2);
                }

                strQry.Clear();
                strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");

                strQry.AppendLine(" SET ");
                strQry.AppendLine(" OLD_HOURLY_RATE = HOURLY_RATE");

                if (parstrType == "P")
                {
                    strQry.AppendLine(",HOURLY_RATE = " + dblNewValue);
                    strQry.AppendLine(",SALARY_WAGE_INCREASE = " + pardblIncrease);
                }
                else
                {
                    strQry.AppendLine(",HOURLY_RATE = " + parDataSet.Tables["Employee"].Rows[intRow]["HOURLY_RATE"].ToString());
                    strQry.AppendLine(",SALARY_WAGE_INCREASE = " + parDataSet.Tables["Employee"].Rows[intRow]["SALARY_WAGE_INCREASE"].ToString());
                }

                strQry.AppendLine(",SALARY_WAGE_INCREASE_DATETIME = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");

                strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo.ToString());
                strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parintPaycategoryNo.ToString());
                strQry.AppendLine(" AND PAY_CATEGORY_TYPE = '" + parstrPayrollType + "'");
                strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
                strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");

                clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

                if (intRow == 0)
                {
                    strEmployeeNos += parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString();
                }
                else
                {
                    strEmployeeNos += "," + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString();
                }
            }

            strEmployeeNos += ")";

            DataSet DataSet = new System.Data.DataSet();

            strQry = Get_Employee_Script(parint64CurrentUserNo, parstrCurrentUserAccess);

            strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = " + parintPaycategoryNo);
            strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = '" + parstrPayrollType + "'");
            strQry.AppendLine(" AND EPC.EMPLOYEE_NO IN " + strEmployeeNos);

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);

            byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
            DataSet.Dispose();
            DataSet = null;

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            return(bytCompress);
        }
        public int Insert_New_Record(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
        {
            DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);

            int           intLeaveTypeNo;
            StringBuilder strQry = new StringBuilder();

            strQry.Clear();
            strQry.AppendLine(" SELECT");
            strQry.AppendLine(" MAX(EARNING_NO) AS MAX_LEAVE_TYPE_NO");

            strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING ");

            strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["LeaveType"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine(" AND EARNING_NO > 199");

            DataSet DataSet = new DataSet();

            clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);

            if (DataSet.Tables[0].Rows[0].IsNull("MAX_LEAVE_TYPE_NO") == true)
            {
                //NB 200 = Normal LeaveType 201 = Sick LeaveType
                intLeaveTypeNo = 202;
            }
            else
            {
                intLeaveTypeNo = Convert.ToInt32(DataSet.Tables[0].Rows[0]["MAX_LEAVE_TYPE_NO"]) + 1;
            }

            DataSet.Dispose();
            DataSet = null;

            strQry.Clear();
            strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING");
            strQry.AppendLine("(COMPANY_NO");
            strQry.AppendLine(",PAY_CATEGORY_TYPE");
            strQry.AppendLine(",EARNING_NO");
            strQry.AppendLine(",TIE_BREAKER");
            strQry.AppendLine(",EARNING_DESC");
            strQry.AppendLine(",LEAVE_PERCENTAGE");
            strQry.AppendLine(",EARNING_REPORT_HEADER1");
            strQry.AppendLine(",EARNING_REPORT_HEADER2");
            strQry.AppendLine(",IRP5_CODE");
            strQry.AppendLine(",DATETIME_NEW_RECORD");
            strQry.AppendLine(",USER_NO_NEW_RECORD");
            strQry.AppendLine(",EARNING_DEL_IND)");
            strQry.AppendLine(" VALUES ");
            strQry.AppendLine("(" + parDataSet.Tables["LeaveType"].Rows[0]["COMPANY_NO"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
            strQry.AppendLine("," + intLeaveTypeNo);
            strQry.AppendLine("," + parDataSet.Tables["LeaveType"].Rows[0]["TIE_BREAKER"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["EARNING_DESC"].ToString()));
            strQry.AppendLine("," + parDataSet.Tables["LeaveType"].Rows[0]["LEAVE_PERCENTAGE"].ToString());
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["EARNING_REPORT_HEADER1"].ToString()));
            strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["EARNING_REPORT_HEADER2"].ToString()));
            //20170418 - Fix IRP5Code
            strQry.AppendLine(",3601");
            strQry.AppendLine(",GETDATE()");
            strQry.AppendLine("," + parint64CurrentUserNo);
            strQry.AppendLine(",'Y')");

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);

            parDataSet.Dispose();
            parDataSet = null;

            strQry.Clear();

            strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
            strQry.AppendLine(" SET BACKUP_DB_IND = 1");
            strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);

            clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);

            return(intLeaveTypeNo);
        }