This app is used by University of Wisconsin-Madison Physics and others to
coordinate building usage during the COVID-19 dirsuption.

Known to work in Debian 9 under Apache 2.4 with PHP 7.0.33 and MariaDB 10.1.44.

The .htaccess file assumes that shibboleth is set up to authenticate
people by NetID.  The code makes use of the shibboleth attributes cn
(or givenName and sn if cn is not available) and
wiscEduMSOLPrimaryAddress (or if not available, mail).

Copy config_example.php to config.php and edit it to match your
situation.

Some optional CSV files may be used to provide information about
people and rooms.  The order of columns in the CSV file does not
matter.  Instead, column names in the first line in the CSV file are
used to determine the contents of each column.  The names are not case
sensitive, and spaces and underscores may be used interchangeably.
Columns with unexpected names are simply ignored.  The CSV file names
are case sensitive (assuming your filesystem is case sensitive).  The
file names are derived from department or building names with all but
alphabetic and numeric characters stripped.  Any fields in the CSV
file that contain commas must be surrounded by double quotes.

Copy approvals/ExampleDepartment.csv to
approvals/YourDepartmentName.csv and edit.  See the note above about
CSV naming.  This file specifies who should be automatically approved
to be in what room.  Required columns are NETID and ROOM.  If you
don't have NETID, you can use NAME or FIRST and LAST, but matching by
NETID is better, because name matching relies on some fuzzy rules.  A
person may be listed more than once.  All entries for the person will
be used.  A value of * for NETID, NAME, or FIRST and LAST will match
all people.  The room column may contain a list of rooms separated by
commas.  (Remember that if you are creating the CSV file in a text
editor, a field containing commas must be surrounded by double
quotes.)  If you have configured multiple buildings, each room should
be followed by a space and then a building name or abbreviation.  The
room * will match any requested room.  The additional columns HOURS
and DAYS may be included to specify a maximum number of hours per week
and a specific set of weekdays for which the automatic approval
applies.  Days are specified as a string of characters from the set
U, M, T, W, R, F, S for Sunday through Saturday respectively.

Copy roomcaps/ExampleBuilding.csv to roomcaps/YourBuildingName.csv and
edit.  See the note above about CSV naming.  The required columns are
ROOM and MAXCAP.  If somebody registeres to be in a room and would
exceed MAXCAP occupants, the request is not automatically approved.
An additional column NORMCAP may be specified (default 1).  If
somebody registers to be in a room and would exceed NORMCAP occupants,
the request is not rejected, but a warning is displayed to make sure
they are aware of the unusual situation.  If the DESCRIPTION column is
provided, the room description will be displayed in any MAXCAP or
NORMCAP messages.

Copy people/ExampleDepartment.csv to people/YourDepartmentName.csv and
edit.  See the above note about CSV naming.  This file is only needed
if shibboleth does not provide a name attribute (cn or givenName and
sn) or if you are using the safety monitor feature.  All columns but
NETID are optional.  NAME or FIRST and LAST are used if shibboleth
does not provide name attributes.  EMAIL is used if shibboleth does
not provide email attributes (wiscEduMSOLPrimaryAddress or mail).  If
a column URL is provided, it will be used in place of a wisc.edu
search for the person when the person is listed as a safety monitor.
If the column SAFETY_MONITOR is present and set to Y, the person
will be allowed to sign up to be a safety monitor if that feature
is enabled in config.php.

Copy db_example.php to db.php and insert db access parameters.

Example of how to set up the database:

CREATE DATABASE building_access;

GRANT ALL ON building_access.* TO building_access_user@hostname IDENTIFIED BY 'password here';

USE building_access;

CREATE TABLE `building_access` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NETID` varchar(60) NOT NULL,
  `NAME` varchar(120) NOT NULL,
  `EMAIL` varchar(60) NOT NULL,
  `PURPOSE` varchar(280) NOT NULL,
  `BUILDING` varchar(20) NOT NULL,
  `ROOM` varchar(60) NOT NULL,
  `REQUESTED` datetime NOT NULL,
  `START_TIME` datetime NOT NULL,
  `END_TIME` datetime NOT NULL,
  `APPROVED` char(1) NOT NULL DEFAULT '',
  `APPROVED_TIME` datetime DEFAULT NULL,
  `APPROVED_BY` varchar(60) NOT NULL DEFAULT '',
  `ADMIN_REASON` varchar(60) NOT NULL DEFAULT '',
  `UPDATED` datetime NOT NULL,
  `DEPARTMENT` varchar(10) NOT NULL,
  `REPEAT_PARENT` int(11) NOT NULL DEFAULT '0',
  `REPEAT_DAYS` varchar(7) NOT NULL DEFAULT '',
  `REPEAT_THROUGH` date DEFAULT NULL,
  `SAFETY_MONITOR` char(1) NOT NULL DEFAULT '',
  `PRIVACY` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `NETID` (`NETID`,`START_TIME`),
  KEY `START_TIME` (`START_TIME`),
  KEY `REPEAT_PARENT` (`REPEAT_PARENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8