<commit_before><?php

include_once( "header.php" );
include_once( "methods.php" );
include_once( 'ldap.php' );


class BMVPDO extends PDO 
{
    function __construct( $host = 'localhost'  )
    {
        $conf = parse_ini_file( '/etc/hippo/hipporc', $process_section = TRUE );
        $options = array ( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );
        $host = $conf['mysql']['host'];
        $port = $conf['mysql']['port'];
        if( $port == -1 )
            $port = 3306;

        $user = $conf['mysql']['user'];
        $password = $conf['mysql']['password'];
        $dbname = $conf['mysql']['database'];
        
        try {
            parent::__construct( 'mysql:host=' . $host . ";dbname=$dbname"
                , $user, $password, $options 
            );
        } catch( PDOException $e) {
            echo minionEmbarrassed( 
                "failed to connect to database: ".  $e->getMessage()
            );
            $this->error = $e->getMessage( );
            echo goBackToPageLink( 'index.php', 0 );
            exit;
        }

    }
}

// Construct the PDO
$db = new BMVPDO( "localhost" );
initialize( );

/**
    * @brief Create all tables.
    *
    * @return 
 */
function initialize( )
{
    global $db;
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS holidays 
            (date DATE NOT NULL PRIMARY KEY, description VARCHAR(100) NOT NULL)
        ' );
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS visitors 
        ( id INT NOT NULL AUTO_INCREMENT
            , title ENUM( "Dr", "Prof", "Mr", "Ms" ) DEFAULT "Dr"
            , email VARCHAR(100) 
            , first_name VARCHAR(100) NOT NULL CHECK( first_name <> "" )
            , middle_name VARCHAR(100)
            , last_name VARCHAR(100)
            , department VARCHAR(500)
            , homepage VARCHAR(500)
            , institute VARCHAR(1000) NOT NULL CHECK( institute <> "" )
            , PRIMARY KEY (id)
            , UNIQUE KEY (email,first_name,last_name)
            )' );

    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS talks 
        ( id INT NOT NULL AUTO_INCREMENT
        , speaker VARCHAR(100) NOT NULL
        , host VARCHAR(100) NOT NULL
        , title VARCHAR(1000) NOT NULL
        , description TEXT 
        , created_by VARCHAR(100) NOT NULL CHECK( register_by <> "" )
        , create_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        , date DATE 
        , time TIME
        , venue VARCHAR(100)
        , status ENUM( 
            "DELIVERED", "SCHEDULED", "NOT_SCHEDULED", "CANCELLED", "INVALID" 
            ) DEFAULT "NOT_SCHEDULED"
        , PRIMARY KEY (id)
        )' );

    // This table holds the email template.
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS email_templates
        ( id VARCHAR(100) NOT NULL
        , when_to_send VARCHAR(200)
        , description TEXT, PRIMARY KEY (id) )' 
        );

    // Save the emails here. A bot should send these emails.
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS emails
        ( id INT NOT NULL AUTO_INCREMENT
            , recipients VARCHAR(1000) NOT NULL
            , subject VARCHAR(1000) NOT NULL
            , msg TEXT NOT NULL
            , when_to_send DATETIME NOT NULL
            , status ENUM( "PENDING", "SENT", "FAILED", "CANCELLED" ) DEFAULT "PENDING"
            , created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            , last_tried_on DATETIME 
            , PRIMARY KEY (id) )' 
        );

    // Other tables.
    $res = $db->query( "
        CREATE TABLE IF NOT EXISTS logins (
            id VARCHAR( 200 ) 
            , login VARCHAR(100) 
            , email VARCHAR(200)
            , alternative_email VARCHAR(200)
            , first_name VARCHAR(200)
            , last_name VARCHAR(100)
            , roles SET( 
                'USER', 'ADMIN', 'JOURNALCLUB_ADMIN', 'AWS_ADMIN', 'BOOKMYVENUE_ADMIN'
            ) DEFAULT 'USER'
            , last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            , created_on DATETIME 
            , joined_on DATE
            , eligible_for_aws ENUM ('YES', 'NO' ) DEFAULT 'NO'
            , valid_until DATE
            , status SET( 'ACTIVE', 'INACTIVE', 'TEMPORARLY_INACTIVE', 'EXPIRED' ) DEFAULT 'ACTIVE' 
            , laboffice VARCHAR(200)
            -- The faculty fields here must match in faculty table.
            , title ENUM( 
                'FACULTY', 'POSTDOC'
                , 'PHD', 'INTPHD', 'MSC'
                , 'JRF', 'SRF'
                , 'NONACADEMIC_STAFF'
                , 'VISITOR', 'ALUMNI', 'OTHER'
                , 'UNSPECIFIED' 
            ) DEFAULT 'UNSPECIFIED'
            , institute VARCHAR(300)
            , PRIMARY KEY (login))" 
        );

    $res = $db->query( "
        CREATE TABLE IF NOT EXISTS bookmyvenue_requests (
            gid INT NOT NULL
            , rid INT NOT NULL
            , user VARCHAR(50) NOT NULL
            , title VARCHAR(100) NOT NULL
            , description TEXT 
            , venue VARCHAR(80) NOT NULL
            , date DATE NOT NULL
            , start_time TIME NOT NULL
            , end_time TIME NOT NULL
            , status ENUM ( 'PENDING', 'APPROVED', 'REJECTED', 'CANCELLED' ) DEFAULT 'PENDING'
            , is_public_event ENUM( 'YES', 'NO' ) DEFAULT 'NO'
            , url VARCHAR( 1000 )
            , modified_by VARCHAR(50) -- Who modified the request last time.
            , timestamp TIMESTAMP  DEFAULT CURRENT_TIMESTAMP 
            , PRIMARY KEY( gid, rid ) )
           " ); 
    $res = $db->query( "
        -- venues must created before events because events refer to venues key as
        -- foreign key.
        CREATE TABLE IF NOT EXISTS venues (
            id VARCHAR(80) NOT NULL
            , name VARCHAR(300) NOT NULL
            , institute VARCHAR(100) NOT NULL
            , building_name VARCHAR(100) NOT NULL
            , floor INT NOT NULL
            , location VARCHAR(500) 
            , type VARCHAR(30) NOT NULL
            , strength INT NOT NULL
            , distance_from_ncbs DECIMAL(3,3) DEFAULT 0.0 
            , has_projector ENUM( 'YES', 'NO' ) NOT NULL
            , suitable_for_conference ENUM( 'YES', 'NO' ) NOT NULL
            , has_skype ENUM( 'YES', 'NO' ) DEFAULT 'NO'
            -- How many events this venue have hosted so far. Meaure of popularity.
            , total_events INT NOT NULL DEFAULT 0 
            , PRIMARY KEY (id) )"
        );

    $res = $db->query( "
        CREATE TABLE IF NOT EXISTS events (
            -- Sub even will be parent.children format.
            gid INT NOT NULL
            , eid INT NOT NULL
            -- If yes, this entry will be put on google calendar.
            , is_public_event ENUM( 'YES', 'NO' ) DEFAULT 'NO' 
            , class ENUM( 'LABMEET', 'LECTURE', 'MEETING'
                , 'SEMINAR', 'TALK', 'SCHOOL'
                , 'CONFERENCE', 'CULTURAL', 'AWS'
                , 'SPORTS'
                , 'UNKNOWN'
                ) DEFAULT 'UNKNOWN' 
            , short_description VARCHAR(200) NOT NULL
            , description TEXT
            , date DATE NOT NULL
            , venue VARCHAR(80) NOT NULL
            , user VARCHAR( 50 ) NOT NULL
            , start_time TIME NOT NULL
            , end_time TIME NOT NULL
            , status ENUM( 'VALID', 'INVALID', 'CANCELLED' ) DEFAULT 'VALID' 
            , calendar_id VARCHAR(500)
            , calendar_event_id VARCHAR(500)
            , url VARCHAR(1000)
            , PRIMARY KEY( gid, eid )
            , FOREIGN KEY (venue) REFERENCES venues(id)
            )"
        );
            
    $res = $db->query( "
        create TABLE IF NOT EXISTS supervisors (
            email VARCHAR(200) PRIMARY KEY NOT NULL
            , first_name VARCHAR( 200 ) NOT NULL
            , middle_name VARCHAR(200)
            , last_name VARCHAR( 200 ) 
            , affiliation VARCHAR( 1000 ) NOT NULL
            , url VARCHAR(300) ) " 
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS faculty (
            email VARCHAR(200) PRIMARY KEY NOT NULL
            , first_name VARCHAR( 200 ) NOT NULL
            , middle_name VARCHAR(200)
            , last_name VARCHAR( 200 ) 
            , status ENUM ( 'ACTIVE', 'INACTIVE', 'INVALID' ) DEFAULT 'ACTIVE'
            , affiliation VARCHAR( 1000 ) NOT NULL DEFAULT 'NCBS Bangalore'
            , created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
            , modified_on DATETIME NOT NULL
            , url VARCHAR(300) )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS annual_work_seminars (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
            , speaker VARCHAR(200) NOT NULL -- user
            , date DATE NOT NULL -- final date
            , time TIME NOT NULL DEFAULT '16:00'
            , supervisor_1 VARCHAR( 200 ) NOT NULL -- first superviser must be from NCBS
            , supervisor_2 VARCHAR( 200 ) -- superviser 2, optional
            , tcm_member_1 VARCHAR( 200 ) -- Can be null at the time of inserting a query.
            , tcm_member_2 VARCHAR( 200 ) -- optional 
            , tcm_member_3 VARCHAR( 200 ) -- optional 
            , tcm_member_4 VARCHAR( 200 ) -- optional
            , title VARCHAR( 1000 )
            , abstract TEXT
            , FOREIGN KEY (speaker) REFERENCES logins(login)
            , UNIQUE KEY (speaker, date)
            , FOREIGN KEY (supervisor_1) REFERENCES faculty(email) 
            )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS upcoming_aws (
            id INT AUTO_INCREMENT PRIMARY KEY
            , speaker VARCHAR(200) NOT NULL -- user
            , date DATE NOT NULL -- tentative date
            , time TIME NOT NULL DEFAULT '16:00'
            , supervisor_1 VARCHAR( 200 ) 
            , supervisor_2 VARCHAR( 200 ) 
            , tcm_member_1 VARCHAR( 200 ) 
            , tcm_member_2 VARCHAR( 200 ) 
            , tcm_member_3 VARCHAR( 200 ) 
            , tcm_member_4 VARCHAR( 200 ) 
            , title VARCHAR( 1000 )
            , abstract TEXT
            , status ENUM( 'VALID', 'INVALID' ) DEFAULT 'VALID'
            , comment TEXT 
            , FOREIGN KEY (speaker) REFERENCES logins(login)
            , UNIQUE (speaker, date) )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS aws_requests (
            id INT AUTO_INCREMENT PRIMARY KEY
            , speaker VARCHAR(200) NOT NULL -- user
            , date DATE -- final date
            , time TIME DEFAULT '16:00'
            , supervisor_1 VARCHAR( 200 ) -- first superviser must be from NCBS
            , supervisor_2 VARCHAR( 200 ) -- superviser 2, optional
            , tcm_member_1 VARCHAR( 200 ) -- Can be null at the time of inserting a query.
            , tcm_member_2 VARCHAR( 200 ) -- optional 
            , tcm_member_3 VARCHAR( 200 ) -- optional 
            , tcm_member_4 VARCHAR( 200 ) -- optional
            , scheduled_on DATE 
            , title VARCHAR( 1000 )
            , abstract TEXT
            , status ENUM( 'PENDING', 'APPROVED', 'REJECTED', 'INVALID', 'CANCELLED' ) DEFAULT 'PENDING'
            , modidfied_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS logins_metadata (
            login VARCHAR(200) PRIMARY KEY
            , user_image blob NOT NULL
            , other_image blob
            , FOREIGN KEY (login) REFERENCES logins( login )
            )"
        );

    return $res;
}

function getVenues( $sortby = 'total_events' )
{
    global $db;
    // Sort according to total_events hosted by venue
    $res = $db->query( "SELECT * FROM venues ORDER BY $sortby, id" );
    return fetchEntries( $res );
}


function getTableSchema( $tableName )
{
    global $db;
    $stmt = $db->prepare( "DESCRIBE $tableName" );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getVenuesGroupsByType(  )
{
    global $db;
    // Sort according to total_events hosted by venue
    $venues = getVenues( );
    $newVenues = Array( );
    foreach( $venues as $venue )
    {
        $vtype = $venue['type'];
        if( ! array_key_exists( $vtype, $newVenues ) )
            $newVenues[ $vtype ] = Array();
        array_push( $newVenues[$vtype], $venue );
    }
    return $newVenues;
}

// Return the row representing venue for given venue id.
function getVenueById( $venueid )
{
    global $db;
    $venueid = trim( $venueid );
    $stmt = $db->prepare( "SELECT * FROM venues WHERE id=:id" );
    $stmt->bindValue( ':id', $venueid );
    $stmt->execute( );
    return $stmt->fetch( PDO::FETCH_ASSOC );
}


// Get all requests which are pending for review.
function getPendingRequestsGroupedByGID( )
{
    return getRequestsGroupedByGID( 'PENDING' );
}

// Get all requests with given status.
function getRequestsGroupedByGID( $status  )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE status=:status GROUP BY gid' );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

// Get all events with given status.
function getEventsByGroupId( $gid, $status = NULL  )
{
    global $db;
    $query = "SELECT * FROM events WHERE gid=:gid";
    if( $status )
        $query .= " AND status=:status ";

    $stmt = $db->prepare( $query );
    $stmt->bindValue( ':gid', $gid );
    if( $status )
        $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

//  Get a event of given gid and eid. There is only one such event.
function getEventsById( $gid, $eid )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM events WHERE gid=:gid AND eid=:eid' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':eid', $eid );
    $stmt->execute( );
    return $stmt->fetch( PDO::FETCH_ASSOC );
}

/**
    * @brief Get list of requests made by this users. These requests must be 
    * newer than the current date minus 2 days and time else they won't show up.
    *
    * @param $userid
    * @param $status
    *
    * @return 
 */
function getRequestOfUser( $userid, $status = 'PENDING' )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE user=:user 
        AND status=:status AND date >= NOW() - INTERVAL 2 DAY
        GROUP BY gid' );
    $stmt->bindValue( ':user', $userid );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOfUser( $userid, $from = '-1 days', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( $from ));
    $stmt = $db->prepare( 'SELECT * FROM events WHERE user=:user 
        AND date >= :from
        AND status=:status
        GROUP BY gid' );
    $stmt->bindValue( ':user', $userid );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':from', $from );
    $stmt->execute( );
    return fetchEntries( $stmt );

}

// Fetch entries from sqlite responses
function fetchEntries( $res, $how = PDO::FETCH_ASSOC )
{
    $array = Array( );
    if( $res ) {
        while( $row = $res->fetch( $how ) )
            array_push( $array, $row );
    }
    return $array;
}

// Get the request when group id and request id is given.
function getRequestById( $gid, $rid )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE gid=:gid AND rid=:rid' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':rid', $rid );
    $stmt->execute( );
    return $stmt->fetch( PDO::FETCH_ASSOC );
}

