API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 29th, 2016
69
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 29.57 KB | None | 0 0
raw download clone embed print report
  1. google.setOnLoadCallback(drawManyCharts);
  2.  
  3. function drawManyCharts()
  4. //parameters are: timeUnit, target div, abscissa bins, MultiplicationFactor, Graphtype, SensorString
  5. {drawAllCharts("minutes", "GoogleChart_div", 500, 0.01, "Linechart", "Temperature");
  6. drawAllCharts("hours", "GoogleChart2_div", 400, 0.01, "Linechart", "Temperature");
  7. drawAllCharts("days", "GoogleChart3_div", 48, 0.01, "Linechart", "Temperature");
  8. drawAllCharts("weeks", "GoogleChart4_div", 48, 0.01, "Linechart", "Temperature");
  9. drawAllCharts("hours", "GoogleChartPressure_div", 48, 0.01, "Linechart", "Pressure");
  10. }
  11.  
  12. function drawAllCharts(TimeUnit, containerChart, NoAbscissaBins, MultiplicationFactor, Graphtype, SensorString)
  13. {
  14.  
  15. // var SensorString="Temperature";
  16. var TableType="integer";
  17. // var NoAbscissaBins=12;
  18. var DataTableName="dataMasterTable";
  19. // var TimeUnit="days";
  20. var TimeStart="test";
  21. // var Graphtype="Linechart";
  22. var graphtitle = "Heizungskeller";
  23. var OrdinateLabel = "Temperature (Celsius 100x)"
  24. var maxViewWindow= 8000;
  25. var minViewWindow= 0;
  26.  
  27. var GoogleArray = $.ajax({
  28. url: "/php/PrepareGoogleDataTable.php" +"?"+
  29. "SensorString="+SensorString + "&"+
  30. "TableType=" + TableType +"&"+
  31. "NoAbscissaBins=" + NoAbscissaBins + "&"+
  32. "MultiplicationFactor=" + MultiplicationFactor + "&"+
  33. "DataTableName=" + DataTableName +"&"+
  34. "TimeUnit=" + TimeUnit +"&"+
  35. "TimeStart=" + TimeStart,
  36. //dataType:"json",
  37. async: false
  38. }).responseText;
  39. console.log(GoogleArray);
  40. GoogleArray = JSON.parse(GoogleArray);
  41. eval(GoogleArray.GoogleAddColumns);
  42. eval(GoogleArray.GoogleAddRows);
  43.  
  44.  
  45.  
  46. //create dynamic dataview for charts
  47. //columns as specified by TableType
  48. switch (TableType)
  49. {
  50. case "boolean":
  51. eval(GoogleArray.GoogleVisualizeColumnCount) ;
  52. break;
  53.  
  54. case "integer" :
  55. eval(GoogleArray.GoogleVisualizeColumnsMinStdevStdevMax) ;
  56. eval(GoogleArray.GoogleVisualizeColumnsAvg) ;
  57. break;
  58.  
  59. case "float" :
  60. eval(GoogleArray.GoogleVisualizeColumnsMinStdevStdevMax) ;
  61. eval(GoogleArray.GoogleVisualizeColumnsAvg) ;
  62. break;
  63.  
  64. case "string" :
  65. break;
  66.  
  67.  
  68. }
  69.  
  70. switch (Graphtype)
  71. {
  72. case "Candlestick":
  73. var optionsCandlestick = {
  74. width: '100%',
  75. height: 480,
  76. bar:{groupWidth: '90%'},
  77. vAxis: {minValue: 0,
  78. baseline: 0,
  79. textStyle: {color: 'blue',
  80. fontSize: 10},
  81. viewWindowMode: 'explicit',
  82. viewWindow: {max: maxViewWindow,
  83. min: minViewWindow
  84. }
  85. },
  86. hAxis: {textStyle:
  87. {color: 'blue',
  88. fontSize: 10},
  89. },
  90. pointSize: 5,
  91. title: graphtitle
  92. };
  93.  
  94. var wrapper = new google.visualization.ChartWrapper(
  95. {chartType: 'CandlestickChart',
  96. dataTable: dataViewMinStdevStdevMax,
  97. options: optionsCandlestick,
  98. containerId: containerChart});
  99. wrapper.draw();
  100.  
  101. break;
  102.  
  103. case "StackedColumn":
  104.  
  105. var optionsColumnchart = {
  106. chartArea: {width: '80%', height: '70%'},
  107. //legend: {position: 'in'},
  108. //titlePosition: 'in',
  109. axisTitlesPosition: 'in',
  110. hAxis: {textPosition: 'in'},
  111. vAxis: {textPosition: 'in'},
  112. width: '100%',
  113. height: 520,
  114. isStacked: true,
  115. pointSize: 3,
  116. fontSize: 10,
  117. backgroundColor: 'transparent',
  118. bar:{groupWidth: '90%'},
  119. hAxis:{slantedTextAngle: 90},
  120. legend:{position: 'in', textStyle: {color: 'blue', fontSize: 12}},
  121. title: graphtitle
  122. };
  123. var wrapper = new google.visualization.ChartWrapper(
  124. {chartType: 'ColumnChart',
  125. dataTable: dataViewCount,
  126. options: optionsColumnchart,
  127. containerId: containerChart});
  128. wrapper.draw();
  129. break;
  130.  
  131. case "Linechart":
  132. //these are the customizable options from google
  133. var optionsLinechart = {
  134. chartArea: {width: '80%', height: '70%'},
  135. //legend: {position: 'in'},
  136. width: 1000,
  137. height: 480,
  138. pointSize: 0,
  139. title: graphtitle,
  140. vAxis: {title: OrdinateLabel}
  141. };
  142.  
  143. var wrapper = new google.visualization.ChartWrapper(
  144. {chartType: 'LineChart',
  145. dataTable: dataViewAvg,
  146. options: optionsLinechart,
  147. containerId: containerChart});
  148. wrapper.draw();
  149. break;
  150.  
  151. }
  152.  
  153. }
  154. </script>
  155.  
  156. <?php
  157. if(isset($_GET['SensorString']) &&
  158. (!empty($_GET['SensorString'])) &&
  159. (!empty($_GET['TableType'])) &&
  160. (!empty($_GET['TimeUnit'])) &&
  161. (!empty($_GET['NoAbscissaBins'])) &&
  162. (!empty($_GET['DataTableName'])) &&
  163. (!empty($_GET['MultiplicationFactor'])) &&
  164. (!empty($_GET['TimeStart'])))
  165.  
  166. {$SensorString = str_replace ( "*", "%", $_GET['SensorString']); // string to fill "WHERE" condition
  167. $TableType = $_GET['TableType']; // boolean/integer/float/string
  168. $TimeUnit = $_GET['TimeUnit']; //hours/days/weeks/months/years
  169. $NoAbscissaBins = $_GET['NoAbscissaBins']; //number of time units to be displayed on the abscissa
  170. $DataTableName = $_GET['DataTableName']; //number of time units to be displayed on the abscissa
  171. $TimeStart = $_GET['TimeStart']; //should not necessarily be used
  172.  
  173. }
  174. else
  175. {// assume Bewegungsmelder, boolean, hours, 12 bins
  176. $SensorString = "OutsideTemp";
  177. $TableType = "float";
  178. $TimeUnit = "days";
  179. $TimeStart = -12;
  180. $NoAbscissaBins = 30;
  181. $DataTableName = "dataMasterTable";
  182. }
  183.  
  184. $RedirectEchoToFile = false;
  185.  
  186.  
  187.  
  188. $DataCollectionArray = DataCollectionCrossTab($SensorString, $TableType, $TimeUnit, $NoAbscissaBins, $TimeStart, $DataTableName);
  189. //echo "GoogleDataRow: ". $DataCollectionArray['GoogleDataRow'] ;
  190. $GoogleArray = PrepareGoogleChart($DataCollectionArray);
  191.  
  192.  
  193. /* $GoogleArray has the following keys:
  194. / [0] => GoogleAddColumns
  195. / [1] => GoogleAddRows
  196. / [2] => GoogleVisualizeColumnCount
  197. / [3] => GoogleVisualizeColumnsAvg
  198. / [4] => GoogleVisualizeColumnsMinStdevStdevMax
  199. / [5] => MainCrosstabSelectQuery
  200. */
  201.  
  202.  
  203. echo json_encode($GoogleArray);
  204.  
  205.  
  206.  
  207. //---------------------------------------------------
  208.  
  209. Function PrepareGoogleChart($DataCollectionArray)
  210. {
  211. // var GoogleAddColumns adds columns to DataTable
  212.  
  213. $TimeUnit = "hours"; // (can be "hours", "days", "months", "years" )
  214. $GoogleAddColumns = "";
  215.  
  216. // var GoogleVisualizeColumnsAll renders DataView1 view of DataTable
  217. // var VisualizeFirstColumn sets the first column, and renders "hours" (can be changed to days etc.)
  218. $VisualizeFirstColumn = ".setColumns([
  219. {calc: function(data, row)
  220. {return data.getFormattedValue(row, "
  221. . ColumnLabel($TimeUnit). "); },
  222. type:'string'}";
  223.  
  224. //set up dataView1, which is essentially useless
  225. $GoogleVisualizeColumnsAll =
  226. "var dataView1 = new google.visualization.DataView(".$DataCollectionArray['DataTableName'].");".chr(13)
  227. ."dataView1".$VisualizeFirstColumn;
  228.  
  229. //set up dataViewCount, which is good to count events from IpsLoggingBoolean and feed a column chart
  230. $GoogleVisualizeColumnCount =
  231. "var dataViewCount = new google.visualization.DataView(".$DataCollectionArray['DataTableName'].");"
  232. .chr(13)."dataViewCount".$VisualizeFirstColumn;
  233.  
  234. //set up dataViewAvg, which visualizes only the averages and is good for line charts
  235. $GoogleVisualizeAvg =
  236. "var dataViewAvg =
  237. new google.visualization.DataView(".$DataCollectionArray['DataTableName'].");".chr(13).
  238. "dataViewAvg"
  239. .".setColumns([ {calc: function(data, row)
  240. {return data.getFormattedValue(row, "
  241. . ColumnLabel($TimeUnit). "); },
  242. type:'string'}";
  243.  
  244. //set up dataViewMinStdevStdevMax, which can feed a candlestick chart
  245. $GoogleVisualizeColumnsMinStdevStdevMax =
  246. "var dataViewMinStdevStdevMax =
  247. new google.visualization.DataView(".$DataCollectionArray['DataTableName'].");".chr(13)
  248. ."dataViewMinStdevStdevMax"
  249. .".setColumns([ {calc: function(data, row)
  250. {return data.getFormattedValue(row, "
  251. . ColumnLabel($TimeUnit). "); },
  252. type:'string'}";
  253.  
  254. $GoogleVisualizeScattergram =
  255. "var dataViewScattergram = new google.visualization.DataView(".$DataCollectionArray['DataTableName'].");"
  256. .chr(13)."dataViewScattergram.setColumns([";
  257.  
  258.  
  259. //create strings for Google DataTable/DataView1 columns definition
  260. $GoogleAddColumns =
  261. "var ". $DataCollectionArray['DataTableName']. "= new google.visualization.DataTable();".chr(13).
  262. $DataCollectionArray['DataTableName'].".addColumn('string', 'hoursIndex');".chr(13).
  263. $DataCollectionArray['DataTableName'].".addColumn('string', 'hours');".chr(13).
  264. $DataCollectionArray['DataTableName'].".addColumn('string', 'PlaceHolder1');".chr(13).
  265. $DataCollectionArray['DataTableName'].".addColumn('string', 'PlaceHolder2');".chr(13).
  266. $DataCollectionArray['DataTableName'].".addColumn('string', 'PlaceHolder3'); ";
  267.  
  268. for ($varIndex=0; $varIndex < ($DataCollectionArray['NumberOfColumns']-5); $varIndex++)
  269. {
  270.  
  271. Switch ($DataCollectionArray['TableType'])
  272. {
  273. case "boolean":
  274. $GoogleAddColumns .=
  275. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. ", 'count$varIndex'); ";
  276. break;
  277.  
  278. case "integer":
  279. $GoogleAddColumns .=
  280. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'avg'); ".chr(13).
  281. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'stddev+'); ".chr(13).
  282. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'stddev-'); ". chr(13).
  283. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'max'); " .chr(13).
  284. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'min'); " .chr(13);
  285. break;
  286.  
  287. case "float":
  288. $GoogleAddColumns .=
  289. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'avg'); ".chr(13).
  290. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'stddev+'); ".chr(13).
  291. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'stddev-'); ". chr(13).
  292. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'max'); " .chr(13).
  293. $DataCollectionArray['DataTableName'].".addColumn('number', ".$DataCollectionArray['CategoryArray'][$varIndex]. " +'min'); " .chr(13);
  294. break;
  295.  
  296. case "string":
  297. //no visualization in case of strings, but a "tableChart" could be generated if need be.
  298. break;
  299. }
  300.  
  301. $GoogleVisualizeColumnsAll .=
  302. ", ". strval(($varIndex+1)*4+1).", ".
  303. strval(($varIndex+1)*4+2).", ".
  304. strval(($varIndex+1)*4+3).", ".
  305. strval(($varIndex+1)*4+4);
  306.  
  307. $GoogleVisualizeColumnCount .=
  308. ", ". strval(($varIndex+1)+4);
  309.  
  310. $GoogleVisualizeAvg .=
  311. ", ". strval(($varIndex+1)*5+0);
  312.  
  313. $GoogleVisualizeColumnsMinStdevStdevMax .=
  314. ", ". strval(($varIndex+1)*5+1).
  315. ", ". strval(($varIndex+1)*5+2).
  316. ", ". strval(($varIndex+1)*5+3).
  317. ", ". strval(($varIndex+1)*5+4);
  318.  
  319. $GoogleVisualizeScattergram .= strval(($varIndex+1)*4+2).", ";
  320. }
  321.  
  322. //echo "Tabletype: ". $DataCollectionArray['TableType'].chr(13)."<br>"."<br>";
  323. //echo "GoogleAddColumns: ". $GoogleAddColumns.chr(13)."<br>"."<br>";
  324.  
  325. $GoogleVisualizeColumnsAll .= "]);".chr(13);
  326. $GoogleVisualizeColumnCount .= "]);".chr(13);
  327. $GoogleVisualizeAvg .= "]);".chr(13);
  328. $GoogleVisualizeColumnsMinStdevStdevMax.= "]);".chr(13);
  329. $GoogleVisualizeScattergram = substr($GoogleVisualizeScattergram, 0, -2) . "]);".chr(13);
  330.  
  331. VerboseEcho($GoogleAddColumns. "<br><br><br>".$GoogleVisualizeColumnsAll);
  332.  
  333.  
  334. //create dataset rows to be processed into google datatable
  335. $DataForGoogleChart = $DataCollectionArray['GoogleDataRow'];
  336.  
  337.  
  338.  
  339. //echo "<br><font color='green'>DataForGoogleChart: $DataForGoogleChart</font><br>";
  340.  
  341.  
  342. $GoogleFormattedDataArray = array(
  343. 'GoogleAddColumns' => $GoogleAddColumns,
  344. 'GoogleAddRows' => $DataCollectionArray['DataTableName'].'.addRows('.$DataCollectionArray['GoogleDataRow'].'); '.chr(13),
  345. 'GoogleVisualizeColumnCount' => $GoogleVisualizeColumnCount,
  346. 'GoogleVisualizeColumnsAvg' => $GoogleVisualizeAvg,
  347. 'GoogleVisualizeColumnsMinStdevStdevMax' => $GoogleVisualizeColumnsMinStdevStdevMax,
  348. 'MainCrosstabSelectQuery' => $DataCollectionArray['MainCrosstabSelectQuery']
  349. );
  350.  
  351. return $GoogleFormattedDataArray;
  352. }
  353.  
  354.  
  355.  
  356.  
  357. //-----------------------------------------------------
  358.  
  359. Function DataCollectionCrossTab($SensorString, $TableType, $TimeUnit, $NoAbscissaBins, $TimeStart, $DataTableName) //sensorstring: e.g. DigitalInput 2
  360. {
  361. $dbconn = pg_connect("host=10.10.10.8 port=5432 dbname=IpsLogging user=ips password=IPS")
  362. or die ('connection aborted: ' . pg_last_error().chr(13));
  363.  
  364. switch ($TableType)
  365. {
  366. case "boolean":
  367. $IpsTable = "loggingdb_ips_boolean";
  368. $AggregateFunctions = "COUNT(*) As value ";
  369. $SensorColumn = 2;
  370. $WhereClause=" AND (log.ipsvalue = true) "; //only movement triggers
  371. break;
  372. case "integer":
  373. $IpsTable = "loggingdb_ips_integer";
  374. $AggregateFunctions = "(AVG(ipsvalue)::NUMERIC(8,2))
  375. ||'', '' ||
  376. (MIN(ipsvalue)::NUMERIC(8,2))
  377. ||'', '' ||
  378. ((AVG(ipsvalue)::NUMERIC(8,2)) - (coalesce(STDDEV(ipsvalue), 0)::NUMERIC(8,2)))
  379. ||'', '' ||
  380. ((AVG(ipsvalue)::NUMERIC(8,2)) + (coalesce(STDDEV(ipsvalue), 0)::NUMERIC(8,2)))
  381. ||'', '' ||
  382. (MAX(ipsvalue)::NUMERIC(8,2))
  383. As value ";
  384. $WhereClause="";
  385. $SensorColumn = 4;
  386. break;
  387. case "float":
  388. $IpsTable = "loggingdb_ips_float";
  389. $AggregateFunctions = "(AVG(ipsvalue)::NUMERIC(8,2))
  390. ||'', '' ||
  391. (MIN(ipsvalue)::NUMERIC(8,2))
  392. ||'', '' ||
  393. ((AVG(ipsvalue)::NUMERIC(8,2)) - (coalesce(STDDEV(ipsvalue), 0)::NUMERIC(8,2)))
  394. ||'', '' ||
  395. ((AVG(ipsvalue)::NUMERIC(8,2)) + (coalesce(STDDEV(ipsvalue), 0)::NUMERIC(8,2)))
  396. ||'', '' ||
  397. (MAX(ipsvalue)::NUMERIC(8,2))
  398. As value ";
  399. $SensorColumn = 4;
  400. $WhereClause="";
  401. break;
  402. case "string":
  403. $IpsTable = "loggingdb_ips_string";
  404. $WhereClause="";
  405. break;
  406. }
  407.  
  408. Switch ($TimeUnit)
  409. {case "minutes":
  410. $SelectRowNames =
  411. "to_char(ipstimestamp, ''YYYYMMDDHH24MI'') As row_name,
  412. to_char(ipstimestamp, ''FMHH24h:MI'') As labelled_row_name,
  413. ''PlaceHolder1'' As PlaceHolder1,
  414. ''PlaceHolder2'' As PlaceHolder2,
  415. ''PlaceHolder3'' As PlaceHolder3,";
  416. $GroupOrder = "
  417. GROUP BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category
  418. ORDER BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category', ";
  419. break;
  420.  
  421. case "hours":
  422. $SelectRowNames =
  423. "to_char(ipstimestamp, ''YYYYMMDDHH24'') As row_name,
  424. to_char(ipstimestamp, ''FMDD mon FMHH24h'') As labelled_row_name,
  425. ''PlaceHolder1'' As PlaceHolder1,
  426. ''PlaceHolder2'' As PlaceHolder2,
  427. ''PlaceHolder3'' As PlaceHolder3,";
  428. $GroupOrder = "
  429. GROUP BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category
  430. ORDER BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category', ";
  431. break;
  432.  
  433. case "days":
  434. $SelectRowNames =
  435. "to_char(ipstimestamp, ''YYYYMMDD'') As row_name,
  436. to_char(ipstimestamp, ''FMDD mon YYYY'') As labelled_row_name,
  437. ''PlaceHolder1'' As PlaceHolder1,
  438. ''PlaceHolder2'' As PlaceHolder2,
  439. ''PlaceHolder3'' As PlaceHolder3,";
  440. $GroupOrder = "
  441. GROUP BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category
  442. ORDER BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category', ";
  443. break;
  444.  
  445. case "weeks":
  446. $SelectRowNames =
  447. "to_char(ipstimestamp, ''YYYYWW'') As row_name,
  448. ''wk'' || to_char(ipstimestamp, ''FMWW YYYY'') As labelled_row_name,
  449. ''PlaceHolder1'' As PlaceHolder1,
  450. ''PlaceHolder2'' As PlaceHolder2,
  451. ''PlaceHolder3'' As PlaceHolder3,";
  452. $GroupOrder = "
  453. GROUP BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category
  454. ORDER BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category', ";
  455. break;
  456.  
  457. case "months":
  458. $SelectRowNames =
  459. "to_char(ipstimestamp, ''YYYYMM'') As row_name,
  460. to_char(ipstimestamp, ''mon YYYY'') As labelled_row_name,
  461. ''PlaceHolder1'' As PlaceHolder1,
  462. ''PlaceHolder2'' As PlaceHolder2,
  463. ''PlaceHolder3'' As PlaceHolder3,";
  464. $GroupOrder = "
  465. GROUP BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category
  466. ORDER BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category', ";
  467. break;
  468.  
  469. case "years":
  470. $SelectRowNames =
  471. "to_char(ipstimestamp, ''YYYY'') As row_name,
  472. to_char(ipstimestamp, ''YYYY'') As labelled_row_name,
  473. ''PlaceHolder1'' As PlaceHolder1,
  474. ''PlaceHolder2'' As PlaceHolder2,
  475. ''PlaceHolder3'' As PlaceHolder3,";
  476. $GroupOrder = "
  477. GROUP BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category
  478. ORDER BY row_name, labelled_row_name, PlaceHolder1, PlaceHolder2, PlaceHolder3, objectid, category', ";
  479. break;
  480. }
  481.  
  482.  
  483.  
  484. // echo "type ".$IpsTable." <br>";
  485. $SensorResult = GetSensorNames($SensorString, $TableType, "", "",0, 1);
  486. // echo "SensorResult: ".$SensorResult.chr(13);
  487. $CategoryString = implode(", ", pg_fetch_all_columns($SensorResult,$SensorColumn));
  488. $CategoryArray = pg_fetch_all_columns(GetSensorNames($SensorString, $TableType, "", "",0, 0),5); //array containing the name of each sensor
  489.  
  490. // echo "<strong>CategoryString: </strong>". $CategoryString.chr(13)."<br><br><br><br>";
  491. //Print_r ($CategoryArray);
  492.  
  493.  
  494. //primary query (non-crosstabbed yet)
  495. $PrimaryQuery ="'SELECT ". $SelectRowNames.
  496. "varid As category, "
  497. .$AggregateFunctions
  498. ." FROM $IpsTable As log
  499. JOIN ipsobjects_with_parent ips
  500. ON log.varid = ips.objectid
  501. WHERE (ips.objectname LIKE ''".$SensorString."'') "
  502. . $WhereClause . "
  503. AND (ipstimestamp > (now()- ''". $NoAbscissaBins . " " . $TimeUnit. "''::interval)) ". $GroupOrder ;
  504.  
  505. // echo "<strong>PrimaryQuery: $PrimaryQuery</strong>";
  506.  
  507. //main crosstab query which pulls all the content data:
  508. $MainCrosstabSelectQuery =
  509. "SELECT * FROM crosstab
  510. (" .$PrimaryQuery .
  511. "'SELECT DISTINCT varid
  512. FROM $IpsTable As log
  513. JOIN ipsobjects_with_parent ips
  514. ON log.varid = ips.objectid
  515. WHERE (ips.objectname LIKE ''".$SensorString."'')
  516. ORDER BY 1;'
  517. )
  518.  
  519. As CountsPerHour(row_name text,
  520. label_row_name text,
  521. PlaceHolder1 text,
  522. PlaceHolder2 text,
  523. PlaceHolder3 text, "
  524. .$CategoryString.")";
  525.  
  526. //echo "<br><font color='red'>$MainCrosstabSelectQuery</font><br>";
  527.  
  528. $QueryResult=pg_query ($dbconn, $MainCrosstabSelectQuery);
  529. $CrosstabArray = pg_fetch_all($QueryResult);
  530. //echo "CrosstabArray: " . $CrosstabArray ;
  531. //$table = good_query_table($CrosstabArray, $QueryResult, 0);
  532. //BetterTable($QueryResult); //this functions outputs a nicely formatted crosstab!
  533.  
  534. if (!$QueryResult)
  535. echo "PG Error: ". pg_last_error().chr(13);
  536.  
  537. // create bracketed Array of Arrays GoogleDataRow
  538. $GoogleDataRow ="["; //opening bracket inserted
  539. while ($field = pg_fetch_row($QueryResult))
  540. {
  541.  
  542. $GoogleDataRow .="[";
  543. $index =0;
  544.  
  545. switch ($TableType)
  546. {
  547. case "boolean":
  548. foreach ($field as $col_value) {
  549. $index++;
  550. if ($index<6)
  551. //the first five columns are strings (date headers)
  552. {$GoogleDataRow .= "'".$col_value."', ";}
  553. else{
  554. //anythign above 5 is data
  555. //substitute NULL with zeroes, else GoogleChart doesnt work
  556. if($col_value == NULL) $col_value = "null";
  557. $GoogleDataRow .= $col_value.", ";
  558. }
  559. }
  560. break;
  561.  
  562. case "integer":
  563. foreach ($field as $col_value) {
  564. ++$index;
  565. if ($index<6)
  566. {//the first give columns are strings (date headers)
  567. $GoogleDataRow .= "'".$col_value."', ";}
  568. else{
  569. //anythign above 5 is data
  570. //substitute NULL with 5x zeroes, else GoogleChart doesnt work
  571. if($col_value == NULL) $col_value = "null,null,null,null,null";
  572. $GoogleDataRow .= $col_value.", ";
  573. }
  574. }
  575. break;
  576.  
  577. case "float":
  578. foreach ($field as $col_value) {
  579. ++$index;
  580. if ($index<6)
  581. {//the first give columns are strings (date headers)
  582. $GoogleDataRow .= "'".$col_value."', ";}
  583. else{
  584. //anythign above 5 is data
  585. //substitute NULL with 5x zeroes, else GoogleChart doesnt work
  586. if($col_value == NULL) $col_value = "null,null,null,null,null";
  587. $GoogleDataRow .= $col_value.", ";
  588. }
  589. }
  590. break;
  591.  
  592. case "string":
  593. foreach ($field as $col_value) {
  594. ++$index;
  595. if ($index<5)
  596. //the first give columns are strings (date headers)
  597. {$GoogleDataRow .= "'".$col_value."', ";}
  598. else{
  599. //anythign above 5 is data
  600. //substitute NULL with zeroes, else GoogleChart doesnt work
  601. if($col_value == NULL) $col_value = 0;
  602. $GoogleDataRow .= $col_value." ";
  603. }
  604. }
  605. break;
  606. }
  607.  
  608. $GoogleDataRow = substr($GoogleDataRow,0,-5)."], ";
  609.  
  610. //echo "<br><font color='blue'>$GoogleDataRow </font><br>";
  611. }
  612.  
  613.  
  614. $GoogleDataRow = substr($GoogleDataRow,0,-2)."]";
  615.  
  616. $DataCollectionArray = array(
  617. "GoogleDataRow"=>$GoogleDataRow,
  618. "NumberOfColumns"=>pg_num_fields($QueryResult), // total number of columns, including all row headers
  619. "CategoryArray"=>$CategoryArray,
  620. "TableType"=>$TableType,
  621. "DataTableName" => $DataTableName,
  622. "MainCrosstabSelectQuery" => $MainCrosstabSelectQuery
  623. );
  624. return $DataCollectionArray;
  625. }
  626.  
  627.  
  628.  
  629. //---------------------------------------
  630.  
  631. function BetterTable($result)
  632. {
  633. $i = 0;
  634. echo "<html>
  635. <body>
  636. <table>
  637. <table class='BetterTable' border='1'>";
  638.  
  639. echo "<tr>";
  640. echo '<td>Line #
  641. </td>';
  642. while ($i < pg_num_fields($result))
  643. {
  644. $fieldName = pg_field_name($result, $i);
  645. echo '<td>' . $fieldName . '</td>';
  646. $i = $i + 1;
  647. }
  648. echo '</tr>';
  649. $i = 0;
  650.  
  651. while ($field = pg_fetch_row($result))
  652. {
  653. if ($i%2 == 0)
  654. Echo "<tr bgcolor="#d0d0d0" >";
  655. else
  656. Echo "<tr bgcolor="#eeeeee">";
  657. $fields = count($field);
  658. $y = 0;
  659. echo '<td>'.$i. '</td>';
  660. while ($y < $fields)
  661. {
  662. $c_row = current($field);
  663. echo '<td>'.$c_row . '</td>';
  664. next($field);
  665. $y = $y + 1;
  666. }
  667. echo '</tr>';
  668. $i = $i + 1;
  669. }
  670.  
  671.  
  672. echo '</table><br><br></body></html>';
  673.  
  674. }
  675.  
  676. //----------------------------------------------------
  677.  
  678. Function GetSensorNames($SensorType, $TableType, $prefix, $postfix, $QueryType, $column)
  679. {//TableType specifies the type of variable, and redirectes to the correct table.
  680. //prefix and postfix are adapters returned for each row
  681. //
  682. //retrieve full names of DataCollection variables, including parent names
  683. //QueryColumns specifies which query columns (see below)
  684.  
  685. $dbconn = pg_connect("host=10.10.10.8 port=5432 dbname=IpsLogging user=*** password=***")
  686. or die ('connection aborted: ' . pg_last_error().chr(13));
  687.  
  688.  
  689. switch ($TableType)
  690. {
  691. case "boolean":
  692. $IpsTable = "loggingdb_ips_boolean";
  693. break;
  694. case "integer":
  695. $IpsTable = "loggingdb_ips_integer";
  696. break;
  697. case "float":
  698. $IpsTable = "loggingdb_ips_float";
  699. break;
  700. case "string":
  701. $IpsTable = "loggingdb_ips_string";
  702. break;
  703. }
  704. // echo "type ".$IpsTable." <br>";
  705.  
  706.  
  707. if ($prefix!="") $prefix = $prefix." || ";
  708. if ($postfix!="")$postfix = " || ". $postfix;
  709.  
  710. $GetSensorNamesString =
  711. "SELECT
  712. DISTINCT '"' || varid ||'"' || ' integer ' as integername,
  713. varid as NakedVariable,
  714. '"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')"' || ' integer ' AS fullname,
  715. '"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')"' || ' numeric(8,2) ' AS fullnamenumeric,
  716. '"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')"' || ' text ' AS fullnamestring,
  717. '"' || ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')"' AS fullnamestring,
  718. ips.parentname || ' - ' || ips.objectname || ' (' || ips.objectid || ')' || ' text ' AS fullnamestringwithoutquotes,
  719. $prefix varid $postfix as DecoratedVariable
  720.  
  721. FROM $IpsTable As log
  722. JOIN ipsobjects_with_parent ips
  723. ON log.varid = ips.objectid
  724. WHERE (ips.objectname LIKE '$SensorType')
  725. ORDER BY 1;
  726. ";
  727. //echo "<strong>$GetSensorNamesString</strong>";
  728.  
  729. $QueryResult=pg_query ($dbconn, $GetSensorNamesString);
  730. if (!$QueryResult)
  731. echo "PG Error: ". pg_last_error().chr(13);
  732. else
  733. {return $QueryResult;}
  734. }
  735. //-----------------------------
  736. Function ColumnLabel($TimeUnit)
  737. {
  738. switch ($TimeUnit)
  739. {
  740. case "minutes": $ColumnLabel= "0"; break;
  741. case "hours": $ColumnLabel= "1"; break;
  742. case "days": $ColumnLabel= "2"; break;
  743. case "months": $ColumnLabel= "3"; break;
  744. case "years": $ColumnLabel= "4"; break;}
  745. return($ColumnLabel);
  746. }
  747. //--------------------------------------------
  748. function VerboseEcho($Anything)
  749. {global $Verbose;
  750. if ($Verbose == true) Echo $Anything;}
  751. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!