pears.TSQueryLog

Timesheet Queries - current state of each Query (log is an inaccurate description)

• TSQueryLogID

-- 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