-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TSQueryLog"
-- Table comment: Timesheet Queries - current state of each Query (log is an inaccurate description)
-- Statement count: 13
CREATE TABLE "pears"."TSQueryLog" (
"TSQueryLogID" CHAR(20) NOT NULL
,"RefNo" INTEGER NULL DEFAULT autoincrement
,"ReasonCode" CHAR(5) NOT NULL
,"TempDeskID" CHAR(20) NULL
,"PersonID" CHAR(20) NULL
,"TempProvTimeSheetID" CHAR(20) NULL
,"FileNamePath" CHAR(250) NULL
,"Notes" long VARCHAR NULL
,"WhenEntered" TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
,"WhoEntered" CHAR(20) NULL
,"PersonName" CHAR(100) NULL
,"ClientName" CHAR(100) NULL
,"Status" CHAR(1) NULL
,"Orientation" SMALLINT NULL
,"TempTimeSheetID" CHAR(20) NULL
,"Source" CHAR(1) NULL
,"ImageBlobID" CHAR(20) NULL
,"WebOrigin" SMALLINT NULL DEFAULT 0
,"ClientContactName" CHAR(255) NULL
,"ClientContactPhone" CHAR(255) NULL
,"ClientContactEmail" CHAR(255) NULL
,PRIMARY KEY ("TSQueryLogID" ASC)
)
GO
COMMENT ON COLUMN "pears"."TSQueryLog"."Status" IS
'A=archived, F=requires front attention=owner front, B=requires back attention=owner back, T=requires TI attention=owner TI'
GO
COMMENT ON TABLE "pears"."TSQueryLog" IS
'Timesheet Queries - current state of each Query (log is an inaccurate description)'
GO
ALTER TABLE "pears"."TSQueryLog"
ADD FOREIGN KEY "TempDesk" ("TempDeskID" ASC)
REFERENCES "pears"."tempdesk" ("tempdeskid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."TSQueryLog"
ADD FOREIGN KEY "Person" ("PersonID" ASC)
REFERENCES "pears"."Person" ("personid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."TSQueryLog"
ADD FOREIGN KEY "TempProvTimeSheet" ("TempProvTimeSheetID" ASC)
REFERENCES "pears"."TempProvTimeSheet" ("TempProvTimeSheetID")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."TSQueryLog"
ADD FOREIGN KEY "TempTimeSheet" ("TempTimeSheetID" ASC)
REFERENCES "pears"."TempTimeSheet" ("TempTimeSheetID")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."TSQueryLog"
ADD NOT NULL FOREIGN KEY "TSQueryReasons" ("ReasonCode" ASC)
REFERENCES "pears"."TSQueryReasons" ("ReasonCode")
GO
CREATE INDEX "TSQueryLog_WhenEntered" ON "pears"."TSQueryLog"
( "WhenEntered" )
GO
CREATE TRIGGER "TSQueryLoginsert" BEFORE INSERT ORDER 1 ON
"pears"."TSQueryLog"
REFERENCING NEW AS "new_tsq"
FOR each ROW
BEGIN
IF("new_tsq"."WhoEntered" IS NULL) AND("varexists"("userstaffid") = 1) THEN SET "new_tsq"."WhoEntered" = "userstaffid"
END IF;
IF EXISTS(SELECT * FROM "wpkglobalswitchsetting" WHERE "wpkglobalswitchid" = 'TSQUERYPOPUP' AND "value" = 'Y') AND("new_tsq"."WhoEntered" IS NOT NULL)
AND("new_tsq"."Tempdeskid" IS NOT NULL) AND("new_tsq"."Status" = 'F') THEN
-- have a tempdesk, is for front office and the switch is on
FOR "StaffLoop" AS "StaffCursor" no scroll cursor FOR SELECT "StaffID" AS @StaffID FROM "staff" WHERE "tempdeskid" = "new_tsq"."Tempdeskid" FOR READ ONLY
do
INSERT INTO "diary"( "diaryid","staffid","diaryfrom","diaryto","personid","description","notes","durationtype","duration","PopupMinutes","PopupTime" ) VALUES( "uniquekey"('x'),@StaffID,CURRENT TIMESTAMP,
CURRENT TIMESTAMP,"new_tsq"."Personid",
"string"('A Timesheet Query was created ',"new_tsq"."RefNo"),
"string"('Temp - ',(SELECT "name" FROM "person" WHERE "personid" = "new_tsq"."Personid"),"char"(13),'Desk - ',(SELECT "name" FROM "tempdesk" WHERE "tempdeskid" = "new_tsq"."TempdeskID"),
"char"(13),'Created by - ',(SELECT "name" FROM "staff" WHERE "staffid" = "new_tsq"."WhoEntered"),
"char"(13),'Reason - ',(SELECT "reasonname" FROM "TSQueryReasons" WHERE "reasoncode" = "new_tsq"."reasoncode"),
"char"(13),'Timesheet - ',(SELECT "serialnumber" FROM "tempprovtimesheet" WHERE "tempprovtimesheetid" = "new_tsq"."tempprovtimesheetid"),"char"(13),
'Notes - ',"new_tsq"."notes"),'R',0,0,"dateadd"("second",1,CURRENT TIMESTAMP) )
END FOR
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TSQueryLog"."TSQueryLoginsert" IS
{CREATE TRIGGER TSQueryLoginsert
BEFORE INSERT ORDER 1 ON
pears.TSQueryLog
REFERENCING NEW AS new_tsq
FOR each ROW
BEGIN
IF(new_tsq.WhoEntered IS NULL) AND(varexists(userstaffid) = 1) THEN SET new_tsq.WhoEntered = userstaffid
END IF;
IF EXISTS(SELECT * FROM wpkglobalswitchsetting WHERE wpkglobalswitchid = 'TSQUERYPOPUP' AND VALUE = 'Y') AND(new_tsq.WhoEntered IS NOT NULL)
AND(new_tsq.Tempdeskid IS NOT NULL) AND(new_tsq.Status = 'F') THEN
-- have a tempdesk, is for front office and the switch is on
FOR StaffLoop AS StaffCursor no scroll cursor FOR SELECT StaffID AS @StaffID FROM staff WHERE tempdeskid = new_tsq.Tempdeskid FOR READ ONLY
do
INSERT INTO diary( diaryid,staffid,diaryfrom,diaryto,personid,description,notes,durationtype,duration,PopupMinutes,PopupTime ) VALUES( uniquekey('x'),@StaffID,CURRENT TIMESTAMP,
CURRENT TIMESTAMP,new_tsq.Personid,
string('A Timesheet Query was created ',new_tsq.RefNo),
string('Temp - ',(SELECT name FROM person WHERE personid = new_tsq.Personid),"char"(13),'Desk - ',(SELECT name FROM tempdesk WHERE tempdeskid = new_tsq.TempdeskID),
"char"(13),'Created by - ',(SELECT name FROM staff WHERE staffid = new_tsq.WhoEntered),
"char"(13),'Reason - ',(SELECT reasonname FROM TSQueryReasons WHERE reasoncode = new_tsq.reasoncode),
"char"(13),'Timesheet - ',(SELECT serialnumber FROM tempprovtimesheet WHERE tempprovtimesheetid = new_tsq.tempprovtimesheetid),"char"(13),
'Notes - ',new_tsq.notes),'R',0,0,dateadd(SECOND,1,CURRENT TIMESTAMP) )
END FOR END IF
END
}
GO
CREATE TRIGGER "TSQueryLogupdate" BEFORE UPDATE ORDER 1 ON
"pears"."TSQueryLog"
REFERENCING OLD AS "old_tsq" NEW AS "new_tsq"
FOR each ROW
BEGIN
IF "old_tsq"."status" <> "new_tsq"."status" THEN
IF EXISTS(SELECT * FROM "wpkglobalswitchsetting" WHERE "wpkglobalswitchid" = 'TSQUERYPOPUP' AND "value" = 'Y') AND("new_tsq"."WhoEntered" IS NOT NULL)
AND("new_tsq"."Tempdeskid" IS NOT NULL) AND("new_tsq"."Status" = 'F') THEN
-- have a tempdesk, is for front office and the switch is on
FOR "StaffLoop" AS "StaffCursor" no scroll cursor FOR SELECT "StaffID" AS @StaffID FROM "staff" WHERE "tempdeskid" = "new_tsq"."Tempdeskid" FOR READ ONLY
do
INSERT INTO "diary"( "diaryid","staffid","diaryfrom","diaryto","personid","description","notes","durationtype","duration","PopupMinutes","PopupTime" ) VALUES( "uniquekey"('x'),@StaffID,CURRENT TIMESTAMP,
CURRENT TIMESTAMP,"new_tsq"."Personid",
"string"('A Timesheet Query was transferred ',"new_tsq"."RefNo"),
"string"('Temp - ',(SELECT "name" FROM "person" WHERE "personid" = "new_tsq"."Personid"),"char"(13),'Desk - ',(SELECT "name" FROM "tempdesk" WHERE "tempdeskid" = "new_tsq"."TempdeskID"),
"char"(13),'Created by - ',(SELECT "name" FROM "staff" WHERE "staffid" = "new_tsq"."WhoEntered"),
"char"(13),'Reason - ',(SELECT "reasonname" FROM "TSQueryReasons" WHERE "reasoncode" = "new_tsq"."reasoncode"),
"char"(13),'Timesheet - ',(SELECT "serialnumber" FROM "tempprovtimesheet" WHERE "tempprovtimesheetid" = "new_tsq"."tempprovtimesheetid"),"char"(13),
'Notes - ',"new_tsq"."notes"),'R',0,0,"dateadd"("second",1,CURRENT TIMESTAMP) )
END FOR END IF
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TSQueryLog"."TSQueryLogupdate" IS
{CREATE TRIGGER TSQueryLogupdate
BEFORE UPDATE ORDER 1 ON
pears.TSQueryLog
REFERENCING OLD AS old_tsq NEW AS new_tsq
FOR each ROW
BEGIN
IF old_tsq.status<>new_tsq.status THEN
IF EXISTS(SELECT * FROM wpkglobalswitchsetting WHERE wpkglobalswitchid = 'TSQUERYPOPUP' AND VALUE = 'Y') AND(new_tsq.WhoEntered IS NOT NULL)
AND(new_tsq.Tempdeskid IS NOT NULL) AND(new_tsq.Status = 'F') THEN
-- have a tempdesk, is for front office and the switch is on
FOR StaffLoop AS StaffCursor no scroll cursor FOR SELECT StaffID AS @StaffID FROM staff WHERE tempdeskid = new_tsq.Tempdeskid FOR READ ONLY
do
INSERT INTO diary( diaryid,staffid,diaryfrom,diaryto,personid,description,notes,durationtype,duration,PopupMinutes,PopupTime ) VALUES( uniquekey('x'),@StaffID,CURRENT TIMESTAMP,
CURRENT TIMESTAMP,new_tsq.Personid,
string('A Timesheet Query was transferred ',new_tsq.RefNo),
string('Temp - ',(SELECT name FROM person WHERE personid = new_tsq.Personid),"char"(13),'Desk - ',(SELECT name FROM tempdesk WHERE tempdeskid = new_tsq.TempdeskID),
"char"(13),'Created by - ',(SELECT name FROM staff WHERE staffid = new_tsq.WhoEntered),
"char"(13),'Reason - ',(SELECT reasonname FROM TSQueryReasons WHERE reasoncode = new_tsq.reasoncode),
"char"(13),'Timesheet - ',(SELECT serialnumber FROM tempprovtimesheet WHERE tempprovtimesheetid = new_tsq.tempprovtimesheetid),"char"(13),
'Notes - ',new_tsq.notes),'R',0,0,dateadd(SECOND,1,CURRENT TIMESTAMP) )
END FOR END IF END IF
END
}
GO