Jump to content


  • Content Count

  • Joined

  • Last visited

Everything 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 thes
  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
  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 + wedne
  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. no, a user can have different work patterns, for instance a user could work monday, wednesday, friday from 8-1 each day. 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 ho
  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 (@br
  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`,
  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 durati
  10. i will give it ago...thank you for you post kicken ?
  11. 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; c
  12. 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.
  13. 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.pro
  14. 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
  15. 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 mak
  16. 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. thank you for the insight on how i could of done the query also.
  17. 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
  18. 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
  19. ok brillant, i'll do it that way, thank you for your help again barand
  20. 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 -------------------------------------------------------------
  21. 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. ?
  22. 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))
  23. 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?
  24. 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));
  25. 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! 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.