<?
ini_set('sendmail_from',"info@ravenssun.ca"); 
date_default_timezone_set('America/Moncton');


//DEFINE CONSTANTS HERE -------------------------------------------------------------------------------------
define("DEBUG",false);
define("LOGIN_ERROR", 1);
define("TIMEOUT_ERROR", 2);
define("DIE_ON_ERROR", false);

define("APP_TITLE", "LLNB");
define("ADMIN_EMAIL", "info@ravenssun.ca");

define("LLNB_URL", "http://www.tracking.llnb.ca/");

define("TEXTAREA_MAXLEN_", "200");

//accents to escape before using SQL
define("_CHARS_TO_ESCAPE", "éèêçöü");

//TABLE codes for Adjust setting
$_SETTINGTABLES = array('1'=>'refclientof', 
					'2'=>'disability',
					'3'=>'essentialskills',
					'4'=>'resources',
					'5'=>'areaofinterest',
					'6'=>'reasonforleaving'
					);
					
$_SETTINGTABLES_USR_DISPLAY = array('1'=>'Referral Source/Client Of', 
					'2'=>'Diagnosed Disability',
					'3'=>'Essential Skills',
					'4'=>'Resources',
					'5'=>'Volunteer Areas',
					'6'=>'Reasons For Leaving'
					);
										
$_SETTINGTABLES_RFL = array('1'=>'rfllearner', 
					'2'=>'rfltutor',
					'3'=>'rflcoordinator',
					'4'=>'rflvolunteer'
					);
$_SETTINGTABLES_RFL_USR_DISPLAY = array('1'=>'Learner: Reason For Leaving', 
					'2'=>'Tutor: Reason For Leaving',
					'3'=>'Coordinator: Reason For Leaving',
					'4'=>'Volunteer: Reason For Leaving'
					);
					
$_DOCSNAMES = array(	'1'=>'learner_intake.pdf',
					'2'=>'tutor_intake.pdf',
					'3'=>'volunteer_intake.pdf',
					'4'=>'WOW_portfolio.pdf',
					'5'=>'essential_skills.pdf' 
					);	
					
$_DOCSNAMES_USR_DISPLAY = array(	'1'=>'Learner Intake',
					'2'=>'Tutor Intake',
					'3'=>'Volunteer Intake',
					'4'=>'Wow Portfolio',
					'5'=>'Essential Skills' 
					);		
					
//USER types
$_EXECDIR = 5;
$_COORD = 4;
$_TUTOR = 2;

//this is used to help display easier to read dates
$_MONTHS = array('01'=>'January', '02'=>'February', '03'=>'March', 
				'04'=>'April', '05'=>'May', '06'=>'June', '07'=>'July', '08'=>'August', 
				'09'=>'September', '10'=>'October', '11'=>'November', '12'=>'December');

				
$_MONTHS_abbr_zero = array('01'=>'Jan', '02'=>'Feb', '03'=>'Mar', 
				'04'=>'Apr', '05'=>'May', '06'=>'Jun', '07'=>'Jul', '08'=>'Aug', 
				'09'=>'Sep', '10'=>'Oct', '11'=>'Nov', '12'=>'Dec');
 
$_MONTHS_abbr = array('1'=>'Jan', '2'=>'Feb', '3'=>'Mar', 
				'4'=>'Apr', '5'=>'May', '6'=>'Jun', '7'=>'Jul', '8'=>'Aug', 
				'9'=>'Sep', '10'=>'Oct', '11'=>'Nov', '12'=>'Dec');						

$_LEADINGZEROS = 5;			
$_PAGINATIONLEN = 20;

//RESOURCE OTHER ID
$_RES_OTHER = 95;


//Connect to DB ------------------------------------------------------------------------------------------
static $dbh;


function db_connect($dbname)
{
	global $dbh;

	$dbh = mysql_connect("localhost", "llnb_llnbengusr", "l1l2nb3") 
	
	or trigger_error('I cannot connect to the database because: ' . mysql_error());
	
	
	mysql_select_db($dbname) or trigger_error(mysql_error());
}



function getTable($usertype)
{

	if ($usertype==1)	
		$table="learner"; 
	else if ($usertype==2)
		$table="tutor";
	else if ($usertype==3)
		$table="volunteer";
	else if ($usertype==4)
		$table="coordinator";

	return $table;
}

function createLLNBID($ccode, $currentid)
{

	
	global $_LEADINGZEROS;
	

	$len = strlen($currentid);
	
	for ($i=1; $i<= ($_LEADINGZEROS-$len); $i++)
		$thezeros.= "0";
		
	$theid = $ccode.$thezeros.$currentid;
	return $theid;

}

function constructPagination($rcount, $currpg, $pg, $urlvars)
{
	global $_PAGINATIONLEN;
	
  	if ($rcount <= $_PAGINATIONLEN)
  		return "";
  	
  	//math
  	$tempval= $rcount/$_PAGINATIONLEN;
  	$roundedval=round($tempval);
  	if ($tempval>$roundedval)
  		$pages=$roundedval+1;
  	else
  		$pages=$roundedval;

	
	$controls.= "<p>";

	for ($i=1; $i<=$pages; $i++)
	{
		if ($i==$currpg)
			$on="on";
		else
			$on="";

		$controls.= "<a href=\"".$pg."?p=".$i."&".$urlvars."\" class=\"btn btnpg".$on."\">".$i."</a>";
	}

	$controls.= "</p>";
	return $controls;

}


