susignup-admin.php

<?PHP
/***
 * SU Signup admin 
 * Allows us to search the SU api for a given student number and checks if they're a SUCS member.
 * If they are - allows account renewal
 * If not - allows signup to be bootstrapped as per susignup component
 ***/

include("../lib/member_functions.php");
include("../lib/date.php");
include("../suapi.inc.php");

//Restrict access to staff. 
$permission="sucsstaff";
$apibaseurl="https://$suapi_user:$suapi_pass@su-api.sucs.org/memberships/Membership.asmx";

if (isset($session->groups[$permission])) {
	//Setup smarty magic, step 1
	$smarty->assign("staff", TRUE);

	if(!isset($_REQUEST['mode'])) {
		$mode = 'menu';
	} else {
		$mode = urldecode($_REQUEST['mode']);
	}

	//Set up SUCS DB Connection.
	//Note that $DB is the generic sucssite connection
	
	$sucsDB = NewADOConnection('postgres8');
	$sucsDB->Connect('dbname=sucs user=apache');
	$sucsDB->SetFetchMode(ADODB_FETCH_ASSOC);

	if ($mode == 'search') {
		if (empty($_REQUEST['sid']) || empty($_REQUEST['snsubmit'])) {
			$mode = 'error';
			$smarty->assign("error_text", "Invalid search request");	
		}else{
			$pres=preg_match("/^[0-9]{6}$/",$_REQUEST['sid'],$sid);
			if ($pres!=1) {
				$mode = 'error';
				$smarty->assign("error_text", "Search term doesn't look like a valid student ID");
			} else {
				$url = "$apibaseurl/IsPersonMember?strCriteria=".$sid[0]."&GroupingId=6613";
				$apiReq = curl_init();
				curl_setopt($apiReq, CURLOPT_URL, $url);
				curl_setopt($apiReq, CURLOPT_RETURNTRANSFER, TRUE);

				$apiResult = curl_exec($apiReq);
				if ($apiResult === FALSE) {
					$mode = 'error';
					$smarty->assign("error_text", "An error occurred communicating with the SUSU API.");
				}else {
					// Ostensibly we now have a valid search result from the SU - go to work
					libxml_use_internal_errors(true);
					$xml=simplexml_load_string($apiResult);
					if (!$xml||isset($xml->head->title)) {
						$mode='error';
						$smarty->assign("error_text", "An error occurred communicating with the SUSU API");
					} else {
						$ismember = $xml[0];
						if ($ismember=="true") {
							//Yay, we have a student who has paid and needs to be signed up.
							//Check they don't have a signup slip already
							$query = "SELECT transactionid, signupid FROM transactions WHERE cardNumber = ?;";
							$qres = $sucsDB->Execute($query, $sid);
							
							if ($qres->RecordCount()==0) {
								// No transaction, but might have unused signup slip. If so, retrieve values.
								$query = "SELECT id, username, password FROM signup WHERE sid=?;";
								$qres = $sucsDB->Execute($query, array($sid[0]));
								if ($qres && $qres->RecordCount() > 0) {
									if ($qres->RecordCount() > 1) {
										$mode='error';
										$smarty->assign("error_text", "Student has multiple signup slips in the DB. Bork! Bork! Bork!");
									} else if (!empty($qres->fields['username'])) {
										$mode='error';
										$smarty->assign("error_text", "Student hasn't tried to use the SU signup component (No transaction in DB), but has a previously used Signup Slip with username ".$qres->fields['username'].".<br />Is this a renewal? If not, ask an admin to generate a new signup slip for this student");
										//TODO: Add option to generate new signup slip?
									} else {
										$id = $qres->fields['id'];
										$pass = $qres->fields['password'];
									}
								} else {
									$pass = make_password();
									$query = "INSERT INTO signup (password,sid,issuedby) VALUES ( ?, ?, ?) RETURNING id";
									$attribs[]=addslashes($pass);
									$attribs[]=$sid[0];
									$attribs[]='99999'; //SUCS Magic internal use UID

									$id = $sucsDB->Execute($query,$attribs);
									$id = $id->fields['id'];
									if (!$id) {
										$mode="error";
										$smarty->assign("error_text", "An error occurred generating a signup ID. Report the following message to the admins:<br /><pre>".$sucsDB->ErrorMsg()."</pre>");
									} else {
										$smarty->assign('slipid', $id);
										$smarty->assign('slippass', $pass);
										$smarty->assign('sid', $sid[0]);
									}
								}
							} else {
								//Retrieve existing slip
								$id = $qres->fields['signupid'];
								$tid = $qres->fields['transactionid'];
								if (empty($id)) {
									$pass = make_password();
									$query = "INSERT INTO signup (password,sid,issuedby) VALUES ( ?, ?, ?) RETURNING id";
									$attribs[]=addslashes($pass);
									$attribs[]=$sid[0];
									$attribs[]='99999'; //SUCS Magic internal use UID

									$qres = $sucsDB->Execute($query,$attribs);
									if (!$qres) {
										$mode="error";
										$smarty->assign("error_text", "An error occurred generating a signup ID. Report the following message to the admins:<br /><pre>".$sucsDB->ErrorMsg()."</pre>");
									} else {
										$id = $qres->fields['id'];
										$query = "UPDATE transactions SET signupid=? WHERE transactionid=?;";
										$qres = $sucsDB->Execute($query, array($id, $tid));
										$smarty->assign('slipid', $id);
										$smarty->assign('slippass', $pass);
										$smarty->assign('sid', $sid[0]);
									}

								}else {
									$query = "SELECT username, password FROM signup WHERE id=?;";
									$qres = $sucsDB->Execute($query, array($id));
									if (!$qres) {
										$mode="error";
										$smarty->assign("error_text", "The user appears to have generated a signup ID using the SU Signup system (Slip ID: ".$id."), but the password for that slip can't be retrieved.<br />Request assistance.");
									} else if ($qres->fields['username'] !== NULL) {
										$mode="error";
										$smarty->assign("error_text", "This user appears to have completed signup, with username <strong>".$qres->fields['username']."</strong><br />Check that this user exists, and offer to reset their password if necessary.");
									}
									$pass = $qres->fields['password'];
									$smarty->assign('slipid', $id);
									$smarty->assign('slippass', $pass);
									$smarty->assign('sid', $sid[0]);
								}
							}

							if(!$mode=='error') {
								//Right, this should be the point where we hand off to signup
								$smarty->assign('slipid', $id);
								$smarty->assign('slippass', $pass);
								$smarty->assign('sid', $sid[0]);
							}

						}else{
							$mode='error';
							$smarty->assign("error_text", "Student does not appear to have paid. Extract fees");
						}
					}
				}
			}
		}
	} else if ($mode=="renew") {
		if (empty($_REQUEST['member'])){
			$mode='error';
			$smarty->assign('error_text',"Can't renew a member without knowing their username!");
		} else if (!isset($_REQUEST['renewconf'])) {
			//Should be trying to renew $_REQUEST['member']
			$username=urldecode($_REQUEST['member']);
			$q = "SELECT username, typename, sid, paid, email FROM members, member_type WHERE username=?";
			$res = $sucsDB->Execute($q,array($username));
			if (!$res) {
				$mode='error';
				$smarty->assign('error_text', "A database error occurred while trying to retrieve member details");
			} else if ($res->fields['paid'] == paidUntil(time())) {
					$mode='error';
					$smarty->assign('error_text', 'User appears to have been renewed already?');
			} else {
				$smarty->assign('renew_user', $username);
				$smarty->assign('renew_paid', $res->fields['paid']);
				$smarty->assign('renew_type', $res->fields['typename']);
				$url = "$apibaseurl/IsPersonMember?strCriteria=".$res->fields['sid']."&GroupingId=6613";
                                $apiReq = curl_init();
                                curl_setopt($apiReq, CURLOPT_URL, $url);
                                curl_setopt($apiReq, CURLOPT_RETURNTRANSFER, TRUE);

                                $apiResult = curl_exec($apiReq);
				libxml_use_internal_errors(true);
				$xml=simplexml_load_string($apiResult);
				if (!$xml || isset($xml->head->title)) {
					$mode='error';
					$smarty->assign("error_text", "An error occurred communicating with the SUSU API.");
				} else {
					$ismember = $xml[0];

					$user = posix_getpwnam($session->username);

					if ($ismember!="true") {
						$mode='error';
						$smarty->assign('error_text', 'Member does not appear to have paid via the SU system. Use the old renewals system if they have paid using some other method');;
					} else {
						if (renew_member($username, $user['uid'], $user['name'])) {
							message_flash("Successfully renewed");
							$mode='menu';
						} else {
							$mode='error';
							$smarty->assign('error_text', 'An error occurred renewing account '.$username);
						}

					}

				}
			}
		}
			
	} else if ($mode == 'renewals') {
		//Get list of members according to the SU
		$url = "$apibaseurl/GetMemberListData?GroupingId=6613";
		$apiReq = curl_init();
		curl_setopt($apiReq, CURLOPT_URL, $url);
		curl_setopt($apiReq, CURLOPT_RETURNTRANSFER, TRUE);

		$apiResult = curl_exec($apiReq);
		$sumembers = su_response_decode($apiResult);
		if (!$sumembers) {
			$mode ='error';
			$smarty->assign("error_text", "An error occurred communicating with the SUSU API.");
		} else {
			$matches = array();
			$others=0;
			$paidup=0;
			foreach ($sumembers as $sumem) {
				$sucsmem = get_sucs_record($sumem['uni_card_number']);
				if ($sucsmem && $sucsmem['paid'] != paidUntil(time()) && $sucsmem['type']==1) {
					$matches[]=array($sumem['firstName']." ".$sumem['lastName'], $sucsmem['realname'], $sumem['uni_card_number'], $sucsmem['username'], $sucsmem['paid']);
				} else if ($sucsmem && $sucsmem['paid'] == paidUntil(time())) {
					$others++;
					$paidup++;
				} else {
					$others++;
				}
			}
			$smarty->assign("matches", $matches);
			$smarty->assign("others", $others);
			$smarty->assign("paidup", $paidup);
			$smarty->assign("pending", $others - $paidup);
		}
	} else if ($mode == 'renewals2') {
		$failures = array();
		$successes = array();

		if (empty($_REQUEST['renew'])) {
			$mode='error';
			$smarty->assign("error_text", "Can't renew an empty list!");
		} else {
			foreach($_REQUEST['renew'] as $user) {
				$admin_user=posix_getpwnam($session->username);
				if (renew_member($user, $admin_user['uid'], $admin_user['name'])) {
					$successes[]=$user;
				} else {
					$failures[]=$user;
				}
			}
			$smarty->assign("attempt", count($_REQUEST['renew']));
			$smarty->assign("failures", count($failures));
			$smarty->assign("failusers", $failures);
			$smarty->assign("successes", count($successes));
		}
	} else if ($mode == 'list') {
		//Get list of members according to the SU
		$url="$apibaseurl/GetMemberListData?GroupingId=6613";
		$apiReq = curl_init();
		curl_setopt($apiReq, CURLOPT_URL, $url);
		curl_setopt($apiReq, CURLOPT_RETURNTRANSFER, TRUE);

		$apiResult = curl_exec($apiReq);
		$sumembers = su_response_decode($apiResult);
		if (!$sumembers) {
			$mode='error';
			$smarty->assign("error_text", "An error occurred communicating with the SUSU API.");
		} else {
			$matches = array();
			foreach ($sumembers as $sumem) {
				$sucsmem = get_sucs_record($sumem['uni_card_number']);
				if ($sucsmem) {
					$matches[]=array($sumem['firstName']." ".$sumem['lastName'], $sucsmem['realname'], $sumem['uni_card_number'], $sucsmem['username'], $sucsmem['paid']);
				} else {
					$matches[]=array($sumem['firstName']." ".$sumem['lastName'], "N/A", $sumem['uni_card_number'], "N/A", "Not signed up");
				}
			}
			function sortbypaid($a, $b) {
				//Lets us array sort by final column ('Paid')
				return ($a[4] < $b[4]) ? -1  : 1;
			}
			usort($matches, 'sortbypaid');
			$smarty->assign("matches", $matches);
		}
	}
$smarty->assign('renewables', get_renewable_members());
}


