USE [IndInsurance]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[proc_copy_web_spaj](
@pi_no_spaj varchar(11),  
@po_message varchar(100) output)
AS

BEGIN
/* yg belum:
   - keterangan_cara_bayar_nasabah
   - tgl_selesai (not null)
   - tgl_produksi (not null)
   - total_premi (not null)
   - id_cabang (not null)   
   - id_cabang_korespondensi
oke - multi_polis_ya (not null)                -> false
oke - multi_payor_benefit_rider_ya (not null)  -> false
oke - materai_polis_ya (not null)'             -> true
oke - materai_kuitansi_ya (not null)           -> true
oke - administrasi_ya (not null)               -> true
oke - paket_ya (not null)                      -> false
oke - alternatif2_ya (not null)                -> false
   - tgl_alternatif2
   - setor_ya (not null)                      -> [func_get_setor_ya]
   - catatan
   - tgl_aplikasi (not null)     -> getdate/tgl hari ini
   - biaya_administrasi (not null)      -> biaya polis - biaya materai (lihat tabel)
   - biaya_materai (not null)            -> true
   - id_perusahaan
oke - medical_ppm_ya (not null)           -> false
oke - medical_ttg_ya (not null)           -> false
oke - id_program_marketing (not null)     -> 00001
   - id_jenis_em
   - id_jenis_em_ppm
   - cancel_note
   - cancel_by
   - cancel_date
   - id_reas_fakultatif
   - prosen_ekstra_premi_ttg (not null) -> 0
   - prosen_ekstra_premi_ppm (not null) -> 0
   - id_cara_bayar_next     -> samain dgn id cara bayar
oke - id_unit_pelayanan      -> samain dgn get id cabang
oke - id_unit_pelayanan_produksi -> samain dgn get id cabang
oke - id_unit_pelayanan_korespondensi -> samain dgn get id cabang
*/
INSERT INTO spaj (no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, id_valuta, tgl_mulai, id_jenis_investasi, uang_pertanggungan_dasar, premi_dasar, premi_rider, 
premi_berkala, top_up_berkala, id_agen, masa_kontrak_dasar, masa_pembayaran_premi_dasar, status, create_date, create_by, change_date, change_by)
SELECT no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, id_valuta, tgl_mulai, id_jenis_investasi, uang_pertanggungan_dasar, premi_dasar, premi_rider, 
premi_berkala, top_up_berkala, id_agen, masa_kontrak_dasar, masa_pembayaran_premi_dasar, status, create_date, create_by, change_date, change_by
FROM web_temp_spaj WHERE no_spaj=@pi_no_spaj

INSERT INTO spaj_pemegang (no_spaj, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, 
id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, status, create_date, create_by, change_date, change_by, hp2)
                    SELECT no_spaj, nama_lengkap, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, 
id_jenis_alamat_korespondensi, hp, hp2, email, status, create_date, create_by, change_date, change_by
FROM web_temp_spaj_pemegang WHERE no_spaj=@pi_no_spaj
  
	BEGIN
		IF EXISTS(select 1 from polis where id_polis=@pi_id_polis )
		BEGIN
			--UPDATE polis set tgl_berlaku=perubahan_polis.tgl_berlaku, aktif_ya=0, polis.status=CASE WHEN perubahan_polis.status='I' THEN 'I' WHEN perubahan_polis.status='G' THEN 'E' END
			--FROM perubahan_polis 
			--WHERE polis.id_polis=perubahan_polis.id_polis and polis.id_polis=@pi_id_polis
			
			DECLARE
			  @p_tgl_berlaku datetime,
			  @p_status varchar(1)
      SELECT 
        @p_tgl_berlaku=tgl_berlaku, 
        @p_status=status 
      FROM perubahan_polis 
      WHERE id_polis = @pi_id_polis
      
      UPDATE polis 
      SET 
        tgl_berlaku = @p_tgl_berlaku, 
        status = 
			    CASE 
			      WHEN @p_status = 'I' THEN 'I' 
			      WHEN @p_status = 'G' THEN 'E' 
			    END
      WHERE 
        id_polis = @pi_id_polis
      		
			--UPDATE p
			--SET
			--  p.tgl_berlaku = pp.tgl_berlaku,
			--  p.aktif_ya = 0,
			--  p.status = 
			--    CASE 
			--      WHEN pp.status = 'I' THEN 'I' 
			--      WHEN pp.status = 'G' THEN 'E' 
			--    END
			--FROM
			--  polis p
			--  INNER JOIN
			--  perubahan_polis pp
			--  ON p.id_polis = pp.id_polis
			--WHERE
			--  p.id_polis = @pi_id_polis
			 
			SET @po_id_polis=@pi_id_polis
