Участок пользовательских транзакций на каждый день недели
В моей БД есть таблица транзакций. Эта таблица содержит информацию о денежных транзакциях пользователя. Моя задача — сгенерировать две диаграммы (графики), показывающие сумму транзакции за день и за месяц.
Пример схемы, которую я должен реализовать, показан на рисунке ниже:
Как видно из диаграммы, при отсутствии транзакций в день, общее значение должно быть 0.
В базе данных я группирую свои транзакции по дням, используя следующий запрос:
select DATE_FORMAT(t.transactionDate ,"%b %d") as dayName, sum(t.amount) total from `Transaction` t, t.transactionStatus = 1 and t.user=17
group by dayName
order by t.transactionDate asc;
Это работает как талисман, когда есть транзакции на каждый день, но это не работает нормально, когда есть день без какой-либо транзакции. Допустим, в БД у нас есть следующие транзакции:
May 1st 20$
May 1st 30$
May 1st 38$
May 2nd 20$
May 4th 100$
May 4th 50$
Так что никаких сделок 3 мая.
Когда я группирую эти транзакции, я получаю следующий результат:
May 1st 88$
May 2nd 20$
May 4th 150$
Теперь я хочу создать May 3rd 0$, Можно ли сделать это непосредственно в БД, или я должен обрабатывать его с помощью PHP?
Если мне придется обрабатывать его с помощью PHP, есть идеи?
Для построения графиков я использую библиотеку Chart JS, а вот пример ввода данных:
var areaChartData = {
labels: ["May 01", "May 02", "May 04"],
datasets: [
{
label: "Transactions",
fillColor: "rgba(210, 214, 222, 1)",
strokeColor: "rgba(210, 214, 222, 1)",
pointColor: "rgba(210, 214, 222, 1)",
pointStrokeColor: "#c1c7d1",
pointHighlightFill: "#fff",
pointHighlightStroke: "rgba(220,220,220,1)",
data: [88,20,150]
},
]
};
Как вы можете видеть, моя идея состоит в том, чтобы использовать значения из базы данных непосредственно на графике, где дата транзакции — моя ось X, а total — моя ось Y.
Решение
Один из подходов состоит в том, чтобы сначала иметь все возможные даты и значения в виде массива PHP:
$labels = array(
"May 1st" => 0,
"May 2nd" => 0,
"May 3rd" => 0,
"May 4th" => 0,
"May 6th" => 0,
"May 6th" => 0,
....
);
Затем заполните все значения из базы данных до соответствующего ключа даты в массиве
...
foreach ( $rows as $row )
{
$labels[$row['date']] = $row['agg_val'];
...
Теперь вы можете перебирать $labels массив и выложить значения. Те, которых нет в БД (без транзакций), обнуляются.
Другие решения
Глядя на ваш запрос, он сгенерирует $result как ниже,
array (size=3)
0 =>
array (size=2)
'dayName' => string 'May 01' (length=6)
'total' => int 88
1 =>
array (size=2)
'dayName' => string 'May 02' (length=6)
'total' => int 20
2 =>
array (size=2)
'dayName' => string 'May 04' (length=6)
'total' => int 150
Этот скрипт подготовит массив для диаграммы данных,
$begin = reset($result) ['dayName']; // 1st date in result
$end = end($result) ['dayName']; // last date in result
$begin = new DateTime($begin);
$end = new DateTime($end);
$end->modify('+1 day');
$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);
function search($input, $day)
{
foreach($input as $k => $v)
{
if ($v['dayName'] == $day) return $k;
}
return false;
}
$chartdata = []; // array for chartdata
foreach($period as $dt) // loop for everyday from 1st to last day
{
$format = $dt->format('M d');
$flag = search($result, $dt->format('M d'));
$chartdata[$format] = is_int($flag) ? $result[$flag]['total'] : 0;
}
Теперь ваш $chartdata содержит пропущенный день May 03 из таблицы транзакций.
Теперь используйте это как
labels: <?php echo json_encode(array_keys($chartdata))?>,
а также
data: <?php echo json_encode(array_values($chartdata))?>