[OPEN-ILS-GENERAL] ***SPAM*** Setting up a web-based php report for bookings

[Soulliere, Robert]

Setting up a web-based php report for bookings (The new booking module will be available in Evergreen release 1.6.1.0) as shown during lightning talks at the Evergreen 2010 conference
Background information:
Mohawk College has been using a new booking module (to be released to the community in version 1.6.1.0). The booking module includes a backend which allows our staff to book multimedia items for our faculty. However, it does not yet include a calendar or list of upcoming bookings so that staff could easily check to find  alternative times if an item is already booked at the specified time. The following is a quick fix solution in the interim as we wait for a more efficient and effective way to view a calendar of future bookings built into the Evergreen framework. I must emphasize that this is a quick and dirty temporary solution and  would expect that a better and  more permanent solution will be developed in the near future.  This can be adapted for other quick live reports for all kinds of data from the evergreen database. If you need more information, let me know. If you want to provide more effective alternatives, that would be great as well.
The following is a way to set up a php page to view future bookings in the next week. This creates a very basic table list view.

1)      Create the directory /openils/var/web/booking_view. Make sure it is owned by opensrf.


2)      For authentication to the folder append the following lines to eg_vhost.conf in your apache2 configuration :

# ----------------------------------------------------------------------------------
# The booking view
# ----------------------------------------------------------------------------------
<Location /booking_view/>
  SetHandler perl-script
  PerlSetVar OILSProxyTitle "Report Login"
  PerlSetVar OILSProxyDescription "Please log in to view report"
  PerlSetVar OILSProxyPermissions "STAFF_LOGIN"
  PerlHandler OpenILS::WWW::Proxy
  Options +ExecCGi
  allow from all
</Location>

This will force the user to login before accessing the booking report.  You could tighten up authentication even more by adjusting PerlSetVar OILSProxyPermissions as needed using the permission groups in your evergreen database.


3)      Install  PHP5 and pgsql postgresql support (if not already installed). Refer to http://ca.php.net/manual/en/index.php  and http://ca.php.net/pgsql for help.


4)      Create a view in your postgresql database. Here is the SQL :

CREATE OR REPLACE VIEW booking AS SELECT DISTINCT start_time, end_time, req_lib.name AS "request lib", pic_lib.name AS "pick up lib", usrname,card.barcode AS "user barcode", resource.barcode, asset.call_number.label AS "Call Number", config.copy_status.name, item.value FROM booking.reservation AS reserv INNER JOIN booking.resource AS resource ON reserv.target_resource = resource.ID INNER JOIN actor.usr ON reserv.usr = actor.usr.ID INNER JOIN actor.card ON actor.usr.card = actor.card.ID INNER JOIN asset.copy ON resource.barcode = asset.copy.barcode INNER JOIN config.copy_status ON asset.copy.status = config.copy_status.id INNER JOIN asset.call_number ON asset.copy.call_number = asset.call_number.id INNER JOIN metabib.full_rec AS item ON asset.call_number.record = item.record INNER JOIN actor.org_unit as req_lib ON request_lib = req_lib.id INNER JOIN actor.org_unit as pic_lib ON pickup_lib = pic_lib.id  WHERE start_time >= current_date AND start_time < current_date + 7 AND reserv.cancel_time IS NULL AND item.tag = '245' AND item.subfield = 'a' ORDER BY req_lib.name, start_time;
NOTE: the SQL could be adjusted for different time periods and allow for more flexibility in regards to time periods and locations.
 5) Create your PHP file in the booking _view directory  to bring in the postgresql data. Below are the key php parts:
   <?php
                echo "Date: ".date("Y-m-d")."<br/>";
                $host = "[HOST OF YOUR EVERGREEN DATABASE] ";
                $user = "evergreen";
                $pass = "[ENTER YOUR EVERGREEN PASSWORD HERE]";
                $db = "evergreen";
                $connection = pg_connect ("host=$host dbname=$db user=$user password=$pass");
                if (!$connection)  {
                                die("Could not Connect to database");
                }
                $query = "SELECT * FROM booking";
                $result = pg_query($connection, $query);
                 $rows = pg_num_rows($result);
                 echo "There are currently $rows reservations in the next week";
                if ($rows > 0)  {
     ?>
                                <p>
                                <table border=1 width="80%">
                                <tr>
                                                <td>Start</td>
                                                <td>Finish</td>
                                                <td>Request library</td>
                                                <td>Pickup</td>
                                                <td>User</td>
                                                 <td>User ID</td>
                                                <td>Barcode</td>
                                                <td>Call Number</td>
                                                <td>Status</td>
                                                <td>Title</td>
                                </tr>
<?php
                                for ($i=0; $i<$rows; $i++) {
                                                $row = pg_fetch_row($result, $i);
?>
                                 <tr>
 <?php
                                                for ($j=0; $j<10; $j++) {
?>
                                <td><?php echo $row[$j];  ?></td>
<?php
                          }
?>
                                </tr>
                                              <?php  }  ?>
 </table>
                              <?php  }
              else  { ?>
                <p>No Data!</p>
              <?php }
              pg_close($connection);
?>

6) test by going to the php page you created using a browser. E.g. Use "https://HOSTNAME/booking_view/PHPFILE.php" for the authenticated page.

 Creative Commons Attribution-ShareAlike 2.5 license<http://creativecommons.org/licenses/by-sa/2.5/>.




________________________________
This E-mail contains privileged and confidential information intended
only for the individual or entity named in the message. If the reader
of this message is not the intended recipient, or the agent responsible
to deliver it to the intended recipient, you are hereby notified that
any review, dissemination, distribution or copying of this communication
is prohibited. If this communication was received in error, please
notify the sender by reply E-mail immediately, and delete and destroy
the original message.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20100426/17781254/attachment-0001.htm