function buildLimitClause($p)
{
	global $_PAGINATIONLEN;

	//math
	$startpt = ($p-1)*$_PAGINATIONLEN;
	$endpt = $_PAGINATIONLEN;

	$SQL=" LIMIT ".$startpt.", ".$endpt."";
	return $SQL;
	
}

//General Use functions ---------------------------------------------------------------------------------------------
function setDateNullFields($DFIELDS, $lid, $dbtable)
{
	
	for ($i = 0; $i < count($DFIELDS); $i++) 
	{
    		$SQL = "UPDATE ".$dbtable." SET ".$DFIELDS[$i]." = NULL 
    				WHERE ".$DFIELDS[$i]."='0000-00-00' AND  id =".$lid;
		
		$results = llnb_query($SQL);	
	}



}



function printVolunteerHRs($userid, $usertype)
{
	$y=date("Y");
	
	$SQL	=
	"SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `duration` ) ) ) AS total_time
	FROM `volunteersession`
	WHERE YEAR(thedate) = ".$y." AND user_id = ".$userid." AND user_type=".$usertype." AND alive=1";
	
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	$timepieces = explode(":", $row['total_time'] );
	$hrs=$timepieces[0];
	$mins=$timepieces[1];
	
	$hrs=$hrs+0;
	$mins=$mins+0;
	
	if ($hrs < 1)
		$printhrs = "";
	else
		$printhrs= $hrs." hrs";
		
	if ($mins < 1)
		$printmins = "";
	else
		$printmins= $mins." mins";
	
	if (!$printhrs && !$printmins)
		$printtime="0 hrs";
	else
		$printtime = $printhrs." ".$printmins;
		
	$badge = 
	"<div class=\"graybox vol\">
	  	<p>Current year's</br> Volunteering hours</p>
		<span class=\"bignumber\">".$printtime."</span>
	</div>";
	return $badge;
	
}

function printTutorHRs($userid, $stype)
{

	$y=date("Y");

	$SQL	=
	"SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `duration` ) ) ) AS total_time
	FROM `learnersession`
	WHERE  YEAR(thedate) = ".$y." AND  tutor_id = ".$userid." AND stype=".$stype." AND alive=1";
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	$timepieces = explode(":", $row['total_time'] );
	$hrs=$timepieces[0];
	$mins=$timepieces[1];
	
	$hrs=$hrs+0;
	$mins=$mins+0;
	
	if ($hrs < 1)
		$printhrs = "";
	else
		$printhrs= $hrs." hrs";
		
	if ($mins < 1)
		$printmins = "";
	else
		$printmins= $mins." mins";
	
	if (!$printhrs && !$printmins)
		$printtime="0 hrs";
	else
		$printtime = $printhrs." ".$printmins;
	
	if ($stype == 1)
	{	$printstype = "Tutoring"; $c=" tut";}
	else
	{	$printstype = "Prep" ; $c=" prep";}
 
	$badge = 
	"<div class=\"graybox".$c."\">
	  	<p>Current year's </br>".$printstype." hours</p>
		<span class=\"bignumber\">".$printtime."</span>
	</div>";
	return $badge;

}

function printLearnHRs($userid)
{

	$y=date("Y");

	$SQL	=
	"SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `duration` ) ) ) AS total_time
	FROM `learnersession`
	WHERE  YEAR(thedate) = ".$y." AND  learner_id = ".$userid." AND stype=1 AND alive=1";
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	$timepieces = explode(":", $row['total_time'] );
	$hrs=$timepieces[0];
	$mins=$timepieces[1];
	
	$hrs=$hrs+0;
	$mins=$mins+0;
	
	if ($hrs < 1)
		$printhrs = "";
	else
		$printhrs= $hrs." hrs";
		
	if ($mins < 1)
		$printmins = "";
	else
		$printmins= $mins." mins";
	
	if (!$printhrs && !$printmins)
		$printtime="0 hrs";
	else
		$printtime = $printhrs." ".$printmins;
	
	$badge = 
	"<div class=\"graybox learn\">
	  	<p>Current year's </br>Tutored hours</p>
		<span class=\"bignumber\">".$printtime."</span>
	</div>";
	return $badge;

}

function buildCouncilDropdown($csel)
{

	$options = "";

	$SQL="SELECT * FROM council";
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
	    
		if ($row['id'] == $csel)
			$sel="selected";
		else 
		$sel="";

	    
	    $options .= "<option value=\"".$row['id']."\"  ".$sel.">".$row['name']."</option>\n";
	}
	
	return $options;
}


function buildCouncilDropdownALLoption($csel)
{

	$options = "";

	$SQL="SELECT * FROM council";
	$results = llnb_query($SQL);
	
	if (!$csel)
		$csel=-1;
	
	//PROVINCE option
	if ($csel == -1)
	$sel="selected";	else 	$sel="";
	$options .= "<option value=\"-1\"  ".$sel.">All</option>\n";
	
	while($row = llnb_fetch_assoc($results))
	{
	    
		if ($row['id'] == $csel)
			$sel="selected";
		else 
		$sel="";

	    
	    $options .= "<option value=\"".$row['id']."\"  ".$sel.">".$row['name']."</option>\n";
	}
	
	return $options;
}





function fetchCurrentStatus($utype, $id)
{
	$table = getTable($utype);
	
	$SQL="SELECT active FROM ".$table." WHERE id=".$id;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['active'];
}

function fetchCouncilCode($cid)
{
	$options = "";

	$SQL="SELECT code FROM council WHERE id=".$cid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['code'];
}

function fetchCouncilCodeForTutor($id)
{
	$options = "";

	$SQL="SELECT council_id FROM tutor  WHERE id=".$id;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['council_id'];
}

