Page 1 of 1

Working with times...

Posted: Thu Jan 20, 2005 12:10 am
by Czar
I have an employee database, which logs when employee enters work and when he/she leaves work. Times are in YYYY-MM-DD HH:MM:SS form. Now i am trying to work out a script that pulls out weekly hours of employee.

Code: Select all

while($row_getReport = mysql_fetch_assoc($getReport)) {

			$week_hours_row = "SELECT ((UNIX_TIMESTAMP( ma1 ) - UNIX_TIMESTAMP( ma2 )) / 3600),
			((UNIX_TIMESTAMP( ti1 ) - UNIX_TIMESTAMP( ti2 )) / 3600),((UNIX_TIMESTAMP( ke1 ) - UNIX_TIMESTAMP( ke2 )) / 3600),
			((UNIX_TIMESTAMP( to1 ) - UNIX_TIMESTAMP( to2 )) / 3600),((UNIX_TIMESTAMP( pe1 ) - UNIX_TIMESTAMP( pe2 )) / 3600),
			((UNIX_TIMESTAMP( la1 ) - UNIX_TIMESTAMP( la2 )) / 3600),((UNIX_TIMESTAMP( su1 ) - UNIX_TIMESTAMP( su2 )) / 3600) AS hrs FROM workhours";
			mysql_select_db($database_gntdb, $gntdb) or die(mysql_error());
			$hours_in_week = mysql_query($week_hours_row) or die(mysql_error());
			$get_hours_in_week = mysql_fetch_array($hours_in_week) or die(mysql_error());
This one gets the difference of coming and leaving in decimal hours. Then i make some math to solve out if if employee gets extra 0,5 hours per day.

Code: Select all

$ma = abs($get_hours_in_weekї'0']);
			if($ma > '7.5' && $ma < '8.0') &#123; $ma_VKY = '8.0'-$ma; &#125; elseif($ma > '8.0') &#123; $ma_VKY = '0.5'; &#125; else &#123; $ma_VKY = '0.0'; &#125;
...and so on for each day.

next the script prints out the values and closes while loop.

The prob is this: Its works out fine with ONE entry in DB, but of course with multiple entries (the way it goes...) those figures won't be realistic, due to multiple instances of same variables ( because of the while loop ).

For example, if i have two entries in DB, the first one has worked from monday to thursday, but the other one only from monday to tuesday, the print screws up like this:

Name: John Doe
MON: 8.75 VKY: 0.5
TUE: 8.97 VKY: 0.5
WED: 9.07 VKY: 0.5
THU: 5.5 VKY: 0.0
FRI: 0 VKY: 0.0
SAT: 0 VKY: 0.0
SUN: 0 VKY: 0.0
WEEK: 32.29 1.5

Name: Jane Doe
MON: 8.75 VKY: 0.5
TUE: 8.97 VKY: 0.5
WED: 9.07 VKY: 0.5
THU: 5.5 VKY: 0.0
FRI: 0 VKY: 0.0
SAT: 0 VKY: 0.0
SUN: 0 VKY: 0.0
WEEK: 32.29 1.5

U see what i mean? Each row is different in database, but the print is like this...

Anyone? Anything?

Posted: Fri Jan 21, 2005 3:28 am
by Czar
up...

Posted: Fri Jan 21, 2005 3:37 am
by feyd
sorry I forgot about this thread.. :oops:

could you post the table structure (create table and all) and some dump data, so I can play around with it?

Posted: Mon Jan 24, 2005 4:02 am
by Czar
Table structure:

Code: Select all

CREATE TABLE `workhours` (
  `id` int(10) NOT NULL auto_increment,
  `kpa` char(3) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  `viikko` int(2) NOT NULL default '0',
  `ma1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ma2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ma_s` varchar(25) NOT NULL default '',
  `ti1` datetime default '0000-00-00 00:00:00',
  `ti2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ti_s` varchar(25) NOT NULL default '',
  `ke1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ke2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ke_s` varchar(25) NOT NULL default '',
  `to1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `to2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `to_s` varchar(25) NOT NULL default '',
  `pe1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `pe2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `pe_s` varchar(25) NOT NULL default '',
  `la1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `la2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `la_s` varchar(25) NOT NULL default '',
  `su1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `su2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `su_s` varchar(25) NOT NULL default '',
  `comments` varchar(200) default NULL,
  `shift` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
And this is how data goes into the table:

Code: Select all

INSERT INTO `workhours` VALUES (7, '402', 'John Doe', 2, 0x323030352d30312d31302030383a30303a3030, 0x323030352d30312d31302031363a34353a3030, '1', '2005-01-11 08:44:00', 0x323030352d30312d31312031373a34323a3030, '1', 0x323030352d30312d31322030393a31303a3030, 0x323030352d30312d31322031383a31343a3030, '1', 0x323030352d30312d31332030373a30303a3030, 0x323030352d30312d31332031323a33303a3030, '1', 0x303030302d30302d30302030303a30303a3030, 0x303030302d30302d30302030303a30303a3030, '1', 0x303030302d30302d30302030303a30303a3030, 0x303030302d30302d30302030303a30303a3030, '1', 0x303030302d30302d30302030303a30303a3030, 0x303030302d30302d30302030303a30303a3030, '1', NULL, 1);
etc...