FooterValue

procedure FrmBukuBesar_TableGrid1_OnChange(Sender: TObject);
var
  i: Integer;
  Debet, Kredit, Hasil: double;

  LastRow: Integer;
  ColumnValue: string;
begin
  for i := 4 to 5 do
  begin
    if FrmBukuBesar.TableGrid1.Columns[i] is TNxNumberColumn then
    begin
      with TNxNumberColumn(FrmBukuBesar.TableGrid1.Columns[i]) do
      begin
        FormatMask := '###,##0';
        Footer.FormatMask := '###,##0';
        Footer.FormulaKind := fkSum;
        end;
    end;
  end;

  with TNxNumberColumn(FrmBukuBesar.TableGrid1.Columns[6]) do
  begin
    FormatMask := '###,##0';
  end;

  if FrmBukuBesar.TableGrid1.RowCount > 1 then
  begin
    LastRow := FrmBukuBesar.TableGrid1.RowCount - 1;
    ColumnValue := FrmBukuBesar.TableGrid1.Cells[6, LastRow];

    if FrmBukuBesar.TableGrid1.Columns.Count > 6 then
    begin
      FrmBukuBesar.TableGrid1.Columns[6].Footer.Caption := ColumnValue;
      FrmBukuBesar.TableGrid1.Refresh;
    end
    else
      ShowMessage('Kolom ke-6 tidak ditemukan');
  end
  else
    ShowMessage('Tidak ada data di TableGrid1');

  FrmBukuBesar.TableGrid1.CalculateFooter;
  FrmBukuBesar.TableGrid1.Columns[7].Visible := False;
end;

-- Baris Saldo Awal
SELECT 
  a.kode_akun AS Kode_Akun,
  a.nama_akun AS Nama_Akun,
  strftime('%d', date({DateTimePicker1}, '-1 day')) || ' ' ||
        CASE strftime('%m', date({DateTimePicker1}, '-1 day'))
            WHEN '01' THEN 'Jan'                                        
            WHEN '02' THEN 'Feb'
            WHEN '03' THEN 'Mar'         
            WHEN '04' THEN 'Apr'
            WHEN '05' THEN 'Mei'                
            WHEN '06' THEN 'Jun'
            WHEN '07' THEN 'Jul'
            WHEN '08' THEN 'Agt'
            WHEN '09' THEN 'Sep'
            WHEN '10' THEN 'Okt'
            WHEN '11' THEN 'Nov'
            WHEN '12' THEN 'Des'
        END || ' ' || strftime('%Y', date({DateTimePicker1}, '-1 day')) AS Tanggal,
  'Saldo Awal' AS Deskripsi,
  CAST(0 AS NUMERIC) AS Total_Debet,
  CAST(0 AS NUMERIC) AS Total_Kredit,

  IFNULL((
    SELECT SUM(CASE 
      WHEN td2.posisi = 'Debet' THEN td2.jumlah 
      ELSE -td2.jumlah 
    END)
    FROM transaksi_detail td2 
    JOIN transaksi t2 ON td2.id_transaksi = t2.id
    WHERE td2.id_akun = a.id
      AND t2.tanggal < {DateTimePicker1}
  ), 0) AS Saldo_Berjalan,
  0 AS urutan_sorting

FROM akun a
WHERE a.id = {ComboBox1}

UNION ALL

-- Transaksi Normal
SELECT 
  a.kode_akun AS Kode_Akun,
  a.nama_akun AS Nama_Akun,
  strftime('%d', t.tanggal) || ' ' ||
        CASE strftime('%m', t.tanggal)
            WHEN '01' THEN 'Jan'
            WHEN '02' THEN 'Feb'
            WHEN '03' THEN 'Mar'         
            WHEN '04' THEN 'Apr'
            WHEN '05' THEN 'Mei'
            WHEN '06' THEN 'Jun'
            WHEN '07' THEN 'Jul'
            WHEN '08' THEN 'Agt'
            WHEN '09' THEN 'Sep'
            WHEN '10' THEN 'Okt'
            WHEN '11' THEN 'Nov'
            WHEN '12' THEN 'Des'
        END || ' ' || strftime('%Y', t.tanggal) AS Tanggal,
  t.deskripsi AS Deskripsi,
  SUM(CASE WHEN td.posisi = 'Debet' THEN td.jumlah ELSE 0 END) AS Total_Debet,
  SUM(CASE WHEN td.posisi = 'Kredit' THEN td.jumlah ELSE 0 END) AS Total_Kredit,
  (
    SELECT SUM(CASE 
      WHEN td2.posisi = 'Debet' THEN td2.jumlah                
      ELSE -td2.jumlah 
    END)
    FROM transaksi_detail td2                               
    JOIN transaksi t2 ON td2.id_transaksi = t2.id
    WHERE td2.id_akun = a.id
      AND (t2.tanggal < t.tanggal OR (t2.tanggal = t.tanggal AND t2.id <= t.id))
  ) AS Saldo_Berjalan,                   
  t.tanggal AS urutan_sorting

FROM transaksi_detail td
JOIN transaksi t ON td.id_transaksi = t.id
JOIN akun a ON td.id_akun = a.id
WHERE a.id = {ComboBox1}
  AND t.tanggal BETWEEN {DateTimePicker1} AND {DateTimePicker2} 
  AND a.id_status_akun = 1  -- hanya akun aktif 
GROUP BY a.kode_akun, a.nama_akun, a.id, t.tanggal, t.deskripsi, t.id
                                           
ORDER BY urutan_sorting ASC;

  (
    SELECT SUM(CASE 
      WHEN td2.posisi = 'Debet' THEN td2.jumlah                
      ELSE -td2.jumlah 
    END)
    FROM transaksi_detail td2                               
    JOIN transaksi t2 ON td2.id_transaksi = t2.id
    WHERE td2.id_akun = a.id
      AND (t2.tanggal < t.tanggal OR (t2.tanggal = t.tanggal AND t2.id <= t.id))
  ) AS Saldo_Berjalan