index.php

   1 <html>
   2 <head>
   3 <title>Bliep</title>
   4 <link rel="stylesheet" type="text/css" href="s.css" />
   5 <script src="js/canvastext.js"></script>
   6 <script src="js/gnuplot_common.js"></script>
   7 <script src="js/gnuplot_mouse.js"></script>
   8 </head>
   9 <body>
  10 <canvas id="bliep" width=900 height=600>
  11 <div id="err_msg">No support for HTML 5 canvas element</div>
  12 </canvas>
  13 <?php
  14 error_reporting(E_ALL);
  15 ini_set('display_errors', '1');
  16
  17 //FIXME
  18 $conn = mysqli_connect("localhost", "USER", "PASSWORD", "bliep");
  19 if (!$conn) {
  20         die(sprintf("Connect error: %s\n", mysqli_connect_error()));
  21 }
  22
  23 $descriptorspec = array(
  24    0 => array("pipe", "r"),
  25    1 => array("pipe", "w"),
  26    2 => array("pipe", "w")
  27 );
  28 $process = proc_open('gnuplot', $descriptorspec, $pipes);
  29 $tmpfile = tempnam("/tmp", "bliep");
  30 $tmphandle = fopen($tmpfile, "w");
  31 $gnuplot =<<<GNUPLOT
  32 set term canvas size 900,600 name 'bliep'
  33 set xlabel "Weeknumber"
  34 set ylabel "Count"
  35 set xrange [-0.5:*]
  36 set xtics 1
  37 set ytics 0.25
  38 set style data histograms
  39 set style histogram rowstacked
  40 set boxwidth 1 relative
  41 set style fill solid 1.0 border -1
  42 set title "Telefoon gebruik"
  43
  44 GNUPLOT;
  45 fwrite($pipes[0], $gnuplot);
  46 $r = mysqli_query($conn, "
  47 SELECT WEEK(date), amount, description
  48 FROM data
  49 WHERE YEAR(date)=2016
  50 ");
  51 if($r === FALSE){
  52         die(sprintf("Query error: %s\n", mysqli_error($conn)));
  53 }
  54 $data = array();
  55 while($row = mysqli_fetch_array($r)){
  56         if(!array_key_exists($row[0], $data)){
  57                 $data[$row[0]] = array(
  58                         "sms" => 0.0,
  59                         "bel" => 0.0,
  60                         "int" => 0.0,
  61                         "opw" => 0.0
  62                 );
  63         }
  64         $row[1] = floatval(intval($row[1])*0.01);
  65         if(strpos($row[2], "sms") !== FALSE){
  66                 $data[$row[0]]["sms"] += -$row[1];
  67         } else if (strpos($row[2], "Bellen") !== FALSE){
  68                 preg_match_all('!\d+!', $row[2], $matches);
  69                 $nulnegen = strstr("0900", $matches[0][0]);
  70                 if($nulnegen === FALSE || $nulnegen != 0){
  71                         if($matches[0][0][0] === "0"){
  72                                 $data[$row[0]]["bel"] += -$row[1];
  73                         }
  74                 }
  75         } else if (strpos($row[2], "pwaardering") !== FALSE){
  76                 $data[$row[0]]["opw"] += $row[1];
  77         } else {
  78                 $data[$row[0]]["int"] += -$row[1];
  79         }
  80 }
  81 foreach($data as $key => $row){
  82         fwrite($tmphandle, sprintf("%d\t%.2f\t%.2f\t%.2f\n",
  83                 intval($key), $row["int"], $row["bel"], $row["sms"]));
  84 }
  85 fclose($tmphandle);
  86 fwrite($pipes[0], sprintf("
  87 plot '%s' using 2 t 'internet',\
  88 '' using 3 t 'bellen',\
  89 '' using 4 t 'sms'\
  90 \n", $tmpfile));
  91 fwrite($pipes[0], "quit\n");
  92 fclose($pipes[0]);
  93 $javascript = stream_get_contents($pipes[1]);
  94 fclose($pipes[1]);
  95 echo "err: " . stream_get_contents($pipes[2]);
  96 fclose($pipes[2]);
  97 $return_value = proc_close($process);
  98 unlink($tmpfile);
  99
 100 $r = mysqli_query($conn, "
 101 SELECT amount, date, description
 102 FROM data
 103 ORDER BY date DESC LIMIT 25");
 104 if($r === FALSE){
 105         die(sprintf("Query error: %s\n", mysqli_error($conn)));
 106 }
 107 echo "\n<table>\n";
 108 echo "<tr><td>Date</td><td>Amount</td><td>Description</td></tr>\n";
 109 while($row = mysqli_fetch_array($r)){
 110         printf("<tr><td>%s</td><td>&euro;%.2f</td><td>%s</td></tr>\n",
 111                 $row[1], intval($row[0])*0.01, $row[2]);
 112 }
 113 echo "</table>\n";
 114 mysqli_close($conn);
 115 echo "<script>$javascript</script>";
 116 ?>
 117 <script>
 118 bliep()
 119 </script>
 120 </body>
 121 </html>