<?php
$pdo->query("create table tis_temp_dsr (customer_no varchar(9), customer_name varchar(9), tis_posting_date datetime, tis_customer_no int,tis_account_name varchar(9),tis_amount_with_vat int)");
$pdo->query("insert into tis_temp_dsr values ('bla', 'bla', '2025-01-01',1,'bla',10), ('hey', 'bart', '2025-02-02',2,'ney',20)");
$colsQuery =
"
SELECT CONCAT('[', FORMAT(month_year, 'MMM yyyy'), ']')
FROM (
SELECT DISTINCT EOMONTH(tis_posting_date) AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '01-01-2025' AND '03-31-2025'
) t
ORDER BY month_year
";
$cols = implode(',', $pdo->query($colsQuery)->fetchAll(PDO::FETCH_COLUMN)).',[Total]';
//echo $cols."\n";
$query =
"
with cte as
(
select tis_customer_no,tis_account_name,
month_year = FORMAT(tis_posting_date, 'MMM yyyy'), tis_amount_with_vat
from tis_temp_dsr
union all
select tis_customer_no,tis_account_name,
month_year = 'Total', tis_amount_with_vat = sum(tis_amount_with_vat)
from tis_temp_dsr
group by tis_customer_no,tis_account_name
)
SELECT tis_customer_no,tis_account_name,
".$cols."
from
(
select tis_customer_no,tis_account_name, month_year, tis_amount_with_vat
from cte
-- the following is for row wise total
union all
select tis_customer_no = '',tis_account_name = 'Total', month_year, sum(tis_amount_with_vat)
from cte
group by month_year
) x
pivot
(
Sum(tis_amount_with_vat)
for month_year in (".$cols.")
) p
";
//echo $query."\n";
$results = $pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
//print_r($results);
foreach([ -1 => array_keys($results[0]) ] + $results as $line)
echo implode(' | ', array_map(function($x) => str_pad($x, 16), $line))."\n";