source: trunk/Mars/datacenter/db/querymc.php@ 15197

Last change on this file since 15197 was 9498, checked in by Daniela Dorner, 15 years ago
*** empty log message ***
  • Property svn:executable set to *
File size: 9.9 KB
Line 
1<?php
2{
3 function CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup)
4 {
5 $fromtable="MCRunData";
6
7 $groups = 0;
8 foreach ($checkgroup as $element)
9 if ($element==-1)
10 $groups++;
11
12 $query0 = "SELECT SQL_CALC_FOUND_ROWS ";
13
14 if ($groups>0)
15 {
16 foreach ($checkgroup as $key => $element)
17 if ($element==-1)
18 $query0 .= $key . " as '" . $alias[$key] . "' " . ", ";
19 //--------------------------------------------------
20 //$query0 .= " TIMEDIFF(fRunStop, fRunStart), ";
21 // Only available in MySQL>4.1.1
22// $query0 .= "SUM(if(TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)<0, " .
23// "TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)+24*60*60, " .
24// "TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)))/3600 as '" . $alias["SUM(fRunTime)/3600"] . "', ";
25 //--------------------------------------------------
26 $query0 .= " SUM(fNumEvents) as '" . $alias["SUM(fNumEvents)"] . "', ";
27// $query0 .= " Min(fZenithDistance) as '" . $alias["Min(fZenithDistance)"] . "', ";
28// $query0 .= " Max(fZenithDistance) as '" . $alias["Max(fZenithDistance)"] . "', ";
29 $query0 .= " COUNT(*) as '# Runs' ";
30 }
31 else
32 {
33 $query0 .= " fRunNumber as 'Run#' ";
34
35 foreach ($_GET as $key => $element)
36 if ($_GET[$key]=="On")
37 if (empty($checkwhere[$key]) || $checkwhere[$key]==0)
38 $query0 .= ", " . $key . " as '" . $alias[$key] . "' ";
39 }
40
41 $query0 .= " FROM MCRunData ";
42
43 foreach ($_GET as $key => $element)
44 if (($_GET[$key]=="On" || $groups>0))// && !empty($joins[$key]))
45 $query0 .= GetJoin($fromtable, $key);
46
47 foreach ($checkwhere as $key => $element)
48 {
49 if (empty($element) || $element<=0)
50 continue;
51
52 if (strpos($query0, " WHERE ")==FALSE)
53 $query0 .= " WHERE ";
54 else
55 if ($element!=-1)
56 if (strrpos($query0, " AND ")!=strlen($query0)-5)// this if clause doesn't work
57 $query0 .= " AND ";
58
59 if ($element!=-1)
60 $query0 .= GetCheck($fromtable, $key) . "=" . $element;
61 }
62
63 if (strpos($query0, " WHERE ")==FALSE)
64 $query0 .= " WHERE ";
65 else
66 $query0 .= " AND ";
67
68 if (!empty($_GET["fRunMin"]) && !empty($_GET["fRunMax"]))
69 $query0 .= "fRunNumber BETWEEN " . $_GET["fRunMin"] . " AND " . $_GET["fRunMax"] . " ";
70
71/*
72 if (!empty($_GET["fSequenceNo"]))
73 {
74 if (strpos(strrev($query0), " DNA ")!=0)
75 $query0 .= " AND ";
76
77 $query0 .= " fSequenceFirst = '" . $_GET["fSequenceNo"] . "' ";
78 }
79*/
80
81 if ($groups>0)
82 {
83 $query0 .= " GROUP BY ";
84 $num = $groups;
85 foreach ($checkgroup as $key => $element)
86 if ($element==-1)
87 {
88 $query0 .= GetCheck($fromtable, $key);
89 if ($num-->1)
90 $query0 .= ", ";
91 }
92 }
93
94 if (!empty($_GET["fSortBy"]))
95 {
96 $query0 .= " ORDER BY " . substr($_GET["fSortBy"], 0, -1) . " ";
97 if (substr($_GET["fSortBy"], -1)=="-")
98 $query0 .= "DESC";
99 }
100
101 if (empty($_GET["fNumStart"]))
102 $_GET["fNumStart"]=0;
103
104 if (empty($_GET["fSendTxt"]))
105 $query0 .= " LIMIT " . $_GET["fNumStart"] . ", " . $_GET["fNumResults"];
106
107 return $query0;
108 }
109
110 function InitGet($_GET)
111 {
112 /*
113 if (empty($_GET["fRawFileKEY"]))
114 $_GET["fRawFileKEY"]="Off";
115 */
116
117 // Find out whether it is the first call to the php script
118 $first = empty($_GET["fRunMin"]) && empty($_GET["fRunMax"]);
119
120 if (empty($_GET["fNumResults"]))
121 $_GET["fNumResults"]="20";
122
123 if (empty($_GET["fNumEvents"]))
124 $_GET["fNumEvents"]=$first?"On":"";
125
126 if (empty($_GET["fEMin"]))
127 $_GET["fEMin"]=$first?"On":"";
128
129 if (empty($_GET["fEMax"]))
130 $_GET["fEMax"]=$first?"On":"";
131
132 if (empty($_GET["fParticleTypeName"]))
133 $_GET["fParticleTypeName"]="Off";
134
135 }
136
137 function PrintForm($_GET, $host, $user, $pw, $db)
138 {
139 printf("<center>\n");
140 printf("<form action=\"querymc.php\" METHOD=\"GET\">\n");
141 printf(" <table>\n");
142 printf(" <tr>\n");
143
144 CheckBox("fNumEvents", "Num of events");
145 CheckBox("fEMin", "EMin");
146 CheckBox("fEMax", "EMax");
147
148 printf(" </tr>\n");
149 printf(" </table>\n");
150 printf(" <p>\n");
151
152 // pull down boxes
153
154 printf(" <table>\n");
155 printf(" <tr><td>\n");
156 PrintPullDown($host, $user, $pw, $db, "ParticleType", "fParticleTypeName", "fParticleTypeKEY", "particle type");
157 printf(" </td></tr></table>\n");
158 printf(" <p>\n");
159
160/*
161 if (empty($_GET["fStartDate"]))
162 $timemin = GetMin("fRunStart", "RunData", $host, $user, $pw, $db);
163 else
164 $timemin = $_GET["fStartDate"];
165
166 if (empty($_GET["fStopDate"]))
167 $timemax = GetMax("fRunStart", "RunData", $host, $user, $pw, $db);
168 else
169 $timemax = $_GET["fStopDate"];
170
171 printf("Night&nbsp;(yyyy-mm-dd)&nbsp;from&nbsp;<input name=\"fStartDate\" type=\"text\" size=\"10\" maxlength=\"10\" value=\"%s\">\n", $timemin);
172 printf("to&nbsp;<input name=\"fStopDate\" type=\"text\" size=\"10\" maxlength=\"10\" value=\"%s\">&nbsp;&nbsp;&nbsp;&nbsp;\n", $timemax);
173*/
174
175 if (empty($_GET["fRunMin"]))
176 $min = GetMin("fRunNumber", "MCRunData", $host, $user, $pw, $db);
177 else
178 $min = $_GET["fRunMin"];
179
180 if (empty($_GET["fRunMax"]))
181 $max = GetMax("fRunNumber", "MCRunData", $host, $user, $pw, $db);
182 else
183 $max = $_GET["fRunMax"];
184
185 printf("Runs&nbsp;from&nbsp;<input name=\"fRunMin\" type=\"text\" size=\"6\" maxlength=\"6\" value=\"%s\">\n", $min);
186 printf("to&nbsp;<input name=\"fRunMax\" type=\"text\" size=\"6\" maxlength=\"6\" value=\"%s\">&nbsp;&nbsp;&nbsp;\n", $max);
187
188 printf(" <P>\n");
189
190/*
191 printf("Sequ#&nbsp;<input name=\"fSequenceNo\" type=\"text\" size=\"6\" maxlength=\"6\" value=\"");
192 if (!empty($_GET["fSequenceNo"]))
193 printf("%s", $_GET["fSequenceNo"]);
194 printf("\">&nbsp;&nbsp;&nbsp;\n");
195*/
196
197 printf(" Results:\n");
198 printf(" <select name=\"fNumResults\">\n");
199
200 $numres = array("10", "20", "50", "100", "200", "500", "1000", "2000");
201 foreach ($numres as $element)
202 {
203 if ($element==$_GET["fNumResults"])
204 printf("<option value=\"%s\" selected>%3s</option>\n", $element, $element);
205 else
206 printf("<option value=\"%s\">%3s</option>\n", $element, $element);
207 }
208 printf(" </select>\n");
209 printf(" &nbsp;&nbsp;&nbsp;\n");
210
211 ini_set("mysql.trace_mode", "Off");
212 ini_set("display_errors", "Off");
213
214 printf("<input class='Width' type='submit' value='Query Table'>&nbsp;&nbsp;&nbsp;\n");
215 printf("<input class='Width' type='button' value='Reset' onClick='self.location.href=\"querymc.php\"'>&nbsp;&nbsp;&nbsp;\n");
216 if (strchr($_SERVER["REQUEST_URI"], '?')!=FALSE)
217 printf("<input class='Width' type='button' value='Get .txt' onClick='self.location.href=\"%s&fSendTxt=1\"'>&nbsp;&nbsp;&nbsp;\n", $_SERVER["REQUEST_URI"]);
218 printf("</form>\n");
219 printf("</center>\n");
220 printf("</td>\n");
221 printf("</tr>\n");
222 printf("<tr class='Block'>\n");
223 printf("<td>\n");
224 }
225
226 function PrintPage($html, $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup)
227 {
228 $db_id = mysql_connect($host, $user, $pw);
229 if ($db_id==FALSE)
230 {
231 printf("mysql_connect returned the following error: %s\n", mysql_error());
232 die("");
233 }
234 mysql_select_db($db);
235 mysql_query("SET BIG_TABLES=1"); // necessary for mySQL <= 4
236
237
238 $query0 = CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup);
239
240 $result0 = mysql_query($query0, $db_id);
241 $result1 = mysql_query("SELECT FOUND_ROWS()", $db_id);
242
243 if ($result0)
244 {
245 if ($html=="1")
246 PrintMagicTable($result0, $alias, $rightalign, "", "", "", $result1);
247 else
248 PrintText($result0);
249
250 mysql_free_result($result0);
251 mysql_free_result($result1);
252 }
253 mysql_close($db_id);
254
255 if ($html=="1")
256 printf("<U><B>submitted query:</B></U><BR>%s<BR>", htmlspecialchars($query0));
257 }
258
259 include ("include.php");
260 include ("db.php");
261 include ("magicdefs.php");
262
263 ini_set("display_errors", "On");
264 ini_set("mysql.trace_mode", "On");
265
266 if (!empty($_GET["fSendTxt"]))
267 {
268 header("Content-type: application/octet");
269 header("Content-Disposition: attachment; filename=query-result.txt");
270
271 PrintPage("0", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup);
272 }
273 else
274 {
275 echo (file_get_contents("index-header.html"));
276
277 $environment = sizeof($_GET);
278
279 InitGet($_GET);
280 PrintForm($_GET, $host, $user, $pw, $db);
281
282 if ($environment==0)
283 printf("No query submitted yet.<BR>");
284 else
285 PrintPage("1", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup);
286
287 echo (file_get_contents("index-footer.html"));
288 }
289
290 ini_set("display_errors", "Off");
291 ini_set("mysql.trace_mode", "Off");
292}
293?>
Note: See TracBrowser for help on using the repository browser.