Merge query returns to be populated a combochart

Asked

Viewed 92 times

0

I have 2 appointments in the bank:

One returns the goals of the day until the last day of the month:

DIA META
1   3,23
2   6,45
3   9,68
4   12,9
5   16,13
6   19,35
7   22,58
8   25,81
9   29,03
10  32,26
11  35,48
12  38,71
13  41,94
14  45,16
15  48,39
16  51,61
17  54,84
18  58,06
19  61,29
20  64,52
21  67,74
22  70,97
23  74,19
24  77,42
25  80,65
26  83,87
27  87,1
28  90,32
29  93,55
30  96,77
31  100

The other shows the values achieved by the day of the consultation:

DIA REALIZADO
1   4,35
2   8,69
3   13,12
4   17,05
5   21,39
6   25,65
7   30,12
8   34,49
9   38,85

How can I unite them to popular the Chart combo as illustrated in the image below:

Being the first in the row as the goal and the second in the columns:

inserir a descrição da imagem aqui

I am using the following Chart: combochart

I tried to put into an array only the other arrays more without success:

Array
(
    [dados] => Array
        (
            [realizado] => Array
                (
                    [0] => 4,35
                    [1] => 8,69
                    [2] => 13,12
                    [3] => 17,05
                    [4] => 21,39
                    [5] => 25,65
                    [6] => 30,12
                    [7] => 34,49
                    [8] => 38,85
                )

            [dias] => Array
                (
                    [0] => 1
                    [1] => 2
                    [2] => 3
                    [3] => 4
                    [4] => 5
                    [5] => 6
                    [6] => 7
                    [7] => 8
                    [8] => 9
                    [9] => 10
                    [10] => 11
                    [11] => 12
                    [12] => 13
                    [13] => 14
                    [14] => 15
                    [15] => 16
                    [16] => 17
                    [17] => 18
                    [18] => 19
                    [19] => 20
                    [20] => 21
                    [21] => 22
                    [22] => 23
                    [23] => 24
                    [24] => 25
                    [25] => 26
                    [26] => 27
                    [27] => 28
                    [28] => 29
                    [29] => 30
                    [30] => 31
                )

            [meta] => Array
                (
                    [0] => 3,23
                    [1] => 6,45
                    [2] => 9,68
                    [3] => 12,90
                    [4] => 16,13
                    [5] => 19,35
                    [6] => 22,58
                    [7] => 25,81
                    [8] => 29,03
                    [9] => 32,26
                    [10] => 35,48
                    [11] => 38,71
                    [12] => 41,94
                    [13] => 45,16
                    [14] => 48,39
                    [15] => 51,61
                    [16] => 54,84
                    [17] => 58,06
                    [18] => 61,29
                    [19] => 64,52
                    [20] => 67,74
                    [21] => 70,97
                    [22] => 74,19
                    [23] => 77,42
                    [24] => 80,65
                    [25] => 83,87
                    [26] => 87,10
                    [27] => 90,32
                    [28] => 93,55
                    [29] => 96,77
                    [30] => 100,00
                )

        )

)

Follow the consultations:

include 'conexao.php';
date_default_timezone_set('America/Sao_Paulo');
$hoje = date('d');

$loja = '5';
$mes = '3';
$ano = '2018';
$acumReal = '';
$acumMeta = '';
$data        = array();

$consultaRealizadoLoja = "SELECT *,
DAY(rl_data) AS DIA,
MONTH(rl_data) AS MES 
FROM realizado_loja
WHERE rl_loja = '$loja'
AND MONTH(rl_data) = '$mes'";

$consultaMetaMes = "SELECT 
saz_loja_mes AS meta_mes 
FROM 
vi_sazo_energia_loja 
WHERE
loja = '$loja'
AND mes = '$mes'";

$consultaMetaDia = "SELECT 
saz_loja_dia AS meta_dia 
FROM 
vi_sazo_energia_loja 
WHERE
loja = '$loja'
AND mes = '$mes'";

$consultaDiasMes = "SELECT 
dias AS d 
FROM 
vi_sazo_anual
WHERE mes = '$mes'";

