-
Notifications
You must be signed in to change notification settings - Fork 7
/
getHoaPropertiesList.php
179 lines (157 loc) · 7.92 KB
/
getHoaPropertiesList.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
<?php
/*==============================================================================
* (C) Copyright 2015,2020 John J Kauflin, All rights reserved.
*----------------------------------------------------------------------------
* DESCRIPTION:
*----------------------------------------------------------------------------
* Modification History
* 2015-03-09 JJK Initial version to get properties list
* 2015-10-20 JJK Improved the search by adding wildCardStrFromTokens
* function to build wildcard parameter string from tokens
* 2017-02-26 JJK Added a general search to look through all columns
* (someday switch to MySQL full text search for these fields)
* 2020-07-23 JJK Modified to require files from a secure location, use a
* function to get the Credentials file, and pass parameters
* to the getConn function
* 2020-08-01 JJK Re-factored to use jjklogin for authentication
* 2020-12-21 JJK Re-factored to use jjklogin package
* 2023-02-17 JJK Refactor for non-static jjklogin class and settings from DB
*============================================================================*/
// Define a super global constant for the log file (this will be in scope for all functions)
define("LOG_FILE", "./php.log");
require_once 'vendor/autoload.php';
// Figure out how many levels up to get to the "public_html" root folder
$webRootDirOffset = substr_count(strstr(dirname(__FILE__),"public_html"),DIRECTORY_SEPARATOR) + 1;
// Get settings and credentials from a file in a directory outside of public_html
// (assume a settings file in the "external_includes" folder one level up from "public_html")
$extIncludePath = dirname(__FILE__, $webRootDirOffset+1).DIRECTORY_SEPARATOR.'external_includes'.DIRECTORY_SEPARATOR;
require_once $extIncludePath.'hoadbSecrets.php';
require_once $extIncludePath.'jjkloginSettings.php';
// Common functions
require_once 'php_secure/commonUtil.php';
// Common database functions and table record classes
require_once 'php_secure/hoaDbCommon.php';
use \jkauflin\jjklogin\LoginAuth;
try {
$loginAuth = new LoginAuth($hostJJKLogin, $dbadminJJKLogin, $passwordJJKLogin, $dbnameJJKLogin);
$userRec = $loginAuth->getUserRec();
if ($userRec->userName == null || $userRec->userName == '') {
throw new Exception('User is NOT logged in', 500);
}
if ($userRec->userLevel < 1) {
throw new Exception('User is NOT authorized (contact Administrator)', 500);
}
// If they are set, get input parameters from the REQUEST
$searchStr = getParamVal("searchStr");
$parcelId = getParamVal("parcelId");
$lotNo = getParamVal("lotNo");
$address = getParamVal("address");
$ownerName = getParamVal("ownerName");
$phoneNo = getParamVal("phoneNo");
$altAddress = getParamVal("altAddress");
$checkNo = getParamVal("checkNo");
$sql = " ";
$paramStr = " ";
if (!empty($searchStr)) {
$paramStr = wildCardStrFromTokens($searchStr);
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE (p.Parcel_ID = o.Parcel_ID AND o.CurrentOwner = 1) AND (";
$sql = $sql . "UPPER(p.Parcel_ID) LIKE UPPER('" . $paramStr . "')";
$sql = $sql . " OR p.LotNo LIKE UPPER('" . $paramStr . "')";
$sql = $sql . " OR UPPER(p.Parcel_Location) LIKE UPPER('" . $paramStr . "')";
$sql = $sql . " OR UPPER(CONCAT(o.Owner_Name1,' ',o.Owner_Name2,' ',o.Mailing_Name)) LIKE UPPER('" . $paramStr . "')";
/*
$sql = $sql . " OR UPPER(o.Owner_Phone) LIKE UPPER('" . $paramStr . "')";
$sql = $sql . " OR UPPER(o.Alt_Address_Line1) LIKE UPPER('" . $paramStr . "')";
} elseif (!empty($checkNo)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o, hoa_assessments a WHERE p.Parcel_ID = o.Parcel_ID AND p.Parcel_ID = a.Parcel_ID AND o.CurrentOwner = 1 AND UPPER(a.Comments) ";
$paramStr = wildCardStrFromTokens($checkNo);
*/
//$sql = $sql . "AND UPPER(a.Comments)";
$sql = $sql . ") ORDER BY p.Parcel_ID; ";
//error_log(date('[Y-m-d H:i] '). '$sql = ' . $sql . PHP_EOL, 3, 'php.log');
$conn = getConn($host, $dbadmin, $password, $dbname);
$stmt = $conn->prepare($sql);
//$stmt->bind_param("s", $paramStr);
$stmt->execute();
$result = $stmt->get_result();
$outputArray = array();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$hoaPropertyRec = new HoaPropertyRec();
$hoaPropertyRec->parcelId = $row["Parcel_ID"];
$hoaPropertyRec->lotNo = $row["LotNo"];
$hoaPropertyRec->subDivParcel = $row["SubDivParcel"];
$hoaPropertyRec->parcelLocation = $row["Parcel_Location"];
$hoaPropertyRec->ownerName = $row["Owner_Name1"] . ' ' . $row["Owner_Name2"];
$hoaPropertyRec->ownerPhone = $row["Owner_Phone"];
array_push($outputArray,$hoaPropertyRec);
}
}
$stmt->close();
$conn->close();
} else {
// Default SQL
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND o.CurrentOwner = 1 AND UPPER(p.Parcel_ID) ";
$paramStr = " ";
if (!empty($parcelId)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND o.CurrentOwner = 1 AND UPPER(p.Parcel_ID) ";
$paramStr = wildCardStrFromTokens($parcelId);
} elseif (!empty($lotNo)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND o.CurrentOwner = 1 AND p.LotNo ";
$paramStr = wildCardStrFromTokens($lotNo);
} elseif (!empty($address)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND o.CurrentOwner = 1 AND UPPER(p.Parcel_Location) ";
$paramStr = wildCardStrFromTokens($address);
} elseif (!empty($ownerName)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND UPPER(CONCAT(o.Owner_Name1,' ',o.Owner_Name2,' ',o.Mailing_Name)) ";
// Check if a tokenized string was entered, break it into token and put wildcards between each token?
// search need to be bullitproof if you are using it for members
$paramStr = wildCardStrFromTokens($ownerName);
} elseif (!empty($phoneNo)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND UPPER(o.Owner_Phone) ";
$paramStr = wildCardStrFromTokens($phoneNo);
} elseif (!empty($altAddress)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND UPPER(o.Alt_Address_Line1) ";
$paramStr = wildCardStrFromTokens($altAddress);
} elseif (!empty($checkNo)) {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o, hoa_assessments a WHERE p.Parcel_ID = o.Parcel_ID AND p.Parcel_ID = a.Parcel_ID AND o.CurrentOwner = 1 AND UPPER(a.Comments) ";
$paramStr = wildCardStrFromTokens($checkNo);
} else {
$sql = "SELECT * FROM hoa_properties p, hoa_owners o WHERE p.Parcel_ID = o.Parcel_ID AND o.CurrentOwner = 1 AND UPPER(p.Parcel_ID) ";
// Hardcode the default to find all parcels
$paramStr = '%r%';
}
$sql = $sql . "LIKE UPPER(?) ORDER BY p.Parcel_ID; ";
//error_log('$sql = ' . $sql);
$conn = getConn($host, $dbadmin, $password, $dbname);
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $paramStr);
$stmt->execute();
$result = $stmt->get_result();
$outputArray = array();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$hoaPropertyRec = new HoaPropertyRec();
$hoaPropertyRec->parcelId = $row["Parcel_ID"];
$hoaPropertyRec->lotNo = $row["LotNo"];
$hoaPropertyRec->subDivParcel = $row["SubDivParcel"];
$hoaPropertyRec->parcelLocation = $row["Parcel_Location"];
$hoaPropertyRec->ownerName = $row["Owner_Name1"] . ' ' . $row["Owner_Name2"];
$hoaPropertyRec->ownerPhone = $row["Owner_Phone"];
array_push($outputArray,$hoaPropertyRec);
}
}
$stmt->close();
$conn->close();
}
echo json_encode($outputArray);
} catch(Exception $e) {
//error_log(date('[Y-m-d H:i] '). "in " . basename(__FILE__,".php") . ", Exception = " . $e->getMessage() . PHP_EOL, 3, LOG_FILE);
echo json_encode(
array(
'error' => $e->getMessage(),
'error_code' => $e->getCode()
)
);
}
?>