index.php

<?php
$f3=require('lib/base.php');
$f3->set('DEBUG',1);
$f3->config('config.ini');

// MySql settings
$f3->set('DB', new DB\SQL(
   'mysql:host=localhost;port=3306;dbname=ep100',
    'checkinadmin',
    'getRdon3!'
)); 

$f3->route('GET /',
	function($f3) {
$f3->reroute('/home/0');
	}
);

$f3->route('GET /volunteergroups',

	function($f3) {

	
$template=new Template;

		echo $template->render('header.htm');
		
		echo $template->render('volunteergroups.htm');

		echo $template->render('footer.htm');

	}

);
//DEFAULT SEARCH FORM VIEW(HOME)

$f3->route('GET /home/@status',
	function($f3) {

	$db = $f3->get('DB');
	
	//MESSAGING
	$status = $f3->get('PARAMS.status');
	if($status == '1'){
	$f3->set('message', 'Rider(s) successfully checked-in.');
	}elseif($status == '2'){
	$f3->set('message', 'Rider added to database.');
	}elseif($status == '3'){
	$f3->set('message', 'Rider information updated.');
	}elseif($status == '4'){
	$f3->set('message', 'Transfer created.');
	}else{
	$f3->set('message', '');
	}
	
$f3->set('totalriders',$db->exec('SELECT count(LastName) r FROM registrations WHERE TransferFrom IS NULL AND Volunteer = "0"'));
$f3->set('totalriderschecked',$db->exec('SELECT count(LastName) c FROM registrations WHERE BibNumber IS NOT NULL AND BibNumber <>""'));

$f3->set('totalvols',$db->exec('SELECT count(LastName) c FROM registrations WHERE Volunteer = "1" AND VolCheck = "0"'));

$f3->set('totalvolschecked',$db->exec('SELECT count(LastName) c FROM registrations WHERE Volunteer = "1" AND VolCheck = "1"'));
	$template=new Template;
	echo $template->render('header.htm');
    echo $template->render('search.htm');
	echo $template->render('footer.htm');
	}
);



//SEARCH RESULTS VIEW

$f3->route('POST /results',
	function($f3) {
	
	//IF YOU WANT TO GET VALUES FROM SEF URL
	//$f3->route('GET /results/@id',
	//$search = $f3->get('PARAMS.id');
	
	//BACK TO HOME URL VALUE <-- NOT REALLY SURE WHY THIS DOESN'T WORK
	//$f3->set('home',$f3->get('BASE'));
	
	//ASSIGN POST PARAMETERS TO SEARCH VAR
	$search = $f3->get('POST.id');
	
		$db = $f3->get('DB');
	
		//SET VAR VALUE EXAMPLE
		//$f3->set('name','world');

		//CREATE QUERY AND ASSIGN TO DATA SET - OLD WAY
		//$f3->set('result',$db->exec('SELECT RiderID, OrderNum, BibNumber, FirstName, LastName, TicketType, Email, 2011Rider, 2012Rider, 2013Rider FROM registrations WHERE OrderNum LIKE "'.$search.'%" OR LastName LIKE "'.$search.'%" OR FirstName LIKE "'.$search.'%" ORDER BY TicketType, LastName ASC '));

		$onum = substr($search,0,9);
		//echo $onum;
		
		//CREATE QUERY AND ASSIGN TO DATA SET		
		$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');
		$filter = '  OrderNum LIKE "'.$onum.'%" OR LastName LIKE "'.$search.'%" OR FirstName LIKE "'.$search.'%" ';
		//CAN ADD LIMIT PARAM TO OPTION ARRAY, REMEMBER COMMA AFTER ORDER LINE
		$option = array(
            'order' => 'TicketType DESC, LastName ASC'
		);   
		$resultset=$riders->find($filter,$option);
		//HOW MANY RESULTS?
		$count = count($resultset);
		$f3->set('count',$count);
		//SET QUERY VAR
		$f3->set('search',$search);
		$f3->set('result',$resultset);
		
		
		//BUILD DISPLAY TEMPLATES
		$template=new Template;
		echo $template->render('header.htm');
        echo $template->render('results.htm');
		echo $template->render('footer.htm');
	}
);


