----------start salah jurnal transaksi payment gateway--------------------
SELECT * FROM `transactions`
where transaction_mode_value=3
and transaction_type_value=2;

select * from journals j
join transactions t on t.id = j.transaction_id
where t.transaction_mode_value=3
and t.transaction_type_value=2
and debit>0
and LOWER(coa_name) like '%Tabungan siswa MA%';

update journals j
join transactions t on t.id = j.transaction_id
set coa_id = 2, coa_name = 'Bank Bank Mandiri'
where t.transaction_mode_value=3
and t.transaction_type_value=2
and debit>0
and coa_name like '%Tabungan%';

----------ambil data pegawai telat--------------------
SELECT e.name, count(a.id) FROM `attendances` a
join employees e on a.employee_id = e.id
where a.schedule_type_value = 1
and attendance_date >= '2023-02-28'
and attendance_date <= '2023-03-27'
and first_status_value = 3
group by e.id;

----------update transaksi pencairan toko--------------------
UPDATE transactions
JOIN store_withdrawals ON transactions.store_id = store_withdrawals.store_id AND transactions.amount = store_withdrawals.amount
SET transactions.store_withdrawal_id = store_withdrawals.id
WHERE transactions.transaction_type_value = 8 AND transactions.store_withdrawal_id IS NULL;

----------membandingkan autodebet dan autodebet student--------------------
SELECT ad.id AS autodebet_id, ad.successCount, COUNT(ads.id) AS autodebet_student_count
 FROM autodebets ad
 LEFT JOIN autodebet_student ads ON ad.id = ads.autodebet_id
WHERE ads.autodebet_status_value = 1
GROUP BY ad.id, ad.successCount;
----------mencari total uang masuk dan keluar saldo santri--------------------
SELECT * FROM transactions WHERE balance_id = 2327 OR to_balance_id = 2327
----------mencari selisih total uang masuk dan keluar saldo santri--------------------
SELECT
  (SELECT SUM(amount) FROM transactions WHERE (transaction_type_value = 3 AND balance_id = 2327) OR (transaction_type_value != 3 AND to_balance_id = 2327))
  -
  (SELECT SUM(amount) FROM transactions WHERE transaction_type_value != 3 AND balance_id = 2327) AS sum_difference;

SELECT id, amount, description FROM transactions WHERE (transaction_type_value = 3 AND balance_id = 2327) OR (transaction_type_value != 3 AND to_balance_id = 2327);

SELECT id, amount, description FROM transactions WHERE transaction_type_value != 3 AND balance_id = 2327;

----------mencari jurnal yang gagal insert--------------------
select * from transactions
left join journals on journals.transaction_id = transactions.id
where transaction_type_value != 3
and balance_id = 2574
and journals.id is null;
----------update tagihan sesuai tahun ajaran---------------------
UPDATE bills
SET bill_type_id = 61, tahun_id = 6
WHERE (
    (month_value > 6 AND year_value > 2022)
    OR
    (month_value < 7 AND year_value > 2023)
)
AND bill_type_id = 20
AND tahun_id = 5;
---------

-----mencari transaksi tiap toko dalam waktu tertentu--------
SELECT transactionDate, store_id, stores.name,
CASE WHEN transaction_mode_value = 1 THEN 'cashless'
WHEN transaction_mode_value = 2 THEN 'tunai' ELSE 'unknown'
END AS transaction_mode, SUM(amount) AS total_amount
FROM transactions
JOIN stores ON transactions.store_id = stores.id
WHERE transactionDate >= '2024-07-01'
AND transactionDate <= '2024-07-09'
AND transaction_type_value = 1
GROUP BY transactionDate, store_id, transaction_mode
-------------

----------mencari selisih total uang tabungan dan uang keluar tabungan --------------------

select (select sum(balance) from balances where balance_type_value=1)-
(select (select sum(amount) from transactions 
where transaction_type_value=3) - (select sum(amount) from transactions 
where transaction_type_value not in (3, 14, 0)
and balance_id is not null));

---------mencari semua pencairan toko sampai saldo 0------------------
SELECT * FROM `store_balance_mutations` where store_id=23 and balance_after = 0 order by id desc;

----------mencari selisih total uang masuk dan keluar saldo toko--------------------
SELECT
  (SELECT SUM(amount) FROM transactions WHERE (transaction_type_value = 1 AND store_id = 6))
  -
  (SELECT SUM(amount) FROM transactions WHERE transaction_type_value = 8 AND store_id = 6) AS sum_difference;

----------------mencari saldo toko pada waktu tertentu dari transaksi ------------------------
SELECT
  (SELECT SUM(amount) FROM transactions WHERE transaction_type_value = 1 AND store_id = 23 and id > 885293 and id < 890573 )
  -
  (SELECT SUM(amount) FROM transactions WHERE transaction_type_value = 8 AND store_id = 23 and id > 885293 and id < 890573 ) 
  AS sum_difference;

