Add Chart in Dashboard

Development Discussion
1

Hi Guys,

Could you please help me, I’ve write the code below which I want to integrate somehow in dashboard or to add link to menu for example Charts. This chart show monthly (1…,12) and Revenue for current year.

<html>
<head>
    <title>Company Performance</title>
</head>
<body>
<?php

$username = "user";
$password = "pass";
$hostname = "localhost";
$dbname = "invplanedb";

//connect to database
$dbhandle = mysqli_connect($hostname, $username, $password, $dbname);

         if(! $dbhandle ) {
            die('Could not connect: ' . mysqli_error());
         }
//         echo 'Connected successfully<br>';

//execute query
$sql = "SELECT MONTH(t2.invoice_date_created) as MONTH,sum(invoice_total) as TOTAL from ip_invoice_amounts t1 LEFT JOIN ip_invoices t2 ON t2.invoice_id = t1.invoice_id where invoice_paid <> '00.00' and MONTH(t2.invoice_date_created) IN (1,2,3,4,5,6,7,8,9,10,11,12) AND YEAR(t2.invoice_date_created) IN (SELECT YEAR(CURRENT_DATE)) GROUP BY YEAR(t2.invoice_date_created), MONTH(t2.invoice_date_created)";

$result = mysqli_query($dbhandle, $sql);

         if (mysqli_num_rows($result) > 0) {
            while($row = mysqli_fetch_assoc($result)) {
            $entry .= "['".$row['MONTH']."',".$row['TOTAL']."],";
           } 
         } else {
            echo "0 results";
         }

 
//close the connection
mysqli_close($dbhandle);

?>

<div id="chart_div" style="width: 100%; height: 500px;"></div>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

    function drawChart() {
        var data = google.visualization.arrayToDataTable([
        ['MONTH', 'Monthly turnover'],
        <?php echo $entry; ?>
    ]);
        var options = {
            title: 'Company Performance',
            curveType: 'function',
            legend: { position: 'bottom' }
        };
      var options = {
        hAxis: {
          title: 'Monts'
        },
        vAxis: {
          title: 'Total'
        }
      };
        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
    }
</script>

<?php

$username = "user";
$password = "pass";
$servername = "localhost";
$dbn = "invplanedb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbn);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
//echo "Connected successfully";

//execute query
$sql = "SELECT sum(invoice_total) as REVENUE from ip_invoice_amounts t1 LEFT JOIN ip_invoices t2 ON t2.invoice_id = t1.invoice_id where invoice_paid <> '00.00'";

$obrt = $conn->query($sql);

if ($obrt->num_rows > 0) {

 // output data of each row
    while($row = $obrt->fetch_assoc()) {
        echo "REVENUE:  " . $row["REVENUE"]. " BGN <br>";
    }
} else {
    echo "0 results";
}
$conn->close();


?>

</body>
</html>

Thanks in advance.
BR,
Tsvetomir