Jump to content

long time no see

Destramic

By Destramic
in MySQL Help

 Share

Recommended Posts

Destramic Advanced Member

Destramic

Posted
Posted (edited)

after along time away from trying to progamming, im trying to get back into the swing of things.

I'm trying to complete a project i once started that @Barand helped me out with many moons ago.

 

im trying to create a scheduling programme where a task is give to a operative and added to the operatives_tasks table, which stores the start timestamp of the task. Overall what im trying to achieve is to generate a finish timestamp for the task, based on the operatives, contracted days, hours, start time end time and any annual leave he/she man have in between aswell as the task duration.

 

here is my sql and results at present, but im kinda scratching my head on generating a task finish date.

  

SELECT u.username,
       tt.task_type, 
       tt.duration_hours `task_duration_hours`,
       ot.start_timestamp AS `task_start_timestamp`, 
       @task_week_day_start := WEEKDAY(ot.start_timestamp) AS `task_week_day_start`,
       och.shift_hours,
       och.week_day,
       och.start_time AS `operatives_start_time`,
       och.finish_time AS `operatives_finish_time`,
       annual_leave_start_timestamp,
       annual_leave_end_timestamp
FROM tasks t
LEFT JOIN operatives_tasks ot ON ot.task_id = t.task_id
LEFT JOIN task_types tt ON tt.task_type_id = t.task_type_id
LEFT JOIN users u on u.user_id = ot.user_id
LEFT JOIN (
	SELECT och.user_id,
           och.week_day,
           och.start_time,
           och.finish_time,
		   @hours := CONCAT('2020-01-01 ', @contacted_hours),
		   @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30 AS `break_duration`,
		   CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,
		   oal.start_timestamp AS `annual_leave_start_timestamp`,
           oal.end_timestamp AS `annual_leave_end_timestamp`
	FROM operative_contracted_hours och 
    INNER JOIN operatives_annual_leave oal ON oal.user_id = och.user_id
) AS och ON och.user_id = u.user_id 
WHERE u.user_id = 1

 

any help would be appreciated, here are the tables also if required

https://pastebin.com/mKXLZUVJ

better quality picture

https://i.ibb.co/k01nYq2/Untitled.png

thank you

 

Untitled.png

Edited by Destramic
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted
1 hour ago, Destramic said:

to generate a finish timestamp for the task, based on the operatives, contracted days, hours, start time end time and any annual leave he/she man have in between as well as the task duration

What are the rules for this calculation? If your working days are 8 - 4, that's at least 5 tea-breaks

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

nice to hear from you again barand :), the caluclations are based on 4 variables

firsly though ive noticed that i missed @contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`, from my sql 

SELECT u.username,
       tt.task_type, 
       tt.duration_hours `task_duration_hours`,
       ot.start_timestamp AS `task_start_timestamp`, 
       @task_week_day_start := WEEKDAY(ot.start_timestamp) AS `task_week_day_start`,
       och.shift_hours,
       och.week_day,
       och.start_time AS `operatives_start_time`,
       och.finish_time AS `operatives_finish_time`,
       annual_leave_start_timestamp,
       annual_leave_end_timestamp
FROM tasks t
LEFT JOIN operatives_tasks ot ON ot.task_id = t.task_id
LEFT JOIN task_types tt ON tt.task_type_id = t.task_type_id
LEFT JOIN users u on u.user_id = ot.user_id
LEFT JOIN (
	SELECT och.user_id,
           och.week_day,
           och.start_time,
           och.finish_time,
		   @contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`,
		   @hours := CONCAT('2020-01-01 ', @contacted_hours),
		   @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30 AS `break_duration`,
		   CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,
		   oal.start_timestamp AS `annual_leave_start_timestamp`,
           oal.end_timestamp AS `annual_leave_end_timestamp`
	FROM operative_contracted_hours och 
    INNER JOIN operatives_annual_leave oal ON oal.user_id = och.user_id
) AS och ON och.user_id = u.user_id 
WHERE u.user_id = 1

 

the 4 variables are: 

@contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`,
@hours := CONCAT('2020-01-01 ', @contacted_hours),
@break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30 AS `break_duration`,
CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,

@contracted_hours for that weekday

@hours is just a created timestamp so i can workout the breaks

@break_duration is putting the timestamp into seconds and converting into a decimal (ie. 08:00 - 16:00 would be 8.0) then dividing by 4.5 hrs, as a employee is entitled to 30 mins break for every 4.5 hours work.

shift_hours then just taking away the 30 min break in this case returning 7.5 (hours)

in this particual query the task is 9 hours long, but i added a 2 hour annual leave for the next day, in operatives_annual_leave table.

so the operative works 7.5 each day in this case, and the task duration is 9 hours (1.5 hrs remaining), meaning the task would have to extend over 2 days, including the 2 hour annual leave next day, the operative should be finished      2020-06-24 11:30:00

 

i think im on the right track, but struggling to put it all together

 

thanks you for your reply barand

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted 
       @contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`,
       @hours := CONCAT('2020-01-01 ', @contacted_hours),
       @breaks := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) AS `breaks`,
	   @break_duration := @breaks * 30 AS `break_duration`,
	   CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,
        IF (@breaks = 1, CONCAT("{[", DATE_ADD(och.start_time, INTERVAL 270 MINUTE), ":", DATE_ADD(och.start_time, INTERVAL 300 MINUTE),"]}"), 
        IF (@breakss = 2, CONCAT("{[", DATE_ADD(och.start_time, INTERVAL 270 MINUTE), ":", DATE_ADD(och.start_time, INTERVAL 300 MINUTE), "] , [", DATE_ADD(och.start_time, INTERVAL 540 MINUTE), ":", DATE_ADD(och.start_time, INTERVAL 570 MINUTE), "]}"), NULL)) AS `break_times`,

 

i've also added break times which will display as {[12:30:00:13:00:00]}

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

Suppose a task takes 10 hours. They start at 08:00 on day 1, take a 30 min break (12:30 - 13:00) then work until 16:00. They have now done 7.5 hours with 2.5 hour to go.

Day2, starts at 08:00 but at 09:30 they have done another 4.5 hours on that task so break until 10:00 and finishes the task  at 11:00.

On to the next task - at 15:30 is due another break after 4.5 hours work so has break and goes home at 16:00 having had two breaks that day (7 hours work)

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted
1 hour ago, Barand said:

Do all users always work the same hours every day? EG Wednesday 08:00 - 12:00 = short day and no break)

What if a boiler installation starts at 3pm on a Friday?

no, a user can have different work patterns, for instance a user could work monday, wednesday, friday from 8-1 each day.

 

1 hour ago, Barand said:

Suppose a task takes 10 hours. They start at 08:00 on day 1, take a 30 min break (12:30 - 13:00) then work until 16:00. They have now done 7.5 hours with 2.5 hour to go.

Day2, starts at 08:00 but at 09:30 they have done another 4.5 hours on that task so break until 10:00 and finishes the task  at 11:00.

On to the next task - at 15:30 is due another break after 4.5 hours work so has break and goes home at 16:00 having had two breaks that day (7 hours work)

day 1 is correct in your scenario, but day 2 is a new day therefor if the user/operative is working from 8-4 again he/she will only be entitled to a break for 30 mins at 12.30, but break times can change depending on the users contacted hours. ie (8-12 no break)

but 30 mins every 4.5 hrs is rule of thumb (daily)

if working from, 08:00- 19:00,  9 hrs work the user would be entitled to 2 breaks, break at 12.00 - 12:30 then break at 16:30 - 17:00 and home for 19:00

it probably make more sense to have a break on the 4th hour if entitled to breaks.

 

would it be best for me to create a lof of if statements in the query? how is the best way for me to get the end results (task esistmated completion timestamp)?

 

thank you again barand

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

At its most basic (standard days and no holidays) you have something like this 

SELECT ot.task_id
     , tt.duration_hours
     , ot.start_timestamp
     , start_timestamp 
            + INTERVAL (duration_hours DIV 7.5) DAY                                      -- whole days
            + INTERVAL 60 * (duration_hours - (duration_hours DIV 7.5)*7.5) MINUTE       -- remaining portion on final day
            as end_timestamp
     , . . .

Example 

mysql> select '2020-06-23 08:00:00' 
    ->     + INTERVAL (9 DIV 7.5) DAY 
    ->     + INTERVAL 60 * (9 - (9 DIV 7.5)*7.5) MINUTE
    -> as end_time;
+---------------------+
| end_time            |
+---------------------+
| 2020-06-24 09:30:00 |
+---------------------+

Now you have a planned start and end you can look for holiday periods (and weekends) that overlap this work period. Here there are two conditions:

  • holiday start <= start_timestamp
    • task delayed, start_timestamp becomes the holiday end date but actual duration remains the same
  • holiday start > start_timestamp
    • work is interrupted for the duration of the whole holiday so holiday duration is added to task actual duration

When you deviate from standard length days though, it becomes a whole lot more complex as each day's length and breaks (if applicable) need to be considered separately

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

ooook so your a mysql geneious 😄 jeeez, to be honest im gonna have to look at the manual with what your written here, but i just ran the query and the result is beautiful

i'll get onto implimenting annual holidays and bank holidays tomorrow, i'll let you know what it looks like, when its done.

 

