Login   Register  
PHP Classes
elePHPant
Icontem

File: create_tables_and_view.sql

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Josť Filipe Lopes Santos  >  Ticket Calendar  >  create_tables_and_view.sql  >  Download  
File: create_tables_and_view.sql
Role: Auxiliary data
Content type: text/plain
Description: Sql to create all necessary tables and views
Class: Ticket Calendar
View the dates of scheduled tickets on a calendar
Author: By
Last change: remove references for database user used in your servers
Date: 10 months ago
Size: 1,911 bytes
 

Contents

Class file image Download
  CREATE TABLE "TTS_TICKETS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"ID_AREA" NUMBER, 
	"DATA" DATE, 
	"ID_TIPO_CONTACTO" NUMBER, 
	"ID_TIPO_PROBLEMA" NUMBER NOT NULL ENABLE, 
	"ID_TIPO_INTERVENCAO" NUMBER, 
	"ID_SERVICO" NUMBER NOT NULL ENABLE, 
	"TIPO_SERVICO" NUMBER NOT NULL ENABLE, 
	"NOME_SERVICO" VARCHAR2(80 BYTE) NOT NULL ENABLE, 
	"ID_UTENTE" NUMBER, 
	"NOME_UTENTE" VARCHAR2(80 BYTE), 
	"ID_TIPO_UTENTE" NUMBER, 
	"CONTACTO_UTENTE" VARCHAR2(80 BYTE), 
	"ID_PRIORIDADE" NUMBER, 
	"OBS" CLOB, 
	"DATA_AGENDAMENTO" DATE, 
	"SUBMETIDO_POR" VARCHAR2(15 BYTE), 
	"DATA_CRIACAO" DATE, 
	 CONSTRAINT "TTS_TICKETS_PK" PRIMARY KEY ("ID")
  ) ;
/
 
CREATE TABLE "TTS_TICKETS_EQUIPAS" 
   (	"ID_TICKET" NUMBER NOT NULL ENABLE, 
	"LOGIN" VARCHAR2(15 BYTE) NOT NULL ENABLE, 
	"RESP" VARCHAR2(1 BYTE) DEFAULT 'n' NOT NULL ENABLE, 
	 CONSTRAINT "TTS_TICKETS_EQUIPAS_PK" PRIMARY KEY ("ID_TICKET", "LOGIN")
   );
/


CREATE TABLE "TTS_TIPOS_PROBLEMAS" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"ID_AREA" NUMBER NOT NULL ENABLE, 
	"NOME" VARCHAR2(80 BYTE) NOT NULL ENABLE, 
	"ORDEM" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "TTS_TIPOS_PROBLEMAS_PK" PRIMARY KEY ("ID")
  );
/


CREATE TABLE "TTS_TICKETS_ESTADOS" 
   (	"ID_TICKET" NUMBER NOT NULL ENABLE, 
	"ID_ESTADO" NUMBER NOT NULL ENABLE, 
	"DATA" DATE NOT NULL ENABLE, 
	"OBS" VARCHAR2(250 BYTE), 
	"MINS_GASTOS" NUMBER, 
	 CONSTRAINT "TTS_TICKETS_ESTADOS_PK" PRIMARY KEY ("ID_TICKET", "ID_ESTADO")
  );

/
CREATE OR REPLACE FORCE VIEW "TTS_ESTADO_ACTUAL_VW" ("ID_TICKET", "ID_ESTADO", "NOME_ESTADO", "DATA", "OBS") AS 
  SELECT L.ID_TICKET, TE.ID_ESTADO, E.NOME AS NOME_ESTADO, TE.DATA, TE.OBS
from (
  SELECT ID_TICKET,MAX(DATA) as maxdata FROM TTS_TICKETS_ESTADOS
  GROUP BY ID_TICKET
) L, TTS_TICKETS_ESTADOS TE, TTS_ESTADOS E
WHERE L.ID_TICKET = TE.ID_TICKET
AND L.MAXDATA = TE.DATA
AND TE.ID_ESTADO = E.ID;