/*
$f3->route('GET /volunteergroups',

	function($f3) {
	//IF YOU WANT TO GET VALUES FROM SEF URL
	//$f3->route('GET /results/@id',
	//$search = $f3->get('PARAMS.id');
	//BACK TO HOME URL VALUE <-- NOT REALLY SURE WHY THIS DOESN'T WORK
	//$f3->set('home',$f3->get('BASE'));
	//ASSIGN POST PARAMETERS TO SEARCH VAR
	$search = $f3->get('POST.id');
		$db = $f3->get('DB');
		//SET VAR VALUE EXAMPLE
		//$f3->set('name','world');
		//CREATE QUERY AND ASSIGN TO DATA SET - OLD WAY
		//$f3->set('result',$db->exec('SELECT RiderID, OrderNum, BibNumber, FirstName, LastName, TicketType, Email, 2011Rider, 2012Rider, 2013Rider FROM registrations WHERE OrderNum LIKE "'.$search.'%" OR LastName LIKE "'.$search.'%" OR FirstName LIKE "'.$search.'%" ORDER BY TicketType, LastName ASC '));
		$onum = substr($search,0,9);
		//echo $onum;
		//CREATE QUERY AND ASSIGN TO DATA SET		
		$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');
		$filter = '  Volunteer = "1" ';
		//CAN ADD LIMIT PARAM TO OPTION ARRAY, REMEMBER COMMA AFTER ORDER LINE
		$option = array(
            'order' => 'VolGroup ASC ',
			'group' => 'VolGroup'
		);   

		$resultset=$riders->find($filter,$option);

		
//GET NUMBER OF VOLUNTEERS BY GROUP
$volnum = array();
while(!$riders->dry()) {
$volunteers=new DB\SQL\Mapper($f3->get('DB'),'registrations');
$filter = '  Volunteer = "1" ';
		$option = array(
            'order' => 'VolGroup ASC ',
			'group' => 'VolGroup'
		); 
$vnums=$volunteers->find($filter,$option);
$i == 1;
foreach($vnums as $vnum)
$rows=$db->exec('SELECT RiderID FROM registrations WHERE VolGroup = "'.$vnum->VolGroup.'"');
	$volnum[] = count($rows);
	
	$i++;
	 
  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper
  $volunteers->next();
}
			
		//GET # of Volunteers Checked IN
		
		
		//HOW MANY RESULTS?
		$count = count($resultset);
		$f3->set('count',$count);
		//SET QUERY VAR
		$f3->set('search',$search);
		$f3->set('result',$resultset);
		$f3->set('volnum',$volnum);
		
		//BUILD DISPLAY TEMPLATES
		$template=new Template;
		echo $template->render('header.htm');
        echo $template->render('volunteergroups.htm');
		echo $template->render('footer.htm');
	}

);
*/


//SEARCH BY BIB NUMBER VIEW

$f3->route('GET /bibsearch',
	function($f3) {

	$db = $f3->get('DB');

	$template=new Template;
	echo $template->render('header.htm');
    echo $template->render('bibsearch.htm');
	echo $template->render('footer.htm');
	}
);



//BIB SEARCH RESULTS VIEW

$f3->route('POST /bibresults',
	function($f3) {
	
	
	//ASSIGN POST PARAMETERS TO SEARCH VAR
	$search = $f3->get('POST.id');

		$db = $f3->get('DB');
	
		//CREATE QUERY AND ASSIGN TO DATA SET		
		$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');
		$filter = '  BibNumber = "'.$search.'" ';
		//CAN ADD LIMIT PARAM TO OPTION ARRAY, REMEMBER COMMA AFTER ORDER LINE
		$option = array(
            'order' => 'TicketType, LastName ASC'
		);   
		$resultset=$riders->find($filter,$option);
		
		//SET QUERY VAR
		$f3->set('search',$search);
		$f3->set('result',$resultset);		
		
		//BUILD DISPLAY TEMPLATES
		$template=new Template;
		echo $template->render('header.htm');
        echo $template->render('bibresults.htm');
		echo $template->render('footer.htm');
	}
);



