[Tsheetx-developers] New version nearing check in

[Scott M. <Sco...@pr...>]

Hi everyone,

I've been using the original Timesheet system at our company for about 4
years now, and since the original open source project had died, we began
improving our system basically since we started using it.  I then
learned about the resurrection under the "new generation" moniker, and
have been conversing with Tommo (Dave) off and on over the past several
months.  During that time, when I've had time, I've been working hard on
improving the system, incorporating many improvements we've made, fixing
the DST inconsistencies, and making other improvements as I've seen fit.

I'm currently working on adding a couple of the reports we've created to
the new system, and once I get that done, I'll need to fix up the
installation and updating routines.  I'll then be ready to check
everything in.  After discussing with Tommo, the version number will
change to 1.5.0.

Here's a copy of the log I've kept of what I've been doing:

		            Scott Miller's Timesheet NG Release Notes
April, 2010

		A new navigational calendar is included on all the time
entry forms, and the original "prev / next" buttons have been removed.
The navigational calendars are implemented using a set of include files.

		I've consolidated the clock on and off form on the
daily, popup and stopwatch forms by placing the "core" of clock on/off
form into a set of include files (a single include file didn't quite cut
it).

		On the daily form, I've placed both the navigation
calendar and the clock on/off forms next to each other above the
task/time display form.

		Previous versions of this system did not calculate the
ending timestamps correctly.  If we work from midnight to midnight,
we've worked for 24 hours (disregarding DST issues), not 23 hours, 59
minutes and 59 seconds. An ending timestamp of midnight is now perfectly
acceptable and is handled correctly.

		Several functions were modified or added to the
common.inc file, and a few were removed: 
*	get_month_times() has been renamed and broadened in scope to
become get_time_records()
*	get_last_day() function was modified to use "date('t',
strtotime("$year-$month-1"));"
*	get_dst_adjustment() added to help deal with DST adjustments
when needed
*	get_duration() added to calculate number of minutes between
start and end stamps
*	get_end_date_time() added to calculate the end stamp given start
stamp and duration in minutes
*	fix_entry_duration() added to fix db items in which duration
column is NULL
*	fix_entry_endstamp() added to fix db items in which calculated
endstamp doesn't match endstamp in the db
*	formatMinutes() added to create better human readable format for
durations in minutes similar to formatSeconds
*	get_trans_info() modified to get additional DB fields (duration,
trans_num), and renamed start/end_time to start/end_stamp
*	year_button() used to only count up from the context date, which
made it difficult to go back, it now generates a pull down with the
previous 5 years and the next 5 years.
*	setReportDate() was removed, as it was no longer needed
*	printPrevNext() was removed, as it was no longer needed
*	split_data_into_discrete_days() added, see below for further
discussion
*	__put_data_in_array() helper for split_data_into_discrete_days()
*	fixStartEndDuration() helps to ensure time/date data from DB is
as accurate as we can, and keeps duration and end_time columns in a
consistent state.  See Daylight Savings time discussion below for
further information.
		
		A new subdirectory was added for the Navigational
calendar items called 'navigational'. Within that subdirectory another
file called 'common.inc' was created, functions moved to or otherwise
included in this file are:
*	getNextMonth() added to calculate the next month's context date,
keeping the resulting context day of the month as close to the original
context day of the month as it can.
*	getPrevNextMonth() added to calculate the previous and next
month context dates keeping the resulting context day of the month as
close to the current context day of the month as it can.
*	getPrevNextYear() does the same thing as getPrevNextMonth() only
for next/previous years
*	draw_month_year_navigation() prints out a single monthly/yearly
navigational table
*	__print_month_name() helper for above function, prints out a 3
letter month abbreviation link with all needed "$post" variables.
*	draw_month_year_navigation_with_end_dates() prints out a single
monthly/yearly navigational table for report tables that allow user to
select both a start_time and an end_time.
*	__print_month_name_with_end_date () helper for above function,
prints out a 3 letter month abbreviation link with all needed "$post"
variables.
		
		All data entry forms have been modified to use the
get_time_records() function instead of each having their own times table
query.

		The file calendar.php has been renamed to monthly.php;
the menu item "Calendar" has been renamed to "View Month", and the
aclCalendar database item has been renamed to aclMonthly, and the config
database column startPage has been modified from an enum with 'calendar'
in the definition to an enum with 'monthly' in the definition. All files
referencing any of these items were modified to reference the new
'monthly' names.

		Daylight Savings time (DST) threw a big wrench into the
