CREATE OR REPLACE VIEW V_CAR_SALE
(IDSALA, NUMERO_SALA, SALA, NUM_SPET, IDCALEND, 
 IDPACCHETTO, IDTARIFFA, ORARIO, DURATA, PV, 
 PL, TITOLO, DATA_ORA, ESAURITO, MODIFICA, 
 STATUS)
AS 
SELECT   s.idsala AS idsala, s.descr AS numero_sala, s.alias AS sala,
            1 AS num_spet, c.idcalend, c.idpacchetto, c.idtariffa,
            TO_CHAR (c.data_ora, 'HH24:MI') AS orario,
            TO_CHAR (f.durata, 'SSSSS') / 60 AS durata,
            SUM (DECODE (v.tipo, 'A', 0, 1)) AS pv,
            s.nposti - SUM (DECODE (v.tipo, 'A', 0, 1)) AS pl,
            p.descrizione AS titolo, c.data_ora AS data_ora,
            NVL (c.esaurito, 0) AS esaurito, c.modifica,
            DECODE (  ROUND ((SYSDATE - c.data_ora) * 1440)
                    - (SELECT visualizza_fine
                         FROM datifissi),
                    '-1', '***',
                    '###'
                   ) AS status
       FROM cal_corr c, sale s, vendite v, pacchetto p, film f
      WHERE ROUND ((SYSDATE - c.data_ora) * 1440) < (SELECT visualizza_fine
                                                       FROM datifissi)
        AND c.data_ora BETWEEN DECODE (TO_CHAR (SYSDATE, 'HH24'),
                                       00, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                                'dd-mm-yyyy'
                                                               )
                                                    || ' 06:00',
                                                    'dd-mm-yyyy hh24:mi'
                                                   ),
                                       1, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       2, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       3, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       4, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       5, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       TO_DATE (   TO_CHAR (SYSDATE,
                                                            'dd-mm-yyyy'
                                                           )
                                                || ' 06:00',
                                                'dd-mm-yyyy hh24:mi'
                                               )
                                      )
                           AND DECODE (TO_CHAR (SYSDATE, 'HH24'),
                                       00, TO_DATE (   TO_CHAR (SYSDATE,
                                                                'dd-mm-yyyy'
                                                               )
                                                    || ' 05:59',
                                                    'dd-mm-yyyy hh24:mi'
                                                   ),
                                       1, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       2, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       3, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       4, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       5, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       TO_DATE (   TO_CHAR (SYSDATE + 1,
                                                            'dd-mm-yyyy'
                                                           )
                                                || ' 05:59',
                                                'dd-mm-yyyy hh24:mi'
                                               )
                                      )
        AND c.idsala = s.idsala
        AND c.idpacchetto = p.idpacchetto
        AND c.idcalend = v.idcalend
        AND p.idfilm = f.idfilm
   GROUP BY s.idsala,
            s.descr,
            s.alias,
            c.idcalend,
            c.idpacchetto,
            c.idtariffa,
            c.data_ora,
            f.durata,
            s.nposti,
            c.idcalend,
            p.descrizione,
            c.idpacchetto,
            c.esaurito,
            c.modifica
   UNION ALL
   SELECT   s.idsala AS idsala, s.descr AS numero_sala, s.alias AS sala,
            1 AS num_spet, c.idcalend, c.idpacchetto, c.idtariffa,
            TO_CHAR (c.data_ora, 'HH24:MI') AS orario,
            TO_CHAR (f.durata, 'SSSSS') / 60 AS durata, 0 pv, s.nposti AS pl,
            p.descrizione AS titolo, c.data_ora AS data_ora,
            NVL (c.esaurito, 0) AS esaurito, c.modifica,
            DECODE (  ROUND ((SYSDATE - c.data_ora) * 1440)
                    - (SELECT visualizza_fine
                         FROM datifissi),
                    '-1', '***',
                    '###'
                   ) AS status
       FROM cal_corr c, sale s, pacchetto p, film f
      WHERE ROUND ((SYSDATE - c.data_ora) * 1440) < (SELECT visualizza_fine
                                                       FROM datifissi)
        AND c.data_ora BETWEEN DECODE (TO_CHAR (SYSDATE, 'HH24'),
                                       00, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                                'dd-mm-yyyy'
                                                               )
                                                    || ' 06:00',
                                                    'dd-mm-yyyy hh24:mi'
                                                   ),
                                       1, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       2, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       3, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       4, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       5, TO_DATE (   TO_CHAR (SYSDATE - 1,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 06:00',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       TO_DATE (   TO_CHAR (SYSDATE,
                                                            'dd-mm-yyyy'
                                                           )
                                                || ' 06:00',
                                                'dd-mm-yyyy hh24:mi'
                                               )
                                      )
                           AND DECODE (TO_CHAR (SYSDATE, 'HH24'),
                                       00, TO_DATE (   TO_CHAR (SYSDATE,
                                                                'dd-mm-yyyy'
                                                               )
                                                    || ' 05:59',
                                                    'dd-mm-yyyy hh24:mi'
                                                   ),
                                       1, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       2, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       3, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       4, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       5, TO_DATE (   TO_CHAR (SYSDATE,
                                                               'dd-mm-yyyy'
                                                              )
                                                   || ' 05:59',
                                                   'dd-mm-yyyy hh24:mi'
                                                  ),
                                       TO_DATE (   TO_CHAR (SYSDATE + 1,
                                                            'dd-mm-yyyy'
                                                           )
                                                || ' 05:59',
                                                'dd-mm-yyyy hh24:mi'
                                               )
                                      )
        AND c.idsala = s.idsala
        AND c.idpacchetto = p.idpacchetto
        AND p.idfilm = f.idfilm
        AND c.idcalend NOT IN (SELECT DISTINCT idcalend
                                          FROM vendite)
   GROUP BY s.idsala,
            s.descr,
            s.alias,
            c.idcalend,
            c.idpacchetto,
            c.idtariffa,
            c.data_ora,
            f.durata,
            s.nposti,
            c.idcalend,
            p.descrizione,
            c.idpacchetto,
            c.esaurito,
            c.modifica
            WITH READ ONLY
/