//CURRENT RIDE CHECKIN STATS VIEW

$f3->route('GET /stats',
	function($f3) {

	$db = $f3->get('DB');

//10 miler stats
$rows=$db->exec('SELECT LastName FROM registrations WHERE TicketType LIKE "EP100 10 Mile%"');
$t10m = count($rows);
$rows=$db->exec('SELECT LastName FROM registrations WHERE BibNumber IS NOT NULL AND TicketType LIKE "EP100 10 Mile%"');
$t10mcheck = count($rows);
$percent10m = round(($t10mcheck/$t10m)*100)." %";


//32 miler stats

$rows=$db->exec('SELECT LastName FROM registrations WHERE TicketType LIKE "EP100 32 Mile%"');

$t32m = count($rows);

$rows=$db->exec('SELECT LastName FROM registrations WHERE BibNumber IS NOT NULL AND TicketType LIKE "EP100 32 Mile%"');

$t32mcheck = count($rows);

$percent32m = round(($t32mcheck/$t32m)*100)." %";
//54 miler stats
$rows=$db->exec('SELECT LastName FROM registrations WHERE TicketType LIKE "EP100 54 mile%"');
$t50m = count($rows);
$rows=$db->exec('SELECT LastName FROM registrations WHERE BibNumber IS NOT NULL AND TicketType LIKE "EP100 54 mile%"');
$t50mcheck = count($rows);
$percent50m = round(($t50mcheck/$t50m)*100)." %";

//74 miler stats

$rows=$db->exec('SELECT LastName FROM registrations WHERE TicketType LIKE "EP100 74 mile%"');

$t74m = count($rows);

$rows=$db->exec('SELECT LastName FROM registrations WHERE BibNumber IS NOT NULL AND TicketType LIKE "EP100 74 Mile%"');

$t74mcheck = count($rows);

$percent74m = round(($t74mcheck/$t74m)*100)." %";
//100 miler stats
$rows=$db->exec('SELECT LastName FROM registrations WHERE TicketType LIKE "EP100 150K (93.2 miles)%"');
$t100m = count($rows);
$rows=$db->exec('SELECT LastName FROM registrations WHERE BibNumber IS NOT NULL AND TicketType LIKE "EP100 150K (93.2 miles)%"');
$t100mcheck = count($rows);
$percent100m = round(($t100mcheck/$t100m)*100)." %";

//Riders Checked In Before Checkin Dates
$rows=$db->exec('SELECT LastName FROM registrations WHERE DATE_FORMAT(CheckinDate,"%c-%e-%y") <= "9-14-17" and (BibNumber IS NOT NULL AND BibNumber <> "") ');
$totalbefore = count($rows);

//Riders Checked In on Friday
$rows=$db->exec('SELECT LastName FROM registrations WHERE DATE_FORMAT(CheckinDate,"%c-%e-%y") = "9-15-17" and BibNumber IS NOT NULL');
$totalfri = count($rows);

//Riders Checked In on Saturday
$rows=$db->exec('SELECT LastName FROM registrations WHERE DATE_FORMAT(CheckinDate,"%c-%e-%y") = "9-16-17" and BibNumber IS NOT NULL ');
$totalsat = count($rows);

//Riders Checked In on Sunday
$rows=$db->exec('SELECT LastName FROM registrations WHERE DATE_FORMAT(CheckinDate,"%c-%e-%y") = "9-17-17" and BibNumber IS NOT NULL ');
$totalsun = count($rows);


		//SET QUERY VAR
		$f3->set('p10',$percent10m);
		
		$f3->set('p32',$percent32m);
		$f3->set('p50',$percent50m);
		
		$f3->set('p74',$percent74m);
		$f3->set('p100',$percent100m);
		$f3->set('r10r',$t10m);
		
		$f3->set('r32r',$t32m);
		$f3->set('r50r',$t50m);
		
		$f3->set('r74r',$t74m);
		$f3->set('r100r',$t100m);
		$f3->set('r10c',$t10mcheck);
		
		$f3->set('r32c',$t32mcheck);
		$f3->set('r50c',$t50mcheck);
		
		$f3->set('r74c',$t74mcheck);
		$f3->set('r100c',$t100mcheck);
		$f3->set('tbe',$totalbefore);
		$f3->set('tfri',$totalfri);
		$f3->set('tsat',$totalsat);
		$f3->set('tsun',$totalsun);
		

	$template=new Template;
	echo $template->render('header.htm');
    echo $template->render('stats.htm');
	echo $template->render('footer.htm');
	}
);



