Presentation of the chart on page

0

I have this chart with the following query to the database:

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script><scripttype="text/javascript">
      google.charts.load('current', {'packages':['bar']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Ano','Janeiro', 'Fevereiro','Marco', 'Abril','Maio', 'Junho','Julho', 'Agosto','Setembro', 'Outubro','Novembro', 'Dezembro'],
          <?php 
                   $query = "SELECT 'B'.'DescricaoProd',
       YEAR('A'.'Data') AS 'Ano',
       CAST(SUM(IF(MONTH('A'.'Data') = 1 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Janeiro',
       CAST(SUM(IF(MONTH('A'.'Data') = 2 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Fevereiro',
       CAST(SUM(IF(MONTH('A'.'Data') = 3 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Marco',
       CAST(SUM(IF(MONTH('A'.'Data') = 4 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Abril',
       CAST(SUM(IF(MONTH('A'.'Data') = 5 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Maio',
       CAST(SUM(IF(MONTH('A'.'Data') = 6 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Junho',
       CAST(SUM(IF(MONTH('A'.'Data') = 7 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Julho',
       CAST(SUM(IF(MONTH('A'.'Data') = 8 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Agosto',
       CAST(SUM(IF(MONTH('A'.'Data') = 9 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Setembro',
       CAST(SUM(IF(MONTH('A'.'Data') = 10 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Outubro',
       CAST(SUM(IF(MONTH('A'.'Data') = 11 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Novembro',
       CAST(SUM(IF(MONTH('A'.'Data') = 12 , 'A'.'Quantidade', 0)) AS DECIMAL (15 , 0 )) AS 'Dezembro'

FROM(
SELECT 'centrodb'.'regSaidahigieneAntigo'.'Produto',        
       'centrodb'.'regSaidahigieneAntigo'.'DataAtribuicao' AS 'Data',
       'centrodb'.'regSaidahigieneAntigo'.'Quantidade'

FROM 'centrodb'.'regSaidahigieneAntigo'

UNION ALL

SELECT 'A'.'Produto',        
       'A'.'DataAtribuicao' AS 'Data',
       'A'.'Quantidade'

FROM 'centrodb'.'regSaidahigiene' AS 'A' 

UNION ALL

SELECT 'centrodb'.'RegSaidaLuvas'.'Produto',
       'centrodb'.'RegSaidaLuvas'.'DataSaida',
       'centrodb'.'RegSaidaLuvas'.'QtaHigiene'


 FROM 'centrodb'.'RegSaidaLuvas'

 WHERE 'centrodb'.'RegSaidaLuvas'.'Produto' IS NOT NULL) AS 'A' LEFT OUTER JOIN 'centrodb'.'ProdHigiene' AS 'B'
ON 'B'.'IDProd' = 'A'.'Produto'

WHERE YEAR('A'.'Data') = EXTRACT(YEAR FROM CURDATE() - INTERVAL 0 YEAR)
      OR
      YEAR('A'.'Data') = EXTRACT(YEAR FROM CURDATE() - INTERVAL 1 YEAR)

GROUP BY 'B'.'DescricaoProd',  'Ano'

ORDER BY 'B'.'DescricaoProd', YEAR('A'.'Data')";    
                   $exec = mysqli_query($conn,$query);

                   while($row = mysqli_fetch_array($exec)){    
                      echo "['".$row['Ano']."',".$row['Janeiro'].",".$row['Fevereiro'].",".$row['Marco'].",".$row['Abril'].",".$row['Maio'].",".$row['Junho'].",".$row['Julho'].",".$row['Agosto'].",".$row['Setembro'].",".$row['Outubro'].",".$row['Novembro'].",".$row['Dezembro']."],";
                   }
                ?>     
        ]);

        var options = {
          chart: {
            title: 'Company Performance',
            subtitle: 'Sales, Expenses, and Profit: 2014-2017',
          }
        };

        var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

        chart.draw(data, google.charts.Bar.convertOptions(options));
      }
    </script>
  </head>
  <body>
    <div id="columnchart_material" style="width: 2000px; height: 1000px;"></div>
  </body>
</html>

It's all working correctly with the code, I'm not able to present the data as I want. In the images I show how the results appear on the graph in two ways:

The first image shows the year and month but does not see the name of the product to which that quantity refers. The second image shows the product and the month, but does not appear the year to which it refers, since it shows the result of the current year and the previous year.

What I want to show in the chart is the product name, the total spent per month, and to what year and month that total belongs.

    
asked by anonymous 15.03.2018 / 16:24

1 answer

1

Where you name the columns, I think it looks better with the products name

var data = google.visualization.arrayToDataTable([
          ['DescricaoProd','Janeiro', 'Fevereiro','Marco', 'Abril','Maio', 'Junho','Julho', 'Agosto','Setembro', 'Outubro','Novembro', 'Dezembro'],

And in the loop where you print, I could not make it possible to "Skip line" so I put the year aside, I do not know if it suits you perfectly.

 while($row = mysqli_fetch_array($exec)){    
                      echo "['".$row['DescricaoProd']." - ".$row['Ano']."',".$row['Janeiro'].",".$row['Fevereiro'].",".$row['Marco'].",".$row['Abril'].",".$row['Maio'].",".$row['Junho'].",".$row['Julho'].",".$row['Agosto'].",".$row['Setembro'].",".$row['Outubro'].",".$row['Novembro'].",".$row['Dezembro']."],";
                   }

If this does not suit you and you want the information "independent", then I suggest you use another graphic

Resp: An example filter would be to make the user before accessing the chart, choosing a product, such as Alcohol, it goes to a ($produto) variable, and when its graph is loaded , Select quit like this:

$Query = "SELECT * FROM BANCOX.TABELAX WHERE DescricaoProd = '$produto'";

Only one generic example: D

    
15.03.2018 / 18:16