--http://127.0.0.1:8000/tarjeta/zkteco_logs
CREATE OR REPLACE FUNCTION public.sp_zkteco_log_paginado(
    p_numero_documento character varying,
    p_persona character varying,
    p_numero_tarjeta character varying,
    p_id_estado character varying,
    p_fecha_ini character varying,
    p_fecha_fin character varying,
    p_pagina character varying,
    p_limit character varying,
    p_ref refcursor)
  RETURNS refcursor AS
$BODY$

Declare
--v_id numeric;
--v_numinf character varying;
v_scad varchar;
v_campos varchar;
v_tabla varchar;
v_where varchar;
v_count varchar;
v_col_count varchar;
--v_perfil varchar;

Begin

	p_pagina=(p_pagina::Integer-1)*p_limit::Integer;
	
	v_campos=' 
	--t1.id,
	t1.cardno,t1.pin,
	--t1.verified,t1.doorid,t1.eventtype,t1.inoutstate,
	to_char(t1.time_second::timestamp,''dd-mm-yyyy'') as time_second,
	t2.tipo_documento,t2.numero_documento,
	t2.nombres||'' ''||t2.apellido_paterno||'' ''||t2.apellido_materno persona,
	(case when t1.eventtype=''0'' Then ''acceso normal'' when t1.eventtype in(''20'',''23'') Then ''bloqueado'' end)eventtype

	';

	v_tabla='from zkteco_logs t1
	inner join personas t2 on t1.pin::int=t2.id ';

	v_where = ' Where 1=1  ';

	If p_numero_tarjeta<>'' Then
	 v_where:=v_where||'And t1.cardno ilike ''%'||p_numero_tarjeta||'%'' ';
	End If;
	
	If p_numero_documento<>'' Then
	 v_where:=v_where||'And t2.numero_documento ilike ''%'||p_numero_documento||'%'' ';
	End If;

	If p_persona<>'' Then
	 v_where:=v_where||'And trim(t2.nombres)||'' ''||trim(t2.apellido_paterno)||'' ''||trim(t2.apellido_materno) ilike ''%'||p_persona||'%'' ';
	End If;

	If p_fecha_ini<>'' Then
	 v_where:=v_where||'And t1.time_second::timestamp >= '''||p_fecha_ini||' 00:00:00'' ';
	End If;
	If p_fecha_fin<>'' Then
	 v_where:=v_where||'And t1.time_second::timestamp <= '''||p_fecha_fin||' 23:59:59'' ';
	End If;

	If p_id_estado<>'0' Then
		if p_id_estado='1' Then
			v_where:=v_where||'And t1.eventtype = ''0'' ';
		Elseif p_id_estado='2' Then
			v_where:=v_where||'And t1.eventtype in (''20'',''23'') ';
		end IF;
	End If;
	
	
	EXECUTE ('select count(1) from( SELECT distinct '||v_campos||' '||v_tabla||v_where||')R') INTO v_count;
	v_col_count:=' ,'||v_count||' as TotalRows ';

	If v_count::Integer > p_limit::Integer then
		v_scad:='SELECT distinct '||v_campos||v_col_count||v_tabla||v_where||' Order By t2.numero_documento Desc LIMIT '||p_limit||' OFFSET '||p_pagina||';'; 
	else
		v_scad:='SELECT distinct '||v_campos||v_col_count||v_tabla||v_where||' Order By t2.numero_documento Desc;'; 
	End If;
	
	--Raise Notice '%',v_scad;
	Open p_ref For Execute(v_scad);
	Return p_ref;
End

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