// Return a list of requested with same group id.
function getRequestByGroupId( $gid )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE gid=:gid' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

// Return a list of requested with same group id and status
function getRequestByGroupIdAndStatus( $gid, $status )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE gid=:gid AND status=:status' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
    * @brief Change the status of request.
    *
    * @param $requestId
    * @param $status
    *
    * @return true on success, false otherwise.
 */
function changeRequestStatus( $gid, $rid, $status )
{
    global $db;
    $stmt = $db->prepare( "UPDATE bookmyvenue_requests SET 
        status=:status WHERE gid=:gid AND rid=:rid"
    );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':rid', $rid );
    return $stmt->execute( );
}

/**
    * @brief Change status of all request identified by group id.
    *
    * @param $gid
    * @param $status
    *
    * @return 
 */
function changeStatusOfRequests( $gid, $status )
{
    global $db;
    $stmt = $db->prepare( "UPDATE bookmyvenue_requests SET status=:status WHERE gid=:gid" );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':gid', $gid );
    return $stmt->execute( );
}

function changeStatusOfEventGroup( $gid, $user, $status )
{
    global $db;
    $stmt = $db->prepare( "UPDATE events SET status=:status WHERE 
        gid=:gid AND user=:user" );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':user', $user );
    return $stmt->execute( );
}

/**
    * @brief Get the list of upcoming events.
 */
function getEvents( $from = 'today', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date >= :date AND 
        status=:status" );
    $stmt->bindValue( ':date', $from );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
  * @brief Get the list of upcoming events grouped by gid.
 */
function getEventsGrouped( $sortby = NULL, $from = 'today', $status = 'VALID' )
{
    global $db;
    if( ! $sortby )
        $sortby = '';
    else
        $sortby = " ORDER BY $sortby";

    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date >= :date AND 
        status=:status GROUP BY gid $sortby" );
    $stmt->bindValue( ':date', $from );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
    * @brief Get the list of upcoming events.
 */
function getPublicEvents( $from = 'today', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date >= :date AND 
        status=:status AND is_public_event='YES'" );
    $stmt->bindValue( ':date', $from );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
    * @brief Get list of public event on given day.
    *
    * @param $date
    * @param $status
    *
    * @return 
 */
function getPublicEventsOnThisDay( $date = 'today', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date = :date AND 
        status=:status AND is_public_event='YES'" );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOn( $day, $status = 'VALID')
{
    global $db;
    $stmt = $db->prepare( "SELECT * FROM events 
        WHERE status=:status AND date = :date" );
    $stmt->bindValue( ':date', $day );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOnThisVenueOnThisday( $venue, $date, $status = 'VALID' )
{
    global $db;
    $stmt = $db->prepare( "SELECT * FROM events 
        WHERE venue=:venue AND status=:status AND date=:date" );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOnThisVenueBetweenTime( $venue, $date
    , $start_time, $end_time
   ,  $status = 'VALID' )
{
    global $db;
    $stmt = $db->prepare( 
        "SELECT * FROM events
        WHERE venue=:venue AND status=:status AND date=:date 
            AND ( start_time >= :start_time OR end_time <= :end_time )
        "
    );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':start_time', $start_time );
    $stmt->bindValue( ':end_time', $end_time );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getRequestsOnThisVenueOnThisday( $venue, $date, $status = 'PENDING' )
{
    global $db;
    $stmt = $db->prepare( "SELECT * FROM bookmyvenue_requests 
        WHERE venue=:venue AND status=:status AND date=:date" );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getRequestsOnThisVenueBetweenTime( $venue, $date
    , $start_time, $end_time
    , $status = 'PENDING' )
{
    global $db;
    $stmt = $db->prepare( 
        "SELECT * FROM bookmyvenue_requests 
        WHERE venue=:venue AND status=:status AND date=:date
            AND ( start_time >= :start_time OR end_time <= :end_time )
        " );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':start_time', $start_time );
    $stmt->bindValue( ':end_time', $end_time );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}


/**
    * @brief Sunmit a request for review.
    *
    * @param $request
    *
    * @return  Group id of request.
 */
function submitRequest( $request )
{
    global $db;
    if( ! array_key_exists( 'user', $_SESSION ) )
    {
        echo printErrorSevere( "Error: I could not determine the name of user" );
        goToPage( "user.php", 5 );
    }

    if( ! array_key_exists( 'venue', $request ) )
    {
        echo printErrorSevere( "No venue found in your request" );
        goToPage( "user.php", 5 );
    }

    $repeatPat = $request[ 'repeat_pat' ];

    if( strlen( $repeatPat ) > 0 )
        $days = repeatPatToDays( $repeatPat );
    else 
        $days = Array( $request['date'] );

    if( count( $days ) < 1 )
    {
        echo minionEmbarrassed( "I could not generate list of slots for you reuqest" );
        return false;
    }

    $rid = 0;
    $results = Array( );
    $res = $db->query( 'SELECT MAX(gid) AS gid FROM bookmyvenue_requests' );
    $gid = intval($res->fetch( PDO::FETCH_ASSOC )['gid']) + 1;
    foreach( $days as $day ) 
    {
        $rid += 1;
        $query = $db->prepare( 
            "INSERT INTO bookmyvenue_requests ( 
                gid, rid, user, venue
                , title, description
                , date, start_time, end_time
                , status 
            ) VALUES ( 
                :gid, :rid, :user, :venue
                , :title, :description
                , :date , :start_time, :end_time
                , 'PENDING' 
            )");

        $query->bindValue( ':gid', $gid );
        $query->bindValue( ':rid', $rid );
        $query->bindValue( ':user', $_SESSION['user'] );
        $query->bindValue( ':venue' , $request['venue' ] );
        $query->bindValue( ':title', $request['title'] );
        $query->bindValue( ':description', $request['description'] );
        $query->bindValue( ':date', $day );
        $query->bindValue( ':start_time', $request['start_time'] );
        $query->bindValue( ':end_time', $request['end_time'] );
        $res = $query->execute();
        if( ! $res )
        {
            echo printWarning( "Could not submit request id $gid" );
            return 0;
        }
        array_push( $results, $res );
    }
    return $gid;
}


function increaseEventHostedByVenueByOne( $venueId )
{
    global $db;
    $stmt = $db->prepare( 'UPDATE venues SET total_events = total_events + 1 WHERE id=:id' );
    $stmt->bindValue( ':id', $venueId );
    $res = $stmt->execute( );
    return $res;
}

/**
    * @brief Create a new event in dateabase. The group id and event id of event 
    * is same as group id (gid) and rid of request which created it.
    *
    * @param $gid
    * @param $rid
    *
    * @return 
 */
function approveRequest( $gid, $rid )
{
    $request = getRequestById( $gid, $rid );

    global $db;
    $stmt = $db->prepare( 'INSERT INTO events (
        gid, eid, short_description, description, date, venue, start_time, end_time
        , user
    ) VALUES ( 
        :gid, :eid, :short_description, :description, :date, :venue, :start_time, :end_time 
        , :user
    )');
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':eid', $rid );
    $stmt->bindValue( ':short_description', $request['title'] );
    $stmt->bindValue( ':description', $request['description'] );
    $stmt->bindValue( ':date', $request['date'] );
    $stmt->bindValue( ':venue', $request['venue'] );
    $stmt->bindValue( ':start_time', $request['start_time'] );
    $stmt->bindValue( ':end_time', $request['end_time'] );
    $stmt->bindValue( ':user', $request['user'] );
    $res = $stmt->execute();
    if( $res )
    {
        changeRequestStatus( $gid, $rid, 'APPROVED' );
        // And update the count of number of events hosted by this venue.
        increaseEventHostedByVenueByOne( $request['venue'] );
    }

    return $res;<commit_msg>Small tweak to schema. Events can refer to other table id.<commit_after><?php

include_once( "header.php" );
include_once( "methods.php" );
include_once( 'ldap.php' );


class BMVPDO extends PDO 
{
    function __construct( $host = 'localhost'  )
    {
        $conf = parse_ini_file( '/etc/hippo/hipporc', $process_section = TRUE );
        $options = array ( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );
        $host = $conf['mysql']['host'];
        $port = $conf['mysql']['port'];
        if( $port == -1 )
            $port = 3306;

        $user = $conf['mysql']['user'];
        $password = $conf['mysql']['password'];
        $dbname = $conf['mysql']['database'];
        
        try {
            parent::__construct( 'mysql:host=' . $host . ";dbname=$dbname"
                , $user, $password, $options 
            );
        } catch( PDOException $e) {
            echo minionEmbarrassed( 
                "failed to connect to database: ".  $e->getMessage()
            );
            $this->error = $e->getMessage( );
            echo goBackToPageLink( 'index.php', 0 );
            exit;
        }

    }
}

// Construct the PDO
$db = new BMVPDO( "localhost" );
initialize( );

/**
    * @brief Create all tables.
    *
    * @return 
 */
function initialize( )
{
    global $db;
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS holidays 
            (date DATE NOT NULL PRIMARY KEY, description VARCHAR(100) NOT NULL)
        ' );
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS visitors 
        ( id INT NOT NULL AUTO_INCREMENT
            , title ENUM( "Dr", "Prof", "Mr", "Ms" ) DEFAULT "Dr"
            , email VARCHAR(100) 
            , first_name VARCHAR(100) NOT NULL CHECK( first_name <> "" )
            , middle_name VARCHAR(100)
            , last_name VARCHAR(100)
            , department VARCHAR(500)
            , homepage VARCHAR(500)
            , institute VARCHAR(1000) NOT NULL CHECK( institute <> "" )
            , PRIMARY KEY (id)
            , UNIQUE KEY (email,first_name,last_name)
            )' );

    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS talks 
        ( id INT NOT NULL AUTO_INCREMENT
        , speaker VARCHAR(100) NOT NULL
        , host VARCHAR(100) NOT NULL
        , title VARCHAR(1000) NOT NULL
        , description TEXT 
        , created_by VARCHAR(100) NOT NULL CHECK( register_by <> "" )
        , create_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        , date DATE 
        , time TIME
        , venue VARCHAR(100)
        , status ENUM( 
            "DELIVERED", "SCHEDULED", "NOT_SCHEDULED", "CANCELLED", "INVALID" 
            ) DEFAULT "NOT_SCHEDULED"
        , PRIMARY KEY (id)
        , UNIQUE KEY (speaker,date,time)
        , FOREIGN KEY (created_by) REFERENCES logins(id)
        )' );

    // This table holds the email template.
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS email_templates
        ( id VARCHAR(100) NOT NULL
        , when_to_send VARCHAR(200)
        , description TEXT, PRIMARY KEY (id) )' 
        );

    // Save the emails here. A bot should send these emails.
    $res = $db->query( 
        'CREATE TABLE IF NOT EXISTS emails
        ( id INT NOT NULL AUTO_INCREMENT
            , recipients VARCHAR(1000) NOT NULL
            , subject VARCHAR(1000) NOT NULL
            , msg TEXT NOT NULL
            , when_to_send DATETIME NOT NULL
            , status ENUM( "PENDING", "SENT", "FAILED", "CANCELLED" ) DEFAULT "PENDING"
            , created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            , last_tried_on DATETIME 
            , PRIMARY KEY (id) )' 
        );

    // Other tables.
    $res = $db->query( "
        CREATE TABLE IF NOT EXISTS logins (
            id VARCHAR( 200 ) 
            , login VARCHAR(100) 
            , email VARCHAR(200)
            , alternative_email VARCHAR(200)
            , first_name VARCHAR(200)
            , last_name VARCHAR(100)
            , roles SET( 
                'USER', 'ADMIN', 'JOURNALCLUB_ADMIN', 'AWS_ADMIN', 'BOOKMYVENUE_ADMIN'
            ) DEFAULT 'USER'
            , last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            , created_on DATETIME 
            , joined_on DATE
            , eligible_for_aws ENUM ('YES', 'NO' ) DEFAULT 'NO'
            , valid_until DATE
            , status SET( 'ACTIVE', 'INACTIVE', 'TEMPORARLY_INACTIVE', 'EXPIRED' ) DEFAULT 'ACTIVE' 
            , laboffice VARCHAR(200)
            -- The faculty fields here must match in faculty table.
            , title ENUM( 
                'FACULTY', 'POSTDOC'
                , 'PHD', 'INTPHD', 'MSC'
                , 'JRF', 'SRF'
                , 'NONACADEMIC_STAFF'
                , 'VISITOR', 'ALUMNI', 'OTHER'
                , 'UNSPECIFIED' 
            ) DEFAULT 'UNSPECIFIED'
            , institute VARCHAR(300)
            , PRIMARY KEY (login))" 
        );

    $res = $db->query( "
        CREATE TABLE IF NOT EXISTS bookmyvenue_requests (
            gid INT NOT NULL
            , rid INT NOT NULL
            , user VARCHAR(50) NOT NULL
            , title VARCHAR(100) NOT NULL
            , description TEXT 
            , venue VARCHAR(80) NOT NULL
            , date DATE NOT NULL
            , start_time TIME NOT NULL
            , end_time TIME NOT NULL
            , status ENUM ( 'PENDING', 'APPROVED', 'REJECTED', 'CANCELLED' ) DEFAULT 'PENDING'
            , is_public_event ENUM( 'YES', 'NO' ) DEFAULT 'NO'
            , url VARCHAR( 1000 )
            , modified_by VARCHAR(50) -- Who modified the request last time.
            , timestamp TIMESTAMP  DEFAULT CURRENT_TIMESTAMP 
            , PRIMARY KEY( gid, rid ) )
           " ); 
    $res = $db->query( "
        -- venues must created before events because events refer to venues key as
        -- foreign key.
        CREATE TABLE IF NOT EXISTS venues (
            id VARCHAR(80) NOT NULL
            , name VARCHAR(300) NOT NULL
            , institute VARCHAR(100) NOT NULL
            , building_name VARCHAR(100) NOT NULL
            , floor INT NOT NULL
            , location VARCHAR(500) 
            , type VARCHAR(30) NOT NULL
            , strength INT NOT NULL
            , distance_from_ncbs DECIMAL(3,3) DEFAULT 0.0 
            , has_projector ENUM( 'YES', 'NO' ) NOT NULL
            , suitable_for_conference ENUM( 'YES', 'NO' ) NOT NULL
            , has_skype ENUM( 'YES', 'NO' ) DEFAULT 'NO'
            -- How many events this venue have hosted so far. Meaure of popularity.
            , total_events INT NOT NULL DEFAULT 0 
            , PRIMARY KEY (id) )"
        );

    // All events are put on this table.
    $res = $db->query( "
        CREATE TABLE IF NOT EXISTS events (
            -- Sub event will be parent.children format. 
            gid INT NOT NULL -- This is group id of events.
            , eid INT NOT NULL -- This is event id in that group.
            -- If details of this event are also stored in some other table, use 
            -- this field. Format tablename.id e.g. talks and aws can be referred 
            -- to here.
            , external_id VARCHAR(50) NOT NULL default 'SELF.0'
            -- If yes, this entry will be put on google calendar.
            , is_public_event ENUM( 'YES', 'NO' ) DEFAULT 'NO' 
            , class ENUM( 'LABMEET', 'MEETING'
                , 'TALK', 'LECTURE', 'SEMINAR', 'AWS'
                , 'SCHOOL' , 'CONFERENCE'
                , 'CULTURAL', 'SPORTS', 'UNKNOWN'
                ) DEFAULT 'UNKNOWN' 
            , short_description VARCHAR(200) NOT NULL
            , description TEXT
            , date DATE NOT NULL
            , venue VARCHAR(80) NOT NULL
            , created_by VARCHAR( 50 ) NOT NULL
            , start_time TIME NOT NULL
            , end_time TIME NOT NULL
            , status ENUM( 'VALID', 'INVALID', 'CANCELLED' ) DEFAULT 'VALID' 
            , calendar_id VARCHAR(500)
            , calendar_event_id VARCHAR(500)
            , url VARCHAR(1000)
            , last_modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            , PRIMARY KEY( gid, eid )
            , FOREIGN KEY (venue) REFERENCES venues(id)
            )"
        );
            
    $res = $db->query( "
        create TABLE IF NOT EXISTS supervisors (
            email VARCHAR(200) PRIMARY KEY NOT NULL
            , first_name VARCHAR( 200 ) NOT NULL
            , middle_name VARCHAR(200)
            , last_name VARCHAR( 200 ) 
            , affiliation VARCHAR( 1000 ) NOT NULL
            , url VARCHAR(300) ) " 
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS faculty (
            email VARCHAR(200) PRIMARY KEY NOT NULL
            , first_name VARCHAR( 200 ) NOT NULL
            , middle_name VARCHAR(200)
            , last_name VARCHAR( 200 ) 
            , status ENUM ( 'ACTIVE', 'INACTIVE', 'INVALID' ) DEFAULT 'ACTIVE'
            , affiliation VARCHAR( 1000 ) NOT NULL DEFAULT 'NCBS Bangalore'
            , created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
            , modified_on DATETIME NOT NULL
            , url VARCHAR(300) )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS annual_work_seminars (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
            , speaker VARCHAR(200) NOT NULL -- user
            , date DATE NOT NULL -- final date
            , time TIME NOT NULL DEFAULT '16:00'
            , supervisor_1 VARCHAR( 200 ) NOT NULL -- first superviser must be from NCBS
            , supervisor_2 VARCHAR( 200 ) -- superviser 2, optional
            , tcm_member_1 VARCHAR( 200 ) -- Can be null at the time of inserting a query.
            , tcm_member_2 VARCHAR( 200 ) -- optional 
            , tcm_member_3 VARCHAR( 200 ) -- optional 
            , tcm_member_4 VARCHAR( 200 ) -- optional
            , title VARCHAR( 1000 )
            , abstract TEXT
            , FOREIGN KEY (speaker) REFERENCES logins(login)
            , UNIQUE KEY (speaker, date)
            , FOREIGN KEY (supervisor_1) REFERENCES faculty(email) 
            )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS upcoming_aws (
            id INT AUTO_INCREMENT PRIMARY KEY
            , speaker VARCHAR(200) NOT NULL -- user
            , date DATE NOT NULL -- tentative date
            , time TIME NOT NULL DEFAULT '16:00'
            , supervisor_1 VARCHAR( 200 ) 
            , supervisor_2 VARCHAR( 200 ) 
            , tcm_member_1 VARCHAR( 200 ) 
            , tcm_member_2 VARCHAR( 200 ) 
            , tcm_member_3 VARCHAR( 200 ) 
            , tcm_member_4 VARCHAR( 200 ) 
            , title VARCHAR( 1000 )
            , abstract TEXT
            , status ENUM( 'VALID', 'INVALID' ) DEFAULT 'VALID'
            , comment TEXT 
            , FOREIGN KEY (speaker) REFERENCES logins(login)
            , UNIQUE (speaker, date) )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS aws_requests (
            id INT AUTO_INCREMENT PRIMARY KEY
            , speaker VARCHAR(200) NOT NULL -- user
            , date DATE -- final date
            , time TIME DEFAULT '16:00'
            , supervisor_1 VARCHAR( 200 ) -- first superviser must be from NCBS
            , supervisor_2 VARCHAR( 200 ) -- superviser 2, optional
            , tcm_member_1 VARCHAR( 200 ) -- Can be null at the time of inserting a query.
            , tcm_member_2 VARCHAR( 200 ) -- optional 
            , tcm_member_3 VARCHAR( 200 ) -- optional 
            , tcm_member_4 VARCHAR( 200 ) -- optional
            , scheduled_on DATE 
            , title VARCHAR( 1000 )
            , abstract TEXT
            , status ENUM( 'PENDING', 'APPROVED', 'REJECTED', 'INVALID', 'CANCELLED' ) DEFAULT 'PENDING'
            , modidfied_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )"
        );

    $res = $db->query( "
        create TABLE IF NOT EXISTS logins_metadata (
            login VARCHAR(200) PRIMARY KEY
            , user_image blob NOT NULL
            , other_image blob
            , FOREIGN KEY (login) REFERENCES logins( login )
            )"
        );

    return $res;
}

function getVenues( $sortby = 'total_events' )
{
    global $db;
    // Sort according to total_events hosted by venue
    $res = $db->query( "SELECT * FROM venues ORDER BY $sortby, id" );
    return fetchEntries( $res );
}


function getTableSchema( $tableName )
{
    global $db;
    $stmt = $db->prepare( "DESCRIBE $tableName" );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getVenuesGroupsByType(  )
{
    global $db;
    // Sort according to total_events hosted by venue
    $venues = getVenues( );
    $newVenues = Array( );
    foreach( $venues as $venue )
    {
        $vtype = $venue['type'];
        if( ! array_key_exists( $vtype, $newVenues ) )
            $newVenues[ $vtype ] = Array();
        array_push( $newVenues[$vtype], $venue );
    }
    return $newVenues;
}

// Return the row representing venue for given venue id.
function getVenueById( $venueid )
{
    global $db;
    $venueid = trim( $venueid );
    $stmt = $db->prepare( "SELECT * FROM venues WHERE id=:id" );
    $stmt->bindValue( ':id', $venueid );
    $stmt->execute( );
    return $stmt->fetch( PDO::FETCH_ASSOC );
}


// Get all requests which are pending for review.
function getPendingRequestsGroupedByGID( )
{
    return getRequestsGroupedByGID( 'PENDING' );
}

// Get all requests with given status.
function getRequestsGroupedByGID( $status  )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE status=:status GROUP BY gid' );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

// Get all events with given status.
function getEventsByGroupId( $gid, $status = NULL  )
{
    global $db;
    $query = "SELECT * FROM events WHERE gid=:gid";
    if( $status )
        $query .= " AND status=:status ";

    $stmt = $db->prepare( $query );
    $stmt->bindValue( ':gid', $gid );
    if( $status )
        $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

//  Get a event of given gid and eid. There is only one such event.
function getEventsById( $gid, $eid )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM events WHERE gid=:gid AND eid=:eid' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':eid', $eid );
    $stmt->execute( );
    return $stmt->fetch( PDO::FETCH_ASSOC );
}

/**
    * @brief Get list of requests made by this users. These requests must be 
    * newer than the current date minus 2 days and time else they won't show up.
    *
    * @param $userid
    * @param $status
    *
    * @return 
 */
function getRequestOfUser( $userid, $status = 'PENDING' )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE user=:user 
        AND status=:status AND date >= NOW() - INTERVAL 2 DAY
        GROUP BY gid' );
    $stmt->bindValue( ':user', $userid );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOfUser( $userid, $from = '-1 days', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( $from ));
    $stmt = $db->prepare( 'SELECT * FROM events WHERE created_by=:user 
        AND date >= :from
        AND status=:status
        GROUP BY gid' );
    $stmt->bindValue( ':user', $userid );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':from', $from );
    $stmt->execute( );
    return fetchEntries( $stmt );

}

// Fetch entries from sqlite responses
function fetchEntries( $res, $how = PDO::FETCH_ASSOC )
{
    $array = Array( );
    if( $res ) {
        while( $row = $res->fetch( $how ) )
            array_push( $array, $row );
    }
    return $array;
}

// Get the request when group id and request id is given.
function getRequestById( $gid, $rid )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE gid=:gid AND rid=:rid' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':rid', $rid );
    $stmt->execute( );
    return $stmt->fetch( PDO::FETCH_ASSOC );
}

// Return a list of requested with same group id.
function getRequestByGroupId( $gid )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE gid=:gid' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

// Return a list of requested with same group id and status
function getRequestByGroupIdAndStatus( $gid, $status )
{
    global $db;
    $stmt = $db->prepare( 'SELECT * FROM bookmyvenue_requests WHERE gid=:gid AND status=:status' );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
    * @brief Change the status of request.
    *
    * @param $requestId
    * @param $status
    *
    * @return true on success, false otherwise.
 */
function changeRequestStatus( $gid, $rid, $status )
{
    global $db;
    $stmt = $db->prepare( "UPDATE bookmyvenue_requests SET 
        status=:status WHERE gid=:gid AND rid=:rid"
    );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':rid', $rid );
    return $stmt->execute( );
}

/**
    * @brief Change status of all request identified by group id.
    *
    * @param $gid
    * @param $status
    *
    * @return 
 */
function changeStatusOfRequests( $gid, $status )
{
    global $db;
    $stmt = $db->prepare( "UPDATE bookmyvenue_requests SET status=:status WHERE gid=:gid" );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':gid', $gid );
    return $stmt->execute( );
}

function changeStatusOfEventGroup( $gid, $user, $status )
{
    global $db;
    $stmt = $db->prepare( "UPDATE events SET status=:status WHERE 
        gid=:gid AND created_by=:user" );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':user', $user );
    return $stmt->execute( );
}

/**
    * @brief Get the list of upcoming events.
 */
function getEvents( $from = 'today', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date >= :date AND 
        status=:status" );
    $stmt->bindValue( ':date', $from );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
  * @brief Get the list of upcoming events grouped by gid.
 */
function getEventsGrouped( $sortby = NULL, $from = 'today', $status = 'VALID' )
{
    global $db;
    if( ! $sortby )
        $sortby = '';
    else
        $sortby = " ORDER BY $sortby";

    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date >= :date AND 
        status=:status GROUP BY gid $sortby" );
    $stmt->bindValue( ':date', $from );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
    * @brief Get the list of upcoming events.
 */
function getPublicEvents( $from = 'today', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date >= :date AND 
        status=:status AND is_public_event='YES'" );
    $stmt->bindValue( ':date', $from );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

/**
    * @brief Get list of public event on given day.
    *
    * @param $date
    * @param $status
    *
    * @return 
 */
function getPublicEventsOnThisDay( $date = 'today', $status = 'VALID' )
{
    global $db;
    $from = date( 'Y-m-d', strtotime( 'today' ));
    $stmt = $db->prepare( "SELECT * FROM events WHERE date = :date AND 
        status=:status AND is_public_event='YES'" );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOn( $day, $status = 'VALID')
{
    global $db;
    $stmt = $db->prepare( "SELECT * FROM events 
        WHERE status=:status AND date = :date" );
    $stmt->bindValue( ':date', $day );
    $stmt->bindValue( ':status', $status );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOnThisVenueOnThisday( $venue, $date, $status = 'VALID' )
{
    global $db;
    $stmt = $db->prepare( "SELECT * FROM events 
        WHERE venue=:venue AND status=:status AND date=:date" );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getEventsOnThisVenueBetweenTime( $venue, $date
    , $start_time, $end_time
   ,  $status = 'VALID' )
{
    global $db;
    $stmt = $db->prepare( 
        "SELECT * FROM events
        WHERE venue=:venue AND status=:status AND date=:date 
            AND ( start_time >= :start_time OR end_time <= :end_time )
        "
    );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':start_time', $start_time );
    $stmt->bindValue( ':end_time', $end_time );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getRequestsOnThisVenueOnThisday( $venue, $date, $status = 'PENDING' )
{
    global $db;
    $stmt = $db->prepare( "SELECT * FROM bookmyvenue_requests 
        WHERE venue=:venue AND status=:status AND date=:date" );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}

function getRequestsOnThisVenueBetweenTime( $venue, $date
    , $start_time, $end_time
    , $status = 'PENDING' )
{
    global $db;
    $stmt = $db->prepare( 
        "SELECT * FROM bookmyvenue_requests 
        WHERE venue=:venue AND status=:status AND date=:date
            AND ( start_time >= :start_time OR end_time <= :end_time )
        " );
    $stmt->bindValue( ':date', $date );
    $stmt->bindValue( ':start_time', $start_time );
    $stmt->bindValue( ':end_time', $end_time );
    $stmt->bindValue( ':status', $status );
    $stmt->bindValue( ':venue', $venue );
    $stmt->execute( );
    return fetchEntries( $stmt );
}


/**
    * @brief Sunmit a request for review.
    *
    * @param $request
    *
    * @return  Group id of request.
 */
function submitRequest( $request )
{
    global $db;
    if( ! array_key_exists( 'user', $_SESSION ) )
    {
        echo printErrorSevere( "Error: I could not determine the name of user" );
        goToPage( "user.php", 5 );
    }

    if( ! array_key_exists( 'venue', $request ) )
    {
        echo printErrorSevere( "No venue found in your request" );
        goToPage( "user.php", 5 );
    }

    $repeatPat = $request[ 'repeat_pat' ];

    if( strlen( $repeatPat ) > 0 )
        $days = repeatPatToDays( $repeatPat );
    else 
        $days = Array( $request['date'] );

    if( count( $days ) < 1 )
    {
        echo minionEmbarrassed( "I could not generate list of slots for you reuqest" );
        return false;
    }

    $rid = 0;
    $results = Array( );
    $res = $db->query( 'SELECT MAX(gid) AS gid FROM bookmyvenue_requests' );
    $gid = intval($res->fetch( PDO::FETCH_ASSOC )['gid']) + 1;
    foreach( $days as $day ) 
    {
        $rid += 1;
        $query = $db->prepare( 
            "INSERT INTO bookmyvenue_requests ( 
                gid, rid, user, venue
                , title, description
                , date, start_time, end_time
                , status 
            ) VALUES ( 
                :gid, :rid, :user, :venue
                , :title, :description
                , :date , :start_time, :end_time
                , 'PENDING' 
            )");

        $query->bindValue( ':gid', $gid );
        $query->bindValue( ':rid', $rid );
        $query->bindValue( ':user', $_SESSION['user'] );
        $query->bindValue( ':venue' , $request['venue' ] );
        $query->bindValue( ':title', $request['title'] );
        $query->bindValue( ':description', $request['description'] );
        $query->bindValue( ':date', $day );
        $query->bindValue( ':start_time', $request['start_time'] );
        $query->bindValue( ':end_time', $request['end_time'] );
        $res = $query->execute();
        if( ! $res )
        {
            echo printWarning( "Could not submit request id $gid" );
            return 0;
        }
        array_push( $results, $res );
    }
    return $gid;
}


function increaseEventHostedByVenueByOne( $venueId )
{
    global $db;
    $stmt = $db->prepare( 'UPDATE venues SET total_events = total_events + 1 WHERE id=:id' );
    $stmt->bindValue( ':id', $venueId );
    $res = $stmt->execute( );
    return $res;
}

/**
    * @brief Create a new event in dateabase. The group id and event id of event 
    * is same as group id (gid) and rid of request which created it.
    *
    * @param $gid
    * @param $rid
    *
    * @return 
 */
function approveRequest( $gid, $rid )
{
    $request = getRequestById( $gid, $rid );

    global $db;
    $stmt = $db->prepare( 'INSERT INTO events (
        gid, eid, short_description, description, date, venue, start_time, end_time
        , created_by
    ) VALUES ( 
        :gid, :eid, :short_description, :description, :date, :venue, :start_time, :end_time 
        , :user
    )');
    $stmt->bindValue( ':gid', $gid );
    $stmt->bindValue( ':eid', $rid );
    $stmt->bindValue( ':short_description', $request['title'] );
    $stmt->bindValue( ':description', $request['description'] );
    $stmt->bindValue( ':date', $request['date'] );
    $stmt->bindValue( ':venue', $request['venue'] );
    $stmt->bindValue( ':start_time', $request['start_time'] );
    $stmt->bindValue( ':end_time', $request['end_time'] );
    $stmt->bindValue( ':user', $request['user'] );
    $res = $stmt->execute();
    if( $res )
    {
        changeRequestStatus( $gid, $rid, 'APPROVED' );
        // And update the count of number of events hosted by this venue.
        increaseEventHostedByVenueByOne( $request['venue'] );
    }

    return $res;
{'fraction_non_alphanumeric': 0.13675706866705137, 'fraction_numerical': 0.011482977495672245, 'mean_word_length': 2.1530717447995635, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 315, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6832753', 'n_tokens_mistral': 17234, 'n_tokens_neox': 15855, 'n_words': 4581}