-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             12.1.0.6557
-- --------------------------------------------------------


-- Dumping database structure for jadwal_operasi
USE `jadwal_operasi`;

-- Dumping structure for trigger jadwal_operasi.jadwal_operasi_after_insert
DROP TRIGGER IF EXISTS `jadwal_operasi_after_insert`;
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `jadwal_operasi_after_insert` AFTER INSERT ON `jadwal_operasi` FOR EACH ROW BEGIN

  UPDATE request_jadwal_operasi SET ACC = '1'
    WHERE KODE = NEW.KODE;
  
  REPLACE INTO informasi.jadwal_operasi(kodebooking, norm, nopeserta, tanggaloperasi, jenistindakan, kodepoli, namapoli)
  SELECT jo.KODE, p.NORM, IF(pj.JENIS=2,kap.NOMOR,'0000000000000') NOMOR, jo.TANGGAL, rjo.TINDAKAN, bp.kode , bp.nama
    FROM jadwal_operasi.jadwal_operasi jo
      LEFT JOIN jadwal_operasi.request_jadwal_operasi rjo ON jo.KODE = rjo.KODE
      LEFT JOIN pendaftaran.kunjungan k ON rjo.KUNJUNGAN = k.NOMOR
      LEFT JOIN pendaftaran.pendaftaran p ON k.NOPEN = p.NOMOR
      LEFT JOIN master.kartu_asuransi_pasien kap ON p.NORM=kap.NORM AND kap.JENIS=2
      LEFT JOIN master.kartu_identitas_pasien kip ON p.NORM=kip.NORM AND kip.JENIS=1
      LEFT JOIN pendaftaran.penjamin pj ON p.NOMOR = pj.NOPEN AND pj.JENIS = 2
      LEFT JOIN master.ruangan r ON k.RUANGAN = r.ID
      LEFT JOIN master.penjamin_ruangan pr ON k.RUANGAN = pr.RUANGAN_RS AND pr.PENJAMIN = 2
      LEFT JOIN master.dokter_smf mds ON rjo.DOKTER=mds.DOKTER
      LEFT JOIN master.penjamin_sub_spesialistik pss ON mds.SMF=pss.SUB_SPESIALIS_RS AND pss.PENJAMIN=2
      LEFT JOIN bpjs.poli bp ON pss.SUB_SPESIALIS_PENJAMIN = bp.kode
      LEFT JOIN master.referensi smf ON mds.SMF=smf.ID AND smf.JENIS=26
    WHERE jo.ID = NEW.ID;
  
END//
DELIMITER ;