$smarty->assign('title', 'SU Signup Admin');
$smarty->assign('mode', $mode);
$body = $smarty->fetch("susignup-admin.tpl");
$smarty->assign('body', $body);
$smarty->assign("extra_styles", array("$baseurl/css/susignup-admin.css"));

function su_response_decode($text) {
	global $smarty;
	libxml_use_internal_errors(true);
	$xml=simplexml_load_string($text);
	if (!$xml || isset($xml->head->title)) {
		return false;
	} else {
		return json_decode($xml[0],TRUE);
	}
} 

function get_sucs_record($sid) {
	global $sucsDB;

	$query = "SELECT * FROM members WHERE sid=?;";
	$res  = $sucsDB->Execute($query, array($sid));
	if (!$res || $res->RecordCount()<>1) {
		return FALSE;
	}
        return $res->FetchRow();	
}

function get_renewable_members() {
	global $sucsDB;
	$q = "SELECT username, username||' ('||realname||')' AS display FROM members, member_type WHERE paid != ? AND type=1 AND type=member_type.id ORDER BY paid;";
	$r = $sucsDB->Execute($q, array(paidUntil(time())));
	if(!$r) {
		return FALSE;
	}
	$retvals = array();
	while ($rec=$r->FetchRow()) {
		$retvals[$rec['username']]=$rec['display'];
	}
	return $retvals;
}

