Jump to content

Destramic

Members
  • Content Count

    954
  • Joined

  • Last visited

Posts posted by Destramic

  1. 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 😊

     

  2. 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

  3. 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

  4. 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

  5. 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 😁😁😁

  6. 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

  7.        @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]}

  8. 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

  9. 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

  10. i've been playing around with nodejs sodium, and im trying to make my js scipt compatable with my php script.  Both are using xchacha20poly1305m but the problem is

    my php encryption looks like this: (whatever it is)

    ��Bd���||�%��AG�wU�Q��[�V���ȷ&6����_�Y:�q��T��X��e"v�

     

    and my js encrption look like that:

    Uint8Array {0: 195, 1: 119, 2: 234, 3: 247, 4: 236…}

     

    theres no way of testing the compatability of both scripts when both encodings differ.

     

    here is my php

    <?php
    
    CONST NONCE_LENGTH = SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES;
    
    class Sodium
    {
    	private $key;
    	
    	public function __construct(string $key)
    	{
    		if (!extension_loaded('sodium'))
    		{
    		    throw new Exception('Encryption: PHP Sodium extension not found.');
    		}
    		
    	    $key = trim($key);
    
            if (!preg_match('/^[0-9A-Fa-f]{64}+$/', $key))
            {
                throw new Exception('Encryption: Unrecognized key.');
            }
    		
    		$this->key = hex2bin($key);
    	}
    	
    	public function encrypt(string $plaintext)
    	{
    		$nonce = random_bytes(NONCE_LENGTH);
    		
    		$ciphertext = sodium_crypto_aead_xchacha20poly1305_ietf_encrypt(
    		    $plaintext,
    		    null,
    		    $nonce,
    		    $this->key
    		);
    		
    		return $nonce . $ciphertext;
    	}
    	
    	public function decrypt(string $encryption)
    	{
    		if (strlen($encryption) < 24)
    	    {
    	        throw new Exception('Encryption: Unrecognized Encryption.');
    	    }
    	   	    
    		$plaintext = sodium_crypto_aead_xchacha20poly1305_ietf_decrypt(
    		    substr($encryption, 24),
    		    null,
    		    substr($encryption, 0, 24),
    		    $this->key
    		);
    
    		if ($plaintext === false)
    		{
    		    throw new Exception('Encryption: Decryption Failed.');
    		}
    		
    		return $plaintext;
    	}
    }
    
    $sodium = new Sodium('724b092810ec86d7e35c9d067702b31ef90bc43a7b598626749914d6a3e033ed');
    $encryption = $sodium->encrypt('shhh this is a secret');
    echo $encryption;
    echo $sodium->decrypt($encryption);

     

    and you can view the js live here https://codesandbox.io/s/l45orjlnk7

    note: when viewing page you may have to delete a character of the code and replace to see results ?

    or theres a screenshot https://ibb.co/XkcPV4B

     

    i hope you can help with my dilemma

     

    thank you

     

  11. the first select fetches properties from my properties table by thier gas expiry date, ie gas service, which is listed as a pre-task (no property_task entry). when this task is assigned to a trade it will have a property_task_id. so im selecting tasks which exists and dont yet exist for a gas service.

    and the second select will select anything else for that particular deparment. eg. repair on boiler, fire etc.

    it pull all the rows together, giving me the full list of task/pre-tasks for a selected department.

     

  12. thank kicken for seeing my errors, what on earth was i donig with the regex! (head shaking)

    ive made the changes to my query now which gets the users assigned department and task type id's and compares it with the the ids from the appointments

     

    SELECT SUM(task_appointment.count) AS `count`
    FROM ((SELECT COUNT(pt.property_task_id) AS `count`,
    				     p.gas_service_expiry_date AS `priority_date`,
                         d.department_id,
    		             tt.task_type_id,
                         pt.status
                         FROM property_tasks pt
    				     INNER JOIN properties p ON p.property_id = pt.property_id
                         INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
                         INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
                         INNER JOIN trades t ON t.trade_id = ttt.trade_id
                         INNER JOIN departments d ON d.department_id = pt.department_id
                         INNER JOIN property_appointments pa ON pa.property_task_id = pt.property_task_id
                         WHERE tt.task_type = "Gas Service" 
                         AND d.department = "Gas Servicing"
                         AND IF(pa.completed_timestamp, pa.completed_timestamp, pa.end_date) < NOW()
    			)
    UNION ALL
    (SELECT COUNT(pt.property_task_id) AS `count`,
            DATE_FORMAT(DATE_ADD(pt.created_timestamp, INTERVAL pt.priority_days DAY), "%Y-%m-%d") AS `priority_date`,
            d.department_id,
    		tt.task_type_id,
            pt.status 
            FROM property_tasks pt
            INNER JOIN properties p ON p.property_id = pt.property_id
    		INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
            INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
            INNER JOIN trades t ON t.trade_id = ttt.trade_id
            INNER JOIN departments d ON d.department_id = pt.department_id
    		INNER JOIN property_appointments pa ON pa.property_task_id != pt.property_task_id
            WHERE tt.task_type != "Gas Service" 
            AND d.department != "Gas Servicing"
    )) task_appointment
    INNER JOIN (SELECT u.user_id,
                       GROUP_CONCAT(d.department_id) AS `department_ids`,
                       GROUP_CONCAT(tt.task_type_id) AS `task_type_ids`
    		    FROM users u
    			INNER JOIN trade_departments td ON td.user_id = u.user_id
                INNER JOIN departments d ON d.department_id = td.department_id
                INNER JOIN user_trades ut ON ut.user_id = u.user_id
                INNER JOIN trades t ON t.trade_id = ut.trade_id
                INNER JOIN trade_task_types ttt ON ttt.trade_id = t.trade_id
                INNER JOIN task_types tt ON tt.task_type_id = ttt.task_type_id
    ) user
    WHERE status = "Active"
    AND task_appointment.department_id IN (user.department_ids)
    AND task_appointment.task_type_id IN (user.task_type_ids)
    AND user.user_id = 1
    ORDER BY task_appointment.priority_date ASC

     

    it comes back with the correct count which is good...any final thouhts?

     

    thank you again

  13. maybe i should post in the mysql section but i really feel this is a regex issue.

    my query is just to count unallocated/uncompleted tasks and appointments

    here is the query

    SELECT SUM(task_appointment.count) AS `count`
           FROM ((SELECT COUNT(pt.property_task_id) AS `count`,
    				     p.gas_service_expiry_date AS `priority_date`,
                         d.department,
                         GROUP_CONCAT(t.trade) AS `trades`,
                         pt.status
                         FROM property_tasks pt
    				     INNER JOIN properties p ON p.property_id = pt.property_id
                         INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
                         INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
                         INNER JOIN trades t ON t.trade_id = ttt.trade_id
                         INNER JOIN departments d ON d.department_id = pt.department_id
                         INNER JOIN property_appointments pa ON pa.property_task_id = pt.property_task_id
                         AND tt.task_type = "Gas Service" 
                         AND d.department = "Gas Servicing"
                         AND IF(psa.completed_timestamp, psa.completed_timestamp, psa.end_date) < NOW()
    )
    UNION ALL
    (SELECT COUNT(pt.property_task_id) AS `count`,
            DATE_FORMAT(DATE_ADD(pt.created_timestamp, INTERVAL pt.priority_days DAY), "%Y-%m-%d") AS `priority_date`,
            d.department,
    		GROUP_CONCAT(t.trade) AS `trades`,
            pt.status
            FROM property_tasks pt
            INNER JOIN properties p ON p.property_id = pt.property_id
    		INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
            INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
            INNER JOIN trades t ON t.trade_id = ttt.trade_id
            INNER JOIN departments d ON d.department_id = pt.department_id
    		INNER JOIN property_appointments pa ON pa.property_task_id != pt.property_task_id
            AND tt.task_type != "Gas Service" 
            AND d.department != "Gas Servicing"
    )) task_appointment
    INNER JOIN (SELECT GROUP_CONCAT(d.department) AS `departments`,
                       GROUP_CONCAT(t.trade) AS `trades`
    		    FROM users u
    			INNER JOIN trade_departments td ON td.department_id = u.user_id
                INNER JOIN departments d ON d.department_id = td.user_id
                INNER JOIN user_trades ut ON ut.user_id = u.user_id
                INNER JOIN trades t ON t.trade_id = ut.trade_id
    		    WHERE u.user_id = 1
    ) user
    WHERE user.departments REGEXP REPLACE(task_appointment.department, ',', '|') > 0
    AND user.trades REGEXP REPLACE(task_appointment.trades, ',', '|') > 0
    AND status = "Active"
    ORDER BY task_appointment.priority_date ASC

     

    note the unions are almost simular but draw different rows

    1st being gas servicing under certian conditions and 2nd anything else

     

    thank you

  14. i have two CONCAT_GROUPS in my sql query, 1 being the users trades and the other being the trades required for the a particular task.

    What im tyring to do is to find if the user has the correct trade for a particular task.

    here is what ive got so far which does work, but doesnt match the words as i want, for instance if @user_trades = REPLACE('plumb,electrician', ',', '|'), it will still match for plumb

    SELECT @user_trades := REPLACE('plumber,electrician', ',', '|'),   // regex
           @task_trades := 'painter,plumber',   // string
    @task_trades REGEXP @user_trades

    how can i make the regex match a trade 100% or nothing

     

    thank you

  15. i can pass the relevant ID's over infact, i don't know what i didnt do that instead of complicating things!

    the simpliest ways are always the best. :suicide:

    thank you for the insight on how i could of done the query also.

     

  16. im unable to find an example when it comes to select and insert in my particular case, so any help would be greatly appreciated.

    here is my 2 select queries which retrieve the department_id and task_type_id.  what i want to do it is use those two values inside an insert as well as adding additional values.

     

    SELECT department_id FROM departments WHERE department = "Gas Servicing"
    SELECT task_type_id FROM task_Types WHERE task_type = "Gas Service"
    
    INSERT INTO property_appointments (property_id, department_id, task_type_id, notes, priority_days) 
                               VALUES (:property_id, ?, ?, :notes, 1)

     

    is this possible please?

     

    thank you

  17. Beautiful stuff barand, although I've dropped the task statuses and added status, start time stamp and end timestamp to my tasks tablet and did it that way.  

    Ps. I've still used what you've done for me previosuly but adding this will alter task times depending on task start and end, making things more accurate.

    Thank you again for your efforts

  18. i have no idea how to do this in all honesty, hence my post.

    i have a table called task_statuses, which records the statuses and times of a given task,  and those tasks are allocated to an engineer for the day.

    before the engineer is at the task he/she will hit a button called Notify, which will send an sms and email to notify an engineer is on the way....when at the tasks the engineer has 3 further statuses to choose from depending on the outcome of task No Access, Completed or Rearranged.

     

    task statuses
    -------------------------------------------------------------                                                  
                                                           
    Uncommenced                        default (no task status)                                                      
    Notified                           start                                                      
    No Access|Completed|Rearranged     end
    
    -------------------------------------------------------------

    If the engineer has no access at 8am, then there would be 2 statues added.

    1. Notifiied

    2. No Access

     

    now the engineer may try again at 11am.  so before arriving the engineer will click a button called retry which will add Notified to the tasks_statuses and then is able to choose the 2nd outcome of the task

    3. Notifed (Back to square one)

    4. 2nd Outcome (Completed?)

     

    what i want to do is caluclate the complete time spent on the task.

     

    Here is a typical example:

    tt.png.55f03b1f6a5dbe009f59c6c3711e357a.png

    1st attempt: 60 mins

    2nd attempt: 28 mins.

    Total task time : 88 mins

     

    How is this possible please?

    Thank you

     

  19. I've been doing things wrong for years...I've had one model class per controller with multiple methods. Ie. Get tasks, get task etc

    I really do like the structure I've been shown here and I'm excited to make the changes.  Also I will check that link out ignace.

    Thank you all for your help and patience as It means a lot to me to know the correct way of doing things.

     

    ?

     

  20. here is what ive come up with:

     

    <?php
    
    class Validator
    {
        private $entities;
        private $fields;
    
        private $error_messages = array();
    
        public function __construct($entities, array $fields = array())
        {
            if (!is_array($entities))
            {
                $entities = array($entities);
            }
    
            foreach ($entities as $entity)
            {
                $this->check_entity($entity);
            }
    
            $this->entities = $entities;
            $this->fields   = $fields;
        }
    
        private function check_entity($entity)
        {
            if (!is_object($entity))
            {
                throw new Exception('Validator: Entity must be an object.');
            }
    
            return true;
        }
    
        public function validate(array $data, $strict = false)
        {
            foreach ($this->entities as $entity)
            {
                foreach ($data as $property => $value)
                {
                    if (!empty($this->fields) && !in_array($property, $this->fields))
                    {
                        throw new Exception(sprintf('Validator: Unknown field %s', $property));
                    }
    
                    if (!method_exists($entity, $property))
                    {
                        throw new Exception(sprintf('Validator: Unknown method %s', $property));
                    }
    
                    try
                    {
                        $result = $entity->$property($value);
                    }
                    catch (Exception $exception)
                    {
                        if ($strict)
                        {
                            throw new Exception($exception->getMessage());
                        }
                        else
                        {
                            $error_message = ucwords($property) . ': ' . $exception->getMessage();
    
                            $this->error_messages[$property] = $error_message;
                        }
                    }
                }
            }
        }
    
        public function get_error_messages()
        {
            return $this->error_messages;
        }
    
        public function is_valid()
        {
            return empty($this->error_messages);
        }
    }
    
    class Assert
    {
        public function __call(string $constraint, $arguments)
        {
            $class = $constraint;
    
            if (!class_exists($class))
            {
                throw new Exception(sprintf('Assert: Constraint %s doesn\'t exist.', $constraint));
            }
            else if (!method_exists($class, 'assert'))
            {
                throw new Exception(sprintf('Assert: Method assert doesn\'t exist for %s.', $constraint));
            }
    
            return call_user_func_array(array(new $class, 'assert'), $arguments);
        }
    }
    
    class Not_Blank
    {
        public function assert($value)
        {
            if (empty($value))
            {
                throw new Exception('Value is empty');
            }
    
            return true;
        }
    }
    
    class Login extends Assert
    {
        public function username($username)
        {
            $this->not_blank($username);
        }
    
        public function password($password)
        {
            $this->not_blank($password);
        }
    }

     

    form validation:  i added fields to be inputted to make validation stricter, becasue if post data is manipulated and there is no username or post in the data then the validator would return true.

    $validator = new Validator(array(new Login));
    
    $post = array(
        'username' => null,
        'password' => null
    );
    
    // fields added to be validated
    $validator->validate($post, array(
        'username',
        'password'
    ));
    
    if (!$validator->is_valid())
    {
        print_r($validator->get_error_messages());
    }

     

    business model data validation...this is where im able to put validation to strict and it will return exceptions instead of getting friendly error messages

    $validator = new Validator(array(new Login));
    
    // true = exceptions will be returned
    $validator->validate($data, true);

     

    if you could tell me what you think and please elaborate on how form validation and business model validation should work

     

    thank you

  21. im trying to digest everything your saying here...so...

    ok validating a form and returning error messages for a user is great...but for instance, if form data is passed to my business model after being validated would i need to validate the data again in themodel, but this time returning exceptions?

     

     

     

  22. i like the idea of loading a controller view inside of the view it's self. so here it is...

     

    view method 

       public function load($controller, $variables = array())
        {
            $dispatcher = new Dispatcher($this->get_request(), $this->get_response());
    
            if (!$dispatcher->is_dispatchable($controller))
            {
                throw new Exception(sprintf('View: Unable to dispatch %s.', $controller));
            }
            
            if (!empty($variables))
            {
                self::$variables = array_merge(self::$variables, $this->escape_values($variables));
            }
            
            return $dispatcher->dispatch()->get_view()->get_contents();
        }

     

    then in my pages

    echo $this->load('template:common:header', array(
    	'heading'             => 'Task',
        'heading_description' => 'This is a quick view of your current task'
    ));

     

    thank you both for your help :)

  23. Quote

    This comes close to your desired results, although our arithmetic differs

    i just ran the query and seen the figures.  I assumed it was my error and not your query ?

    but it works brillantly...can't thank you enough for your efforts barand.

    thank you!

     

    1 hour ago, Barand said:

    because there is never no empty date for a two week period 

    and i hope not :)

×
×
  • 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.