Query Enties Start/Ent Time

[jberanek]

Hello,

I'm creating a custom php page to query only today's approve entries in all areas and rooms as a table (needed by our users) I have created the page with needed data but the problem is with the start_time and end_time format to be displayed as a regulrar date & time format not UNOX timestamp. I have tried many options but none worked. How can this be done? or is there another way to view today's approved entries without the calendar layout? Thanks.

Reported by: mabdelhady

Original Ticket: mrbs/support-requests/1427

[jberanek]

You should use PHP's strftime() function to convert a UNIX timestamp into a local date and time.

Original comment by: campbell-m

[jberanek]

The other thing you could do is use the Report page to get a list of today's approved entries.

Original comment by: campbell-m

[jberanek]

Thanks Campbell but strftime() always returns "01 Jan 1970 01:00:00"

Original comment by: mabdelhady

[jberanek]

Check the value of the timestamp that you are passing to strftime(). It sounds like you are passing it 0.

Original comment by: campbell-m

[jberanek]

Sorry I'm not sure I understand. This is waht I'm using to display end time for example:

echo date('d-M-Y H:i:s', strtotime($row_approved['end_time']);

Original comment by: mabdelhady

[jberanek]

You are using strtotime not strftime

Original comment by: *anonymous

[jberanek]

The issue with using the report is that we need to display all the entry details plus the user who will be viewing this page doesn't wish to select today from date picker everytime they look at the page. Thanks

Original comment by: mabdelhady

[jberanek]

strftime also returned "01 Jan 1970 01:00:00"

Original comment by: mabdelhady

[jberanek]

You want strftime() not strtotime(), and don't use it with date(). So you want, for example,

echo strftime('%d-%m-%Y %H:%M:$S', $row_approved['end_time']);

Note that the formatting codes for strftime() are not the same as those for date().

Original comment by: campbell-m

[jberanek]

Try echo strftime('d-M-Y H:i:s', $row_approved['end_time']);

Original comment by: *anonymous

[jberanek]

Thank you so much Campbell. echo date('d-M-Y H:i:s', strftime($row_approved['start_time'])); worked after I changed the query.

Original comment by: mabdelhady

[jberanek]

One last question: what is status value of approved requests? I'm a bit confused about it, I though that pending requests has a value of "4"

Original comment by: mabdelhady

[jberanek]

The status field is a bit field and covers three different statuses:

// The status code field for an entry is a tinyint (smallint on PostgreSQL)
// with individual bits set to record the various possible boolean properties
// of a booking:
//
// Bit 0:  Privacy status (set = private)
// Bit 1:  Approval status (set = not yet approved)
// Bit 2:  Confirmation status (set = not yet confirmed)
//
// A "standard" booking has status 0x00;

define('STATUS_PRIVATE',           0x01);
define('STATUS_AWAITING_APPROVAL', 0x02);
define('STATUS_TENTATIVE',         0x04);

So you will need to do bitwise checking of the status field to see if a booking is approved, eg

WHERE (status&" . STATUS_AWAITING_APPROVAL . " = 0)

Original comment by: campbell-m

[jberanek]

Thanks Campbell. Please accept my apology about the repeated questions when I added the where part to my query, I got a "Unknown column 'STATUS_AWAITING_APPROVAL' in 'where clause'" here's my query:

SELECT * FROM mrbs_entry WHERE now() >= FROM_UNIXTIME(start_time) and now() <= FROM_UNIXTIME(end_time) and (status&" . STATUS_AWAITING_APPROVAL . " = 0)

Please note that this is a custom php page with a manual query.

Original comment by: mabdelhady

[jberanek]

Sorry, I wasn't very clear above. STATUS_AWAITING_APPROVAL is a PHP constant, so that example above was part of a PHP statement.

You could use

SELECT * FROM mrbs_entry WHERE now() >= FROM_UNIXTIME(start_time) and now() <= FROM_UNIXTIME(end_time) and (status&2 = 0)

but it would be better to use the PHP constant, rather than hard code the value 2. So something like

$sql = "SELECT * FROM mrbs_entry WHERE now() >= FROM_UNIXTIME(start_time) and now() <= FROM_UNIXTIME(end_time) and (status&" . STATUS_AWAITING_APPROVAL . " = 0)";
// and then use $sql in a query

Original comment by: campbell-m

[jberanek]

Thank you Campbell, that worked.

Original comment by: mabdelhady

[jberanek]

Sorry about opening this topic again but I'm facing an issue on thsi custom page which queries today's approved requested. Because the start & end times are stored in UNIX times stamp format I'm using the query below:

SELECT *
FROM mrbs_entry
WHERE now() >= FROM_UNIXTIME(start_time) and now() <= FROM_UNIXTIME(end_time) and (status&2 = 0)

But it only displays the approved ones that are hapenning right now meaning that if there's a request which starts at 9:00 AM if I look at the page at 8:00 AM I won't see the request. How can I remove the time part for the query and only use dates? Thanks a million.

Original comment by: mabdelhady

[jberanek]

It should be noted that MRBS never uses FROM_UNIXTIME() in queries, in order to support user-configurable timezones.

We always return these times in UNIX/POSIX time format, and then convert in PHP, using the user's configured timezone.

Equally, to support non-English date/time formats, we always use utf8_strftime() on the returned UNIX/POSIX timestamps.

utf8_strftime() takes 2 arguments, as the standard PHP strftime function (http://php.net/manual/en/function.strftime.php)- the first argument being the string format and the second being the timestamp.

Using date() and strftime() in the same call doesn't make any sense, as date() takes a timestamp and strftime() takes 2 arguments including a timestamp.

If you want to retrieve a specific time range, you should be using mktime() in the PHP code to create timestamps and then passing these timestamps to the SQL.

For an example look elsewhere in the MRBS code, a simple example is in the function get_start_first_slot() inside functions.php.

Original comment by: jberanek

[jberanek]

Thanks John but it seems taht because of the old version I'm currently on things are a bit different. I have searched for get_start_first_slot() in functions.inc and in all of the web folder and couldn't find it.

Original comment by: mabdelhady

[jberanek]

I would greatly appreciate if you assist me on should a query that queries today's approved requests look like. Thanks a lot

Original comment by: mabdelhady

[jberanek]

Look at the function day_table_innerhtml() in functions.inc to see how MRBS does it. To get just the approved bookings add

AND (status&2 = 0)

to the query.

Original comment by: campbell-m

[jberanek]

So Ilooked at the day_table_innerhtml() function on functions_table.inc and used it as a guide to alter my query to:

$dst_change = is_dst($month, $day, $year);
  $am7=mktime($morningstarts, $morningstarts_minutes, 0,
              $month, $day, $year, is_dst($month,$day,$year,$morningstarts));
  $pm7=mktime($eveningends, $eveningends_minutes, 0,
              $month, $day, $year, is_dst($month,$day,$year,$eveningends));
$query_approved = "SELECT * FROM mrbs_entry WHERE start_time <= $pm7 AND end_time > $am7 and (status&2 = 0)";

And I got a blank page. Sorry for the multiple questions and I do appreciate the help.

Original comment by: mabdelhady

[jberanek]

Add the following lines to the end of internalconfig.inc.php so that you can see what the error is:

error_reporting(-1);
ini_set('display_errors', '1');

Original comment by: campbell-m

[jberanek]

It's already there and I see no errors just a blank page

Original comment by: mabdelhady

[jberanek]

Then you'll need to comment out sections of the code until you see what's causing the problem.

If you haven't included the MRBS files, then you'll need to do that:

require_once "defaultincludes.inc";

Otherwise things like is_dst() won't be recognised and you'll get a fatal error for calling an undefined function.

Original comment by: campbell-m

[jberanek]

Same blank page. Here's a screenshot of the require once at the top of the page

Original comment by: mabdelhady

Attachments: https://sourceforge.net/p/mrbs/support-requests/_discuss/thread/f513329d/13d7/b3d7/attachment/Screen%20Shot%202018-02-08%20at%2012.56.49%20PM.jpg

[jberanek]

Never mind, it's working now! Thank you sooooo much!

Original comment by: mabdelhady

[jberanek]

Hello Campbell,

I'm reopening this post for another question :) I'm trying to create a pending requests page for each of the areas so I copied the pending.php page and added another where condition with the ared id as follow:

SELECT E.id, E.name, E.room_id, E.start_time, E.create_by, " .
               sql_syntax_timestamp_to_unix("E.timestamp") . " AS last_updated,
               E.reminded, E.repeat_id,
               M.room_name, M.area_id, A.area_name, A.enable_periods,
               E.info_time AS entry_info_time, E.info_user AS entry_info_user,
               T.info_time AS repeat_info_time, T.info_user AS repeat_info_user
          FROM $tbl_room AS M, $tbl_area AS A, $tbl_entry AS E
     LEFT JOIN $tbl_repeat AS T ON E.repeat_id=T.id
         WHERE E.room_id = M.id
           AND M.area_id = A.id
           AND M.area_id = 2
           AND M.disabled = 0
           AND A.disabled = 0
           AND $sql_approval_enabled
           AND (E.status&" . STATUS_AWAITING_APPROVAL . " != 0)

But that genrates a blank page. Help please!

Thank you for your amazing support!

Original comment by: mabdelhady

[jberanek]

It looks like you havent't got the full line, ie starting with $sql = " etc.. and similarly on the last line.

I think you want

$sql = "SELECT E.id, E.name, E.room_id, E.start_time, E.create_by, " .
               sql_syntax_timestamp_to_unix("E.timestamp") . " AS last_updated,
               E.reminded, E.repeat_id,
               M.room_name, M.area_id, A.area_name, A.enable_periods,
               E.info_time AS entry_info_time, E.info_user AS entry_info_user,
               T.info_time AS repeat_info_time, T.info_user AS repeat_info_user
          FROM $tbl_room AS M, $tbl_area AS A, $tbl_entry AS E
     LEFT JOIN $tbl_repeat AS T ON E.repeat_id=T.id
         WHERE E.room_id = M.id
           AND M.area_id = 2
           AND M.disabled = 0
           AND A.disabled = 0
           AND $sql_approval_enabled
           AND (E.status&" . STATUS_AWAITING_APPROVAL . " != 0)";

Note that I have also deleted the AND M.area_id = A.id as you're looking (presumably) for area 2.

Original comment by: campbell-m

[jberanek]

Thanks Campbell but still getting empty page!

Original comment by: mabdelhady

[jberanek]

Have a look in your PHP error log to see what the error is, or else, temporarily, add the following lines to the bottom of internalconfig.inc.php to display the error in the browser:

error_reporting(-1);
ini_set('display_errors', '1');

Original comment by: campbell-m