onShore TimeSheet™ Operations Manual

Greg Gallagher

Adam Di Carlo

$Revision: 1.10 $

Table of Contents
Overview of onShore TimeSheet Work Flow and Data Flow
Automated Operations (Cron Entries)
Application Logs
Suggested Backup Operations
Where to Go for Help
Database Dictionary

A handbook for system administrators who are responsible for the care and feeding of an installation of onShore TimeSheet.


Overview of onShore TimeSheet Work Flow and Data Flow

This section will give a quick overview of the onShore TimeSheet typical work and data flow. There is no set way or manner that onShore TimeSheet must be used, this is just an example in order to get the big picture:

onShore TimeSheet Work and Data Flow

  1. Client X approaches company for a project or work.

    1. Client X entered in accounting system or database external to onShore TimeSheet which assigns the client a unique Client Id number.

    2. Client X entered into onShore TimeSheet by a supervisor with administration privileges.

  2. Job(s) created (and opened) for Client X.

    1. Users in the onShore TimeSheet system can be notified of the job's creation.

    2. Optional cron script runs to export the job's information for importing into an external database system separate from onShore TimeSheet

  3. User(s) log hours to the job(s).

  4. Cron script runs to inform supervisors that jobs have hours which require their approval.

    1. Optionally, the supervisor can do a search for unapproved hours whenever they wish through the View/Edit Hours screen.

  5. Supervisor approves (and edits if necessary) hours.

  6. Cron script, if optionally installed or enabled by the onShore TimeSheet administrator, exports all hours which have been approved for importing into an external database system separate from onShore TimeSheet. That way approved hours can be directly integrated with an invoicing system in order to bill the client.

  7. Supervisor closes a job after it is completed.


Automated Operations (Cron Entries)

There are various cron scripts which the onShore TimeSheet administrator can chose to run, depending on what your business' needs are. For example, at onShore, Inc. we have a separate business database for contacts invoicing and project tracking, so there are various scripts we've included in this distribution for exporting hours entries and jobs from PostgreSQL to a flat text file suitable for importing into this other database. In addition to those scripts, there are also scripts for backing the database up, which is described in a separate section, and advising supervisors that they have unapproved hours. This section will briefly describe the various scripts and suggest crontab entries for enabling automated operations. In a default installation these scripts can be found in /usr/sbin.

As previously mentioned, on all installations there are no crontab entries created that will run by without explicitly enabling them. If you are not installing this on a Debian Linux system, the onShore TimeSheet administrator will need to create the crontab entries from scratch. Below the descriptions for the scripts you will find suggested standard crontab entries for enabling the script as an automated operation.


Application Logs

onShore TimeSheet access logs are the same as the HTTP server's logs, no separate logging is used.

The application logging directory is defined in the Makefile with the LOGDIR variable.

The export programs, timesheet-export-hours and timesheet-export-jobs, will use the EXPORT_LOG variable in the Makefile.

The format of the export log file is simple. Basically it is the scripts main action (either exporthours or exportjobs), followed by a colon, the date, another colon and a space-separated list of ids, either hour_ids or job_ids. An example follows:

exporthours:03-16-1999:11955 11990 12025 12026
exportjobs:03-16-1999:13064 13065
      


Suggested Backup Operations

By default, onShore TimeSheet will not be backed up, this is up to the onShore TimeSheet administrator to initialize. However, it it highly encouraged that backup is done at least once a week if your company's work flow and billing highly depends on onShore TimeSheet, not because onShore TimeSheet is inherently unstable but because accidents happen, database deletions, database corruption, disk corruption, etc. may necessitate a partial or full database restore.

