Add timestamps to get total time

matthewst · May 4, 2007

Since you have your job "actions" in a table already, just add a column to that table with -1, 0, or 1 if the action is a "starting/resume" action (-1), "ending/pause" action (+1), or neither (0). Then multiply this coefficient by a UNIX timestamp and add up the results. A UNIX timestamp is the number of seconds since 1/1/1970 GMT/UTC.

I'm using phpmyadmin. I can add the new column but don't know how to do the rest {if the action is a "starting/resume" action (-1), "ending/pause" action (+1), or neither (0)}.

Wildbug · May 4, 2007

I'm using phpmyadmin. I can add the new column but don't know how to do the rest {if the action is a "starting/resume" action (-1), "ending/pause" action (+1), or neither (0)}.

You can do an UPDATE, but you'll have to decide which actions are which. The column type should probably be TINYINT (I don't think ENUM would work).

UPDATE actions_table SET new_column=1 WHERE name="end_action_1" OR name="end_action_2" OR name="end_action_3".... etc.

UPDATE actions_table SET new_column=-1 WHERE name="start_action_1" OR .... etc, etc.

I noticed after I posted that you also want the times between action pairs as well as cumulative time. That might be a bit more complicated. I think I would still advocate an SQL solution, though, for at least two reasons. First, you eliminate the work you actually need to do in PHP. Second, if your action names change at any point, you won't have them hard coded into your PHP script; you can update the SQL tables fairly easily while the SQL query is general enough that it can stay the same. This solution will remain robust if the actions ever change or new ones need to be added.

Wildbug · May 7, 2007

I looked at your sample data this weekend. There are alot of inconsistencies that make certain assumptions impossible. For instance, there are some bogus entries such as a "Begin Job X" right after an "End Job X." And it doesn't help that Pause and Resume are not job-specific. If the database were altered to something like:

# If job_log had these ENUMs
action		subaction
---------------	---------
Creation		Start
First Proof	Stop
Outside Fix	Pause
Outside Change	Resume
In-House Fix
Second Proof

# ...and had data set up like this:

action		subaction
---------------	---------
Creation		Start
Creation		Pause
Creation		Resume
Creation		Stop
First Proof	Start
First Proof	Stop

then you could account better for inaccurate entries, but I realize that would entail a major change to the rest of your code. I'll keep thinking about it. The dirty data makes it difficult.

(Incidently, having the data set up as above also makes it easy to group by action and get the amount of time spent on each as well as the whole job.)

Wildbug · May 11, 2007

Okay, I think I have something. Remember when I said I had an elegant solution? Well, stop remembering that.

Upon closer examination of your data, there are vast numbers of incorrect "jobstamps" (timestamp + action = "jobstamp"). Simply subtracting the START/RESUMEs from the PAUSE/ENDs wouldn't work because there were unmatched and extraneous entries. A filter is necessary. After a week of working on this, I came up with a solution that works. It isn't to-the-minute perfect due to some conservative assumptions that I made, but on the dataset of 17,887 rows in your sample file, there are no wild values.

Examples:

+-------+-------+------+---------------------+----------------------+
| id    | ad_id | eid  | FROM_UNIXTIME(time) | action_name          |
+-------+-------+------+---------------------+----------------------+
|  4177 | 15923 | 1018 | 2007-02-21 07:01:40 | Start Outside Change | Double START
|  4178 | 15923 | 1018 | 2007-02-21 07:01:47 | Start Outside Change |
|  4179 | 15923 | 1018 | 2007-02-21 07:09:02 | End Creation         | Double, unmatched ENDs
|  4180 | 15923 | 1018 | 2007-02-21 07:09:05 | End Outside Change   |

