CONNECT SYSTEM/fiscaleoutcast@CINEMA

GRANT ALTER ANY INDEX TO "CINEMA";
GRANT ALTER ANY PROCEDURE TO "CINEMA";
GRANT ALTER ANY SEQUENCE TO "CINEMA";
GRANT ALTER ANY TABLE TO "CINEMA";
GRANT ALTER ANY TRIGGER TO "CINEMA";
GRANT ALTER ANY TYPE TO "CINEMA";
GRANT CREATE ANY INDEX TO "CINEMA";
GRANT CREATE ANY PROCEDURE TO "CINEMA";
GRANT CREATE ANY SEQUENCE TO "CINEMA";
GRANT CREATE ANY TABLE TO "CINEMA";
GRANT CREATE ANY TRIGGER TO "CINEMA";
GRANT CREATE ANY VIEW TO "CINEMA";
GRANT CREATE PROCEDURE TO "CINEMA";
GRANT CREATE SEQUENCE TO "CINEMA";
GRANT CREATE TABLE TO "CINEMA";
GRANT CREATE VIEW TO "CINEMA";
GRANT CREATE PUBLIC SYNONYM TO "CINEMA";
GRANT CREATE ROLE TO "CINEMA";
GRANT CREATE ANY TYPE TO "CINEMA";
GRANT DROP ANY INDEX TO "CINEMA";
GRANT DROP ANY PROCEDURE TO "CINEMA";
GRANT DROP ANY SEQUENCE TO "CINEMA";
GRANT DROP ANY TABLE TO "CINEMA";
GRANT DROP ANY VIEW TO "CINEMA";
GRANT DROP ANY TRIGGER TO "CINEMA";
GRANT DROP ANY TYPE TO "CINEMA";
GRANT EXECUTE ANY PROCEDURE TO "CINEMA";
GRANT SELECT ANY DICTIONARY TO "CINEMA";
GRANT SELECT ANY TABLE TO "CINEMA";
GRANT SELECT ANY SEQUENCE TO "CINEMA";
GRANT UPDATE ANY TABLE TO "CINEMA";
GRANT DELETE ANY TABLE TO "CINEMA";
GRANT INSERT ANY TABLE TO "CINEMA";
GRANT "CONNECT" TO "CINEMA";
GRANT "DBA" TO "CINEMA";


GRANT ALTER ANY INDEX TO "WTIC";
GRANT ALTER ANY PROCEDURE TO "WTIC";
GRANT ALTER ANY SEQUENCE TO "WTIC";
GRANT ALTER ANY TABLE TO "WTIC";
GRANT ALTER ANY TRIGGER TO "WTIC";
GRANT ALTER ANY TYPE TO "WTIC";
GRANT CREATE ANY INDEX TO "WTIC";
GRANT CREATE ANY PROCEDURE TO "WTIC";
GRANT CREATE ANY SEQUENCE TO "WTIC";
GRANT CREATE ANY TABLE TO "WTIC";
GRANT CREATE ANY TRIGGER TO "WTIC";
GRANT CREATE ANY VIEW TO "WTIC";
GRANT CREATE PROCEDURE TO "WTIC";
GRANT CREATE SEQUENCE TO "WTIC";
GRANT CREATE TABLE TO "WTIC";
GRANT CREATE VIEW TO "WTIC";
GRANT CREATE PUBLIC SYNONYM TO "WTIC";
GRANT CREATE ROLE TO "WTIC";
GRANT CREATE ANY TYPE TO "WTIC";
GRANT DROP ANY INDEX TO "WTIC";
GRANT DROP ANY PROCEDURE TO "WTIC";
GRANT DROP ANY SEQUENCE TO "WTIC";
GRANT DROP ANY TABLE TO "WTIC";
GRANT DROP ANY VIEW TO "WTIC";
GRANT DROP ANY TRIGGER TO "WTIC";
GRANT DROP ANY TYPE TO "WTIC";
GRANT EXECUTE ANY PROCEDURE TO "WTIC";
GRANT SELECT ANY DICTIONARY TO "WTIC";
GRANT SELECT ANY TABLE TO "WTIC";
GRANT SELECT ANY SEQUENCE TO "WTIC";
GRANT UPDATE ANY TABLE TO "WTIC";
GRANT DELETE ANY TABLE TO "WTIC";
GRANT INSERT ANY TABLE TO "WTIC";
GRANT "CONNECT" TO "WTIC";
GRANT "DBA" TO "WTIC";

