--Order 20010951.50


CREATE SEQUENCE SEQ_WEBEVENTTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEBEVENTTYPE
(
	WEBEVENTTYPE_ID      NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;

ALTER TABLE WEBEVENTTYPE
ADD CONSTRAINT  XPKWEBEVENTTYPE PRIMARY KEY (WEBEVENTTYPE_ID) USING INDEX TABLESPACE INDX;

CREATE SEQUENCE SEQ_WEBPORTALEVENT
START WITH 1
INCREMENT BY 1;


CREATE TABLE WEBPORTALEVENT
(
	WEBPORTALEVENT_ID    NUMBER NOT NULL ,
	EVENTDATE            DATE NOT NULL ,
	SESSIONNUMBER        NUMBER NOT NULL ,
	PLANT_ID             NUMBER NOT NULL ,
	WEBUSER_ID           NUMBER NOT NULL ,
	CUSTOMER_LINK_ID     NUMBER NULL ,
	WEBEVENTTYPE_ID      NUMBER NOT NULL ,
	WEBMENUDIALOG_ID     NUMBER NULL ,
	DETAILS              VARCHAR2(1000) NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL	
)
TABLESPACE ABS_DATA;


ALTER TABLE WEBPORTALEVENT
ADD CONSTRAINT  XPKWEBPORTALEVENT PRIMARY KEY (WEBPORTALEVENT_ID) USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF1WEBPORTALEVENT ON WEBPORTALEVENT
(WEBUSER_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIF2WEBPORTALEVENT ON WEBPORTALEVENT
(CUSTOMER_LINK_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIF3WEBPORTALEVENT ON WEBPORTALEVENT
(WEBEVENTTYPE_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIF4WEBPORTALEVENT ON WEBPORTALEVENT
(WEBMENUDIALOG_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIE1WEBPORTALEVENT ON WEBPORTALEVENT
(WEBUSER_ID   ASC,EVENTDATE   ASC,WEBEVENTTYPE_ID   ASC);



CREATE  INDEX XIE2WEBPORTALEVENT ON WEBPORTALEVENT
(CUSTOMER_LINK_ID   ASC,EVENTDATE   ASC,WEBEVENTTYPE_ID   ASC);



CREATE  INDEX XIE3WEBPORTALEVENT ON WEBPORTALEVENT
(WEBMENUDIALOG_ID   ASC,EVENTDATE   ASC,WEBEVENTTYPE_ID   ASC);



CREATE  INDEX XIE4WEBPORTALEVENT ON WEBPORTALEVENT
(PLANT_ID  ASC,EVENTDATE   ASC);



CREATE  INDEX XIE5WEBPORTALEVENT ON WEBPORTALEVENT
(EVENTDATE   ASC,WEBEVENTTYPE_ID   ASC);


CREATE  INDEX XIE6WEBPORTALEVENT ON WEBPORTALEVENT
(SESSIONNUMBER      ASC) TABLESPACE INDX;


CREATE SEQUENCE SEQ_WEBUSERSESSION
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEBUSERSESSION
(
	WEBUSERSESSION_ID    NUMBER NOT NULL ,
	WEBSESSION_ID        VARCHAR2(50) NOT NULL ,
	CUSTOMER_ID          NUMBER NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE WEBUSERSESSION
ADD CONSTRAINT  XPKWEBUSERSESSION PRIMARY KEY (WEBUSERSESSION_ID) USING INDEX TABLESPACE INDX;


ALTER TABLE WEBPORTALEVENT
ADD CONSTRAINT WEBPORTALEVENT_WEBUSER FOREIGN KEY (WEBUSER_ID) REFERENCES WEBUSER (WEBUSER_ID);



ALTER TABLE WEBPORTALEVENT
ADD CONSTRAINT WEBPORTALEVENT_CUSTOMER_LINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);



ALTER TABLE WEBPORTALEVENT
ADD CONSTRAINT WEBPORTALEVENT_WEBEVENTTYPE FOREIGN KEY (WEBEVENTTYPE_ID) REFERENCES WEBEVENTTYPE (WEBEVENTTYPE_ID);



ALTER TABLE WEBPORTALEVENT
ADD CONSTRAINT WEBPORTALEVENT_WEBMENUDIALOG FOREIGN KEY (WEBMENUDIALOG_ID) REFERENCES WEBMENUDIALOG (WEBMENUDIALOG_ID) ON DELETE SET NULL;

ALTER TABLE WEBUSER
	ADD (LASTLOGOUTTIMESTAMP DATE NULL);

--insert into WebEventType table

insert into webeventtype
  select 0, 'Log On', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 1, 'Log On after Session Time Out', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 2, 'Log Off', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 3, 'Session Time Out', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 4, 'Select from Menu', getminsystemuser(), sysdate from dual;

insert into webeventtype
  select 5, 'Submit Report', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 6, 'Save Data', getminsystemuser(), sysdate from dual;

insert into webeventtype
  select 7, 'Select Customer', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 10, 'Confirm Stock Count', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 11, 'Revoke Confirmation', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 20, 'Add Wearer', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 21, 'Edit Wearer', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 22, 'Quit Wearer', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 23, 'Add Employment', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 24, 'Remove Employment', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 25, 'Add Inventory', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 26, 'Edit Inventory', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 27, 'Quit Inventory', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 28, 'Create Work Order', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 29, 'Cancel Work Order', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 30, 'Add Locker', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 31, 'Edit Locker', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 32, 'Delete Locker', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 40, 'Add Flag', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 41, 'Edit Flag', getminsystemuser(), sysdate from dual;
  
insert into webeventtype
  select 42, 'Remove Flag', getminsystemuser(), sysdate from dual;

--insert into PurgeTable

insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   datefieldname,
   plantfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'WEBPortalEvent',
         'Web Portal Event',
         'Time filter based on Timestamp',
         'Timestamp',
         'Plant_ID',
         getminsystemuser(),
         sysdate
    from dual;

--update webuser

update webuser w set w.lastlogouttimestamp = w.lastinlogtimestamp;

commit;