function uType()
{
	if ($_SESSION['ulevel'] == 5)
	{
	
		return "Executive Director";
	
	}
	else if ($_SESSION['ulevel'] == 4)
	{
	
		return "Council Coordinator";
	}
	else if ($_SESSION['ulevel'] == 2)
	{
			return "Tutor";
	}
}





function fetchCouncilName($cid)
{
	$options = "";

	if (!$cid)
		return "";
		
	$SQL="SELECT name FROM council WHERE id=".$cid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	
	if (!$row['name'])
		$row['name']= "All";
		
	return $row['name'];
	
}

function fetchLearnerName($lid)
{
	$SQL="SELECT firstname, lastname FROM learner WHERE id=".$lid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['firstname']." ".$row['lastname'];
}

function fetchProvName($id)
{
	$SQL="SELECT abbrv FROM province WHERE id=".$id;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['abbrv'];
}

function fetchTutorName($lid)
{
	$SQL="SELECT firstname, lastname FROM tutor WHERE id=".$lid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['firstname']." ".$row['lastname'];
}

function fetchExecDirName($lid)
{
	$SQL="SELECT firstname, lastname FROM execdir WHERE id=".$lid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['firstname']." ".$row['lastname'];
}

function fetchAreaOfInterest($aoi_id)
{
	$SQL="SELECT name FROM areaofinterest WHERE id=".$aoi_id;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['name'];
}

function fetchVolName($lid)
{
	$SQL="SELECT firstname, lastname FROM volunteer WHERE id=".$lid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['firstname']." ".$row['lastname'];
}

function fetchCoordName($lid)
{
	$SQL="SELECT firstname, lastname FROM coordinator WHERE id=".$lid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['firstname']." ".$row['lastname'];
}

function fetchLearnerGoal($lgid)
{
	$SQL="SELECT goal FROM learnergoals WHERE id=".$lgid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['goal'];
	
}

function fetchReasonForLeaving($id)
{
	$SQL="SELECT name FROM reasonforleaving WHERE id=".$id;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['name'];
	
}

function buildProvDropdown($provsel)
{

	$options = "";

	$SQL="SELECT * FROM province";
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
	     
	     if (!$provsel)
	     	$provsel=4;
	     else
	     {
			if ($row['id'] == $provsel)
				$sel="selected";
			else 
				$sel="";
	     }
	     	
	     $options .= "<option value=\"".$row['id']."\" ".$sel.">".$row['abbrv']."</option>\n";
	}
	
	return $options;
}

function buildRefSourceDropdown($refsel)
{

	$options = "";

	$SQL="SELECT * FROM refclientof WHERE refclientof.show=1 ORDER by listorder ASC";
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
	     if ($row['id'] == $refsel)
	     	$sel="selected";
	     else 
	     	$sel="";
	     	
	     $options .= "<option value=\"".$row['id']."\" ".$sel.">".$row['name']."</option>\n";
	}
	
	return $options;
}

function buildClientOfDropdown($csel)
{

	$options = "";

	$SQL="SELECT * FROM refclientof WHERE refclientof.show=1 ORDER by listorder ASC";
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
	     if ($row['id'] == $csel)
	     	$sel="selected";
	     else 
	     	$sel="";
	     	
	     $options .= "<option value=\"".$row['id']."\" ".$sel.">".$row['name']."</option>\n";
	}
	
	return $options;
}

function buildVtypeDropdown($vsel)
{

	$options = "";

	$SQL="SELECT * FROM volunteertype";
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
	     if ($row['id'] == $vsel)
	     	$sel="selected";
	     else 
	     	$sel="";
	     	
	     $options .= "<option value=\"".$row['id']."\" ".$sel.">".$row['descr']."</option>\n";
	}
	
	return $options;
}

function buildAreasOfDisability($selectedareas)
{
	$options = "";

	$SQL="SELECT * FROM disability WHERE disability.show=1 ORDER by listorder ASC";
	$results = llnb_query($SQL);
	$options .="<ul>";
	while($row = llnb_fetch_assoc($results))
	{
		if ($selectedareas[$row['id']] == 1)
			$sel = "checked";
		else
			$sel = "";

		$options .= "
            	<li><label class=\"check\"><input name=\"areaofdisability[]\" type=\"checkbox\" ".$sel." value=\"".$row['id']."\">".$row['name']."</label></li>";

	}
	$options.="</ul>";

	return $options;
}


function buildResourceList($sel_resid, $other)
{
	//number of resrouces per column
	$COL_LENGTH = 32;
	global $_RES_OTHER;
	
	$options = "";
	$SQL="SELECT * FROM resources WHERE resources.show=1 ORDER BY listorder ASC";
	$results = llnb_query($SQL);

	$options .= "
	  <div  class=\"input moveright\" style=\"width: 185px;\">            
	    <ul style=\"position: relative;\">";        
	
	$i=1;
	while($row = llnb_fetch_assoc($results))
	{
	     if ($sel_resid == $row['id'])
			$sel = "checked";
		else
			$sel = "";
		
		
		if ($row['id']== $_RES_OTHER)
			$options .= "
 			<li><label class=\"radio\"><input onClick=\"displayOther();\" name=\"resource\" type=\"radio\" id=\"r".$row['id']."\" ".$sel." value=\"".$row['id']."\">".$row['resource']."</label></li>
            	";
		else
			$options .= "
 			<li><label class=\"radio\"><input name=\"resource\" type=\"radio\" id=\"r".$row['id']."\" ".$sel." value=\"".$row['id']."\">".$row['resource']."</label></li>
            	";
            	
		if (($i % $COL_LENGTH) == 0)		
			$options .= "
			   </ul>
					</div>      
				        <div  class=\"input moveright\" style=\"width: 185px;\">            
				         <ul style=\"position: relative;\">";
		$i++;
	}

	if ($sel_resid == $_RES_OTHER)
		$thecss="";
	else
		$thecss="style=\"display: none;\"";
		
		$options .= "
		<li id=\"other\" ".$thecss.">Other: <input name=\"other\" type=\"text\" value=\"".$other."\">
	   </ul>
	</div>"; 

	return $options;
}