//ADD RIDER VIEW

$f3->route('GET /add',

	function($f3) {

	$template=new Template;
	echo $template->render('header.htm');
    echo $template->render('addrider.htm');
	echo $template->render('footer.htm');
	}
);


//RIDER DETAIL (& EDIT) VIEW

$f3->route('GET /details/@RiderID',

	function($f3) {

	$db = $f3->get('DB');
	$RiderID = $f3->get('PARAMS.RiderID');

$redit=new DB\SQL\Mapper($f3->get('DB'),'registrations');
$filter = ' RiderID = "'.$RiderID.'"';
    
$rider=$redit->find($filter);
$f3->set('rider',$rider);

	$template=new Template;
	echo $template->render('header.htm');
    echo $template->render('details.htm');
	echo $template->render('footer.htm');
	}
);




//RIDER TRANSFER VIEW

$f3->route('GET /transfer/@RiderID',

	function($f3) {

	$db = $f3->get('DB');
	$RiderID = $f3->get('PARAMS.RiderID');

$redit=new DB\SQL\Mapper($f3->get('DB'),'registrations');
$filter = ' RiderID = "'.$RiderID.'"';
    
$rider=$redit->find($filter);
$f3->set('rider',$rider);

	$template=new Template;
	echo $template->render('header.htm');
    echo $template->render('transfer.htm');
	echo $template->render('footer.htm');
	}
);



//UPDATE GROUP FUNCTIONALITY (STATUS 1)

$f3->route('POST /updategroup',
function($f3) {

	$db = $f3->get('DB');

$R = $f3->get('POST.RiderID');
$B = $f3->get('POST.BibNumber');

$V = $f3->get('POST.VolCheck');
$E = $f3->get('POST.Email');
$i = 0;
$x = 0;

//$f3->set('rider',new DB\SQL\Mapper($f3->get('DB'),'registrations'));

foreach ($R as &$value) {
if($B[$i] != ''){
//CHECK TO SEE IF THEY HAVE BEEN CHECKED IN ALREADY
$rows=$db->exec('SELECT BibNumber FROM registrations WHERE BibNumber = "'.$B[$i].'"');
$exists = count($rows);
if($exists == 0){
$db->exec('UPDATE registrations SET BibNumber = "'.$B[$i].'", Email = "'.$E[$i].'", CheckInDate = now() WHERE RiderID = "'.$value.'"');
}
}

if($V[$i] != '0'){
$db->exec('UPDATE registrations SET VolCheck = "'.$V[$i].'", Email = "'.$E[$i].'", CheckInDate = now() WHERE RiderID = "'.$value.'"');
}

/*
if($V[$i] != '0'){
//CHECK TO SEE IF THE VOLUNTEER HAS BEEN CHECKED IN ALREADY
$rows=$db->exec('SELECT VolCheck FROM registrations WHERE RiderID = "'.$R[$i].'"');
$exists = count($rows);
if($exists == 1){
$db->exec('UPDATE registrations SET VolCheck = "'.$V[$i].'", Email = "'.$E[$i].'", CheckInDate = now() WHERE RiderID = "'.$value.'"');
}
}
*/
$i++;

}

$f3->reroute('/home/1');
	}
);


//CREATE NEW RIDER (STATUS 2)

