Date printed: 11-20-2017   Last updated: 06-14-2012

To view our complete support knowledge base and most current version of this article visit support.timeips.com.
316-264-1600
Information
Sales
Support

Applies to versions: 1.10, 1.11, 2.0, 2.1, 2.2, 2.3, 2.4, 2.5



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.

 



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



Master Configuration

Click on the IPSDBRO Database Configuratoin tab.

 

  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

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.

Translating Timestamps

Times in TimeIPS are usually stored as Unix Timestamps. Unix timestamps are stored as the number of seconds that have elapsed since the epoch which was midnight Coordinated Universal Time January 1st 1970. This format provides a very accurate and unambiguous record of exactly when an event occurred without the ambiguities caused by time zones or Daylight Saving. It also makes it easy to calculate the duration of an event. For example in the eventLog table the adjusted in and out timestamps can be subtracted to yield the full duration of the event in seconds (adjTimeOut - adjTimeIn).

Timestamps can be somewhat difficult to translate accurately, especially if an employee in question is not following the specific time zone and Daylight Saving rules used by the host TimeIPS server. To that end we have provided our ODBC customers with two functions which allow for easy translation of timestamps.

userDate

Format: userDate(timestamp, usersID, format)

The arguments are as follows

  1. The timestamp you want to convert
  2. The usersID of the user to use for time zone and DST lookups
  3. A format string. Use null for the default format "%m/%d/%Y %h:%i:%s %p" which will translate to something like "04/08/2012 07:27:04 AM". You can construct your own string using the guidelines found here.

Example: SELECT usersID, eventLogID, userDate(adjTimeIn, usersID, null) as "Punch In" FROM eventLog LIMIT 10;

Example: SELECT usersID, eventLogID, userDate(adjTimeIn, usersID, "%W %M %e %Y") as "Punch In" FROM eventLog LIMIT 10;

zoneDate

Format: zoneDate(timestamp, zoneID, format)

The arguments are as follows

  1. The timestamp you want to convert
  2. The zoneID of the timezone to use for the conversion. This can be found in timecard.zones
  3. A format string. This follows the same logic as userDate

Example: SELECT zoneDate(UNIX_TIMESTAMP(), 1, "%m/%d/%Y" ) as "Today";

 

Example Querys

Below are some example's on how to construct SQL Querys to pull data out of the system with ODBC.

The following query's are for pulling Benefit data out of the database, as benefit time all calculated on the fly, we can't just pull the data out of the table.

-- Used In Range:

SELECT SUM(adjustment) / 3600 as used,usersID,typeID
FROM benefits_logWHERE adjustment < 0
        AND accrualLogID IS NULL
        AND deleted IS NULL
        AND adjusted_on BETWEEN 1363582800 and 1364274000
        AND usersID IN (1)
        AND typeID IN (3)
GROUP BY usersID, typeID;


-- Earned In Range:

SELECT SUM(adjustment) / 3600 as earned,usersID,typeID
FROM benefits_log
WHERE deleted IS NULL
        AND (accrualLogID IS NOT NULL OR (adjusterID IS NOT NULL AND adjustment>0))
        AND adjusted_on BETWEEN 1363582800 AND 1364274000
        AND usersID IN (1)
        AND typeID IN (3)
GROUP BY usersID,typeID;


-- Net In Range:

SELECT u.usersID, tempBenefitLog.typeID, SUM(adjustment) / 3600 as net
FROM users u LEFT JOIN (
    SELECT usersID, typeID, adjustment, adjusted_on
    FROM benefits_log log
    WHERE adjusted_on <= 1364274000
        AND usersID IN (1)
        AND typeID IN (3)
        AND deleted IS NULL
        AND adjustment IS NOT NULL
        AND adjusted_on >= (
            SELECT IFNULL(MAX(occurs),0)
            FROM benefit_reset r
            WHERE r.typeID = log.typeID
                AND r.usersID = log.usersID
                AND r.occurs <= 1364274000
        )

    UNION ALL

    SELECT usersID, typeID, amount as adjustment, occurs as adjusted_on
    FROM benefit_reset r
    WHERE resetID NOT IN (
            SELECT r1.resetID
            FROM benefit_reset r1, benefit_reset r2
            WHERE r1.typeID = r2.typeID
                AND r1.usersID = r2.usersID
                AND r1.occurs <= 1364274000
                AND r2.occurs <= 1364274000
                AND r1.occurs < r2.occurs
                AND r1.typeID IN (3)
                AND r1.usersID IN (1)
        )        
AND r.usersID IN (1) AND r.typeID IN (3) AND r.occurs <= 1364274000
) as tempBenefitLog ON u.usersID = tempBenefitLog.usersID
WHERE u.usersID IN (1)
GROUP BY tempBenefitLog.usersID, tempBenefitLog.typeID;


-- Available At Start

SELECT u.usersID, tempBenefitLog.typeID, SUM(adjustment) / 3600 as net
FROM users u LEFT JOIN (
    SELECT usersID, typeID, adjustment, adjusted_on
    FROM benefits_log log
    WHERE adjusted_on <= 1363582800
        AND usersID IN (1)
        AND typeID IN (3)
        AND deleted IS NULL
        AND adjustment IS NOT NULL
        AND adjusted_on >= (
            SELECT IFNULL(MAX(occurs),0)
            FROM benefit_reset r
            WHERE r.typeID = log.typeID
                AND r.usersID = log.usersID
                AND r.occurs <= 1363582800
        )

    UNION ALL

    SELECT usersID, typeID, amount as adjustment, occurs as adjusted_on
    FROM benefit_reset r    WHERE resetID NOT IN (
            SELECT r1.resetID
            FROM benefit_reset r1, benefit_reset r2
            WHERE r1.typeID = r2.typeID
                AND r1.usersID = r2.usersID
                AND r1.occurs <= 1363582800
                AND r2.occurs <= 1363582800
                AND r1.occurs < r2.occurs
                AND r1.typeID IN (3)
                AND r1.usersID IN (1)
        ) AND r.usersID IN (1) AND r.typeID IN (3) AND r.occurs <= 1363582800
) as tempBenefitLog ON u.usersID = tempBenefitLog.usersID
WHERE u.usersID IN (1)
GROUP BY tempBenefitLog.usersID, tempBenefitLog.typeID;



©2004-2017 TimeIPS, Inc. All Rights Reserved - TimeIPS and the TimeIPS logo are registered trademarks of TimeIPS, Inc.
"Intelligent Personnel System" "Run your Business. We'll watch the clock." are trademarks of TimeIPS, Inc.
TimeIPS is protected by one or more patents. Patent No. US 7,114.684 B2.