GRANT ALTER ANY INDEX TO "TEX";
GRANT ALTER ANY PROCEDURE TO "TEX";
GRANT ALTER ANY SEQUENCE TO "TEX";
GRANT ALTER ANY TABLE TO "TEX";
GRANT ALTER ANY TRIGGER TO "TEX";
GRANT ALTER ANY TYPE TO "TEX";
GRANT CREATE ANY INDEX TO "TEX";
GRANT CREATE ANY PROCEDURE TO "TEX";
GRANT CREATE ANY SEQUENCE TO "TEX";
GRANT CREATE ANY TABLE TO "TEX";
GRANT CREATE ANY TRIGGER TO "TEX";
GRANT CREATE ANY VIEW TO "TEX";
GRANT CREATE PROCEDURE TO "TEX";
GRANT CREATE SEQUENCE TO "TEX";
GRANT CREATE TABLE TO "TEX";
GRANT CREATE VIEW TO "TEX";
GRANT CREATE PUBLIC SYNONYM TO "TEX";
GRANT CREATE ROLE TO "TEX";
GRANT CREATE ANY TYPE TO "TEX";
GRANT DROP ANY INDEX TO "TEX";
GRANT DROP ANY PROCEDURE TO "TEX";
GRANT DROP ANY SEQUENCE TO "TEX";
GRANT DROP ANY TABLE TO "TEX";
GRANT DROP ANY VIEW TO "TEX";
GRANT DROP ANY TRIGGER TO "TEX";
GRANT DROP ANY TYPE TO "TEX";
GRANT EXECUTE ANY PROCEDURE TO "TEX";
GRANT SELECT ANY DICTIONARY TO "TEX";
GRANT SELECT ANY TABLE TO "TEX";
GRANT SELECT ANY SEQUENCE TO "TEX";
GRANT UPDATE ANY TABLE TO "TEX";
GRANT DELETE ANY TABLE TO "TEX";
GRANT INSERT ANY TABLE TO "TEX";
GRANT "CONNECT" TO "TEX";
GRANT "DBA" TO "TEX";


CONNECT TEX/TEX@TEX

CREATE TABLE TEX.DOTAZIONI_DA_PROPAGARE (IDDOTAZIONE NUMBER NOT NULL);

SELECT * FROM TEX.DOTAZIONE;

CONNECT CINEMA/CINEMA@CINEMA

ALTER TABLE CINEMA.CINEMA ADD(COD_MEDUSA NUMBER);

UPDATE CINEMA.CINEMA SET COD_MEDUSA=IDCINEMA;

COMMIT;

SELECT IDCINEMA, COD_MEDUSA,DESCR  FROM CINEMA;

CREATE SEQUENCE CINEMA.SEQ_ORDINI_OPERAZIONI
START WITH 1
INCREMENT BY 2
MINVALUE 0
NOCACHE
NOCYCLE
NOORDER;


CREATE TABLE RS_SMART_TESSERE
(
  ID_ORDINE_OPERAZIONE     NUMBER               NOT NULL,
  IDCINEMA                 NUMBER               NOT NULL,
  VENDUTO_ANNULLATO        VARCHAR2(1 BYTE)     NOT NULL,
  IDVEND                   NUMBER               NOT NULL,
  IDTEX                    NUMBER               NOT NULL,
  PIN                      NUMBER               NOT NULL,
  TIPO_TURNO               VARCHAR2(1 BYTE)     NOT NULL,
  NUMERO_EVENTI            NUMBER               NOT NULL,
  VALIDITA                 DATE                 NOT NULL,
  CODICE_ABBONAMENTO       VARCHAR2(8 BYTE)     NOT NULL,
  NUM_PROG_ABB             NUMBER               NOT NULL,
  RATEO                    NUMBER(12,2)         NOT NULL,
  IVA_RATEO                NUMBER(12,2)         NOT NULL,
  RATEO_IMP_INTR           NUMBER(12,2),
  CAUSALE_OR               VARCHAR2(10 BYTE),
  CORRISPETTIVOTITOLO      NUMBER(12,2)         NOT NULL,
  CORRISPETTIVOPREVENDITA  NUMBER(12,2)         NOT NULL,
  IVATITOLO                NUMBER(12,2)         NOT NULL,
  IVAPREVENDITA            NUMBER(12,2)         NOT NULL,
  TIPO_TITOLO              VARCHAR2(2 BYTE)     NOT NULL,
  DATA_EMI                 DATE                 NOT NULL,
  CF_ORGANIZZATORE         VARCHAR2(20 BYTE)    NOT NULL,
  TIPO_IVA                 VARCHAR2(1 BYTE)     NOT NULL,
  NOMEPRE                  VARCHAR2(45 BYTE),
  STATO                    NUMBER               NOT NULL
);




