API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 17th, 2019
167
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.48 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2.  
  3. //fill the dropdown list
  4. $queryDropdown = "SELECT DISTINCT mac FROM location";
  5. $resultDropdown = filterFunction($queryDropdown);
  6. // if not set then filter the table with default values using this mac address
  7. $query = "SELECT mac,hostid,interface FROM location where mac='C8:50:E9:8C:E2:52' ORDER BY timestamp DESC";
  8. $result = filterFunction($query);
  9.  
  10. $variablesQuery = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='C8:50:E9:8C:E2:52' and datetime BETWEEN '2019-09-20' AND '2019-09-21'";
  11. $resultVariables = filterFunction($variablesQuery);
  12.  
  13. $variablesQuerySecondTable = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='C8:50:E9:8C:E2:52' and datetime BETWEEN '2019-09-20' AND '2019-09-21'";
  14. $resultVariablesSecondTable = filterFunction($variablesQuerySecondTable);
  15.  
  16. $variablesQueryBody = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='C8:50:E9:8C:E2:52' and datetime BETWEEN '2019-09-20' AND '2019-09-21'";
  17. $resultVariablesBody = filterFunction($variablesQueryBody);
  18.  
  19. // SQL STATEMENTS FOR THE VARIABLES THAT INCLUDE AVG MIN MAX etc.
  20. $uptimeSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Uptime' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  21. $resultUptimeSql = filterFunction($uptimeSql);
  22. $rowUptime = mysqli_fetch_row($resultUptimeSql);
  23.  
  24. $distanceSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Distance' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  25. $resultDistanceSql = filterFunction($distanceSql);
  26. $rowDistance = mysqli_fetch_row($resultDistanceSql);
  27.  
  28. $supplyVoltageAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Supply Voltage' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  29. $resultSupplyVoltageAvg = filterFunction($supplyVoltageAvg);
  30. $rowSupplyVoltage = mysqli_fetch_row($resultSupplyVoltageAvg);
  31.  
  32. $rxPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'RX Power' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  33. $resultRXPowerAvg = filterFunction($rxPowerAvg);
  34. $rowRXPower = mysqli_fetch_row($resultRXPowerAvg);
  35.  
  36. $temperatureAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Temperature' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  37. $resultTemperature = filterFunction($temperatureAvg);
  38. $rowTemperature = mysqli_fetch_row($resultTemperature);
  39.  
  40. $txPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'TX Power' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  41. $resultTXPowerAvg = filterFunction($txPowerAvg);
  42. $rowTXPower = mysqli_fetch_row($resultTXPowerAvg);
  43.  
  44. $txBiasCurrent = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'TX Bias Current' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  45. $resultTXBiasCurrent = filterFunction($txBiasCurrent);
  46. $rowTxBiasCurrent = mysqli_fetch_row($resultTXBiasCurrent);
  47.  
  48. $trafficUploadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Traffic_Upload' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  49. $resultTrafficUpload = filterFunction($trafficUploadSUM);
  50. $rowTrafficUpload = mysqli_fetch_row($resultTrafficUpload);
  51.  
  52. $trafficDownloadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Traffic_Download' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  53. $resultTrafficDownload = filterFunction($trafficDownloadSUM);
  54. $rowTrafficDownload = mysqli_fetch_row($resultTrafficDownload);
  55.  
  56. $UploadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Upload_Speed' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  57. $resultUploadSpeed = filterFunction($UploadSpeed);
  58. $rowUploadSpeed = mysqli_fetch_row($resultUploadSpeed);
  59.  
  60. $DownloadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Download Speed' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
  61. $resultDownloadSpeed = filterFunction($DownloadSpeed);
  62. $rowDownloadSpeed = mysqli_fetch_row($resultDownloadSpeed);
  63.  
  64.  
  65. if(isset($_GET['submit'])){
  66.  
  67. if(isset($_GET['dropDown'])){
  68. $selected_Val = $_GET['dropDown'];
  69. $from = $_GET['from'];
  70. $to=$_GET['to'];
  71.  
  72.  
  73.  
  74.  
  75. // make a SQL Query with the value from the dropDown list, this lists the mac addresses
  76. $query = "SELECT mac,hostid,interface FROM location where mac='".$selected_Val."' ORDER BY timestamp DESC";
  77. $result = filterFunction($query);
  78. // after finishing with the first query, fill the dropdown
  79. $queryDropdown = "SELECT DISTINCT mac FROM location";
  80. $resultDropdown = filterFunction($queryDropdown);
  81. // selecting the distinct variables from the client_item table
  82. $variablesQuery = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='".$selected_Val."' and datetime BETWEEN '".$from."' AND '".$to."' "; //".$selected_Val."
  83. $resultVariables = filterFunction($variablesQuery);
  84.  
  85. $variablesQuerySecondTable = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='".$selected_Val."' and datetime BETWEEN '".$from."' AND '".$to."'";
  86. $resultVariablesSecondTable = filterFunction($variablesQuerySecondTable);
  87.  
  88. $variablesQueryBody = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='".$selected_Val."' and datetime BETWEEN '".$from."' AND '".$to."'";
  89. $resultVariablesBody = filterFunction($variablesQueryBody);
  90.  
  91. // SQL STATEMENTS FOR THE VARIABLES THAT INCLUDE AVG MIN MAX etc.
  92. $uptimeSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Uptime' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  93. $resultUptimeSql = filterFunction($uptimeSql);
  94. $rowUptime = mysqli_fetch_row($resultUptimeSql);
  95.  
  96. $distanceSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Distance' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  97. $resultDistanceSql = filterFunction($distanceSql);
  98. $rowDistance = mysqli_fetch_row($resultDistanceSql);
  99.  
  100. $supplyVoltageAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Supply Voltage' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  101. $resultSupplyVoltageAvg = filterFunction($supplyVoltageAvg);
  102. $rowSupplyVoltage = mysqli_fetch_row($resultSupplyVoltageAvg);
  103.  
  104. $rxPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'RX Power' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  105. $resultRXPowerAvg = filterFunction($rxPowerAvg);
  106. $rowRXPower = mysqli_fetch_row($resultRXPowerAvg);
  107.  
  108. $temperatureAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Temperature' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  109. $resultTemperature = filterFunction($temperatureAvg);
  110. $rowTemperature = mysqli_fetch_row($resultTemperature);
  111.  
  112. $txPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'TX Power' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  113. $resultTXPowerAvg = filterFunction($txPowerAvg);
  114. $rowTXPower = mysqli_fetch_row($resultTXPowerAvg);
  115.  
  116. $txBiasCurrent = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'TX Bias Current' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  117. $resultTXBiasCurrent = filterFunction($txBiasCurrent);
  118. $rowTxBiasCurrent = mysqli_fetch_row($resultTXBiasCurrent);
  119.  
  120. $trafficUploadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Traffic_Upload' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  121. $resultTrafficUpload = filterFunction($trafficUploadSUM);
  122. $rowTrafficUpload = mysqli_fetch_row($resultTrafficUpload);
  123.  
  124. $trafficDownloadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Traffic_Download' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  125. $resultTrafficDownload = filterFunction($trafficDownloadSUM);
  126. $rowTrafficDownload = mysqli_fetch_row($resultTrafficDownload);
  127.  
  128. $UploadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Upload_Speed' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  129. $resultUploadSpeed = filterFunction($UploadSpeed);
  130. $rowUploadSpeed = mysqli_fetch_row($resultUploadSpeed);
  131.  
  132. $DownloadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Download Speed' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
  133. $resultDownloadSpeed = filterFunction($DownloadSpeed);
  134. $rowDownloadSpeed = mysqli_fetch_row($resultDownloadSpeed);
  135.  
  136.  
  137. }else{
  138. echo "<div class=\"alert alert-danger alert-dismissible fade show\">
  139. <strong>Error!</strong> Please select a mac address.
  140. <button type=\"button\" class=\"close\" data-dismiss=\"alert\">&times;</button>
  141. </div>";
  142. }
  143.  
  144.  
  145. }
  146. function filterFunction($query){
  147. $connect = mysqli_connect("localhost","root","","praksa");
  148. $filter_query = mysqli_query($connect,$query);
  149. return $filter_query;
  150. }
  151. function dateDiff ($d1, $d2) {
  152.  
  153. // Return the number of days between the two dates:
  154. return round(abs(strtotime($d1) - strtotime($d2))/86400);
  155.  
  156. } // end function dateDiff
  157. ?>
  158. <html>
  159. <head>
  160. <title>Zabbix</title>
  161.  
  162. <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  163. <link rel="stylesheet" href="style.css">
  164. <script
  165. src="https://code.jquery.com/jquery-3.4.1.min.js"
  166. integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="
  167. crossorigin="anonymous"></script>
  168. <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js"></script>
  169.  
  170. </head>
  171. <body>
  172. <div class="container">
  173. <form class="form-inline" method="GET" action="index.php">
  174. <label class="my-1 mr-2" for="inlineFormCustomSelectPref">Preference</label>
  175. <select class="custom-select my-1 mr-sm-2" name="dropDown" id="inlineFormCustomSelectPref">
  176. <option selected disabled>Choose...</option>
  177. <?php while ($row = mysqli_fetch_array($resultDropdown)): ?>
  178. <option value="<?php echo $row['mac']?>"><?php echo $row['mac']?></option>
  179. <?php endwhile; ?>
  180. </select>
  181. <div class="form-group mr-2">
  182. <label for="from">From date:</label>
  183. <input type="date" class="form-control" id="from" name="from" required/>
  184. <label for="to">To date:</label>
  185. <input type="date" class="form-control" id="to" name="to" required/>
  186. </div>
  187. <button type="submit" name="submit" class="btn btn-primary my-1">Submit</button>
  188. </form>
  189. <div class="row">
  190. <div class="col-lg-4">
  191. <table>
  192. <thead>
  193. <tr>
  194. <td>Mac address</td>
  195. <td>Host</td>
  196. <td>Interface</td>
  197. </tr>
  198. </thead>
  199. <tbody>
  200. <?php while ($row = mysqli_fetch_array($result)): ?>
  201. <tr>
  202. <td><?php echo $row['mac']?></td>
  203. <td><?php echo $row['hostid']?></td>
  204. <td><?php echo $row['interface']?></td>
  205. </tr>
  206. <?php endwhile; ?>
  207. </tbody>
  208. </table>
  209. </div>
  210. <div class="col-lg-8">
  211. <table style="width: 500px;height: 40px">
  212. <thead>
  213. <tr>
  214. <?php while($row = mysqli_fetch_array($resultVariablesSecondTable)): ?>
  215. <td><?php echo $row['variable']; ?></td>
  216. <?php endwhile ; ?>
  217. </tr>
  218. </thead>
  219. <tbody>
  220. <tr>
  221. <?php while($row2 = mysqli_fetch_array($resultVariablesBody)):?>
  222. <?php if($row2['variable']=="Supply Voltage") echo "<td>" . "Supply Voltage " .round($rowSupplyVoltage[1],4)."</td>"; ?>
  223. <?php if($row2['variable']=="Distance") echo "<td>" . " Distance ".$rowDistance[1]."</td>"; ?>
  224. <?php if($row2['variable']=="RX Power") echo "<td>" . " RX Power ".round($rowRXPower[1],4)."</td>"; ?>
  225. <?php if($row2['variable']=="Temperature") echo "<td>" ." Temperature ".round($rowTemperature[1],4)."</td>"; ?>
  226. <?php if($row2['variable']=="TX Bias Current") echo "<td>"." TX Bias Current ".round($rowTxBiasCurrent[1],4)."</td>"; ?>
  227. <?php if($row2['variable']=="TX Power") echo "<td>"."TX Power ".round($rowTXPower[1],4)."</td>"; ?>
  228. <?php if($row2['variable']=="Uptime") echo "<td>"." Uptime ".$rowUptime[1]."</td>"; ?>
  229. <?php if($row2['variable']=="Traffic_Upload") echo "<td>"." Traffic Upload ".$rowTrafficUpload[1]."</td>"; ?>
  230. <?php if($row2['variable']=="Traffic_Download") echo "<td>"." Traffic Download ".$rowTrafficDownload[1]."</td>"; ?>
  231. <?php if($row2['variable']=="Upload_Speed") echo "<td>"." Upload Speed ".round($rowUploadSpeed[1],4)."</td>"; ?>
  232. <?php if($row2['variable']=="Download Speed") echo "<td>"." Download Speed ".round($rowDownloadSpeed[1],4)."</td>"; ?>
  233. <?php endwhile; ?>
  234. </tr>
  235. </tbody>
  236. </table>
  237. </div>
  238. </div>
  239. <?php
  240.  
  241. if(mysqli_num_rows($resultVariables)!=0){
  242. if(isset($_GET['dropDown'])){
  243. $mac = $_GET['dropDown'];
  244. $fromDate = $_GET['from'];
  245. $toDate = $_GET['to'];
  246. $printDays = dateDiff($from,$to);
  247. }else{
  248. $mac = 'C8:50:E9:8C:E2:52';
  249. $fromDate = '2019-09-20';
  250. $toDate='2019-09-21';
  251. $printDays = dateDiff($fromDate,$toDate);
  252. }
  253. $dataValues = [];
  254. $datetimeValues = [];
  255. ?>
  256. <?php while ($row = mysqli_fetch_array($resultVariables)): ?>
  257.  
  258. <?php
  259.  
  260. $sql = $printDays<5 ? "SELECT itemid,value,datetime FROM client_item where mac='".$mac."' AND itemid= " . $row['itemid'] . " and datetime BETWEEN '".$fromDate."' AND '".$toDate."' ":
  261. "SELECT itemid,AVG(`value`) as value,datetime FROM `client_item` WHERE mac='".$mac."' AND itemid= " . $row['itemid'] . " and datetime BETWEEN '".$fromDate."' AND '".$toDate."' GROUP BY DATE(datetime)";
  262. $resultStatement = filterFunction($sql);
  263. $dataValues = [];
  264. $datetimeValues = [];
  265. while($row1 = mysqli_fetch_array($resultStatement)) {
  266.  
  267. $dataValues[] = $row1['value'];
  268. $datetimeValues[] = $row1['datetime'];
  269.  
  270. } ;
  271. $dataValues = json_encode($dataValues);
  272. $datetimeValues = json_encode($datetimeValues);
  273. ?>
  274. <div class="mt-5">
  275. <h3><?php echo $row['variable'] ?></h3>
  276. <canvas id="myChart-<?php echo $row['itemid']?>"
  277. </div>
  278.  
  279.  
  280. </div>
  281. <script>
  282. $(document).ready(function () {
  283. var ctx = document.getElementById("myChart-<?php echo $row['itemid'] ?>");
  284.  
  285. var data = {
  286. labels:<?php echo $datetimeValues ?> ,
  287. datasets:[{
  288. label:"<?php echo $row['variable'] ?>",
  289. fill:false,
  290. data:<?php echo $dataValues ?>,
  291. backgroundColor:"#455C73"
  292. }],
  293.  
  294. };
  295. var myChart = new Chart(ctx,{
  296. data:data,
  297. type:'line'
  298. })
  299. })
  300. </script>
  301.  
  302. <?php endwhile; ?>
  303. <?php }
  304. else {
  305. echo "<div class=\"alert alert-warning\" role=\"alert\">
  306. No data was available for this mac address
  307. </div>";
  308. }
  309. ?>
  310.  
  311. </body>
  312. </html>
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!