<?php
/**
* Data Access Object
*
* PHP version 5
*
* LICENSE: This source file is subject to LGPL license
* that is available through the world-wide-web at the following URI:
* http://www.gnu.org/copyleft/lesser.html
*
* @author Antonio Alcorn
* @author Giovanni Capalbo
* @author Sylvia Hristakeva
* @author Kumud Nepal
* @author Ernel Wint
* @copyright Lanka Software Foundation - http://www.opensource.lk
* @copyright Trinity Humanitarian-FOSS Project - http://www.cs.trincoll.edu/hfoss
* @package sahana
* @subpackage vm
* @tutorial
* @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General
* Public License (LGPL)
*/
/**
* The DAO class is designed to handle the retrieval and manipulation
* of information from the database.
*/
class DAO
{
private $db; //a reference to Sahana's database object
/**
* Constructor that stores Sahana's object object
*
* @access public
* @param $db - reference to sahana's database
* @return void
*/
function DAO(&$db)
{
$this->db = &$db;
}
/**
* Performs queries to Sahana's database
*
* @access public
* @param $query - the query string
* @return an ADODB query result object
*/
function execute($query)
{
//optionally show the query if desired
if(debug_show_queries)
{
global $query_count;
echo (++$query_count). " $query<br />\n";
}
return $this->db->Execute($query);
}
/**
* Returns information about a specific volunteer
*
* @param $id - the volunteer's p_uuid
* @return an associative array, with the following structure:
* Array
* (
* 'full_name' => volunteer's full name,
* 'ids' => an array of IDs for the volunteers where each key
* is an ID type ('nic', 'dln', etc.) and each value
* is the corresponding serial number
* 'locations' => an array specifying the volunteer's location, where
* each key is numerically-based (starting from 0), and each
* value is a 'loc_uuid' from the 'location' table; the
* locations are specified from most specific to most
* general (e.g., New York City, New York, USA)
* 'proj_id' => an array of project IDs that the volunteer is currently assigned to
* 'gender' => the volunteer's gender
* 'dob' => the volunteer's date of birth
* 'date_start' => the day that the volunteer can begin working
* 'date_end' => the last day that the volunteer can work
* 'hour_start' => the time of day that the volunteer can begin working
* 'hour_end' => the time of day that the volunteer must stop working
* 'contact' => an array of contact information for the volunteer, where each
* key is an 'opt_contact_type' from the 'field_options' table
* and each value is the corresponding contact information
* 'occupation' => the volunteer's occupation
* 'affiliation' => the organization that the volunteer is affiliated with
* 'status' => either 'Assigned' or 'Unassigned', whether the volunteer has
* been assigned to a project
* 'messages' => the number of unread messages for this volunteer
* )
*/
function getVol($id) //
{
$info = array();
// full name
$result = $this->execute("select p_uuid, full_name from person_uuid where p_uuid = '$id'");
if($result->EOF)
return false;
$info["full_name"] = $result->fields["full_name"];
// IDs and ID types
$result = $this->execute("select opt_id_type, serial from identity_to_person where p_uuid = '" . $id. "'");
$info["ids"]= array();
while(!$result->EOF)
{
$info["ids"][$result->fields["opt_id_type"]] = $result->fields["serial"];
$result->moveNext();
}
// locations
$result = $this->execute("select location_id from location_details where poc_uuid = '" . $id . "'");
$locations = array();
if (!$result->EOF)
{
$lastId = $result->fields["location_id"];
do
{
$locations[]= $lastId;
$result= $this->execute("select parent_id from location where loc_uuid = '$lastId'");
$lastId = $result->fields["parent_id"];
}
while($lastId!=NULL && $lastId!='NULL' && !$result->EOF);
}
$info['locations'] = $locations;
// get project[s] to which they are assigned
// a volunteer may be assigned to more than one project
$info['proj_id'] = $info['pos_id'] = array();
$result = $this->execute("select pos_id, proj_id from vm_vol_assignment_active where p_uuid = '$id'");
while(!$result->EOF) {
$info['pos_id' ][] = $result->fields['pos_id'];
$info['proj_id'][] = $result->fields['proj_id'];
$result->MoveNext();
}
//get the gender, birth date, and occupation
$result = $this->execute("select opt_gender, birth_date, occupation from person_details where p_uuid = '$id'");
$info['gender'] = $result->fields['opt_gender'];
$info['dob'] = $result->fields['birth_date'];
$info['occupation'] = $result->fields['occupation'];
$info['status'] = $this->volIsAssignedToProject($id)?'Assigned':'Unassigned';
//get work times and organization affiliation
$result= $this->execute(" select date_avail_start, date_avail_end, hrs_avail_start, hrs_avail_end, org_id from vm_vol_active where p_uuid = '$id' ");
$info['date_start'] = $result->fields['date_avail_start'];
$info['date_end'] = $result->fields['date_avail_end'];
$info['hour_start'] = $result->fields['hrs_avail_start'];
$info['hour_end'] = $result->fields['hrs_avail_end'];
$info['affiliation'] = $result->fields['org_id'];
//all the contact information
$result = $this->execute("select opt_contact_type, contact_value from contact " .
"where pgoc_uuid = '$id'");
$contacts=array();
while(!$result->EOF)
{
$contacts[$result->fields['opt_contact_type']] = $result->fields['contact_value'];
$result->moveNext();
}
$info['contact'] = $contacts;
//number of unread messages
$info['messages'] = $this->getUnreadMessages($id);
//get special needs of volunteers, if any
$result = $this->execute("SELECT special_needs FROM vm_vol_details WHERE p_uuid='$id'");
$info['special_needs']=$result->fields['special_needs'];
return $info;
}
/**
* Gets the ID types available for anyone in the Sahana system
*
* @access public
* @return $id_types - an associative array, where each key is the shorthand
* identifier for the ID type, and each value is the
* corresponding description of the ID (e.g., 'dln' =>
* 'Driver's License Number')
*/
function getIdTypes() //
{
// get the ID codes and names from the 'field_options' table
$result = $this->execute("select option_code, option_description from field_options where field_name = 'opt_id_type'");
$id_types = array();
while(!$result->EOF)
{
$id_types[$result->fields['option_code']]= $result->fields['option_description'];
$result->moveNext();
}
return $id_types;
}
/**
* Function removes, by default, the numerically indexed keys of an array, or optionally the associative ones.
* this is because the database object returns arrays with both.
*
* @param $a - the array to remove keys from
* @param $type - the type of keys to remove; either MYSQL_NUM or MYSQL_ASSOC
*/
function remove_keys(&$a, $type=MYSQL_NUM) //
{
$count = count($a);
if($type == MYSQL_NUM){
for($i = 0; $i<$count/2; $i++){
unset($a[$i]);
}
}
else
{
$result = array();
for($i=0; $i<count($a)/2; $i++)
$result[$i] = $a[$i];
$a = $result;
}
}
/**
* Retrieves general location information about a specific location.
*
* @access public
* @param $loc_uuid - the location ID to look up
* @return an associative array, with the following structure:
* Array
* (
* 'opt_location_type' => the location level,
* 'name' => the location's name,
* 'iso_code' => the location's ISO code,
* 'description' => a brief description about the location
* )
*/
/*
*
*/
function getLocation($loc_uuid) //
{
// get the information from the 'location' table
$result = $this->execute("select opt_location_type, name, iso_code, description from location where loc_uuid = '$loc_uuid'");
$this->remove_keys($result->fields);
return $result->fields;
}
/**
* Retrieves the names and location IDs of a location and all parent locations
*
* @param $child - the Id of the location to look up
* @return an array, where each key is each location ID and each value is the name of the location; results
* are sorted from most specific to most general
*/
function getParentLocations($child, &$locs=array())
{
$result = $this->execute("SELECT name, parent_id FROM location WHERE loc_uuid = '$child'");
if(!$result->EOF)
$locs[$child] = $result->fields['name'];
if($result->fields['parent_id'] == null)
return $locs;
else
return $this->getParentLocations($result->fields['parent_id'], $locs);
}
/**
* Retrieves all contact types and their descriptions.
*
* @access public
* @return an associative array, where each key is the shorthand name for the contact
* and each value is the corresponding description for that contact type
* (e.g., 'emai' => 'Email Address')
*/
function getContactTypes() //
{
$result = $this->execute("select option_code, option_description from field_options where field_name = 'opt_contact_type'");
$contact_types = array();
while(!$result->EOF)
{
$contact_types[$result->fields['option_code']]= $result->fields['option_description'];
$result->moveNext();
}
return $contact_types;
}
/**
* Retrieves the hours and payrate of a volunteer for each position he is assigned in each project/
* Specifying both $proj_id and $org_id is ok
*
* @access public
* @param $p_uuid - the p_uuid of the volunteer
* @param $proj_id - (optional) specify this to only return results from this project
* @return an array with the following structure:
*
* Array
* (
* proj_id =>
* Array
* (
* 'project_name' => the name of the project
* pos_id =>
* Array
* (
* 'title' => the position title
* 'hours' => the total number of hours worked
* 'payrate' => the volunteer's hourly payrate
* 'status' => the volunteer's status for this position
* )
* )
* )
*/
function getVolHoursAndRate($p_uuid, $proj_id=null)
{
//get all of the volunteer's positions' information
$q = "SELECT proj_id, project_name, status, pos_id, title, payrate
FROM vm_vol_assignment
WHERE p_uuid = '$p_uuid' ";
if(!is_null($proj_id))
$q .= " AND proj_id = '$proj_id' ";
$r = $this->execute($q);
$info = array();
while(!$r->EOF)
{
if(!isset($info[$r->fields['proj_id']]))
$info[$r->fields['proj_id']] = array('project_name' => $r->fields['project_name']);
$info[$r->fields['proj_id']][$r->fields['pos_id']] = array
(
'title' => $r->fields['title'],
'payrate' => $r->fields['payrate'],
'status' => $r->fields['status']
);
$r->MoveNext();
}
//get the volunteer's hours worked for each project position
foreach($info as $proj_id => $positions)
{
foreach($positions as $pos_id => $pos_info)
{
if($pos_id != 'project_name')
{
$t = $this->execute( "SELECT SUM(HOUR(TIMEDIFF(shift_end, shift_start))) hours, SUM(MINUTE(TIMEDIFF(shift_end, shift_start))) minutes, SUM(SECOND(TIMEDIFF(shift_end, shift_start))) seconds
FROM vm_hours
WHERE p_uuid = '$p_uuid'
AND pos_id = '$pos_id'");
$hours = $t->fields['hours'];
$minutes = $t->fields['minutes'];
$seconds = $t->fields['seconds'];
$info[$proj_id][$pos_id]['hours'] = $hours + $minutes / 60 + $seconds / 3600;
}
}
}
return $info;
}
/**
* Retrieve a list of volunteers and some related information
*
* @param $proj_id (optional) - give a value to return volunteers only from this project
* @param $assigned (optional, default VM_SHOW_ALL_VOLUNTEERS) -
* VM_SHOW_ALL_VOLUNTEERS_ASSIGNED to return only assigned volunteers
* VM_SHOW_ALL_VOLUNTEERS_UNASSIGNED to return only unassigned volunteers
* VM_SHOW_ALL_VOLUNTEERS to return all volunteers
*
* @return an array, where each key is a volunteer's p_uuid and each value is the following array of information:
* Array
* (
* 'full_name' => the volunteer's full name
* 'ids' => an array of the volunteer's IDs, where each key is
* the full description for ID type and each value is the ID code
* 'skills' => an array of skill codes => descriptions for the volunteer
* 'date_start' => the volunteer's available start date
* 'date_end' => the volunteer's available end date
* 'locations' => an array of the volunteer's location and its parents
* 'affiliation' => the organization that the volunteer is affiliated with
* )
*/
function getVolunteers($proj_id=null, $assigned=VM_SHOW_ALL_VOLUNTEERS)
{
if(is_null($proj_id))
{
if($assigned == VM_SHOW_ALL_VOLUNTEERS_ASSIGNED)
$whereClause = " WHERE vm_vol_active.p_uuid IN (SELECT DISTINCT p_uuid FROM vm_vol_assignment_active)";
else if($assigned == VM_SHOW_ALL_VOLUNTEERS_UNASSIGNED)
$whereClause = " WHERE vm_vol_active.p_uuid NOT IN (SELECT DISTINCT p_uuid FROM vm_vol_assignment_active)";
else
$whereClause = " ";
}
else
{
$whereClause = " WHERE vm_vol_active.p_uuid IN (SELECT p_uuid FROM vm_vol_assignment_active WHERE proj_id='$proj_id')";
}
$q = "SELECT vm_vol_active.p_uuid, person_uuid.full_name, vm_vol_assignment_active.pos_id, vm_vol_assignment_active.status pos_status, vm_vol_assignment_active.proj_id, location_id, opt_id_type, serial, org_id, org_main.name org_name, option_description id_desc, date_avail_start, date_avail_end
FROM vm_vol_active LEFT JOIN person_uuid USING (p_uuid)
LEFT JOIN vm_vol_assignment_active USING (p_uuid)
LEFT JOIN location_details ON (location_details.poc_uuid = person_uuid.p_uuid)
LEFT JOIN identity_to_person USING (p_uuid)
LEFT JOIN org_main ON (vm_vol_active.org_id = org_main.o_uuid)
LEFT JOIN field_options ON (identity_to_person.opt_id_type = field_options.option_code) "
. $whereClause;
$result = $this->execute($q);
// put all information into an array for returning
$volunteers = array();
while(!$result->EOF)
{
//first check to see if we already started this volunteer's information
//if so, just update the volunteer's info with the info from this next row because
//since we are doing so many joins, we may have more than one row returned per volunteer
$p_uuid = $result->fields['p_uuid'];
if(isset($volunteers[$p_uuid]))
{
//update the existing entry for the volunteer
if($result->fields['serial'] != null)
$volunteers[$p_uuid]['ids'][$result->fields['id_desc']] = $result->fields['serial'];
if($result->fields['org_name'] != null)
$volunteers[$p_uuid]['affiliation'] = $result->fields['org_name'];
if($result->fields['pos_id'] != null && $result->fields['pos_status'] == 'active')
$volunteers[$p_uuid]['pos_id'][] = $result->fields['pos_id'];
}
else
{
//create a new entry in the array for the volunteer
if(empty($result->fields['location_id']))
$locations = array();
else
$locations = $this->getParentLocations($result->fields['location_id']);
$volunteers[$result->fields['p_uuid']] = array
(
'full_name' => $result->fields['full_name'],
'ids' => ($result->fields['serial']==null)?array():array($result->fields['id_desc'] => $result->fields['serial']),
'locations' => $locations,
'affiliation' => ($result->fields['org_name']==null)?'':$result->fields['org_name'],
'skills' => $this->getSkillsAndDescriptions($result->fields['p_uuid']),
'date_start' => $result->fields['date_avail_start'],
'date_end' => $result->fields['date_avail_end'],
'pos_id' => ($result->fields['pos_id'] != null && $result->fields['pos_status'] == 'active')?array($result->fields['pos_id']):array()
);
if($result->fields['pos_id'] != null)
$volunteers[$result->fields['p_uuid']]['pos_id'][] = $result->fields['pos_id'];
}
$result->moveNext();
}
return $volunteers;
}
/**
* Retrieve a list of volunteers with information pertinent to reporting. All volunteers
* (regardless of status) are returned.
*
* @param $proj_id (optional) - specify this to return volunteers only from this project
* @param $org_id (optional) - specify this to return volunteers only from this organization
* @param $vols (optional) - an array of p_uuids of volunteers to report on; specifying this will make the proj_id and org_id parameters be ignored
*
* @return an array, where each key is a volunteer's p_uuid and each value is the following array of information:
* Array
* (
* 'full_name' => the volunteer's full name
* 'status' => the volunteer's overall status
* 'locations' => an array of the volunteer's location and its parents
* 'affiliation' => the organization that the volunteer is affiliated with
* 'pay_info' => the volunteer's pay information
* )
*/
function getVolunteersForReport($proj_id=null, $org_id=null, $vols=array())
{
$whereSaid = false;
$whereClause = "WHERE 1";
if(!empty($vols))
{
$id_list = "(";
foreach($vols as $index => $p_uuid)
{
$id_list .= "'$p_uuid'";
if($index < count($vols) - 1)
$id_list .= ", ";
}
$id_list .= ")";
$whereClause .= " AND vm_vol_details.p_uuid IN $id_list ";
}
else
{
if(!is_null($proj_id))
$whereClause .= " AND vm_vol_details.p_uuid IN (SELECT p_uuid FROM vm_vol_assignment WHERE proj_id='$proj_id')";
if(!is_null($org_id))
$whereClause .= " AND org_id = '$org_id' ";
}
$q = "SELECT DISTINCT person_uuid.p_uuid, person_uuid.full_name, vm_vol_details.status, location_id, org_main.name org_name
FROM vm_vol_details LEFT JOIN person_uuid ON (person_uuid.p_uuid=vm_vol_details.p_uuid)
LEFT JOIN vm_vol_assignment ON (vm_vol_assignment.p_uuid=vm_vol_details.p_uuid)
LEFT JOIN location_details ON (location_details.poc_uuid = person_uuid.p_uuid)
LEFT JOIN org_main ON (vm_vol_details.org_id = org_main.o_uuid) "
. $whereClause;
$result = $this->execute($q);
// put all information into an array for returning
$volunteers = array();
while(!$result->EOF)
{
if(empty($result->fields['location_id']))
$locations = array();
else
$locations = $this->getParentLocations($result->fields['location_id']);
$volunteers[$result->fields['p_uuid']] = array
(
'full_name' => $result->fields['full_name'],
'locations' => $locations,
'affiliation' => ($result->fields['org_name']==null)?'':$result->fields['org_name'],
'pay_info' => $this->getVolHoursAndRate($result->fields['p_uuid'], $proj_id),
'status' => $result->fields['status']
);
$result->moveNext();
}
return $volunteers;
}
/**
* Either creates volunteer information in the database if a volunteer is being added, or updates
* volunteer information if a volunteer already exists.
*
* @param $v - a Volunteer object. If it's p_uuid is not set, new volunteer information is
* inserted into the database. Otherwise, the information of the volunteer
* whose p_uuid is $v->p_uuid is simply updated.
* @param $shn_user - (optional, default false) set to true if registering a current Sahana user as a volunteer
* @return void
*/
function saveVol(&$v, $shn_user=false)
{
if(isset($v->p_uuid) && !$shn_user)
{
// this Volunteer already has a p_uuid, so simply update its information
//update full name
$this->execute("UPDATE person_uuid SET full_name='{$v->info['full_name']}' WHERE p_uuid = '{$v->p_uuid}'");
//delete old ID information and insert the new
$this->execute("DELETE FROM identity_to_person WHERE p_uuid = '{$v->p_uuid}'");
foreach($v->info['ids'] as $id_type => $serial)
{
if(trim($serial) != '')
$this->execute("INSERT INTO identity_to_person (opt_id_type,serial, p_uuid) values ('$id_type','$serial' ,'{$v->p_uuid}')");
else
unset($v->info['ids'][$id_type]);
}
//update phonetic sound matching
$this->execute("DELETE FROM phonetic_word WHERE pgl_uuid='{$v->p_uuid}'");
$names = preg_split("/\s+/", $v->info['full_name']);
foreach($names as $single_name)
{
$this->execute("INSERT INTO phonetic_word VALUES('" . soundex($single_name) . "', '" . metaphone($single_name) . "', '{$v->p_uuid}')");
}
//update gender, date of birth, and occupation information
$result= $this->execute("Select p_uuid from person_details where p_uuid ='{$v->p_uuid}'");
if ($result->EOF)
$this->execute("INSERT INTO person_details (p_uuid,opt_gender,birth_date,occupation) values ('".$v->p_uuid."','".$v->info['gender']."','".$v->info['dob']."','".$v->info['occupation']."')");
else
$this->execute("UPDATE person_details SET opt_gender = '{$v->info['gender']}', birth_date = '{$v->info['dob']}', occupation = '{$v->info['occupation']}' WHERE p_uuid ='{$v->p_uuid}'");
//update the location information
$specific_loc = $v->info['locations'][0];
if($specific_loc == -1 || $specific_loc == null || $specific_loc === '0')
{
$this->execute("DELETE FROM location_details WHERE poc_uuid='{$v->p_uuid}'");
}
else
{
$result = $this->execute("SELECT location_id FROM location_details WHERE poc_uuid='{$v->p_uuid}'");
if($result->EOF)
$this->execute("INSERT INTO location_details (poc_uuid, location_id) VALUES ('{$v->p_uuid}', '$specific_loc')");
else
$this->execute("UPDATE location_details SET location_id='$specific_loc' WHERE poc_uuid='{$v->p_uuid}'");
}
//Update a volunteers availability and organization affiliation along with hours of availability and special needs
$this->execute("update vm_vol_details SET date_avail_start= '{$v->info['date_start']}',date_avail_end='{$v->info['date_end']}' ,hrs_avail_start= '{$v->info['hour_start']}',hrs_avail_end= '{$v->info['hour_end']}', org_id='{$v->info['affiliation']}', special_needs='{$v->info['special_needs']}' WHERE p_uuid='".$v->p_uuid."'");
//delete the old contacts and replace with new ones if they are not blank
$this->execute("DELETE FROM contact WHERE pgoc_uuid = '{$v->p_uuid}'");
foreach($v->info['contact'] as $key => $value)
{
if(trim($value != ''))
$this->execute("insert into contact (pgoc_uuid, opt_contact_type, contact_value) values ('{$v->p_uuid}', '$key', '$value')");
}
//get rid of any pre-existing skills and replace them with the new
$this->execute("DELETE FROM vm_vol_skills WHERE p_uuid = '{$v->p_uuid}'");
foreach($v->info['skills'] as $skill)
{
$this->execute("INSERT INTO vm_vol_skills (p_uuid, opt_skill_code) VALUES('{$v->p_uuid}', '$skill')");
}
}
else
{
//create a new p_uuid for the volunteer and insert its new information into the database
//generate a new p_uuid only if $shn_user is false
global $global;
if(!$shn_user)
{
require_once($global['approot']."/inc/lib_uuid.inc");
$v->p_uuid = shn_create_uuid();
}
//create a Sahana account if necessary
if(isset($v->info['account_info']))
{
include_once($global['approot'].'inc/lib_security/lib_auth.inc');
include_once($global['approot'].'inc/lib_security/constants.inc');
$acct = $v->info['account_info'];
//create an account and give the user 'Registered User' privileges
shn_auth_add_user($acct['account_name'], $acct['user_name'], $acct['pass'], REGISTERED, $v->p_uuid);
}
//add phonetic sound matching
$names = preg_split("/\s+/", $v->info['full_name']);
foreach($names as $single_name)
{
$this->execute("INSERT INTO phonetic_word VALUES('" . soundex($single_name) . "', '" . metaphone($single_name) . "', '{$v->p_uuid}')");
}
//insert the volunteer's full name
$result = $this->db->execute("insert into person_uuid (p_uuid, full_name) values ('".$v->p_uuid."', '".$v->info['full_name']."')");
//insert the volunteer's availibility and organization affiliation along with hours of availability and special needs
$this->execute("insert into vm_vol_details (p_uuid,date_avail_start,date_avail_end,hrs_avail_start,hrs_avail_end, org_id, special_needs) values ('{$v->p_uuid}', '{$v->info['date_start']}', '{$v->info['date_end']}', '{$v->info['hour_start']}', '{$v->info['hour_end']}', '{$v->info['affiliation']}', '{$v->info['special_needs']}')");
//insert new ID information
if (!empty($v->info['ids'])) {
$id_type= array_pop(array_keys($v->info["ids"]));
$serial=$v->info['ids'][$id_type];
if($serial != '')
$this->execute("INSERT INTO identity_to_person (opt_id_type,serial, p_uuid) values ('$id_type','$serial' ,'{$v->p_uuid}')");
else
unset($v->info['ids'][$id_type]);
}
//insert gender, birth date, and occupation information
$this->execute("INSERT INTO person_details (p_uuid,opt_gender,birth_date,occupation) values ('".$v->p_uuid."','".$v->info['gender']."','".$v->info['dob']."','".$v->info['occupation']."')");
//insert contact types
if(!empty($v->info['contact']))
foreach($v->info['contact'] as $key => $value)
{
if(trim($value != ''))
$this->execute("insert into contact (pgoc_uuid, opt_contact_type, contact_value) values ('{$v->p_uuid}', '$key', '$value')");
}
//insert skill information
if(!empty($v->info['skills']))
foreach($v->info['skills'] as $skill)
{
$this->execute("INSERT INTO vm_vol_skills (p_uuid, opt_skill_code) VALUES('{$v->p_uuid}', '$skill')");
}
//insert the location information
$specific_loc = $v->info['locations'][0];
if($specific_loc != null && $specific_loc != -1)
$this->execute("INSERT INTO location_details (poc_uuid, location_id) VALUES ('{$v->p_uuid}', '$specific_loc')");
//also insert into the vm_user_role table
$this->execute("INSERT INTO vm_user_role (fk_user, fk_role) VALUES ('".($v->p_uuid)."', ".VM_ROLE_VOLUNTEER.")");
}
}
/*
* Return all of the position of a specific project of a user using
* @param pos_id
* @param proj_id
* @param p_uuid - to select position that are assigned to a volunteer.
*/
function listPositions($proj_id=null, $p_uuid=null) {
if($proj_id == null && $p_uuid == null)
$whereClause = '';
else if($proj_id == null)
$whereClause = "WHERE p_uuid = '$p_uuid' AND status='active'";
else if($p_uuid == null)
$whereClause = "WHERE proj_id = '$proj_id'";
else
$whereClause = "WHERE proj_id = '$proj_id' AND p_uuid = '$p_uuid'";
$result = $this->execute("SELECT pos_id, proj_id,project_name, ptype_id, slots, title, description, ptype_title, ptype_description, skill_code FROM vm_vol_assignment $whereClause ORDER BY proj_id ");
$positions = array();
while(!$result->EOF) {
$this->remove_keys($result->fields);
$positions[] = $result->fields;
$result->moveNext();
}
$result = $this->execute("select pos_id, count(*) numVolunteers FROM vm_vol_assignment_active group by pos_id");
return $positions;
}
/*
* Saves changes to a position
* @ param $p- reference of a Positisn object. If the position does not have an ID, a new position
* is inserted into the database, otherwise existing position information is updated.
* @return void
*/
function savePosition(&$p) {
global $global;
if(!isset($p->pos_id)) {
//generate a new id
require_once($global['approot']."/inc/lib_uuid.inc");
$p->pos_id = shn_create_uuid();
//create a new position
$this->execute("INSERT INTO vm_position (pos_id, proj_id, ptype_id, title, slots, description,payrate)
values ('{$p->pos_id}', '{$p->proj_id}', '{$p->ptype_id}', '{$p->title}', '{$p->numSlots}', '{$p->description}', '{$p->payrate}')");
} else {
//update existing position information
$this->execute("UPDATE vm_position SET proj_id = '{$p->proj_id}', ptype_id = '{$p->ptype_id}', title = '{$p->title}', slots = '{$p->numSlots}', description = '{$p->description}', payrate = '{$p->payrate}'
where pos_id = '{$p->pos_id}'");
}
}
/*
* Removes all active positions and sets the position in the database
* @param $pos_id - However does not remove from the database but sets as inactive position
*/
function removePosition($pos_id) {
$this->execute("update vm_position set status = 'retired' where pos_id = '$pos_id'");
}
/*
* Select a project
* @param $proj_id - so locate all position within a given a project
*/
function getPostionsByProject($proj_id) {
//build the query
$query = "SELECT skill_code,pos_id, title, description, payrate FROM vm_position JOIN vm_proj_position USING (pos_id) WHERE(proj_id = '$proj_id')";
//store the info
$result = $this->execute($query);
$position = array();
while(!$result->EOF)
{
$position[$result->fields['pos_id']] = array('title' => $result->fields['title'],'skill_code' => $result->fields['skill_code'], 'payrate' => $result->fields['payrate'], 'description' => $result->fields['description'],'slots' => $result->fields['slots']);
$result->moveNext();
}
return $position;
}
/*
* Select the type of position
* @param $pos_id - and returns the type of position of a project
*/
function getPositionType($pos_id) {
$query = "SELECT skill_code, title, description FROM vm_positiontype WHERE (pos_id='$pos_id')";
//store the info
$result = $this->execute($query);
$this->remove_keys($result->fields);
return $result->fields;
}
/*
* List the type of positions and returns all the positions
* @ param $ptypes
*/
function listPositionTypes() {
$result = $this->execute("select ptype_id, title, description, skill_code from vm_positiontype");
$ptypes = array();
while(!$result->EOF) {
$this->remove_keys($result->fields);
$ptypes[] = $result->fields;
$result->moveNext();
}
return $ptypes;
}
/*
* getPosition select a position
* @param $pos_id - with a array of information with the $pos_id and return that information
* @param $p
*/
function getPosition($pos_id) {
$query = "SELECT proj_id, ptype_id, slots, title, description, project_name, ptype_title, ptype_description, skill_code, payrate FROM vm_position_full WHERE (pos_id='$pos_id')";
$result = $this->execute($query);
$this->remove_keys($result->fields);
$p = $result->fields;
$result = $this->execute("select count(*) numVolunteers from vm_vol_assignment_active where pos_id = '$pos_id'");
$p['numVolunteers'] = $result->fields['numVolunteers'];
return $p;
}
/**
* Saves changes made to a project to the database
*
* @access public
* @param @p - reference of a Project object. If the project does not have an ID, a new project
* is inserted into the database, otherwise existing project information is updated.
* @return void
*/
function saveProject(&$p)
{
if(!isset($p->proj_id))
{
//create a new project
$specific_loc= $p->info['locations'][0];
if($specific_loc==-1 ||$specific_loc==="0" || $specific_loc===null)
$specific_loc= "null";
else
$specific_loc="'".$specific_loc."'";
$this->execute("INSERT INTO vm_projects (name, description,location_id,start_date,end_date) values ('{$p->info['name']}', '{$p->info['description']}', $specific_loc, '{$p->info['start_date']}', '{$p->info['end_date']}')");
$result = $this->execute("SELECT LAST_INSERT_ID()");
$proj_id = $result->fields[0];
//create a project manager position using the project manager position type
$pos = new Position();
$pos->title = 'Project Manager';
$pos->proj_id = $proj_id;
$pos->ptype_id = 'smgr';
$pos->description = 'Manage the entire project';
$pos->numSlots = 1;
$pos->payrate = 20.00;
$this->savePosition($pos);
//assign the project manager to the position and refresh the project information
//cannot simply redeclare $p because we're in the middle of a method call
$this->assignVolunteerToPosition($p->info['mgr_id'], $pos->pos_id);
$p_tmp = new Project($proj_id);
$p->proj_id = $proj_id;
$p->positions = $p_tmp->positions;
}
else
{
//update existing project information
$specific_loc= $p->info['locations'][0];
if($specific_loc==-1 ||$specific_loc==="0" || $specific_loc===null)
$specific_loc= "null";
else
$specific_loc="'".$specific_loc."'";
$result = $this->execute("UPDATE vm_projects SET name = '{$p->info['name']}', description = '{$p->info['description']}', location_id = $specific_loc, start_date = '{$p->info['start_date']}', end_date = '{$p->info['end_date']}' WHERE proj_id = '{$p->proj_id}'");
}
}
/**
* Retrieve a list of project information
*
* @access public
* @param $p_uuid - (optional) if specified, only return projects that this volunteer is working on (project managers are handled as well)
* @param $mgr - (optional) if specified, $p_uuid is treated as project manager and only projects he is a project manager for will be returned; if $p_uuid
* is a project manager but this is false, it will also return any projects that the project manager may just be assigned to
* @param $simple - (optional) if true, the return array only contains the proj_id for each key and the project name for each value
* @return an array of project information arrays, where each key is the project ID and each value
* is an array with the following structure:
*
* Array
* (
* 'name' => the name of the project
* 'description' => the project's description
* )
*/
function listProjects($p_uuid=null, $mgr=false, $simple=false)
{
//build the query
$query = "SELECT proj_id, name, description FROM vm_projects_active";
if(!is_null($p_uuid))
{
if($mgr)
$query .= " WHERE proj_id IN (SELECT proj_id FROM vm_vol_assignment_active WHERE p_uuid = '$p_uuid' AND ptype_id = 'smgr')";
else
$query .= " WHERE proj_id IN (SELECT proj_id FROM vm_vol_assignment_active WHERE p_uuid = '$p_uuid') OR proj_id IN (SELECT proj_id FROM vm_vol_assignment_active WHERE p_uuid = '$p_uuid' AND ptype_id = 'smgr')";
}
//store the info
$result = $this->execute($query);
$projects = array();
while(!$result->EOF)
{
if($simple)
$projects[$result->fields['proj_id']] = $result->fields['name'];
else
$projects[$result->fields['proj_id']] = array('name' => $result->fields['name'], 'description' => $result->fields['description']);
$result->moveNext();
}
return $projects;
}
/**
* Retrieves information about a single project.
*
* @access public
* @param $id - the project's ID
* @return the project information in an associative array, with the following structure:
* Array
* (
* 'name' => the name of the project,
* 'location_id' => the 'loc_uuid' from the 'location' table of the most specific location
* where the project takes place
* 'start_date' => the beginning date of the project
* 'end_date' => the end date of the project
* 'description' => a brief description of the project
* )
*/
function getProject($proj_id)
{
$result = $this->execute("SELECT name, location_id, start_date, end_date, description FROM vm_projects_active WHERE proj_id = '$proj_id'");
//Get rid of numerically-based indices
$this->remove_keys($result->fields);
$proj = $result->fields;
//get positions for this project
$result = $this->execute("select pos_id this_pos_id, ptype_id, title, description, ptype_title, ptype_description, slots numSlots, payrate, (select count(*) FROM vm_vol_assignment_active WHERE pos_id = this_pos_id AND proj_id='$proj_id') numVolunteers, skill_code " .
"from vm_position_active where proj_id = '$proj_id'");
$proj['positions'] = array();
while(!$result->EOF) {
$this->remove_keys($result->fields);
$result->fields['pos_id'] = $result->fields['this_pos_id'];
unset($result->fields['this_pos_id']);
$proj['positions'][$result->fields['pos_id']] = $result->fields;
$result->moveNext();
}
return $proj;
}
/**
* Retrieves a project's name
*
* @access public
* @param $proj_id - the ID of the project
* @return the project's name
*/
function getProjectName($proj_id)
{
$result = $this->execute("SELECT name FROM vm_projects_active WHERE proj_id = '$proj_id'");
if(!$result->fields)
return false;
return $result->fields['name'];
}
/**
* Retrieves whether or not the volunteer is assigned to the given project
*
* @param $p_uuid - the p_uuid of the volunteer to check
* @param $proj_id - the p_uuid of the project to check
* @return true if the volunteer is assigned to the given project, false if not
*/
function volIsAssignedToProject($p_uuid, $proj_id=null)
{
$query = "SELECT p_uuid FROM vm_vol_assignment_active WHERE p_uuid = '$p_uuid'";
if($proj_id!=null)
$query .= " AND proj_id = '$proj_id'";
$result = $this->execute($query);
return !$result->EOF;
}
/**
* Removes a volunteer and all the related information relating to that specific
* volunteer from the database
*
* @access public
* @param $id - the volunteer's p_uuid
* @return void
*/
function deleteVolunteer($id)
{
$this->execute("UPDATE vm_vol_details SET status = 'retired' WHERE p_uuid = '$id'");
$this->execute("UPDATE vm_vol_assignment SET status = 'retired' WHERE p_uuid = '$id'");
}
/**
* Removes a volunteer from a project
*
* @access public
* @param $p_uuid - the p_uuid of the volunteer to remove
* @param $proj_id - the ID of the project to remove the volunteer from
* @return void
*/
function deleteFromProject($p_uuid, $proj_id)
{
$this->execute("UPDATE vm_vol_assignment SET status='retired' WHERE p_uuid = '$p_uuid' AND proj_id = '$proj_id'");
//send the volunteer a message from the project manager notifying them of the assignment
$result = $this->execute("SELECT name, FROM vm_projects WHERE proj_id = '$proj_id'");
$this->sendMessage('SYS_MSG', array($p_uuid), "You have been removed from Project: {$result->fields['name']}. \n\n (This is an automated message)");
}
/**
* Removes a project and all related information.
*
* @access public
* @param $id - the project's ID
* @return void
*/
function deleteProject($proj_id)
{
$this->execute("UPDATE vm_projects SET status = 'completed' WHERE proj_id = '$proj_id'");
$this->execute("UPDATE vm_vol_assignment SET status = 'retired' WHERE proj_id = '$proj_id'");
}
/**
* Retrieves all possible skills as a Tree object with CheckboxNodes for all
* Nodes other than the root node. Optionally, the skills of a particular
* volunteer can be pre-checked.
*
* @access public
* @param $p_uuid - if specified, the skills of the volunteer or project with this p_uuid
* are pre-checked; otherwise, all skill checkboxes remain empty.
* @param $vol - true to return a set of volunteer's skills , false to return a set of
* project's skills (this only matters if $p_uuid is set)
* @param $any_skills - (optional) an array of any skill codes to pre-check
* @return the Tree object
*/
function getSelectSkillsTree($p_uuid=null, $vol=true, $any_skills=null)
{
//first get and store all of the volunteer's skills as an array if necessary
if($p_uuid != null)
$skills_array = $this->getVolSkillsArray($p_uuid, $vol);
else if(is_array($any_skills))
$skills_array = $any_skills;
else
$skills_array = array();
//now query for all skills and store them in a Tree structure
$result = $this->execute("SELECT DISTINCT option_code, option_description FROM field_options WHERE field_name = 'opt_skill_type' ORDER BY option_description");
$tree = new Tree("?mod=vm&stream=text&act=display_js&js=");
$tree->setRoot(new Node('Skills and Work Restrictions'));
while(!$result->EOF)
{
$split = preg_split('/'. VM_SKILLS_DELIMETER .'/', $result->fields['option_description']);
$cur_parent = $tree->root;
foreach($split as $index => $name)
{
if($index < (count($split) - 1))
{
$name = trim($name);
$search_result = $tree->findNodeAux($cur_parent, $name);
if($search_result == null)
{
$tmp_child = new Node($name, 'CheckboxNode', array('input_name' => 'null'));
$cur_parent->addChild($tmp_child);
$cur_parent = $tmp_child;
}
else
{
$cur_parent = $search_result;
}
}
else
{
$extra_info = array('input_name' => "'SKILL_{$result->fields['option_code']}'");
if(in_array($result->fields['option_code'], $skills_array))
$extra_info['checked'] = true;
$tmp_child = new Node($name, 'CheckboxNode', $extra_info);
$cur_parent->addChild($tmp_child);
}
}
$result->MoveNext();
}
return $tree;
}
/**
* Retrieves all skills for a particular volunteer as an array
*
* @access public
* @param $p_uuid - the volunteer's p_uuid
* @param $vol - true to return a set of volunteer's skills, false to return a set of
* project's required skills
* @return the array, where each key is numerically based and each value is the skill ID
*/
function getVolSkillsArray($p_uuid)
{
$skills_array = array();
$result = $this->execute("SELECT opt_skill_code FROM vm_vol_skills WHERE p_uuid = '$p_uuid'");
while(!$result->EOF)
{
$skills_array[] = $result->fields['opt_skill_code'];
$result->MoveNext();
}
return $skills_array;
}
/**
* Retrieves all skills for a particular volunteer as a Tree object with simple
* Nodes for everything (no CheckboxNodes)
*
* @access public
* @param $p_uuid - the volunteer's p_uuid
* @param $vol - true to retrieve a volunteer's skills, false to retrieve a
* project's required skills
* @return the Tree object
*/
function getVolSkillsTree($p_uuid)
{
// query for all skills that belong to this volunteer
$result = $this->execute("SELECT option_description FROM field_options WHERE option_code IN
(SELECT opt_skill_code FROM vm_vol_skills WHERE p_uuid = '$p_uuid')
ORDER BY option_description");
$tree = new Tree("?mod=vm&stream=text&act=display_js&js=");
$tree->setRoot(new Node('Skills and Work Restrictions'));
// now store the skills in a Tree structure
while(!$result->EOF)
{
$split = preg_split('/'. VM_SKILLS_DELIMETER .'/', $result->fields['option_description']);
$cur_parent = $tree->root;
foreach($split as $index => $name)
{
$name = trim($name);
$search_result = $tree->findNodeAux($cur_parent, $name);
if($search_result == null)
{
$tmp_child = new Node($name);
$cur_parent->addChild($tmp_child);
$cur_parent = $tmp_child;
}
else
{
$cur_parent = $search_result;
}
}
$result->MoveNext();
}
return $tree;
}
/**
* Returns all skill IDs as an array
*
* @access public
* @return all skill IDs as a numerically-based array, where each value is the shorthand ID of a skill
*/
function getSkillIDs()
{
$result = $this->execute("SELECT option_code FROM field_options WHERE field_name = 'opt_skill_type'");
$skill_ids = array();
while(!$result->EOF)
{
$skill_ids[] = $result->fields['option_code'];
$result->MoveNext();
}
return $skill_ids;
}
function getSkillList() {
$result = $this->execute("select option_code code, option_description skill from field_options where field_name = 'opt_skill_type' order by option_description asc");
if(!$result->EOF) {
$skills = array();
while(!$result->EOF) {
$skills[$result->fields['code']] = $result->fields['skill'];
$result->moveNext();
}
return $skills;
} else
return false;
}
function removeSkill($code)
{
$this->execute("delete from field_options where field_name = 'opt_skill_type' and option_code = '$code'");
}
/**
* Adds a new skill to the field_options table
*
* @param $code - the abbreviated name for the skill
* @param $desc - a description of the skill
* @return true if successful, false if the skill already exists
*/
function addSkill($code, $desc)
{
$result = $this->execute("SELECT 1 FROM field_options WHERE field_name = 'opt_skill_type' AND option_code = '$code'");
if(!$result->EOF)
return false;
$this->execute("INSERT INTO field_options (field_name, option_code, option_description) VALUES ('opt_skill_type', '$code', '$desc')");
return true;
}
/**
* Retrieves all organizations registered in the system
*
* @param $vmOnly - (optional, default false) - true to return only organizations that at least one
* volunteer (active or retired) is a part of (useful for reports)
* @return an associative array, where each key is the organization's o_uuid and each
* value is its corresponding name
*/
function getOrganizations($vmOnly=false)
{
$q = "SELECT o_uuid, name FROM org_main ";
if($vmOnly)
$q .= "WHERE o_uuid IN (SELECT org_id FROM vm_vol_active)";
$result = $this->execute($q);
$orgs = array();
while(!$result->EOF)
{
$orgs[$result->fields['o_uuid']] = $result->fields['name'];
$result->MoveNext();
}
return $orgs;
}
/**
* Retrieves an organization's information.
*
* @param $o_uuid - the UUID of the organization
* @return an associative array, which has the following structure:
* Array
* (
* 'name' => the organization's name
* )
*
* NOTE: If the organization is not found, an empty array is returned.
*/
function getOrganizationInfo($o_uuid)
{
$result = $this->execute("SELECT name FROM org_main WHERE o_uuid = '{$o_uuid}'");
if($result->EOF)
{
return array();
}
else
{
$this->remove_keys($result->fields);
return $result->fields;
}
}
/**
* Checks to see if the given user is registered as a volunteer and still being active (not retired)
*
* @access public
* @param $p_uuid - the user's p_uuid
* @return true if the user is a volunteer, false otherwise
*/
function isVolunteer($p_uuid) //
{
$result = $this->execute("SELECT p_uuid FROM vm_vol_active WHERE p_uuid = '$p_uuid'");
return !$result->EOF;
}
/**
* Gets a list of volunteers who have the given skill/credential
*
* @param $ability - the skill code of the skill/credential to search by
* @param $status - (optional, default null) set to the ability status to filter by:
* 'approved' - already approved
* 'denied' - ability request was denied
* 'unapproved' - not yet approved but not yet denied
* @return an associative array where each key is a volunteer's p_uuid and the corresponding value is an array with the following structure:
*
* Array
* (
* 'name' => the volunteer's name
* 'status' => the status of the volunteer's ability:
* 'approved' - already approved
* 'denied' - ability request was denied
* 'unapproved' - not yet approved but not yet denied
* )
*/
function getVolunteersByAbility($ability, $status=null)
{
if($status != null)
$extra_clause = " AND vm_vol_skills.status = '$status' ";
else
$extra_clause = "";
$result = $this->execute( "SELECT DISTINCT vm_vol_skills.p_uuid, full_name, vm_vol_skills.status
FROM vm_vol_skills JOIN person_uuid USING(p_uuid)
JOIN vm_vol_active USING (p_uuid)
WHERE opt_skill_code = '$ability'
$extra_clause
ORDER BY status");
$vols = array();
while(!$result->EOF)
{
$vols[$result->fields['p_uuid']] = array('name' => $result->fields['full_name'], 'status' => $result->fields['status']);
$result->moveNext();
}
return $vols;
}
/**
* Updates a volunteers's ability's status. Currently this is only used for project managers, but
* can be used later for credentialing.
*
* @access public
* @param $p_uuid - the p_uuid of the volunteer whose skill status to update
* @param $ability - the skill code of the ability whose status to modify
* @param $approved - true to approve the status, false to deny it
* @return void
*/
function updateAbilityStatus($p_uuid, $ability, $approved)
{
//get the name of the ability for later use in messaging
$result = $this->execute("SELECT option_description FROM field_options WHERE option_code = '$ability'");
$ability_name = $result->fields['option_description'];
//check to see if we're modifying an existing ability or adding a new ability
$result = $this->execute("SELECT 1 FROM vm_vol_skills WHERE p_uuid = '$p_uuid' AND opt_skill_code = '$ability'");
if(!$result->EOF)
{
if($approved)
{
$this->execute("UPDATE vm_vol_skills SET status='approved' WHERE opt_skill_code = '$ability' AND p_uuid = '$p_uuid'");
$this->sendMessage('SYS_MSG', array($p_uuid), "Your ability status for $ability_name has been approved. \n\n (This is an automated message)");
}
else
{
//check to see if we're denying or revoking the status from the user (just for messaging)
$result = $this->execute("SELECT 1 FROM vm_vol_skills WHERE p_uuid = '$p_uuid' AND opt_skill_code = '$ability' AND status = 'approved'");
if(!$result->EOF)
$this->sendMessage('SYS_MSG', array($p_uuid), "Your ability status for $ability_name has been revoked. \n\n (This is an automated message)");
else
$this->sendMessage('SYS_MSG', array($p_uuid), "Your ability status request for $ability_name has been denied. \n\n (This is an automated message)");
$this->execute("UPDATE vm_vol_skills SET status='denied' WHERE opt_skill_code = '$ability' AND p_uuid = '$p_uuid'");
}
}
else
{
$this->execute("INSERT INTO vm_vol_skills (p_uuid, opt_skill_code, status) VALUES ('$p_uuid', '$ability', 'approved')");
$this->sendMessage('SYS_MSG', array($p_uuid), "You have been given an approved ability status for $ability_name. \n\n (This is an automated message)");
}
if($ability == 'MGR') {
if($approved) {
$this->execute("INSERT INTO vm_user_role VALUES ('$p_uuid', ".VM_ROLE_PROJECT_MANAGER.")");
} else {
$this->execute("DELETE FROM vm_user_role WHERE fk_user = '$p_uuid' AND fk_role = ".VM_ROLE_PROJECT_MANAGER);
}
}
}
/**
* Sends a message to a volunteer from another person
*
* @param $from_id - the p_uuid of the sender
* @param $to_id - an array of p_uuids representing the recepients
* @param $message - the text to send
* @return void
*/
function sendMessage($from_id, $to_ids, $message)
{
$this->execute("INSERT INTO vm_message (message) values ('$message')");
$result= $this->execute("SELECT LAST_INSERT_ID()");
$message_id = $result->fields[0];
foreach($to_ids as $to_id)
{
$this->execute("INSERT INTO vm_courier (message_id, to_id, from_id) VALUES ($message_id, '$to_id', '$from_id')");
$this->execute("INSERT INTO vm_mailbox (p_uuid, message_id, box, checked) VALUES ('$to_id', $message_id, 0, 0)");
}
$this->execute("INSERT INTO vm_mailbox (p_uuid, message_id, box, checked) VALUES ('$from_id', $message_id, 1, 0)");
}
/**
* Retrieves information about all the messages sent to the volunteer
*
* @param $p_uuid - the id of the volunteer
* @param $inbox - true to retrieve inbox messages, false to retrieve outbox messages
* (defaults to outbox)
* @return an array of arrays, where each inner array has following structure:
* Array
* (
* 'message_id' => the ID of the message
* 'from_id' => the p_uuid of the person who the message is from
* 'to_id' => the p_uuid of the person who the message is to
* 'time' => a timestamp of when the message was sent
* 'message' => the message sent
* 'checked' => 1 if the message has been read yet, 0 if not
* )
*/
function getMessages($p_uuid, $inbox=true)
{
if($inbox)
$result = $this->execute("SELECT from_id, vm_message.message_id, time, message, checked FROM vm_message, vm_courier, vm_mailbox WHERE p_uuid = '$p_uuid' AND box = 0 AND to_id = '$p_uuid' AND vm_message.message_id = vm_courier.message_id AND vm_message.message_id = vm_mailbox.message_id ORDER BY time DESC");
else
$result = $this->execute("SELECT vm_message.message_id, time, message, checked FROM vm_message, vm_courier, vm_mailbox WHERE p_uuid = '$p_uuid' AND box = 1 AND from_id = '$p_uuid' AND vm_message.message_id = vm_courier.message_id AND vm_message.message_id = vm_mailbox.message_id GROUP BY vm_message.message_id ORDER BY time DESC");
$messages = array();
while(!$result->EOF)
{
$this->remove_keys($result->fields);
$messages[]= $result->fields;
$result->moveNext();
}
return $messages;
}
/**
* Retrieves the entire list of who a message is sent to
*
* @access public
* @param $msg_id - the ID of the message to check
* @return an associative array, where each key is a person's p_uuid and each
* value is the person's name
*/
function getToList($msg_id)
{
$result = $this->execute("SELECT to_id, full_name FROM vm_courier, person_uuid WHERE p_uuid = to_id AND message_id = $msg_id");
$list = array();
while(!$result->EOF && $result != null)
{
$list[$result->fields['to_id']] = $result->fields['full_name'];
$result->MoveNext();
}
return $list;
}
/**
* Retrieves a person's name
*
* @param $p_uuid - a person's unique id
* @return their name
*/
function getPersonName($p_uuid) //
{
$result = $this->execute("SELECT full_name FROM person_uuid WHERE p_uuid ='$p_uuid'");
if(!$result->fields)
return false;
return $result->fields['full_name'];
}
/**
* Retrieves the information from a message and sets it as checked.
*
* @param $p_uuid - the p_uuid of the volunteer to check the message for
* @param $message_id - the message ID
* @param $box - true to check someone's inbox, false to check the outbox
* @return the array of message information if successful, or an empty array if the message was not found
*/
function getMessage($p_uuid, $message_id, $box=true)
{
$box_num = ($box)?'0':'1';
$result = $this->execute("SELECT from_id, vm_message.message_id, time, message, checked FROM vm_message, vm_courier, vm_mailbox WHERE p_uuid = '$p_uuid' AND box=$box_num AND vm_message.message_id = vm_courier.message_id AND vm_message.message_id = vm_mailbox.message_id AND vm_message.message_id = $message_id");
if(!$result->EOF)
{
//set the message as checked and return the info
$this->execute("UPDATE vm_mailbox SET checked = 1 WHERE p_uuid='$p_uuid' AND message_id = $message_id AND box=$box_num");
$this->remove_keys($result->fields);
return $result->fields;
}
else
{
return array();
}
}
/**
* Deletes a message
*
* @param $p_uuid - the person to delete the message for
* @param $msg_id - the ID of the message
* @param $box - true to delete from inbox, false to delete from outbox
* @return void
*/
function deleteMessage($p_uuid, $msg_id, $box)
{
$box_num = ($box)?'0':'1';
$this->execute("DELETE FROM vm_mailbox WHERE message_id = '$msg_id' AND p_uuid = '$p_uuid' AND box=$box_num");
}
/**
* Retrieve number of unread messages from an inbox
*
* @param $p_uuid - the p_uuid of the volunteer to check
* @return the number of unread messages
*/
function getUnreadMessages($p_uuid)
{
$result = $this->execute("SELECT COUNT(*) FROM vm_mailbox WHERE p_uuid = '$p_uuid' AND box = 0 AND checked = 0");
return $result->fields[0];
}
/**
* Generates the SQL SELECT statement clauses based on a phonetic search for a volunteer's name
*
* @param $vol_name - the full volunteer's name (spaces ok)
* @param $loose - true to use looser matching, false otherwise
* @return the SELECT statement
*/
function generateSelectPhoneticClauses($vol_name, $loose=false)
{
if(trim($vol_name)=='')
return '';
else
{
$query = " (";
$names = preg_split("/\s+/", $vol_name);
if($loose)
{
//looser matching only matters for metaphone
foreach($names as $single_name)
{
$query .= " encode2 LIKE '%" . metaphone($single_name) . "%' OR ";
$query .= "'" . metaphone($single_name) . "'" . " LIKE concat('%', encode2, '%') OR ";
}
}
else
{
foreach($names as $single_name)
{
$query .= " encode1 = '" . soundex($single_name) . "' OR ";
$query .= " encode2 = '" . metaphone($single_name) . "' OR ";
}
}
$query = substr($query, 0, strlen($query) - 3);
$query .= ") ";
return $query;
}
}
/**
* Retrieve a set of search results for a volunteer search. It is assumed
* that validation on the parameters has already occurred.
*
* @param $id - an ID to look for (NIC, DLN, etc.)
* @param $name - a name to search by
* @param $skills - an array of skill IDs to search by
* @param $skills_matching - VM_SKILLS_ALL to match all $skills, VM_SKILLS_ANY to match any $skills
* @param $start_date - the start date of required availability
* @param $end_date - the end date of required availability
* @param $location - the most specific location to search by
* @param $date_constraint - true to search for a volunteer that is available for the entire time period specified, false
* to search for a volunteer that is available for any portion of the time period specified
* @param $unassigned - (optional) true to show only unassigned volunteers, false to show all
* @param $loose - (optional) true for looser matching on the name
* @param $assigning_proj - (optional, for assigning) the project ID of the project we are assigning to, to exclude all volunteers already assigned to it
* @return an array where each key is a p_uuid of a volunteer and each value is an array with the following structure:
*
* Array
* (
* 'full_name' => the volunteer's full name
* 'ids' => an array of the volunteer's IDs, where each key is
* the abbreviation for an ID type and each value is the ID code
* 'skills' => an array of skill codes => descriptions for the volunteer
* 'date_start' => the volunteer's available start date
* 'date_end' => the volunteer's available end date
* 'locations' => an array of the volunteer's location and its parents where each key is a location id and each value is the location name
* 'affiliation' => the organization that the volunteer is affiliated with
* 'id_searched' => the Id number searched for, nice to have when displaying to highlight the searched-for substring
* 'levenshtein' => the levenshtein distance between the name searched for and this
* volunteer's full name
* )
*
* The search results are ordered by increasing levenshtein distance.
*/
function getVolSearchResults($id, $name, $skills, $skills_matching, $start_date, $end_date, $location, $date_constraint, $unassigned=false, $loose=false, $assigning_proj=null)
{
$name_set = $name != '';
$id_set = $id != '';
$date_set = $start_date != '';
$skills_set = count($skills) > 0;
$location_set = $location != '';
$query = "SELECT vm_vol_active.p_uuid, person_uuid.full_name, vm_vol_assignment_active.proj_id, location_id, opt_id_type, serial, org_id, org_main.name org_name, option_description id_desc, date_avail_start, date_avail_end
FROM vm_vol_active
LEFT JOIN person_uuid USING (p_uuid)
LEFT JOIN vm_vol_assignment_active USING (p_uuid)
LEFT JOIN location_details ON (location_details.poc_uuid = person_uuid.p_uuid)
LEFT JOIN identity_to_person USING (p_uuid)
LEFT JOIN org_main ON (vm_vol_active.org_id = org_main.o_uuid)
LEFT JOIN field_options ON (identity_to_person.opt_id_type = field_options.option_code) ";
//generate the FROM clause
if($name_set)
$query .= " JOIN phonetic_word ON (person_uuid.p_uuid = phonetic_word.pgl_uuid) ";
$query .= "WHERE 1 ";
//generate the WHERE clause
//search by name
if($name_set)
$query .= " AND ".$this->generateSelectPhoneticClauses($name, $loose);
//search by IDs
if($id_set)
$query .= " AND serial LIKE '%{$id}%' ";
//search by skills
if($skills_set)
{
$skills_set_search = " (";
foreach($skills as $skill_id)
{
$skills_set_search .= "'$skill_id', ";
}
$skills_set_search = substr($skills_set_search, 0, strlen($skills_set_search) - 2);
$skills_set_search .= ') ';
$query .= " AND (
SELECT COUNT(*)
FROM vm_vol_skills
WHERE p_uuid = person_uuid.p_uuid
AND opt_skill_code IN $skills_set_search
) ";
if($skills_matching == VM_SKILLS_ALL)
$query .= "= ". count($skills) . " ";
else
$query .= "> 0 ";
}
//search by availability
if($date_set)
{
if($date_constraint)
{
//volunteer must be available for entire date range specified
$query .= " AND date_avail_start<='$start_date' AND date_avail_end>='$end_date' ";
}
else
{
if($end_date == '')
{
//volunteer must be available for any time after the start date specified
$query .= " AND date_avail_end>='$start_date' AND date_avail_start<='$start_date'";
}
else
{
//volunteer must be available for any portion of time between the dates specified
$query .= " AND date_avail_start<='$end_date' AND date_avail_end>='$start_date' ";
}
}
}
//get only unassigned
if($unassigned)
$query .= " AND person_uuid.p_uuid NOT IN (SELECT DISTINCT p_uuid FROM vm_vol_assignment_active) ";
//if we are assigning to a project, exclude results from the project we are assigning to
if($assigning_proj != null)
$query .= " AND person_uuid.p_uuid NOT IN (SELECT p_uuid FROM vm_vol_assignment_active WHERE proj_id = '$assigning_proj') AND person_uuid.p_uuid NOT IN (SELECT p_uuid FROM vm_vol_assignment_active WHERE proj_id = '$assigning_proj' AND ptype_id = 'smgr') ";
//get the results and format them into the resulting array
$result = $this->execute($query);
$search_results = array();
while(!$result->EOF)
{
//first check to see if there already is an entry in the array (since we are doing so many joins, more than one row may be returned per volunteer)
$p_uuid = $result->fields['p_uuid'];
if(isset($search_results[$p_uuid]))
{
//update existing information for this entry
if($result->fields['serial'] != null)
$search_results[$p_uuid]['ids'][$result->fields['id_desc']] = $result->fields['serial'];
if($result->fields['org_name'] != null)
$search_results[$p_uuid]['affiliation'] = $result->fields['org_name'];
}
else
{
//create a new entry for the volunteer in the $search_results array
//first, have to do refinement of results by location manually due to its recursive nature
//if we are searching by location and the location to search by is not in the resulting volunteer's
//location hierarchy, exclude him from the results
$move_on = true;
$location_tree = $this->getParentLocations($result->fields['location_id']);
if($location_set)
{
if(!in_array($location, array_keys($location_tree)))
$move_on = false;
}
if($move_on)
{
$search_results[$p_uuid] = array
(
'full_name' => $result->fields['full_name'],
'ids' => ($result->fields['serial']==null)?array():array($result->fields['id_desc'] => $result->fields['serial']),
'date_start' => $result->fields['date_avail_start'],
'date_end' => $result->fields['date_avail_end'],
'locations' => $location_tree,
'skills' => $this->getSkillsAndDescriptions($result->fields['p_uuid']),
'affiliation' => $result->fields['org_name'],
'id_searched' => $id
);
$levenshtein = 0;
if($name_set)
$levenshtein = levenshtein(strtoupper($result->fields['full_name']), strtoupper($name));
$search_results[$p_uuid]['levenshtein'] = $levenshtein;
}
}
$result->MoveNext();
}
/*
* Sort the resulting array by levenshtein distance. To change the acceptable levenshtein distance
* between the inputted name and resulting names, modify SHN_VM_MAX_LEVENSHTEIN in the Constants.php file.
*/
if($name_set)
{
//bucket sort
$buckets = array();
foreach($search_results as $p_uuid => $info)
{
if(isset($buckets[$info['levenshtein']]))
$buckets[$info['levenshtein']][$p_uuid] = $info;
else
$buckets[$info['levenshtein']] = array($p_uuid => $info);
}
$sorted_results = array();
for($i = 0; $i < VM_MAX_LEVENSHTEIN; $i++)
{
if(isset($buckets[$i]))
foreach($buckets[$i] as $p_uuid => $info)
$sorted_results[$p_uuid] = $info;
}
return $sorted_results;
}
else
{
return $search_results;
}
}
/**
* Update the 'phonetic_word' table with volunteer phonetic word information. This is
* included in case someone's name changes outside the VM module.
*
* @return void
*/
function updatePhonetics()
{
$result = $this->execute("SELECT p_uuid, full_name FROM person_uuid WHERE p_uuid IN (SELECT p_uuid FROM vm_vol_active)");
while(!$result->EOF)
{
$p_uuid = $result->fields['p_uuid'];
$full_name = $result->fields['full_name'];
$this->execute("DELETE FROM phonetic_word WHERE pgl_uuid='{$p_uuid}'");
$names = preg_split("/\s+/", $full_name);
foreach($names as $single_name)
{
$this->execute("INSERT INTO phonetic_word VALUES('" . soundex($single_name) . "', '" . metaphone($single_name) . "', '{$p_uuid}')");
}
$result->MoveNext();
}
}
/**
* Assigns a volunteer to a project.
*
* @access public
* @param $vol_id - the p_uuid of the volunteer to assign
* @param $p_id - the ID of the project to assign the volunteer to
* @return void
*/
function assignToProject ($vol_id, $p_id)
{
$this->execute("INSERT INTO vm_vol_assignment (p_uuid,proj_id) values('$vol_id', '$p_id') ");
//send the volunteer a message notifying them of the assignment
$result = $this->execute("SELECT name FROM vm_projects WHERE proj_id = '$p_id'");
$this->sendMessage('SYS_MSG', array($vol_id), "You have been assigned to {proj $p_id {$result->fields['name']}}. \n\n (This is an automated message)");
}
/**
* Retrieves whether or not the volunteer is an approved project manager
*
* @param $p_uuid - the p_uuid of the volunteer
* @return true if the volunteer is an approved project manager, false if not
*/
function isSiteManager($p_uuid)
{
$result = $this->execute("SELECT * FROM vm_user_role WHERE fk_user = '$p_uuid' AND fk_role = ".VM_ROLE_PROJECT_MANAGER);
return !$result->EOF;
}
/*
* retieves a pictue and returns the results
*/
function getVMPicture($img_uuid) {
$result = $this->execute("select image_data, thumb_data, p_uuid, width, height, thumb_width, thumb_height, mime_type, name from vm_image where img_uuid = '$img_uuid'");
if($result->EOF)
return false;
else {
$this->remove_keys($result->fields, MYSQL_ASSOC);
return $result->fields;
}
}
/*
* Save the image or updates a new image that will be displayed as the users image
*/
function saveVMPicture($pic) {
$original = addslashes($pic->original);
$image_data = addslashes($pic->image_data);
$thumb_data = addslashes($pic->thumb_data);
if(!empty($pic->p_uuid))
$this->execute("delete from vm_image where p_uuid = '{$pic->p_uuid}'");
$this->execute("insert into vm_image (img_uuid, original, image_data, thumb_data, p_uuid, date_added, width, height, thumb_width, thumb_height, mime_type, name) " .
"values ('{$pic->img_uuid}', '$original', '$image_data', '$thumb_data', '{$pic->p_uuid}', now(), '{$pic->width}', '{$pic->height}', '{$pic->thumb_width}', '{$pic->thumb_height}', '{$pic->type}', '{$pic->name}')");
}
function getPictureID($p_uuid) {
$result = $this->execute("select img_uuid from vm_image where p_uuid = '$p_uuid'");
if($result->EOF)
return false;
else
return $result->fields['img_uuid'];
}
/*
* Removes the current picture that is display as your image
*/
function deletePicture($img_uuid) {
$this->execute("delete from vm_image where img_uuid = '$img_uuid'");
}
/**
* Retrieves all possible VM access constraints that could apply to any given access request
*
* @return an array, where each key is the access constraint short-hand and each value is
* a description of the access constraint
*/
function getPossibleAccessConstraints()
{
$result = $this->execute("SELECT constraint_id, description FROM vm_access_constraint");
$constraints = array();
while(!$result->EOF)
{
$constraints[$result->fields['constraint_id']] = $result->fields['description'];
$result->MoveNext();
}
return $constraints;
}
/**
* Retrieves constraints on access requests particular to the VM module
*/
function getAccessRequestConstraints()
{
//first get VM-specific access constraints
$result = $this->execute( "SELECT act, vm_action, vm_access_constraint.constraint_id
FROM vm_access_request, vm_access_constraint, vm_access_constraint_to_request
WHERE vm_access_request.request_id = vm_access_constraint_to_request.request_id
AND vm_access_constraint.constraint_id = vm_access_constraint_to_request.constraint_id");
$access = array();
while(!$result->EOF)
{
$act = $result->fields['act'];
$vm_action = $result->fields['vm_action'];
$constraint = $result->fields['constraint_id'];
$req_desc = $result->fields['req_desc'];
if(!is_array($access[$act]))
$access[$act] = array();
if(!is_array($access[$act][$vm_action]))
$access[$act][$vm_action] = array();
if(!is_array($access[$act][$vm_action]['extra']))
$access[$act][$vm_action]['extra'] = array();
$access[$act][$vm_action]['extra'][] = $constraint;
$result->MoveNext();
}
//next get Sahana-specific data classification constraints
$result = $this->execute( "SELECT act, vm_action, table_name, crud
FROM vm_access_request, vm_access_classification_to_request
WHERE vm_access_request.request_id = vm_access_classification_to_request.request_id");
while(!$result->EOF)
{
$act = $result->fields['act'];
$vm_action = $result->fields['vm_action'];
$table_name = $result->fields['table_name'];
$crud = $result->fields['crud'];
if(!is_array($access[$act]))
$access[$act] = array();
if(!is_array($access[$act][$vm_action]))
$access[$act][$vm_action] = array();
if(!is_array($access[$act][$vm_action]['tables']))
$access[$act][$vm_action]['tables'] = array();
$access[$act][$vm_action]['tables'][$table_name] = $crud;
$result->MoveNext();
}
return $access;
}
/**
* Gets a specific access request's name based on act and vm_action
*
* @param $act the act
* @param $vm_action the vm_action
* @return the description if a match found, otherwise null
*/
function getAccessRequestName($act, $vm_action) {
$result = $this->execute("SELECT description FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'");
if(!$result->EOF)
return $result->fields['description'];
else
return null;
}
/**
* Retreives all access requests
*/
function getAccessRequests()
{
$result = $this->execute("SELECT act, vm_action, description FROM vm_access_request ORDER BY description");
$requests = array();
while(!$result->EOF)
{
$act = $result->fields['act'];
$vm_action = $result->fields['vm_action'];
$desc = $result->fields['description'];
if(!is_array($requests[$act]))
$requests[$act] = array();
$requests[$act][$vm_action] = $desc;
$result->moveNext();
}
return $requests;
}
/**
* Retreives all access requests and return them in a way that is easier for displaying
*/
function getAccessRequestsForDisplay()
{
$result = $this->execute("SELECT act, vm_action, description FROM vm_access_request ORDER BY description");
$requests = array();
while(!$result->EOF)
{
$act = $result->fields['act'];
$vm_action = $result->fields['vm_action'];
$desc = $result->fields['description'];
$matches = array();
preg_match("/^\s*(\w+)(.*)/", $desc, $matches);
$requests[] = array('display_action' => $matches[1], 'partial_desc' => $matches[2], 'act' => $act, 'vm_action' => $vm_action);
$result->MoveNext();
}
return $requests;
}
/**
* Retrieves all access constraints for a particular request situation
*/
function getSpecificAccessRequestConstraints($act, $vm_action)
{
$constraints = array('tables' => array(), 'extra' => array());
//special case access constraints
$result = $this->execute( "SELECT vm_access_constraint.constraint_id, vm_access_constraint.description
FROM vm_access_request, vm_access_constraint, vm_access_constraint_to_request
WHERE act = '$act' AND vm_action = '$vm_action'
AND vm_access_request.request_id = vm_access_constraint_to_request.request_id
AND vm_access_constraint.constraint_id = vm_access_constraint_to_request.constraint_id");
while(!$result->EOF)
{
$constraints['extra'][] = $result->fields['constraint_id'];
$result->MoveNext();
}
//data classification access constraints
$result = $this->execute( "SELECT table_name, crud
FROM vm_access_request, vm_access_classification_to_request
WHERE act = '$act' AND vm_action = '$vm_action'
AND vm_access_request.request_id = vm_access_classification_to_request.request_id");
while(!$result->EOF)
{
$constraints['tables'][$result->fields['table_name']] = $result->fields['crud'];
$result->MoveNext();
}
return $constraints;
}
/**
* Updates the 'vm_access_classification_to_request' table with new data classification access information
*
* @param $act - the 'act' of the request to modify
* @param $vm_action - the 'vm_action' of the request to modify
* @param $tables - an array specifying the tables and permissions to modify, where each key is the table name
* and each value is the permissions (in 'crud' format to require)
* @return void
*/
function updateClassificationConstraints($act, $vm_action, $tables)
{
//first get the request_id of the request
$result = $this->execute("SELECT request_id FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'");
$request_id = $result->fields['request_id'];
//get rid of the old classification constraints
$this->execute("DELETE FROM vm_access_classification_to_request WHERE request_id = $request_id");
//add the new classification constraints
foreach($tables as $table_name => $crud)
$this->execute("INSERT INTO vm_access_classification_to_request (request_id, table_name, crud) VALUES ($request_id, '$table_name', '$crud')");
}
/**
* Retrieves all tables in the database
*/
function getDBTables()
{
$tables = array();
$result = $this->execute("SHOW TABLES");
while(!$result->EOF)
{
$tables[] = $result->fields[0];
$result->MoveNext();
}
return $tables;
}
/**
* Retreives the description of an access request
*
* @param $act - the 'act' to look up by
* @param $vm_action - the 'vm_action' to look up by
* @return the description
*/
function getAccessRequestDescription($act, $vm_action)
{
$result = $this->execute("SELECT description FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'");
return $result->fields['description'];
}
/**
* Adds a constraint on an access request
*/
function addConstraint($act, $vm_action, $constraint)
{
$this->execute("INSERT INTO vm_access_constraint_to_request (request_id, constraint_id) VALUES
((SELECT request_id FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'), '$constraint')");
}
/**
* Removes all access constraints on an access request
*/
function removeConstraints($act, $vm_action)
{
$this->execute("DELETE FROM vm_access_constraint_to_request WHERE request_id =
(SELECT request_id FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action')");
}
/**
* Retrieves a set of skills and descriptions of those skills
*
* $id - the ID to search by
* $proj - true to return a project's needed set of skills
*/
function getSkillsAndDescriptions($id)
{
$result = $this->execute("select opt_skill_code, option_description from vm_vol_skills, field_options where p_uuid = '{$id}' AND opt_skill_code = option_code order by option_description asc");
$options = array();
while(!$result==NULL && !$result->EOF)
{
$options[$result->fields['opt_skill_code']] = $result->fields['option_description'];
$result->MoveNext();
}
return $options;
}
function assignVolunteerToPosition($p_uuid, $pos_id) {
$this->execute("delete from vm_vol_position where p_uuid = '$p_uuid' and pos_id = '$pos_id'");
$this->execute("insert into vm_vol_position (p_uuid, pos_id, date_assigned) values ('$p_uuid', '$pos_id', now())");
}
/**
* Retrieves a set of volunteer p_uuids and names
*
* @param $all - (optional, default false) true to return all volunteers, not only active ones
* @return an array where each key is the volunteer's id and each value is the corresponding name
*/
function getVolunteerNames($all=false)
{
$q = "SELECT person_uuid.p_uuid, full_name FROM vm_vol_details JOIN person_uuid USING(p_uuid)";
if(!$all)
$q .= " WHERE status = 'active'";
$result = $this->execute($q);
$vols = array();
while(!$result->EOF)
{
$vols[$result->fields['p_uuid']] = $result->fields['full_name'];
$result->moveNext();
}
return $vols;
}
function logShift($p_uuid, $pos_id, $start, $end) {
if(!empty($p_uuid) && !empty($pos_id) && !empty($start) && !empty($end)) {
$startStamp = date("Y-m-d H:i:s", $start);
$endStamp = date("Y-m-d H:i:s", $end);
$this->execute("insert into vm_hours (p_uuid, pos_id, shift_start, shift_end)
values ('$p_uuid', '$pos_id', '$startStamp', '$endStamp')");
return true;
} else return false;
}
/**
* Retrieves whether or not the given volunteer is a project manager for the given project
*/
function isSiteManagerForProject($p_uuid, $proj_id)
{
$result = $this->execute("SELECT 1 FROM vm_vol_assignment_active WHERE p_uuid = '$p_uuid' AND proj_id = '$proj_id' AND ptype_id = 'smgr'");
return !$result->EOF;
}
/**
* Retrieves whether or not the given 'act' and 'vm_action' are in the vm_access_request table
*/
function isAccessRequest($act, $vm_action)
{
$result = $this->execute("SELECT 1 FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'");
return !$result->EOF;
}
/**
* Retrieves whether or not the given database table is given any classification level under Sahana's ACL system
*/
function isClassified($table)
{
$result = $this->execute("SELECT 1 FROM sys_tablefields_to_data_classification WHERE table_field = '$table'");
return !$result->EOF;
}
/**
* Retreieves whether or not the given position is or was ever in a project for which the given volunteer is a project manager for
*
* @param $pos_id - the position ID
* @param $p_uuid - the project manager's p_uuid
* @return true if $pos_id is a position in any of $p_uuid's projects, active or retired
*/
function isPositionUnderManager($pos_id, $p_uuid)
{
$result = $this->execute("SELECT 1 FROM vm_position WHERE '$pos_id' IN (SELECT pos_id FROM vm_position_full WHERE proj_id IN (SELECT proj_id FROM vm_vol_assignment_active WHERE p_uuid = '$p_uuid' AND ptype_id = 'smgr'))");
return !$result->EOF;
}
/**
* Retrieves all possible levels of data classification
*
* @return an array, where each key is the level and each value is its description
*/
function getDataClassificationLevels()
{
$result = $this->execute("SELECT level_id, level FROM sys_data_classifications");
$levels = array();
while(!$result->EOF)
{
$levels[$result->fields['level_id']] = $result->fields['level'];
$result->moveNext();
}
return $levels;
}
/**
* Adds an access request
*
* @param $act - the act parameter of the request
* @param $vm_action - the vm_action parameter of the request
* @param $description - a brief description of what is being requested when the request is loaded
* @return void
*/
function addAccessRequest($act, $vm_action, $description)
{
$this->execute("INSERT INTO vm_access_request (act, vm_action, description) VALUES ('$act', '$vm_action', '$description')");
}
/**
* Removes an access request
*
* @param $act - the act parameter of the request
* @param $vm_action - the vm_action parameter of the request
* @return void
*/
function removeAccessRequest($act, $vm_action)
{
$result = $this->execute("SELECT request_id FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'");
$id = $result->fields['request_id'];
$this->execute("DELETE FROM vm_access_request WHERE request_id = '$id'");
$this->execute("DELETE FROM vm_access_constraint_to_request WHERE request_id = '$id'");
$this->execute("DELETE FROM vm_access_classification_to_request WHERE request_id = '$id'");
}
/**
* Gives the table the given classification level
*
* @param $table - the table to classify
* @param $level - the level ID to classify the table as
* @return void
*/
function classifyTable($table, $level)
{
$this->execute("DELETE FROM sys_tablefields_to_data_classification WHERE table_field = '$table'");
$this->execute("INSERT INTO sys_tablefields_to_data_classification (table_field, level_id) VALUES ('$table', '$level')");
}
/**
* Retrieves the various vm roles that the user has been assigned to
*
* @param $p_uuid - the user's p_uuid
* @return an array of integers representing the roles (which are defined in Constants.php)
*/
function getUserRoles($p_uuid) {
$result = $this->execute("SELECT fk_role FROM vm_user_role WHERE fk_user = '$p_uuid'");
$roles = array();
while(!$result->EOF) {
$roles[] = $result->fields['fk_role'];
$result->moveNext();
}
return $roles;
}
/**
* Retrieves the roles that have access to the given access request
*
* @param $act - the act from the URL
* @param $vm_action - the vm_action from the URL
* @return an array of integers representing the roles (which are defined in Constants.php)
*/
function getAccessRequestRoles($act, $vm_action) {
$result = $this->execute("SELECT fk_role FROM vm_access_request JOIN vm_access_request_role ON (request_id = fk_access_request) WHERE act = '$act' AND vm_action = '$vm_action'");
$roles = array();
while(!$result->EOF) {
$roles[] = $result->fields['fk_role'];
$result->moveNext();
}
return $roles;
}
/**
* Retrieves all possible vm roles
*
* @return an array of role => descriptions
*/
function getVMRoles() {
$result = $this->execute("SELECT id, description FROM vm_role");
$roles = array();
while(!$result->EOF) {
$roles[$result->fields['id']] = $result->fields['description'];
$result->moveNext();
}
return $roles;
}
/**
* Updates the roles that have access to a given request
*
* @param $act - the act for the request
* @param $vm_action - the vm_action for the request
* @param $roles - an array of role ids representing all of the roles to give access to
* (only these roles will have access to this request when the function has returned)
*/
function updateAccessRequestRoles($act, $vm_action, $roles) {
$result = $this->execute("SELECT request_id FROM vm_access_request WHERE act = '$act' AND vm_action = '$vm_action'");
$id = $result->fields['request_id'];
$this->execute("DELETE FROM vm_access_request_role WHERE fk_access_request = $id");
foreach($roles as $role_id) {
$this->execute("INSERT INTO vm_access_request_role (fk_access_request, fk_role) VALUES ($id, $role_id)");
}
}
/**
* Updates the roles for a user
*
* @param $p_uuid - the user's p_uuid
* @param $roles - an array of role ids representing all of the roles for the user
* (the user will have only these roles when the function has returned)
*/
function updateUserRoles($p_uuid, $roles) {
$this->execute("DELETE FROM vm_user_role WHERE fk_user = '$p_uuid'");
foreach($roles as $role_id) {
$this->execute("INSERT INTO vm_user_role (fk_user, fk_role) VALUES ('$p_uuid', $role_id)");
}
//also handle skill for project manager if necessary
$result = $this->execute("SELECT 1 FROM vm_vol_skills WHERE p_uuid = '$p_uuid' AND opt_skill_code = 'MGR'");
$has_mgr_skill = !$result->EOF;
if($has_mgr_skill && in_array(VM_ROLE_PROJECT_MANAGER, $roles)) {
$this->execute("UPDATE vm_vol_skills SET status = 'approved' WHERE p_uuid = '$p_uuid' AND opt_skill_code = 'MGR'");
} else if($has_mgr_skill && !in_array(VM_ROLE_PROJECT_MANAGER, $roles)) {
$this->execute("UPDATE vm_vol_skills SET status = 'denied' WHERE p_uuid = '$p_uuid' AND opt_skill_code = 'MGR'");
} else if(!$has_mgr_skill && in_array(VM_ROLE_PROJECT_MANAGER, $roles)) {
$this->execute("INSERT INTO vm_vol_skills (p_uuid, opt_skill_code, status) VALUES ('$p_uuid', 'MGR', 'approved')");
}
}
}
?>