2. PHPRunner General questions
  3. Call catagories against Responce Times?
This topic is locked

Call catagories against Responce Times?

 		1/24/2011 6:42:12 AM
PHPRunner General questions
S
 swanside author

Hi.
We have just been told that a new client of ours want to place a job request with a call catagory of anything between 1 and 5.
1 = 2 Hour responce

2 = 4 Hour responce

3 = 8 Hour responce

4 = 16 Hour responce

5 = 24 Hour responce

Problem I have is the times and responce are only valid for a working day between 08:00 and 16:30
So, If we get a call at 15:30 and they say it is a Catagory 3 which is an 8 hour response, we need to attend the job before 15:00 the next day.

How can I get the system to not count the hours that are not classed as normal working hours or the time between 16:30 and 08:00 the next day?
Thanks

Paul.
S
 		swanside author 1/24/2011

A Bit more Info.

I have these fields in the job table

CREATE TABLE IF NOT EXISTS `job` (

`Order_Date` datetime DEFAULT NULL,

`File_No` varchar(50) DEFAULT NULL,

`Job_No` int(50) NOT NULL AUTO_INCREMENT,

`Order_Time` varchar(50) DEFAULT NULL,

`Date_Added` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`ResponseCatagories` varchar(10) DEFAULT NULL,

`Period` varchar(50) DEFAULT NULL,

`Hours` time DEFAULT NULL,

PRIMARY KEY (`Job_No`),

UNIQUE KEY `File_No` (`File_No`)



And these in a Responcetimes table

CREATE TABLE IF NOT EXISTS `responcetimes` (

`Code` int(11) NOT NULL AUTO_INCREMENT,

`Period` varchar(10) NOT NULL,

`Hours` time NOT NULL,

PRIMARY KEY (`Code`)


In PHPRunner I have this

SELECT Order_Date,

File_No,

Job_No,

Order_Time,

Date_Added,

ResponseCatagories,

Period,

Hours,

(SELECT TIME_TO_SEC(Hours)) AS Seconds,

(SELECT TIMESTAMPADD(SECOND,Seconds,Date_Added)) AS Target

FROM job


Now, When I add into the database I get this

<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>

- <table>

- <row>

<OrderDate>2011-01-24 00:00:00</OrderDate>

<FileNo />

<JobNo>1027307</JobNo>

<OrderTime>14:13:29</OrderTime>

<DateAdded>2011-01-24 14:13:36</DateAdded>

<ResponseCatagories>4</ResponseCatagories>

<Period>2 Days</Period>

<Hours>16:00:00</Hours>

<Seconds>57600</Seconds>

<Target>2011-01-25 06:13:36</Target>

</row>

</table>


Now here we can see the target time is 06:13:36 as it has added 16 hours onto the Date Added field, but I need it to ignore the times between 08:00 and 16:30 Monday to Friday and all day Saturday and Sunday?
Any ideas please?

Thanks

Paul.
Sergey Kornilov admin 1/24/2011

You need to write a function that is able to add time counting business hours only.
Check this for starters:

http://efreedom.com/Question/1-2153500/Add-Working-Hours-Timestamp
S
 		swanside author 1/24/2011



You need to write a function that is able to add time counting business hours only.
Check this for starters:

http://efreedom.com/Question/1-2153500/Add-Working-Hours-Timestamp

OK, I can see how that would operate, but, Where would I put that?

Thanks

Paul
Sergey Kornilov admin 1/24/2011

Since this calculation needs to be performed in PHP code you can set 'View as' type of Target field to 'Custom' and use this code there.
Remove calculation from the SQL query as it won't work there.