/*
			SET @po_message='no polis:'+@pi_no_polis+' update'
			RETURN 1	*/
		END;
	END
	
-- select * from status_polis
IF NOT EXISTS(SELECT 1 from polis where no_polis=@pi_no_polis)
BEGIN
	SET @po_message='no polis:'+@pi_no_polis+' tidak ada'
	RETURN 1
END

IF NOT EXISTS(SELECT 1 from polis where no_polis=@pi_no_polis and dbo.func_fixed_date(tgl_berlaku)<dbo.func_fixed_date(@pi_tgl_berlaku))
BEGIN
	SET @po_message='pada tanggal '+dbo.func_date_format('dd/mm/yyyy',@pi_tgl_berlaku) +' belum ada polis'
	RETURN 1
END
select @tgl_max=MAX(tgl_berlaku) from polis where no_polis=@pi_no_polis and dbo.func_fixed_date(tgl_berlaku)<=dbo.func_fixed_date(@pi_tgl_berlaku)

If EXISTS(SELECT 1 from polis where no_polis=@pi_no_polis and dbo.func_fixed_date(tgl_berlaku)>dbo.func_fixed_date(@tgl_max) and status<>'I')
BEGIN
	SET @po_message='diatas tanggal '+dbo.func_date_format('dd/mm/yyyy',@pi_tgl_berlaku) +' masih ada polis'
	RETURN 1
END

SELECT @id_polis_last=id_polis from polis where no_polis=@pi_no_polis and dbo.func_fixed_date(tgl_berlaku)=dbo.func_fixed_date(@tgl_max)

SET @tahun=DATEPART(YEAR, @Create_Date)

