---------------------------------------------------------------------------------------------------------------------------------------------
--En DBGRIFO
---------------------------------------------------------------------------------------------------------------------------------------------

-- Function: sp_interfaz_insertatran(character varying, character varying, character varying, numeric, numeric, numeric, character varying, character varying, character, integer)

-- DROP FUNCTION sp_interfaz_insertatran(character varying, character varying, character varying, numeric, numeric, numeric, character varying, character varying, character, integer);

CREATE OR REPLACE FUNCTION sp_interfaz_insertatran(
    p_cara character varying,
    p_manguera character varying,
    p_producto character varying,
    p_soles numeric,
    p_galones numeric,
    p_precio numeric,
    p_fecha character varying,
    p_hora character varying,
    p_estado character,
    p_facturado integer)
  RETURNS boolean AS
$BODY$
declare result boolean;
BEGIN
	/*
    insert into "TRAN"(cara,manguera,producto,soles,galones,precio,fecha,hora,estado,facturado ) 
    values(p_cara,p_manguera,p_producto,p_soles,p_galones,p_precio,p_fecha,p_hora,p_estado,p_facturado);
	*/
	insert into "TRAN"(cara,manguera,producto,soles,galones,precio,fecha,hora) 
    values(p_cara,p_manguera,p_producto,p_soles,p_galones,p_precio,p_fecha,p_hora);
	IF FOUND THEN
		result= TRUE;
	ELSE
		result= FALSE; 
	END IF;
return result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_interfaz_insertatran(character varying, character varying, character varying, numeric, numeric, numeric, character varying, character varying, character, integer)
  OWNER TO postgres;

---------------------------------------------------------------------------------------------------------------------------------------------
--En DBGRIFO  tabla INTERACCION
---------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER interfaz_cambioturno
  AFTER UPDATE
  ON "INTERACCION"
  FOR EACH ROW
  EXECUTE PROCEDURE interfaz_cambioturnosistema();

---------------------------------------------------------------------------------------------------------------------------------------------
--En DBGRIFO crear TRIGGER
---------------------------------------------------------------------------------------------------------------------------------------------

-- Function: interfaz_cambioturnosistema()

-- DROP FUNCTION interfaz_cambioturnosistema();

CREATE OR REPLACE FUNCTION interfaz_cambioturnosistema()
  RETURNS trigger AS
$BODY$
DECLARE 
        update_statement TEXT;
        res TEXT;
BEGIN
   IF NEW.estado <> OLD.estado THEN
	if NEW.tipo='T' and NEW.categoria='C' and NEW.estado='1' then
		perform dblink_connect('db0', 'dbname=dbInterface host=192.168.122.246 user=postgres password=1234*granprix');
		update_statement = 'update public."aComandos" set "cmValor"=1 where "cmNumero"=1';
		res := dblink_exec('db0', update_statement, true);
		RAISE INFO '%', res;
		perform dblink_disconnect('db0');
        END IF;
   END IF;
 
   RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION interfaz_cambioturnosistema()
  OWNER TO postgres;


---------------------------------------------------------------------------------------------------------------------------------------------
--En dbInterface tabla aComandos
---------------------------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER "interfazComandos"
  AFTER UPDATE
  ON "aComandos"
  FOR EACH ROW
  EXECUTE PROCEDURE interfaz_inserttotales();


---------------------------------------------------------------------------------------------------------------------------------------------
--En dbInterface tabla aVenta
---------------------------------------------------------------------------------------------------------------------------------------------

CREATE TRIGGER tr_aventa
  AFTER INSERT
  ON "aVenta"
  FOR EACH ROW
  EXECUTE PROCEDURE interfaz_inserttran();
---------------------------------------------------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------------------------------------------------
--En dbInterface create TRIGGER
---------------------------------------------------------------------------------------------------------------------------------------------

-- Function: interfaz_inserttotales()

-- DROP FUNCTION interfaz_inserttotales();

CREATE OR REPLACE FUNCTION interfaz_inserttotales()
  RETURNS trigger AS
