/*DATABASE FUNCTIONS*/
	/*
	Create a connection with the mysql database.
	The format is mysqli object-oriented with prepared statements.
	This must be correct with the current DB info.
	*/
	function open_db_connection($db_type)
	{
		$db_conn = ''; 

		/*$db = 'cftbUser.db.6560963.hostedresource.com';
		$db_user = 'cftbUser';
		$db_pass = 'CFTb@#17';
		$db_name = 'cftbUser';*/
		
		/*$db = '198.71.235.79';
		$db_user = 'cftbUser';
		$db_pass = 'CFTb@#17';
		$db_name = 'cftbUser';*/
		
		switch($db_type)
		{
			case 1://adminbeta db
				{
					$db = 'localhost';
					$db_user = 'compu200_cftbUser';
					$db_pass = '%I^R75eMg9Wu';
					$db_name = 'compu200_cftbUser';
					break;
				}
			case 2://center db
				{
					$db = 'localhost';
					$db_user = 'compu200_cftbUser';
					$db_pass = '%I^R75eMg9Wu';
					$db_name = 'compu200_cftbcentertest';
					break;
				}
		}
        
        

		$db_conn = new MySQLi($db,$db_user,$db_pass,$db_name);
		return $db_conn;
	}

	/*
	This function should be called at the end 
	of every script that calls open_db_connection().
	*/
	function close_db_connection($db_conn)
	{
		$db_conn->close();
	}

	/*
	This function will be updated as needed. Right now it only 
    checks the session cookie for login status.
	*/
	function login_check()
	{
		//checks the session. If it passes, the user is logged on and can continue
		if(isset($_SESSION['login']) && $_SESSION['login'] == 1)
		{
			return true;
		}
		//session is not valid and cookie is not present, user is not logged in
		else
		{
			return false;
		}
	}

    function logout()
	{
		session_start();
		session_destroy();
	}

    function getTotalBuildCount()
    {
        $id = $_SESSION['id'];
        $db_conn = open_db_connection(1);
        $totalBuild = 0;
        
        
        //get all matches from client database
        $sql_cmd = $db_conn->prepare('SELECT id from clients where bldid=?');
        $sql_cmd->bind_param('i',$id);
        $sql_cmd->execute();
        $sql_cmd->store_result();
        $totalBuild += $sql_cmd->num_rows;

        $sql_cmd->close();

        //get all matches from reship database
        $sql_cmd2 = $db_conn->prepare('SELECT id from reships where bldid=?');
        $sql_cmd2->bind_param('i',$id);
        $sql_cmd2->execute();
        $sql_cmd2->store_result();
        $totalBuild += $sql_cmd2->num_rows;

        $sql_cmd2->close();

        close_db_connection($db_conn);
        
        return $totalBuild;
    }

    function getTotalVolunteerHours()
    {
        $id = $_SESSION['id'];
                    
        $db_conn = open_db_connection(1);
        $sql_cmd = $db_conn->prepare('SELECT starttime, endtime FROM timecard WHERE volunteerid=?');
        $sql_cmd->bind_param('i',$id);
        $sql_cmd->execute();
        $sql_cmd->store_result();
        $rows = $sql_cmd->num_rows;

        if($rows > 0)
        {
            $sql_cmd->bind_result($starttime, $endtime);
            $totalHours = $tempMinutes = 0;

            while($sql_cmd->fetch())
            {
                if($starttime != '00:00:00' && $endtime != '00:00:00')
                {
                    $formattedStartTime = new DateTime($starttime);
                    $formattedEndTime = new DateTime($endtime);
                    $difference = $formattedStartTime->diff($formattedEndTime);

                    $totalHours += $difference->h;
                    $tempMinutes += $difference->i;

                }
            }

            $convertMinutes = $tempMinutes / 60;//find how many hours in the total minutes column
            $addHours = (int)$convertMinutes;
            $totalMinutes = $tempMinutes - ($addHours * 60);

            $totalHours += $addHours;
        }
        else
        {
            $totalHours = 0;
            $totalMinutes = 0;
        }
        
        $totalTime = ["totalHours"=>$totalHours, "totalMinutes"=>$totalMinutes];

        $sql_cmd->close();
        close_db_connection($db_conn);
        
        return $totalTime;
    }

    /*Checks the database for inputted initials. If it finds a
    match, it checks if that match is the same person that is 
    submitting the request. If it is, it will return 0.
    If it is not the same person, it will return 1. If it finds
    multiple uses of the same initials, it will return 2, and the 
    user will need to sort that out.*/
	function checkVolunteerInitials($initials,$id)
	{
        if($initials == '')
        {
            return 0;
        }
        else
        {
            $db_conn = open_db_connection(1);

            $sql_cmd = $db_conn->prepare('SELECT id FROM volunteers WHERE initials=?');
            $sql_cmd->bind_param('s',$initials);
            $sql_cmd->execute();
            $sql_cmd->store_result();
            $sql_cmd->bind_result($checkID);
            $sql_cmd->fetch();
            $rows = $sql_cmd->num_rows;
            if($rows == 1)//initials are already in use
            {
                if($id == $checkID)//result found is same person
                {
                    return 0;
                }
                else
                {
                    return 1;
                }
            }
            else if($rows > 1)//multiple uses of initials
            {
                return 2;
            }
            else//initials are not in use
            {
                return 0;
            }

            $sql_cmd->close();
            close_db_connection($db_conn);
        }
	}

    /*Checks the database for inputted username. If it finds a
    match, it will check if the match belongs to the volunteer
    that is submitting the request. If it is, it will return a 0.
    If not, it will return a 1.*/
	function checkVolunteerUsername($username,$id)
	{
        if($username == '')
        {
            return 0;
        }
        else
        {
            $db_conn = open_db_connection(1);

            $sql_cmd = $db_conn->prepare('SELECT id FROM volunteers WHERE username=?');
            $sql_cmd->bind_param('s',$username);
            $sql_cmd->execute();
            $sql_cmd->store_result();
            $sql_cmd->bind_result($checkID);
            $sql_cmd->fetch();
            $rows = $sql_cmd->num_rows;
            if($rows > 0)//username is already in use
            {
                if($id == $checkID)//result found is same person
                {
                    return 0;
                }
                else
                {
                    return 1;
                }
            }
            else//username is not in use
            {
                return 0;
            }

            $sql_cmd->close();
            close_db_connection($db_conn);
        }
	}




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


	function machineType($id)
	{
		switch($id)
		{
			case 1:
				return 'Desktop';
				break;
			case 2:
				return 'Laptop';
				break;
			default:
				return 'Unknown';
		}
	}

	function category($id,$text)
	{
		switch($id)
		{
			case 1:
				return 'NVDA';
				break;
			case 2:
				return 'MAGic';
				break;
			case 3:
				return 'PO# '.$text;
				break;
			case 4:
				return 'Zoomtext';
				break;
			case 5:
				return 'WinEyes';
				break;
			case 6:
				return 'Fusion';
				break;
			case 7:
				return 'JAWS';
				break;
			case 8:
				return 'DEMO';
				break;
			default:
				return 'Unknown';
		}
	}

	function payment($id)
	{
		switch($id)
		{
			case 1:
				return 'Paypal';
				break;
			case 2:
				return 'Check';
				break;
			default:
				return 'Unknown';
		}
	}

    function clientStage($id)
    {
        switch($id)
		{
			case 0:
				return 'Processed';
			case 1:
				return 'Assigned';
			case 2:
				return 'Awaiting Check';
			case 3:
				return 'Quality Checked';
            case 4:
                return 'Packed';
            case 5:
                return 'Shipped';
            case 6:
                return 'Cancelled';
            case 7:
                return 'Returning';
            case 8:
                return 'Under Repair';
            case 9:
                return 'Replaced';
            case 10:
                return 'Returned';
            case 11:
                return 'Refunded';
			default:
				return 'Unknown';
		}
    }

    function getClientStage($stage)
    {
        switch($stage)
        {
            case 'Processed':
				return 0;
			case 'Assigned':
				return 1;
			case 'Awaiting Check':
				return 2;
			case 'Quality Checked':
				return 3;
            case 'Packed':
                return 4;
            case 'Shipped':
                return 5;
            case 'Cancelled':
                return 6;
            case 'Returning':
                return 7;
            case 'Under Repair':
                return 8;
            case 'Replaced':
                return 9;
            case 'Returned':
                return 10;
            case 'Refunded':
                return 11;
			default:
				return 'Unknown';
        }
    }

	function reshipType($id)
    {
        switch($id)
		{
			case 0:
				return 'Unknown';
			case 1:
				return 'Return for Repair';
			case 2:
				return 'Ship Immediately';
			case 3:
				return 'Return for Refund';
            case 4:
                return 'Revised Machine Order';
            case 5:
                return 'Change of Addons';
            case 6:
                return 'Replacement Order';
            case 7:
                return 'Ship New Addon';
			default:
				return 'Unknown';
		}
    }

	function ticketStatus($id)
	{
		switch($id)
		{
			case 0:
				return 'Unopened';
			case 1:
				return 'Reviewed';
			case 2:
				return 'In Progress';
			case 3:
				return 'Resolved';
			default:
				return 'Unknown';
		}
	}

	function addLinkedID($type,$id,$link)
	{
		$db_conn = open_db_connection(1);
		if($type == 0)//request to link is for a potential client
		{
			//first find client at $link to see if it has a link already

			$sql_cmd = $db_conn->prepare('SELECT nextid FROM clients WHERE id=?');
			$sql_cmd->bind_param('i',$link);
			$sql_cmd->execute();
			$sql_cmd->store_result();
			$sql_cmd->bind_result($nextid);
			$sql_cmd->fetch();
			$sql_cmd->close();
			
			//check if there are other later linked clients
			//assume they are linked properly (previd matches nextid of link)
			while($nextid != '')
			{
				$link = $nextid;
				$sql_cmd2 = $db_conn->prepare('SELECT nextid FROM clients WHERE id=?');
				$sql_cmd2->bind_param('i',$nextid);
				$sql_cmd2->execute();
				$sql_cmd2->store_result();
				$sql_cmd2->bind_result($nextid);
				$sql_cmd2->fetch();
				$sql_cmd2->close();
			}
			close_db_connection($db_conn);
			return setPrevID(0,$id,$link);
		}
		else if($type == 1)//request to link is when potential client added to db
		{
			//see if client already has links
			$sql_cmd3 = $db_conn->prepare('SELECT nextid FROM clients WHERE id=?');
			$sql_cmd3->bind_param('i',$link);
			$sql_cmd3->execute();
			$sql_cmd3->store_result();
			$sql_cmd3->bind_result($nextid);
			$sql_cmd3->fetch();
			$sql_cmd3->close();
			
			//check if there are other later linked clients
			//assume they are linked properly
			while($nextid != '')
			{
				$link = $nextid;
				$sql_cmd2 = $db_conn->prepare('SELECT nextid FROM clients WHERE id=?');
				$sql_cmd2->bind_param('i',$nextid);
				$sql_cmd2->execute();
				$sql_cmd2->store_result();
				$sql_cmd2->bind_result($nextid);
				$sql_cmd2->fetch();
				$sql_cmd2->close();
			}
			close_db_connection($db_conn);
			if(!setPrevID(1,$id,$link))//check whether setting the previd was successful
			{
				return false;
			}
			else
			{
				return setNextID(1,$link,$id);//attempt to set the nextid of previous client
			}
		}
		else if($type == 2)//request is to link a newly created client
		{
			$sql_cmd = $db_conn->prepare('SELECT nextid FROM clients WHERE id=?');
			$sql_cmd->bind_param('i',$link);
			$sql_cmd->execute();
			$sql_cmd->store_result();
			$sql_cmd->bind_result($nextid);
			$sql_cmd->fetch();
			$sql_cmd->close();
			
			//check if there are other later linked clients
			//assume they are linked properly (previd matches nextid of link)
			while($nextid != '')
			{
				$link = $nextid;
				$sql_cmd2 = $db_conn->prepare('SELECT nextid FROM clients WHERE id=?');
				$sql_cmd2->bind_param('i',$nextid);
				$sql_cmd2->execute();
				$sql_cmd2->store_result();
				$sql_cmd2->bind_result($nextid);
				$sql_cmd2->fetch();
				$sql_cmd2->close();
			}
			close_db_connection($db_conn);
			return $link;
		}
		else if($type == 3)//request is to link 2 existing clients
		{
			
		}
		
	}

	/*Sets the previd field for the client submitted. 
	type: which situation is ocurring
	id: the client that will have the field set
	link: the client number to add to that field*/
	function setPrevID($type,$id,$link)
	{
		$db_conn = open_db_connection(1);
		if($type == 0)//request to link is for a potential client
		{
			$sql_cmd = $db_conn->prepare('UPDATE tempclients set previd=? WHERE id=?');
			$sql_cmd->bind_param('ii',$link,$id);
			if($sql_cmd->execute())
			{
				$sql_cmd->close();
				close_db_connection($db_conn);
				return true;
			}
			else
			{
				$sql_cmd->close();
				close_db_connection($db_conn);
				return false;
			}
		}
		else if($type == 1)//request to link is for existing clients
		{
			$sql_cmd = $db_conn->prepare('UPDATE clients set previd=? WHERE id=?');
			$sql_cmd->bind_param('ii',$link,$id);
			if($sql_cmd->execute())
			{
				$sql_cmd->close();
				close_db_connection($db_conn);
				return true;
			}
			else
			{
				$sql_cmd->close();
				close_db_connection($db_conn);
				return false;
			}
		}
	}

	/*Sets the nextid field for the client submitted. 
	type: which situation is ocurring
	id: the client that will have the field set
	link: the client number to add to that field*/
	function setNextID($type,$id,$link)
	{
		$db_conn = open_db_connection(1);
		if($type == 1)//request to link is for existing clients
		{
			$sql_cmd = $db_conn->prepare('UPDATE clients set nextid=? WHERE id=?');
			$sql_cmd->bind_param('ii',$link,$id);
			if($sql_cmd->execute())
			{
				$sql_cmd->close();
				close_db_connection($db_conn);
				return true;
			}
			else
			{
				$sql_cmd->close();
				close_db_connection($db_conn);
				return false;
			}
		}
	}

	function grantSelect()
	{
		$db_conn = open_db_connection(1);
		$html = '';
		
		$sql_cmd = $db_conn->prepare('SELECT id,grantname,comments,remaining FROM grantlist');
		$sql_cmd->execute();
		$sql_cmd->store_result();
		$rows = $sql_cmd->num_rows;
		
		$html .= '
						<option value="1">None</option>
					';

		if($rows > 0)
		{
			$sql_cmd->bind_result($id,$grantname,$comments,$remaining);
			
			while($sql_cmd->fetch())
			{
				$text = '';
				if($remaining == 0)
				{
					$text = 'disabled';
				}
				$html .= '
						<option value="'.$id.'" title="'.$comments.'" '.$text.'>'.$grantname.'</option>
				';
			}
			$sql_cmd->close();
		}
		
		return $html;
	}

	function grantName($id)
	{
		$db_conn = open_db_connection(1);
		
		$sql_cmd = $db_conn->prepare("SELECT name from grantlist WHERE id=?");
		$sql_cmd->bind_param('i',$id);
		$sql_cmd->execute();
		$sql_cmd->store_result();
		$sql_cmd->bind_result($name);
		$sql_cmd->fetch();
		$sql_cmd->close();
		
		close_db_connection($db_conn);
		
		return $name;
	}

	function staffInitials($id)//change to get from db
	{
		switch($id)
		{
			case 0:
				return '';
				break;
			case 1:
				return 'nch';
				break;
			case 2:
				return 'jlk';
				break;
			case 3:
				return 'mlm';
				break;
			case 4:
				return 'jag';
				break;
			case 5:
				return 'kxm';
				break;
			case 6:
				return 'sag';
				break;
			case 7:
				return 'gxb';
				break;
			case 8:
				return 'mxd';
				break;
			case 9:
				return 'dlj';
				break;
			case 10:
				return 'alm';
				break;
			case 11:
				return 'rjf';
				break;
			case 12:
				return 'eal';
				break;
			case 13:
				return 'sae';
				break;
			case 14:
				return 'dgb';
				break;
			case 15:
				return 'dmh';
				break;
			case 16:
				return 'jda';
				break;
			case 17:
				return 'ege';
				break;
			case 18:
				return 'kem';
				break;
			case 19:
				return 'maj';
				break;
			default:
				return 'unk';
		}
	}

	function getClientType($machine,$category)
	{
		$fullMachine = '';
		
		switch($machine)
		{
			case 1:
				$fullMachine .= 'Desktop';
				break;
				
			case 2:
				$fullMachine .= 'Laptop';
				break;
				
			default:
				$fullMachine .= 'Unknown';
		}
		
		switch($category)
		{
			case 1:
				$fullMachine .= ' NVDA';
				break;
				
			case 2:
				$fullMachine .= ' MAGic';
				break;
				
			case 3:
				$fullMachine .= ' P.O.';
				break;
			
			case 4:
				$fullMachine .= ' Zoomtext';
				break;
				
			case 5:
				$fullMachine .= ' WinEyes';
				break;
				
			case 6:
				$fullMachine .= ' Fusion';
				break;
				
			case 7:
				$fullMachine .= ' JAWS';
				break;
				
			default:
				$fullMachine .= ' Unknown';
		}
		
		return $fullMachine;
	}

	function printSystem($machine,$category,$instruction,$wifi,$webcam,$numpad,$lglcd,$keyboard,$mouse,$hdd,$ram,$ttt)
	{
		if($machine == 1)
		{
			$output = getClientType($machine,$category);
			if($ttt)
			{
				$output .= ' TTT';
			}
			if($wifi)
			{
				$output .= ' WIFI';
			}
			if($webcam)
			{
				$output .= ' Webcam';
			}
			if($lglcd)
			{
				$output .= ' Lrg LCD';
			}
			switch($hdd)
			{
				case 1:
					break;
				case 2:
					$output .= ' 320GB';
					break;
				case 3:
					$output .= ' 500GB';
					break;
				case 4:
					$output .= ' 640GB';
					break;
				case 5:
					$output .= ' 1TB';
					break;
			}
			switch($ram)
			{
				case 1:
					break;
				case 2:
					$output .= ' 6GB';
					break;
				case 3:
					$output .= ' 8GB';
					break;
			}
		}
		else if($machine == 2)
		{
			$output = getClientType($machine,$category);
			if($ttt)
			{
				$output .= ' TTT';
			}
			if($numpad)
			{
				$output .= ' Numpad';
			}
			if($lglcd)
			{
				$output .= ' Lrg LCD';
			}
			if($keyboard)
			{
				$output .= ' Ext Keyboard';
			}
			if($mouse)
			{
				$output .= ' Ext Mouse';
			}
			switch($hdd)
			{
				case 1:
					break;
				case 2:
					break;
				case 3:
					$output .= ' 500GB';
					break;
				case 4:
					break;
				case 5:
					$output .= ' 1TB';
					break;
			}
			switch($ram)
			{
				case 1:
					break;
				case 2:
					$output .= ' 6GB';
					break;
				case 3:
					$output .= ' 8GB';
					break;
			}
		}
		$output  .= ' ' . $instruction;
		return $output;
	}

	function roles()
	{
		$output = '
			<option value="9">Build Volunteer</option>
			<option value="8">Triage Volunteer</option>
			<option value="10">Repair Volunteer</option>
			<option value="7">Senior Volunteer</option>
			<option value="6">Customer Service</option>
			<option value="5">Technical Support</option>
			<option value="4">Board Member</option>
			<option value="3">Administration</option>
			<option value="2">Operations</option>
			<option value="1">IT Director</option>
		';
		
		return $output;
	}

	function printRoles($type)
	{
		switch($type)
		{
			case 1:
				return 'IT Director';
				break;
			case 2: 
				return 'Operations';
				break;
			case 3:
				return 'Administration';
				break;
			case 4:
				return 'Board Member';
				break;
			case 5:
				return 'Technical Support';
				break;
			case 6:
				return 'Customer Support';
				break;
			case 7:
				return 'Senior Volunteer';
				break;
			case 8:
				return 'Triage Volunteer';
				break;
			case 9:
				return 'Build Volunteer';
				break;
			case 10:
				return 'Repair Volunteer';
				break;
			default:
				return 'Unknown';
		}
	}

	function notifications($type)
	{
		switch($type)
		{
			case 1: //new client notifications
				$status = 0;
				
				$db_conn = open_db_connection(1);
				
				$sql_cmd = $db_conn->prepare('SELECT started FROM tempclients');
				$sql_cmd->execute();
				$sql_cmd->store_result();
				$rows = $sql_cmd->num_rows;
				
				if($rows > 0)
				{
					$sql_cmd->bind_result($started);
					
					while($sql_cmd->fetch())
					{
						if(!$started)
						{
							$status = 1;
							break;
						}
					}				  
				}
				
				$sql_cmd->close();
				close_db_connection($db_conn);
				return $status;
			case 2: //ticket notifications
				$status = 0;
				
				$db_conn = open_db_connection(1);
				
				$sql_cmd = $db_conn->prepare('SELECT state FROM tickets');
				$sql_cmd->execute();
				$sql_cmd->store_result();
				$rows = $sql_cmd->num_rows;
				
				if($rows > 0)
				{
					$sql_cmd->bind_result($state);
					
					while($sql_cmd->fetch())
					{
						if($state == 0)
						{
							$status = 1;
							break;
						}
					}				  
				}
				
				$sql_cmd->close();
				close_db_connection($db_conn);
				return $status;
			case 3: //corporate donor notifications
				$status = 0;
				
				$db_conn = open_db_connection(1);
				
				$sql_cmd = $db_conn->prepare('SELECT contacted FROM corporate');
				$sql_cmd->execute();
				$sql_cmd->store_result();
				$rows = $sql_cmd->num_rows;
				
				if($rows > 0)
				{
					$sql_cmd->bind_result($contacted);
					
					while($sql_cmd->fetch())
					{
						if(!$contacted)
						{
							$status = 1;
							break;
						}
					}				  
				}
				
				$sql_cmd->close();
				close_db_connection($db_conn);
				return $status;
			default:
				return false;
		}
	}

	function clientUpdates($clientid)
	{
		$lasteditby = $_SESSION['id'];
		$lasteditdate = date("Y-m-d H:i:s");

		$db_conn = open_db_connection(1);

		$sql_cmd = $db_conn->prepare("UPDATE clients SET lastedit=?, lasteditdate=? WHERE id=?");
		$sql_cmd->bind_param('isi',$lasteditby,$lasteditdate,$clientid);
		$sql_cmd->execute();
		$sql_cmd->close();

		close_db_connection($db_conn);
	}