EXEC gproc_SetNomor 1, 1, @tahun, 'id_polis'
IF (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Set Nomor Id Polis Gagal'
  RETURN 1
END
EXEC gproc_GetNo @Create_Date, 'id_polis', @id_polis_new OUTPUT
IF (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Get No Id Polis Gagal'
  RETURN 1
END

INSERT INTO polis
(id_polis, no_polis, no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, keterangan_cara_bayar_nasabah, id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, 
id_jenis_investasi, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, 
id_agen, id_cabang, id_cabang_korespondensi, multi_polis_ya, multi_payor_benefit_rider_ya, paket_ya, alternatif2_ya, tgl_alternatif2, catatan, aktif_ya, id_endorse, 
id_status_perubahan, biaya_administrasi, biaya_materai, status, create_date, create_by, change_date, change_by, id_perusahaan, status_valid, cancel_date, cancel_by, approve_date, 
approve_by, cancel_note, approve_note, medical_ppm_ya, medical_ttg_ya, id_program_marketing, id_jenis_em, id_jenis_em_ppm, id_reas_fakultatif, clasif, tgl_mulai_investasi, 
prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, endorse_no, migrasi_ya, tgl_berlaku, id_unit_pelayanan, id_unit_pelayanan_produksi, id_unit_pelayanan_korespondensi, no_polis_cetak)
SELECT @id_polis_new, no_polis, no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, keterangan_cara_bayar_nasabah, id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, id_jenis_investasi, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_agen, id_cabang, id_cabang_korespondensi, multi_polis_ya, multi_payor_benefit_rider_ya, paket_ya, alternatif2_ya, tgl_alternatif2, catatan, 0, id_endorse, id_status_perubahan, biaya_administrasi, biaya_materai, status, @create_date, @pi_id_user, null, null, id_perusahaan, status_valid, cancel_date, cancel_by, approve_date, approve_by, cancel_note, approve_note, medical_ppm_ya, medical_ttg_ya, id_program_marketing, id_jenis_em, id_jenis_em_ppm, id_reas_fakultatif, clasif, tgl_mulai_investasi, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, endorse_no, 0, @pi_tgl_berlaku, id_unit_pelayanan, id_unit_pelayanan_produksi, id_unit_pelayanan_korespondensi, no_polis_cetak from polis WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Gagal'
  RETURN 1
END

INSERT INTO polis_pemegang(id_polis, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, status, @create_date, @pi_id_user, null, null FROM polis_pemegang WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Pemegang Gagal'
  RETURN 1
END

INSERT INTO polis_pemegang_alamat(id_polis, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, @create_date, @pi_id_user, null, null FROM polis_pemegang_alamat WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Pemegang Alamat'
  RETURN 1
END

INSERT INTO polis_plan(id_polis, id_plan_dasar, id_plan, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, create_by, change_date, change_by, aktif_ya, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, id_status_plan, diskon_premi)
SELECT @id_polis_new, id_plan_dasar, id_plan, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, @create_date, @pi_id_user, null, null, aktif_ya, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, id_status_plan, diskon_premi FROM polis_plan WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Plan'
  RETURN 1
END


INSERT INTO polis_tertanggung(id_polis, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, id_hubungan, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, id_hubungan, status, @create_date, @pi_id_user, null, null FROM polis_tertanggung WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Tertanggung Gagal'
  RETURN 1
END

INSERT INTO polis_tertanggung_alamat(id_polis, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, @create_date, @pi_id_user, null, null FROM polis_tertanggung_alamat WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Tertanggung Alamat Gagal'
  RETURN 1
END

INSERT INTO polis_termaslahat(id_polis, no_urut, nama_ahli_waris, gelar_sebelum_nama, gelar_setelah_nama, tgl_lahir, id_hubungan, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, no_urut, nama_ahli_waris, gelar_sebelum_nama, gelar_setelah_nama, tgl_lahir, id_hubungan, status, @create_date, @pi_id_user, null, null FROM polis_termaslahat WHERE id_polis=@id_polis_last
IF (@@RowCount = 0) OR (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Termaslahat Gagal'
  RETURN 1
END


INSERT INTO polis_Jenis_em_ttg(id_polis, id_jenis_em, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, id_jenis_em, status, @create_date, @pi_id_user, null, null FROM polis_jenis_em_ttg WHERE id_polis=@id_polis_last
IF (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Jenis EM TTG'
  RETURN 1
END

INSERT INTO polis_Jenis_em_ppm(id_polis, id_jenis_em, status, create_date, create_by, change_date, change_by)
SELECT @id_polis_new, id_jenis_em, status, @create_date, @pi_id_user, null, null FROM polis_jenis_em_ppm WHERE id_polis=@id_polis_last
IF (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Copy Polis Jenis EM PPM'
  RETURN 1
END

EXEC dbo.proc_proses_history_status_polis @pi_no_polis, @pi_tgl_berlaku, 1, @pi_id_user, @po_message output
IF (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Proses history status polis Gagal'
  RETURN 1
END

EXEC [dbo].[proc_set_aktif_ya_polis] @pi_no_polis, @pi_tgl_berlaku
IF (@@Error <> 0)
BEGIN
  -- Gagal
  SET @po_message = 'Set Aktif ya PolisGagal'
  RETURN 1
END

SET @po_id_polis=@id_polis_new
SET @po_message='proses copy sukses'
RETURN 0

