CREATE OR REPLACE FUNCTION public.insert_executive_by_day()
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $function$
DECLARE
    fecha1 date;
	fecha2 date;
	datos bigint;
	states bigint;
BEGIN

	SELECT (to_char(MAX(alr.created_at),'YYYY-MM-DD'))::date AS last_connection into fecha1
    FROM role_user ru
    JOIN "role" r ON ru.role_id = r.id
    JOIN "user" u ON ru.user_id = u.id
	JOIN auth_log_record alr ON alr.user_id = u.id
    WHERE(ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
		AND u.deleted = 'true'
		AND alr.is_denied = 'false';
	
	SELECT (TO_CHAR(NOW(), 'YYYY-MM-DD'))::DATE INTO fecha2;
	
	RAISE NOTICE 'fecha1: %', fecha1;
	RAISE NOTICE 'fecha2: %', fecha2;

	
	IF fecha1 = fecha2 THEN
		SELECT COUNT(*) into datos FROM control_login_sells;
		IF datos > 0 THEN
			SELECT COUNT (cl.state ) into states FROM control_login_sells cl WHERE cl.state =0;
			IF states > 0 THEN
					UPDATE control_login_sells SET state = 1 WHERE state = 0;
					
					INSERT INTO control_login_sells (id_user, cant, "state", "date",state_assignation)
					SELECT DISTINCT alr.user_id, 0, 1, fecha2,0
					FROM role_user ru
					JOIN "role" r ON ru.role_id = r.id
					JOIN "user" u ON ru.user_id = u.id
					JOIN auth_log_record alr ON alr.user_id = u.id
					WHERE (ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
						AND u.deleted = 'true'
						AND alr.is_denied = 'false'
						AND (to_char(alr.created_at, 'YYYY-MM-DD'))::date = fecha2
						AND NOT EXISTS (
						SELECT 1
						FROM control_login_sells cls
						WHERE cls.id_user = alr.user_id
						);	
			ELSE 
				INSERT INTO control_login_sells (id_user, cant, "state", "date",state_assignation)
					SELECT DISTINCT alr.user_id, 0, 1, fecha2,0
					FROM role_user ru
					JOIN "role" r ON ru.role_id = r.id
					JOIN "user" u ON ru.user_id = u.id
					JOIN auth_log_record alr ON alr.user_id = u.id
					WHERE (ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
						AND u.deleted = 'true'
						AND alr.is_denied = 'false'
						AND (to_char(alr.created_at, 'YYYY-MM-DD'))::date = fecha2
						AND NOT EXISTS (
						SELECT 1
						FROM control_login_sells cls
						WHERE cls.id_user = alr.user_id
						);	
			END IF ;
		ELSE 
			INSERT INTO control_login_sells (id_user, cant, "state", "date",state_assignation)
					SELECT DISTINCT alr.user_id, 0, 1, fecha2,0
					FROM role_user ru
					JOIN "role" r ON ru.role_id = r.id
					JOIN "user" u ON ru.user_id = u.id
					JOIN auth_log_record alr ON alr.user_id = u.id
					WHERE (ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
						AND u.deleted = 'true'
						AND alr.is_denied = 'false'
						AND (to_char(alr.created_at, 'YYYY-MM-DD'))::date = fecha2
						AND NOT EXISTS (
						SELECT 1
						FROM control_login_sells cls
						WHERE cls.id_user = alr.user_id
						);
		
		END IF ;
	ELSE 
		SELECT COUNT(*) into datos FROM control_login_sells;
		while datos>0 loop
			delete from control_login_sells;
			SELECT COUNT(*) into datos FROM control_login_sells;
		end loop;
		
		if fecha1 = fecha2 THEN
			SELECT COUNT(*) into datos FROM control_login_sells;
			IF datos > 0 THEN
				SELECT COUNT (cl.state ) into states FROM control_login_sells cl WHERE cl.state =0;
				IF states > 0 THEN
					UPDATE control_login_sells SET state = 1 WHERE state = 0;
				END IF ;
			END IF ;	
		ELSE
			INSERT INTO control_login_sells (id_user, cant, "state", "date",state_assignation)
			SELECT DISTINCT alr.user_id, 0, 1, fecha2,0
			FROM role_user ru
			JOIN "role" r ON ru.role_id = r.id
			JOIN "user" u ON ru.user_id = u.id
			JOIN auth_log_record alr ON alr.user_id = u.id
			WHERE (ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
				AND u.deleted = 'true'
				AND alr.is_denied = 'false'
				AND (to_char(alr.created_at, 'YYYY-MM-DD'))::date = fecha2
				AND NOT EXISTS (
				SELECT 1
				FROM control_login_sells cls
				WHERE cls.id_user = alr.user_id
				);
		END IF;
		
	END IF;
	RETURN true;
END;
$function$;

drop function public.insert_executive_by_day();

select public.insert_executive_by_day();


	CREATE OR REPLACE FUNCTION public.get_id_user()
	returns character varying
	LANGUAGE plpgsql
	AS $function$
	DECLARE
		cant integer;
		code character varying;
	BEGIN
		SELECT COUNT(*) INTO cant FROM control_login_sells WHERE state_assignation = 0;

		IF(cant > 0) THEN
			SELECT cl.id_user 
			FROM control_login_sells cl INTO code
			WHERE cl.id = (SELECT MIN(id) FROM control_login_sells WHERE state_assignation = 0);

			UPDATE control_login_sells SET state_assignation = 1 WHERE id_user = code;
			
			UPDATE control_login_sells
			SET cant = (SELECT SUM(cls.cant) + 1 FROM control_login_sells cls WHERE cls.id_user = code)
			WHERE id_user =code;

			RETURN code;

		ElSE
			UPDATE control_login_sells SET state_assignation = 0;

			SELECT cl.id_user 
			FROM control_login_sells cl INTO code
			WHERE cl.id = (SELECT MIN(id) FROM control_login_sells WHERE state_assignation = 0);
			
			UPDATE control_login_sells SET state_assignation = 1 WHERE id_user = code;
			
			UPDATE control_login_sells
			SET cant = (SELECT SUM(cls.cant) + 1 FROM control_login_sells cls WHERE cls.id_user = code)
			WHERE id_user =code;

			RETURN code;
		END IF;

	END;
	$function$;
	
	drop function get_id_user();
		select get_id_user();
		
	select count(*) from opportunity
	
	
	
	
