source: trunk/jpdd/class.jpdd.php @ 1441

Last change on this file since 1441 was 1441, checked in by Daniel Kahn Gillmor, 5 years ago

make attendee index more readable

  • limit the paging to 100 people per column (means some attendee indexes will be larger).
  • add header and footer to make it easier to keep track of the full list
File size: 44.6 KB
Line 
1<?php  /* -*- indent-tabs-mode: nil; tab-width: 4; c-basic-offset: 2; -*-
2
3           */
4
5require_once 'class.dkg.site.php';
6
7if (!class_exists('JPDD_JPDD')) {
8
9  class JPDD_JPDD extends DKG_Site {
10
11    var $_signup_closed_message;
12    var $_presenter_title;
13    var $_presenters_title;
14
15
16    function JPDD_JPDD() {
17      $this->DKG_Site();
18      $this->addClassMapEntry('workshop', array('classname' => 'JPDD_Workshop', 
19                                                'filename' => 'class.jpdd.workshop.php',
20                                                'table' => 'workshop',
21                                                'limit' => 'event',
22                                                'sort' => 'title'));
23      $this->addClassMapEntry('proposal', array('classname' => 'JPDD_Proposal',
24                                                'filename' => 'class.jpdd.proposal.php',
25                                                'table' => 'proposal',
26                                                'limit' => 'event',
27                                                'sort' => 'title'));
28      // overrides the default DKG_Person map, which is fine because
29      // it's a subclass:
30      $this->addClassMapEntry('person', array('classname' => 'JPDD_Person', 
31                                              'filename' => 'class.jpdd.person.php',
32                                              'table' => 'person',
33                                              'sort' => 'lower(last_name),lower(first_name)'));
34      // likewise for DKG_Broadcast:
35      $this->addClassMapEntry('broadcast', array('classname' => 'JPDD_Broadcast', 
36                                              'filename' => 'class.jpdd.broadcast.php',
37                                              'table' => 'broadcast',
38                                              'sort' => 'id DESC'));
39      $this->addClassMapEntry('category', array('classname' => 'JPDD_Category', 
40                                                'filename' => 'class.jpdd.category.php',
41                                                'table' => 'category',
42                                                'sort' => 'title'));
43      $this->addClassMapEntry('room', array('classname' => 'JPDD_Room', 
44                                                'filename' => 'class.jpdd.room.php',
45                                                'table' => 'room',
46                                                'sort' => 'title'));
47      $this->addClassMapEntry('organization', array('classname' => 'JPDD_Organization', 
48                                                    'filename' => 'class.jpdd.organization.php',
49                                                    'table' => 'organization',
50                                                    'sort' => 'title'));
51      $this->addClassMapEntry('event', array('classname' => 'JPDD_Event', 
52                                                    'filename' => 'class.jpdd.event.php',
53                                                    'table' => 'event',
54                                                    'sort' => 'start_time'));
55      $this->addClassMapEntry('role', array('classname' => 'JPDD_Role', 
56                                            'filename' => 'class.jpdd.role.php',
57                                            'table' => 'role',
58                                            'sort' => 'title'));
59     
60    }
61    function getSiteDefaults() {
62      return array_merge(parent::getSiteDefaults(), 
63                         array('presenter_name' => 'presenter', // what should we call presenters?
64                               ));
65    }
66
67    function getPresenterName() {
68      return $this->_presenter_name;
69    }
70    function getPresenterNamePlural() {
71      if (property_exists($this, '_presenter_name_plural'))
72        return $this->_presenter_name_plural;
73      else
74        return $this->_presenter_name.'s'; // FIXME: this is bogus.  need better default pluralization rules
75    }
76   
77    function getDefaultPage() {
78      return ($this->isSignupClosed() ? '<div class="signupclosed">'.$this->getSignupClosedText().'</div>' : '').$this->getSnippet('home');
79    }
80
81    function initialize() {
82      parent::initialize();
83      $this->_stylesheets[] = 'jpdd.css';
84    }
85
86    function getNavLink($title,$targ) {
87      return '<a '.((($this->_action == $targ || (in_array($this->_action, array('view', 'edit')) && $this->_type == $targ)) || 
88                     ($this->_action.'/'.$this->_type.'/'.$this->_identifier == $targ)) ? 'class="current" ' : '').'href="'.$this->Path($targ).'">'.$title.'</a>';
89    }
90
91    function getActiveEvent() {
92      $map = $this->prepClass('event');
93      return new $map['classname'](array('id' => $this->getActiveEventID()));
94    }
95
96    function getNavLinks() {
97      // FIXME: this is way way way too static.
98      $links = array('Participating Schools' => 'organization');
99
100      if ($this->isAuthenticated()) {
101        if ($this->_authenticated_user->hasAnyOfThesePrivileges('Update Workshops'))
102          $links['Workshops'] = 'workshop';
103        if ($this->_authenticated_user->hasAnyOfThesePrivileges('List People'))
104          $links['People'] = 'person';
105        if ($this->_authenticated_user->hasAnyOfThesePrivileges('List Proposals'))
106          $links['Proposals'] = 'proposal';
107        if ($this->_authenticated_user->hasAnyOfThesePrivileges('List Rooms'))
108          $links['Rooms'] = 'room';
109        if ($this->_authenticated_user->hasAllOfThesePrivileges('Update Workshops', 'Edit People', 'Edit Categories')) {
110          $links['Review Signups'] = 'overview';
111          $links['Get Printouts'] = 'printout';
112        }
113        if ($this->_authenticated_user->hasAllOfThesePrivileges('Send Broadcast'))
114          $links['E-mail Alert'] = 'broadcast';
115        if ($this->_authenticated_user->hasAnyOfThesePrivileges('Manage Signups', 'Edit Categories'))
116          $links['Subject Areas'] = 'category';
117        if ($this->_authenticated_user->hasAnyOfThesePrivileges(array('Assign Roles', 'Edit Roles')))
118          $links['Liaisons'] = 'role'; // FIXME: the title for this link should really be "Roles", but for now it's easier to explain this way.
119
120        // add links if the user is a liaison:
121        $role = $this->getAll('role', "WHERE title = 'liaison'");
122        if (count($role) == 1) {
123          $orgs = $this->_authenticated_user->getRoleOrgs($role[0]->getID());
124          reset($orgs);
125          while (list(,$org) = each($orgs)) {
126            // this user is a liaison for some organizations:
127            $links['Liaison for '.$org->getTitle()] = 'liaison/organization/'.$org->getID();
128          }
129        }
130      } else {
131        if (($this->_action != 'newacct') && ((!$this->isSignupClosed()) || $this->getActiveEvent()->advertiseProposals()))
132          $links['Create New Account'] = 'newacct';
133      }
134      return '<div class="navlinks">'."\n".
135        join(' ', array_map(create_function('$t,$v', 'global $jpdd; return $jpdd->getNavLink($t,$v);'), array_keys($links), $links)).
136        "\n".'</div>';
137    }
138
139    function getTopofPage() {
140      return '<div class="container"><a href="'.$this->Path().'"><div class="header"><h1>'.$this->getPageTitle().'</h1>'.
141        '<div class="subtitle"><q>'.$this->_site_subtitle.'</q></div>'.
142        '<div class="date">'.$this->_site_date.'</div>'.
143        '</div></a>'."\n";
144    } 
145    function getSidebar() {
146      return '<div class="sidebar">
147'.(in_array($this->_action, array('login', 'logout', 'resetpass', 'newacct')) ? '' : $this->getLoginForm()).'
148'.$this->getNavLinks().'
149</div>';
150    }
151
152    function getPageBody() {
153      return $this->getTopofPage().'<div class="maincontent">'.$this->getMainContent().$this->getSnippet('directions').'</div>'.$this->getSidebar().'</div>'.$this->getSnippet('footer');
154    }
155
156
157    function getWorkshops() {
158      $workshops = $this->getAll('workshop');
159     
160      $ret = '<dl>';
161      reset($workshops);
162      while (list(,$workshop) = each($workshops)) {
163        $ret .= '<dt><a href="'.$this->Path('workshop', (int)($workshop->_id)).'">'.htmlentities($workshop->_title).'</a></dt>'.
164          '<dd>'.htmlentities($workshop->_description).'</dd>';
165      }
166      return $ret.'</ul>';
167    }
168
169
170    function getAdditionalSalutation() {
171      $x = $this->_authenticated_user->getSalutation();
172      if ('' != $x)
173        return '<div>'.$x.'</div>';
174      return $x;
175    }
176
177    function getAllowedActions() {
178      $ret = array_merge(parent::getAllowedActions(), array('signup', 'printout', 'overview', 'open', 'liaison', 'managesignups', 'accept'));
179      return $ret;
180    }
181
182    function getGenericOverview() {
183      $lists = '';
184
185      $categories = $this->getSeriesFromSQL('SELECT MAX(title) AS category, MAX(category.id) AS id,  COUNT(DISTINCT person_id) AS count FROM application JOIN category ON (category_id = category.id) WHERE event_id = '.$this->getActiveEventID().' GROUP BY category.id ORDER BY MAX(title)');
186      $total = $this->getValueFromSQL('SELECT COUNT(*) AS foo FROM application WHERE event_id = '.$this->getActiveEventID(), 'foo');
187      if (count($categories) > 0) {
188        $lists .= '<li>Total Signups: '.(int)$total.'<ul><li>by Subject Area:<ul>'."\n";
189        reset($categories);
190        while (list(,$cat) = each($categories)) {
191          $link = ($this->isAuthenticated() && $this->_authenticated_user->hasAllOfThesePrivileges('Manage Signups'));
192          $lists .= '<li>'.($link ? '<a href="'.$this->Path('managesignups','category', (int)$cat['id']).'">' : '').htmlentities($cat['category']).($link ? '</a>' : '').': '.(int)$cat['count']."</li>\n";
193        }
194        $lists .= "</ul></li>\n";
195        $lists .= '<li><a href="'.$this->Path('overview', 'appliednoattend').'">Signed up, but not assigned to a workshop</a>: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM (SELECT person.id FROM person JOIN (SELECT * FROM application WHERE event_id = '.$this->getActiveEventID().') AS application ON (person.id = person_id) LEFT JOIN (SELECT attendance.* FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().') AS attendance ON (person.id = attendance.person_id) WHERE attendance.id IS NULL) AS anw', 'foo')."</li>\n";
196        $lists .= '<li><a href="'.$this->Path('overview', 'attendnotapplied').'">Assigned to a workshop, but did not apply</a>: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM (SELECT person.id FROM person LEFT JOIN (SELECT * FROM application WHERE event_id = '.$this->getActiveEventID().') AS application ON (person.id = person_id) JOIN (SELECT attendance.* FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().') AS attendance ON (person.id = attendance.person_id) WHERE application.id IS NULL) AS wna', 'foo')."</li>\n";
197        $lists .= '</ul>';
198      }
199
200      $roles = $this->getKeyValuePairsFromSQL('SELECT MAX(title) AS role, COUNT(DISTINCT person_id) AS count FROM person_role JOIN role ON (role_id = role.id) WHERE event_id = '.$this->getActiveEventID(), 'role', 'count');
201      if (count($roles) > 0) {
202        $lists .= '<li>Roles for this event:<ul>'."\n";
203        reset($roles);
204        while (list($role,$num) = each($roles))
205          $lists .= '<li>'.htmlentities($role).': '.(int)$num."</li>\n";
206        $lists .= "</ul>\n";
207      }
208     
209
210      return '<ul>'."\n".
211        '<li>Workshops available: '.$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM workshop WHERE event_id = '.$this->getActiveEventID(), 'foo')."\n<ul>\n".
212        '<li><a href="'.$this->Path('overview', 'underenrolled').'">Underenrolled</a>: '.$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM '.$this->getAudienceCountSQL().' WHERE (ac IS NULL OR ac < min_attendees) AND event_id = '.$this->getActiveEventID(), 'foo')."\n".
213        '<li><a href="'.$this->Path('overview', 'full').'">Full</a>: '.$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM '.$this->getAudienceCountSQL().' WHERE ac = max_attendees AND event_id = '.$this->getActiveEventID(), 'foo')."\n".
214        '<li><a href="'.$this->Path('overview', 'overenrolled').'">Overenrolled</a>: '.$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM '.$this->getAudienceCountSQL().' WHERE ac > max_attendees AND event_id = '.$this->getActiveEventID(), 'foo')."\n</ul>\n".
215        '<li>Workshop '.$this->getPresenterNamePlural().': '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM presenter JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID(), 'foo')."\n".
216        '<li>Workshop attendees: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM audience JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID(), 'foo')."\n".
217        '<li>Maximum possible attendees: '.(int)$this->getValueFromSQL('SELECT SUM(max_attendees) AS foo FROM workshop WHERE event_id = '.$this->getActiveEventID(), 'foo')."\n".
218        '<li>Complete on-line accounts: '.$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM person WHERE pass IS NOT NULL', 'foo')."\n<ul>\n".
219        '<li>who <a href="'.$this->Path('overview', 'noworkshop').'">aren\'t '.$this->getPresenterNamePlural().' or attendees</a> yet: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM (SELECT id FROM person WHERE pass IS NOT NULL) AS p LEFT JOIN (SELECT attendance.* FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().') AS attendance ON (p.id = attendance.person_id) WHERE workshop_id IS NULL', 'foo')."\n".
220        '<li>who are <a href="'.$this->Path('overview', 'unaffiliated').'">unaffiliated with any school</a>: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM (SELECT id FROM person WHERE pass IS NOT NULL) AS p LEFT JOIN affiliation ON (p.id = affiliation.person_id) WHERE organization_id IS NULL', 'foo')."</ul>\n".
221        '<li>People: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM person WHERE last_name IS NOT NULL', 'foo')."\n<ul>\n".
222        '<li><a href="'.$this->Path('overview', 'noemail').'">Without e-mail addresses</a>: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM person WHERE email IS NULL', 'foo')."\n".
223        '<li><a href="'.$this->Path('overview', 'overcommitted').'">Overcommitted</a>: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM (SELECT person_id,COUNT(*) AS x FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().' GROUP BY person_id) AS enrollments WHERE x > 1', 'foo')."\n</ul>\n".
224        '<li>Rooms: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM room', 'foo').
225        '<ul><li><a href="'.$this->Path('overview', 'roomsinuse').'">In Use</a>: '.(int)$this->getValueFromSQL('SELECT COUNT(*) AS foo FROM room JOIN workshop ON (room_id = room.id) WHERE event_id = '.$this->getActiveEventID(), 'foo').'</li></ul>'.
226        $lists.
227        '</ul>';
228    }
229
230    function getAudienceCountSQL() {
231      return 'workshop LEFT JOIN (SELECT workshop_id, COUNT(*) AS ac FROM audience GROUP BY workshop_id) AS audience_count ON workshop.id = audience_count.workshop_id';
232    }
233
234    function getOvercommittedPeople() {
235      $sql = 'SELECT person.*,workshop_count  AS foo FROM person JOIN (SELECT person_id,COUNT(*) AS workshop_count FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().' GROUP BY person_id) AS enrollments ON (person.id = enrollments.person_id) WHERE workshop_count > 1';
236      $this->prepClass('person');
237      $ps = $this->getSeriesFromSQL($sql, 'JPDD_Person');
238      return '<h3>People attending or presenting more than one workshop</h3>'."\n<ul>\n".
239        join('', array_map(create_function('$x', 'return "<li>".$x->getLinkedTitle();'), $ps))."\n</ul>\n";
240    }
241   
242    function getWorkshopsByAttendance($filter, $title, $measure) {
243      $sql = 'SELECT workshop.*, ac FROM '.$this->getAudienceCountSQL().' WHERE ('.$filter.') AND event_id = '.$this->getActiveEventID().' ORDER BY workshop.title';
244      $this->prepClass('workshop');
245      $us = $this->getSeriesFromSQL($sql, 'JPDD_Workshop');
246      return "<h3>".$title."</h3><ul>\n".join('', array_map(create_function('$x', 'return "<li>".$x->getLinkedTitle()." [".(int)$x->_ac."/".(int)$x->'.$measure.'."]\n";'), $us))."</ul>";
247    }
248    function getUnderenrolledWorkshops() {
249      return $this->getWorkshopsByAttendance('ac IS NULL OR ac < min_attendees', 'Underenrolled workshops', '_min_attendees');
250    }
251    function getFullWorkshops() {
252      return $this->getWorkshopsByAttendance('ac = max_attendees', 'Full workshops', '_max_attendees');
253    }
254    function getOverenrolledWorkshops() {
255      return $this->getWorkshopsByAttendance('ac > max_attendees', 'Overenrolled workshops', '_max_attendees');
256    }
257
258
259    // fudged from JPDD_Category::getList()
260    function getOpenWorkshops() {
261      $cats = $this->getAll('category');
262      $ret = '';
263      reset($cats);
264      while (list(,$cat) = each($cats)) {
265        $wshops = $cat->getM2MPeers('workshop', 'workshop_category', 'Workshops');
266        $wshops = array_filter($wshops, create_function('$x', 'return !$x->isFull();'));
267        if (count($wshops)) {
268          $ret .= '<div class="array"><div class="array-title">'.$cat->getLinkedTitle('title')."</div>\n";
269          $ret .= join('', array_map(create_function('$w', 'return "<h2>".$w->getLinkedTitle()."</h2>\n".$w->getPresentersWithOrgs()."<div class=\"workshop-blurb\">".$w->getDescription()."</div>\n";'), $wshops));
270          $ret .= "</div>\n";
271        }
272      }
273      return $ret;
274    }
275
276    function getUnaffiliatedAccounts() {
277      $sql = 'SELECT person.* FROM person LEFT JOIN affiliation ON (person.id = affiliation.person_id) WHERE organization_id IS NULL AND person.pass IS NOT NULL ORDER BY last_name, first_name';
278      $us = $this->getSeriesFromSQL($sql, 'JPDD_Person');
279      return "<h3>Unaffiliated accounts</h3><ul>\n".join('', array_map(create_function('$x', 'return "<li>".$x->getLinkedTitle()." ".$x->getLinkedEmail()."\n";'), $us))."</ul>";
280    }
281    function getAccountsWithNoWorkshop() {
282      $sql = 'SELECT person.* FROM person LEFT JOIN (SELECT attendance.* FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().') AS attendance ON (person.id = attendance.person_id) WHERE workshop_id IS NULL AND person.pass IS NOT NULL ORDER BY last_name, first_name';
283      $us = $this->getSeriesFromSQL($sql, 'JPDD_Person');
284      return "<h3>Accounts who are not ".$this->getPresenterNamePlural()." or attendees</h3><ul>\n".join('', array_map(create_function('$x', 'return "<li>".$x->getNameWithOrgs()." ".$x->getLinkedEmail()."\n";'), $us))."</ul>";
285    }
286
287    function getPeopleWithNoEmail() {
288      $sql = 'SELECT person.* FROM person WHERE email IS NULL ORDER BY last_name, first_name';
289      $us = $this->getSeriesFromSQL($sql, 'JPDD_Person');
290      return "<h3>People with no E-mail address</h3><ul>\n".join('', array_map(create_function('$x', 'return "<li>".$x->getNameWithOrgs()."\n";'), $us))."</ul>";
291    }
292
293    function getRoomsInUse() {
294      $sql = 'SELECT room.*, workshop.title AS workshop, workshop.id AS workshop_id FROM room JOIN workshop ON (room.id = room_id) WHERE event_id = '.$this->getActiveEventID().' ORDER BY room.title';
295      $this->prepClass('room');
296      $us = $this->getSeriesFromSQL($sql, 'JPDD_Room');
297      return "<h3>Rooms in use:</h3><ul>\n".join('', array_map(create_function('$x', 'global $jpdd; return "<li>".$x->getLinkedTitle()." [<a href=\"".$jpdd->Path("workshop", $x->_workshop_id)."\">".$x->_workshop."</a>]\n";'), $us))."</ul>";
298    }
299
300
301    function getApplicationsWithoutAttendance() {
302      $sql = 'SELECT person.*,application.category AS category FROM person JOIN (SELECT application.*,category.title AS category FROM application LEFT JOIN category ON (category.id = category_id) WHERE event_id = '.$this->getActiveEventID().') AS application ON (person.id = person_id) LEFT JOIN (SELECT attendance.* FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().') AS attendance ON (person.id = attendance.person_id) WHERE attendance.id IS NULL';
303      $us = $this->getSeriesFromSQL($sql, 'JPDD_Person');
304      return "<h3>People who have applied but are not assigned to a workshop</h3><ul>\n".join('', array_map(create_function('$x', 'return "<li>".$x->getNameWithOrgs()." [".$x->_category."]\n";'), $us))."</ul>";
305    }
306    function getAttendanceWithoutApplication() {
307      $sql = 'SELECT person.* FROM person LEFT JOIN (SELECT * FROM application WHERE event_id = '.$this->getActiveEventID().') AS application ON (person.id = person_id) JOIN (SELECT attendance.* FROM attendance JOIN workshop ON (workshop_id = workshop.id) WHERE event_id = '.$this->getActiveEventID().') AS attendance ON (person.id = attendance.person_id) WHERE application.id IS NULL';
308      $us = $this->getSeriesFromSQL($sql, 'JPDD_Person');
309      return "<h3>People who are attending a workshop but who did not apply</h3><ul>\n".join('', array_map(create_function('$x', 'return "<li>".$x->getNameWithOrgs()."\n";'), $us))."</ul>";
310    }
311
312    function getActiveApplicationCategories($event_id) {
313      return $this->getKeyValuePairsFromSQL('SELECT DISTINCT category_id AS id, title FROM application JOIN category ON (category.id = category_id) WHERE event_id = '.(int)$event_id, 'id', 'title');
314    }
315
316
317    function getSchoolOverview() {
318      // check which roles are active for this event:'
319      $roles = $this->getKeyValuePairsFromSQL('SELECT DISTINCT role_id AS id, title FROM person_role JOIN role ON (role.id = role_id) WHERE event_id = '.$this->getActiveEventID(), 'id', 'title');
320      $application_categories = $this->getActiveApplicationCategories($this->getActiveEventID()); 
321      $attendreq = $this->getHashFromSQL('SELECT * FROM attendance_requirements WHERE event_id = '.$this->getActiveEventID(), 'category_id');
322
323      $selectitems = array('organization.id', 'audience', 'presenter');
324      $rolesql = 'SELECT ';
325      reset($roles);
326      while (list($id,$title) = each($roles)) {
327        $rolesql .= 'SUM(CASE WHEN role_id = '.(int)$id.' THEN 1 ELSE 0 END) AS role_'.(int)$id.', ';
328        $selectitems[] = 'role_'.(int)$id;
329      }
330      $rolesql .= 'organization_id FROM person_role WHERE event_id = '.$this->getActiveEventID().'  GROUP BY organization_id';
331
332      $catsql = 'SELECT ';
333      reset($application_categories);
334      while (list($id,$title) = each($application_categories)) {
335        $catsql .= 'SUM(CASE WHEN category_id = '.(int)$id.' THEN 1 ELSE 0 END) AS category_'.(int)$id.', ';
336        $selectitems[] = 'category_'.(int)$id;
337      }
338      $catsql .= 'organization_id FROM application LEFT JOIN affiliation USING(person_id) WHERE event_id = '.$this->getActiveEventID().'  GROUP BY organization_id';
339
340      $sql = 'SELECT organization.title AS title, '.join(', ', $selectitems).'
341FROM (
342(SELECT SUM(CASE WHEN flavor = '.$this->escStr('audience').' THEN 1 ELSE 0 END) AS audience,
343SUM(CASE WHEN flavor = '.$this->escStr('presenter').' THEN 1 ELSE 0 END) AS presenter, organization_id
344FROM attendance JOIN workshop ON (workshop_id = workshop.id) LEFT JOIN affiliation ON (attendance.person_id = affiliation.person_id)
345WHERE event_id = '.$this->getActiveEventID().' GROUP BY organization_id) AS audience
346FULL OUTER JOIN ('.$rolesql.') AS rolecounts ON (rolecounts.organization_id = audience.organization_id)
347FULL OUTER JOIN ('.$catsql.') AS catcounts ON (rolecounts.organization_id = catcounts.organization_id))
348LEFT JOIN organization ON (rolecounts.organization_id = organization.id OR catcounts.organization_id = organization.id OR audience.organization_id = organization.id)
349ORDER BY organization.title';
350      $os = $this->getSeriesFromSQL($sql);
351      $ret = '<table><tr><th>school</th><th>attendees</th><th>'.$this->getPresenterNamePlural().'</th>';
352      reset($roles);
353      while(list($id,$title) = each($roles))
354        $ret .= '<th>Role:<br/>'.htmlentities($title).'</th>';
355      reset($application_categories);
356      while(list($id,$title) = each($application_categories))
357        $ret .= '<th>Subject:<br/>'.htmlentities($title).'</th>';
358      $ret .= '</tr>'."\n<tbody>\n";
359
360
361      reset($os);
362      while(list(,$o) = each($os)) {
363        $ret .= '<tr><td>'.($this->isEmpty($o['title']) ? 'unaffiliated' : 
364                            '<a href="'.$this->Path('organization', $o['id']).'">'.htmlentities($o['title'])."</a>").
365          '</td><td class="attendance">'.(int)$o['audience'].'</td><td class="attendance">'.(int)$o['presenter'].'</td>';
366        reset($roles);
367        while(list($id,$title) = each($roles))
368          $ret .= '<td class="attendance">'.(int)$o['role_'.$id].'</td>';
369        reset($application_categories);
370        while(list($id,$title) = each($application_categories)) {
371          $cur = (int)$o['category_'.$id];
372          $status = '';
373          if (array_key_exists($id, $attendreq)) {
374            $req = $attendreq[$id];
375            if (array_key_exists('maximum', $req) && $cur > $req['maximum'])
376              $status = ' over';
377            if (array_key_exists('minimum', $req) && $cur < $req['minimum'])
378              $status = ' under';
379            $ret .= '<td class="attendance'.$status.'">'.$cur.'</td>';
380          }
381        }
382
383        $ret .= "</tr>\n";
384      }
385
386      return $ret.
387        '</tbody></table>'."\n";
388    }
389
390    function getWorkshopOverviewWithAttendence() {
391      return '<table><tr><th>workshop</th><th>max</th><th>min</th><th>total</th><th>by school</th></tr>'."\n<tbody>\n".
392        join('', array_map(create_function('$w', 'return $w->getOverviewRowWithAttendence();'), $this->getAll('workshop'))).
393        '</tbody></table>'."\n";
394    }
395
396    function isSignupClosed() {
397      return !is_null($this->_signup_closed_message);
398    }
399    function getSignupClosedText() {
400      return $this->_signup_closed_message;
401    }
402
403    function acceptProposal() {
404      if (($this->_type) != 'proposal') {
405        $this->addWarning('You can only accept a proposal.');
406        return '';
407      }
408
409      $proposal = $this->getItem('proposal', $this->_identifier);
410     
411      // must POST
412      if ($_SERVER['REQUEST_METHOD'] != 'POST') {
413        $x = $this->Path('proposal', (int)$proposal->getID());
414        $this->redirectLocal($x);
415        exit;
416      }
417      if (!($this->isAuthenticated() && $this->_authenticated_user->hasAllOfThesePrivileges('Update Workshops'))) {
418        $this->permissionDenied('Not allowed to update workshops!');
419      }
420      // must match form token
421      if (!$this->verifyFormToken()) {
422        $this->addWarning('Form token did not match.');
423        return $proposal->getDetailView();
424      }
425      $workshop = $proposal->createWorkshop();
426      $x = $this->Path('workshop', (int)$workshop->getID());
427      $this->redirectLocal($x);
428      exit;
429    }
430
431
432    function handleSignup() {
433      if (($this->_type) != 'workshop') {
434        $this->addWarning('You can only sign up for a workshop.');
435        return '';
436      }
437      if ($this->isSignupClosed()) {
438        $this->addWarning($this->getSignupClosedText());
439        return '';
440      }
441
442      $workshop = $this->getItem('workshop', $this->_identifier);
443     
444      // must POST
445      if ($_SERVER['REQUEST_METHOD'] != 'POST') {
446        $x = $this->Path('workshop', (int)$workshop->getID());
447        $this->redirectLocal($x);
448        exit;
449      }
450      if (!$this->isAuthenticated()) {
451        // FIXME: should this be $this->permissionDenied() instead?  if not, document why not.
452        $this->addWarning('You must be logged in to sign up for a workshop.');
453        return $workshop->getDetailView();
454      }
455      // must match form token
456      if (!$this->verifyFormToken()) {
457        $this->addWarning('Form token did not match.');
458        return $workshop->getDetailView();
459      }
460
461      if ($_POST['cancel'] == 'cancel') {
462        // this is to cancel a signup.
463        if (!$this->_authenticated_user->isAttendingWorkshop($workshop->getID(), 'audience')) {
464          $this->addWarning('You are not signed up for this workshop at the moment.');
465          return $workshop->getDetailView();
466        }
467        $sql = 'DELETE FROM attendance WHERE workshop_id = '.(int)$workshop->getID().' AND person_id = '.(int)$this->_authenticated_user->getID().' AND flavor = '.$this->escStr('audience');
468        $this->executeSQL($sql);
469      } else {
470        // this is to actually sign up for this course.
471        // authenticated user must not be attending any workshop currently.
472        if ($this->_authenticated_user->getAttendanceCount() > 0) {
473          $this->addWarning('You are already signed up for a workshop.');
474          return $workshop->getDetailView();
475        }
476        // must be room in the workshop.
477        if ($workshop->isFull()) {
478          $this->addWarning('Sorry, '.$workshop->getLinkedTitle().' is already full.  Please choose <a href="'.$this->Path('category').'">another workshop</a>.');
479          return $workshop->getDetailView();
480        }
481        $sql = 'INSERT INTO attendance (workshop_id, person_id, flavor) VALUES ('.(int)$workshop->getID().', '.(int)$this->_authenticated_user->getID().', '.$this->escStr('audience').')';
482        $this->executeSQL($sql);
483      }
484      return $workshop->getDetailView();
485    }
486
487
488    function addOpenWorkshopsToPDF(&$pdf) {
489      $cats = $this->getAll('category');
490      $pdf->newPage();
491      reset($cats);
492      while (list(,$cat) = each($cats)) {
493        $wshops = $cat->getM2MPeers('workshop', 'workshop_category', 'AND event_id = '.$this->getActiveEventID());
494        $wshops = array_filter($wshops, create_function('$x', 'return !$x->isFull();'));
495        if (count($wshops)) {
496          // don't start a section header more than halfway down the
497          // page: (this is a hack)
498          if ($pdf->GetY() >= $pdf->_page_height/2)
499            $pdf->newPage();
500          $pdf->SetFont('helvetica', 'B', 24);
501          $pdf->Cell($pdf->_page_width - 2*$pdf->_margin, 30, html_entity_decode($cat->getTitle()), 1, 1);
502          $pdf->SetY($pdf->GetY() + 20);
503          reset($wshops);
504          while(list(,$w) = each($wshops))
505            $w->writeToPDF($pdf);
506        }
507      }
508      return $ret;
509    }
510
511    function addSignupPDFs(&$pdf) {
512      $workshops = $this->getAll('workshop');
513      reset($workshops);
514      while(list(,$w) = each($workshops))
515        $pdf->addSignupSheet($w);
516    }
517
518    function addDoorPDFs(&$pdf) {
519      $this->prepClass('workshop');
520      // get them again, but ordered by room.
521      $workshops = $this->getSeriesFromSQL('SELECT workshop.* FROM workshop JOIN room ON (workshop.room_id = room.id) WHERE event_id = '.$this->getActiveEventID().' ORDER BY room.title', 'JPDD_Workshop');
522      reset($workshops);
523      while(list(,$w) = each($workshops))
524        $pdf->addDoorPage($w);
525    }
526    function addTablePDFs(&$pdf) {
527      $orgs = $this->getAll('organization');
528      reset($orgs);
529      while(list(,$o) = each($orgs))
530        $pdf->addAttendanceLocationsPage($o);
531    }
532    function addAttendeeIndexPDF(&$pdf) {
533      $attendeecount = $this->getActiveEvent()->participantCount();
534      $pdf->addAttendeeIndex(ceil($attendeecount/400)*2);
535    }
536
537
538    function addAllPDFs(&$pdf) {
539      $this->addDoorPDFs($pdf);
540      $this->addAttendeeIndexPDF($pdf);
541      $this->addSignupPDFs($pdf);
542      $this->addTablePDFs($pdf);
543      $this->addOpenWorkshopsToPDF($pdf);
544    }
545
546    function getFullPDF() {
547      require_once('class.jpdd.pdf.php');
548      $pdf = new JPDD_PDF();
549     
550      $this->addAllPDFs($pdf);
551
552      return $pdf;
553    }
554
555
556    function getMainContentExtended() {
557      if ($this->_action == 'signup') {
558        return $this->handleSignup();
559      } elseif ($this->_action == 'open') {
560        return $this->getOpenWorkshops();
561      } elseif ($this->_action == 'accept') {
562        return $this->acceptProposal();
563      } elseif ($this->_action == 'printout') {
564        $pdf = NULL;
565        $pdfname = array_shift($this->_extra_URI_args);
566        if ($this->_type == 'person') {
567          if ($this->isAuthenticated()) {
568            $subj = $this->getItem('person', (int)$this->_identifier);
569            if ($this->_authenticated_user->canEdit($subj) || ($this->_authenticated_user->getID() == $subj->getID())) {
570              // those who can edit the person are allowed to view their pdfs also.
571              $pdf = $subj->getPDF($pdfname);
572            }
573          } else {
574            $this->permissionDenied('You must be logged in to view an individual printout.');
575          }
576        } elseif ($this->_type == 'workshop') {
577          if ($this->isAuthenticated()) {
578            $subj = $this->getItem('workshop', (int)$this->_identifier);
579            if ($this->_authenticated_user->canEdit($subj) || (in_array($this->_authenticated_user->getID(), array_map(create_function('$x', 'return $this->getID();'), $this->getM2MPeers('person', 'presenter'))))) {
580              // those who can edit the workshop or who are presenting it are allowed to view the relevant pdfs:
581              $pdf = $subj->getPDF($pdfname);
582            }
583          } else {
584            $this->permissionDenied('You must be logged in to view a workshop printout.');
585          }
586        } elseif ($this->_type == 'organization') {
587          if ($this->isAuthenticated()) {
588            $subj = $this->getItem('organization', (int)$this->_identifier);
589            if ($this->_authenticated_user->canEdit($subj)) {
590              // those who can edit the workshop or who are presenting it are allowed to view the relevant pdfs:
591              $pdf = $subj->getPDF($pdfname);
592            }
593          } else {
594            $this->permissionDenied('You must be logged in to view a school printout.');
595          }
596        } else {
597          $pdfs = $this->getPDFFunctions();
598          if (in_array($this->_type, array_keys($pdfs))) {
599            if ($this->isAuthenticated() && $this->_authenticated_user->hasAllOfThesePrivileges('Edit People', 'Update Workshops', 'Edit Categories')) {
600              $f = $pdfs[$this->_type]['function'];
601              require_once('class.jpdd.pdf.php');
602              $pdf = new JPDD_PDF();
603              $this->$f($pdf);
604            } else
605              $this->permissionDenied('This printout is not for you.');
606          } elseif ($this->isEmpty($this->_type)) {
607            if ($this->isAuthenticated() && $this->_authenticated_user->hasAllOfThesePrivileges('Edit People', 'Update Workshops', 'Edit Categories')) 
608              return $this->getPDFLinkList();
609            else
610              $this->permissionDenied('This printout is not for you.');
611          }
612        }
613        if (!is_null($pdf)) {
614          // we've got a $pdf, we should emit it properly.
615          $pdf->Output($pdfname, 'I');
616        }
617      } elseif ($this->_action == 'overview') {
618        // we're using the presence of all three privileges as a proxy for sysadmin status:
619        if ($this->isAuthenticated() && $this->_authenticated_user->hasAllOfThesePrivileges('Update Workshops', 'Edit Categories', 'Edit People')) {
620          return $this->getOverview($this->_type);
621        } else {
622          $this->permissionDenied('You are not authorized to view this overview.');
623        }
624      } elseif ($this->_action == 'managesignups') {
625        if ($this->_type != 'category') 
626          $this->error('You can only manage signups by category at the moment.');
627        // first test if this user is in fact a legit liaison for this organization:
628        if (!$this->isAuthenticated() || !$this->_authenticated_user->hasAnyOfThesePrivileges('Manage Signups'))
629          $this->permissionDenied('You must be logged in to an administrative account to manage signups.');
630       
631        $category = $this->getItem('category', $this->_identifier);
632        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
633          if (!$this->verifyFormToken()) {
634            $this->addWarning('Form token did not match.');
635          } else {
636            $category->handleManageSignupForm();
637          }
638        }
639        return $category->getManageSignupForm();
640       
641      } elseif ($this->_action == 'liaison') {
642        // first test if this user is in fact a legit liaison for this organization:
643        if (!$this->isAuthenticated()) {
644          $this->addWarning('You must be logged in to perform liaison functions.');
645          return '';
646        }
647
648        // 'Manage Signups' is good enough to do this:
649        if ($this->_authenticated_user->hasAllOfThesePrivileges('Manage Signups')) {
650          $org = $this->getItem('organization', $this->_identifier);
651        } else {
652          $role = $this->getAll('role', "WHERE title = 'liaison'");
653          if (count($role) != 1) {
654            $this->addWarning('No liaison role exists.');
655            return '';
656          }
657          $role = $role[0];
658         
659          $orgs = $this->_authenticated_user->getRoleOrgs($role->getID());
660          $orgs = array_filter($orgs, create_function('$x', 'return $x->getID() == '.(int)$this->_identifier.';'));
661          if (count($orgs) != 1) {
662            $this->addWarning('You are not authorized as a liaison for this organization.');
663            return '';
664          }
665          $org = array_pop($orgs);
666        }
667        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
668          if (!$this->verifyFormToken()) {
669            $this->addWarning('Form token did not match.');
670          } else {
671            $org->handleLiaisonForm();
672          }
673        }
674        return $org->getLiaisonForm();
675      }
676      return parent::getMainContentExtended();
677    }
678
679    function getPDFFunctions() {
680      return array('all.pdf' => array('description' => 'All together',
681                                      'function' => 'addAllPDFs'),
682                   'door.pdf' => array('description' => 'Printouts for the doors',
683                                       'function' => 'addDoorPDFs'),
684                   'attendeeindex.pdf' => array('description' => 'Single index of attendees and '.$this->getPresenterNamePlural(),
685                                       'function' => 'addAttendeeIndexPDF'),
686                   'attendance.pdf' => array('description' => 'Attendance sheets for the workshops',
687                                         'function' => 'addSignupPDFs'),
688                   'open.pdf' => array('description' => 'Catalog of all open workshops',
689                                         'function' => 'addOpenWorkshopsToPDF'),
690                   'table.pdf' => array('description' => 'Pages for the entrance table',
691                                        'function' => 'addTablePDFs'));
692    }
693
694    function getPDFLinkList() {
695      $z = $this->getPDFFunctions();
696      return '<ul>'.join('', array_map(create_function('$t, $x', 'global $jpdd; return "<li><a href=\"".$jpdd->Path("printout", $t)."\">".$x["description"]."</a>\n";') , array_keys($z), $z)).'</ul>';
697    }
698
699    function getOverview($which) {
700      global $jpdd;
701      if (is_null($which))
702        $which = '';
703
704      $ret = '<div class="overviewnav">';
705
706      // choice of administrator overviews:
707      $overviews = array('Overview' => '',
708                         'By Workshop' => 'workshop',
709                         'By School' => 'organization');
710     
711      reset($overviews);
712      while (list($t,$v) = each($overviews)) {
713        $ret .= '<a '.($which == $v ? 'class="current" ' : '').'href="'.$this->Path('overview', $v).'">'.$t.'</a> ';
714      }
715      $ret .= '</div>';
716
717      if ($which == 'workshop') {
718        $ret .= $this->getWorkshopOverviewWithAttendence();
719      } elseif ($which == 'organization') {
720        $ret .= $this->getSchoolOverview();
721      } elseif ($which == 'unaffiliated') {
722        $ret .= $this->getUnaffiliatedAccounts();
723      } elseif ($which == 'noworkshop') {
724        $ret .= $this->getAccountsWithNoWorkshop();
725      } elseif ($which == 'underenrolled') {
726        $ret .= $this->getUnderenrolledWorkshops();
727      } elseif ($which == 'full') {
728        $ret .= $this->getFullWorkshops();
729      } elseif ($which == 'overenrolled') {
730        $ret .= $this->getOverenrolledWorkshops();
731      } elseif ($which == 'overcommitted') {
732        $ret .= $this->getOvercommittedPeople();
733      } elseif ($which == 'noemail') {
734        $ret .= $this->getPeopleWithNoEmail();
735      } elseif ($which == 'appliednoattend') {
736        $ret .= $this->getApplicationsWithoutAttendance();
737      } elseif ($which == 'attendnotapplied') {
738        $ret .= $this->getAttendanceWithoutApplication();
739      } elseif ($which == 'roomsinuse') {
740        $ret .= $this->getRoomsInUse();
741      } else {
742        $ret .= $this->getGenericOverview();
743      }
744      return $ret;
745    }
746   
747
748    // new account requirements:
749    // FIXME: these should somehow call on JPDD_Person directly, instead of being here...
750   
751    function getNewAccountFormExtraFields() {
752      $lasterr = (($_SERVER['REQUEST_METHOD'] == 'POST') && $this->isEmpty($_POST['last_name']));
753      $orgs = $this->getAll('organization');
754      //      $this->addSourcedScript('scripts/dkg.base.js');
755      //      $this->addScriptChunk('DKG.onLoadScripts.push(\'DKG.ShowOnValue("", "other", "org_title");\');');
756      $acct = $this->getItem('person', (int)$this->_identifier);
757     
758      return '<label>First name:<br/><input type="text" name="first_name" value="'.htmlentities($this->defaultOnEmpty($_POST['first_name'], $acct->_first_name)).'"/></label><br/>
759'.//<label>Middle name:<br/><input type="text" name="middle_name" value="'.htmlentities($_POST['middle_name']).'"/></label><br/>
760'<label>'.$this->getRequiredFieldLabel().'Last name:<br/>'.
761        ($lasterr ? '<span class="error">Must include a last name</span><br/>' : '').
762'<input type="text" '.($lasterr ? 'class="error" ' : '').'name="last_name" value="'.htmlentities($this->defaultOnEmpty($_POST['last_name'], $acct->_last_name)).'"/></label><br/>
763
764'.$acct->getM2MEditView('organization', 'affiliation', 'Affiliated With', 'Add Affiliation')
765
766        /*
767.'<fieldset><legend>Affiliated With</legend>
768<label><select onchange="DKG.ShowOnValue(this.value, \'other\', \'org_title\');" " name="affiliate_id">
769<option value=""></option>
770'.join("\n",array_map(create_function('$n', 'return "<option value=\"".$n->getID()."\">".htmlentities($n->getTitle())."</option>";'),$orgs)).'
771<option value="other">Other...</option>
772</select></label><br/>
773<label id="org_title">School Name:<br/><input name="org_title" type="text" value="'.htmlentities($_POST['org_title']).'"/></label>
774</fieldset>
775'
776        */
777;
778    }
779    // return an array of update statements:
780    function handleNewAccountFormExtraFields() {
781      return array('first_name = '.$this->stringOrDefault($_POST['first_name']),
782                   'middle_name = '.$this->stringOrDefault($_POST['middle_name']),
783                   'last_name = '.$this->stringOrDefault($_POST['last_name']));
784    }
785    // return true if extra fields are all properly validated and can
786    // continue:
787    function validateNewAccountFormExtraFields() {
788      return (!$this->isEmpty($_POST['last_name']));
789    }
790
791    function showLiasonView() {
792      // for the given user, check to see if they're a liason.
793      $roles = array('Science' => array('required' => true),
794                     'Mathematics' => array('required' => true),
795                     'Social Studies' => array('required' => true),
796                     'English' => array('required' => true),
797                     'Art' => array('required' => false));
798
799      $copy = $roles;
800      $ret = '<table><tr><th>discipline</th><th>first name</th><th>last name</th><th>e-mail address</th></tr><tbody>';
801      $counter = 0;
802      reset($roles);
803      while (list($role,$attr) = each($roles)) {
804        //
805        $ret .= '<tr><td><select name="cat_'.(int)$counter.'">';
806        reset($copy);
807        while (list($cr) = each($copy)) {
808          $ret .= '<option value="'.$cr.'">'.$cr.'</option>';
809        }
810        $ret .= '</select></td><td><input name="first_name_'.$counter.'"/></td><td><input name="last_name_'.$counter.'"/></td><td><input name="email_"'.$counter.'"/></td></tr></tbody></table>';
811
812        $counter++;
813      }
814                     
815    }
816
817    function handleAdditionalNewAccountUpdates($id) {
818      $affiliate = $_POST['affiliate_id'];
819      if ('other' == $affiliate) {
820        if ($this->isEmpty($_POST['org_title'])) {
821          $affiliate = 0;
822        } else {
823          // try to make a new org:
824          require_once('class.jpdd.organization.php');
825          $org = new JPDD_Organization(array());
826          $org->handleCreation('org_');
827          $affiliate = $org->getID();
828        }
829      } else {
830        $rem = array_key_exists('remove_affiliation', $_POST) ? $_POST['remove_affiliation'] : array();
831        if (!is_array($rem)) $rem = array($rem);
832        $add = array_key_exists('add_affiliation', $_POST) ? $_POST['add_affiliation'] : array();
833        if (!is_array($add)) $add = array($add);
834        array_map(create_function('$x', 'global $jpdd; $jpdd->executeSQL("INSERT INTO affiliation (person_id, organization_id) VALUES ('.(int)$id.', ".(int)$x.")");'), array_filter($add, create_function('$x', 'global $jpdd; return !$jpdd->isEmpty($x);')));
835        array_map(create_function('$x', 'global $jpdd; $jpdd->executeSQL("DELETE FROM affiliation WHERE person_id = '.(int)$id.' AND organization_id = ".(int)$x);'), array_filter($rem, create_function('$x', 'global $jpdd; return !$jpdd->isEmpty($x);')));
836      }
837    }
838  }
839}
840
841global $jpdd;
842
843?>
Note: See TracBrowser for help on using the repository browser.