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

2

What help do you need exactly?

I think your next step is to look for a suitable chart library. Your final solution will depend on how the data must be fed into the library.

3

I have no clue, how to put information which my code provide into invoiceplane dashboard or to create link in menu which will redirect to my chart php file. But also if you advise, can be done by another way.

4

I can only advise on the Invoice Plane way to do it.

Look at the file application/modules/reports/controllers/Reports.php

There is a function sales_by_year() which is close to what you want. This will give you a good idea on how to fetch the data.

I suggest you copy the code and change it to now print to PDF but instead on the screen,
You can then change the viewsales_by_year.php file to add the chart.

What I said in my first response still applies. You need to find a suitable graph library to work with. Without having done this it is very difficult to assist.

5

Thanks for reply and advise, will try.