source: trunk/MagicSoft/Mars/datacenter/db/findoffdata.php@ 9382

Last change on this file since 9382 was 9106, checked in by Daniela Dorner, 17 years ago
*** empty log message ***
  • Property svn:executable set to *
File size: 18.2 KB
Line 
1<?php
2{
3 function CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup, $needs)
4 {
5 $fromtable="Sequences";
6
7 $groups = 0;
8 foreach ($checkgroup as $element)
9 if ($element==-1)
10 $groups++;
11
12 $statusgroups = 0;
13 foreach ($checkstatusgroup as $element)
14 if ($element==-1)
15 $statusgroups++;
16
17 $query0 = "SELECT ";
18
19 if ($groups>0)
20 {
21 foreach ($checkgroup as $key => $element)
22 if ($element==-1)
23 $query0 .= $key . " as '" . $alias[$key] . "' " . ", ";
24 //--------------------------------------------------
25 $query0 .= "SUM(fRunTime)/3600 as '" . $alias["SUM(fRunTime)/3600"] . "', ";
26 //--------------------------------------------------
27 $query0 .= " SUM(fNumEvents) as '" . $alias["SUM(fNumEvents)"] . "', ";
28 $query0 .= " Min(fZenithDistanceMin) as '" . $alias["Min(fZenithDistanceMin)"] . "', ";
29 $query0 .= " Max(fZenithDistanceMax) as '" . $alias["Max(fZenithDistanceMax)"] . "', ";
30 $query0 .= " COUNT(*) as 'Sequence' ";
31 }
32 else
33 {
34 if ($statusgroups>0)
35 {
36 foreach ($checkstatusgroup as $key => $element)
37 if ($element==-1)
38 $query0 .= " (if(IsNull(" . $key . "), if(isnull(fStartTime), 'not done', if(isnull(fFailedTime),if(isnull(" . $needs[$key] . "),'not done',if(date_sub(Now(),interval 12 hour) < fStartTime,'running','crashed')),if(isnull(" . $needs[$key] . "),'not done','failed'))) ,if(" . $key . "='1970-01-01 00:00:00','dont do','done'))) as '" . $alias[$key] . "', ";
39// $query0 .= " (if(IsNull(" . $key . "), 'not done' ,if(" . $key . "='1970-01-01 00:00:00','dont do','done'))) as '" . $alias[$key] . "', ";
40
41 $query0 .= " count(*) as 'Sequence'";
42
43 }
44 else
45 {
46 if (!empty($_GET["fSendTxt"]))
47 $query0 .= " " . $fromtable . ".fSequenceFirst as '" . $alias["fSequenceFirst"] . "' ";
48 else
49 {
50 $query0 .= " CONCAT('<A&ws;HREF=\"runinfo.php?', 'fRunStart=On', '&fZenithDistance=On', '&fRunMin=', Sequences.fSequenceFirst, '&fMeanTriggerRate=On', '&fRunTypeName=On', '&fRunMax=', fSequenceLast, '&fNumEvents=On', '&fSourceName=On&', 'fExcludedFDAKEY=1', '&fSequenceFirst=On', '&fRawFileAvail=On', '&fCCFileAvail=On', '&fCaCoFileAvail=On', '&fNumResults=500\">', Sequences.fSequenceFirst, '</A>') ";
51 $query0 .= " as " . $alias["fSequenceFirst"];
52 if (!empty($_GET["fLinks"]))
53 {
54 $query0 .= ", CONCAT('<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/callisto/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), 'callisto', LPAD(CONCAT(Sequences.fSequenceFirst , '.html'), 13,'0') , '\">cl</A>'";
55 $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/callisto/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), '\">c</A>'";
56 $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/star/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), 'star', LPAD(CONCAT(Sequences.fSequenceFirst , '.html'), 13,'0') , '\">sl</A>'";
57 $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/star/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), '\">s</A>'";
58 $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/sequences/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/sequence', LPAD(CONCAT(Sequences.fSequenceFirst , '.txt'), 12,'0') , '\">', 'f </A>') ";
59 $query0 .= " as 'Links'";
60 }
61 }
62
63 foreach ($_GET as $key => $element)
64 if ($_GET[$key]=="On")
65 if ($key!="fLinks" && $key!="fOff" && $key!="fOnlyOff")
66 if (empty($checkwhere[$key]) || $checkwhere[$key]==0)
67 $query0 .= ", " . $key . " as '" . $alias[$key] . "' ";
68 }
69 }
70
71 $query0 .= " FROM " . $fromtable;
72
73 $query0 .= " LEFT JOIN SequenceProcessStatus USING(fSequenceFirst,fTelescopeNumber) ";
74 $query0 .= " LEFT JOIN Calibration USING(fSequenceFirst,fTelescopeNumber) ";
75 $query0 .= " LEFT JOIN Star USING(fSequenceFirst,fTelescopeNumber) ";
76
77 foreach ($_GET as $key => $element)
78 if (($_GET[$key]=="On" || $groups>0))// && !empty(GetJoin($fromtable,$key)))
79 $query0 .= GetJoin($fromtable,$key);
80
81 if ($_GET["fOff"]=="Off")
82 {
83 if (strpos($query0, " WHERE ")==FALSE)
84 $query0 .= " WHERE ";
85 else
86 $query0 .= " AND ";
87
88 $query0 .= " NOT (fSourceName like '%Off%')";
89 }
90
91 if ($_GET["fOnlyOff"]=="On")
92 {
93 if (strpos($query0, " WHERE ")==FALSE)
94 $query0 .= " WHERE ";
95 else
96 $query0 .= " AND ";
97
98 $query0 .= " fSourceName like '%Off%'";
99 }
100
101 foreach ($checkwhere as $key => $element)
102 {
103 if (empty($element) || $element<=0)
104 continue;
105
106 if (strpos($query0, " WHERE ")==FALSE)
107 $query0 .= " WHERE ";
108 else
109 if ($element!=-1)
110 if (strrpos($query0, " AND ")!=strlen($query0)-5)
111 $query0 .= " AND ";
112
113 if ($element!=-1)
114 $query0 .= GetCheck($fromtable, $key) . "=" . $element;
115 }
116
117 if (strpos($query0, " WHERE ")==FALSE)
118 $query0 .= " WHERE ";
119 else
120 $query0 .= " AND ";
121
122 $query0 .= StatusQuery("fStar", $needs);
123
124 if (!empty($_GET["fRunMin"]) && !empty($_GET["fRunMax"]))
125 $query0 .= "Sequences.fSequenceFirst BETWEEN " . $_GET["fRunMin"] . " AND " . $_GET["fRunMax"] . " ";
126 else
127 $query0 = substr($query0, 0, -4);
128
129 if ((!empty($_GET["fZDMin"]) || $_GET["fZDMin"]==0) && !empty($_GET["fZDMax"]))
130 $query0 .= "AND (fZenithDistanceMin >= " . $_GET["fZDMin"] . " AND fZenithDistanceMax <= " . $_GET["fZDMax"] . ") ";
131
132 if (!empty($_GET["fSourceN"]))
133 $query0 .= " AND fSourceName REGEXP \"^" . $_GET["fSourceN"] . "\" ";
134
135 if (!empty($_GET["fStartDate"]))
136 {
137 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
138 $query0 .= " AND ";
139
140 $startdate=substr($_GET["fStartDate"], 0, 10);
141 if ($startdate=="0000-00-00")
142 $query0 .=" fRunStart >= '" . $startdate . " 00:00:00' ";
143 else
144 $query0 .= " fRunStart >= ADDDATE('" . $startdate . " 13:00:00', INTERVAL -1 DAY) ";
145 }
146
147 if (!empty($_GET["fStopDate"]))
148 {
149 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
150 $query0 .= " AND ";
151
152 $stopdate=substr($_GET["fStopDate"], 0, 10);
153 $query0 .= " fRunStart < '" . $stopdate . " 13:00:00' ";
154 }
155
156 if (!empty($_GET["fStarStart"]))
157 {
158 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
159 $query0 .= " AND ";
160
161 $starstart=substr($_GET["fStarStart"], 0, 10);
162 $query0 .=" fStar >= '" . $starstart . " 00:00:00' ";
163 }
164
165 if (!empty($_GET["fStarStop"]))
166 {
167 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
168 $query0 .= " AND ";
169
170 $starstop=substr($_GET["fStarStop"], 0, 10);
171 $query0 .= " fStar < '" . $starstop . " 23:59:59' ";
172 }
173
174 if ($groups>0)
175 {
176 $query0 .= " GROUP BY ";
177 $num = $groups;
178 foreach ($checkgroup as $key => $element)
179 if ($element==-1)
180 {
181 $query0 .= GetCheck($fromtable,$key);
182 if ($num-->1)
183 $query0 .= ", ";
184 }
185 }
186
187 if ($statusgroups>0)
188 {
189 $query0 .= " GROUP BY ";
190 $num = $statusgroups;
191 foreach ($checkstatusgroup as $key => $element)
192 if ($element==-1)
193 {
194 $query0 .= $alias[$key];
195 if ($num-->1)
196 $query0 .= ", ";
197 }
198 }
199
200 if (!empty($_GET["fSortBy"]))
201 {
202 $val=substr($_GET["fSortBy"], 0, -1);
203 $query0 .= " ORDER BY " . GetTable($fromtable,$val) . " ";
204 if (substr($_GET["fSortBy"], -1)=="-")
205 $query0 .= "DESC";
206 }
207
208 if (empty($_GET["fSortBy"]) && $groups==0 && $statusgroups==0)
209 $query0 .= "ORDER BY Sequences.fSequenceFirst ASC ";
210
211 if (empty($_GET["fNumStart"]))
212 $_GET["fNumStart"]=0;
213
214 if (empty($_GET["fSendTxt"]))
215 $query0 .= " LIMIT " . $_GET["fNumStart"] . ", " . $_GET["fNumResults"];
216
217 return $query0;
218 }
219
220 function InitGet($_GET)
221 {
222 // Find out whether it is the first call to the php script
223 $first = empty($_GET["fRunMin"]) && empty($_GET["fRunMax"]);
224
225 if (empty($_GET["fNumResults"]))
226 $_GET["fNumResults"]="50";
227
228 if (empty($_GET["fRunStart"]))
229 $_GET["fRunStart"]=$first?"On":"";
230
231 if (empty($_GET["fRunTime/60"]))
232 $_GET["fRunTime/60"]=$first?"On":"";
233
234 if (empty($_GET["fLinks"]))
235 $_GET["fLinks"]=$first?"On":"";
236
237 if (empty($_GET["fOff"]))
238 $_GET["fOff"]=$first?"On":"";
239
240 if (empty($_GET["fOnlyOff"]))
241 $_GET["fOnlyOff"]=$first?"Off":"";
242
243 if (empty($_GET["fZenithDistanceMin"]))
244 $_GET["fZenithDistanceMin"]=$first?"On":"";
245
246 if (empty($_GET["fZenithDistanceMax"]))
247 $_GET["fZenithDistanceMax"]=$first?"On":"";
248
249 if (empty($_GET["fSourceName"]))
250 $_GET["fSourceName"]="On";
251
252 if (empty($_GET["fLightConditionsName"]))
253 $_GET["fLightConditionsName"]=$first?"On":"";
254
255 if (empty($_GET["fStarStatus"]))
256 $_GET["fStarStatus"]=$first?"1":"";
257
258 if (empty($_GET["fUnsuitableInner"]))
259 $_GET["fUnsuitableInner"]=$first?"On":"";
260
261 if (empty($_GET["fMeanPedRmsInner"]))
262 $_GET["fMeanPedRmsInner"]=$first?"On":"";
263
264 if (empty($_GET["fArrTimeRmsInner"]))
265 $_GET["fArrTimeRmsInner"]=$first?"On":"";
266
267 if (empty($_GET["fConvFactorInner"]))
268 $_GET["fConvFactorInner"]=$first?"On":"";
269
270 if (empty($_GET["fPSF"]))
271 $_GET["fPSF"]=$first?"On":"";
272
273 if (empty($_GET["fEffOnTime"]))
274 $_GET["fEffOnTime"]=$first?"On":"";
275
276 if (empty($_GET["fEffOnTime/fRunTime"]))
277 $_GET["fEffOnTime/fRunTime"]=$first?"On":"";
278
279 if (empty($_GET["fMuonRate"]))
280 $_GET["fMuonRate"]=$first?"On":"";
281
282 if (empty($_GET["fInhomogeneity"]))
283 $_GET["fInhomogeneity"]="On";
284
285 if (empty($_GET["fDataRate"]))
286 $_GET["fDataRate"]=$first?"On":"";
287
288 InitFindOffData($_GET, $first);
289 }
290
291 function PrintForm($_GET, $host, $user, $pw, $db, $limitsmean, $limitsmin, $limitsmax, $alias)
292 {
293 printf("<center>\n");
294 printf("<form action=\"findoffdata.php\" METHOD=\"GET\">\n");
295
296 printf(" <table>\n");
297 printf(" <tr><td align='center' valign='top'>\n");
298
299 PrintSequInfoMenu();
300 printf(" <p>\n");
301 PrintCalMenu();
302 printf(" <p>\n");
303 PrintStarMenu();
304 printf(" <p>\n");
305
306 printf(" <table><tr><td>\n");
307 PrintPullDown($host, $user, $pw, $db, "Source", "fSourceName", "fSourceKEY", "Source Name");
308 printf(" </td><td>\n");
309 PrintPullDown($host, $user, $pw, $db, "LightConditions", "fLightConditionsName", "fLightConditionsKEY", "Light Conditions");
310 printf(" </td><td>\n");
311 PrintPullDown($host, $user, $pw, $db, "DiscriminatorThresholdTable", "fDiscriminatorThresholdTableName", "fDiscriminatorThresholdTableKEY", "DT Table");
312 printf(" </td></tr><tr><td>\n");
313 PrintPullDown($host, $user, $pw, $db, "ManuallyChanged", "fManuallyChangedName", "fManuallyChangedKEY", "Manually changed");
314 printf(" </td><td>\n");
315 PrintStatusMenu("fStar", "Star");
316 printf("</td></tr> </table>\n");
317 printf(" <p>\n");
318
319 printf(" <table>\n");
320 printf(" <tr><td colspan='3'>\n");
321
322 if (empty($_GET["fStarStart"]))
323 $starmin = GetMin("fStar", "SequenceProcessStatus", $host, $user, $pw, $db);
324 else
325 $starmin = $_GET["fStarStart"];
326
327 if (empty($_GET["fStarStop"]))
328 $starmax = GetMax("fStar", "SequenceProcessStatus", $host, $user, $pw, $db);
329 else
330 $starmax = $_GET["fStarStop"];
331
332 printf("StarDone&nbsp;(yyyy-mm-dd)&nbsp;from&nbsp;<input name=\"fStarStart\" type=\"text\" size=\"10\" maxlength=\"10\" value=\"%s\">\n", $starmin);
333 printf("to&nbsp;<input name=\"fStarStop\" type=\"text\" size=\"10\" maxlength=\"10\" value=\"%s\">&nbsp;&nbsp;&nbsp;&nbsp;\n", $starmax);
334 printf(" </td></tr><tr><td><br></td></tr><tr>\n");
335
336 CheckBox("fLinks", "Links");
337 CheckBox("fOff", "incl. offsources");
338 CheckBox("fOnlyOff", "only offsources");
339
340 printf(" </tr>\n");
341 printf(" </table>\n");
342
343 printf(" <p>\n");
344
345 PrintZdRangeMenu($host,$user,$pw,$db);
346 printf("<p>\n");
347 PrintSequRangeMenu($host,$user,$pw,$db);
348 printf("<p>\n");
349 PrintSourceMenu($host,$user,$pw,$db);
350 printf("<p>\n");
351 PrintNightRangeMenu($host,$user,$pw,$db, "Sequences");
352 printf("<p>\n");
353
354 printf(" Results:\n");
355 printf(" <select name=\"fNumResults\">\n");
356
357 $numres = array("10", "20", "50", "100", "200", "500");
358 foreach ($numres as $element)
359 {
360 if ($element==$_GET["fNumResults"])
361 printf("<option value=\"%s\" selected>%3s</option>\n", $element, $element);
362 else
363 printf("<option value=\"%s\">%3s</option>\n", $element, $element);
364 }
365 printf(" </select>\n");
366 printf(" &nbsp;&nbsp;&nbsp;\n");
367
368 printf("<input class='Width' type='submit' value='Query Table'>&nbsp;&nbsp;&nbsp;\n");
369 printf("<input class='Width' type='button' value='Reset' onClick='self.location.href=\"findoffdata.php\"'>&nbsp;&nbsp;&nbsp;\n");
370 if (strchr($_SERVER["REQUEST_URI"], '?')!=FALSE)
371 printf("<input class='Width' type='button' value='Get .txt' onClick='self.location.href=\"%s&fSendTxt=1\"'>&nbsp;&nbsp;&nbsp;\n", $_SERVER["REQUEST_URI"]);
372 if (strchr($_SERVER["REQUEST_URI"], '?')!=FALSE)
373 printf("<input class='Width' type='button' value='Print' onClick='self.location.href=\"%s&fPrintTable=1\"'>&nbsp;&nbsp;&nbsp;\n", $_SERVER["REQUEST_URI"]);
374
375 printf(" </td><td valign='top'>\n");
376
377 PrintLimitsMenu($limitsmean, $limitsmin, $limitsmax, $alias, "yes");
378
379 printf(" </td></tr>\n");
380 printf(" </table>\n");
381
382 printf("</form>\n");
383 printf("</center>\n");
384
385
386 ini_set("mysql.trace_mode", "Off");
387 ini_set("display_errors", "Off");
388
389
390 printf("</td>\n");
391 printf("</tr>\n");
392
393
394 printf("<tr class='Block'>\n");
395 printf("<td>\n");
396 }
397
398 function PrintPage($html, $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs)
399 {
400 $db_id = mysql_connect($host, $user, $pw);
401 if ($db_id==FALSE)
402 {
403 printf("mysql_connect returned the following error: %s\n", mysql_error());
404 die("");
405 }
406 mysql_select_db($db);
407
408 $query0 = CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup, $needs);
409
410 $result0 = mysql_query($query0, $db_id);
411
412 if ($result0)
413 {
414 if ($html=="1" || $html=="2")
415 PrintMagicTable($result0, $alias, $rightalign, $limitsmean, $limitsmin, $limitsmax, "", $_GET);
416 else
417 PrintText($result0);
418
419 mysql_free_result($result0);
420 }
421 mysql_close($db_id);
422
423 PrintSubmittedQuery($query0, $html, $db, "old");
424 }
425
426 include ("include.php");
427 include ("menu.php");
428 include ("db.php");
429 include ("magicdefs.php");
430
431 ini_set("display_errors", "On");
432 ini_set("mysql.trace_mode", "On");
433
434 if (!empty($_GET["fSendTxt"]))
435 {
436 header("Content-type: application/octet");
437 header("Content-Disposition: attachment; filename=query-result.txt");
438
439 PrintPage("0", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
440 }
441 else
442 {
443 echo (file_get_contents("index-header.html"));
444
445 $environment = sizeof($_GET);
446
447 InitGet($_GET);
448 if (empty($_GET["fPrintTable"]))
449 PrintForm($_GET, $host, $user, $pw, $db, $limitsmean, $limitsmin, $limitsmax, $alias);
450
451 if ($environment==0)
452 printf("No query submitted yet.<BR>");
453 else
454 {
455 if (empty($_GET["fPrintTable"]))
456 PrintPage("1", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
457 else
458 PrintPage("2", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
459 }
460
461 echo (file_get_contents("index-footer.html"));
462 }
463
464 ini_set("display_errors", "Off");
465 ini_set("mysql.trace_mode", "Off");
466}
467?>
Note: See TracBrowser for help on using the repository browser.