-
Notifications
You must be signed in to change notification settings - Fork 0
/
jpsearch.php
103 lines (100 loc) · 3.27 KB
/
jpsearch.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
<html>
<title>Search in J-Pilot</title>
<?php require 'jptop.php'; ?>
<body>
<h1>Seach in J-Pilot</h1>
<?php
/* jptables: search in J-Pilot data
Elmar Klausmeier, 02-May-2023
*/
$srch = $_POST["searchstr"] ?? NULL;
$casesensitive = $_POST["Casesensitive"] ?? 0;
$day = $_POST['Day'] ?? NULL;
$dayValid = (isset($day) && strlen($day) === 10) ? true : false;
?>
<form action="jpsearch.php" method="post">
<label for=searchstr>Search for strings in J-Pilot:</label>
<input type=text id=searchstr name=searchstr value="<?=$srch??''?>" autofocus>
<input type=checkbox id=Casesensitive name=Casesensitive value=1 <?=$casesensitive? 'checked':''?>>
Case sensitive
<br><br>Search only for dates: <input type=date id=Day name=Day value="<?=$day??''?>">
<br><br><input type=button value=Clear onclick="clearFrm()"> <input type=submit value=submit>
</form>
<?php
//echo '<pre>home=' . $home . ', dbpath=' . $dbpath . "</pre>\n";
if (isset($srch) && strlen($srch) > 0 && strlen($srch) < 240 || $dayValid) {
$db = new SQLite3($dbpath);
if ($dayValid) {
$stmt = $db->prepare(
"select Id, 'Datebook', substr(Begin || ' ' || coalesce(Description,''),1,80) as Line "
."from Datebook "
."where Begin like :srch "
."order by Begin"
);
$stmt->bindValue(":srch",$day.'%',SQLITE3_TEXT); // add % right
} else {
if ($casesensitive) $db->exec("PRAGMA case_sensitive_like=true");
$stmt = $db->prepare(
"select Id, 'Addr', substr(coalesce(Lastname,Firstname,''),1,80) as Line "
."from Addr "
."where Lastname like :srch "
."or Firstname like :srch "
."or Phone1 like :srch "
."or Phone2 like :srch "
."or Phone3 like :srch "
."or Phone4 like :srch "
."or Phone5 like :srch "
."or Address like :srch "
."or City like :srch "
."or State like :srch "
."or Zip like :srch "
."or Custom1 like :srch "
."or Custom2 like :srch "
."or Custom3 like :srch "
."or Custom4 like :srch "
."or Note like :srch "
."union "
."select Id, 'Datebook', substr(Begin || ' ' || coalesce(Description,''),1,80) as Line "
."from Datebook "
."where Description like :srch "
."or Note like :srch "
."union "
."select Id, 'Memo', substr(Text,1,80) as Line "
."from Memo "
."where Text like :srch "
."union "
."select Id, 'ToDo', substr(Description,1,80) as Line "
."from ToDo "
."where Description like :srch "
."or Note like :srch "
."union "
."select Id, 'Expense', substr(coalesce(Date,'') || ' ' "
." || coalesce(Amount,'') || coalesce(Vendor,'') "
." || coalesce(Note,''),1,80) as Line "
."from Expense "
."where Amount like :srch "
."or Vendor like :srch "
."or City like :srch "
."or Attendees like :srch "
."or Note like :srch "
);
$stmt->bindValue(":srch",'%'.$srch.'%',SQLITE3_TEXT); // add % left+right
}
$result = $stmt->execute();
echo "<table>\n";
while (($r = $result->fetchArray()) !== false) {
printf("\t<tr><td><a href=jpedit.php?%s?%d>%s</a></td><td>%s</td></tr>\n",$r[1],$r['Id'],$r[1],strip_tags($r['Line']));
}
echo "</table>\n";
}
?>
<script>
function clearFrm() {
//alert("clearFrm()");
document.getElementById('searchstr').value = "";
document.getElementById('Day').value = "";
document.getElementById('Casesensitive').checked = false;
}
</script>
</body>
</html>