function buildAreasOfInterest($selectedareas)
{

	$options = "";

	$SQL="SELECT * FROM areaofinterest WHERE areaofinterest.show=1 ORDER BY listorder ASC";
	$results = llnb_query($SQL);
	$options .="<ul>";
	while($row = llnb_fetch_assoc($results))
	{
		if ($selectedareas[$row['id']] == 1)
			$sel = "checked";
		else
			$sel = "";

		$options .= "
            	<li><label class=\"check\"><input name=\"areaofinterest[]\" type=\"checkbox\" ".$sel." value=\"".$row['id']."\">".$row['name']."</label></li>";

	}
	$options.="</ul>";

	return $options;
}

function buildEssentialSkills($selectedareas)
{

	$options = "";

	$SQL="SELECT * FROM essentialskills WHERE essentialskills.show =1 ORDER BY listorder ASC";
	$results = llnb_query($SQL);
	$options .="<ul>";
	
	while($row = llnb_fetch_assoc($results))
	{
		if ($selectedareas[$row['id']] == 1)
			$sel = "checked";
		else
			$sel = "";

		$options .= "
            	<li><label class=\"check\"><input name=\"eskills[]\" type=\"checkbox\" ".$sel." value=\"".$row['id']."\">".$row['skill']."</label></li>";

	}
	$options.="</ul>";

	return $options;
}

function buildReasonForLeaving($rid, $utype)
{

 	if ( $utype ==2 )
	    $thetable = "rfltutor";
	else if ($utype==1)
	   $thetable = "rfllearner";
	else if ($utype==3)
	   $thetable = "rflvolunteer";                  
	else if ($utype==4)
	   $thetable = "rflcoordinator";           
	
	$options = "";

	$SQL="SELECT * FROM reasonforleaving WHERE id IN (SELECT rfl_id from ".$thetable.") AND reasonforleaving.show=1 ORDER BY listorder ASC";
	$results = llnb_query($SQL);
	$options .="<ul>";
	
	while($row = llnb_fetch_assoc($results))
	{
		if ($row['id'] == $rid)
			$sel = "checked";
		else
			$sel = "";

		$options .= "
            	<li><label class=\"radio\"><input name=\"reason\" type=\"radio\" ".$sel." value=\"".$row['id']."\">".$row['name']."</label></li>";

	}
	$options.="</ul>";

	return $options;
}


function fetchAreasOfInterest($tid, $mode)
{

	$options = "";
	
	if ($mode == "v")
	{
		$table="volunteer_areaofinterest";
		$col=" volunteer_id";
	}
	else
	{
			$table="tutor_areaofinterest";
			$col=" tutor_id";
	}


	
	$SQL="SELECT * FROM ".$table." WHERE ".$col."=".$tid." ORDER by aoi_id";
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
		$AREAS[$row['aoi_id']]=true;
	}
	
	return $AREAS;
}

function fetchAreasOfDisability($lid)
{

	$options = "";
	$SQL="SELECT * FROM  learner_disability 
		WHERE learner_id =".$lid;
		
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
		$AREAS[$row['disability_id']]=true;
	}
	
	return $AREAS;
}





function fetchEssentialSkills($lgid)
{

	$options = "";
	$SQL="SELECT * FROM  learnergoals_essentialskills 
		WHERE lg_id =".$lgid;
	$results = llnb_query($SQL);
	
	while($row = llnb_fetch_assoc($results))
	{
		$AREAS[$row['skill_id']]=true;
	}
	
	return $AREAS;
}



//DATE fucntions *****************************************************
function buildMonthDropdown($monsel, $M)
{

	
	if (!$monsel)
	    $options .= "<option value=\"\" selected> M </option>\n";

	
	for ($i = 1; $i <= 12; $i++)
	{
		if (isset($monsel))
		{
			if ($monsel == $i)
					$sel = "selected";
				else
					$sel = "";
		}

		
	    $options .= "<option value=\"".$i."\" ".$sel.">".$M[$i]."</option>\n";
	 }
	 
	 return $options;
}

function buildDayDropdown($daysel)
{

	if (!$daysel)
	    $options .= "<option value=\"\" selected> D </option>\n";

	for ($i = 1; $i <= 31; $i++)
	{
			
			
			if (isset($daysel))
			{
				if ($daysel == $i)
					$sel = "selected";
				else
					$sel = "";
			}
			/*else
			{
				if ($i == date('j'))
					$sel = "selected";
				else
					$sel = "";
			}
			*/
		
	    $options .= "<option value=\"".$i."\" ".$sel.">".$i."</option>\n";
	 }
	 
	 return $options;

}


function buildYearDropdown($ysel, $yr)
{

	if (!$ysel )
	    $options .= "<option value=\"\" selected> YYYY </option>\n";

	$endY = date('Y');

	for ($i =$endY; $i>=1927; $i--)
	{
	
		//if (!$ysel)
		//	$ysel = $yr;
			
		if ($ysel == $i)
				$sel = "selected";
			else
				$sel = "";
		
	    $options .= "<option value=\"".$i."\" ".$sel.">".$i."</option>\n";
	 }
	 
	 return $options;

}


