public void Update_Record(string parstrRemoteBackupSiteName)
{
//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 DataSet = new DataSet();
if (parstrRemoteBackupSiteName == "")
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
}
else
{
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" SITE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "RemoteBackupSiteName");
if (DataSet.Tables["RemoteBackupSiteName"].Rows.Count > 0)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
strQry.AppendLine(" SET ");
strQry.AppendLine(" SITE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrRemoteBackupSiteName));
}
else
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
strQry.AppendLine(" (SITE_NAME)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + clsDBConnectionObjects.Text2DynamicSQL(parstrRemoteBackupSiteName) + ")");
}
}
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
DataSet.Dispose();
DataSet = null;
}
public int Insert_Group(Int64 parint64CompanyNo, string parstrGroupDesc, 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);
int intGroupNo = 0;
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(GROUP_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.GROUPS");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intGroupNo = 1;
}
else
{
intGroupNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.GROUPS");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",GROUP_NO");
strQry.AppendLine(",GROUP_DESC)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parint64CompanyNo);
strQry.AppendLine("," + intGroupNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrGroupDesc) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
Insert_Group_Employee_Link(parint64CompanyNo, intGroupNo, DataSet);
return(intGroupNo);
}
public int Backup_DataBase()
{
int intReturnCode = 9;
try
{
StringBuilder strQry = new StringBuilder();
string strFileDirectory = AppDomain.CurrentDomain.BaseDirectory + "Backup";
if (Directory.Exists(strFileDirectory))
{
}
else
{
Directory.CreateDirectory(strFileDirectory);
}
string strDataBaseName = "InteractPayrollClient";
#if (DEBUG)
strDataBaseName = "InteractPayrollClient_Debug";
#endif
string strBackupFileName = strDataBaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".bak";
strQry.Clear();
strQry.AppendLine("BACKUP DATABASE " + strDataBaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "' WITH CHECKSUM ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), 60);
intReturnCode = 0;
}
catch (Exception ex)
{
WriteExceptionLog("Backup_DataBase", ex);
}
return(intReturnCode);
}
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_Records(string parstrParm)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
int intTimeMinutes = 0;
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" EC.EMPLOYEE_NO");
strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS EC ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON EC.EMPLOYEE_NO = E.EMPLOYEE_3RD_PARTY_CODE ");
//No Link
strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE IS NULL");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "LinksMissing");
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(",CLOCKING_TIME");
strQry.AppendLine(",CLOCKING_TYPE");
strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS ");
strQry.AppendLine(" WHERE ISNULL(USED_IND,0) = 0");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clockings");
for (int intCount = 0; intCount < DataSet.Tables["Clockings"].Rows.Count; intCount++)
{
strQry.Clear();
intTimeMinutes = (60 * Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Hour) + Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Minute;
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_CLOCK_TIME");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",TIMESHEET_TIME_MINUTES");
strQry.AppendLine(",CLOCKED_BOUNDARY_TIME_MINUTES");
strQry.AppendLine(",IN_OUT_IND)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 0 ");
strQry.AppendLine(",EPC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
//TIMESHEET_DATE
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd")));
//TIMESHEET_TIME_MINUTES
strQry.AppendLine("," + intTimeMinutes);
//CLOCKED_BOUNDARY_TIME_MINUTES Same as TIMESHEET_TIME_MINUTES
strQry.AppendLine("," + intTimeMinutes);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
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(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_CLOCK_TIME DCT");
strQry.AppendLine(" ON DCT.DEVICE_NO = 0");
strQry.AppendLine(" AND DCT.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND DCT.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND DCT.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DCT.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND DCT.TIMESHEET_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd")));
strQry.AppendLine(" AND DCT.TIMESHEET_TIME_MINUTES = " + intTimeMinutes);
strQry.AppendLine(" AND DCT.IN_OUT_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString()));
//No Record Exists
strQry.AppendLine(" AND DCT.COMPANY_NO IS NULL");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" UPDATE ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS ");
strQry.AppendLine(" SET USED_IND = 1");
strQry.AppendLine(" WHERE EMPLOYEE_NO = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString()));
strQry.AppendLine(" AND CLOCKING_TIME = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss.fff")));
strQry.AppendLine(" AND CLOCKING_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
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();
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 ");
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" D.DEVICE_NO");
strQry.AppendLine(",D.DEVICE_DESC");
strQry.AppendLine(",D.DEVICE_USAGE");
strQry.AppendLine(",D.TIME_ATTEND_CLOCK_FIRST_LAST_IND");
strQry.AppendLine(",D.CLOCK_IN_OUT_PARM");
strQry.AppendLine(",D.CLOCK_IN_RANGE_FROM");
strQry.AppendLine(",D.CLOCK_IN_RANGE_TO");
strQry.AppendLine(",D.LOCK_OUT_MINUTES");
strQry.AppendLine(",D.COMPANY_NO");
strQry.AppendLine(",ISNULL(D.LAN_WAN_IND,'L') AS LAN_WAN_IND");
strQry.AppendLine(",FAR_REQUESTED");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE D");
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 = D.COMPANY_NO ");
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" D.DEVICE_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clock");
for (int intRow = 0; intRow < DataSet.Tables["Clock"].Rows.Count; intRow++)
{
if (DataSet.Tables["Clock"].Rows[intRow]["FAR_REQUESTED"] == System.DBNull.Value)
{
// 1/10 000
DataSet.Tables["Clock"].Rows[intRow]["FAR_REQUESTED"] = 214748;
strQry.Clear();
strQry.AppendLine(" UPDATE DEVICE");
strQry.AppendLine(" SET FAR_REQUESTED = 214748");
strQry.AppendLine(" WHERE DEVICE_NO = " + DataSet.Tables["Clock"].Rows[intRow]["DEVICE_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
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);
}
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());
}
}
}
public void Update_Records(Int64 parint64CompanyNo, int parintDeviceNo, 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();
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_EMPLOYEE_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["EmployeeChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_EMPLOYEE_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["EMPLOYEE_NO"].ToString());
strQry.AppendLine("," + DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["PayCategoryChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["PayCategoryChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategoryChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK_ACTIVE DEL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" DPCLA.DEVICE_NO");
strQry.AppendLine(",DPCLA.COMPANY_NO");
strQry.AppendLine(",DPCLA.PAY_CATEGORY_NO");
strQry.AppendLine(",DPCLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK_ACTIVE DPCLA ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK DPCL");
strQry.AppendLine(" ON DPCL.DEVICE_NO = DPCLA.DEVICE_NO");
strQry.AppendLine(" AND DPCL.COMPANY_NO = DPCLA.COMPANY_NO");
strQry.AppendLine(" AND DPCL.PAY_CATEGORY_NO = DPCLA.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DPCL.PAY_CATEGORY_TYPE = DPCLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE DPCLA.DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND DPCLA.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DPCL.PAY_CATEGORY_NO IS NULL) AS TEMP_TABLE");
strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO");
strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["DepartmentChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEPARTMENT_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["DEPARTMENT_NO"].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK_ACTIVE DEL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" DDLA.DEVICE_NO");
strQry.AppendLine(",DDLA.COMPANY_NO");
strQry.AppendLine(",DDLA.PAY_CATEGORY_NO");
strQry.AppendLine(",DDLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",DDLA.DEPARTMENT_NO ");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK_ACTIVE DDLA ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK DDL");
strQry.AppendLine(" ON DDL.DEVICE_NO = DDLA.DEVICE_NO");
strQry.AppendLine(" AND DDL.COMPANY_NO = DDLA.COMPANY_NO");
strQry.AppendLine(" AND DDL.PAY_CATEGORY_NO = DDLA.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DDL.PAY_CATEGORY_TYPE = DDLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND DDL.DEPARTMENT_NO = DDLA.DEPARTMENT_NO ");
strQry.AppendLine(" WHERE DDLA.DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND DDLA.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DDL.DEPARTMENT_NO IS NULL) AS TEMP_TABLE");
strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO");
strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND DEL.DEPARTMENT_NO = TEMP_TABLE.DEPARTMENT_NO ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["GroupChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_GROUP_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",GROUP_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["GroupChosen"].Rows[intRowCount]["GROUP_NO"].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK_ACTIVE DEL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" DGLA.DEVICE_NO");
strQry.AppendLine(",DGLA.COMPANY_NO");
strQry.AppendLine(",DGLA.GROUP_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK_ACTIVE DGLA ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_GROUP_LINK DGL");
strQry.AppendLine(" ON DGLA.DEVICE_NO = DGL.DEVICE_NO");
strQry.AppendLine(" AND DGLA.COMPANY_NO = DGL.COMPANY_NO");
strQry.AppendLine(" AND DGLA.GROUP_NO = DGL.GROUP_NO");
strQry.AppendLine(" WHERE DGLA.DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND DGLA.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DGL.GROUP_NO IS NULL) AS TEMP_TABLE");
strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO");
strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND DEL.GROUP_NO = TEMP_TABLE.GROUP_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
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());
}
}
public byte[] Get_Form_Records()
{
//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();
byte[] bytCompress;
bool blnInsert = false;
int intYear = DateTime.Now.Year;
if (DateTime.Now.Month < 3)
{
intYear = intYear - 1;
}
DateTime dtDateTimeStart = new DateTime(intYear, 3, 1);
DateTime dtDateTimeEnd = new DateTime(intYear + 1, 3, 1).AddDays(-1);
DataSet DataSet = new DataSet();
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, "Software");
if (DataSet.Tables["Software"].Rows.Count == 0)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE");
strQry.AppendLine("(FINGERPRINT_SOFTWARE_IND)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("('D')");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
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, "IdentifyVerify");
if (DataSet.Tables["IdentifyVerify"].Rows.Count == 0)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD");
strQry.AppendLine("(IDENTIFY_THRESHOLD_VALUE");
strQry.AppendLine(",VERIFY_THRESHOLD_VALUE)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(45");
strQry.AppendLine(",25)");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" READ_OPTION_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.READ_OPTION ");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ReadOption");
if (DataSet.Tables["ReadOption"].Rows.Count == 0)
{
//New Database Created
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION ");
strQry.AppendLine("(READ_OPTION_NO");
strQry.AppendLine(",READ_OPTION_DESC) ");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(0");
strQry.AppendLine(",'None')");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION ");
strQry.AppendLine("(READ_OPTION_NO");
strQry.AppendLine(",READ_OPTION_DESC) ");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(1");
strQry.AppendLine(",'FingerPrint')");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION ");
strQry.AppendLine("(READ_OPTION_NO");
strQry.AppendLine(",READ_OPTION_DESC) ");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(2");
strQry.AppendLine(",'RFID Card Only')");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION ");
strQry.AppendLine("(READ_OPTION_NO");
strQry.AppendLine(",READ_OPTION_DESC) ");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(3");
strQry.AppendLine(",'RFID Card And FingerPrint')");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(DAY_DATE) AS MAX_DATE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DATES ");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
if (DataSet.Tables["Temp"].Rows[0]["MAX_DATE"] == System.DBNull.Value)
{
blnInsert = true;
}
else
{
if (Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]) < DateTime.Now)
{
blnInsert = true;
dtDateTimeStart = new DateTime(Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).Year, Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).Month, Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).Day).AddDays(1);
}
else
{
if (DateTime.Now.Month == 1
| DateTime.Now.Month == 2)
{
DateTime newDateTimeCheck = new DateTime(DateTime.Now.Year, 3, 1).AddDays(-1);
if (newDateTimeCheck.ToString("yyyyMMdd") == Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).ToString("yyyyMMdd"))
{
blnInsert = true;
dtDateTimeStart = dtDateTimeStart.AddYears(1);
dtDateTimeEnd = dtDateTimeEnd.AddYears(1);
}
}
}
}
if (blnInsert == true)
{
while (true)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DATES");
strQry.AppendLine("(DAY_DATE");
strQry.AppendLine(",DAY_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("('" + dtDateTimeStart.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + Convert.ToInt32(dtDateTimeStart.DayOfWeek) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
if (dtDateTimeStart.Year == dtDateTimeEnd.Year
& dtDateTimeStart.Month == dtDateTimeEnd.Month
& dtDateTimeStart.Day == dtDateTimeEnd.Day)
{
break;
}
dtDateTimeStart = dtDateTimeStart.AddDays(1);
}
}
DataSet = null;
DataSet = new DataSet();
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");
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public void Update_Employee(Int64 parint64CompanyNo, Int64 parintEmployeeNo, string parstrRFIDCardNo, string parstrUseEmpNo, 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["EmployeeFingerTemplate"].Rows.Count; intRow++)
{
if (DataSet.Tables["EmployeeFingerTemplate"].Rows[0].RowState == DataRowState.Added)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",FINGER_NO");
strQry.AppendLine(",FINGER_TEMPLATE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parint64CompanyNo);
strQry.AppendLine("," + parintEmployeeNo);
strQry.AppendLine("," + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString());
strQry.AppendLine(",@FINGER_TEMPLATE) ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), (byte[])DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_TEMPLATE"], "@FINGER_TEMPLATE");
}
else
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE_DELETE");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",FINGER_NO");
strQry.AppendLine(",CREATION_DATETIME)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",FINGER_NO");
strQry.AppendLine(",CREATION_DATETIME ");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString());
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString());
strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["EmployeeFingerTemplate"].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.EMPLOYEE_FINGERPRINT_TEMPLATE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString());
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString());
strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
//2017-10-05 (Moved To Internet)
//if (parstrRFIDCardNo == "")
//{
// strQry.Clear();
// strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.EMPLOYEE_RFID_CARD");
// strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString());
// strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString());
// clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//}
//2017-10-05 (Moved To Internet)
//strQry.Clear();
//strQry.AppendLine(" UPDATE EMPLOYEE");
//strQry.AppendLine(" SET USE_EMPLOYEE_NO_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parstrUseEmpNo));
//strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString());
//strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString());
//clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
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());
}
}
}