USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_ConvertPstFROMText]    Script Date: 05/21/2018 13:57:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-05-21 => Panjang kolom nama sebelumnya 30 diubah menjadi 50. Nama di kartu (Card_Name) diperbaiki.

-- EXEC proc_ConvertPstFROMText2 '2008.0512', 1, 'SIS'
ALTER Procedure [dbo].[proc_ConvertPstFROMText]
@NoProposal varchar(9), 
@Mode int, 
--@TglMasuk Datetime,
--@TglMasukYa bit,
@CreateBy varchar(4)
AS
DECLARE @Create_Date DateTime
  SET @Create_Date=dbo.func_FixedDate(GetDate())

  IF isnull(@Mode,0)=0 
  BEGIN
       DELETE FROM Peserta WHERE No_Proposal=@NoProposal
       DELETE FROM Peserta_Produk WHERE No_Proposal=@NoProposal
  END
  DECLARE @Nama varchar(50)
  DECLARE @NoPrs varchar(9)
  DECLARE @Tgl_Masuk_Polis Datetime
  DECLARE @PeriodeBayar varchar(2) 
  DECLARE @MataUang  varchar(2) 
  DECLARE @Faktor float 
  DECLARE @Tgl_Mulai_Polis Datetime
  DECLARE @Tgl_Selesai_Polis Datetime
  DECLARE @ThnMasuk int
  DECLARE @CurrNo varchar(12)
  DECLARE @NoPeserta varchar(12)
  DECLARE @NoPolis varchar(11)
  DECLARE @Kawin varchar(2)
  DECLARE @Status varchar(10)
  DECLARE @Sebagai int
  DECLARE @No int
  DECLARE @Count int
  DECLARE @IdProduk varchar(10)
  DECLARE @Induk varchar(12)
  DECLARE @Sbg varchar(1)
  DECLARE @PPK1 varchar(4)
  DECLARE @PPK2 varchar(4)
  SELECT
    @Tgl_Masuk_Polis = dbo.Perusahaan.Tgl_Masuk,
    @NoPrs = dbo.Perusahaan.No_Prs,
    @Tgl_Mulai_Polis = dbo.Polis_Asuransi.Tgl_Mulai,
    @Tgl_Selesai_Polis = dbo.Polis_Asuransi.Tgl_Selesai,
    @PeriodeBayar = dbo.Polis_Asuransi.Periode_Bayar,
    @MataUang = dbo.Polis_Asuransi.Mata_Uang,
    @Faktor = dbo.Polis_Asuransi.Faktor,
    @NoPolis = dbo.Polis_Asuransi.No_Polis
  FROM
    dbo.Polis_Asuransi
    INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
  WHERE
    dbo.Polis_Asuransi.No_Proposal = @NoProposal 

  SET @ThnMasuk=DATEPART(Year,@Tgl_Masuk_Polis)
  EXEC [dbo].[gproc_SetNo] 1, 1000, @ThnMasuk, 4

  DECLARE Employee_Cursor CURSOR FOR
  SELECT A, LEFT(LTRIM(RTRIM(B)),50), D, W, RTRIM(LTRIM(isnull(X,''))) FROM Tmp_Peserta2 ORDER BY A
  OPEN Employee_Cursor
  SET @Count=0
  FETCH NEXT FROM Employee_Cursor INTO @No, @Nama, @Status, @Induk, @Sbg
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF isnull(@Induk,'')=0
    BEGIN
    IF Upper(@Status) In ('TK', 'K0', 'K1', 'K2', 'K3', 'K4', 'K5', 'W0', 'W1', 'W2', 'W3', 'W4', 'W5') 
    BEGIN
       SET @Sebagai=0
       SET @Count=0       
       EXEC gproc_GetNo 1, @Tgl_Masuk_Polis, 'No_Peserta', @CurrNo OUTPUT
     END
     ELSE IF Upper(@Status)='ISTRI' SET @Sebagai=1
     ELSE IF Upper(@Status)='ANAK'  SET @Sebagai=2
     ELSE IF Upper(@Status)='SUAMI' SET @Sebagai=3

     IF @Sebagai = 0 SET @Kawin=@Status
     ELSE IF @Sebagai in (1,3) SET @Kawin='TK'
     ELSE IF @Sebagai = 2 SET @Kawin='An'
     SET @NoPeserta = @CurrNo +'-'+ CAST(@Count as Varchar(1))
     SET @Count =  @Count +1
     END
     ELSE
     BEGIN
	  SET @NoPeserta = @CurrNo +'-'+ isnull(@Sbg,0)
     END
    --IF LEN(@Nama)>30  SET @Nama =dbo.func_GetCardName(@Nama,30)			-- sebelum perubahan 2018-05-21
    -- UPDATE Tmp_Peserta2 SET No_Peserta=@NoPeserta, B=@Nama WHERE A=@No
     UPDATE Tmp_Peserta2 SET No_Peserta=@NoPeserta WHERE A=@No		-- modified by Pribadi Pamungkas. 2018-05-21
   INSERT INTO Peserta (
  No_Proposal, No_Peserta, No_Prs, Nama, Card_Name, Tgl_Lahir, Status_Kawin, 
  Kelamin, Sebagai, No_Induk, Cabang, Grade, Nik, Rek_No, Rek_Nama, Bank_Nama, Bank_Cabang,
  Kelas, Adm_Keluar_Ok, Tgl_Masuk, Tgl_Mulai, Tgl_Selesai, Health_Dec, 
  Periode_Bayar, Mata_Uang, Faktor, Status, 
  Create_Date, Create_By, Change_Date, Change_By)