function buildTimesPerWeekDropdown($tsel)
{


	for ($i = 0; $i <= 14; $i++)
	{
			if ($tsel == $i)
				$sel = "selected";
			else
				$sel = "";
		
	    if ($i == 0)
	    	    $options .= "<option value=\"".$i."\" ".$sel.">Open to Suggestions</option>\n";
	    else
	    	   $options .= "<option value=\"".$i."\" ".$sel.">".$i."</option>\n";
	
	    	 
	 }
	 
	 return $options;

}



function buildFiveYearDropdown($ysel, $yr)
{
	


	for ($i = $yr; $i >= ($yr-5); $i--)
	{
	
		if (!$ysel)
			$ysel = $yr;
			
		if ($ysel == $i)
				$sel = "selected";
			else
				$sel = "";
		
	    $options .= "<option value=\"".$i."\" ".$sel.">".$i."</option>\n";
	 }
	 
	 return $options;

}


function buildGradeDropdown($gsel, $g)
{

	$options .= "<option value=\"0\" ".$sel.">Select one</option>\n";

	for ($i = 1; $i <= 12; $i++)
	{
	
		if (!$gsel)
			$gsel = $g;
			
		if ($gsel == $i)
				$sel = "selected";
			else
				$sel = "";
		if ($i==12)
			$some="Some";
		else
			$some="";
		
	    $options .= "<option value=\"".$i."\" ".$sel.">".$some." Grade ".$i."</option>\n";
	 }
	 
	 return $options;

}






function buildHourDropdown($hrsel)
{


	
	for ($i = 0; $i <= 10; $i++)
	{
	
		if ($hrsel == $i)
			$sel = "selected";
		else
			$sel = "";
	
		$options .= "<option value=\"".$i."\" ".$sel." >".$i." hrs</option>\n";
	 }
	 
	 return $options;

}

function buildMinDropdown($minsel)
{

	if ($minsel == 0)
		$zerosel = "selected";
	else if ($minsel == 15)
		$fifteensel = "selected";
	else if ($minsel == 30)
		$thirtysel = "selected";
	else
		$fortyfivesel = "selected";

	$options.="<option value=\"00\" ".$zerosel.">00 min</option>";
	$options.="<option value=\"15\" ".$fifteensel.">15 min</option>";
	$options.="<option value=\"30\" ".$thirtysel.">30 min</option>";
	$options.="<option value=\"45\" ".$fortyfivesel.">45 min</option>";
	 
	 return $options;

}


function buildEdulevelDropdown($edusel)
{

	if ($edusel == 0)
		$none = "selected";
	else if ($edusel == 1)
		$college = "selected";
	else if ($edusel == 2)
		$uni = "selected";
	else if ($edusel == 3)
		$compcollege = "selected";
	else if ($edusel == 4)
		$compuni = "selected";
	else if ($edusel == 5)
		$comphighschool = "selected";
	else if ($edusel == 6)
		$modhs = "selected";
	else if ($edusel == 7)
		$ged = "selected";	
		
	$options.="	
      <option value=\"0\" ".$none.">Select one</option>
      <option value=\"5\" ".$comphighschool.">Completed Highschool</option>
      <option value=\"1\" ".$college.">Some College</option>
      <option value=\"2\" ".$uni.">Some University</option>
      <option value=\"3\" ".$compcollege.">Completed College</option>
      <option value=\"4\" ".$compuni.">Completed University</option>
       <option value=\"6\" ".$modhs.">Modified High School</option>
      <option value=\"7\" ".$ged.">Ged</option>     
           ";

	 return $options;

}


function phonenumberToPieces($p)
{

	$phonenums[0]=substr($p, 0, 3);
	$phonenums[1]=substr($p, 3, 3);
	$phonenums[2]=substr($p, 6, 4);
	return $phonenums;
}

//*************** REPORT FUNCTIONS **************** **************************************************************************************

function fetchLastGrade($learnerid)
{
	$SQL="SELECT * FROM `learneredu` WHERE learner_id=".$learnerid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['lastgrade'];

}

function fetchLastYearSchool($learnerid)
{
	$SQL="SELECT * FROM `learneredu` WHERE learner_id=".$learnerid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['lastattend'];

}
function fetchEI($learnerid)
{
	$SQL="SELECT * FROM `learneremploy` WHERE learner_id=".$learnerid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['ei'];

}

function fetchRefBy($refid)
{
	$SQL="SELECT * FROM `refclientof` WHERE id=".$refid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['name'];
	

}


function doStatus($report_start, $report_end,$learnerid)
{

	$SQL="SELECT * FROM `status` 
	WHERE user_id = ".$learnerid." AND usertype=1 AND thestatus=0 AND  thedate <= '".$report_end."' AND thedate >= '".$report_start."'
	ORDER BY thedate DESC
	";
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);	
	
	if ($row)
	{	

		$statusrow = "
				<tr>
					<td>
						<p class=\"table-headers\">End Date</p>
					</td>
					<td  colspan=\"5\">
						<p class=\"table-headers\">Reason For Leaving</p>
					</td>
				</tr>
				<tr>
					<td>
						<p class=\"table-content\">".$row['thedate']."</p>
					</td>
					<td  colspan=\"5\">
						<p class=\"table-content\">". fetchReasonForLeaving($row['reason'])." <BR><i>NOTE</i>: ".$row['note']."</p>
					</td>
			</tr>";
	}
	
	
	return $statusrow;
}

