-- DROP FUNCTION public.sp_crud_tarjeta(varchar, varchar);

CREATE OR REPLACE FUNCTION public.sp_crud_tarjeta(
p_opc character varying,
p_id_persona character varying, 
p_numero_tarjeta character varying
)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
	entradas record;
	idp integer;
	v_id_asistencia integer;
	_fecha varchar;
	v_dia_marcacion varchar;
	
begin

	
	--insert into comandos(sn,comando,estado,datereg,datesend)
	--values ('BRIE223760052','DATA UPDATE user CardNo=	Pin='||p_id_persona||'		Password=	Group=1	StartTime=0	EndTime=0	Name=	SuperAuthorize=0	Disable=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8),to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

	--insert into comandos(sn,comando,estado,datereg,datesend)
	--values ('BRIE223760052','DATA UPDATE mulcarduser Pin='||p_id_persona||'		CardNo='||to_hex(p_numero_tarjeta::bigint)||'	LossCardFlag=0	CardType=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8),to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
	--insert into comandos(sn,comando,estado,datereg,datesend)
	--values ('BRIE223760052','DATA UPDATE userauthorize Pin='||p_id_persona||'	AuthorizeTimezoneId=1	AuthorizeDoorId=15','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8),to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
	--insert into comandos(sn,comando,estado,datereg,datesend)
	--values ('AJYX224460021','DATA UPDATE user CardNo=	Pin='||p_id_persona||'		Password=	Group=1	StartTime=0	EndTime=0	Name=	SuperAuthorize=0	Disable=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8),to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

	--insert into comandos(sn,comando,estado,datereg,datesend)
	--values ('AJYX224460021','DATA UPDATE mulcarduser Pin='||p_id_persona||'		CardNo='||to_hex(p_numero_tarjeta::bigint)||'	LossCardFlag=0	CardType=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8),to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
	--insert into comandos(sn,comando,estado,datereg,datesend)
	--values ('AJYX224460021','DATA UPDATE userauthorize Pin='||p_id_persona||'	AuthorizeTimezoneId=1	AuthorizeDoorId=15','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8),to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
	if p_opc='1' then -- Crea solo tarjeta

		--insert into comandos(sn,comando,estado,datereg)
		--values ('BRIE223760052','DATA UPDATE user CardNo=	Pin='||p_id_persona||'	Password=	Group=1	StartTime=0	EndTime=0	Name=	SuperAuthorize=0	Disable=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA UPDATE mulcarduser Pin='||p_id_persona||'	CardNo='||to_hex(p_numero_tarjeta::bigint)||'	LossCardFlag=0	CardType=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA UPDATE userauthorize Pin='||p_id_persona||'	AuthorizeTimezoneId=1	AuthorizeDoorId=15','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
	
		--insert into comandos(sn,comando,estado,datereg)
		--values ('AJYX224460021','DATA UPDATE user CardNo=	Pin='||p_id_persona||'	Password=	Group=1	StartTime=0	EndTime=0	Name=	SuperAuthorize=0	Disable=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA UPDATE mulcarduser Pin='||p_id_persona||'	CardNo='||to_hex(p_numero_tarjeta::bigint)||'	LossCardFlag=0	CardType=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA UPDATE userauthorize Pin='||p_id_persona||'	AuthorizeTimezoneId=1	AuthorizeDoorId=15','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
		
	end if;

	if p_opc='0' then -- Elimina solo tarjeta
	
		--insert into comandos(sn,comando,estado,datereg)
		--values ('BRIE223760052','DATA DELETE user Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		--insert into comandos(sn,comando,estado,datereg)
		--values ('BRIE223760052','DATA DELETE mulcarduser Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA DELETE userauthorize Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
		
		--insert into comandos(sn,comando,estado,datereg)
		--values ('AJYX224460021','DATA DELETE user Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		--insert into comandos(sn,comando,estado,datereg)
		--values ('AJYX224460021','DATA DELETE mulcarduser Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA DELETE userauthorize Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));


	end if;

	if p_opc='2' then -- Elimina usuario y tarjeta
	
		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA DELETE user Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA DELETE mulcarduser Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA DELETE userauthorize Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
		
		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA DELETE user Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA DELETE mulcarduser Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA DELETE userauthorize Pin='||p_id_persona||'','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));

	end if;

	if p_opc='3' then -- Crea solo usuario

		insert into comandos(sn,comando,estado,datereg)
		values ('BRIE223760052','DATA UPDATE user CardNo=	Pin='||p_id_persona||'	Password=	Group=1	StartTime=0	EndTime=0	Name=	SuperAuthorize=0	Disable=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
		insert into comandos(sn,comando,estado,datereg)
		values ('AJYX224460021','DATA UPDATE user CardNo=	Pin='||p_id_persona||'	Password=	Group=1	StartTime=0	EndTime=0	Name=	SuperAuthorize=0	Disable=0','0',to_char(current_date,'dd-mm-yyyy')||' '||substring(current_time::varchar,1,8));
	
	end if;

	_fecha := p_id_persona;
	return _fecha;	
	--return idp;
	/*EXCEPTION
	WHEN OTHERS THEN
        idp:=-1;
	return idp;*/
end;
$function$
;
