function dbSearch()
{
//$this->debug = true;
if ($this->debug) {
echo "Entering dbSearch<br>";
print_r($_POST);
}
echo "<div class='bgWhite'>";
$firstName = "";
$lastName = "";
$streetNum = "";
$streetName = "";
$email = "";
$orgName = "";
$hoodName = "";
$phoneNum = "";
foreach ($_POST as $param_name => $param_val) {
switch ($param_name) {
case "firstName":
$firstName = $param_val;
break;
case "lastName":
$lastName = $param_val;
break;
case "streetName":
$streetName = $param_val;
break;
case "streetNum":
$streetNum = $param_val;
break;
case "streetName":
$streetName = $param_val;
break;
case "email":
$email = $param_val;
break;
case "orgName":
$orgName = $param_val;
break;
case "hoodName":
$hoodName = $param_val;
break;
case "phoneNum":
$phoneNum = $param_val;
break;
}
//echo "Param: $param_name; Value: $param_val<br />\n";
}
// set a counter for how many fields we are searching
$fieldCount = 0;
// search on the resident table
if (!empty($firstName) || !empty($lastName) || !empty($streetNum) || !empty($streetName) || !empty($email) || !empty($phoneNum) || !empty($orgName) || !empty($hoodName)) {
// Search for all of the residents
$query = "SELECT a.resident_first_name, a.resident_middle_initial, a.resident_last_name, " . " a.residence_number, c.street_name, a.resident_email_address_text, d.phone_num, " . " e.organization_name, e.organization_code, d.phone_type_code from resident a " . " inner join residence b on a.street_id = b.street_id and a.residence_number = b.residence_number" . " inner join street c on a.street_id = c.street_id " . " inner join organization e on b.organization_code = e.organization_code " . "left join phone d on a.resident_id = d.resident_id where (";
if (!empty($firstName)) {
$fieldCount++;
$query = $query . " a.resident_first_name LIKE '{$firstName}%' ";
}
if (!empty($lastName)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
$query = $query . " a.resident_last_name LIKE '{$lastName}%' ";
}
if (!empty($streetNum)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
$query = $query . " a.residence_number = '{$streetNum}' ";
}
if (!empty($streetName)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
//$query = $query . " c.street_name = '$streetName' " ;
// using street id instead of street name
$query = $query . " a.street_id = '{$streetName}' ";
}
if (!empty($email)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
$query = $query . " a.resident_email_address_text = '{$email}' ";
}
if (!empty($phoneNum)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
$query = $query . " d.phone_num = '{$phoneNum}' ";
}
if (!empty($orgName)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
$query = $query . " b.organization_code = '{$orgName}' ";
}
// only return rows in the given neighborhood
if (!empty($hoodName)) {
$fieldCount++;
if ($fieldCount > 1) {
$query = $query . " AND ";
}
$query .= " b.organization_code in (SELECT a.organization_code FROM organization a " . "inner join organization b where b.organization_name = '{$hoodName}' " . "and a.parent_organization_code is not null and " . "a.parent_organization_code = b.organization_code) ";
}
$query .= " ) order by a.resident_last_name, a.resident_first_name, a.resident_middle_initial, d.contact_seqnum";
if ($this->debug) {
echo "EXECUTING QUERY: {$query}<br>";
}
$result = $this->conn->query($query);
if ($this->conn->errno) {
echo "Query to select resident information failed: Error = " . $this->conn->error . "<br>";
return ERROR_RC;
}
$count = 0;
$prevRow = NULL;
$phoneNums = "";
$orgName = "";
while ($row = $result->fetch_row()) {
$count++;
// print out the table header
if ($count === 1) {
echo "<span class='title'>Search Results</span>";
echo self::BACK_TO_SEARCH . "<br />";
echo "<table class='tablesorter' border='0' cellpadding='1'><thead><tr><th>First Name</th><th>Last Name</th><th>Street #</th><th>Street Name</th><th>Email</th><th>Phone</th><th>Group</th><th>Nbrhd</th><tr></thead><tbody>";
}
// check to see if we have the same person with just a new phone number
if ($prevRow[0] === $row[0] && $prevRow[1] === $row[1] && $prevRow[2] === $row[2]) {
if (!empty($row[6])) {
$phoneNums .= "<br />" . formatPhoneNbr($row[6], $row[9]);
}
} else {
// Display first name, middle initial, last name, address, email and phone number for the previous resident
if ($count > 1) {
//echo "<tr><td>$row[0] $row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5] </td><td>$row[6] </td><td>$row[7]</td><td>$orgName</td></tr>";
echo "<tr><td>{$prevRow['0']} {$prevRow['1']}</td><td>{$prevRow['2']}</td><td>{$prevRow['3']}</td><td>{$prevRow['4']}</td><td>{$prevRow['5']} </td><td>{$phoneNums} </td><td>{$prevRow['7']}</td><td>{$orgName}</td></tr>";
}
if (!empty($row[6])) {
$phoneNums = formatPhoneNbr($row[6], $row[9]);
} else {
$phoneNums = NULL;
}
if ($this->debug) {
echo "OrgCode = {$row['8']} <br />ROW = ";
print_r($row);
echo "<br />";
}
// Get the neighborhood code for this residence
$orgCode = $row[8];
$orgName = $this->getOrgName($orgCode);
}
// end if new resident
$prevRow = $row;
}
// end while resident rows returned
if ($count === 0) {
echo "<span class='title'>No results found</span>";
echo self::BACK_TO_SEARCH;
} else {
// write out the last row and close the table
echo "<tr><td>{$prevRow['0']} {$prevRow['1']}</td><td>{$prevRow['2']}</td><td>{$prevRow['3']}</td><td>{$prevRow['4']}</td><td>{$prevRow['5']} </td><td>{$phoneNums} </td><td>{$prevRow['7']}</td><td>{$orgName}</td></tr>";
echo "</tbody></table>";
}
} else {
echo "<span class='title'>No results found</span>";
echo self::BACK_TO_SEARCH;
}
if (!empty($result)) {
$result->free();
}
// close the main div
echo "</div>";
return GOOD_RC;
}
function getResidentInfo($userId, $resident)
{
//$this->debug = true;
if ($this->debug) {
echo "Entering getResidentInfo<br />";
print_r($_POST);
}
if (empty($userId)) {
echo "User Id is Null<br />";
return ERROR_RC;
}
$rc = GOOD_RC;
// Retrieve the information for this resident
$query = "SELECT a.resident_id, a.resident_first_name, a.resident_middle_initial, a.resident_last_name, " . " a.resident_email_address_text, a.resident_employer_name, a.resident_birth_year, " . " b.phone_num, b.phone_type_code, b.contact_seqnum, " . " a.street_id, a.residence_number, a.residence_id from resident a " . " left join phone b on a.resident_id = b.resident_id " . " where a.resident_id = '{$userId}' order by b.contact_seqnum ";
if ($this->debug) {
echo "EXECUTING QUERY: {$query}<br>";
}
$result = $this->conn->query($query);
if ($this->conn->errno) {
printf("Select resident info QUERY FAILED: %s\n", $this->conn->error);
$rc = ERROR_RC;
} else {
$count = 0;
while ($row = $result->fetch_row()) {
$count++;
$seqNbr = $row[9];
// print out the table header
if ($count === 1) {
$resident['resId'] = $row[0];
$resident['streetId'] = $row[10];
$resident['resNbr'] = $row[11];
$resident['residenceId'] = $row[12];
$resident['firstName'] = $row[1];
$resident['middleInit'] = $row[2];
$resident['lastName'] = $row[3];
$resident['email'] = $row[4];
//$resident['birthDate'] = $row[6];
$resident['birthDate'] = "";
$resident['employer'] = $row[5];
$resident['phone1'] = "";
$resident['phoneType1'] = "";
$resident['phone2'] = "";
$resident['phoneType2'] = "";
$resident['phone3'] = "";
$resident['phoneType3'] = "";
$resident['fax'] = "";
}
if ($row[8] == "FAX") {
$resident['fax'] = $row[7];
} else {
$phoneNbr = formatPhoneNbr($row[7], "");
switch ($seqNbr) {
case "1":
$resident['phone1'] = $phoneNbr;
$resident['phoneType1'] = $row[8];
break;
case "2":
$resident['phone2'] = $phoneNbr;
$resident['phoneType2'] = $row[8];
break;
case "3":
$resident['phone3'] = $phoneNbr;
$resident['phoneType3'] = $row[8];
break;
default:
break;
}
}
}
// end while resident rows returned
if ($count === 0) {
echo "<span class='title'>No Resident Information Found</span>";
$this->errmsg = "Resident information not found, please try again. <br />";
if ($this->debug) {
echo "{$this->errmsg} <br />";
}
$rc = ERROR_RC;
}
}
// end if
if (!empty($result)) {
$result->free();
}
return $rc;
}
