source: trunk/MagicSoft/Mars/datacenter/db/sequinfo-aio.php@ 9501

Last change on this file since 9501 was 9500, checked in by Daniela Dorner, 15 years ago
*** empty log message ***
  • Property svn:executable set to *
File size: 19.5 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 SQL_CALC_FOUND_ROWS ";
18
19 if ($groups>0 || ((!empty($_GET["fGroupByDate"])) && !$_GET["fGroupByDate"]==0) || $statusgroups>0)
20 {
21 if ($groups>0)
22 foreach ($checkgroup as $key => $element)
23 if ($element==-1)
24 $query0 .= $key . " as '" . $alias[$key] . "' " . ", ";
25
26 if ((!empty($_GET["fGroupByDate"])) && !$_GET["fGroupByDate"]==0)
27 {
28 $query0 .= " DATE_FORMAT(ADDDATE(fRunStart,Interval 12 hour), '";
29 switch($_GET["fGroupByDate"])
30 {
31 case "Year":
32 $query0 .= "%Y ";
33 break;
34 case "Month":
35 $query0 .= "%Y-%m ";
36 break;
37 case "Night":
38 $query0 .= "%Y-%m-%d ";
39 break;
40 }
41 $query0 .= "') AS GroupByDate, ";
42 }
43
44 //--------------------------------------------------
45 $query0 .= "SUM(fRunTime)/3600 as '" . $alias["SUM(fRunTime)/3600"] . "', ";
46 //--------------------------------------------------
47 $query0 .= " SUM(fNumEvents) as '" . $alias["SUM(fNumEvents)"] . "', ";
48 $query0 .= " Min(fZenithDistanceMin) as '" . $alias["Min(fZenithDistanceMin)"] . "', ";
49 $query0 .= " Max(fZenithDistanceMax) as '" . $alias["Max(fZenithDistanceMax)"] . "', ";
50 $query0 .= " COUNT(*) as '# Sequ' ";
51
52 if ($statusgroups>0)
53 {
54 foreach ($checkstatusgroup as $key => $element)
55 if ($element==-1)
56 $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] . "' ";
57// $query0 .= " (if(IsNull(" . $key . "), 'not done' ,if(" . $key . "='1970-01-01 00:00:00','dont do','done'))) as '" . $alias[$key] . "', ";
58
59 $query0 .= ", COUNT(*) as '# Sequ'";
60
61 }
62 }
63 else
64 {
65 if (!empty($_GET["fSendTxt"]))
66 $query0 .= " " . $fromtable . ".fSequenceFirst as '" . $alias["fSequenceFirst"] . "' ";
67 else
68 {
69 //old version using fRunMin, fRunMax
70 //$query0 .= " CONCAT('<A&ws;HREF=\"runinfo-aio.php?', 'fRunStart=On', '&amp;fZenithDistance=On', '&amp;fRunMin=', Sequences.fSequenceFirst, '&amp;fMeanTriggerRate=On', '&amp;fTest=On', '&amp;fRunTypeName=On', '&amp;fRunMax=', fSequenceLast, '&amp;fNumEvents=On', '&amp;fSourceName=On&amp;', 'fExcludedFDAKEY=1', '&amp;fSequenceFirst=On', '&amp;fRawFileAvail=On', '&amp;fCCFileAvail=On', '&amp;fCaCoFileAvail=On', '&amp;fNumResults=500\">', Sequences.fSequenceFirst, '</A>') ";
71 //new version using fSequenceNo
72 $query0 .= " CONCAT('<A&ws;HREF=\"runinfo-aio.php?', 'fRunStart=On', '&amp;fZenithDistance=On', '&amp;fSequenceNo=', Sequences.fSequenceFirst, '&amp;fMeanTriggerRate=On', '&amp;fTest=On', '&amp;fRunTypeName=On', '&amp;fNumEvents=On', '&amp;fSourceName=On&amp;', 'fExcludedFDAKEY=1', '&amp;fSequenceFirst=On', '&amp;fRawFileAvail=On', '&amp;fCCFileAvail=On', '&amp;fCaCoFileAvail=On', '&amp;fNumResults=500\">', 'M', Sequences.fTelescopeNumber, ':', LPAD(Sequences.fSequenceFirst, 8, ' '), '</A>') ";
73 //$query0 .= " as " . $alias["fSequenceFirst"];
74 $query0 .= " as 'Tel:Sequ'";
75 if (!empty($_GET["fLinks"]))
76 {
77 $query0 .= ", CONCAT('<A&ws;HREF=\"tabs.php?t=calib&n=', fSequenceFirst, '\">cal</A>'";
78 $query0 .= ", '&nbsp;<A&ws;HREF=\"tabs.php?t=signal&n=', fSequenceFirst, '\">sig</A>'";
79 $query0 .= ", '&nbsp;<A&ws;HREF=\"tabs.php?t=star&n=', fSequenceFirst, '\">star</A>'";
80 $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>' ";
81 //$query0 .= ", '&nbsp;<A&ws;HREF=\"sequence.php?fSequenceNo=', Sequences.fSequenceFirst , '&amp;fAll=On\">i</A>'";
82 $query0 .= ", '&nbsp;<A&ws;HREF=\"queryrbk.php?fNight=', date_format(adddate(fRunStart, interval +13 HOUR), '%Y-%m-%d') , '&amp;fDate=2\">rbk</A>') ";
83 //$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>'";
84 //$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>'";
85 //$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>'";
86 //$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>'";
87 //$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>' ";
88 //$query0 .= ", '&nbsp;<A&ws;HREF=\"sequence.php?fSequenceNo=', Sequences.fSequenceFirst , '&amp;fAll=On\">i</A>'";
89 //$query0 .= ", '&nbsp;<A&ws;HREF=\"queryrbk.php?fNight=', date_format(adddate(fRunStart, interval +13 HOUR), '%Y-%m-%d') , '&amp;fDate=2\">rbk</A>') ";
90 $query0 .= " as 'Links'";
91 }
92 }
93
94 foreach ($_GET as $key => $element)
95 if ($_GET[$key]=="On")
96 if ($key!="fLinks" && $key!="fOff" && $key!="fOnlyOff" && $key!="fOnlySum")
97 if (empty($checkwhere[$key]) || $checkwhere[$key]==0)
98 $query0 .= ", " . $key . " as '" . $alias[$key] . "' ";
99 }
100
101 $query0 .= " FROM " . $fromtable;
102
103 $query0 .= " LEFT JOIN SequenceProcessStatus USING(fSequenceFirst,fTelescopeNumber) ";
104 $query0 .= " LEFT JOIN Calibration USING(fSequenceFirst,fTelescopeNumber) ";
105 $query0 .= " LEFT JOIN Star USING(fSequenceFirst,fTelescopeNumber) ";
106
107 foreach ($_GET as $key => $element)
108 if (($_GET[$key]=="On" || $groups>0))// && !empty(GetJoin($fromtable,$key)))
109 $query0 .= GetJoin($fromtable,$key);
110
111 if ($_GET["fTest"]!="On")
112 {
113 if ($_GET["fSourceName"]!="On")
114 $query0 .= " LEFT JOIN Source USING(fSourceKEY) ";
115 $query0 .= " WHERE fTest='no'";
116 }
117
118 if ($_GET["fOff"]=="Off")
119 {
120 if (strpos($query0, " WHERE ")==FALSE)
121 $query0 .= " WHERE ";
122 else
123 $query0 .= " AND ";
124
125 $query0 .= " NOT (fSourceName like '%Off%')";
126 }
127
128 if ($_GET["fOnlyOff"]=="On")
129 {
130 if (strpos($query0, " WHERE ")==FALSE)
131 $query0 .= " WHERE ";
132 else
133 $query0 .= " AND ";
134
135 $query0 .= " fSourceName like '%Off%'";
136 }
137
138 if ($_GET["fOnlySum"]=="On")
139 {
140 if (strpos($query0, " WHERE ")==FALSE)
141 $query0 .= " WHERE ";
142 else
143 $query0 .= " AND ";
144
145 $query0 .= " NOT ISNULL(fRateSumEvts) ";
146 }
147
148 foreach ($checkwhere as $key => $element)
149 {
150 if (empty($element) || $element<=0)
151 continue;
152
153 if (strpos($query0, " WHERE ")==FALSE)
154 $query0 .= " WHERE ";
155 else
156 if ($element!=-1)
157 if (strrpos($query0, " AND ")!=strlen($query0)-5)
158 $query0 .= " AND ";
159
160 if ($element!=-1)
161 $query0 .= GetCheck($fromtable, $key) . "=" . $element;
162 }
163
164 if (strpos($query0, " WHERE ")==FALSE)
165 $query0 .= " WHERE ";
166 else
167 $query0 .= " AND ";
168
169 $query0 .= StatusQuery("fSequenceFileWritten", $needs, $timelimits);
170 $query0 .= StatusQuery("fAllFilesAvail", $needs, $timelimits);
171 $query0 .= StatusQuery("fCallisto", $needs, $timelimits);
172 $query0 .= StatusQuery("fFillCallisto", $needs, $timelimits);
173 $query0 .= StatusQuery("fStar", $needs, $timelimits);
174 $query0 .= StatusQuery("fFillStar", $needs, $timelimits);
175
176 if (!empty($_GET["fRunMin"]) && !empty($_GET["fRunMax"]))
177 $query0 .= "Sequences.fSequenceFirst BETWEEN " . $_GET["fRunMin"] . " AND " . $_GET["fRunMax"] . " ";
178 else
179 $query0 = substr($query0, 0, -4);
180
181 if ((!empty($_GET["fZDMin"]) || $_GET["fZDMin"]==0) && !empty($_GET["fZDMax"]))
182 $query0 .= "AND (fZenithDistanceMin >= " . $_GET["fZDMin"] . " AND fZenithDistanceMax <= " . $_GET["fZDMax"] . ") ";
183
184 if (!empty($_GET["fSourceN"]))
185 $query0 .= " AND fSourceName REGEXP \"^" . $_GET["fSourceN"] . "\" ";
186
187 if (!empty($_GET["fStartDate"]))
188 {
189 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
190 $query0 .= " AND ";
191
192 $startdate=substr($_GET["fStartDate"], 0, 10);
193 if ($startdate=="0000-00-00")
194 $query0 .=" fRunStart >= '" . $startdate . " 00:00:00' ";
195 else
196 $query0 .= " fRunStart >= ADDDATE('" . $startdate . " 13:00:00', INTERVAL -1 DAY) ";
197 }
198
199 if (!empty($_GET["fStopDate"]))
200 {
201 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
202 $query0 .= " AND ";
203
204 $stopdate=substr($_GET["fStopDate"], 0, 10);
205 $query0 .= " fRunStart < '" . $stopdate . " 13:00:00' ";
206 }
207
208 if (!empty($_GET["fStarStart"]))
209 {
210 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
211 $query0 .= " AND ";
212
213 $starstart=substr($_GET["fStarStart"], 0, 10);
214 $query0 .=" fStar >= '" . $starstart . " 00:00:00' ";
215 }
216
217 if (!empty($_GET["fStarStop"]))
218 {
219 if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
220 $query0 .= " AND ";
221
222 $starstop=substr($_GET["fStarStop"], 0, 10);
223 $query0 .= " fStar < '" . $starstop . " 23:59:59' ";
224 }
225
226 if ($groups>0)
227 {
228 $query0 .= " GROUP BY ";
229 $num = $groups;
230 foreach ($checkgroup as $key => $element)
231 if ($element==-1)
232 {
233 $query0 .= GetCheck($fromtable,$key);
234 if ($num-->1)
235 $query0 .= ", ";
236 }
237 }
238
239 if ((!empty($_GET["fGroupByDate"])) && !$_GET["fGroupByDate"]==0)
240 if (strpos($query0, " GROUP BY ")==FALSE)
241 $query0 .= " GROUP BY GroupByDate ";
242 else
243 $query0 .= ", GroupByDate ";
244
245 if ($statusgroups>0)
246 {
247// $query0 .= " GROUP BY ";
248 if (strpos($query0, " GROUP BY ")==FALSE)
249 $query0 .= " GROUP BY ";
250 else
251 $query0 .= ", ";
252 $num = $statusgroups;
253 foreach ($checkstatusgroup as $key => $element)
254 if ($element==-1)
255 {
256 $query0 .= $alias[$key];
257 if ($num-->1)
258 $query0 .= ", ";
259 }
260 }
261
262 if (!empty($_GET["fSortBy"]))
263 {
264 $val=substr($_GET["fSortBy"], 0, -1);
265 $query0 .= " ORDER BY " . GetTable($fromtable,$val) . " ";
266 if (substr($_GET["fSortBy"], -1)=="-")
267 $query0 .= "DESC";
268 }
269
270 if (empty($_GET["fSortBy"]) && $groups==0 && $statusgroups==0)
271 $query0 .= "ORDER BY Sequences.fSequenceFirst ASC ";
272
273 if (empty($_GET["fNumStart"]))
274 $_GET["fNumStart"]=0;
275
276 if (empty($_GET["fSendTxt"]))
277 $query0 .= " LIMIT " . $_GET["fNumStart"] . ", " . $_GET["fNumResults"];
278
279 return $query0;
280 }
281
282 function InitGet()
283 {
284 // Find out whether it is the first call to the php script
285 $first = empty($_GET["fRunMin"]) && empty($_GET["fRunMax"]);
286
287 if (empty($_GET["fNumResults"]))
288 $_GET["fNumResults"]="50";
289
290 if (empty($_GET["fRunStart"]))
291 $_GET["fRunStart"]=$first?"On":"";
292
293 if (empty($_GET["fRunTime/60"]))
294 $_GET["fRunTime/60"]=$first?"On":"";
295
296 if (empty($_GET["fZenithDistanceMin"]))
297 $_GET["fZenithDistanceMin"]=$first?"On":"";
298
299 if (empty($_GET["fSourceName"]))
300 $_GET["fSourceName"]=$first?"On":"";
301
302 if (empty($_GET["fStar"]))
303 $_GET["fStar"]=$first?"On":"";
304
305 if (empty($_GET["fInhomogeneity"]))
306 $_GET["fInhomogeneity"]=$first?"On":"";
307
308 if (empty($_GET["fPSF"]))
309 $_GET["fPSF"]=$first?"On":"";
310
311 if (empty($_GET["fMeanPedRmsInner"]))
312 $_GET["fMeanPedRmsInner"]=$first?"On":"";
313
314 if (empty($_GET["fOff"]))
315 $_GET["fOff"]=$first?"On":"";
316
317 if (empty($_GET["fLinks"]))
318 $_GET["fLinks"]=$first?"On":"";
319
320 if (empty($_GET["fOnlyOff"]))
321 $_GET["fOnlyOff"]=$first?"Off":"";
322
323 if (empty($_GET["fOnlySum"]))
324 $_GET["fOnlySum"]=$first?"Off":"";
325
326 InitFindOffData($first);
327 }
328
329 function PrintForm($_GET, $host, $user, $pw, $db, $limitsmean, $limitsmin, $limitsmax, $alias)
330 {
331 printf("<center>\n");
332 printf("<form action='sequinfo-aio.php' METHOD='GET'>\n");
333 printf("<input id='sh' type='hidden' name='fShowHide' value='");
334 if (!empty($_GET["fShowHide"]))
335 printf("%s", $_GET["fShowHide"]);
336 else
337 printf("1000000010");
338 printf("'>\n");
339 printf("<img id='allbutton' src='minus.png' alt='-' onclick='showhide(\"all\")'> <b>Menu</b>&nbsp;&nbsp;&nbsp;&nbsp; \n");
340 printf("&nbsp;&nbsp;<img id='infobutton' src='plus.png' alt='+' onClick='showhide(\"info\");showhide(\"info2\")'> SequInfo \n");
341 printf("&nbsp;&nbsp;<img id='statbutton' src='plus.png' alt='+' onClick='showhide(\"stat\");showhide(\"fail\")'> StatusInfo \n");
342 printf("&nbsp;&nbsp;<img id='calbutton' src='plus.png' alt='+' onClick='showhide(\"cal\")'> CalInfo \n");
343 printf("&nbsp;&nbsp;<img id='starbutton' src='plus.png' alt='+' onClick='showhide(\"star\")'> StarInfo \n");
344 printf("&nbsp;&nbsp;<img id='limitsbutton' src='plus.png' alt='+' onClick='showhide(\"limits\")'> Limits \n");
345 printf("&nbsp;&nbsp;<img id='rangesbutton' src='minus.png' alt='-' onClick='showhide(\"ranges\")'> Ranges \n");
346
347 printf(" <div id='all' style='display:block'>");
348
349 PrintSequInfoMenu();
350 PrintSequInfo2Menu($host,$user,$pw,$db);
351 PrintSequStatMenu();
352 PrintFailMenu();
353 PrintCalMenu();
354 PrintStarMenu();
355 PrintLimitsMenu($limitsmean, $limitsmin, $limitsmax, $alias, "");
356
357 printf(" <div id='ranges' style='display:block'>");
358 PrintZdRangeMenu($host,$user,$pw,$db);
359 PrintSequRangeMenu($host,$user,$pw,$db);
360 printf("<p>");
361 PrintSourceMenu($host,$user,$pw,$db);
362 PrintGroupByDateMenu();
363 printf("<p>");
364 PrintNightRangeMenu($host,$user,$pw,$db, "Sequences");
365 printf("<p>");
366 printf("</div>");
367
368 printf("</div>");
369 printf(" <P>\n");
370
371
372 ini_set("mysql.trace_mode", "Off");
373 ini_set("display_errors", "Off");
374
375 PrintNumResPullDown();
376 PrintButtons("sequinfo-aio.php");
377
378 printf("</form>\n");
379 printf("</center>\n");
380 printf("</td>\n");
381 printf("</tr>\n");
382 printf("<tr class='Block'>\n");
383 printf("<td>\n");
384 }
385
386 function PrintPage($html, $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs)
387 {
388 $db_id = mysql_pconnect($host, $user, $pw);
389 if ($db_id==FALSE)
390 {
391 printf("mysql_connect returned the following error: %s\n", mysql_error());
392 die("");
393 }
394 mysql_select_db($db);
395
396 $query0 = CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup, $needs);
397
398 $result0 = mysql_query($query0, $db_id);
399 $result1 = mysql_query("SELECT FOUND_ROWS()", $db_id);
400
401 if ($result0)
402 {
403 if ($html=="1" || $html=="2")
404 PrintMagicTable($result0, $alias, $rightalign, $limitsmean, $limitsmin, $limitsmax, $result1, "");
405 else
406 PrintText($result0);
407
408 mysql_free_result($result0);
409 mysql_free_result($result1);
410 }
411 mysql_close($db_id);
412
413 PrintSubmittedQuery($query0, $html, $db, "");
414 }
415
416 include ("include.php");
417 include ("menu.php");
418 include ("db.php");
419 include ("magicdefs.php");
420
421 ini_set("display_errors", "On");
422 ini_set("mysql.trace_mode", "On");
423
424 if (!empty($_GET["fSendTxt"]))
425 {
426 header("Content-type: application/octet");
427 header("Content-Disposition: attachment; filename=query-result.txt");
428
429 PrintPage("0", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
430 }
431 else
432 {
433 if (empty($_GET["fPrintTable"]))
434 echo (file_get_contents("index-header.html"));
435
436 $environment = sizeof($_GET);
437
438 InitGet();
439 if (empty($_GET["fPrintTable"]))
440 PrintForm($_GET, $host, $user, $pw, $db, $limitsmean, $limitsmin, $limitsmax, $alias);
441
442 if ($environment==0)
443 printf("No query submitted yet.<BR>");
444 else
445 {
446 if (empty($_GET["fPrintTable"]))
447 PrintPage("1", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
448 else
449 PrintPage("2", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
450 }
451
452 if (empty($_GET["fPrintTable"]))
453 echo (file_get_contents("index-footer.html"));
454 }
455
456 ini_set("display_errors", "Off");
457 ini_set("mysql.trace_mode", "Off");
458}
459?>
Note: See TracBrowser for help on using the repository browser.