public byte[] Get_Form_Records()
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
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(" DEVICE_NO");
strQry.AppendLine(",DEVICE_DESC");
strQry.AppendLine(",DEVICE_USAGE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE D");
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);
}
public int Restore_DataBase(string parstrFileName)
{
int intReturnCode = 1;
try
{
StringBuilder strQry = new StringBuilder();
string strDataBaseName = "InteractPayrollClient";
#if (DEBUG)
strDataBaseName = "InteractPayrollClient_Debug";
#endif
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" SPID");
strQry.AppendLine(" FROM MASTER.dbo.SYSPROCESSES");
strQry.AppendLine(" WHERE DBID = DB_ID('" + strDataBaseName + "')");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" SPID DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Spid", 60);
for (int intRow = 0; intRow < DataSet.Tables["Spid"].Rows.Count; intRow++)
{
string strKill = "USE MASTER KILL " + DataSet.Tables["Spid"].Rows[intRow]["spid"].ToString();
try
{
clsDBConnectionObjects.Execute_SQLCommand_Client(strKill, 60);
}
catch
{
}
}
strQry.Clear();
strQry.AppendLine("RESTORE DATABASE " + strDataBaseName + " FROM DISK = '" + parstrFileName + "' WITH REPLACE");
clsDBConnectionObjects.Execute_SQLCommand_Restore_Client(strQry.ToString(), 60);
intReturnCode = 0;
}
catch (Exception ex)
{
WriteExceptionLog("Restore_DataBase " + parstrFileName, ex);
}
return(intReturnCode);
}
public byte[] Get_Form_Records()
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" SITE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "RemoteBackupSiteName");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_PayCategory_Records(Int64 parint64CompanyNo, string parstrPayrollType)
{
//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();
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" EPC.PAY_CATEGORY_NO");
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(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" ON E.COMPANY_NO = PC.COMPANY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo.ToString());
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "PayCategory");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
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);
}
private void btnRunFix_Click(object sender, EventArgs e)
{
try
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//Money Mine (Johnny)
Int64 Int64CompanyNo = 11;
object[] objParm = new object[1];
objParm[0] = Int64CompanyNo;
bool blnAllow = (Boolean)clsISUtilities.DynamicFunction("Get_Form_Records", objParm);
if (blnAllow == true)
{
DataSet = null;
DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
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(",DEPARTMENT_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE ");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Employee");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY ");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "EmployeePayCategory");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
objParm = new object[2];
objParm[0] = Int64CompanyNo;
objParm[1] = bytCompress;
clsISUtilities.DynamicFunction("Upload_Records", objParm);
MessageBox.Show("Upload Records Successful", "Communication", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("You are NOT allowed to Upload Records", "Communication", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception eException)
{
MessageBox.Show("Error = " + eException.Message);
clsISUtilities.ErrorHandler(eException);
}
}
public byte[] Logon_Client_DataBase()
{
//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
try
{
//2017-05-03 Fix To Make Sure Databases are In Sync
clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase();
clsFixInteractPayrollClientDatabase.Fix_Client_Database();
}
catch
{
}
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
try
{
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
//2013-07-18 - Empty (This Can be Removed in 2 -3 Months Time (For Backkward Compatibility)
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'ZZ'");
//Used to Delete (Cleanup) where File is Not Downloaded from Internet Site
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ServerFile");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_LAYER_IND");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
//Empty - Used to Add Files To be Deleted from Client Database
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'Z'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FileToDelete");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_LAYER_IND");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",FILE_LAST_UPDATED_DATE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
//2013-06-27
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'P'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ClientFile");
string strBaseDirectory = AppDomain.CurrentDomain.BaseDirectory;
#if (DEBUG)
strBaseDirectory += "bin\\";
#endif
//2013-06-27
DirectoryInfo di = new DirectoryInfo(strBaseDirectory);
FileInfo[] fiFiles = di.GetFiles("*.*");
DataRow DataRow;
foreach (FileInfo fi in fiFiles)
{
DataRow = DataSet.Tables["ClientFile"].NewRow();
DataRow["FILE_LAYER_IND"] = "S";
DataRow["FILE_NAME"] = fi.Name;
DataRow["FILE_LAST_UPDATED_DATE"] = fi.LastWriteTime;
DataSet.Tables["ClientFile"].Rows.Add(DataRow);
}
DataRow[] tempDataRows = DataSet.Tables["ClientFile"].Select("FILE_LAYER_IND ='S' AND (FILE_NAME Like '%dll_' OR FILE_NAME Like '%exe_')");
foreach (DataRow dr in tempDataRows)
{
string strName = dr["FILE_NAME"].ToString().Replace("dll_", "dll").Replace("exe_", "exe");
//Find File with Same Name Excluding '_'
DataView fiDataView = new DataView(DataSet.Tables["ClientFile"], "FILE_LAYER_IND ='S' AND FILE_NAME = '" + strName + "'", "", DataViewRowState.CurrentRows);
if (fiDataView.Count == 1)
{
fiDataView.Delete(0);
}
}
DataSet.AcceptChanges();
DataTable DataTable = new DataTable("ReturnValues");
DataTable.Columns.Add("MACHINE_NAME", typeof(String));
DataTable.Columns.Add("MACHINE_IP", typeof(String));
DataSet.Tables.Add(DataTable);
DataRow = DataSet.Tables["ReturnValues"].NewRow();
try
{
DataRow["MACHINE_NAME"] = System.Net.Dns.GetHostName();
}
catch
{
DataRow["MACHINE_NAME"] = "UNKNOWN";
}
try
{
DataRow["MACHINE_IP"] = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList[0].ToString();
}
catch
{
DataRow["MACHINE_IP"] = "UNKNOWN";
}
DataSet.Tables["ReturnValues"].Rows.Add(DataRow);
DataSet.AcceptChanges();
}
catch (Exception ex)
{
string strInnerExceptionMessage = "";
if (ex.InnerException != null)
{
strInnerExceptionMessage = ex.InnerException.Message;
}
using (StreamWriter writeLog = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "busClientPayrollLogon_Log.txt", true))
{
writeLog.WriteLine(" ********** Exception = " + ex.Message + " " + strInnerExceptionMessage);
}
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
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 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[] 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(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(",'" + clsDBConnectionObjects.Get_ClientConnectionString().Replace("InteractPayrollClient_Debug", "InteractPayrollClient") + "' AS DB_CONNECTION_STRING");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
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 ");
strQry.AppendLine(" FIVT.IDENTIFY_THRESHOLD_VALUE");
strQry.AppendLine(",FIVT.VERIFY_THRESHOLD_VALUE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD FIVT");
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 DISTINCT ");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.USER_ID");
strQry.AppendLine(",UI.FIRSTNAME");
strQry.AppendLine(",UI.SURNAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UI.USER_NO = UCA.USER_NO");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA1");
strQry.AppendLine(" ON UCA.COMPANY_NO = UCA1.COMPANY_NO");
strQry.AppendLine(" AND UCA1.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UCA1.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" WHERE UI.SYSTEM_ADMINISTRATOR_IND <> 'Y'");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" UI.USER_ID");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "User");
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" UFT.USER_NO");
strQry.AppendLine(",UFT.FINGER_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE UFT");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UFT.USER_NO = UCA.USER_NO");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA1");
strQry.AppendLine(" ON UCA.COMPANY_NO = UCA1.COMPANY_NO");
strQry.AppendLine(" AND UCA1.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UCA1.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" ORDER BY UFT.FINGER_NO");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserFingerTemplate");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}