USE [IndInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_text_info_curr_polis_biodata_web]    Script Date: 11/05/2012 22:55:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_text_info_curr_polis_biodata_web]
  @pi_no_polis varchar(12)
AS
BEGIN
  DECLARE @Image nVarchar(4000)
  SET @Image = ''
  
  SELECT     
    @image =          'TERTANGGUNG' 
  +CHAR(13)+CHAR(10)+ '- Nama               : ' + pt.nama_Lengkap 
  +CHAR(13)+CHAR(10)+ '- Tgl.lahir          : ' + dbo.func_Datetostr(pt.Tgl_lahir) 
  +CHAR(13)+CHAR(10)+ '- Usia               : ' + CAST(dbo.func_GetUmurLife(pt.tgl_lahir, p.tgl_mulai) AS varchar(3)) 
  +CHAR(13)+CHAR(10)+ '- Jenis kelamin      : ' + dbo.func_get_jenis_kelamin(pt.id_jenis_kelamin) 
  +CHAR(13)+CHAR(10)+ '- No Hp              : ' + COALESCE(pt.hp, '') 
  +CHAR(13)+CHAR(10)+ '- Email              : ' + COALESCE(pt.email, '') 
  +CHAR(13)+CHAR(10)+ '- Alamat Koresponden : ' + COALESCE(pta.alamat, '')
  
                    + CASE 
                        WHEN COALESCE(pta.no_rt, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'RT.' + COALESCE(pta.no_rt, '') 
                        ELSE '' 
                      END
                    + CASE WHEN COALESCE(pta.no_rw, '') <> ''     THEN ' RW.' + COALESCE(pta.no_rw, '') ELSE '' END                    
                    
                    + CASE 
                        WHEN COALESCE(pta.Kelurahan, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'Kel. ' + COALESCE(pta.Kelurahan, '') 
                        ELSE '' 
                      END
                    + CASE WHEN COALESCE(pta.Kecamatan, '') <> '' THEN ' Kec. ' + COALESCE(pta.Kecamatan, '') ELSE '' END

                    + CASE 
                        WHEN COALESCE(pta.Kota, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'Kota ' + COALESCE(pta.Kota, '') 
                        ELSE '' 
                      END
                    + CASE WHEN COALESCE(pta.id_kd_pos, '') <> '' THEN ' ' + COALESCE(pta.id_kd_pos, '') ELSE '' END

                    + CASE 
                        WHEN COALESCE(pta.Telp, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'Telp ' + COALESCE(pta.Telp, '') 
                        ELSE '' 
                      END

  +CHAR(13)+CHAR(10)
  +CHAR(13)+CHAR(10)+ 'PEMEGANG POLIS'
  +CHAR(13)+CHAR(10)+ '- Nama               : ' + pp.nama_Lengkap 
  +CHAR(13)+CHAR(10)+ '- Tgl.lahir          : ' + dbo.func_Datetostr(pp.Tgl_lahir)
  +CHAR(13)+CHAR(10)+ '- Usia               : ' + CAST(dbo.func_GetUmurLife(pp.tgl_lahir, p.tgl_mulai) AS varchar(3))
  +CHAR(13)+CHAR(10)+ '- Jenis kelamin      : ' + dbo.func_get_jenis_kelamin(pp.id_jenis_kelamin)
  +CHAR(13)+CHAR(10)+ '- No Hp              : ' + COALESCE(pp.hp, '')
  +CHAR(13)+CHAR(10)+ '- Email              : ' + COALESCE(pp.email, '') 
  +CHAR(13)+CHAR(10)+ '- Alamat Koresponden : ' + COALESCE(ppa.alamat, '') 
  
                    + CASE 
                        WHEN COALESCE(ppa.no_rt, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'RT.' + COALESCE(ppa.no_rt, '') 
                        ELSE '' 
                      END 
                    + CASE WHEN COALESCE(ppa.no_rw, '') <> '' THEN ' RW.' + COALESCE(ppa.no_rw, '') ELSE '' END 
                    
                    + CASE 
                        WHEN COALESCE(ppa.Kelurahan, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'Kel. ' + COALESCE(ppa.Kelurahan, '') 
                        ELSE '' 
                      END 
                    + CASE WHEN COALESCE(ppa.Kecamatan, '') <> '' THEN ' Kec. ' + COALESCE(ppa.Kecamatan, '') ELSE '' END 
                    
                    + CASE 
                        WHEN COALESCE(ppa.Kota, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'Kota ' + COALESCE(ppa.Kota, '') 
                        ELSE '' 
                      END 
                    + CASE WHEN COALESCE(ppa.id_kd_pos, '') <> '' THEN ' ' + COALESCE(ppa.id_kd_pos, '') ELSE '' END
                    
                    + CASE 
                        WHEN COALESCE(ppa.Telp, '') <> '' THEN 
  +CHAR(13)+CHAR(10)+ '                       ' + 'Telp ' + COALESCE(ppa.Telp,'') 
                        ELSE '' 
                      END
  +CHAR(13)+CHAR(10)
  +CHAR(13)+CHAR(10)
  FROM
    (
			select * from polis
			where id_polis = dbo.func_get_id_polis_per_tanggal(no_polis, dbo.func_fixed_date(getdate()), 0)
			and no_polis = @pi_no_polis
		) p
    INNER JOIN
    plan_asuransi pa 
    ON p.id_plan_dasar = pa.id_plan 
    INNER JOIN
    periode_bayar pb 
    ON p.id_periode_bayar = pb.id_periode_bayar 
    INNER JOIN
    cara_bayar_nasabah cbn 
    ON p.id_cara_bayar_nasabah = cbn.id_cara_bayar_nasabah 
    INNER JOIN
    valuta v
    ON p.id_valuta = v.id_valuta 
    LEFT JOIN
    polis_pemegang pp
    ON p.id_polis = pp.id_polis 
    LEFT JOIN
    polis_pemegang_alamat ppa
    ON (pp.id_jenis_alamat_korespondensi = ppa.id_jenis_alamat) AND
       (p.id_polis = ppa.id_polis)
    LEFT JOIN
    polis_tertanggung pt
    ON p.id_polis = pt.id_polis 
    LEFT JOIN
    polis_tertanggung_alamat pta 
    ON (pt.id_jenis_alamat_korespondensi = pta.id_jenis_alamat) AND
       (p.id_polis = pta.id_polis)
    LEFT JOIN
    agen a
    ON p.id_agen = a.id_agen 
    INNER JOIN
    cabang c
    ON p.id_cabang = c.id_cabang
/*
  WHERE 
    (p.no_polis = @pi_no_polis) AND
    (p.aktif_ya = 1)
*/
  
--  DECLARE @urut int 

--  SET @Image = @Image 
--                    + 'TERMASLAHAT'
--  +CHAR(13)+CHAR(10)
  
--  DECLARE Master_Cursor CURSOR FOR
--  SELECT no_urut
--  FROM 
--    polis_termaslahat pt
--    INNER JOIN 
--    hubungan h
--    ON pt.id_hubungan = h.id_hubungan 
--    INNER JOIN
--    (
--			select * from polis
--			where id_polis = dbo.func_get_id_polis_per_tanggal(no_polis, dbo.func_fixed_date(getdate()), 0)
--			and no_polis = @pi_no_polis
--		) p
--    ON pt.id_polis = p.id_polis  
--/*
--  WHERE 
--    (p.no_polis = @pi_no_polis) AND
--    (p.aktif_ya = 1)
--*/    
--  SET @image = @image 
--                    + '   No. Nama Termaslahat                                  Hubungan            Tgl Lahir'
--  +CHAR(13)+CHAR(10)+ ' ----- ------------------------------------------------- ------------------- ---------'
--  +CHAR(13)+CHAR(10)

--  OPEN Master_Cursor;
--  FETCH NEXT FROM Master_Cursor INTO @urut;

--  WHILE @@FETCH_STATUS = 0
--  BEGIN
--    SELECT @image = @image +
--      dbo.func_GetJustify(CAST(pt.no_urut AS varchar(3)),3,5,' ') + '. ' + 
--      dbo.func_GetJustify(pt.nama_ahli_waris, 1, 50,' ') +
--      dbo.func_GetJustify(h.deskripsi, 1, 20, ' ') + 
--      CASE
--        WHEN COALESCE(pt.tgl_lahir, '') <> '' THEN
--          dbo.func_GetJustify(dbo.func_date_format('mm-dd-yyyy', pt.tgl_lahir), 1, 10, ' ')
--        ELSE
--          ''
--      END
--      +CHAR(13)+CHAR(10)
--    FROM 
--      polis_termaslahat pt
--      INNER JOIN 
--      hubungan h
--      ON pt.id_hubungan = h.id_hubungan 
--      INNER JOIN
--			(
--				select * from polis
--				where id_polis = dbo.func_get_id_polis_per_tanggal(no_polis, dbo.func_fixed_date(getdate()), 0)
--				and no_polis = @pi_no_polis
--			) p
--      ON pt.id_polis = p.id_polis  
--    WHERE 
--/*
--      (p.no_polis = @pi_no_polis) AND
--      (p.aktif_ya = 1) AND
--*/
--      (pt.no_urut = @urut)

--    FETCH NEXT FROM Master_Cursor INTO @urut;
--  END;
  
--  CLOSE Master_Cursor;
--  DEALLOCATE Master_Cursor;

  SELECT Memo = CAST(@Image as Text) 
END