$f3->route('POST|HEAD /addrider',
function($f3) {

	$db = $f3->get('DB');
	$f3->set('rider',new DB\SQL\Mapper($f3->get('DB'),'registrations'));
	//$f3->get('rider')->load(array('RiderID=?',$f3->get('POST.RiderID')));
	$f3->get('rider')->copyFrom('POST');
	//$f3->get('rider')->update(); 
	$f3->get('rider')->save(); 
	$lastInsertedID = $f3->get('rider')->get('_id');

$BibNumber = $f3->get('POST.BibNumber');
if($BibNumber != ""){
$db->exec('UPDATE registrations SET BibNumber = "'.$BibNumber.'", CheckInDate = now() WHERE RiderID = "'.$lastInsertedID.'"');
}else{
$db->exec('UPDATE registrations SET BibNumber = NULL WHERE RiderID = "'.$lastInsertedID.'"');
}
	
	//echo $db->log();
$f3->reroute('/home/2');
 
	}
);


//UPDATE RIDER FUNCTIONALITY (STATUS 3)

$f3->route('POST|HEAD /updaterider',
function($f3) {

	$db = $f3->get('DB');
	$RiderID = $f3->get('POST.RiderID');

if($f3->exists('POST.RiderID'))
    {
	$f3->set('rider',new DB\SQL\Mapper($f3->get('DB'),'registrations'));
	$f3->get('rider')->load(array('RiderID=?',$f3->get('POST.RiderID')));
	$f3->get('rider')->copyFrom('POST');
	$f3->get('rider')->update(); 
	$f3->get('rider')->save();  
	//echo $db->log();
$f3->reroute('/home/3');
    }
	
	}
);


//NEW TRANSFER (STATUS 4)

$f3->route('POST|HEAD /transferrider',
function($f3) {

	$db = $f3->get('DB');
	$OLDRiderID = $f3->get('POST.TransferFrom');
	
	//CREATE NEW RECORD
	
	$f3->set('rider',new DB\SQL\Mapper($f3->get('DB'),'registrations'));
	$f3->get('rider')->copyFrom('POST');
	$f3->get('rider')->save(); 
	$lastInsertedID = $f3->get('rider')->get('_id');
	
$BibNumber = $f3->get('POST.BibNumber');
if($BibNumber != ""){
$db->exec('UPDATE registrations SET BibNumber = "'.$BibNumber.'", CheckInDate = now() WHERE RiderID = "'.$lastInsertedID.'"');
}else{
$db->exec('UPDATE registrations SET LastUpdate = now() WHERE RiderID = "'.$lastInsertedID.'"');
}
	
	//UPDATE OLD RECORD

if($f3->exists('POST.TransferFrom'))
    {
	$db->exec('UPDATE registrations SET TransferTo = "'.$lastInsertedID.'" WHERE RiderID = "'.$OLDRiderID.'"');  
	//echo $db->log();
$f3->reroute('/home/4');
    }
	
	}
);


//ROUTE FOR CHECKING IF RIDER HAS RIDEN IN THE 2016 EP100



$f3->route('GET /2016rider',

function($f3) {



	$db = $f3->get('DB');

	$i = 0;

	

//GET LIST OF 2015 RIDERS

$pastrider=new \DB\SQL\Mapper($db,'mc-2016riders');

$pastrider->load('');

while(!$pastrider->dry()) {



$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');

$filter = '  LastName = "'.str_replace('"', "", $pastrider->LastName).'" AND FirstName = "'.str_replace('"', "", $pastrider->FirstName).'" ';

//$resultset=$riders->find($filter);

$matches=$riders->find($filter);



foreach($matches as $match)

$db->exec('UPDATE registrations SET 2016Rider = "Yes" WHERE RiderID = "'.$match->RiderID.'"');

  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper

  

  $pastrider->next();

}



 

	

	}

);


//ROUTE FOR CHECKING IF RIDER HAS RIDEN IN THE 2015 EP100



$f3->route('GET /2015rider',

function($f3) {



	$db = $f3->get('DB');

	$i = 0;

	

//GET LIST OF 2015 RIDERS

$pastrider=new \DB\SQL\Mapper($db,'mc-2015riders');

$pastrider->load('');

while(!$pastrider->dry()) {



$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');

$filter = '  LastName = "'.str_replace('"', "", $pastrider->LastName).'" AND FirstName = "'.str_replace('"', "", $pastrider->FirstName).'" ';

//$resultset=$riders->find($filter);

$matches=$riders->find($filter);



foreach($matches as $match)

$db->exec('UPDATE registrations SET 2015Rider = "Yes" WHERE RiderID = "'.$match->RiderID.'"');

  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper

  

  $pastrider->next();

}



 

	

	}

);