function getProgressNotes($lgid)
{
	//fetch open learning goals in range
	$SQL="SELECT * FROM `progressnotes` WHERE learnergoal_id = ".$lgid." AND alive =1";
	$results = llnb_query($SQL);
	$i=1;
	while($row = llnb_fetch_assoc($results))
	{
		$thenotes.= " NOTE ".$i.": ".$row['note']." |";
		$i++;
	}
	
	if ($thenotes)
		return substr($thenotes, 0, -1);
	else
		return "";
	
}

function doLearningPlan($report_start, $report_end, $learnerid)
{

	//fetch open learning goals (and associated notes) in range
	$SQL="SELECT * FROM `learnergoals` 
	WHERE learner_id = ".$learnerid." AND  alive=1 AND completed=0 AND start <= '".$report_end."' AND start >= '".$report_start."'
	ORDER BY start ASC";
	$results = llnb_query($SQL);
	//echo $SQL."<BR>";
	
	while($row = llnb_fetch_assoc($results))
	{
		$thegoals_active .= $row['goal'];	

		$pnotes = getProgressNotes($row['id']);
		if ($pnotes)
			$thegoals_active .= " (".$pnotes.")";
			
		$thegoals_active .= "<BR>";

	}
	if (!$thegoals_active)
		$thegoals_active = "No goals for this learner";



	//fetch completed learning goals (and associated notes) in range
	$SQL="SELECT * FROM `learnergoals` 
	WHERE learner_id = ".$learnerid." AND  alive=1 AND completed=1 AND start <= '".$report_end."' AND start >= '".$report_start."'
	ORDER BY start ASC";
	$results = llnb_query($SQL);
	//echo $SQL."<BR>";
	
	while($row = llnb_fetch_assoc($results))
	{
		$thegoals_completed .= $row['goal'];	

		$pnotes = getProgressNotes($row['id']);
		if ($pnotes)
			$thegoals_completed .= " (".$pnotes.")";
			
		$thegoals_completed .= "<BR>";

	}
	if (!$thegoals_completed)
		$thegoals_completed = "No goals for this learner";




	
	
	//get resources at Begin
	$SQL="SELECT * FROM `learner_resource` WHERE learner_id=".$learnerid." AND alive=1 
		AND start <= '".$report_end."'
	ORDER by start ASC, timestamp ASC";
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);	
	
	if ($row['res_id']>0)
		$beginres= dispResourceName($row['res_id']);
	else
		$beginres = "No resource(s) assigned";
		
		
		
		
	//get open resources in a ordered date array
	$SQL="SELECT * FROM `learner_resource` WHERE learner_id=".$learnerid." AND alive=1 AND completed =0
				AND start <= '".$report_end."' AND start >= '".$report_start."' 
	ORDER by start ASC, timestamp ASC";
	$results = llnb_query($SQL);
	$i=0;
	while($row = llnb_fetch_assoc($results))
	{
		
		
		$startres.= "-".dispResourceName($row['res_id'])."<BR>";
		$i++;
	}
	if (!$startres)
		$startres= "No resource(s) assigned";
		
	//get open resources in a ordered date array
	$SQL="SELECT * FROM `learner_resource` WHERE learner_id=".$learnerid." AND alive=1 AND completed =1
				AND start <= '".$report_end."' AND start >= '".$report_start."' 
	ORDER by start ASC, timestamp ASC";
	$results = llnb_query($SQL);
	$i=0;
	while($row = llnb_fetch_assoc($results))
	{
		$endres.= "-".dispResourceName($row['res_id'])."<BR>";
		$i++;
	}
	if (!$endres)
		$endres= "No resource(s) assigned";		

		
	
	$plan=
		"<tr>
				<td>
				</td>
				<td colspan=\"5\">
					<p class=\"table-headers\">Active Learning Goals</p>
				</td>
			</tr>
			<tr>
				<td>
				</td>
				<td  colspan=\"5\">
					<p class=\"table-content\">".$thegoals_active."</p>
				</td>
			</tr>
			<tr>
				<td>
				</td>
				<td colspan=\"5\">
					<p class=\"table-headers\">Completed Learning Goals</p>
				</td>
			</tr>
			<tr>
				<td>
				</td>
				<td  colspan=\"5\">
					<p class=\"table-content\">".$thegoals_completed."</p>
				</td>
			</tr>
			
			
			<tr>
				<td>
				</td>
				<td>
					<p class=\"table-headers\">Beginning Resources</p>
				</td>
				<td>
					<p class=\"table-headers\">Active Resources</p>
				</td>
				<td>
					<p class=\"table-headers\">Completed Resources</p>
				</td>
				<td>
				</td>
				<td>
				</td>
			</tr>
			<tr>
				<td>
				</td>
				<td>
					<p class=\"table-content\">".$beginres."</p>
				</td>
				<td>
					<p class=\"table-content\">".$startres."</p>
				</td>
				<td>
					<p class=\"table-content\">".$endres."</p>
				</td>
				<td>
				</td>
				<td>
				</td>
			</tr>";
			
			return $plan;

}





