Melihat Procedure
SHOW PROCEDURE STATUS WHERE Db = 'db_mitra_gbee'
Hapus Procedure
DROP PROCEDURE IF EXISTS data_follow_up_penjualan;
DELIMITER $$
CREATE PROCEDURE data_follow_up_penjualan(
MITRAID VARCHAR(255)
)
BEGIN
SELECT a.id, a.nama_lengkap, a.id_pelanggan_accurate, a.id_kustomer, b.tanggal_transaksi as transaksi_terakhir, c.last_follow_up, d.status, f.nama_metode, (select count(*) from follow_up_penjualan e where e.kustomer_mitra_id = a.id and e.alarm="Y") AS alarm
FROM kustomer_mitra a
INNER JOIN view_last_transaksi_penjualan b on a.id=b.transaksiable_id and b.transaksiable_type like "%KustomerMitra%"
LEFT JOIN view_last_follow_up_head c on c.kustomer_mitra_id=a.id
LEFT JOIN follow_up_penjualan_head d on a.id=d.kustomer_mitra_id and d.created_at=c.last_follow_up and d.parent_id is null
LEFT JOIN metode_pembayaran f on b.metode_pembayaran_id = f.id
WHERE a.mitra_id=MITRAID
GROUP BY a.id, a.nama_lengkap, a.id_pelanggan_accurate, a.id_kustomer, b.tanggal_transaksi, c.last_follow_up, d.status, f.nama_metode
ORDER BY alarm, b.tanggal_transaksi DESC;
END$$
DELIMITER ;
Cara Memanggil Procedure
CALL data_follow_up_penjualan('3375000002');