SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas, 2014-10-23
CREATE FUNCTION list_imaging
(	
	@p_submitted BIT, 
	@p_id_user VARCHAR(4),
	@p_submit_date_1 DATETIME,
	@p_submit_date_2 DATETIME,
	@p_row_start BIGINT,
	@p_row_end BIGINT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
      TOP 99.99999 PERCENT
      *
    FROM (
          SELECT
            ROW_NUMBER() OVER (ORDER BY r.submitted_date, r.create_date) AS RowNum, 
            r.id_register,
            r.[guid],
            r.id_transaction_type,
            t.[description] AS transaction_type,
            r.no_spaj,
            r.no_polis,
            r.ref_no,
            dokfile.jumlah,
            r.[status],
            r.id_unit_pelayanan_create,
            r.create_by,
            u1.Nama AS create_name,
            r.create_date,
            r.change_by,
            u2.Nama AS change_name,
            r.change_date,
            r.submitted,
            r.submitted_by,
            u3.Nama AS submitted_name,
            r.submitted_date
          FROM
            dbo.register AS r INNER JOIN
            dbo.transaction_type AS t ON r.id_transaction_type = t.id_transaction_type LEFT JOIN 
            IndInsurance.dbo.gUser_Name u1 ON u1.Id_User=r.create_by LEFT JOIN 
            IndInsurance.dbo.gUser_Name u2 ON u2.Id_User=r.change_by LEFT JOIN 
            IndInsurance.dbo.gUser_Name u3 ON u3.Id_User=r.submitted_by LEFT JOIN
            (SELECT [guid], COUNT(*) AS jumlah FROM dbo.document_files GROUP BY guid) AS dokfile ON r.guid = dokfile.guid
          WHERE 
            r.status='I' 
            AND r.submitted=@p_submitted
            AND r.id_unit_pelayanan_create IN (SELECT id_unit_pelayanan FROM dbo.func_get_user_hirarki_unit_pelayanan(@p_id_user))
		        AND (r.submitted_date>=@p_submit_date_1 OR @p_submitted=0)
		        AND (r.submitted_date<=@p_submit_date_2 OR @p_submitted=0)
          ) AS list
    WHERE
      RowNum >= @p_row_start
      AND RowNum < @p_row_end
    ORDER BY RowNum
)
GO