Tape backups of the filesytem where the database keeps its data files will protect you from losing the database completely, but we highly suggest using the timesheet-dumpscript which has been included in the onShore TimeSheet distribution for backup operations. This script uses pg_dump (1) to dump the database into a script file containing the SQL commands necessary for recreating the database. Storing the database into this ASCII format allows you to easily restore the database using timesheet-load, which in turn runs the scripts that was dumped with psql (1), which will re-create or replace any lost data. Also, you could use the dumps from timesheet-dump for importing or creating a duplicate database on another server.


Where to Go for Help

If there is something in this document which doesn't help you and you need more support there are several options. Firstly, a onShore TimeSheet mailing list can be subscribed or posted to. The email address is . To subscribe to the mailing list you can either subscribe through the web, which also offers a searchable archive of previous posts to the mailing list. Alternatively, to join the list send an email to with a body which says SUBSCRIBE followed by an email address.

Secondly, the home page for onShore TimeSheet, http://www.onshore-timesheet.org will offer all the documentation for onShore TimeSheet online, as well as links for development and consulting services we offer for supporting onShore TimeSheet. This site will also be the main distribution point for releasing new distributions of the onShore TimeSheet source as it becomes available.


Database Dictionary

There are some tables in the database. They are listed below:

Table 1. category -- list of possible categories of work for hours and jobs

Field NameData TypeField Definition
category_idtextthe id for the category
creator_idtextthe id for who created the category

Table 2. client -- list of all clients with open or closed jobs

Field NameData TypeField Definition
default_bill_ratemoneythe default billing rate for the client
client_idintclient id (generated externally from onShore TimeSheet)
default_approvedintif automatically approved, 0 = no, 1 = yes

Table 3. hours -- list of all logged, approved and deleted hours in onShore TimeSheet

Field NameData TypeField Definition
downloadedtextdownloaded because approved, 0 = no, 1 = yes
approval_datedatethe date the hours were approved
date_entereddatedate the user logged the hours, generated by onShore TimeSheet as the time the entry is made
time_intexttime the hours entry started
time_outtexttime the hours entry stopped
intimetextdate the user attributes the hours worked
expense_amountmoney 
fkapprover_idtextthe id of the supervisor who approved the hours
parkingrealparking fees accrued for this hours entry
commenttextcomments about the hours worked
hours_descriptiontextthe description of the hours worked
deltextis this a deleted hours entry, 0 = no, 1 = yes
billableintshould this be billable to the client, 0 = no, 1 = yes
hours_idintid of the hours being logged, generated internally by onShore TimeSheet
categorytextcategory for this work (from set list)

Table 4. job -- list of all possible jobs

Field NameData TypeField Definition
downloadedtextif the job has been downloaded or not, 0 = no, 1 = yes
default_approvedintany hours logged to the job are automatically approved
flag_hrsintnumber of hours logged to this job for flagging a warning to the supervisor
job_idintid of the job, generated internally by onShore TimeSheet
bill_ratemoneythe billing rate for this job
current_hours_estimatereal 
open_datedatedate the job was opened
estimated_hoursrealestimated number of hours for the completion of this job
openintjob is open or not, 0 = no, 1 = yes
close_datedatedate the job was closed
fknotify_idtextlist of user id's, as relate to the personnel_ids in the personnel table, to notify of the job's creation
current_estimate_datedatethe date the estimate of hours was made on the job
job_descriptiontextthe description of the job
categorytextcategory for this job (from set list)
fksupervisor_idtextthe id of the supervisor responsible for this job, as relates to the id in the personnel table

Table 5. notification -- table of who should be notified for actions on a job (not implemented)

Field NameData TypeField Definition
fkjob_idintthe job id number (relates to job_id in job table)
fkpersonnel_idtextthe personnel id number (relates to personnel_id in personnel table)

Table 6. personnel -- list of all personnel, employees and supervisors who use timesheet

Field NameData TypeField Definition
emailtexte-mail address
first_nametextuser's first name
personnel_idtextthe logon id for the user
last_nametextuser's last name
super_usertextif they have supervisor status or not, 0 = no, 1 = yes
middle_nametextuser's middle initial