thank you very much barand 😁😁😁

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted (edited)

hi Barand,

i've digested what it is you've done, but realised that the query would work of only that scenario, so ive had a little play for the last hour and this is what i've came up with

  

SELECT ot.task_id
     , tt.duration_hours
	 , ot.start_timestamp
     , @start_weekday := weekday(ot.start_timestamp)
	, @days_work := IF (@start_weekday = 0 AND monday.shift_hours > tt.duration_hours, 0, 
       IF (@start_weekday = 1 AND monday.shift_hours + tuesday.shift_hours > tt.duration_hours, 1, 
       IF (@start_weekday = 2 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours > tt.duration_hours, 2,
	   IF (@start_weekday = 3 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours > tt.duration_hours, 3, 
       IF (@start_weekday = 4 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours > tt.duration_hours, 4, 
       IF (@start_weekday = 5 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours > tt.duration_hours, 5, 
       IF (@start_weekday = 6  AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours + sunday.shift_hours > tt.duration_hours, 6, 'who knows'))))))) as `days_work`
	, @hours_remaining := IF (@start_weekday = 0 AND monday.shift_hours > tt.duration_hours, tt.duration_hours, 
       IF (@start_weekday = 1 AND monday.shift_hours + tuesday.shift_hours > tt.duration_hours, tt.duration_hours - monday.shift_hours, 
       IF (@start_weekday = 2 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours), 
	   IF (@start_weekday = 3 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours), 
       IF (@start_weekday = 4 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours), 
       IF (@start_weekday = 5 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours), 
       IF (@start_weekday = 6  AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours + sunday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours), 
       tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours + sunday.shift_hours)))))))) as `hours_remaining`
	, ot.start_timestamp + INTERVAL (@days_work) DAY + INTERVAL (60 * @hours_remaining) MINUTE as `end_timestamp`
     FROM tasks t
INNER JOIN operatives_tasks ot ON ot.task_id = t.task_id
INNER JOIN task_types tt ON tt.task_type_id = t.task_type_id
INNER JOIN users u on u.user_id = ot.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 0
		   ) monday ON monday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 1
		   ) tuesday ON tuesday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 2
		   ) wednesday ON wednesday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 3
) thursday ON thursday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 4
) friday ON friday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 5
) saturday ON saturday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 6
) sunday ON sunday.user_id = u.user_id
INNER JOIN operatives_annual_leave oal ON oal.user_id = u.user_id
WHERE u.user_id = 1

 

https://i.ibb.co/GJYG2Vq/Untitled3.png

 

so i've worked out how many days work and hours remaining and added it all to the start timestamp, but the big problem is, what if the task takes more than 7 days, i could go forever with these if statements.

if its not possible with mysql then i could work it all out via php

what are your thoughts please?

 

thank you

Edited by Destramic
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

