USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_create_enrollment_peserta_admedika]    Script Date: 11/06/2015 15:29:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- DELETE FROM enrollment WHERE no_proposal='2007.0776' and md_001='5'
-- SELECT no_peserta, md_001, md_045 FROM enrollment WHERE no_proposal='2007.0776' and md_001='5'
-- EXEC proc_CreateEnrollment '12/26/2007', '12/12/2007', '12/12/2007', 'SYS', '2007.0776'
--select top 100 * from Enrollment

ALTER PROCEDURE [dbo].[proc_create_enrollment_peserta_admedika]
      @pi_tgl_proses DATETIME,
      @pi_tgl_buat_awal DATETIME,
      @pi_tgl_buat_akhir DATETIME,
      @pi_id_user VARCHAR(5),
      @pi_no_proposal VARCHAR(9),
      @pi_tipe5 VARCHAR(30)
AS 
IF UPPER(COALESCE(@pi_tipe5, '')) = 'NORMAL' 
   BEGIN
         SET @pi_tipe5 = ''
  
         SET NOCOUNT ON
         DECLARE @gk_Kartu BIT
         DECLARE @No_Proposal_Old VARCHAR(9) 
         DECLARE @No_Proposal VARCHAR(9) 
         DECLARE @No_Peserta VARCHAR(12) 
         DECLARE @Foto VARCHAR(50) 
         DECLARE @MD_001 VARCHAR(1) 
         DECLARE @Jenis INT
         DECLARE @Sub_Init VARCHAR(1)
         DECLARE @Msg VARCHAR(100)
  

  -- Proses Enrollment I (Baru)
         DECLARE CreateEnrollment_Cursor CURSOR
         FOR
         SELECT
          dbo.Peserta_Produk_Detail.No_Proposal,
          dbo.Peserta_Produk_Detail.No_Peserta,
          dbo.Note_Terbit_Master.Jenis
         FROM
          dbo.Peserta_Produk_Detail
          INNER JOIN dbo.Note_Terbit_Master ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref
                                               AND dbo.Peserta_Produk_Detail.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
          INNER JOIN dbo.Polis_Asuransi ON dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
          INNER JOIN dbo.Peserta ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
          INNER JOIN produk_polis ON Peserta_Produk_Detail.No_Proposal = produk_polis.No_Proposal
                                     AND Peserta_Produk_Detail.id_produk = produk_polis.id_produk
                                     AND Produk_Polis.AdMedika_Ya = 1
         WHERE
          (dbo.Note_Terbit_Master.No_Proposal = @pi_no_proposal
          OR ISNULL(@pi_no_proposal, '') = '')
          AND (dbo.Note_Terbit_Master.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19)
          OR (dbo.Note_Terbit_Master.Jenis BETWEEN 60 AND 69))
          AND (dbo.Note_Terbit_Master.Status = 'I')
          AND (ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) = 1)
          AND (dbo.Polis_Asuransi.Status = 'I')
          AND (LEFT(dbo.Peserta_Produk_Detail.Sub_Kelompok, 2) = '03')
          AND (dbo.Note_Terbit_Master.Tgl_Buat >= @pi_tgl_buat_awal)
          AND (dbo.Note_Terbit_Master.Tgl_Buat <= @pi_tgl_buat_akhir)
         GROUP BY
          dbo.Note_Terbit_Master.Tgl_Buat,
          dbo.Peserta_Produk_Detail.No_Proposal,
          dbo.Peserta_Produk_Detail.No_Peserta,
          dbo.Note_Terbit_Master.Jenis
         ORDER BY
          dbo.Note_Terbit_Master.Tgl_Buat

         OPEN CreateEnrollment_Cursor
         FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @No_Peserta, @Jenis
         WHILE @@FETCH_STATUS = 0 
               BEGIN
                     EXEC proc_add_enrollment_peserta_admedika @pi_tgl_proses, @No_Proposal, @No_Peserta, 1, '', @pi_tipe5, @pi_id_user, @Msg OUTPUT
                     FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @No_Peserta, @Jenis
               END
         CLOSE CreateEnrollment_Cursor
         DEALLOCATE CreateEnrollment_Cursor

  -- Proses Enrollment I (Lapse)
         DECLARE CreateEnrollment_Cursor CURSOR
         FOR
         SELECT
          dbo.Peserta_Produk_Detail.No_Proposal,
          dbo.Peserta_Produk_Detail.No_Peserta,
          dbo.Note_Terbit_Master.Jenis
         FROM
          dbo.Peserta_Produk_Detail
          INNER JOIN dbo.Note_Terbit_Master ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref
                                               AND dbo.Peserta_Produk_Detail.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
          INNER JOIN dbo.Polis_Asuransi ON dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
          INNER JOIN dbo.Peserta ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
          INNER JOIN produk_polis ON Peserta_Produk_Detail.No_Proposal = produk_polis.No_Proposal
                                     AND Peserta_Produk_Detail.id_produk = produk_polis.id_produk
                                     AND Produk_Polis.AdMedika_Ya = 1
         WHERE
          (dbo.Note_Terbit_Master.No_Proposal = @pi_no_proposal
          OR ISNULL(@pi_no_proposal, '') = '')
          AND (dbo.Note_Terbit_Master.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19, 60, 61, 62, 63))
          AND (dbo.Note_Terbit_Master.Status = 'C')
          AND (ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) = 1)
          AND (dbo.Polis_Asuransi.Status = 'I')
          AND (LEFT(dbo.Peserta_Produk_Detail.Sub_Kelompok, 2) = '03')
          AND (dbo.Note_Terbit_Master.Tgl_Batal >= @pi_tgl_buat_awal)
          AND (dbo.Note_Terbit_Master.Tgl_Batal <= @pi_tgl_buat_akhir)
         GROUP BY
          dbo.Peserta_Produk_Detail.No_Proposal,
          dbo.Peserta_Produk_Detail.No_Peserta,
          dbo.Note_Terbit_Master.Jenis
         OPEN CreateEnrollment_Cursor
         FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @No_Peserta, @Jenis
         WHILE @@FETCH_STATUS = 0 
               BEGIN
                     EXEC proc_add_enrollment_peserta_admedika @pi_tgl_proses, @No_Proposal, @No_Peserta, 1, '', @pi_tipe5, @pi_id_user, @Msg OUTPUT
                     FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @No_Peserta, @Jenis
               END
         CLOSE CreateEnrollment_Cursor
         DEALLOCATE CreateEnrollment_Cursor