$resultRealizadoLoja = mysqli_query($conn, $consultaRealizadoLoja);
$resultMetaMes       = mysqli_query($conn, $consultaMetaMes);
$resultMetaDia           = mysqli_query($conn, $consultaMetaDia);
$resultDiasMes       = mysqli_query($conn, $consultaDiasMes);

while($rowMetaMes = mysqli_fetch_assoc($resultMetaMes)) {
    $metaMes = $rowMetaMes['meta_mes']."<br>";
}
while($rowMetaDia = mysqli_fetch_assoc($resultMetaDia)) {
    $metaDiaria = $rowMetaDia['meta_dia']."<br>";
}
while($rowDiasMes = mysqli_fetch_assoc($resultDiasMes)) {
    $diasMes = $rowDiasMes['d']."<br>";
}

// FAZ O ACUMULADO DO REALIZADO
while($rowRealizadoLoja = mysqli_fetch_assoc($resultRealizadoLoja)) {
    $dia = $rowRealizadoLoja['DIA'];
    $retorno = $rowRealizadoLoja['rl_valor'];
    $acumReal = $retorno + $acumReal ;
    $calculo = ( $acumReal / $metaMes * 100 );
    $realizadoFinal =  number_format($calculo,2,",",".");

    $data['dados']['realizado'][] = $realizadoFinal;
}

//FAZ ACULADO DA META
for ($i=1; $i <= $diasMes  ; $i++) { 
    $acumMeta = $metaDiaria + $acumMeta ;
    $calculo2 = ( $acumMeta / $metaMes * 100 );
    $acumuladoMeta =  number_format($calculo2,2,",",".");

    $data['dados']['dias'][] = $i;
    $data['dados']['meta'][] = $acumuladoMeta;
}

The chart needs to return exactly this way: https://jsfiddle.net/j0x8wzaf/4/

php javascript html5 mysqli google-charts

2 answers

1


     $array_dados = $data;
$exibir_chart = '';
    for( $x=0; $x < count($array_dados['dados']['dias']); $x++ ){
    $exibir_chart .= '[\'' . $array_dados['dados']['dias'][$x] . '\', ' . str_replace(',', '.', $array_dados['dados']['meta'][$x]) . ',  ' . (isset($array_dados['dados']['realizado'][$x])?str_replace(',', '.', $array_dados['dados']['realizado'][$x]):'0.00' ) . '],' . PHP_EOL;

    }
    $exibir_chart = substr(trim($exibir_chart), 0, -1);
    // echo $exibir_chart;

  • To format select the code and press CTRL+K, use snippet for codes only HTML / Javascript. Take advantage and explain your answer!

  • https://pastebin.com/E7h7P9G8

  • I put a loop to take the days of the array and from it generate the data of every day. str_replace to change the commas to points. And the isset to check if you have the die, if you don’t have it puts as 0.00 . What I posted should be posted after his code and echo should be placed in the javascript that should be inline.

  • Put in the answer! Do Tour to learn how the community, read the topics How do I write a good answer?, Help with Markdown editing.

0

If I understand your problem correctly, I think this should solve.

Assuming that the day vector will always be the largest of all three vectors, you can try something like this

for ($i = 0; $i < count($dias); $i++) {
    $result[] =[
        isset($dias[$i]) ?? $dias[$i]: 0,
        isset($meta[$i]) ? $meta[$i]: 0,
        isset($realizado[$i]) ? $realizado[$i]: 0
    ];
}

Or you can use PHP 7.x+ null coalescence operator and do something like this

for ($i = 0; $i < count($dias); $i++) {
    $result[] =[$dias[$i] ?? 0, $meta[$i] ?? 0, $realizado[$i] ?? 0];
}

At the end of any of the ties, you perform

$header = ['DIA', 'META', 'REALIZADO'];
array_unshift($result, $header);

This will place the $header array in the first position of $result.

Browser other questions tagged php javascript html5 mysqli google-charts

You are not signed in. Login or sign up in order to post.