Untitled

-- SELECT * FROM t_journal_detail WHERE journal_id = 'BI.2001.001.BCA 8999.00003'

SET @start_date = '2024-01-01';
SET @end_date   = '2024-01-31';
SET @slip_name  = 'BCA 6699';

SET @slip_name = CASE WHEN @slip_name = 'All' THEN '%%' ELSE @slip_name END;


SELECT ms.slip_name, '01. Saldo Awal' as journal_id, @start_date as journal_date, '01. Saldo Awal' as journal_type, NULL as notes_header, 0 as `index`, si.nm_acc, 
 CASE WHEN si.idr_value >= 0 THEN si.idr_value ELSE 0 END as debet, 
 CASE WHEN si.idr_value <  0 THEN si.idr_value ELSE 0 END as credit, 
 NULL as notes_detail
FROM s_initial_coa as si
INNER JOIN m_slip as ms on si.no_acc1 = ms.no_acc1 AND
                           si.no_acc2 = ms.no_acc2 AND
                           si.no_acc3 = ms.no_acc3 AND
                           si.no_acc4 = ms.no_acc4 AND
                           si.no_acc5 = ms.no_acc5 AND
                           si.no_acc6 = ms.no_acc6 AND
                           si.no_acc7 = ms.no_acc7
WHERE si.`year` = YEAR(@start_date)
 AND si.`month` = MONTH(@start_date)
 AND ms.slip_name LIKE @slip_name


UNION ALL
SELECT tmp.slip_name,
 jh.journal_id,
 jh.journal_date,
 jh.journal_type,
 REPLACE(jh.notes,'\r\n',' ') as notes_header,
 jd.`index`,
 jd.nm_acc,
 jd.credit as debet,
 jd.debet as kredit,
 REPLACE(jd.notes,'\r\n',' ') as notes_detail 
FROM t_journal_header as jh
INNER JOIN t_journal_detail as jd on jd.journal_id = jh.journal_id
INNER JOIN (
  SELECT jd.journal_id, jd.`index`, ms.slip_name
  FROM t_journal_header as jh
  INNER JOIN t_journal_detail as jd on jd.journal_id = jh.journal_id
  INNER JOIN m_slip as ms on jd.no_acc1 = ms.no_acc1 AND
                             jd.no_acc2 = ms.no_acc2 AND
                             jd.no_acc3 = ms.no_acc3 AND
                             jd.no_acc4 = ms.no_acc4 AND
                             jd.no_acc5 = ms.no_acc5 AND
                             jd.no_acc6 = ms.no_acc6 AND
                             jd.no_acc7 = ms.no_acc7
  WHERE jh.journal_date BETWEEN @start_date AND @end_date
   AND jh.journal_void = 0
   AND ms.slip_name LIKE @slip_name
) as tmp on jd.journal_id = tmp.journal_id AND jd.`index` <> tmp.'index'

ORDER BY slip_name, journal_date, journal_id, 'index'