-- Proses Enrollment I (Lapse 2)
         DECLARE CreateEnrollment_Cursor CURSOR
         FOR
         SELECT
          dbo.Peserta.No_Proposal,
          dbo.Peserta.No_Peserta,
          3
         FROM
          dbo.Peserta
          INNER JOIN dbo.Polis_Asuransi ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
         WHERE
          (dbo.Polis_Asuransi.No_Proposal = @pi_no_proposal
          OR ISNULL(@pi_no_proposal, '') = '')
          AND (ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) = 1)
          AND (dbo.Polis_Asuransi.Status = 'I')
          AND (dbo.Peserta.Status <> 'I')
          AND (dbo.Peserta.Tgl_Lapse >= @pi_tgl_buat_awal)
          AND (dbo.Peserta.Tgl_Lapse <= @pi_tgl_buat_akhir)
         GROUP BY
          dbo.Peserta.No_Proposal,
          dbo.Peserta.No_Peserta 
         OPEN CreateEnrollment_Cursor
         FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @No_Peserta, @Jenis
         WHILE @@FETCH_STATUS = 0 
               BEGIN
                     EXEC proc_add_enrollment_peserta_admedika @pi_tgl_proses, @No_Proposal, @No_Peserta, 1, '', @pi_tipe5, @pi_id_user, @Msg OUTPUT
                     FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @No_Peserta, @Jenis
               END
         CLOSE CreateEnrollment_Cursor
         DEALLOCATE CreateEnrollment_Cursor
   END
---- start: tambah mode 6 & 7. [by Pribadi Pamungkas, 2015-11-06] ----
ELSE
   IF SUBSTRING(UPPER(COALESCE(@pi_tipe5, '')), 1, 6) = 'MODE 6'
    BEGIN
    
    -- buat enrollment normal (mode 5) dulu
    DECLARE	@retval int
    EXEC  @retval = [dbo].[proc_create_enrollment_peserta_admedika]
	        @pi_tgl_proses,
          @pi_tgl_buat_awal,
          @pi_tgl_buat_akhir,
          @pi_id_user,
          @pi_no_proposal,
          'NORMAL'
    IF (@@Error = 0) AND (@retval = 0)  -- jika sukses, ubah modenya jadi 6
      BEGIN
        UPDATE
          dbo.Enrollment
        SET
          MD_001 = '6'
        WHERE
          No_Proposal = @pi_no_proposal
          AND Tgl_Proses = @pi_tgl_proses
          AND Status = 'G'
      END

    END 
ELSE
   IF SUBSTRING(UPPER(COALESCE(@pi_tipe5, '')), 1, 6) = 'MODE 7'
    BEGIN
    
    -- buat enrollment normal (mode 5) dulu
    DECLARE	@retval int
    EXEC  @retval = [dbo].[proc_create_enrollment_peserta_admedika]
	        @pi_tgl_proses,
          @pi_tgl_buat_awal,
          @pi_tgl_buat_akhir,
          @pi_id_user,
          @pi_no_proposal,
          'NORMAL'
    IF (@@Error = 0) AND (@retval = 0)  -- jika sukses, copy jadi mode 7 dengan beberapa perubahan data
      BEGIN
        UPDATE
          dbo.Enrollment
        SET
          MD_001 = '6'
        WHERE
          No_Proposal = @pi_no_proposal
          AND Tgl_Proses = @pi_tgl_proses
          AND Status = 'G'
      END
      
    END 
---- start: tambah mode 6 & 7. [by Pribadi Pamungkas, 2015-11-06] ----
ELSE 
   IF UPPER(COALESCE(@pi_tipe5, '')) = 'MODE 11' 
      SET @pi_tipe5 = '11'
BEGIN
	--select * from Enrollment where MD_001='5' and no_proposal='2013.0511'
      UPDATE
        Enrollment
      SET
        MD_001 = @pi_tipe5
      WHERE
        MD_001 = '5'
        AND no_proposal = @pi_no_proposal
        AND tgl_proses = @pi_tgl_proses
END  
