A handbook for system administrators who are responsible for the care and feeding of an installation of onShore TimeSheet.
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
Client X approaches company for a project or work.
Client X entered in accounting system or database external to onShore TimeSheet which assigns the client a unique Client Id number.
Client X entered into onShore TimeSheet by a supervisor with administration privileges.
Job(s) created (and opened) for Client X.
Users in the onShore TimeSheet system can be notified of the job's creation.
Optional cron script runs to export the job's information for importing into an external database system separate from onShore TimeSheet
User(s) log hours to the job(s).
Cron script runs to inform supervisors that jobs have hours which require their approval.
Optionally, the supervisor can do a search for unapproved hours whenever they wish through the View/Edit Hours screen.
Supervisor approves (and edits if necessary) hours.
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.
Supervisor closes a job after it is completed.
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.
timesheet-daily-report:
Reports to supervisors through e-mail. The supervisor daily report includes a summary of unapproved hours, and a summary of open jobs managed by the supervisor.
This is an example crontab entry, which will send off the reports at 1:00am every night:
0 1 * * * /usr/sbin/timesheet-daily-report
timesheet-export-hours:
Exports the hours which have been marked approved in the database, and marks the hours as "download" (and therefore not editable so the records cannot get out of sync with the other database).
This crontab entry will export hours data every weekday night at 2:00am:
0 2 * * * /usr/sbin/timesheet-export-hours
timesheet-export-jobs:
To be written.
timesheet-export-hours:
Exports any jobs which have been created since the last time was run, and marks them as "downloaded" (and thus not needing to be downloaded again), for importing into another database. Jobs are editable after being marked as "downloaded".
This crontab entry will export the new jobs data every weekday night at 1:30am:
v30 1 * * 1-5 /usr/sbin/timesheet-export-jobs
timesheet-dump:
Exports tables in the database using PostgreSQL's pg_dump (1) command for backup purposes. The text files produced contain the necessary SQL commands for re-creating the database from scratch and importing the information in the tables.
This crontab entry will export the full database every Friday evening at midnight for backup purposes:
0 0 * * 5 /usr/sbin/timesheet-dump
timesheet-load:
Imports the tables dumped from timesheet-dump into the PostgreSQL database for restoration purposes.
There is no example crontab entry, as this shouldn't be an automated operation.
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
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.
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 <timesheet@onShore.com>. 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 <timesheet-request@onShore.com> 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.
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 Name | Data Type | Field Definition |
---|---|---|
category_id | text | the id for the category |
creator_id | text | the id for who created the category |
Table 2. client -- list of all clients with open or closed jobs
Field Name | Data Type | Field Definition |
---|---|---|
default_bill_rate | money | the default billing rate for the client |
client_id | int | client id (generated externally from onShore TimeSheet) |
default_approved | int | if automatically approved, 0 = no, 1 = yes |
Table 3. hours -- list of all logged, approved and deleted hours in onShore TimeSheet
Field Name | Data Type | Field Definition |
---|---|---|
downloaded | text | downloaded because approved, 0 = no, 1 = yes |
approval_date | date | the date the hours were approved |
date_entered | date | date the user logged the hours, generated by onShore TimeSheet as the time the entry is made |
time_in | text | time the hours entry started |
time_out | text | time the hours entry stopped |
intime | text | date the user attributes the hours worked |
expense_amount | money | |
fkapprover_id | text | the id of the supervisor who approved the hours |
parking | real | parking fees accrued for this hours entry |
comment | text | comments about the hours worked |
hours_description | text | the description of the hours worked |
del | text | is this a deleted hours entry, 0 = no, 1 = yes |
billable | int | should this be billable to the client, 0 = no, 1 = yes |
hours_id | int | id of the hours being logged, generated internally by onShore TimeSheet |
category | text | category for this work (from set list) |
Table 4. job -- list of all possible jobs
Field Name | Data Type | Field Definition |
---|---|---|
downloaded | text | if the job has been downloaded or not, 0 = no, 1 = yes |
default_approved | int | any hours logged to the job are automatically approved |
flag_hrs | int | number of hours logged to this job for flagging a warning to the supervisor |
job_id | int | id of the job, generated internally by onShore TimeSheet |
bill_rate | money | the billing rate for this job |
current_hours_estimate | real | |
open_date | date | date the job was opened |
estimated_hours | real | estimated number of hours for the completion of this job |
open | int | job is open or not, 0 = no, 1 = yes |
close_date | date | date the job was closed |
fknotify_id | text | list of user id's, as relate to the personnel_ids in the personnel table, to notify of the job's creation |
current_estimate_date | date | the date the estimate of hours was made on the job |
job_description | text | the description of the job |
category | text | category for this job (from set list) |
fksupervisor_id | text | the 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 Name | Data Type | Field Definition |
---|---|---|
fkjob_id | int | the job id number (relates to job_id in job table) |
fkpersonnel_id | text | the personnel id number (relates to personnel_id in personnel table) |
Table 6. personnel -- list of all personnel, employees and supervisors who use timesheet
Field Name | Data Type | Field Definition |
---|---|---|
text | e-mail address | |
first_name | text | user's first name |
personnel_id | text | the logon id for the user |
last_name | text | user's last name |
super_user | text | if they have supervisor status or not, 0 = no, 1 = yes |
middle_name | text | user's middle initial |