CREATE OR REPLACE TRIGGER CINEMA.TRG_RS_SMART_TESSERE_I BEFORE
INSERT ON CINEMA.RS_SMART_TESSERE 
REFERENCING NEW AS new
FOR EACH ROW
BEGIN 
    IF INSERTING THEN
	IF :new.ID_ORDINE_OPERAZIONE = 0 THEN
		:new.STATO := 0;
	END IF;
    END IF;
END;
.
R

CREATE OR REPLACE TRIGGER "CINEMA".TRG_RS_SMART_TESSERE_U 
BEFORE UPDATE OF "STATO" ON "CINEMA"."RS_SMART_TESSERE"
REFERENCING OLD AS old NEW as new
FOR EACH ROW
DECLARE
    iCounter RS_SMART_TESSERE.ID_ORDINE_OPERAZIONE%TYPE;
BEGIN 
    IF :old.STATO = 0 AND :new.STATO = 1 THEN
        select SEQ_ORDINI_OPERAZIONI.NEXTVAL into iCounter FROM DUAL;
        :new.ID_ORDINE_OPERAZIONE := iCounter;
    END IF;
END;
.
R

ALTER TABLE SMART_TESSERE ADD (NOMEPRE VARCHAR2(45));

ALTER TABLE STORICO_SMART_TESSERE ADD (NOMEPRE VARCHAR2(45));

ALTER TABLE S_SMART_TESSERE ADD (NOMEPRE VARCHAR2(45));


CREATE TABLE CINEMA.RS_TESSERE
(
ID_ORDINE_OPERAZIONE		NUMBER(10)	NOT NULL,
IDCINEMA			NUMBER		NOT NULL,
VENDUTO_ANNULLATO		VARCHAR2(1)	NOT NULL,
IDTEX				NUMBER(10)	NOT NULL,
PIN				NUMBER(10)	NOT NULL,
VAL				NUMBER		NOT NULL
);


CREATE TABLE CINEMA.RS_TEMP_TESSERE
(
ID_ORDINE_OPERAZIONE		NUMBER(10)	NOT NULL,
IDCINEMA			NUMBER		NOT NULL,
VENDUTO_ANNULLATO		VARCHAR2(1)	NOT NULL,
IDTEX				NUMBER(10)	NOT NULL,
PIN				NUMBER(10)	NOT NULL,
VAL				NUMBER		NOT NULL
);


CREATE OR REPLACE TRIGGER CINEMA.TRG_SMART_TESSERE_I
after insert ON CINEMA.SMART_TESSERE
for each row
declare
            nPin   RS_SMART_TESSERE.PIN%TYPE;
            cTasto Varchar2(4);
	    nIdDotazione NUMBER;