function getVolunteeringHours($report_start, $report_end, $council)
{
	$doexec = false;
	
	if ($council)
	{
		$c_clauseT = " AND user_id IN (SELECT id FROM tutor WHERE council_id = ".$council.") ";
		$c_clauseV = " AND user_id IN (SELECT id FROM volunteer WHERE council_id = ".$council.") ";
		$c_clauseC = " AND user_id IN (SELECT id FROM coordinator WHERE council_id = ".$council.") ";
		$doexec = true;
	}
	
	
	//tutor
	$SQL="SELECT (sum(minute(duration))/60) + sum(hour(duration)) as totalhrs
	FROM `volunteersession` 
	WHERE alive=1 AND thedate <= '".$report_end."' AND thedate >= '".$report_start."' AND user_type=2  ".$c_clauseT;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	$tutortotal =  $row['totalhrs'];
	//echo "TUTOR VOL:".$SQL;
	
	//vol
	$SQL="SELECT (sum(minute(duration))/60) + sum(hour(duration)) as totalhrs
	FROM `volunteersession` 
	WHERE alive=1 AND thedate <= '".$report_end."' AND thedate >= '".$report_start."' AND user_type=3  ".$c_clauseV;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	$voltotal =  $row['totalhrs'];

	//coord
	$SQL="SELECT (sum(minute(duration))/60) + sum(hour(duration)) as totalhrs
	FROM `volunteersession` 
	WHERE alive=1 AND thedate <= '".$report_end."' AND thedate >= '".$report_start."' AND user_type=4  ".$c_clauseC;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	$coordtotal =  $row['totalhrs'];

	//exec
	if ($doexec)
	{
		$SQL="SELECT (sum(minute(duration))/60) + sum(hour(duration)) as totalhrs
		FROM `volunteersession` 
		WHERE alive=1 AND thedate <= '".$report_end."' AND thedate >= '".$report_start."' AND user_type=5";
		$results = llnb_query($SQL);
		$row = llnb_fetch_assoc($results);
		$exectotal =  $row['totalhrs'];	
	
	}
	
	$TOTAL = ($tutortotal +$voltotal + $coordtotal +$exectotal);
	
	return $TOTAL;

}


function getTutoringHours($report_start, $report_end, $council, $stype, $learner)
{
	
	if ($council)
	{
		$c_clauseT = " AND tutor_id IN (SELECT id FROM tutor WHERE council_id = ".$council.") ";
	}

	if ($learner)
		$l_clause = " AND learner_id = ".$learner;
	else
		$l_clause = "";
		
	
	$SQL="SELECT (sum(minute(duration))/60) + sum(hour(duration)) as totalhrs
	FROM `learnersession` 
	WHERE stype=".$stype." AND alive=1 ".$l_clause." AND thedate <= '".$report_end."' AND thedate >= '".$report_start."' ".$c_clauseT;
	//echo $SQL."<br><br>";
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['totalhrs'];

}




/*QUERY TO GET avg hours per week WHEN training has occured
SELECT 
(sum(minute(duration))/60) + sum(hour(duration)) as hoursthatweek
	FROM `learnersession` 
	WHERE stype=1 AND alive=1 AND learner_id = 18 AND thedate <= '2012-12-30' AND thedate >= '2012-1-1'
    group by week(thedate)

*/

function getWeekCount($report_start, $report_end)
{
	$SQL= "SELECT ROUND(DATEDIFF('".$report_end."', '".$report_start."')/7, 0) AS weekcount";
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);
	return $row['weekcount'];

}

function isPersonInReportRange($id, $rdate_start, $rdate_end, $ut)
{

	$pieces = explode("-", $rdate_start);
	$thestart = mktime(0, 0, 0, $pieces[1], $pieces[2], $pieces[0] );
	$pieces = explode("-", $rdate_end);
	$theend = mktime(0, 0, 0, $pieces[1], $pieces[2], $pieces[0] );

	
	$SQL="select * from status where usertype=".$ut." AND user_id =  ".$id." order by thedate ";
	$results = llnb_query($SQL);
	//stuff the records in a data strcuture
	$i=0;
	while($row = llnb_fetch_assoc($results))
	{
		$THESTATUS[$i]=$row['thestatus'];
		
		//dates to time
		$pieces = explode( "-", $row['thedate']);
		$timeinsec = mktime(0, 0, 0, $pieces[1], $pieces[2], $pieces[0] );
		$THEDATES[$i]=$timeinsec;
		
		$THEIDS[$i]=$row['id'];
		$i++;
	}
	
	
	//analyze the date information based on the report range
	for( $x=0; $x<$i; $x++)
	{
		
		$winner = false;
		
		if ($THESTATUS[$x]==1)
		{
			
			//echo "Person:".$id."Active date:".$THEDATES[$x]." -> Inactive date:".$THEDATES[$x+1]." **** Report start:".$thestart."-> Report end:".$theend."<BR>";
			
			if ($THEDATES[$x] <= $theend)
			{
				//if no next status, and the active date is less the end of range
				if ( !isset($THESTATUS[($x+1)]) )
					$winner= true;
				else if ( ( isset($THESTATUS[$x+1]) && $THESTATUS[$x+1]==0 ) && ($THEDATES[($x+1)] >= $thestart) )
					$winner= true;	
			}
		}
		
		if ($winner == true)
			return true;
	
	}
}

//***************************************************************************************************************************











//Make user input SQL safe (ie. no injection possible) 
function secure_user_input($value)
{
   //Stripslashes from Get, Post, Cookie
   if (get_magic_quotes_gpc()) 
       $value = stripslashes($value);
   
   $value = mysql_real_escape_string($value);
     
   
   return $value;
}


function displayMoney($amount, $type="USD", $prefix=false)
{
	$symbol="";
	
	if ($prefix)
	{	
		if ($type == "USD")
			$symbol = "$";
		else if ($type == "GBP")
			$symbol = "&pound";
		else
			$symbol="";
	}

	return $symbol.number_format(($amount),2,".","");
}


