fannie/reports/Store-Specific/WFC/AnnualMeeting/AnnualMeetingDetailPage.php
<?php
include('../../../../config.php');
include_once(__DIR__ . '/../../../../src/SQLManager.php');
include_once(__DIR__ . '/../../../../classlib2.0/FannieAPI.php');
include(__DIR__ . '/../../../../src/Credentials/OutsideDB.tunneled.php');
class AnnualMeetingDetailPage extends FannieRESTfulPage {
protected $header = "Annual Meeting Registration";
protected $title = "Annual Meeting Registration";
function get_id_handler(){
global $FANNIE_TRANS_DB, $FANNIE_OP_DB, $dbc;
$fannie = FannieDB::get($FANNIE_OP_DB);
$cardno = $this->id;
$upc = str_pad($this->id,13,'0',STR_PAD_LEFT);
$matches = array();
if ($this->id !== ''){
$cardP = $fannie->prepare('SELECT CardNo FROM
custdata WHERE CardNo=? AND personNum=1 AND Type=\'PC\'');
$cardR = $fannie->execute($cardP, array($cardno));
if ($fannie->num_rows($cardR) == 0){
$upcP = $fannie->prepare('SELECT card_no
FROM memberCards WHERE upc=?');
$upcR = $fannie->execute($upcP, array($upc));
if ($fannie->num_rows($upcR) > 0){
$upcW = $fannie->fetch_row($upcR);
$cardno = $upcW['card_no'];
}
else
$cardno = False;
}
}
elseif (FormLib::get_form_value('ln') !== ''){
$nameQ = "SELECT CardNo, FirstName, LastName FROM custdata
WHERE personNum=1 AND Type='PC' AND LastName LIKE ?";
$args = array('%'.FormLib::get_form_value('ln').'%');
if (FormLib::get_form_value('fn') !== ''){
$args[] = '%'.FormLib::get_form_value('fn').'%';
$nameQ .= ' AND FirstName LIKE ?';
}
$nameP = $fannie->prepare($nameQ);
$nameR = $fannie->execute($nameP, $args);
while($w = $fannie->fetch_row($nameR))
$matches[$w['CardNo']] = $w['FirstName'].' '.$w['LastName'];
$cardno = False;
}
if (count($matches) == 1){
list($cardno) = array_keys($matches);
$matches = array();
}
$this->card_no = $cardno;
$this->matches = $matches;
return True;
}
function get_view(){
$ret = '<form action="AnnualMeetingDetailPage.php" method="get">';
$ret .= '<b># or UPC</b> <input type="text" name="id" /><br /><br />';
$ret .= '<b>Last Name</b> <input type="text" name="ln" /> ';
$ret .= '<b>First Name</b> <input type="text" name="fn" /> ';
$ret .= '<br /><br />';
$ret .= '<input type="submit" value="Submit" />';
$ret .= '</form>';
return $ret;
}
function get_id_view(){
if ($this->card_no)
return $this->showRegistration($this->card_no);
elseif(count($this->matches) > 0)
return $this->showMultiple($this->matches);
else {
$ret = '<em>No results found</em><hr />';
$ret .= $this->get_view();
return $ret;
}
}
function showRegistration($cn){
global $dbc, $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$fannieDB = FannieDB::get($FANNIE_OP_DB);
// POS registrations from today
$hereQ = "SELECT MIN(tdate) AS tdate,d.card_no,".
$fannieDB->concat('c.FirstName',"' '",'c.LastName','')." as name,
m.phone, m.email_1 as email,
SUM(CASE WHEN charflag IN ('M','V','S') THEN quantity ELSE 0 END)-1 as guest_count,
SUM(CASE WHEN charflag IN ('K') THEN quantity ELSE 0 END) as child_count,
SUM(CASE WHEN charflag = 'M' THEN quantity ELSE 0 END) as chicken,
SUM(CASE WHEN charflag = 'V' THEN quantity ELSE 0 END) as veg,
'pos' AS source
FROM ".$FANNIE_TRANS_DB.$fannieDB->sep()."dlog AS d
LEFT JOIN custdata AS c ON c.CardNo=d.card_no AND c.personNum=1
LEFT JOIN meminfo AS m ON d.card_no=m.card_no
WHERE upc IN ('0000000001041','0000000001042')
AND d.card_no = ?
ORDER BY MIN(tdate)";
$records = array();
$hereP = $fannieDB->prepare($hereQ);
$hereR = $fannieDB->execute($hereP, array($cn));
while($hereW = $fannieDB->fetch_row($hereR)){
$records[] = $hereW;
}
// POS registrations from last 90 days
$hereQ = str_replace('dlog ','dlog_90_view ',$hereQ);
$hereP = $fannieDB->prepare($hereQ);
$hereR = $fannieDB->execute($hereP, array($cn));
while($hereW = $fannieDB->fetch_row($hereR)){
$records[] = $hereW;
}
// online registrations
$q = "SELECT tdate,r.card_no,name,email,
phone,guest_count,child_count,
SUM(CASE WHEN m.subtype=1 THEN 1 ELSE 0 END) as chicken,
SUM(CASE WHEN m.subtype=2 THEN 1 ELSE 0 END) as veg,
'website' AS source
FROM registrations AS r LEFT JOIN
regMeals AS m ON r.card_no=m.card_no
WHERE r.card_no = ?
GROUP BY tdate,r.card_no,name,email,
phone,guest_count,child_count
ORDER BY tdate";
$p = $dbc->prepare($q);
$r = $dbc->execute($p, array($cn));
while($w = $dbc->fetch_row($r)){
$records[] = $w;
}
if (count($records) == 0){
return 'Owner #'.$cn.' is not currently registered
<input type="submit"
onclick="location=\'AnnualMeetingDetailPage.php\';return false;"
value="Go Back" />';
}
$ret = '<table><tr><th>Date</th><th>Chicken</th><th>Veg</th><th>Kids</th><th>Source</th></tr>';
foreach($records as $r){
$ret .= sprintf('<tr><td>%s</td><td>%d</td><td>%d</td>
<td>%d</td><td>%s</td></tr>',
$r['tdate'], $r['chicken'], $r['veg'],
$r['child_count'], $r['source']
);
}
$ret .= '</table>';
$ret .= sprintf('%d %s<br />Ph: %s<br />Email: %s<br />',
$records[0]['card_no'],$records[0]['phone'],
$records[0]['email']);
return $ret;
}
function showMultiple($matches){
$ret = '<b>Multiple matching accounts: </b>';
$ret .= '<select onchange="location=\'AnnualMeetingDetailPage.php?id=\'+this.value;">';
$ret .= '<option>Choose...</option>';
foreach($matches as $cn => $name){
$ret .= sprintf("<option value=%d>%d %s</option>",$cn, $cn, $name);
}
$ret .= '</select><br /><br />';
$ret .= '<input type="submit"
onclick="location=\'AnnualMeetingDetailPage.php\';return false;"
value="Go Back" />';
return $ret;
}
}
FannieDispatch::conditionalExec();