Date printed: 03-29-2024 Last updated: 06-07-2019 To view our complete support knowledge base and most current version of this article visit support.timeips.com. |
316-264-1600 Information Sales Support |
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.
Click on the IPSDBRO Database Configuratoin tab.
Database Access
To access the database, you will need an ODBC driver compatible with your operating system.
Visit the MariaDB download page for the latest version of a compatible ODBC connector/driver for your operating system. (In most cases a MySQL ODBC connector can be used as well.) You will be connecting to a 64-bit MariaDB server. The SQL you create to process with the ODBC connector will need to be compatible with MariaDB version 10.2. Documentation on the SQL syntax is available on the MariaDB web page.
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 MariaDB or MySQL 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 LibreOffice/OpenOffice 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.
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.
Format: userDate(timestamp, usersID, format)
The arguments are as follows
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;
Format: zoneDate(timestamp, zoneID, format)
The arguments are as follows
Example: SELECT zoneDate(UNIX_TIMESTAMP(), 1, "%m/%d/%Y" ) as "Today";
Below are some example's on how to construct SQL Querys to pull data out of the system with ODBC.
-- Currently active employees and their pay type, title and pay rate. For a time other than the time of the query, put the timestamp in place of both "unix_timestamp()" entries in the query:
SELECT u.usersID,u.nameFirst,u.nameLast,ph.title,pt.name,CONCAT(ph.payRate,' ',pt.salaryBasis) AS pay
FROM users u
JOIN pay_history ph ON ph.usersID=u.usersID
JOIN pay_types pt ON ph.payTypeID = pt.payTypeID
WHERE ph.begTimestamp < unix_timestamp() AND (ph.endTimestamp IS NULL OR ph.endTimestamp > unix_timestamp());
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;