USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_rep_daftar_peserta_asuransi]    Script Date: 03/20/2014 11:15:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE
 @pi_no_peserta  bit,
 @pi_cabang      varchar(8), 
 @pi_no_ref      varchar(12) --=  '2008.856763'--'2013.030689' --'2012.053505'--'2013.004051'

SET @pi_no_peserta = 1
SET @pi_cabang = ''
SET @pi_no_ref = '2014.001576'
 -- SUB KELOMPOK
   --0100		AJB
   --0200		ACC
   --0301		Rawat Jalan
   --0302		Rawat Inap
   --0304       Lahir
   --0305		kaca mata
   

DECLARE
   @table_temp TABLE (
    no_peserta    varchar(12),
    no_ref        varchar(11),
    no_proposal   varchar(9),
    setatus        varchar(5),
    sub_kelompok  varchar(6),
    nik           varchar(30),
    nama          varchar(30),
    grade		  smallint,
    pln           smallint,
    kelamin		  varchar(1),
    status_kawin  varchar(2),
    Umr		      smallint,
    jenis         smallint,
    sebagai       tinyint,
    benefit		  float,
    kelas         varchar(10),
    resiko        varchar(15),
    curr_premi    float)


    
 
DECLARE
  @table_output TABLE (
    no_peserta    varchar(12),
    no_ref        varchar(11),
    no_proposal   varchar(9),
    nik           varchar(30),
    nama          varchar(30),
    kelamin		  varchar(1),
    status_kawin  varchar(2),
    umur		  int,
    grade		  int,
    pln           int,
    jenis         smallint,
    sebagai       tinyint,
    premi1		  float,
    kelas1		  varchar(10),
    up1			  float,
    premi2		  float,
    kelas2		  varchar(10),
    up2			  float,
    premi3		  float,
    kelas3		  varchar(10),
    up3			  float,
    premi4		  float,
    kelas4		  varchar(10),
    up4			  float,
    premi5		  float,
    kelas5		  varchar(10),
    up5			  float,
    premi6		  float,
    kelas6		  varchar(10),
    up6			  float,
    total		  float,
    sub_total     float)
    
  DECLARE
  @temp_total TABLE(
  t_no_peserta varchar(12),
  t_total  Float)
    
    
    
 IF COALESCE (@pi_cabang,'')<>''   
 BEGIN
  INSERT INTO @table_temp (no_peserta, no_ref, no_proposal, setatus, sub_kelompok, nik, nama, grade, pln, kelamin, status_kawin, Umr, jenis, sebagai, benefit, kelas, resiko, curr_premi)
  SELECT  pd.No_Peserta, pd.No_Ref,  pd.No_Proposal ,pd.Status,  
  pd.Sub_Kelompok, p.Nik, p.Nama, p.Grade, p.Pln,
  pd.Kelamin, pd.Status_Kawin  , pd.Usia As Umr, pd.Jenis, pd.Sebagai,
  pd.Benefit, pd.kelas, pd.Resiko ,pd.Curr_Premi
  FROM Peserta_Produk_Detail pd  , Peserta p WHERE p.No_Peserta = pd.No_Peserta
  AND p.No_Proposal = pd.No_Proposal
  AND pd.No_Ref =@pi_no_ref
  AND p.cabang =@pi_cabang
  AND p.No_Peserta = '1991000001-0'
 ORDER BY pd.No_Peserta
 END ELSE
 BEGIN
 PRINT 'test'
  INSERT INTO @table_temp (no_peserta, no_ref, no_proposal, setatus, sub_kelompok, nik, nama, grade, pln, kelamin, status_kawin, Umr, jenis, sebagai, benefit, kelas, resiko, curr_premi)
  SELECT  pd.No_Peserta, pd.No_Ref,  pd.No_Proposal, pd.Status,  
  pd.Sub_Kelompok, p.Nik, p.Nama, p.Grade, p.Pln,
  pd.Kelamin, pd.Status_Kawin  , pd.Usia As Umr, pd.Jenis, pd.Sebagai,
  pd.Benefit, pd.kelas, pd.Resiko, pd.Curr_Premi
  FROM Peserta_Produk_Detail pd  , Peserta p WHERE p.No_Peserta = pd.No_Peserta
  AND p.No_Proposal = pd.No_Proposal
  AND pd.No_Ref = @pi_no_ref
  AND p.No_Peserta = '1991000001-0'
 ORDER BY pd.No_Peserta
 END




 INSERT INTO @table_output (no_peserta, no_ref, no_proposal , nik, nama, kelamin, status_kawin, umur, grade, pln, jenis, sebagai, sub_total)
 SELECT t.no_peserta, t.no_ref, t.no_proposal,  t.nik, t.nama, t.kelamin, t.status_kawin, t.umr, t.grade, t.pln, t.jenis, t.sebagai, ''
 FROM  @table_temp t LEFT JOIN peserta p
 ON t.No_peserta = p.No_peserta
 GROUP BY  t.no_peserta, t.no_ref, t.no_proposal, t.nik, t.nama, t.kelamin, t.status_kawin, t.umr, t.grade, t.pln, t.jenis, t.sebagai
 ORDER BY t.no_peserta   
 



UPDATE t
SET t.premi1 = COALESCE(k1.curr_premi, 0),
    t.kelas1 = k1.kelas,
    t.up1    = COALESCE(k1.benefit, 0)
    
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  WHERE sub_Kelompok = '0100' -- Ajb
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  
  
UPDATE t
SET t.premi2 = COALESCE(k1.curr_premi, 0),
    t.kelas2 = k1.kelas,
    t.up2    = COALESCE(k1.benefit, 0)
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  WHERE sub_Kelompok = '0200' -- Acc
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  
UPDATE t
SET t.premi3 = COALESCE(k1.curr_premi, 0),
    t.kelas3 = k1.kelas,
    t.up3    = COALESCE(k1.benefit, 0)
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  WHERE sub_Kelompok = '0301' -- Rawat Jalan
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  


UPDATE t
SET t.premi4 = COALESCE(k1.curr_premi, 0),
    t.kelas4 = k1.kelas,
    t.up4    = COALESCE(k1.benefit, 0)
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  WHERE sub_Kelompok = '0302' -- Rawat Inap
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  
UPDATE t
SET t.premi5 = COALESCE(k1.curr_premi, 0),
    t.kelas5 = k1.kelas,
    t.up5    = COALESCE(k1.benefit, 0)
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  WHERE sub_Kelompok = '0304' -- Lahir
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  
  
UPDATE t
SET t.premi6 = COALESCE(k1.curr_premi, 0),
    t.kelas6 = k1.kelas,
    t.up6    = COALESCE(k1.benefit, 0)
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  WHERE sub_Kelompok = '0305' -- kaca Mata
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  
  
UPDATE t
SET t.total  = premi1+premi2+premi3+premi4+premi5+premi6
FROM
  @table_output t
  LEFT JOIN
  (
  SELECT *
  FROM @table_temp
  ) k1 
  ON t.No_peserta = k1.no_peserta
  
  

  
  INSERT INTO @temp_total
  SELECT LEFT(no_peserta,10), SUM(total)
  FROM @table_output
  GROUP BY LEFT(no_peserta,10)
  
  
  
  UPDATE @table_output
  SET sub_total = t.t_total
  FROM @temp_total t
  WHERE  t.t_no_peserta+'-0' = no_peserta
  

  
 SELECT * FROM @table_temp
  SELECT * FROM @table_output
  
  