/// <summary>
/// checks that there are no duplicate time ID's in the time table. This can happen if the database gets corrupted.
/// </summary>
private bool checkNoDuplicateTimeID(application application)
{
bool bReturn = true;
DataTable tables;
string sTableName = string.Empty;
string sFieldName = string.Empty;
this.clearLog();
tables = this.getTables();
this.clearLog();
try
{
DbDataReader dataReader;
dataReader = this.returnDataReader("select count(tblTimes.lngID) as count_id, lngID from tblTimes group by tblTimes.lngID having count(tblTimes.lngID)>1;");
if (dataReader.HasRows)
{
this.OnLogEvent(new LogEventArgs("access.checkNoDuplicateTimeID duplicate primary keys found in tblTimes"));
bReturn = false;
}
dataReader.Close();
}
catch (Exception e)
{
bReturn = false;
this.writeLog("access.checkNoDuplicateTimeID " + e.ToString());
}
return(bReturn);
}
/// <summary>
/// runs through each of the database validity checks
/// </summary>
public bool checkValidDatabase(application application)
{
bool bReturn = true;
try
{
//this.OnLogEvent(new LogEventArgs("Checking that Time Clock MTS Database Can be Opened"));
this.openConn();
}
catch (Exception e)
{
if (e.GetType().Name == typeof(System.Data.OleDb.OleDbException).Name)
{
this.OnLogEvent(new LogEventArgs("Cannot open " + this.DatabasePath + ". The file might be corrupt or not even a valid database."));
}
else
{
this.OnLogEvent(new LogEventArgs("An unhandled exception has occurred trying to open " + this.DatabasePath));
this.OnLogEvent(new LogEventArgs(e.ToString()));
}
bReturn = false;
}
if (bReturn)
{
this.OnLogEvent(new LogEventArgs("Checking that Primary Keys Exist"));
bReturn = checkPrimaryKeysExist(application);
}
if (bReturn)
{
this.OnLogEvent(new LogEventArgs("Primary Keys Exist!"));
this.OnLogEvent(new LogEventArgs("Checking for duplicate time ID's"));
bReturn = checkNoDuplicateTimeID(application);
}
if (bReturn)
{
this.OnLogEvent(new LogEventArgs("No duplicate time ID's!"));
this.OnLogEvent(new LogEventArgs("Checking first time punches for each employee"));
bReturn = checkFirstPunchType(application);
}
if (bReturn)
{
this.OnLogEvent(new LogEventArgs("First time punches valid for each employee!"));
this.OnLogEvent(new LogEventArgs("Checking that time punch structure is valid"));
bReturn = checkValidTimeStructure(application);
}
if (bReturn)
{
this.OnLogEvent(new LogEventArgs("The time punch structure is valid!"));
}
this.closeConn();
return(bReturn);
}
/// <summary>
/// checks each employee's time records to make sure they are in the correct in/out sequence
/// </summary>
private bool checkValidTimeStructure(application application)
{
bool bReturn = true, bFirstRowForEmployees = false, bLastEvent = false;
Int32 iLastEmployeeID = 0;
List <string> sBadStructureMessages = new List <string>();
string sSql = "select tblTimes.lngID, tblTimes.blnEventType, tblTimes.lngEmployeeID, tblEmployees.strFullName from tblTimes inner join tblEmployees on tblTimes.lngEmployeeID=tblEmployees.lngID where tblEmployees.blnDeleted=FALSE order by tblEmployees.lngID ASC, tblTimes.datEvent DESC";
DbDataReader dataReader;
try
{
dataReader = this.returnDataReader(sSql);
if (dataReader.HasRows)
{
while (dataReader.Read())
{
if (iLastEmployeeID != (Int32)dataReader["lngEmployeeID"])
{
this.OnLogEvent(new LogEventArgs("Checking time structure for employee " + dataReader["strFullName"].ToString() + "(" + dataReader["lngEmployeeID"].ToString() + ")"));
bFirstRowForEmployees = true;
}
else
{
bFirstRowForEmployees = false;
}
if (!bFirstRowForEmployees)
{
if (bLastEvent == (bool)dataReader["blnEventType"])
{
bReturn = false;
sBadStructureMessages.Add("Bad time structure at TimeID " + dataReader["lngID"].ToString() + " for employee " + dataReader["strFullName"].ToString() + "(" + dataReader["lngEmployeeID"].ToString() + ")");
}
}
bLastEvent = (bool)dataReader["blnEventType"];
iLastEmployeeID = (Int32)dataReader["lngEmployeeID"];
bFirstRowForEmployees = false;
}
}
//write all bad structure messages to end of log
string[] sMessage = sBadStructureMessages.ToArray();
if (sMessage.Length > 0)
{
for (Int32 i = 0; i < sMessage.Length; i++)
{
this.OnLogEvent(new LogEventArgs(sMessage[i]));
}
}
}
catch (Exception e)
{
bReturn = false;
this.writeLog("access.checkValidTimeStructure " + e.ToString());
}
return(bReturn);
}
public frmMain()
{
InitializeComponent();
application = new application();
this.writeInitialEntries();
this.Text = this.Text + " (Version: " + Application.ProductVersion + ")";
this.txtDatabasePath.Text = application.DatabasePath;
this.logMessage("DatabasePath::" + this.txtDatabasePath.Text);
if (application.ErrorMessage.Length > 0)
{
this.logMessage("DatabasePath::" + this.txtDatabasePath.Text);
}
this.txtAccount.Text = application.AccountName;
this.txtAccountAdminLogin.Text = application.AccountLogin;
this.txtDataUploadToken.Text = string.Empty;
if (application.ExportPath.Length == 0)
{
if (application.bDirectoryExists(System.IO.Path.GetDirectoryName(this.txtDatabasePath.Text)))
{
this.txtExportFileLocation.Text = System.IO.Path.GetDirectoryName(this.txtDatabasePath.Text) + "\\" + "timeclock-import.json";
}
else
{
this.txtExportFileLocation.Text = string.Empty;
}
}
else
{
this.txtExportFileLocation.Text = application.ExportPath;
}
this.logMessage("AccountName::" + this.txtAccount.Text);
this.logMessage("AccountLogin::" + this.txtAccountAdminLogin.Text);
this.logMessage("ExportPath::" + this.txtExportFileLocation.Text);
this.FormClosing += new FormClosingEventHandler(frmMain_FormClosing);
this.btnBrowse.Click += new EventHandler(btnBrowse_Click);
this.btnBrowseExportLocation.Click += new EventHandler(btnBrowseExportLocation_Click);
this.btnClearLog.Click += new EventHandler(btnClearLog_Click);
this.btnCopyLog.Click += new EventHandler(btnCopyLog_Click);
this.access = new access(this.txtDatabasePath.Text.Trim());
this.access.LogEvent += new LogEventHandler(logEvent);
#if DEBUG
this.chkNoFormValidation.Visible = true;
this.chkNoValidityChecks.Visible = true;
#else
this.chkNoFormValidation.Visible = false;
this.chkNoValidityChecks.Visible = false;
#endif
}
/// <summary>
/// checks that the first punch for each employee is an IN
/// </summary>
private bool checkFirstPunchType(application application)
{
bool bReturn = true;
string sSqlEmployees = "select lngID, strFullName from tblEmployees where blnDeleted=FALSE order by strFullName ASC;";
string sSQLTimePunch = string.Empty;
DbDataReader dataReaderEmployees, dataReaderTimePunch;
OleDbConnection connLocal;
connLocal = this.createDatabaseConnection();
try
{
dataReaderEmployees = this.returnDataReader(sSqlEmployees, null, connLocal);
if (dataReaderEmployees.HasRows)
{
while (dataReaderEmployees.Read())
{
Console.WriteLine("Loading times for " + dataReaderEmployees["strFullName"].ToString() + "(" + dataReaderEmployees["lngID"] + ")");
dataReaderTimePunch = this.returnDataReader("select top 1 datEvent, blnEventType, lngID from tblTimes where lngEmployeeID=" + (Int32)dataReaderEmployees["lngID"] + " order by datEvent ASC", null, connLocal);
if (dataReaderTimePunch.HasRows)
{
while (dataReaderTimePunch.Read())
{
if ((bool)dataReaderTimePunch["blnEventType"] == false)
{
bReturn = false;
this.OnLogEvent(new LogEventArgs("Bad first time punch for " + dataReaderEmployees["strFullName"].ToString() + "(Employee ID:" + dataReaderEmployees["lngID"].ToString() + " Time ID: " + dataReaderTimePunch["lngID"].ToString() + ")"));
}
}
}
}
}
}
catch (Exception e)
{
bReturn = false;
this.writeLog("access.checkFirstPunchType " + e.ToString());
}
return(bReturn);
}
/// <summary>
/// checks that all the tables have a primary key. Ignores system tables.
/// </summary>
private bool checkPrimaryKeysExist(application application)
{
bool bReturn = true;
DataTable tables;
string sTableName = string.Empty;
string sFieldName = string.Empty;
this.clearLog();
tables = this.getTables();
this.clearLog();
Int32 iTableNameColumn = tables.Columns.IndexOf("TABLE_NAME");
foreach (DataRow row in tables.Rows)
{
sTableName = row[iTableNameColumn].ToString();
if (sTableName.Substring(0, 3).ToLower() == "tbl")
{
DataTable fields = this.getTableFields(sTableName);
Int32 iFieldNameColumn = fields.Columns.IndexOf("COLUMN_NAME");
Int32 iDataTypeColumn = fields.Columns.IndexOf("DATA_TYPE");
Int32 iMaxLengthColumn = fields.Columns.IndexOf("CHARACTER_MAXIMUM_LENGTH");
this.writeLog("access.checkValidDatabase :: checking for PrimaryKey in " + sTableName);
if (this.getAccessPrimaryKeyName(sTableName).Length == 0)
{
bReturn = false;
this.writeLog("access.checkValidDatabase:: Table " + sTableName + " has no primary key");
break;
}
}
}
return(bReturn);
}