| 17263 | 16298 | 1001 | 2007-04-26 10:33:19 | Start Outside Change |
| 17266 | 16298 | 1001 | 2007-04-26 10:39:38 | End Creation         |
| 17267 | 16298 | 1001 | 2007-04-26 10:43:24 | End Outside Change   | Double, unmatched ENDs
+-------+-------+------+---------------------+----------------------+
|  6034 | 16740 | 1016 | 2007-03-02 02:49:07 | End 1st Proof - FIX  | Superfluous END
|  6160 | 16740 | 1016 | 2007-03-02 10:03:15 | Begin 1st Proof      |
|  6161 | 16740 | 1016 | 2007-03-02 10:04:50 | End 1st Proof -OK    |
|  6162 | 16740 | 1016 | 2007-03-02 10:04:53 | Begin 1st Proof      | Superfluous START

|  6005 | 16740 | 1018 | 2007-03-01 12:16:50 | End Creation         | Superfluous END
|  6007 | 16740 | 1018 | 2007-03-01 12:17:00 | End Creation         | Superfluous END
|  6148 | 16740 | 1018 | 2007-03-02 09:10:06 | Start Outside Change |
|  6150 | 16740 | 1018 | 2007-03-02 09:29:12 | End Outside Change   |

|  2023 | 16194 | 1016 | 2007-02-12 06:28:33 | Begin 1st Proof      |  Your users probably aren't
|  2591 | 16194 | 1016 | 2007-02-14 06:12:02 | End 1st Proof -OK    |  actually this industrious.

One of the constraints I used was to do this within your existing database structure, or at least implement nothing that made you have to change pre-existing code. I added one column, started_by, to your job_actions table. This is similar to your next_action column, except in reverse, since there are two endings for one beginning in many cases. This is what I added:

ALTER TABLE job_actions ADD COLUMN started_by SMALLINT UNSIGNED AFTER next_action;

UPDATE job_actions SET started_by=200 WHERE action_id=201;

UPDATE job_actions SET started_by=202 WHERE action_id=203;

UPDATE job_actions SET started_by=204 WHERE action_id=205;

UPDATE job_actions SET started_by=206 WHERE action_id=207;

UPDATE job_actions SET started_by=300 WHERE action_id=301;

UPDATE job_actions SET started_by=300 WHERE action_id=302;

UPDATE job_actions SET started_by=303 WHERE action_id=304;

UPDATE job_actions SET started_by=303 WHERE action_id=305;

UPDATE job_actions SET started_by=210 WHERE action_id=211;

UPDATE job_actions SET started_by=303 WHERE action_id=305;

UPDATE job_actions SET started_by=400 WHERE action_id=401;

UPDATE job_actions SET started_by=410 WHERE action_id=411;

UPDATE job_actions SET started_by=440 WHERE action_id=441;

UPDATE job_actions SET started_by=430 WHERE action_id=431;

UPDATE job_actions SET started_by=420 WHERE action_id=421;

I also added two indexes:

CREATE INDEX ad_index ON job_log (ad_id);

CREATE INDEX employee_index ON job_log (employee_id);

(See query, next post)