//ROUTE FOR CHECKING IF RIDER HAS RIDEN IN THE 2014 EP100



$f3->route('GET /2014rider',

function($f3) {



	$db = $f3->get('DB');

	$i = 0;

	

//GET LIST OF 2014 RIDERS

$pastrider=new \DB\SQL\Mapper($db,'mc-2014riders');

$pastrider->load('');

while(!$pastrider->dry()) {



$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');

$filter = '  LastName = "'.str_replace('"', "", $pastrider->LastName).'" AND FirstName = "'.str_replace('"', "", $pastrider->FirstName).'" ';

//$resultset=$riders->find($filter);

$matches=$riders->find($filter);



foreach($matches as $match)

$db->exec('UPDATE registrations SET 2014Rider = "Yes" WHERE RiderID = "'.$match->RiderID.'"');

  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper

  

  $pastrider->next();

}



 

	

	}

);

//ROUTE FOR CHECKING IF RIDER HAS RIDEN IN THE 2011 EP100

$f3->route('GET /2011rider',
function($f3) {

	$db = $f3->get('DB');
	$i = 0;
	
//GET LIST OF 2011 RIDERS
$pastrider=new \DB\SQL\Mapper($db,'mc-2011riders');
$pastrider->load('');
while(!$pastrider->dry()) {

$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');
$filter = '  LastName = "'.str_replace('"', "", $pastrider->LastName).'" AND FirstName = "'.str_replace('"', "", $pastrider->FirstName).'" ';
//$resultset=$riders->find($filter);
$matches=$riders->find($filter);

foreach($matches as $match)
$db->exec('UPDATE registrations SET 2011Rider = "Yes" WHERE RiderID = "'.$match->RiderID.'"');
  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper
  
  $pastrider->next();
}

 
	
	}
);



//ROUTE FOR CHECKING IF RIDER HAS RIDEN IN THE 2012 EP100

$f3->route('GET /2012rider',
function($f3) {

	$db = $f3->get('DB');
	$i = 0;
	
//GET LIST OF 2012 RIDERS
$pastrider=new \DB\SQL\Mapper($db,'mc-2012riders');
$pastrider->load('');
while(!$pastrider->dry()) {

$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');
$filter = '  LastName = "'.str_replace('"', "", $pastrider->LastName).'" AND FirstName = "'.str_replace('"', "", $pastrider->FirstName).'" ';
//$resultset=$riders->find($filter);
$matches=$riders->find($filter);

foreach($matches as $match)
$db->exec('UPDATE registrations SET 2012Rider = "Yes" WHERE RiderID = "'.$match->RiderID.'"');
  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper
  
  $pastrider->next();
}

 
	
	}
);



//ROUTE FOR CHECKING IF RIDER HAS RIDEN IN THE 2013 EP100

$f3->route('GET /2013rider',
function($f3) {

	$db = $f3->get('DB');
	$i = 0;
	
//GET LIST OF 2013 RIDERS
$pastrider=new \DB\SQL\Mapper($db,'mc-2013riders');
$pastrider->load('');
while(!$pastrider->dry()) {

$riders=new DB\SQL\Mapper($f3->get('DB'),'registrations');
$filter = '  LastName = "'.str_replace('"', "", $pastrider->LastName).'" AND FirstName = "'.str_replace('"', "", $pastrider->FirstName).'" ';
//$resultset=$riders->find($filter);
$matches=$riders->find($filter);

foreach($matches as $match)
$db->exec('UPDATE registrations SET 2013Rider = "Yes" WHERE RiderID = "'.$match->RiderID.'"');
  //echo $i.". ".$match->FirstName." ".$match->LastName."<br>";//db mapper
  
  $pastrider->next();
}

 
	
	}
);

$f3->run();