SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Pribadi Pamungkas
-- Create date: 2014-08-05
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION func_get_alamat_korespondensi_test
(	
	@pi_no_polis VARCHAR(11)
)
RETURNS
  @po_ret_table TABLE (
        baris_1     VARCHAR(100),
        baris_2     VARCHAR(100),
        baris_3     VARCHAR(100),
        baris_4     VARCHAR(100)
  )
AS
BEGIN

  DECLARE 
    @p_alamat_gabung varchar(4000),
    @p_alamat_1    varchar(50),
    @p_alamat_2    varchar(50),
    @p_alamat_3    varchar(50),
    @p_alamat      varchar(200),
    @p_no_rt       varchar(5),
    @p_no_rw       varchar(5),
    @p_kelurahan   varchar(50),
    @p_kecamatan   varchar(50),    
    @p_id_polis    varchar(12),
    @p_jumlah_karakter INT
  
  SET @p_jumlah_karakter = 100  -- Jumlah maksimal karakter di tiap kolom (kecuali kolom 4)

  SELECT @p_id_polis = id_polis 
  FROM polis 
  WHERE id_polis = dbo.func_get_id_polis_per_tanggal(@pi_no_polis, dbo.func_fixed_date(getdate()), 0)

  SELECT 
    @p_alamat_1    = ppa.alamat_1,
    @p_alamat_2    = ppa.alamat_2,
    @p_alamat_3    = ppa.alamat_3,
    @p_alamat      = ppa.alamat,
    @p_no_rt       = ppa.no_rt,
    @p_no_rw       = ppa.no_rw,
    @p_kelurahan   = ppa.kelurahan,
    @p_kecamatan   = ppa.kecamatan
  FROM 
    polis_pemegang pp
    INNER JOIN
    polis_pemegang_alamat ppa
    ON (pp.id_polis = ppa.id_polis) AND 
       (pp.id_jenis_alamat_korespondensi = ppa.id_jenis_alamat)
    LEFT JOIN
    provinsi p
    ON ppa.id_provinsi = p.id_provinsi
    LEFT JOIN
    negara n
    on ppa.id_negara = n.id_negara 
  WHERE 
    (pp.id_polis = @p_id_polis)

  SET @p_alamat_gabung = ''

  -- Alamat
  IF (COALESCE(@p_alamat_1, '') <> '')
  BEGIN
    SET @p_alamat_gabung = @p_alamat_gabung + LTRIM(RTRIM(@p_alamat_1))
  END
  
  IF (COALESCE(@p_alamat_2, '') <> '')
  BEGIN
    IF (COALESCE(@p_alamat_gabung, '') <> '')
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + CHAR(13) + CHAR(10) + LTRIM(RTRIM(@p_alamat_2))
    END
    ELSE
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + LTRIM(RTRIM(@p_alamat_2))
    END
  END

  IF (COALESCE(@p_alamat_3, '') <> '')
  BEGIN
    IF (COALESCE(@p_alamat_gabung, '') <> '')
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + CHAR(13) + CHAR(10) + LTRIM(RTRIM(@p_alamat_3))
    END
    ELSE
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + LTRIM(RTRIM(@p_alamat_3))
    END
  END
  
  IF (COALESCE(@p_alamat_gabung, '') = '')
  BEGIN
    SET @p_alamat_gabung = @p_alamat_gabung + LTRIM(RTRIM(@p_alamat))
  END
  -- End: Alamat
  
  -- RT dan RW
  IF (COALESCE(@p_no_rt, '') <> '')
  BEGIN
    IF (COALESCE(@p_alamat_gabung, '') <> '')
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + CHAR(13) + CHAR(10) + 'RT.' + dbo.func_get_aligned_string('R', LTRIM(RTRIM(@p_no_rt)), 3, '0')
    END
    ELSE
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + 'RT.' + dbo.func_get_aligned_string('R', LTRIM(RTRIM(@p_no_rt)), 3, '0')
    END
  END
  
  IF (COALESCE(@p_no_rw, '') <> '')
  BEGIN
    IF (COALESCE(@p_no_rt, '') <> '') SET @p_alamat_gabung = @p_alamat_gabung + ' '
    ELSE SET @p_alamat_gabung = @p_alamat_gabung + CHAR(13) + CHAR(10)
    
    SET @p_alamat_gabung = @p_alamat_gabung + 'RW.' + dbo.func_get_aligned_string('R', LTRIM(RTRIM(@p_no_rw)), 3, '0')
  END
  -- End: RT dan RW

  -- Kelurahan dan Kecamatan
  IF (COALESCE(@p_kelurahan, '') <> '')
  BEGIN
    IF (COALESCE(@p_alamat_gabung, '') <> '')
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + CHAR(13) + CHAR(10) + 'KEL.' + LTRIM(RTRIM(@p_kelurahan))
    END
    ELSE
    BEGIN
      SET @p_alamat_gabung = @p_alamat_gabung + 'KEL.' + LTRIM(RTRIM(@p_kelurahan))
    END
  END
  
  IF (COALESCE(@p_kecamatan, '') <> '')
  BEGIN
    IF (COALESCE(@p_kelurahan, '') <> '') SET @p_alamat_gabung = @p_alamat_gabung + ' '
    ELSE SET @p_alamat_gabung = @p_alamat_gabung + CHAR(13) + CHAR(10)

    SET @p_alamat_gabung = @p_alamat_gabung + 'KEC.' + LTRIM(RTRIM(@p_kecamatan))
  END
  -- End: Kelurahan dan Kecamatan


  SET @p_alamat_gabung = CAST(@p_alamat_gabung as text);

  
  ------------ pecah jadi 4 --------------
  WITH data (col) AS (
    SELECT @p_alamat_gabung
  )

  INSERT INTO @po_ret_table
  SELECT
    RTRIM(col1) AS col1, RTRIM(col2) AS col2, col3, col4
  FROM data
    CROSS APPLY (
      SELECT
        NULLIF((@p_jumlah_karakter + 2) - CHARINDEX(' ', REVERSE(LEFT(col, (@p_jumlah_karakter + 1)))), 0)
    ) AS x1 (first_space_pos)
    CROSS APPLY (
      SELECT
        LEFT(col, ISNULL(first_space_pos, (@p_jumlah_karakter + 0))),
        LTRIM(NULLIF(SUBSTRING(col, ISNULL(first_space_pos, (@p_jumlah_karakter + 1)), 4000), ''))
    ) AS x2 (col1, col23)
    CROSS APPLY (
      SELECT
        NULLIF((@p_jumlah_karakter + 2) - CHARINDEX(' ', REVERSE(LEFT(col23, (@p_jumlah_karakter + 1)))), 0)
    ) AS x3 (second_space_pos)
    CROSS APPLY (
      SELECT
        LEFT(col23, ISNULL(second_space_pos, (@p_jumlah_karakter + 0))),
        LTRIM(NULLIF(SUBSTRING(col23, ISNULL(second_space_pos, (@p_jumlah_karakter + 1)), 4000), ''))
    ) AS x4 (col2, col34)
    CROSS APPLY (
      SELECT
        NULLIF((@p_jumlah_karakter + 2) - CHARINDEX(' ', REVERSE(LEFT(col34, (@p_jumlah_karakter + 1)))), 0)
    ) AS x5 (third_space_pos)
    CROSS APPLY (
      SELECT
        LEFT(col34, ISNULL(third_space_pos, (@p_jumlah_karakter + 0))),
        LTRIM(NULLIF(SUBSTRING(col34, ISNULL(third_space_pos, (@p_jumlah_karakter + 1)), 4000), ''))
    ) AS x6 (col3, col4)



  RETURN
END
