USE [AJK]
GO
/****** Object:  StoredProcedure [dbo].[proc_get_premi_web]    Script Date: 02/25/2014 11:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- SP Get Premi untuk web (validasi & tanpa print)
-- By: Pribadi Pamungkas, 2013-04-12
-- Last edited by: Pribadi Pamungkas, 2013-08-28
ALTER PROCEDURE [dbo].[proc_get_premi_web]
  @pi_id_produk     varchar(12),
  @pi_umur          int,
  @pi_masa          int,
  @pi_up            float,
  @pi_prosen_bunga  float,
  @po_premi         float  OUTPUT,
  @po_message varchar(100) OUTPUT
AS
BEGIN
  DECLARE
    @p_umur          int,
    @p_masa          int,
    @p_up            float,
    @p_prosen_bunga  float,
    @p_tabel_rate_ya bit,
    @p_rate          float,
    @p_premi         float,
    @p_umur_awal_min   float,
    @p_umur_awal_max   float,
    @p_umur_akhir_min  float,
    @p_umur_akhir_max  float,
    @p_masa_awal_min   float,
    @p_masa_awal_max   float,
    @p_masa_akhir_min  float,
    @p_masa_akhir_max  float,
    @p_up_awal_min     float,
    @p_up_awal_max     float,
    @p_up_akhir_min    float,
    @p_up_akhir_max    float,
    @p_bunga_awal_min  float,
    @p_bunga_awal_max  float,
    @p_bunga_akhir_min float,
    @p_bunga_akhir_max float,
    @mkthn             FLOAT,
    @max_umur          INT,
    
    -- cek apakah umur + masa kontrak melebihi max_umur
    SELECT
        @mkthn = CASE 
                   WHEN satuan_masa='BLN' THEN @p_masa / 12
                   ELSE @p_masa
                 END,
        @max_umur = max_umur
    FROM produk WHERE id_produk=@pi_id_produk
    --PRINT @mkthn + @p_umur
    IF (@mkthn + @p_umur) > @max_umur
      BEGIN
			  SET @po_message= 'Usia/masa kontrak melebihi ketentuan.'
			  RETURN 1
		  END
      

    --SELECT * FROM produk_tabel WHERE id_produk='2013.0000006'--@pi_id_produk
  
    SELECT 
    @p_umur_awal_min=Min(umur_awal), 
    @p_umur_awal_max=Max(umur_awal),
     
    @p_umur_akhir_min=Min(umur_akhir),
    @p_umur_akhir_max=Max(umur_akhir),
    
    @p_masa_awal_min = Min(masa_awal), 
    @p_masa_awal_max = Max(masa_awal), 
    
    @p_masa_akhir_min = Min(masa_akhir),
    @p_masa_akhir_max = Max(masa_akhir),
    
    @p_up_awal_min = Min(up_awal), 
    @p_up_awal_max = Max(up_awal),
     
    @p_up_akhir_min = Min(up_akhir), 
    @p_up_akhir_max = Max(up_akhir),
    
    @p_bunga_awal_min = Min(bunga_awal), 
    @p_bunga_awal_max = Max(bunga_awal),
    
    @p_bunga_akhir_min = Min(bunga_akhir), 
    @p_bunga_akhir_max = Max(bunga_akhir)
    
    FROM produk_tabel WHERE id_produk=@pi_id_produk
    IF (@@ERROR <> 0) OR (@@RowCount = 0)
		BEGIN
			SET @po_message= 'Tabel premi belum ada. Silakan hubungi bagian IT.'
			RETURN 1
		END
		
    IF @p_umur_awal_min<>'-1' and @p_umur_akhir_max<>'-1'
    BEGIN
      IF @pi_umur<@p_umur_awal_min or @pi_umur>@p_umur_akhir_max 
      BEGIN
        SET @po_message = 'Umur harus diantara '+CAST(@p_umur_awal_min as varchar)+' sampai '+ CAST(@p_umur_akhir_max as varchar)
      END;
    END;
    
    IF @p_masa_awal_min<>'-1' and @p_masa_akhir_max<>'-1'
    BEGIN
      IF @pi_masa<@p_masa_awal_min or @pi_masa>@p_masa_akhir_max 
      BEGIN
        SET @po_message = 'Masa Kontrak harus diantara '+CAST(@p_masa_awal_min as varchar)+' sampai '+ CAST(@p_masa_akhir_max as varchar)
      END;
    END;
    
    IF @p_bunga_awal_min<>'-1' and @p_bunga_akhir_max<>'-1'
    BEGIN
      IF @pi_prosen_bunga<@p_bunga_awal_min or @pi_prosen_bunga>@p_bunga_akhir_max 
      BEGIN
        SET @po_message = 'Bunga harus diantara '+CAST(@p_bunga_awal_min as varchar)+' sampai '+ CAST(@p_bunga_akhir_max as varchar)
      END;
    END;
    
--  @pi_masa          int,
--  @pi_up            float,
  

  -- Perlu diperhatikan bahwa ada beberapa kombinasi tabel.
  -- - Ada produk yang tidak menggunakan umur untuk menentukan premi
  -- - Ada produk yang tidak menggunakan prosen bunga untuk menentukan premi
  -- - dsb.
  -- Cara yang paling mudah untuk mengetahui parameter apa saja yang dipakai
  -- untuk menentukan premi adalah cek isi field di tabel.
  -- - Jika field hanya berisi nilai -1 maka field tersebut tidak dipakai.

  -- Jika di field umur_awal ada nilai -1 maka dapat dipastikan umur bukan parameter
  -- untuk menghitung rate premi
  IF EXISTS (
    SELECT * FROM produk_tabel
    WHERE (id_produk = @pi_id_produk) AND (umur_awal = -1)
  )
  BEGIN
    SET @p_umur = -1
    --PRINT 'Umur tidak berpengaruh pada perhitungan premi'
  END
  ELSE
  BEGIN
    SET @p_umur = COALESCE(@pi_umur, -1)
    --PRINT 'Umur berpengaruh pada perhitungan premi'
  END

  -- Jika di field masa_awal ada nilai -1 maka dapat dipastikan masa bukan parameter
  -- untuk menghitung rate premi
  IF EXISTS (
    SELECT * FROM produk_tabel
    WHERE (id_produk = @pi_id_produk) AND (masa_awal = -1)
  )
  BEGIN
    SET @p_masa = -1
    --PRINT 'Masa tidak berpengaruh pada perhitungan premi'
  END
  ELSE
  BEGIN
    SET @p_masa = COALESCE(@pi_masa, -1)
    --PRINT 'Masa berpengaruh pada perhitungan premi'
  END

  -- Jika di field up_awal ada nilai -1 maka dapat dipastikan UP bukan parameter
  -- untuk menghitung rate premi
  IF EXISTS (
    SELECT * FROM produk_tabel
    WHERE (id_produk = @pi_id_produk) AND (up_awal = -1)
  )
  BEGIN
    SET @p_up = -1
    --PRINT 'UP tidak berpengaruh pada perhitungan premi'
  END
  ELSE
  BEGIN
    SET @p_up = COALESCE(@pi_up, -1)
    --PRINT 'UP berpengaruh pada perhitungan premi'
  END

  -- Jika di field bunga_awal ada nilai -1 maka dapat dipastikan bunga bukan parameter
  -- untuk menghitung rate premi
  IF EXISTS (
    SELECT * FROM produk_tabel
    WHERE (id_produk = @pi_id_produk) AND (bunga_awal = -1)
  )
  BEGIN
    SET @p_prosen_bunga = -1
    --PRINT 'Prosen bunga tidak berpengaruh pada perhitungan premi'
  END
  ELSE
  BEGIN
    SET @p_prosen_bunga = COALESCE(@pi_prosen_bunga, -1)
    --PRINT 'Prosen bunga berpengaruh pada perhitungan premi'
  END

  SELECT @p_rate = rate
  FROM produk_tabel
  WHERE 
    (id_produk = @pi_id_produk) AND
    (umur_awal <= @p_umur) AND
    (umur_akhir >= @p_umur) AND
    (masa_awal <= @p_masa) AND
    (masa_akhir >= @p_masa) AND
    (up_awal <= @p_up) AND
    (up_akhir >= @p_up) AND
    (bunga_awal <= @p_prosen_bunga) AND
    (bunga_akhir >= @p_prosen_bunga)
    
  -- Ambil flag di tabel produk untuk menentukan cara perhitungan premi
  SELECT @p_tabel_rate_ya = tabel_rate_ya
  FROM produk
  WHERE (id_produk = @pi_id_produk)

  -- Jika @p_tabel_rate_ya = 1 maka perhitungan premi adalah sebagai berikut:
  --   premi = rate * UP /1000
  -- else
  --   premi = rate
  IF @p_tabel_rate_ya = 1
  BEGIN
    SET @p_premi = @p_rate * @pi_up / 1000
    --PRINT 'Tabel mengembalikan rate'
  END
  ELSE
  BEGIN
    SET @p_premi = @p_rate
    --PRINT 'Tabel mengembalikan premi'
  END

  --PRINT @p_rate
  --PRINT @p_premi
  
  SET @po_premi = ROUND(@p_premi,0)

  RETURN 0
END