There are five queries. The first sets some MySQL session variables that will be used in the filtering block. Then comes a four-subquery nest. The innermost query joins job_log to job_actions on action=action_id, ignores all non-paired actions, includes a dummy row of data via a UNION, and orders the results before passing them to the next subquery. This next one is the one that does most of the work. I came up with a few heurstics that identified potentially "bogus" jobstamps and included their id in the results. The next outermost subquery selects only the two rows wherein are the bogus ids -- bogus_current (the current row's id if it's bogus) and bogus_prev (a previous row's id that is bogus) -- and LEFT JOINs these results to the outermost query on id=bogus_current or bogus_prev. The WHERE clause selects those which are NULL for bogus_current (i.e., nothing has been LEFT JOINed to that row because its id has not been identified as erroneous). Ta da! Clean results! The clean results are GROUPed by ad_id then employee_id. There is a SUM() function in the SELECT list that adds up the time multiplied by -1 or 1 depending on if it's a START/RESUME or PAUSE/END action. The WITH ROLLUP in the GROUP clause causes a summary row to be printed for each subgroup.

The filter uses a few things to help it decide if a jobstamp is valid. (1) a boundary is defined at a new ad_id, employee_id, or time difference since the last jobstamp of more than @interval seconds (currently 4 hours). (2) It takes the most recent START in the case of repeated STARTs. (3) It prefers the proper END stamp but will accept any END if it doesn't find the right one. (4) It takes latest proper END or the earliest "other" END.

Example output:

+-------+-------------+---------------+
| ad_id | employee_id | time_elapsed  |
+-------+-------------+---------------+
| 17537 |        1001 | 00:05:39      |
| 17537 |        1027 | 00:03:23      |
| 17537 |        NULL | 00:09:02      |
| 17538 |        1015 | 00:49:04      |
| 17538 |        NULL | 00:49:04      |

The NULL employee_id values are time subtotals.

Wildbug · May 11, 2007

SELECT @start_resume:=-1, @current_block:=-1, @last_ad:=-1, @last_em:=-1, @last_time:=0, @last_end:= -1, @interval:=4/*hours*/*60*60;
SELECT ad_id,employee_id,SEC_TO_TIME(SUM(time*IF(action IN (200,202,204,206,300,303,801),-1,1))) AS time_elapsed
FROM job_log LEFT JOIN (
  SELECT bogus_current,bogus_prev FROM (
    SELECT
      @boundary := IF (employee_id != @last_em OR ad_id != @last_ad OR time - @last_time > @interval,1,0) AS b,
      CASE -- Verify current line
      -- Boundary change / A boundary must be followed by a START/RESUME action
        WHEN @boundary THEN IF (action IN /*Start*/ (200,202,204,206,300,303,801),-1,id)
      -- Outside START, anything is bogus
        WHEN @current_block = -1 AND action NOT IN /*Start*/ (200,202,204,206,300,303) THEN id
      -- PAUSE
        WHEN action = 800 AND @start_resume = -1 THEN id
      -- END
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @start_resume = -1 AND @last_end = -1 THEN id
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @last_end != -1 AND started_by != @current_block THEN id
        ELSE -1
      END AS bogus_current,

      CASE -- Verify a previous line
        WHEN @boundary THEN @start_resume
      -- START/RESUME
        WHEN action IN /*Start-Resume*/ (200,202,204,206,300,303,801) THEN @start_resume
      -- END
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND started_by = @current_block THEN @last_end
        ELSE -1
      END AS bogus_prev,

      @last_end := CASE -- Store the value of the last used END
        WHEN @boundary THEN -1
        WHEN action IN /*Start*/ (200,202,204,206,300,303) THEN -1
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @start_resume = -1 AND @last_end = -1 THEN -1
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND (started_by = @current_block OR @last_end = -1 AND @current_block != -1) THEN id
        WHEN action = 801 AND @start_resume != -1 THEN -1
        ELSE @last_end
      END AS set_end,
      @current_block := CASE -- Store the action value of the current START
        WHEN action IN /*Start*/ (200,202,204,206,300,303) THEN action
        -- in case of accidental time break at PAUSE/RESUME, don't reset block
        WHEN action = 801 AND employee_id = @last_em AND ad_id = @last_ad THEN @current_block
        WHEN @boundary THEN -1
        ELSE @current_block
      END AS set_cb,
      @start_resume := IF(action IN /*Start-Resume*/ (200,202,204,206,300,303,801),id,-1) AS set_sr,
      @last_time := time AS set_time,
      @last_ad := ad_id AS set_la,
      @last_em := employee_id AS set_pe
    FROM (
      (SELECT id,employee_id,ad_id,table_id,time,action,action_name,started_by FROM job_log JOIN job_actions ON action=action_id
        WHERE action IN (200,202,204,206,300,303,201,203,205,207,301,302,304,305,800,801)) -- *** (here)
      UNION
      -- Adding this row of dummy data via UNION is necessary in order to return a final bogus_prev value
      (SELECT 2147483647,'2147483647','999999999','999999999','0',998,'Dummy Row',NULL)
      ORDER BY ad_id,employee_id,time)
    AS j1) AS j2
  ) AS j3 ON (id=bogus_current OR id=bogus_prev)
  WHERE bogus_current IS NULL AND action IN (200,202,204,206,300,303,201,203,205,207,301,302,304,305,800,801)
  -- AND ad_id = -- Use either of these clauses to restrict your results.  Also add the same restriction to the innermost SELECT. ***
  -- AND ad_id IN ()
GROUP BY ad_id,employee_id WITH ROLLUP;

3176 rows in set (25.22 sec) on this computer (dual AMD something or other w/ 1G RAM)

matthewst · May 15, 2007

:o

:D

matthewst · May 15, 2007

Your code is so far above my level it's making me go cross-eyed.

I was able to alter the table as you suggested.

Now, what do I do with this?

SELECT @start_resume:=-1, @current_block:=-1, @last_ad:=-1, @last_em:=-1, @last_time:=0, @last_end:= -1, @interval:=4/*hours*/*60*60;
SELECT ad_id,employee_id,SEC_TO_TIME(SUM(time*IF(action IN (200,202,204,206,300,303,801),-1,1))) AS time_elapsed
FROM job_log LEFT JOIN (
  SELECT bogus_current,bogus_prev FROM (
    SELECT
      @boundary := IF (employee_id != @last_em OR ad_id != @last_ad OR time - @last_time > @interval,1,0) AS b,
      CASE -- Verify current line
      -- Boundary change / A boundary must be followed by a START/RESUME action
        WHEN @boundary THEN IF (action IN /*Start*/ (200,202,204,206,300,303,801),-1,id)
      -- Outside START, anything is bogus
        WHEN @current_block = -1 AND action NOT IN /*Start*/ (200,202,204,206,300,303) THEN id
      -- PAUSE
        WHEN action = 800 AND @start_resume = -1 THEN id
      -- END
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @start_resume = -1 AND @last_end = -1 THEN id
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @last_end != -1 AND started_by != @current_block THEN id
        ELSE -1
      END AS bogus_current,

      CASE -- Verify a previous line
        WHEN @boundary THEN @start_resume
      -- START/RESUME
        WHEN action IN /*Start-Resume*/ (200,202,204,206,300,303,801) THEN @start_resume
      -- END
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND started_by = @current_block THEN @last_end
        ELSE -1
      END AS bogus_prev,

      @last_end := CASE -- Store the value of the last used END
        WHEN @boundary THEN -1
        WHEN action IN /*Start*/ (200,202,204,206,300,303) THEN -1
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @start_resume = -1 AND @last_end = -1 THEN -1
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND (started_by = @current_block OR @last_end = -1 AND @current_block != -1) THEN id
        WHEN action = 801 AND @start_resume != -1 THEN -1
        ELSE @last_end
      END AS set_end,
      @current_block := CASE -- Store the action value of the current START
        WHEN action IN /*Start*/ (200,202,204,206,300,303) THEN action
        -- in case of accidental time break at PAUSE/RESUME, don't reset block
        WHEN action = 801 AND employee_id = @last_em AND ad_id = @last_ad THEN @current_block
        WHEN @boundary THEN -1
        ELSE @current_block
      END AS set_cb,
      @start_resume := IF(action IN /*Start-Resume*/ (200,202,204,206,300,303,801),id,-1) AS set_sr,
      @last_time := time AS set_time,
      @last_ad := ad_id AS set_la,
      @last_em := employee_id AS set_pe
    FROM (
      (SELECT id,employee_id,ad_id,table_id,time,action,action_name,started_by FROM job_log JOIN job_actions ON action=action_id
        WHERE action IN (200,202,204,206,300,303,201,203,205,207,301,302,304,305,800,801)) -- *** (here)
      UNION
      -- Adding this row of dummy data via UNION is necessary in order to return a final bogus_prev value
      (SELECT 2147483647,'2147483647','999999999','999999999','0',998,'Dummy Row',NULL)
      ORDER BY ad_id,employee_id,time)
    AS j1) AS j2
  ) AS j3 ON (id=bogus_current OR id=bogus_prev)
  WHERE bogus_current IS NULL AND action IN (200,202,204,206,300,303,201,203,205,207,301,302,304,305,800,801)
  -- AND ad_id = -- Use either of these clauses to restrict your results.  Also add the same restriction to the innermost SELECT. ***
  -- AND ad_id IN ()
GROUP BY ad_id,employee_id WITH ROLLUP;

I put it in navicat and ran it as a query, but now what?

P.S. Thanks for all your help.

Wildbug · May 15, 2007

Has it given you the results you expected when you ran it as a query? As is, it should give you everything in your table, grouped and totaled.

The other alterations you may need to make would be to add any other starting or ending actions in the "action IN (...)" clauses that I may have left out. I just used the 2xx and 3xx ones. Comments (/*...*/) are there to explain which clause should contain which ids.

If you want to use it for totalling table time, you'll need to alter the outer SELECT to include the table_id and change the GROUP BY clause to "GROUP BY table_id,ad_id(?),employee_id". You can also JOIN other tables to it to include the name of the company, employee name, etc.

In practice, will you use this for a single table at a time? If you're using this through PHP, then put a variable in the innermost and outermost SELECTs like: "WHERE table_id=$table_id_var". It might be more legible to use heredoc syntax to define this large query.

(In fact, this is such a long query, for only a single table's worth of jobstamps, it might be better to implement it in PHP using the same heuristics.)

matthewst · May 15, 2007

yes, one table at at time

Wildbug · May 16, 2007

+------------+-----------+-------------+---------------------+----------------------+---+---------------+------------+---------+--------+--------+
| id         | ad_id     | employee_id | FROM_UNIXTIME(time) | action_name          | b | bogus_current | bogus_prev | set_end | set_cb | set_sr |
+------------+-----------+-------------+---------------------+----------------------+---+---------------+------------+---------+--------+--------+
| ...                                                                                                                                            |
|       4248 | 15923     | 1016        | 2007-02-21 09:32:18 | Begin 1st Proof      | 1 |            -1 |         -1 |      -1 | 300    |   4248 |

|       4177 | 15923     | 1018        | 2007-02-21 07:01:40 | Start Outside Change | 1 |            -1 |       4248 |      -1 | 206    |   4177 |
|       4178 | 15923     | 1018        | 2007-02-21 07:01:47 | Start Outside Change | 0 |            -1 |       4177 |      -1 | 206    |   4178 |
|       4179 | 15923     | 1018        | 2007-02-21 07:09:02 | End Creation         | 0 |            -1 |         -1 |    4179 | 206    |     -1 |
|       4180 | 15923     | 1018        | 2007-02-21 07:09:05 | End Outside Change   | 0 |            -1 |       4179 |    4180 | 206    |     -1 |

|       4709 | 15923     | 1018        | 2007-02-23 08:51:01 | Start Outside Change | 1 |            -1 |         -1 |      -1 | 206    |   4709 |
|       4712 | 15923     | 1018        | 2007-02-23 08:53:27 | End Outside Change   | 0 |            -1 |         -1 |    4712 | 206    |     -1 |
+------------+-----------+-------------+---------------------+----------------------+---+---------------+------------+---------+--------+--------+
|       1305 | 16194     | 0           | 2007-02-07 06:37:03 | End Outside Change   | 1 |          1305 |         -1 |      -1 | -1     |     -1 |
| ...                                                                                                                                            |
|       1337 | 16194     | 1016        | 2007-02-07 07:18:14 | Begin 1st Proof      | 0 |            -1 |         -1 |      -1 | 300    |   1337 |

|       2017 | 16194     | 1016        | 2007-02-12 06:22:46 | Begin 1st Proof      | 1 |            -1 |       1337 |      -1 | 300    |   2017 |
|       2022 | 16194     | 1016        | 2007-02-12 06:28:22 | End 1st Proof -OK    | 0 |            -1 |         -1 |    2022 | 300    |     -1 |
|       2023 | 16194     | 1016        | 2007-02-12 06:28:33 | Begin 1st Proof      | 0 |            -1 |         -1 |      -1 | 300    |   2023 |

|       2591 | 16194     | 1016        | 2007-02-14 06:12:02 | End 1st Proof -OK    | 1 |          2591 |       2023 |      -1 | -1     |     -1 |

|       4629 | 16194     | 1016        | 2007-02-23 04:17:03 | Begin 1st Proof      | 1 |            -1 |         -1 |      -1 | 300    |   4629 |
|       4632 | 16194     | 1016        | 2007-02-23 04:24:40 | End 1st Proof -OK    | 0 |            -1 |         -1 |    4632 | 300    |     -1 |
|       4633 | 16194     | 1016        | 2007-02-23 04:24:44 | Begin 1st Proof      | 0 |            -1 |         -1 |      -1 | 300    |   4633 |
+------------+-----------+-------------+---------------------+----------------------+---+---------------+------------+---------+--------+--------+
| 2147483647 | 999999999 | 2147483647  | 1969-12-31 19:00:00 | Dummy Row            | 1 |    2147483647 |         -1 |      -1 | -1     |     -1 |
+------------+-----------+-------------+---------------------+----------------------+---+---------------+------------+---------+--------+--------+

I hope this helps you visualize the "meat" of the query.

That's an excerpt of what the inner subquery looks like that marks bogus jobstamps. You can see their ids in the bogus_current and bogus_prev columns, depending on if it's a current line or a previous line.

Notice the "b" column. This is 1 on a "boundary", zero otherwise. If you wanted to do the actual processing in PHP instead of with a complicated MySQL query, you could retain this column and use it to help decide which entries were valid. I'd define it as "on an ad_id or employee_id change, or on a time difference greater than x hours except in the case of PAUSE/RESUME, define a boundary." The time difference metric helps you seperate START/END stamps which don't otherwise cross a boundary but are probably invalid, as in the case of 2023 and 2591 in the example above.

These data are LEFT JOINed to the original job_log data on the id contained in either bogus_current or bogus_prev, thus identifying invalid entries. The WHERE clause in that SELECT then weeds them out. A LEFT (or OUTER) JOIN (as opposed to a "regular" [or INNER] JOIN) creates a NULL set of data from the second table to join when there is no matching value.

Is any of this getting you closer to your goals?

matthewst · May 17, 2007

Wild, I can't thank you enough for all your help. The problem now is that I'm just a sql noob and as such I don't know what to do with all the code you've written. I've read through it as best as I can and I think you may have the solution, I just don't know how to implement it. I hate to ask for more when you've done so much already, but can you tell me what I'm supposed to do now?

Do I put the queries in a php page and run them then echo the results?

Do I put the queries in navicat, run them then query the results from a php page when I need them?

Again, thank you.

Wildbug · May 17, 2007

Well, it all depends on how you want to return the values. I'm not really familiar with Navicat (a MySQL GUI client?), but I've been assuming you'll use these results on a dynamic PHP page. I made a small script that returns the times, but adds them in PHP:

<html><body><pre><?php

function sec_to_time($secs) {
return sprintf('%02d:%02u:%02u',floor($secs/3600),floor($secs/60) % 60,$secs % 60);
}

mysql_connect('localhost','xxxxxxxxx','xxxxxxxxx');
mysql_select_db('test');

$ad = 15923;

$query = <<<END_OF_Q
SELECT ad_id,employee_id,FROM_UNIXTIME(time),time*IF(action IN (200,202,204,206,300,303,801),-1,1) AS ts,IF(action IN (200,202,204,206,300,303),@task:=@task+1,@task) AS task,action_name
FROM job_log LEFT JOIN (
  SELECT bogus_current,bogus_prev FROM (
    SELECT
      @boundary := IF (employee_id != @last_em OR ad_id != @last_ad OR time - @last_time > @interval,1,0) AS b,
      CASE -- Verify current line
        WHEN @boundary THEN IF (action IN /*Start*/ (200,202,204,206,300,303,801),-1,id)
        WHEN @current_block = -1 AND action NOT IN /*Start*/ (200,202,204,206,300,303) THEN id
        WHEN action = 800 AND @start_resume = -1 THEN id
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @start_resume = -1 AND @last_end = -1 THEN id
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @last_end != -1 AND started_by != @current_block THEN id
        ELSE -1
      END AS bogus_current,

      CASE -- Verify a previous line
        WHEN @boundary THEN @start_resume
        WHEN action IN /*Start-Resume*/ (200,202,204,206,300,303,801) THEN @start_resume
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND started_by = @current_block THEN @last_end
        ELSE -1
      END AS bogus_prev,

      @last_end := CASE -- Store the value of the last used END
        WHEN @boundary THEN -1
        WHEN action IN /*Start*/ (200,202,204,206,300,303) THEN -1
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND @start_resume = -1 AND @last_end = -1 THEN -1
        WHEN action IN /*End*/ (201,203,205,207,301,302,304,305) AND (started_by = @current_block OR @last_end = -1 AND @current_block != -1) THEN id
        WHEN action = 801 AND @start_resume != -1 THEN -1
        ELSE @last_end
      END AS set_end,
      @current_block := CASE -- Store the action value of the current START
        WHEN action IN /*Start*/ (200,202,204,206,300,303) THEN action
        WHEN action = 801 AND employee_id = @last_em AND ad_id = @last_ad THEN @current_block
        WHEN @boundary THEN -1
        ELSE @current_block
      END AS set_cb,
      @start_resume := IF(action IN /*Start-Resume*/ (200,202,204,206,300,303,801),id,-1) AS set_sr,
      @last_time := time AS set_time,
      @last_ad := ad_id AS set_la,
      @last_em := employee_id AS set_pe
    FROM (
      (SELECT id,employee_id,ad_id,table_id,time,action,action_name,started_by FROM job_log JOIN job_actions ON action=action_id
        WHERE action IN (200,202,204,206,300,303,201,203,205,207,301,302,304,305,800,801) AND ad_id = $ad)
      UNION
      (SELECT 2147483647,'2147483647','999999999','999999999','0',998,'Dummy Row',NULL)
      ORDER BY ad_id,employee_id,time)
    AS j1) AS j2
  ) AS j3 ON (id=bogus_current OR id=bogus_prev) JOIN job_actions ON action=action_id
  WHERE bogus_current IS NULL AND action IN (200,202,204,206,300,303,201,203,205,207,301,302,304,305,800,801)
  AND ad_id = $ad
END_OF_Q;

mysql_query('SELECT @start_resume:=-1, @current_block:=-1, @last_ad:=-1, @last_em:=-1, @last_time:=0, @last_end:= -1, @interval:=4/*hours*/*60*60, @task:=0');
$result = mysql_query($query);

if ($result && mysql_num_rows($result)) {
$time_total_task = 0;
$time_total_ad = 0;
$time_total = 0;
$ad = 0;
$task = 0;
echo "<table>\n";
echo "\t<tr><td>Ad</td><td>Employee</td><td></td><td></td></tr>\n";
while ($row = mysql_fetch_row($result)) {
	if ($row[4] != $task and $task != 0) {
	// Next task?  Then print previous task tally:
		printf("\t<tr><td colspan=3></td><td>%s</td></tr>\n",sec_to_time($time_total_task));
		$time_total_task = 0;
	}
	if ($row[0] != $ad and $ad != 0) {
	// Next ad?  Then print previous ad tally:
		printf("\t<tr><td colspan=3>Total for Ad #$ad</td><td>%s</td></tr>\n",sec_to_time($time_total_ad));
		$time_total_ad = 0;
	}
	echo "\t<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[5]</td></tr>\n";
	$time_total_task += $row[3];
	$time_total_ad += $row[3];
	$task = $row[4];
	$ad = $row[0];
}
// Print final task and ad tallys:
printf("\t<tr><td colspan=3></td><td>%s</td></tr>\n",sec_to_time($time_total_task));
printf("\t<tr><td colspan=3>Total for Ad #$ad</td><td>%s</td></tr>\n",sec_to_time($time_total_ad));
echo "</table>\n";
}
?></pre></body></html>

Script output:

Ad	Employee		
15923	1015	2007-02-08 04:46:30	Start Creation
15923	1015	2007-02-08 05:43:09	Pause
15923	1015	2007-02-08 05:55:20	Resume
15923	1015	2007-02-08 06:20:25	End Creation
                           01:21:44
15923	1010	2007-02-08 09:32:06	Begin 1st Proof
15923	1010	2007-02-08 09:37:25	End 1st Proof - FIX
                           00:05:19
15923	1015	2007-02-08 10:18:16	Start In-House Fix
15923	1015	2007-02-08 10:20:02	End In-House Fix
                           00:01:46
15923	1010	2007-02-08 10:21:57	Begin 1st Proof
15923	1010	2007-02-08 10:22:58	End 1st Proof -OK
                           00:01:01
15923	1018	2007-02-21 07:01:47	Start Outside Change
15923	1018	2007-02-21 07:09:05	End Outside Change
                           00:07:18
15923	1018	2007-02-23 08:51:01	Start Outside Change
15923	1018	2007-02-23 08:53:27	End Outside Change
                           00:02:26
Total for Ad #15923        01:39:34

Actual query output:

 $row[0]       $row[1]               $row[2]      $row[3]  $row[4]              $row[5]
+-------+-------------+---------------------+-------------+------+----------------------+
| ad_id | employee_id | FROM_UNIXTIME(time) | ts          | task | action_name          |
+-------+-------------+---------------------+-------------+------+----------------------+
| 15923 |        1015 | 2007-02-08 04:46:30 | -1170927990 |    1 | Start Creation       |
| 15923 |        1015 | 2007-02-08 05:43:09 |  1170931389 |    1 | Pause                |
| 15923 |        1015 | 2007-02-08 05:55:20 | -1170932120 |    1 | Resume               |
| 15923 |        1015 | 2007-02-08 06:20:25 |  1170933625 |    1 | End Creation         |
| 15923 |        1010 | 2007-02-08 09:32:06 | -1170945126 |    2 | Begin 1st Proof      |
| 15923 |        1010 | 2007-02-08 09:37:25 |  1170945445 |    2 | End 1st Proof - FIX  |
| 15923 |        1015 | 2007-02-08 10:18:16 | -1170947896 |    3 | Start In-House Fix   |
| 15923 |        1015 | 2007-02-08 10:20:02 |  1170948002 |    3 | End In-House Fix     |
| 15923 |        1010 | 2007-02-08 10:21:57 | -1170948117 |    4 | Begin 1st Proof      |
| 15923 |        1010 | 2007-02-08 10:22:58 |  1170948178 |    4 | End 1st Proof -OK    |
| 15923 |        1018 | 2007-02-21 07:01:47 | -1172059307 |    5 | Start Outside Change |
| 15923 |        1018 | 2007-02-21 07:09:05 |  1172059745 |    5 | End Outside Change   |
| 15923 |        1018 | 2007-02-23 08:51:01 | -1172238661 |    6 | Start Outside Change |
| 15923 |        1018 | 2007-02-23 08:53:27 |  1172238807 |    6 | End Outside Change   |
+-------+-------------+---------------------+-------------+------+----------------------+
14 rows in set (0.05 sec)

Note: The outer SQL query might need an extra subSELECT to return the rows properly ordered for the purpose of assigning the @task column, similiar to the innermost SELECT, since it relies on the rows being in order.

Sign In

Add timestamps to get total time

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information