BEGIN


 IF INSERTING THEN

            cTasto := '.';
            select TASTO into cTasto from TIPO_BIGLIETTO where TIPO_TITOLO = :new.TIPO_TITOLO AND TASTO LIKE 'T%' AND IDTIPOBIGL IN
            (SELECT IDTIPOBIGL FROM BIGLIETTO WHERE PREZZO_LORDO = :new.RATEO AND FINE_VALIDITA IS NULL);

            IF TRIM(cTasto) <> '.' THEN

                        select nvl(max(PIN),0) into nPin from TEX.TESSERE where IDTEX = :new.IDTEX and TASTO = cTasto;

			IF nPin > 0 THEN
				select NVL(MAX(P.IDDOTAZIONE),0) into nIdDotazione from TEX.TESSERE T, TEX.LOTTI L, TEX.DOTAZIONI_DA_PROPAGARE P where T.IDTEX = :new.IDTEX and T.PIN = nPin and T.TASTO = cTasto and T.IDLOTTO = L.IDLOTTO and L.IDDOTAZIONE = P.IDDOTAZIONE;
			END IF;

                        IF nPin > 0 THEN
				IF nIdDotazione > 0 THEN

                                   insert into RS_SMART_TESSERE
                                   select
                                   0,
                                   COD_MEDUSA,
                                   'V',
                                   :new.IDVEND,
                                   :new.IDTEX,
                                   nPin,
                                   :new.TIPO_TURNO,
                                   :new.NUMERO_EVENTI,
                                   :new.VALIDITA,
                                   :new.CODICE_ABBONAMENTO,
                                   :new.NUM_PROG_ABB,
                                   :new.RATEO,
                                   :new.IVA_RATEO,
                                   :new.RATEO_IMP_INTR,
                                   :new.CAUSALE_OR,
                                   :new.CORRISPETTIVOTITOLO,
                                   :new.CORRISPETTIVOPREVENDITA,
                                   :new.IVATITOLO,
                                   :new.IVAPREVENDITA,
                                   :new.TIPO_TITOLO,
                                   :new.DATA_EMI,
                                   :new.CF_ORGANIZZATORE,
                                   :new.TIPO_IVA,
                                   :new.NOMEPRE,
                                   null
                                   FROM
                                   CINEMA;
				END IF;

                        END IF;

            END IF;

  END IF;

  EXCEPTION
    WHEN OTHERS THEN
     null;

END;
.
R


CREATE OR REPLACE TRIGGER CINEMA.TRG_RS_TESSERE BEFORE
INSERT ON "CINEMA"."RS_TESSERE"
REFERENCING NEW AS new
FOR EACH ROW
DECLARE
	iCounter Number;
BEGIN 
    IF INSERTING THEN
	select SEQ_ORDINI_OPERAZIONI.NEXTVAL into iCounter FROM DUAL;
	:new.ID_ORDINE_OPERAZIONE := iCounter;
    END IF;
END;
.
R

CONNECT TEX/TEX@TEX

CREATE OR REPLACE TRIGGER TEX.TRG_RR_TESSERE
AFTER UPDATE OF RIMANENZA ON "TEX"."TESSERE"
REFERENCING OLD AS old NEW as new
FOR EACH ROW
DECLARE
    nIdCinema number;
    nIdCinemaTemp number;
    nIdOperazione number;
    nIdDotazione NUMBER;
BEGIN

    if :old.DOTAZIONEF = :new.DOTAZIONEF THEN

        select nvl(COD_MEDUSA,0) into nIdCinema FROM CINEMA.CINEMA;

        IF (nIdCinema > 0) THEN

            select NVL(MAX(ID_ORDINE_OPERAZIONE),0) into nIdOperazione from CINEMA.RS_TEMP_TESSERE
            where IDTEX = :new.IDTEX and PIN = :new.PIN;

            select NVL(MAX(IDCINEMA),0) into nIdCinemaTemp from CINEMA.RS_TEMP_TESSERE
            where IDTEX = :new.IDTEX and PIN = :new.PIN;

	    select NVL(MAX(P.IDDOTAZIONE),0) into nIdDotazione from 
	    TEX.LOTTI L, TEX.DOTAZIONI_DA_PROPAGARE P 
	    where L.IDLOTTO = :old.IDLOTTO and L.IDDOTAZIONE = P.IDDOTAZIONE;

            If nIdDotazione > 0 then
		if nIdOperazione = 0 then

			IF :old.RIMANENZA > :new.RIMANENZA THEN

				insert into CINEMA.RS_TESSERE
				(IDCINEMA, VENDUTO_ANNULLATO, IDTEX, PIN, VAL)
				values
				(nIdCinema, 'V', :old.IDTEX, :old.PIN, :new.RIMANENZA - :old.RIMANENZA);

			END IF;

	                IF :old.RIMANENZA < :new.RIMANENZA THEN

		            insert into CINEMA.RS_TESSERE
			    (IDCINEMA, VENDUTO_ANNULLATO, IDTEX, PIN, VAL)
	                    values
	                    (nIdCinema, 'A', :old.IDTEX, :old.PIN, :new.RIMANENZA - :old.RIMANENZA);

			END IF;
		END IF;

            end if;

            if nIdoperazione > 0 then
                delete from CINEMA.RS_TEMP_TESSERE where IDTEX = :new.IDTEX and PIN = :new.PIN and ID_ORDINE_OPERAZIONE = nIdOperazione and IDCINEMA = nIdCinemaTemp;
            end if;

        END IF;
    END IF;