$BODY$
DECLARE
cursor_totales CURSOR FOR select * from public."aTotalizadorA" where "taVolumen">0 and "taTipo"=0;
fecha_actual date;
hora_actual  time;
insert_txt TEXT;
res TEXT;
producto varchar(20)='';
manguera varchar;
cara integer;
BEGIN
select DATE(now()) into fecha_actual;
select now()::time into hora_actual;
perform dblink_connect('db9', 'dbname=bdgrifo_granprix_jaen port=5433 host=192.168.1.254 user=postgres password=LX300II**01');
if NEW."cmNumero"=1 and NEW."cmValor"=1 Then
Begin
	FOR row IN cursor_totales LOOP
		select trim("veProducto") into producto from "aVenta" where "veCara"=row."taCara" and "veManguera"=row."taManguera" limit 1;
		if producto is null or trim(producto)='' then producto:='--';end if;
		
		if    row."taManguera"='0' then manguera:='A';
		elsif row."taManguera"='1' then manguera:='B'; 
		elsif row."taManguera"='2' then manguera:='C'; 
		elsif row."taManguera"='3' then manguera:='D';
		elsif row."taManguera"='4' then manguera:='E';
		elsif row."taManguera"='5' then manguera:='F';
		end if;
		cara:=row."taCara"::integer +1;
		if    producto='G84' then  producto:='GASOHOL 84';
		elsif producto='G90' then  producto:='GASOHOL 90';
		elsif producto='G95' then  producto:='GASOHOL 95';
		elsif producto='DB5' then  producto:='DIESEL B5';
		elsif producto='GLP' then  producto:='GLP';
		elsif producto='--'  then producto:='INDEFINIDO';
		end if;
		insert_txt = 'insert into "TOTALIZADORES"(cara,manguera,producto,total_volumen,total_monto,fecha,hora) values ('||cara||', 
			     '''||manguera||''','''||producto||''','''||row."taVolumen"||''','''||row."taImporte"||''','''||fecha_actual||''','''||hora_actual||''');';
        res := dblink_exec('db9', insert_txt, true);
        RAISE INFO '%', res;
END LOOP;
end;
elsif  NEW."cmNumero"=1 and NEW."cmValor"=0 Then
Begin
	insert_txt = 'update "INTERACCION" set estado=''0'' where tipo=''T'' and categoria=''C''';
        res := dblink_exec('db9', insert_txt, true);
        RAISE INFO '%', res;
End;
	
END IF;
perform dblink_disconnect('db9');
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION interfaz_inserttotales()
  OWNER TO postgres;

---------------------------------------------------------------------------------------------------------------------------------------------
--En dbInterface create TRIGGER
---------------------------------------------------------------------------------------------------------------------------------------------

-- Function: interfaz_inserttran()

-- DROP FUNCTION interfaz_inserttran();

CREATE OR REPLACE FUNCTION interfaz_inserttran()
  RETURNS trigger AS
$BODY$
    DECLARE 
        insert_statement TEXT;
        res TEXT;
        manguera varchar;
        producto varchar;
        cara integer;
        idventa integer;
    BEGIN                
        perform dblink_connect('db', 'dbname=bdgrifo_granprix_jaen port=5433 host=192.168.1.254 user=postgres password=LX300II**01');
        idventa:=NEW."veID";
	if    NEW."veManguera"='0' then  manguera:='A';
	elsif NEW."veManguera"='1' then manguera:='B'; 
	elsif NEW."veManguera"='2' then manguera:='C'; 
	elsif NEW."veManguera"='3' then manguera:='D';
	elsif NEW."veManguera"='4' then manguera:='E';
	elsif NEW."veManguera"='5' then manguera:='F';
	end if;
	cara:=NEW."veCara"::integer +1;

	if    NEW."veProducto"='G84' then  producto:='1';
	elsif NEW."veProducto"='G90' then  producto:='2';
	elsif NEW."veProducto"='G95' then  producto:='3';
	elsif NEW."veProducto"='DB5' then  producto:='6';
	elsif NEW."veProducto"='GLP' then  producto:='7';
	end if;
	
        insert_statement = 'insert into "TRAN"(cara,manguera,producto,soles,galones,precio,fecha,hora) 
                                    values ('||cara||', 
                                    '''||manguera||''', 
                                    '''||producto||''',
                                    '''||NEW."veImporte"||''',
                                    '''||NEW."veVolumen"||''',
                                    '''||NEW."vePrecio"||''',
                                    '''||DATE(NEW."veHoraCS")||''',
                                    '''||NEW."veHoraCS"::time||'''  
                                    );';
        res := dblink_exec('db', insert_statement, true);
        RAISE INFO '%', res;
        perform dblink_disconnect('db');
        update "aVenta" set "veEstado"=1 where "veID"=idventa;
        RETURN NEW;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION interfaz_inserttran()
  OWNER TO postgres;