Applies to versions: 1.8, 1.9

ODBC Access

The TimeIPS® Database Read-Only Access Module allows setting a password to enable read-only access directly to the TimeIPS database. This allows users with database experience to write queries for external applications including custom reporting.

 

Security Level Required: Full Access or System Administrator

Using the left pane Main Menu, click on: Administration » System » System Settings

 

 

 

1. Type in a password in the "Limited Remote Database Password:" field
   
   
2. Retype the password in the "Limited Remote Database Password (again):" field
   
   
3. Click on the Update Database Configuration button

Database Access

To access the database, you will need an ODBC driver compatible with your operating system.

Visit the MySQL download page for compatible ODBC drivers for many operating systems.

After the ODBC driver is installed, configure it to communicate with your TimeIPS system's IP address and a database called "timecard." The location of the configuration varies by operating system.

As an example, in Windows XP, access ODBC settings by going to Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC). Click "Add" to add a new data source and select the MySQL 3.51 driver. Pick a name, like TimeIPS for the connection. Specify the IP address of your TimeIPS System in the Server field. The database name will be '"timecard" and you will use the username "readonly" and the password you configured.

After the ODBC connection is configured, you will need an application that can use ODBC to process queries and/or generate reports. There are many tools available. For example, the free office suite OpenOffice.org 2.0 includes a database component that can be used.

There are many database tables available in TimeIPS. The following list covers some of the main tables with the purpose of the table and notes on using it. The role of tables not listed here are beyond the scope of this document.

Table - Purpose

• users - List of TimeIPS users (employees). The usersID field is used in many other tables to identify the employee. There are over 100 data fields in the users table. The essential fields are:
  
  • usersID - A unique internal database identifier for the employee
  • isActive - 0 or 1, to indicate if the employee is active (employed)
  • empNum - The employee alternate number
  • HID - The employee's badge number
  • nameFirst - The employee's name
  • namePref - The employee's preferred name (nickname)
  • nameMid - The employee's middle name
  • nameLast - The employee's last name
  • mgrID - The usersID of the employee's direct manager
  • username - The username the employee uses to log into TimeIPS
  • password - A hashed password for logging into TimeIPS
  • title - The employee's title
• eventLog - Raw and adjusted records of the employee clocks. The usersID field relates to the users table. You will normally use the adjTimeIn and adjTimeOut fields for reporting or calculations. The timeIn and timeOut fields are the actual time the clock was created (i.e. the time the event was created, which could be hours or days before or after the paid time). The realTimeIn and realTimeOut are the time the employee actually clocked (before rounding, snapping, or adjustments) and are used to compare the net effect of adjustments vs. actual. All times are stored as Unix Timestamps (i.e. seconds since midnight, Jan 1st, 1970 in GMT). You will need to convert and process these in your reports. There are over 30 fields in the eventLog table. The essential fields are:
  
  • eventLogID - A unique internal database identifier for the punch
  • usersID - A unique internal database identifier for the employee
  • timeIn - The time the punch-in was created
  • timeOut - The time the punch-out was created
  • adjTimeIn - The paid punch-in time after all adjustments (rounding, snapping, editing)
  • adjTimeOut - The paid punch-out time after all adjustments (rounding, snapping, editing)
  • realTimeIn - The original/intended punch-in time without any all adjustments (rounding, snapping, editing)
  • realTimeOut - The original/intended punch-out time without any adjustments (rounding, snapping, editing)
  • jobID - A unique internal database identifier for the job being worked
  • clockNote - The note left with the clock in
  • clockNoteOut - The note left with the clock out
  • clientIn - A unique internal database identifier for the client that created the punch in
  • clientOut - A unique internal database identifier for the client that created the punch in
  • clientAdjIn - A unique internal database identifier for the client that created the punch in after all adjustments and edits
  • clientAdjOut - A unique internal database identifier for the client that created the punch in after all adjustments and edits
• jobs - TimeIPS Jobs. The "code" field holds the code that employees enter to clock into the job.
  • jobID - A unique internal database identifier for the job
  • isActive - 0 or 1, to indicate if the job is active (can be used)
  • lcdName - The small display name (short name) that appears on the clock screen
  • code - The job code, used to clock into the job
  • name - The name of the job
  • ctrlNum - A control number, generally used for billing
  • billingRate - A billing rate for this job to customers
  • catID - A unique internal database identifier for the category this job belongs to
  • custID - A unique internal database identifier for the customer this job is for
  • type - The kind of job, "validated" or "flexible" (almost always validated)
  • glNum - The general ledger code for the job
  • description - The description of the job
  • mgrID - A unique internal database identifier for the usersID of the manager of this job
• zones - Time Zones for employees and clients. When creating reports, use the "zoneid" field in the users table to identify the time zone use when adjusting clock timestamps.
• dst - Timestamps for the beginning and end of DST (daylight savings time) for each zone that uses DST. If the time you are looking at is for an employee in a zone that follows DST, you will need to check to see if the time event falls in a DST date range and apply the corresponding "shift" in seconds.
• depts - Departments
• customers - Customers for Jobs
• payroll_events - Useful for reporting. The payroll_events are processed eventLog events, plus benefits events that are broken into segments for payroll and reporting. This is the table used to produce sign-off reports and payroll exports because it includes the type of pay that is due, such as overtime or doubletime, based on the employees work-weeks and the payroll periods. Please note that this table is NOT updated in real time. It is updated on demand when reports or payroll exports are run.

Please note that the tables sometimes change between versions and the way the data in the tables is used also sometimes changes. If you have critical reports pulling from these tables, we suggest verifying your reports after each TimeIPS upgrade.