function renew_member($renew_name, $admin_uid, $admin_name) {
	global $sucsDB;

	$q="UPDATE members SET paid=?, lastupdate=DEFAULT, lastedit=? WHERE username=?;";
	$r=$sucsDB->Execute($q, array(paidUntil(time()), $admin_uid, $renew_name));
	if (!$r) {
		print $sucsDB->ErrorMsg();
		return FALSE;
	} else {	
		$q="SELECT email, typename FROM members, member_type WHERE username=?  AND type=member_type.id;";
		$r=$sucsDB->Execute($q, array($renew_name));

		$message  = "Account Renewal notification\n\n";
		$message .= "Account   : ".$renew_name."\n";
		$message .= "User Type : ".$r->fields['typename']."\n";
		$message .= "Renewed by: ".$admin_name."\n\n";
		$message .= "**** Payment was made via the SU payments system ****\n";
		$message .= "Regards\n  The SU Renewals script";
		mail("treasurer@sucs.org","Account Renewal",$message);
		
		$message = "Your Swansea University Computer Society (SUCS) membership has been renewed\n\n";
		$message .= "Username: ".$renew_name."\n";
		$message .= "If you do not know or have forgotten your password, please email admin@sucs.org to arrange for it to be changed.\n\n";
		$message .= "Regards\n  The SUCS admin";
		$header = "From: admin@sucs.org\r\n";
		$header .= "Reply-To: admin@sucs.org";
		// Personal account
		mail($r->fields['email'],"SUCS account renewal",$message,$header);
		// sucs account
		mail($renew_name."@sucs.org","SUCS account renewal",$message,$header);
		return TRUE;
	}

}