END;
.
R


CREATE OR REPLACE TRIGGER TEX.TRG_RR_TESSERE_A
BEFORE UPDATE OF DOTAZIONEF ON "TEX"."TESSERE"
REFERENCING OLD AS old NEW as new
FOR EACH ROW
DECLARE
    nIdCinema number;
    nIdCinemaTemp number;
    nIdOperazione number;
BEGIN
   if :old.DOTAZIONEF IS NOT NULL THEN
      
      if :new.DOTAZIONEF IS NULL THEN
         
	 select nvl(COD_MEDUSA,0) into nIdCinema FROM CINEMA.CINEMA;

	 IF (nIdCinema > 0) THEN

            select NVL(MAX(ID_ORDINE_OPERAZIONE),0) into nIdOperazione from CINEMA.RS_TEMP_TESSERE
            where IDTEX = :new.IDTEX and PIN = :new.PIN;

            select NVL(MAX(IDCINEMA),0) into nIdCinemaTemp from CINEMA.RS_TEMP_TESSERE
            where IDTEX = :new.IDTEX and PIN = :new.PIN;

            if nIdOperazione = 0 then

		   insert into CINEMA.RS_SMART_TESSERE
		   select
		   CINEMA.SEQ_ORDINI_OPERAZIONI.NEXTVAL,
		   CINEMA.COD_MEDUSA,
		   'A',
		   CINEMA.SMART_TESSERE.IDVEND,
		   :new.IDTEX,
		   :new.PIN,
		   CINEMA.SMART_TESSERE.TIPO_TURNO,
		   CINEMA.SMART_TESSERE.NUMERO_EVENTI,
		   CINEMA.SMART_TESSERE.VALIDITA,
		   CINEMA.SMART_TESSERE.CODICE_ABBONAMENTO,
		   CINEMA.SMART_TESSERE.NUM_PROG_ABB,
		   CINEMA.SMART_TESSERE.RATEO,
		   CINEMA.SMART_TESSERE.IVA_RATEO,
		   CINEMA.SMART_TESSERE.RATEO_IMP_INTR,
		   CINEMA.SMART_TESSERE.CAUSALE_OR,
		   CINEMA.SMART_TESSERE.CORRISPETTIVOTITOLO,
		   CINEMA.SMART_TESSERE.CORRISPETTIVOPREVENDITA,
		   CINEMA.SMART_TESSERE.IVATITOLO,
		   CINEMA.SMART_TESSERE.IVAPREVENDITA,
		   CINEMA.SMART_TESSERE.TIPO_TITOLO,
		   CINEMA.SMART_TESSERE.DATA_EMI,
		   CINEMA.SMART_TESSERE.CF_ORGANIZZATORE,
		   CINEMA.SMART_TESSERE.TIPO_IVA,
		   CINEMA.SMART_TESSERE.NOMEPRE,
		   1
		   FROM
		   CINEMA.CINEMA, CINEMA.SMART_TESSERE
		   where
		   CINEMA.SMART_TESSERE.IDTEX = :new.IDTEX
		   and
		   CINEMA.SMART_TESSERE.CODICE_ABBONAMENTO = :old.DOTAZIONEF;

           end if;

            if nIdoperazione > 0 then
                delete from CINEMA.RS_TEMP_TESSERE where IDTEX = :new.IDTEX and PIN = :new.PIN and ID_ORDINE_OPERAZIONE = nIdOperazione and IDCINEMA = nIdCinemaTemp;
            end if;


	 END IF;

      end if;
   end if;

END;
.
R
