phpLicenseWatcher/graph_data.php at master · dcpc007/phpLicenseWatcher · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
<?php

require_once("./common.php");
require_once('./tools.php');
require_once('./config.php');


require_once("DB.php");


$feature = preg_replace("/[^a-zA-Z0-9_|]+/", "", htmlspecialchars($_GET['feature'])) ;
$days = intval($_GET['days']);

$crit = "";
if( $feature == 'all' ){
    $crit = " 1 = 1 ";
}else if( $feature != '' ){

    $features = array() ;
    foreach(explode('|', $feature ) as $i ){
        $features[] = "'".$i."'";
    }

    $crit = " flmusage_product IN ( " . implode(',',$features) . " )  ";

}else{
    $crit = " showInLists = 1 ";
}


if( !$days > 0 ){
    $days= 7;
}

################################################################
#  Connect to the database
#   Use persistent connections
################################################################
$db = DB::connect($dsn, true);

if (DB::isError($db)) {
  die ($db->getMessage());
}

    $result = array( "cols"=>array() , "rows"=>array() );

    $result["cols"][] = array("id"=>"","label"=>"Date","pattern"=>"","type"=>"string");

    $table = array();
    $products = array();

    $sql = "SELECT flmusage_product , CONCAT( flmusage_date , ' ' , flmusage_time ) as flmusage_datetime , SUM(flmusage_users)
FROM license_usage u LEFT JOIN feature f ON f.feature = u.`flmusage_product` WHERE $crit  AND
     DATE_SUB(NOW(),INTERVAL $days DAY ) <= DATE(flmusage_date)
GROUP BY flmusage_product, flmusage_datetime ORDER BY flmusage_datetime ASC";

    $recordset = $db->query($sql);

    if (DB::isError($recordset)) {
        die ($recordset->getMessage());
    }

    while ($row = $recordset->fetchRow()){

        $date = $row[1];

        if( $days == 1 ){
            $date =  date('H:i',strtotime($date));
        }else if( $days <= 7 ){
            $date =  date('Y-m-d H',strtotime($date));
        }else{
             $date =  date('Y-m-d',strtotime($date));
        }

        if( !array_key_exists($row[0], $products)){
            $products[$row[0]] = $row[0];
        }

        if( !array_key_exists($date, $table)){
            $table[$date] = array();
        }

        //[date][product] = value
        if( isset($table[$date][$row[0]]) ){
			//make sure to select the largest value if we are reducing the data by changing the date key
            if( $row[2] > $table[$date][$row[0]] ){
                $table[$date][$row[0]] = $row[2];
            }
        }else{
            $table[$date][$row[0]] = $row[2];
        }


    }


    $recordset->free();

$db->disconnect();


foreach( array_keys($products) as $product ){
    $result["cols"][] = array("id"=>"","label"=>$product,"pattern"=>"","type"=>"number");
}

foreach( array_keys($table) as $date ){
    $ta = array();

    $ta[] =  array('v'=>$date);

    foreach( array_keys($products) as $product ){
        $ta[] =  array('v'=>$table[$date][$product]);
    }


    $result['rows'][] = array('c' => $ta );
}

header('Content-Type: application/json');
echo json_encode( $result);

?>