For tasks like this, a really useful addition to any database is a date table (either permanent or temporary containing just the required range of dates 

CREATE TABLE `date` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For this I just created date records from June 15 to July 15. I also assume in the following query that no task will go over 7 days (change as required)

The purpose of the query is to give the task details and the day-by-day hours workable. From hereon I would recommend looping through the query results (in PHP) until the task is completed. I don't think the effort required to do it all in SQL is worth it. 

SELECT task_id
     , u.user_id
     , task_type
     , task_start
     , duration_hours
     , date
     , day_start
     , day_end
     , day_hours
     , hol_start
     , hol_end
FROM (
        SELECT ot.task_id
             , ot.user_id
             , tt.task_type
             , tt.duration_hours
             , ot.start_timestamp as task_start
             , ot.start_timestamp 
                    + INTERVAL (duration_hours DIV 7.5) DAY                                      -- whole days
                    + INTERVAL 60 * (duration_hours - (duration_hours DIV 7.5)*7.5) MINUTE       -- remaining portion on final day
                    as task_end
         FROM operatives_tasks ot
              JOIN tasks USING (task_id)
              JOIN task_types tt USING (task_type_id)
       ) task
       JOIN 
            users u ON task.user_id = u.user_id
        LEFT JOIN
            (
            SELECT date
                  , och.user_id
                  , och.start_time as day_start
                  , och.finish_time as day_end
                  , (timestampdiff(MINUTE, och.start_time, och.finish_time)
                            - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_hours
                  , oal.start_timestamp as hol_start
                  , oal.end_timestamp as hol_end
            FROM date
                 LEFT JOIN operative_contracted_hours och
                            ON weekday(date) = och.week_day
                 LEFT JOIN 
                      operatives_annual_leave oal
                            ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time)
                            AND oal.end_timestamp > CONCAT(date, ' ', och.start_time)
                            AND och.user_id = oal.user_id
            ) days
                    ON task.user_id = days.user_id
                    AND days.date BETWEEN date(task_start) AND date(task_start) + interval 7 day
ORDER BY task_id, date;

I added a second user with different working hours and a second task. 

+---------+---------+----------------+---------------------+----------------+------------+-----------+----------+-----------+---------------------+---------------------+
| task_id | user_id | task_type      | task_start          | duration_hours | date       | day_start | day_end  | day_hours | hol_start           | hol_end             |
+---------+---------+----------------+---------------------+----------------+------------+-----------+----------+-----------+---------------------+---------------------+
|       1 |       1 | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-23 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 |       1 | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-24 | 08:00:00  | 16:00:00 |    7.5000 | 2020-06-24 08:00:00 | 2020-06-24 10:00:00 |
|       1 |       1 | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-25 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 |       1 | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-26 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 |       1 | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-29 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 |       1 | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-30 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 |       2 | Boiler Install | 2020-06-24 08:00:00 |              9 | 2020-06-24 | 08:00:00  | 12:00:00 |    4.0000 | NULL                | NULL                |
|       2 |       2 | Boiler Install | 2020-06-24 08:00:00 |              9 | 2020-06-25 | 12:00:00  | 16:00:00 |    4.0000 | NULL                | NULL                |
|       2 |       2 | Boiler Install | 2020-06-24 08:00:00 |              9 | 2020-06-26 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 |       2 | Boiler Install | 2020-06-24 08:00:00 |              9 | 2020-06-29 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 |       2 | Boiler Install | 2020-06-24 08:00:00 |              9 | 2020-06-30 | 08:00:00  | 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 |       2 | Boiler Install | 2020-06-24 08:00:00 |              9 | 2020-07-01 | 08:00:00  | 12:00:00 |    4.0000 | NULL                | NULL                |
+---------+---------+----------------+---------------------+----------------+------------+-----------+----------+-----------+---------------------+---------------------+

 

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

i like that idea, i looked at the old code you helped me with which had the same principle 😁

  

$db->exec("CREATE TEMPORARY TABLE date_range (

              date DATE PRIMARY KEY

         )");

$stmt = $db->prepare("INSERT INTO date_range VALUES (?)");

foreach ($dp as $date) {

    $d = $date->format('Y-m-d');

    $stmt->execute([$d]);

}

 

regarding the query  i added a dates table and inserted a number of dates ands ran the query, thank you, i increased the task duration and increased the contracted working hours on a few days while testing and saw the query would only work a maximum of two days, so i had a little play around, and came up with this

  

set @days_work         = NULL;
set @minutes_remaining = NULL;

SELECT t.task_id
	  , times.day_working_hours
      , times.username
      , times.task_type
      , times.date
      , times.duration_hours
      , times.task_start_timestamp
      , times.task_end_timestamp
      , times.day_name
      , times.remaining_minutes
      , times.days_work
      , times.holiday_start_timestamp
      , times.holiday_end_timestamp
      FROM tasks t
LEFT JOIN (
        SELECT date
             , t.task_id
             , u.username
             , tt.task_type
             , tt.duration_hours
             , ot.start_timestamp as task_start_timestamp
             , @day_working_hours := (timestampdiff(MINUTE, och.start_time, och.finish_time) - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_working_hours
             , DAYOFWEEK(date) as day_name
             , oal.start_timestamp as holiday_start_timestamp
		     , oal.end_timestamp as holiday_end_timestamp 
             , @days_work := IF (@day_working_hours >  @minutes_remaining, IF (@days_work IS NULL, 1, @days_work + 1), NULL) as days_work
			 , @minutes_remaining := IF (@minutes_remaining IS NULL, ((tt.duration_hours * 60) - (@day_working_hours * 60)), @minutes_remaining - (@day_working_hours * 60)) as remaining_minutes
             , CONCAT(IF ((@day_working_hours * 60)> @minutes_remaining , DATE_ADD(date, INTERVAL @days_work DAY), ''), ' ', IF ((@day_working_hours * 60) > @minutes_remaining, DATE_ADD(och.start_time, INTERVAL @remaining_minutes MINUTE), '')) as task_end_timestamp 
		 FROM dates d
         LEFT JOIN  operative_contracted_hours och ON weekday(date) = och.week_day
		 JOIN operatives_tasks ot USING (user_id)
		 JOIN tasks t USING (task_id)
		 JOIN task_types tt USING (task_type_id)
		 JOIN users u USING (user_id)
         LEFT JOIN operatives_annual_leave oal ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time)
                                               AND oal.end_timestamp > CONCAT(date, ' ', och.start_time)
                                               AND och.user_id = oal.user_id  
       ) as times ON times.task_id = t.task_id
       ORDER BY task_id, date;

the results are here:

https://i.ibb.co/Zzn3C9z/Untitled4.png

what im trying to do is caluclate the number of days work it is for the task and the minutes remaining and add them to task start date, but the problem lies with days_work and hours_remaining not corresponding with the correct date and is all muddled up.

i feel if i can get the @days_work and @remaining_minutes in the right dates i can get a task end timestamp working for all different task durations

 

thank you for your help once again barand

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

Barand, can i please get some further advise, i've managed to get the end timestamp when the task is more than 2 days.

But i got 2 problems, other than cleaning up the query.

as seen in the picture, https://ibb.co/cbYN6BC

 

1. the date 24/06/2020 is the first date to subtract the remaining minutes and i don't know why, the first date subtracting minutes should be on the 23/06/2020 (total task time 1800 mins - 540 mins = 1260)

2. how do i end the query once i have a task end date? so it doesnt go through all the dates in the dates table?

 

if i can figure these two problems out then i think im on my way...i'll need to add the annual leave to the task time also, which isnt a issue.

  

set @days_work         = NULL;
set @minutes_remaining = NULL;

SELECT t.task_id
	  , times.day_working_hours
      , times.username
      , times.task_type
      , times.date
      , times.duration_hours
      , times.task_start_timestamp
      , times.task_end_timestamp
      , times.day_name
      , times.remaining_minutes
      , times.days_work
      , times.holiday_start_timestamp
      , times.holiday_end_timestamp
      , (times.duration_hours * 60)
      FROM tasks t
LEFT JOIN (
        SELECT date
             , t.task_id
             , u.username
             , tt.task_type
             , tt.duration_hours
             , ot.start_timestamp as task_start_timestamp
             , @day_working_hours := (timestampdiff(MINUTE, och.start_time, och.finish_time) - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_working_hours
             , DAYNAME(date) as day_name
             , oal.start_timestamp as holiday_start_timestamp
		     , oal.end_timestamp as holiday_end_timestamp 
             , @minutes_remaining := CASE WHEN @minutes_remaining IS NULL 
					THEN TRUNCATE((tt.duration_hours * 60) - (@day_working_hours * 60), 0)
					ELSE TRUNCATE(@minutes_remaining - (@day_working_hours * 60), 0)
			   END as remaining_minutes
			 , @days_work := CASE WHEN @days_work IS NULL AND (@day_working_hours * 60) < @minutes_remaining
                                  THEN 1
                                  WHEN @days_work IS NOT NULL AND (@day_working_hours * 60) < @minutes_remaining OR @days_work IS NOT NULL AND @minutes_remaining > 0
                                  THEN @days_work + 1
						     END as days_work
			 , CASE WHEN (@day_working_hours * 60) > @minutes_remaining AND @minutes_remaining > 0
					THEN CONCAT(DATE_ADD(date(ot.start_timestamp), INTERVAL @days_work DAY), ' ', DATE_ADD(och.start_time, INTERVAL @minutes_remaining MINUTE))
                    ELSE NULL
                    END as task_end_timestamp
		 FROM dates d
         LEFT JOIN  operative_contracted_hours och ON weekday(date) = och.week_day
		 JOIN operatives_tasks ot USING (user_id)
		 JOIN tasks t USING (task_id)
		 JOIN task_types tt USING (task_type_id)
		 JOIN users u USING (user_id)
         LEFT JOIN operatives_annual_leave oal ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time)
                                               AND oal.end_timestamp > CONCAT(date, ' ', och.start_time)
                                               AND och.user_id = oal.user_id  
                                               
                                               ORDER BY date
       ) as times ON times.task_id = t.task_id

 

hope you can help further, thank you so far

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

A problem with queries like that - there is no "exit" instruction.

As I said, better to get the necessary data with the query 

+---------+----------+----------------+---------------------+----------------+------------+---------------------+---------------------+-----------+---------------------+---------------------+
| task_id | username | task_type      | task_start          | duration_hours | date       | day_start           | day_end             | day_hours | hol_start           | hol_end             |
+---------+----------+----------------+---------------------+----------------+------------+---------------------+---------------------+-----------+---------------------+---------------------+
|       1 | User 1   | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-23 | 2020-06-23 08:00:00 | 2020-06-23 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 | User 1   | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-24 | 2020-06-24 08:00:00 | 2020-06-24 16:00:00 |    7.5000 | 2020-06-24 08:00:00 | 2020-06-24 10:00:00 |
|       1 | User 1   | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-25 | 2020-06-25 08:00:00 | 2020-06-25 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 | User 1   | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-26 | 2020-06-26 08:00:00 | 2020-06-26 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 | User 1   | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-29 | 2020-06-29 08:00:00 | 2020-06-29 16:00:00 |    7.5000 | NULL                | NULL                |
|       1 | User 1   | Boiler Install | 2020-06-23 08:00:00 |              9 | 2020-06-30 | 2020-06-30 08:00:00 | 2020-06-30 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 | User 2   | Piping         | 2020-06-24 08:00:00 |              8 | 2020-06-24 | 2020-06-24 08:00:00 | 2020-06-24 12:00:00 |    4.0000 | NULL                | NULL                |
|       2 | User 2   | Piping         | 2020-06-24 08:00:00 |              8 | 2020-06-25 | 2020-06-25 12:00:00 | 2020-06-25 16:30:00 |    4.0000 | NULL                | NULL                |
|       2 | User 2   | Piping         | 2020-06-24 08:00:00 |              8 | 2020-06-26 | 2020-06-26 08:00:00 | 2020-06-26 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 | User 2   | Piping         | 2020-06-24 08:00:00 |              8 | 2020-06-29 | 2020-06-29 08:00:00 | 2020-06-29 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 | User 2   | Piping         | 2020-06-24 08:00:00 |              8 | 2020-06-30 | 2020-06-30 08:00:00 | 2020-06-30 16:00:00 |    7.5000 | NULL                | NULL                |
|       2 | User 2   | Piping         | 2020-06-24 08:00:00 |              8 | 2020-07-01 | 2020-07-01 08:00:00 | 2020-07-01 12:00:00 |    4.0000 | NULL                | NULL                |
+---------+----------+----------------+---------------------+----------------+------------+---------------------+---------------------+-----------+---------------------+---------------------+

the process the results to get the output, EG

image.thumb.png.a3c20e77c3b5fac66cbb4236ef1cf23e.png 

<?php
include 'db_inc.php';
$db = pdoConnect('neo');

const DT_FORMAT = 'j M g:i a';
const HRS_PER_DAY = 10;
const PIX_PER_HR = 12;
const PIX_PER_ROW = 30;
const HEAD_HT = 50;

$wk_commence_date = $_GET['wkcomm'] ?? '2020-06-22';

$res = $db->prepare("SELECT task_id
                         , CONCAT('User ', u.user_id) as username
                         , task_type
                         , task_start
                         , duration_hours
                         , date
                         , CONCAT(date, ' ', day_start) as day_start
                         , CONCAT(date, ' ', day_end) as day_end
                         , day_hours
                         , hol_start
                         , hol_end
                    FROM    (
                            SELECT ot.task_id
                                 , ot.user_id
                                 , tt.task_type
                                 , tt.duration_hours
                                 , ot.start_timestamp as task_start
                                 , ot.start_timestamp 
                                        + INTERVAL (duration_hours DIV 7.5) DAY                                      -- whole days
                                        + INTERVAL 60 * (duration_hours - (duration_hours DIV 7.5)*7.5) MINUTE       -- remaining portion on final day
                                        as task_end
                            FROM operatives_tasks ot
                                 JOIN tasks USING (task_id)
                                 JOIN task_types tt USING (task_type_id)
                            WHERE date(ot.start_timestamp) BETWEEN ? AND ? + INTERVAL 4 DAY
                            ) task
                           JOIN 
                                users u ON task.user_id = u.user_id
                           LEFT JOIN
                                (
                                SELECT date
                                      , och.user_id
                                      , och.start_time as day_start
                                      , och.finish_time as day_end
                                      , (timestampdiff(MINUTE, och.start_time, och.finish_time)
                                                - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_hours
                                      , oal.start_timestamp as hol_start
                                      , oal.end_timestamp as hol_end
                                FROM date
                                     LEFT JOIN operative_contracted_hours och
                                                ON weekday(date) = och.week_day
                                     LEFT JOIN operatives_annual_leave oal
                                                ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time)
                                                AND oal.end_timestamp > CONCAT(date, ' ', och.start_time)
                                                AND och.user_id = oal.user_id
                                ) days
                                        ON task.user_id = days.user_id
                                        AND days.date BETWEEN date(task_start) AND date(task_start) + interval 7 day
                    
                    ORDER BY username, task_id, task_start, date
                    ");
$res->execute([ $wk_commence_date, $wk_commence_date ]);

$data = [];
foreach ($res as $r) {
    if (!isset($data[$r['username']][$r['task_id']])) {
        $data[$r['username']][$r['task_id']] = [ 'type' => $r['task_type'],
                                                 'start' => $r['task_start'],
                                                 'end'   => '',
                                                 'hours' => $r['duration_hours'],
                                                 'days' => []
                                                 ];
    }
    $data[$r['username']][$r['task_id']]['days'][] = [ 'date' => $r['date'],
                                                       'dstart' => $r['day_start'],
                                                       'dend' => $r['day_end'],
                                                       'dhours'  => $r['day_hours'],
                                                       'hstart' => $r['hol_start'],
                                                       'hend' => $r['hol_end']
                                                       ];
}

function processUser($tasks, $wk_commence_date)
{
    $ktasks = count($tasks);
    $chartht = $ktasks * PIX_PER_ROW + HEAD_HT;
    $chartwid = 500 + 5 * HRS_PER_DAY * PIX_PER_HR;
    $id_x = 5;
    $type_x = 
    $chart = "<svg width='$chartwid' height='$chartht'>
              <style type='text/css'>
                  .work {fill: #2DABE1; stroke-width: 0}
                  .leave {fill: #C38E31; stroke-width: 0}
                  .nonwork {fill: #CCCCCC; stroke-width: 0}
                  .bkgrd {fill: #000; stroke: #EEE; stroke-width: 1}
                  .grid {fill:transparent; stroke: #FFF; stroke-width: 1}
                  .gridh {fill:transparent; stroke: #666; stroke-width: 1}
                  .txt {fill: #FFF; font-size: 8pt; font-family: calibri, sans serif;}
              </style>
              <rect x='0' y='0' width='$chartwid' height='$chartht' class='bkgrd' />
              <path d='M 500 25 h $chartwid' class='grid' />
              <path d='M 0 50 h $chartwid' class='grid' />
              \n";
              $chart .= "<text x='5' y = '41' class='txt'>Task</text>\n";
              $chart .= "<text x='300' y = '41' class='txt'>Start</text>\n";
              $chart .= "<text x='400' y = '41' class='txt'>Finish</text>\n";
              // times and date headings
              $dt = new DateTime($wk_commence_date);
              $dp = new DatePeriod($dt, new DateInterval('P1D'), 5);
              $x = 500 + HRS_PER_DAY * PIX_PER_HR / 2;
              $txt_y = HEAD_HT/2 - 9;
              foreach ($dp as $d) {
                  $chart .= "<text x='$x' y='$txt_y' class='txt' text-anchor='middle'>" . $d->format('j M Y') . "</text>\n";
                  $x += HRS_PER_DAY * PIX_PER_HR;
              }
              for ($x=500; $x<$chartwid; $x += PIX_PER_HR) {
                  $chart .= "<path d='M $x 45 v $chartht' class='gridh' />\n";
              }
              for ($x=500; $x<$chartwid; $x += HRS_PER_DAY * PIX_PER_HR) {
                  $chart .= "<path d='M $x 0 v $chartht' class='grid' />\n";
              }

              $x = 500+PIX_PER_HR;
              $txt_y = HEAD_HT - 9; 
              for ($d=0; $d<5; $d++) {
                  $x1 = $x;
                  foreach ([9,12,15] as $h) {
                      $chart .= "<text x='$x1' y='$txt_y' class='txt' text-anchor='middle'>$h</text>\n";
                      $x1 += 3 * PIX_PER_HR;
                  }
                  $x += HRS_PER_DAY * PIX_PER_HR;
              }
              
              $dt = new DateTime($wk_commence_date);
              $dp = new DatePeriod($dt, new DateInterval('P1D'), 5);
              $x = 500 + HRS_PER_DAY * PIX_PER_HR / 2;
              $txt_y = HEAD_HT/2 - 9;
              foreach ($dp as $d) {
                  $chart .= "<text x='$x' y='$txt_y' class='txt' text-anchor='middle'>" . $d->format('j M Y') . "</text>\n";
                  $x += HRS_PER_DAY * PIX_PER_HR;
              }
              for ($x=500; $x<$chartwid; $x += PIX_PER_HR) {
                  $chart .= "<path d='M $x 45 v $chartht' class='gridh' />\n";
              }
              for ($x=500; $x<$chartwid; $x += HRS_PER_DAY * PIX_PER_HR) {
                  $chart .= "<path d='M $x 0 v $chartht' class='grid' />\n";
              }
              
              // now do the tasks
              $tnum = 0;
              foreach ($tasks as $tid => $tdata) {
                  $chart .= processTask($tnum, $tid, $tdata, $wk_commence_date);
              }

    $chart .= "</svg>\n";
    return $chart;
}

function processTask($tnum, $tid, $tdata, $wk_commence_date)
{
    $vOrigin = HEAD_HT + $tnum * PIX_PER_ROW;
    $txt_y = $vOrigin + PIX_PER_ROW - 9;
    $sdt = new DateTime($tdata['start']);
    $start = $sdt->format(DT_FORMAT);
    $calculated_end = '';
    $output = "<text x='5' y='$txt_y' class='txt'>$tid</text>
               <text x='35' y='$txt_y' class='txt'>{$tdata['type']}</text>
               <text x='300' y='$txt_y' class='txt'>$start</text>
              \n";
              
    // process each day until task completion is reached
    foreach ($tdata['days'] as $dayno => $d) {
      $output .= processDay($wk_commence_date, $vOrigin, $tdata, $d);
    } 
    return $output;
}

function processDay($wk_commence_date, $vOrigin, &$tdata, $d)
{
    if ($d['dend'] < $tdata['start']) return '';
    if ($tdata['hours'] <= 0) return;
    $chart_day_start = new DateTime($d['date'] . ' 08:00:00');
    $chart_day_end = new DateTime($d['date'] . ' 18:00:00');
    $day_start = new DateTime($d['dstart']);
    $day_end = new DateTime($d['dend']);
    $output = '';
    $top = $vOrigin + 5;
    $rHt = PIX_PER_ROW - 10;
    // non-work
//    if ($day_start > $chart_day_start) {
//        $x1 = hOffset($chart_day_start, $wk_commence_date);
//        $wid = hOffset($day_start, $wk_commence_date) - $x1;
//        $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n";
//    }
//    if ($day_end < $chart_day_end) {
//        $x1 = hOffset($day_end, $wk_commence_date);
//        $wid = hOffset($chart_day_end, $wk_commence_date) - $x1;
//        $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n";
//    }
        $x1 = hOffset($chart_day_start, $wk_commence_date);
        $wid = hOffset($chart_day_end, $wk_commence_date) - $x1;
        $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n";
    
    // holidays
    if ($d['hstart']) {
        $hol_start = new DateTime($d['hstart']);
        $hol_end = new DateTime($d['hend']);
        $dth1 = max($hol_start, $chart_day_start);
        $dth2 = min($hol_end, $chart_day_end);
        $x1 = hOffset($dth1, $wk_commence_date);
        $wid = hOffset($dth2, $wk_commence_date) - $x1;
        $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='leave' />\n";
        if ($hol_start <= $day_start) {
            $day_start = $hol_end;
        } else $day_end = $hol_start;
        $d['hours'] = ($day_end->getTimestamp() - $day_start->getTimestamp())/3600;
    }

    if ($tdata['hours'] <= $d['dhours']) {
        $day_end = clone $day_start;
        $mins = $tdata['hours'] * 60;
        $day_end->add(new DateInterval("PT{$mins}M"));
        $tdata['hours'] = 0;
    }
    else {
        $tdata['hours'] -= $d['dhours'];
    }

    $x1 = hOffset($day_start, $wk_commence_date);
    $wid = hOffset($day_end, $wk_commence_date) - $x1;
    $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='work' />\n";
    
    if ($d['dhours'] > 4.5) {
        $break_start = clone $day_start;
        $break_start->add(new DateInterval('PT270M'));
        $x1 = hOffset($break_start, $wk_commence_date);
        $wid = PIX_PER_HR / 2;
        $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n";
    }
    if ($tdata['hours'] == 0) {
        $txt_y = $vOrigin + 21;
        $output .= "<text x='400' y='$txt_y' class='txt'>" . $day_end->format(DT_FORMAT) . "</text>\n";
    }
    return $output;
}

function hOffset (DateTime $time, $wk_commence_date)
{
    $dt1 = new DateTime($wk_commence_date);
    $dt2 = clone $time;
    $day_offset = $dt1->diff($time)->d * HRS_PER_DAY * PIX_PER_HR + 500;
    $dt2->setTime(8,0,0);
    $hour_offset = ($time->getTimestamp() - $dt2->getTimestamp())/3600 * PIX_PER_HR;
    return $day_offset + $hour_offset;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="creation-date" content="05/10/2019">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<title>Schedule</title>
<style type="text/css">
    body     { font-family: verdana,sans-serif; font-size: 10pt; padding: 20px 50px; }
</style>
</head>
<body>
    <header class="w3-container w3-blue-gray w3-center">
        <h1>NEO Schedule</h1>
    </header>

    <div class="w3-container w3-left">
        <?php
            foreach ($data as $username => $tasks) {
                echo "<h3>$username</h3>\n";
                echo processUser($tasks, $wk_commence_date);
            }
        ?>
    </div>
</body>
</html>

I just noticed it's not quite right yet - the second one should finish at 8:30, not 9am. But it's well on the way :)

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

baramd this is beyond help, wow, thank you indeed.

such a shame theres no exit statergy with the query, well its late now, so when im back on tomorrow i'll run you code and have a good tinker and get back to you.

once again thank you, you didnt need to go to such efforts, but its greatly appreciated 😊

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.