USE [GrpInsuranceUAT]
GO
/****** Object:  StoredProcedure [dbo].[custPort_Proc_Info_Provider]    Script Date: 19/11/2018 11.32.15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- List Provider Portal
-- last modified by Pribadi Pamungkas. 2018-11-19 => sumber data diubah menjadi berasal dari core
-- catatan: view custPort_ind_Province perlu disesuaikan

ALTER PROCEDURE [dbo].[custPort_Proc_Info_Provider]
  @pi_no_Proposal VARCHAR(9),
	@pi_id_Provider SMALLINT = NULL, 
	@pi_provider_Name VARCHAR(255) = NULL, 
	@pi_address VARCHAR(255) = NULL, 
	@pi_city VARCHAR(255) = NULL, 
	@pi_province VARCHAR(255) = NULL, 
	@pi_country VARCHAR(100) = NULL, 
	@pi_op BIT = NULL, 
	@pi_ip BIT = NULL, 
	@pi_special_op BIT = NULL, 
	@pi_individual_ip BIT = NULL, 
	@pi_phone VARCHAR(255) = NULL, 
	@pi_notes VARCHAR(255) = NULL, 
	@pi_local_Provider BIT = NULL--, 
	--@pi_is_Aai BIT, 
	--@pi_is_Admedika BIT, 
	--@pi_is_Eli BIT
AS
BEGIN

	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	
	DECLARE
	  @v_aai BIT,
	  @v_admedika BIT,
	  @v_tradisional BIT
	  
	SELECT
		@v_aai = COALESCE(AAI_Ya,0),
		@v_admedika = COALESCE(AdMedika_Ya,0),
		@v_tradisional = COALESCE(show_card_ya,0)
	FROM
		dbo.Polis_Asuransi
	WHERE
	  No_Proposal = @pi_no_Proposal
	IF (@@RowCount > 0) -- jika ada hasil (no proposal ditemukan)
    BEGIN
      --SELECT
      --  [id_provider],
      --  [provider_name] AS provider_Name,
      --  [address],
      --  [city],
      --  province,
      --  [country],
      --  [op],
      --  [ip],
      --  [phone],
      --  [notes],
      --  [local_provider] AS local_Provider,
      --  is_Aai,
      --  is_Admedika,
      --  is_Eli
      --FROM
  	   -- custport_provider
      --WHERE
      --  --COALESCE(is_Aai,0) = @v_aai
      --  --AND COALESCE(is_Admedika,0) = @v_admedika
      --  --AND COALESCE(is_Eli,0) = @v_tradisional
      --  (COALESCE(is_Aai,0) = @v_aai OR COALESCE(@v_aai,0)=0)
      --  AND (COALESCE(is_Admedika,0) = @v_admedika OR COALESCE(@v_admedika,0)=0)
      --  AND (COALESCE(is_Eli,0) = @v_tradisional OR COALESCE(@v_tradisional,0)=0)
      --  AND ([id_provider] = @pi_id_Provider OR COALESCE(@pi_id_Provider, 0) = 0)
      --  AND ([provider_name] LIKE '%'+@pi_provider_Name+'%' OR COALESCE(@pi_provider_Name, '') = '')
      --  AND ([address] LIKE '%'+@pi_address+'%' OR COALESCE(@pi_address, '') = '')
      --  AND ([city] = @pi_city OR COALESCE(@pi_city, '') = '')
      --  AND (province = @pi_province OR COALESCE(@pi_province, '') = '')
      --  AND ([country] = @pi_country OR COALESCE(@pi_country, '') = '')
      --  AND ([op] = @pi_op OR (@pi_op IS NULL))
      --  AND ([ip] = @pi_ip OR (@pi_ip IS NULL))
      --  AND ([phone] LIKE '%'+@pi_phone+'%' OR COALESCE(@pi_phone, '') = '')
      --  AND ([notes] LIKE '%'+@pi_notes+'%' OR COALESCE(@pi_notes, '') = '')
      --  AND ([local_provider] = @pi_local_Provider OR (@pi_local_Provider IS NULL))

		SELECT
			rs.IDI AS id_provider,
			rs.Nama AS provider_Name,
			rs.Alamat AS address,
			rs.Kota AS city,
			w.Nama AS province,
			'Indonesia' AS country,
			COALESCE(kp.rj,0) AS op,
			COALESCE(kp.ri,0) AS ip,
			COALESCE(kp.rj_khusus,0) AS special_Op,
			COALESCE(kp.ri_individu,0) AS individual_Ip,
			'+' + rs.Telp1_kode_negara + ' ' + 
			CASE
				WHEN LEFT(COALESCE(rs.Telp1_kode_area,''),1)='0' THEN RIGHT(rs.Telp1_kode_area,LEN(rs.Telp1_kode_area)-1)
				ELSE COALESCE(rs.Telp1_kode_area,'')
			END +
			' ' + rs.Telp1_nomor AS phone,
			NULL AS notes,
			1 AS local_Provider,
			rs.Provider_AAI AS is_Aai,
			rs.Provider_Adm AS is_Admedika,
			rs.Provider AS is_Eli,
			rs.Longitude AS longitude,
			rs.Latitude AS latitude
		FROM
			[dbo].[Provider] AS rs INNER JOIN
			[dbo].[kontrak_provider] AS kp ON rs.IDI = kp.kode_provider LEFT JOIN
			[dbo].[Wilayah] AS w ON rs.Kode_Wilayah = w.Kode
		WHERE
			rs.Status = 'I'
			AND rs.IDI <> '9999'
			AND kp.tgl_mulai <= GETDATE()
			AND kp.tgl_selesai >= GETDATE()
			AND kp.Status = 'I'
			AND kp.id_payor <> '02'
			-- kondisi --
			AND (COALESCE(rs.Provider_AAI,0) = @v_aai OR COALESCE(@v_aai,0)=0)
			AND (COALESCE(rs.Provider_Adm,0) = @v_admedika OR COALESCE(@v_admedika,0)=0)
			AND (COALESCE(rs.Provider,0) = @v_tradisional OR COALESCE(@v_tradisional,0)=0)
			AND (rs.IDI = @pi_id_Provider OR COALESCE(@pi_id_Provider, 0) = 0)
			AND (rs.Nama LIKE '%'+@pi_provider_Name+'%' OR COALESCE(@pi_provider_Name, '') = '')
			AND (rs.Alamat LIKE '%'+@pi_address+'%' OR COALESCE(@pi_address, '') = '')
			AND (rs.Kota = @pi_city OR COALESCE(@pi_city, '') = '')
			AND (w.Nama = @pi_province OR COALESCE(@pi_province, '') = '')
			AND ('Indonesia' = @pi_country OR COALESCE(@pi_country, '') = '')
			AND (kp.rj = @pi_op OR (@pi_op IS NULL))
			AND (kp.ri = @pi_ip OR (@pi_ip IS NULL))
			AND (kp.rj_khusus = @pi_special_op OR (@pi_special_op IS NULL))
			AND (kp.ri_individu = @pi_individual_ip OR (@pi_individual_ip IS NULL))
			AND (rs.Telp1_kode_area + rs.Telp1_nomor LIKE '%'+@pi_phone+'%' OR COALESCE(@pi_phone, '') = '')
			--AND ([notes] LIKE '%'+@pi_notes+'%' OR COALESCE(@pi_notes, '') = '')
			--AND ([local_provider] = @pi_local_Provider OR (@pi_local_Provider IS NULL))
        
    END
  	
END
