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; }