way the timesheet application did most date/time calculations. If users
enter their time using dates/times (daily/pop-up screens), DST
adjustments have always been made automatically. If users enter time
using the simple form, however, DST adjustments were not performed when
storing the data, but upon retrieval, DST adjustments were made, and
thus made incorrectly. Two things have been done to fix this problem:

			1) The database schema has changed to include a
"duration" column in the times table. All forms now populate the
duration column, but if it is null, it is calculated, and written to the
database. If the duration column is a positive integer, the end_time
column information is ignored, and is calculated using the start_time
and duration. If the calculated end_time does not agree with database
end_time, the database end_time is set to the calculated time.

			2) In the simple.php form, if the duration is
missing, and the start date begins at midnight, and the start_time and
end_time are not in the same DST state (ie. one is summer time, the
other standard time), then it is assumed the entry was created by a
previous instance of simple.php, and the incorrect automatic DST
adjustment is reversed. In the new version, the duration column
eliminates the need to perform the DST calculations and adjustments.

			Unfortunately it is inherently ambiguous to use
the datetime SQL datatype to store dates unless extreme care is taken to
cast date information into and out of the UTC timezone when writing to
and reading from the database. The timesheet system has never cast the
time info at all, and without these casts the datetime datatype cannot
unambiguously represent, for example, the US fall DST adjustment hour
between 1am and 2am (where time goes from 1:59:59am back to 1:00:00am
the first time through, and then goes to 2:00:00 the second time
through). I'm sure there are other examples of this problem in other
zones that observe some form of DST. I plan to further move the
Timesheet NG away from this problem, by replacing the datetime datatype
with a 64 bit integer which will store unix timestamps, but I've decided
I cannot complete that work in this iteration.

			For more info about the SQL datetime problems,
see: http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php
			and read the comments by Miles Nordin and Joakim
Nygard.

			I also think the end_time column could be
deprecated by automatically taking tasks that extend past a day boundary
(ie. midnight), and splitting that task up into separate entries for
each day that is involved. This has also not been done for this
iteration.
		
		I've attempted to reduce the number of times that the
date functions were called. For instance, there used to be many cases
where date was called repeatedly to get first the year, then the month,
and then the day, I've replaced many of those instances with a single
call to getdate, and then pulled the 3 variables out of the resulting
array.

		All the reports have been modified to make use of the
duration field to override the end_time, many have been modified to
allow different sorting options by clicking on some of the table
headers, and additional reports that we have found useful have been
added to the list of available reports. All reports now also make use of
the get_time_records() function to read user time entries.

		A "select client" drop down was added to the
'report_specific_user.php' script, thereby eliminating the need for the
report_specific_client_user.php report file.

		Report files have been renamed to remove the "specific"
portion of their names.  Ie. report_specific_user.php =>
report_user.php, report_specific_project.php => report_project.php, etc.
Also, the recently added "summary" report,
report_summary_client_user.php, was renamed to
report_grid_client_user.php.  Most report descriptions in reports.php
have been modified to read slightly more clearly.

		In order to attempt to keep the current context accurate
where ever possible, the links in the navigational calendars include a
new global variable "$post". Each form is responsible for placing all
context variables needed into $post to keep the rest of the context
accurate when changing context dates. For example, if we're on a report
page, and are sorting by date, $post needs to have the string
"&orderby=date" included, or navigating to a different date would revert
back to the default sort order.

		Changing which user form was being viewed (simple,
daily, weekly, monthly) used to reset the views to use the actual
current date; this has also been changed so that $post is used for those
links as well so that changing which form is being viewed now preserves
the current context date being viewed.

		I added a dollar sign ($) icon for the rate menu items.

		The function(s) to create the Previous / Next navigation
links within reports have been eliminated to use the new navigational
calendars. This has helped eliminate the need for the "setReportDate"
function which tended to modify the "current context" date, which
irritated me.

		Since most reports usually want task times broken into
discrete daily time periods, a set of functions were created to do that
work for us. The main function is called split_data_into_discrete_days()
takes one database result at a time, which has a task with a start time
and duration, and could span date boundaries (ie. start on one day and
end on subsequent day) and splits that task into discrete days, and adds
those entries into a new array.  To get the data ordered as we want, the
calling script must have a function called make_index() defined to
create the array keys with which the data is added to the new array.
The task of copying the needed data to the new array is given to yet
another function called __put_data_in_array()

-Scott L. Miller