----------------mencari saldo toko pada waktu tertentu dari jurnal-------------------
SELECT 
    SUM(CASE 
            WHEN t.transaction_type_value = 1 THEN j.credit 
            ELSE 0 
        END) AS total_credit,
    SUM(CASE 
            WHEN t.transaction_type_value = 8 THEN j.debit 
            ELSE 0 
        END) AS total_debit,
    SUM(CASE 
            WHEN t.transaction_type_value = 1 THEN j.credit 
            ELSE 0 
        END) - 
    SUM(CASE 
            WHEN t.transaction_type_value = 8 THEN j.debit 
            ELSE 0 
        END) AS difference
FROM journals j
JOIN transactions t ON j.transaction_id = t.id
WHERE t.store_id = 23 
  AND t.id > 885293 
  AND t.id < 890573
  AND j.coa_id = 227;


----------mencari selisih total uang masuk dan keluar saldo santri--------------------
SELECT id,
       CASE
           WHEN transaction_type_value != 3 AND balance_id = 117 THEN -amount
           ELSE amount
END AS amount,
       description,
       transactionDate
FROM transactions
WHERE (transaction_type_value = 3 AND balance_id = 117)
   OR (transaction_type_value != 3 AND (balance_id = 117 OR to_balance_id=117))

-------------mencari duplikat transaksi jajan santri----------------------
SELECT *
FROM transactions
WHERE 
balance_id = 405
and created_at IN (
    SELECT created_at
    FROM transactions
    where balance_id = 405
    GROUP BY created_at
    HAVING COUNT(*) > 1
);


---------report pencatatan saldo sebelum fitur mutasi saldo -----------------
select s.name as Nama, p.name as Kelas, b.balance as Saldo from balances b
join students s on s.id = b.student_id
join parallels p on p.id=s.parallel_id
where b.balance_type_value=1
  and s.isActive=1
order by p.name, s.name;
----- mencari saldo selisih ---------
SELECT 
  b.id AS balance_id,
  b.balance AS real_balance,
  COALESCE(
    (SELECT SUM(amount) FROM transactions 
     WHERE (transaction_type_value = 3 AND balance_id = b.id) 
        OR (transaction_type_value != 3 AND to_balance_id = b.id)), 0
  )
  -
  COALESCE(
    (SELECT SUM(amount) FROM transactions 
     WHERE transaction_type_value != 3 AND balance_id = b.id), 0
  ) AS sum_difference
FROM 
  balances b
HAVING 
  real_balance != sum_difference;
------------------melihat seluruh saldo-------------------
SELECT 
    CASE 
        WHEN b.user_type_value = 1 THEN e.name
        WHEN b.user_type_value = 2 THEN s.name
        ELSE NULL
    END AS Nama,
    CASE 
        WHEN b.user_type_value = 1 THEN "Pegawai"
        WHEN b.user_type_value = 2 THEN "Santri"
        ELSE NULL
    END AS Tipe,
    b.balance AS Saldo
FROM balances b
LEFT JOIN students s ON s.id = b.student_id
LEFT JOIN employees e ON e.id = b.employee_id
WHERE b.balance_type_value = 1
ORDER BY b.user_type_value;
---------------- cek jurnal minus -----------------
SELECT j.id, j.coa_name, j.debit, j.credit, t.id, t.amount, t.description 
FROM `journals` j 
join transactions t on t.id = j.transaction_id
 WHERE debit < 0 or credit < 0;
 ---------------- cek transaksi untuk jurnal minus-----------------
 SELECT * FROM `transactions` t 
 join bill_transaction bt on t.id = bt.transaction_id 
 JOIN bill_types bts on bts.id = bt.bill_type_id 
 join bills b on b.id = bt.bill_id where t.id = 59782;
 ---------------- cek bill-transaksi untuk jurnal minus-----------------
 SELECT * FROM `bill_transaction` WHERE transaction_id=59782;
 ---------------- cek bill untuk jurnal minus-----------------
 SELECT * FROM `bills` where id =39148;
 ---------------- cek transaksi untuk jurnal minus-----------------
 select * from transactions where id=59782;
 ---------------- cek jurnals untuk jurnal minus-----------------
 SELECT * FROM `journals` where transaction_id = 59782;
 ---------------- cek pembayaran bill untuk jurnal minus-----------------
 SELECT * FROM `bill_transaction` where bill_id=39148;

-----------------cancel pembayaran tagihan dalam transaksi ketika bayar beberapa tagihan sekaligus----------------
SELECT * FROM `transactions` where id=792881;
SELECT * FROM `journals` where transaction_id=792881;
SELECT * FROM `bill_transaction` where transaction_id=792881;
SELECT * FROM `bills` where id in (54164,55914);
SELECT * FROM `bill_transaction` where bill_id in (54164,55914);
hapus jurnals
hapus bill_transaction
edit transactions sesuai angka yang benar 
edit status bills 
hapus bills jika diperlukan