SELECT 
  @NoProposal, @NoPeserta, @NoPrs, B,  
  CASE												-- add by Pribadi Pamungkas. 2018-05-21
	WHEN LEN(B)>20 THEN dbo.func_GetCardName(B,20)
	ELSE B
  END,
  C, @Kawin, 
  Upper(E), @Sebagai, LEFT(No_Peserta,11)+'0',  P,  Q, R, S, T, U, V,
  J, 1, 
  Case WHEN isnull(Z,'')='' THEN @Tgl_Masuk_Polis ELSE Z END, Case WHEN isnull(Z,'')='' THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis, 0,
--Case WHEN isnull(Z,'')=''  THEN @Tgl_Masuk_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis, 0,
  @PeriodeBayar, @MataUang, @Faktor, 'I', 
  @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0100' AND Status='I')
     BEGIN
       SELECT @IdProduk   =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0100' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Benefit, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By) 
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0100', F, Case WHEN isnull(Z,'')='' THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')='' THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(F,0)<>0
END
     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0200' AND Status='I')
     BEGIN
       SELECT @IdProduk   =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0200' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Resiko, Benefit, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0200', G, H, I, Case WHEN isnull(Z,'')='' THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(I,0)<>0
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0302' AND Status='I')
     BEGIN
       SELECT @IdProduk   =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0302' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0302', J, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(J,'')<>''
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0301' AND Status='I')
     BEGIN
       SELECT @IdProduk   =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0301' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0301', K, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(K, '')<>''
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0303' AND Status='I')
     BEGIN
       SELECT @IdProduk   =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0303' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0303', L, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(L, '')<>''
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0304' AND Status='I')
     BEGIN
       SELECT @IdProduk   =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0304' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0304', M, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(M,'')<>''
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0305' AND Status='I')
     BEGIN
       SELECT @IdProduk  =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0305' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0305', N, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(N,'')<>''
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0308' AND Status='I')
     BEGIN
       SELECT @IdProduk  =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0308' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0308', O, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(O,'')<>''
     END

     IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0309' AND Status='I')
     BEGIN
       SELECT @IdProduk  =Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok='0309' AND Status='I'
       INSERT INTO Peserta_Produk (No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Kelas, Tgl_Ubah, Tgl_Mulai, Tgl_selesai, Create_Date, Create_By, Change_Date, Change_By)  
       SELECT @NoProposal, No_Peserta,  @IdProduk, '0309', Y, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, Case WHEN isnull(Z,'')=''  THEN @Tgl_Mulai_Polis ELSE Z END, @Tgl_Selesai_Polis , @Create_Date, @CreateBy, @Create_Date, @CreateBy FROM Tmp_Peserta2 WHERE A=@No AND isnull(Y,'')<>''
     END
     
     -- klinik
     DELETE FROM Klinik_Peserta WHERE No_Peserta=@NoPeserta
     
     INSERT INTO Klinik_Peserta (No_Peserta, Klinik1, Klinik2, Tgl_Ubah, [Status], Change_By, Change_Date, Create_By, Create_Date)  
     SELECT @NoPeserta, AA,  AB, @Create_Date, 'I', @CreateBy, @Create_Date, @CreateBy, @Create_Date FROM Tmp_Peserta2 WHERE A=@No
     
     -- no telp & email
     DELETE FROM dbo.Peserta_handphone WHERE No_Polis=@NoPolis AND No_Peserta=@NoPeserta
     
     INSERT INTO dbo.Peserta_handphone
             (No_Polis,
              No_Peserta,
              handphone1,
              Status,
              Create_By,
              Create_Date,
              email)
     SELECT @NoPolis, @NoPeserta, AC, 'I', @CreateBy, @Create_Date, AD FROM Tmp_Peserta2 WHERE A=@No
     
     FETCH NEXT FROM Employee_Cursor  INTO @No, @Nama, @Status, @Induk, @Sbg
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
-- EXEC proc_SamakanIdProduk @NoProposal


-- Hitung Jumlah Peserta
-- by: Pribadi Pamungkas, 2014-03-11
EXEC [dbo].[proc_SetJmlPeserta] @NoProposal
