/controller/maintain/events.php

<?php if (! constant("INSYSTEM")) { echo "Permission denied"; exit; } ?>
<?php
/*
	hitta.sverok.se site code
	http://hitta.sverok.se
	
	Copyright (c) 2010 Kriss Andsten
	Dual licensed under the MIT and GPL licenses.
	http://hitta.sverok.se/License
*/
	global $settings, $site;
	assertLogin();
	assertMaintainerPermission();

    if (isset($site['requestPath'][2])) {
        list($id, $bla) = explode('#',$site['requestPath'][2]); // We want the hash for another purpose... ignore it here.
        // We get post, we save.
        if(count($_POST)) {
            // Update grants.
            $grantTypes = getGrantTypes();
            foreach($grantTypes as &$gr) {
                $gr = $gr['id'];
            }
            
            $grantIds = array();
            $eventId  = -1;
            foreach($_POST as $key => $value) {
                list($name, $eventId, $grantId) = explode('_', $key);
                if($name == 'grant' && is_numeric($eventId) && is_numeric($grantId)) {
                    $grantIds[] = $grantId;
                }
            } 
/* At the moment you can only add new grants, because we want to save the data in the event_grant-connection table. In the future you might be able to remove grants.
            $diffIds = array_diff($grantTypes, $grantIds); // Get the ids not present in the input data.
            foreach($diffIds as $d) {
                // Delete the grants that weren't selected in the form. 
                $dbh->exec("DELETE FROM event_grant WHERE event=$eventId AND grantid=$d");
            }
*/

            // Create new grant bindings
            foreach($grantIds as $g) {
                $dbh->exec("INSERT INTO event_grant(event, grantid) VALUES($eventId, $g)");
            }
            header('Location: ' . '#event_' . $eventId);
        } // if count $_POST
        
        // // // //
        // Type 1: Show entity information including events.
        $view = new Opt_View('admin/maintain/events/event-listing.tpl');
        $view->entitydata = maintainGetEntity($id);
        $view->site = $site;

        // OPT is frustrating at the moment, so I get my revenge by doing really ugly hacks.
        // Please change this solution if you know a better one.
        $events     = array_values(maintainGetEvents($id));
        $grantTypes = getGrantTypes();
        foreach($events as &$e) {
            $newTypes = $grantTypes; // Create a copy. We will add fields to the copy soon.
            if(!is_null($e['grants'])) {
                foreach($e['grants'] as $g) {
                    foreach($newTypes as &$gt) { // Add checked if we got the grant. 
                        if($g['id'] == $gt['id']) {
                            $gt['checked'] = true;
                            break;
                        }
                    }
                }
            } 
            $e['editGrantsList'] = $newTypes; 
        }

        $eventView = new Opt_View('admin/maintain/events/event-list-entry.tpl');
        $eventView->site = $site;
        $eventView->eventdata = $events;
        $eventView->eventstats = maintainGetNiceEventStats($id);
        $view->events = $eventView;
        $view->grantStats = getApprovedGrantSums($id);
        $title = "Föreningar";
    } else {
        // // // //
        // Type 2: Show all entities with events.
        $view = new Opt_View('admin/maintain/events/entity-listing.tpl');
        $view->site = $site;

        $assView = new Opt_View('admin/maintain/events/entity-list-entry.tpl');
        $assView->associationdata = getEntitiesWithEvents();
        $assView->site = $site;
        $view->associations = $assView;
        $view->grantStats = getApprovedGrantSums();
        $title = 'Förening med träffar';
    }
    
    $view->page = array(title => $title);
    $view->menu = new Opt_View('support/menu.tpl');
    $view->menu->items = getMenuItems('main');
    
    $output = new Opt_Output_Http;
    $output->setContentType(Opt_Output_Http::HTML, 'utf-8');
    $output->render($view);	

    // // // // 
    // Functions
    
    // Show an entity in the event listing
    function maintainGetEntity($id) {
        global $dbh;

        // All ids are numbers. All other input is silly.
        if (!is_numeric($id)) {
            return array();
        }

        $q = $dbh->prepare("SELECT en.id, en.name as name, en.groupkey, en.email, en.website, en.shortDesc, et.name as type, us.username FROM entity en LEFT JOIN(entity_type et, user us, user_entity ue) ON (et.id=en.etype AND us.id=ue.user AND ue.entity=en.id) WHERE en.id=?");
        $q->execute(array($id));

        return $q->fetch(PDO::FETCH_ASSOC);
    }

    // Get the number of not processed grants, grants applied for and the total of events.
    function maintainGetNiceEventStats($id) {
        global $dbh;

        // All ids are numbers. All other input is silly.
        if (!is_numeric($id)) {
            return array();
        }

        $q = $dbh->prepare("select ".
                           "(select count(evgr.approved) from event_grant evgr, event ev where entity=? AND approved='Not processed' AND curdate() > end AND evgr.event=ev.id) as not_processed, ". 
                           "(select count(distinct ev.id) from event ev, grants gr, event_grant evgr where ev.entity=? and ev.id=evgr.event and gr.id=evgr.grantid) as grants, ".
                           "(select count(*) from event where entity=?) as total");
        $q->execute(array($id, $id, $id));

        return $q->fetch(PDO::FETCH_ASSOC);
    }

    // Get events
    function maintainGetEvents($id) {
        global $dbh;

        // All ids are numbers. All other input is silly.
        if (!is_numeric($id)) {
            return array();
        }

        $q = $dbh->prepare("SELECT ev.id, ev.created, ev.start, ev.end, ev.name, ev.shortDesc, ev.fullDesc, ev.instanceOf, ev.website, ev.email, ev.phone, ev.attended, ev.report, egr.approved, ev.paid, gr.id as grantid, gr.grantname FROM event ev LEFT JOIN (event_grant egr, grants gr) ON (ev.id=egr.event AND egr.grantid=gr.id) WHERE ev.entity=? ORDER BY ev.end");
        $q->execute(array($id));

        // An event may have many grants, which gives copies of the event in the outdata. So we reorder that data in a nice fashion.
        // I think this loop does things a bit backwards. But it's okay. :)
        $data = $q->fetchAll(PDO::FETCH_ASSOC);
        $output = array();
        foreach($data as $row) {
            $x = array('name' => $row['grantname'], 'id' => $row['grantid'], 'approved' => $row['approved']);
            if(array_key_exists($row['id'], $output)) {
                $output[$row['id']]['grants'][] = $x; 
            } else {
                $row['grants'] = array($x);
                unset($row['grantname'], $row['grantid'], $row['approved']);
                $output[$row['id']] = $row;
            } 
        }

        return $output;
    }

    // Get all the grant types: id, name
    function getGrantTypes() {
        global $dbh;
        
        $q = $dbh->prepare("SELECT id, grantname AS name FROM grants");
        $q->execute();

        return $q->fetchAll(PDO::FETCH_ASSOC);
    }

    // For the entity listing
    function getEntitiesWithEvents() {
        global $dbh;
        
        $q = $dbh->prepare("SELECT en.id, en.name AS entity_name, COUNT(ev.id) AS number FROM event ev, entity en WHERE en.id=ev.entity GROUP BY entity_name ORDER BY entity_name");
        $q->execute();

        return $q->fetchAll(PDO::FETCH_ASSOC);
    }

    // How much money has the association had approved events for?
    // Input is not mandatory.
    function getApprovedGrantSums($id=NULL) {
        global $dbh;

        // All ids are numbers. All other input is silly.
        if (!is_numeric($id) && !is_null($id)) {
            return array();
        }

        $qs = "SELECT YEAR(ev.end) as year, ev.attended, evgr.grantid, en.id as enid FROM event ev, entity en, event_grant evgr WHERE ev.entity=en.id AND ev.id=evgr.event AND (evgr.approved='Yes' OR evgr.grantid = 1) AND evgr.grantid IN (1,2,4)";
        if(!is_null($id)) {
            $qs .= ' AND en.id=?';
        }

        $q = $dbh->prepare($qs);

        if(!is_null($id)) {
            $q->execute(array($id));
        } else {
            $q->execute();
        }

        $result = array();
        $data   = $q->fetchAll(PDO::FETCH_ASSOC);

        // These are basically the rules for the grants.
        foreach($data as $row) {
            $x = array('year' => $row['year']);
            switch($row['grantid']) {
                case 1 : $x['grant'] = 200;
                    break;
                case 2 : $x['grant'] = (40 * $row['attended']) > 8000 ? 8000 : (40 * $row['attended']); // Max 8000 kr per event
                    break;
                case 4 : $x['grant'] = (20 * $row['attended']);
                    break;
            }
            $result[$row['enid']][] = $x;
        }

        $years = array();
        foreach($result as $enid => $arr) {
            $total = array();
            foreach($arr as $x) {
                $total[$x['year']] += $x['grant'];
            }

            foreach($total as $y => $g) {
                if($g > 8000 && $y == 2011) {
                    $total[$y] = 8000;
                }
                $years[$y] += $total[$y];
            }
        }
        return $years;
    }