Сложите значения из JSON по месяцам для графика Fusion Charts

Я использую MySQL, PHP и Fusion Charts, чтобы создать линейный график для отслеживания количества денег. выставлен счет за каждый месяц в 2016 году. Два столбца в моей таблице, которые я использую для этого графика, — это InvoiceAmount (десятичный тип) и InvoiceDate (тип DateTime).

Моя цель состоит в том, чтобы ось X моего графика представляла собой даты по месяцам (январь, февраль, март и т. д.) из InvoiceDate, а ось Y — сумму в долларах из InvoiceAmount. Я начал с преобразования моих данных MySQL в формат JSON, чтобы их можно было прочитать с помощью Fusion Charts:

//the SQL query to be executed
$query = "SELECT DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, InvoiceAmount FROM Estimates WHERE InvoiceDate IS NOT NULL AND YEAR(InvoiceDate) = 2016 AND InvoiceAmount IS NOT NULL AND InvoiceAmount > 0";

//storing the result of the executed query
$result = $conn->query($query);

//initialize the array to store the processed data
$jsonArray = array();

//check if there is any data returned by the SQL Query
if ($result->num_rows > 0) {
//Converting the results into an associative array
while($row = $result->fetch_assoc()) {
  $jsonArrayItem = array();
  $jsonArrayItem['label'] = $row['InvoiceMonth'];
  $jsonArrayItem['value'] = $row['InvoiceAmount'];
  //append the above created object into the main array.
  array_push($jsonArray, $jsonArrayItem);
  }
}

//Closing the connection to DB
$conn->close();

//set the response content type as JSON
header('Content-type: application/json');
//output the return value of json encode using the echo function.
echo json_encode($jsonArray, JSON_PRETTY_PRINT);

Это выводит JSON следующим образом:

[
{
    "label": "January",
    "value": "11361.00"
},
{
    "label": "December",
    "value": "1164.40"
},
{
    "label": "February",
    "value": "166.80"
},
{
    "label": "July",
    "value": "5088.00"
},
{
    "label": "January",
    "value": "214.50"
},
{
    "label": "June",
    "value": "620.40"
},
{
    "label": "July",
    "value": "5250.00"
},
{
    "label": "March",
    "value": "3425.00"
},
{
    "label": "January",
    "value": "3790.00"
},
{
    "label": "February",
    "value": "1909.80"
},
{
    "label": "January",
    "value": "1780.00"
},
{
    "label": "January",
    "value": "3060.00"
},
{
    "label": "January",
    "value": "2680.00"
},
{
    "label": "February",
    "value": "604.80"
}
]

и Т. Д.

Как видите, есть несколько значений, к которым привязан один и тот же месяц. В настоящее время линейный график обрабатывает каждую из этих пар метка/значение как один отдельный экземпляр, когда мне нужно, чтобы все значения из определенного месяца были сложены вместе, чтобы получить общее месячное значение для января, февраля, марта и т. д. .

Пример линейного графика в его нынешнем виде.

Есть ли функция, которую я могу использовать в PHP или MySQL, которая будет проверять, какой месяц привязан к значению, а затем складывать все значения из одного и того же месяца вместе? Пожалуйста, дайте мне знать, как лучше всего решить эту проблему. Спасибо за помощь.


mysql json php fusioncharts
person Liz    schedule 19.08.2016    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Вы можете использовать GROUP BY и SUM, чтобы оператор SQL возвращал только одну запись в месяц:

SELECT   DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, 
         SUM(InvoiceAmount) AS InvoiceAmount
FROM     Estimates 
WHERE    YEAR(InvoiceDate) = 2016 
     AND InvoiceAmount > 0
GROUP BY DATE_FORMAT(InvoiceDate, '%M')

Имейте в виду, что запрос с жестко закодированным годом не может поддерживаться в долгосрочной перспективе.

Я бы предложил показать последние 12 месяцев. В последнем столбце указан «год-месяц», например 201508. Он используется для упорядочения результатов:

SELECT   DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, 
         SUM(InvoiceAmount) AS InvoiceAmount,
         EXTRACT(YEAR_MONTH FROM InvoiceDate) As InvoiceYearMonth
FROM     Estimates 
WHERE    EXTRACT(YEAR_MONTH FROM InvoiceDate) >= EXTRACT(YEAR_MONTH FROM CURDATE())-100 
     AND InvoiceAmount > 0
GROUP BY DATE_FORMAT(InvoiceDate, '%M')
ORDER BY InvoiceYearMonth
person trincot    schedule 19.08.2016
comment
Это сработало отлично! Спасибо! Эта часть моего запроса: YEAR(InvoiceDate) = 2016 не работает только для получения значений за 2016 год? @тринкот - person Liz; 20.08.2016
comment
Да, но ты собираешься менять это каждый год? Кроме того, если в начале 2017 года вы изменили код на 2017 год, будет ли нормально, если вы получите только месяц(а) 2017 года, а это может быть просто январь? Показ последних 12 месяцев может быть интересной альтернативой, не так ли? - person trincot; 20.08.2016
comment
Это очень верно, и не является устойчивым в долгосрочной перспективе. У вас есть другая рекомендация? - person Liz; 20.08.2016
comment
Да, показывая последние 12 месяцев, добавляя год к меткам (если у вас есть место для него). - person trincot; 20.08.2016
comment
После вашей отличной рекомендации мой последний вопрос: $query = "SELECT DATE_FORMAT(InvoiceDate, '%M %Y') AS InvoiceMonth, SUM(InvoiceAmount) AS InvoiceAmount FROM Estimates WHERE InvoiceDate >= DATE_SUB(now(), INTERVAL 12 MONTH) AND InvoiceDate IS NOT NULL AND InvoiceAmount IS NOT NULL AND InvoiceAmount > 0 GROUP BY DATE_FORMAT(InvoiceDate, '%M %Y')"; Если у вас есть какие-либо дополнительные рекомендации, пожалуйста, дайте мне знать. - person Liz; 20.08.2016
comment
Я добавил свою рекомендацию. Проблема с вашим предложением заключается в том, что возвращение на 12 месяцев назад приводит вас к середине какого-то месяца, так что суммируется только часть этого месяца, что дает неправильное впечатление на вашем графике. - person trincot; 20.08.2016