function insertMoney($amount)
{
	return number_format(($amount*100), 0, ".","");
}


//build a Yes No select box
function buildYesNo($arg)
{
	if ($arg)
		$yes_sel="selected";
	else
		$no_sel="selected";
		
	echo "<option ".$yes_sel." value=\"1\">Yes</option>
		  <option ".$no_sel." value=\"0\">No</option>";
}


function dispDateNoTime($thedate)
{

	global $_MONTHS_abbr;
	
	$datepieces = explode("-", $thedate);
	$mydate = $_MONTHS_abbr[$datepieces[1]]." ".$datepieces[2].", ".$datepieces[0];
	return $mydate;
}


function dispDate($thedatetime, $disptype=0)
{
	
	if (!$thedatetime)
		return "";
		
	global $_MONTHS_abbr_zero;
	
	$temp = explode(" ", $thedatetime);
	$thetime= $temp[1];
	$thedate= $temp[0];
	
	$datepieces = explode("-", $thedate);
	$mydate = $_MONTHS_abbr_zero[$datepieces[1]]." ".$datepieces[2].", ".$datepieces[0];
	
	if ($disptype)
		return $mydate." at ".$thetime;
	else
		return $mydate;

}


function dispPhone($p)
{

	
	if ($p)
	{
		$phonenums[0]=substr($p, 0, 3);
		$phonenums[1]=substr($p, 3, 3);
		$phonenums[2]=substr($p, 6, 4);
		$ph = "(".$phonenums[0].") ".$phonenums[1]."-".$phonenums[2];
	}
	else
		$ph= "(none on file)";
		
	return $ph;
}


function dispResourceName($resid)
{


	$SQL="SELECT * FROM resources WHERE id=".$resid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_assoc($results);

	return $row['resource'];
}














// SESSION Functions	-----------------------------------------------------------------------------------------------------------------------------

//session debug
function dump_session()
{
	
	foreach($_SESSION as $thekey => $v)
	{
	   echo "Session var '".$thekey."': ".$v."<br>";
	}

}

function kill_session()
{
	session_unset(); 
	
	foreach ($_SESSION as $thekey => $v)
	   unset($_SESSION[$thekey]);	
	   
	session_destroy();
	$_SESSION = array(); 

}

function llnb_session_set($name, $value)
{
	$_SESSION[$name] = $value;
}

function llnb_session_get($name)
{
	if(array_key_exists($name, $_SESSION))
		return $_SESSION[$name];
	else
		return "";
}




//---------------------------- Database functions -------------------------------------------------------------

function llnb_query($query)
{
	$res= NULL;
	
	if ($query)
	{
		$res = mysql_query($query);
				
		if (mysql_errno()== 0) 
			return $res;
		else
			return NULL;
	}
	else 
		return NULL;
		
}


function llnb_num_rows(&$res)
{
	return mysql_num_rows($res);
}

function llnb_fetch_row(&$res)
{
	return mysql_fetch_row($res);
}

function llnb_fetch_assoc(&$res)
{
	return mysql_fetch_assoc($res);
}

function llnb_get_affected_rows()
{
	return mysql_affected_rows();
}

function llnb_free_result(&$res)
{
	return mysql_free_result($res);
}

function llnb_insert_id()
{
	return mysql_insert_id();
}

function llnb_sql_error()
{
	return mysql_error();
}


function llnb_display_error($err)
{
	echo "<BR>Error! Please report this to site admin: ".$err."<BR>";
	echo "At:  ".date("F j, Y, g:i a")."<BR>\n\n";
}











function getAttachedFileCat($id)
{

	$suffix  = ($_SESSION['lang_id'] == FRENCH_ID) ? $suffix="_fr" : $suffix="";

	$SQL="SELECT title".$suffix."
		FROM attachedfilecat WHERE id=".$id ;
		
	$results = llnb_query($SQL);
	$row = llnb_fetch_row($results);
	return $row[0];
}


function currentRegYear()
{
	$currMonth = date("n");

	if ($currMonth == 1 || $currMonth ==2 || $currMonth ==3 )
		$reg_year = date("Y")-1;
	else
		$reg_year = date("Y");
	
	return $reg_year;
}


//-----------------------

function dispAttachedFileName($fid)
{
	$SQL=" SELECT userfilename FROM attachedfile WHERE id = ".$fid;
	$results = llnb_query($SQL);
	$row = llnb_fetch_row($results);
	
	$descr=$row[0];
		
	if (strlen($descr) > 30)
		$dotdotdot="...";
	else
		$dotdotdot="";
	
	return substr($row[0], 0, 30).$dotdotdot;
}


function assembleUploadedFilename($id, $fcat, $ext, $maxid)
{

	$fn = date("Y")."-".date("m")."-".date("d")."_".$id."_".$fcat."_".$maxid.".".strtolower($ext);
	
	return  $fn;
}


/////////////////// ****************  DEBUG functions *********************** ///////////////////////////////////////////
function debug_out()
{
	print "<BR>  \$_COOKIE = ";
	print_r($_COOKIE);
	print "<BR>  \$_SESSION = ";
	print_r($_SESSION);
	print "<BR>  \$_GET = ";
	print_r($_GET);
	print "<BR>  \$_POST = ";
	print_r($_POST);
	print "<BR> \$_SERVER = ";
	print_r($_SERVER);
	print "<BR>";

}


//connect to database  OLD BD NAME  raven_LLNBeng
db_connect("llnb_live2012");

?>