-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."staff"
-- Table comment: User records.
-- Statement count: 23
CREATE TABLE "pears"."staff" (
"staffid" CHAR(20) NOT NULL
,"name" CHAR(60) NULL
,"defaultdepartid" CHAR(2) NOT NULL
,"tempdeskid" CHAR(20) NULL
,"shortid" CHAR(2) NOT NULL
,"userid" CHAR(25) NULL
,"divisionid" CHAR(20) NULL
,"title" CHAR(40) NULL
,"agencyid" CHAR(20) NULL
,"canmailmerge" SMALLINT NULL DEFAULT 0
,"cancreatereports" SMALLINT NULL DEFAULT 0
,"reportview" SMALLINT NULL DEFAULT 0
,"reportprint" SMALLINT NULL DEFAULT 0
,"reportexport" SMALLINT NULL DEFAULT 0
,"access1" SMALLINT NULL DEFAULT 0
,"access2" SMALLINT NULL DEFAULT 0
,"access3" SMALLINT NULL DEFAULT 0
,"access4" SMALLINT NULL DEFAULT 0
,"access5" SMALLINT NULL DEFAULT 0
,"password" CHAR(250) NULL
,"canedittemplates" SMALLINT NULL DEFAULT 0
,"candragmerge" SMALLINT NULL DEFAULT 0
,"departmentmaint" SMALLINT NULL DEFAULT 0
,"empquestmaint" SMALLINT NULL DEFAULT 0
,"wpletters" SMALLINT NULL DEFAULT 0
,"wpcvs" SMALLINT NULL DEFAULT 0
,"defunct" SMALLINT NULL DEFAULT 0
,"EMail" CHAR(100) NULL
,"manager" SMALLINT NULL DEFAULT 0
,"tempsaccess" SMALLINT NULL DEFAULT 0
,"Keyname" CHAR(30) NULL
,"AnalysisCode" CHAR(20) NULL
,"rateschememaint" SMALLINT NULL DEFAULT 0
,"template" SMALLINT NULL DEFAULT 0
,"divisionaccess" SMALLINT NULL DEFAULT 0
,"ChangeTracker" INTEGER NULL
,"WPKMainFormID" CHAR(20) NULL
,"WPKStartupForm" CHAR(100) NULL
,"ComboVisibility" CHAR(25) NULL
,"inboxlimit" SMALLINT NULL DEFAULT 0
,"inboxrefreshrate" SMALLINT NULL DEFAULT 0
,"monitorandform" long VARCHAR NULL
,"SMTPSend" SMALLINT NULL DEFAULT 0
,"SMTPUID" CHAR(50) NULL
,"SMTPPWD" CHAR(50) NULL
,"IMAPFetch" SMALLINT NULL DEFAULT 0
,"IMAPUID" CHAR(50) NULL
,"IMAPPWD" CHAR(50) NULL
,"passwordvaliduntil" DATE NULL
,"logins" INTEGER NULL
,"ExtensionNumber" CHAR(50) NULL
,"DoNotDisturb" tinyint NULL
,"LeaveDate" DATE NULL
,"TSQueryCode" CHAR(5) NULL
,"OpenInDefaultDivision" SMALLINT NULL DEFAULT 0
,"menupreference" SMALLINT NULL DEFAULT 0
,"formlimit" SMALLINT NULL DEFAULT 0
,"WhenCreated" TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
,"OauthAccessToken" long VARCHAR NULL
,"OauthRefreshToken" long VARCHAR NULL
,"IQXNetEmailDetailsID" CHAR(20) NULL
,"ThemeFile" CHAR(40) NULL
,"RelaxedRowHeight" tinyint NULL
,"ThemeFormColours" tinyint NULL DEFAULT 0
,"ThemeSystemBorder" tinyint NULL DEFAULT 0
,"mobile" CHAR(20) NULL
,"directdial" CHAR(20) NULL
,"IsSystem" tinyint NULL DEFAULT 0
,"ShiftButtonDefaults" INTEGER NULL
,"dashboardRefreshInterval" SMALLINT NULL DEFAULT 0
,"homepageRefreshInterval" SMALLINT NULL DEFAULT 0
,"tempdeskaccess" SMALLINT NULL DEFAULT 0
,"SqlWindowFontSize" INTEGER NULL
,PRIMARY KEY ("staffid" ASC)
)
GO
COMMENT ON COLUMN "pears"."staff"."divisionaccess" IS
'0=all 1=own 2=selected'
GO
COMMENT ON COLUMN "pears"."staff"."WPKMainFormID" IS
'Used instead of MAIN as the template for the mdi menu and button bar'
GO
COMMENT ON COLUMN "pears"."staff"."WPKStartupForm" IS
'A child form with switches to load on startup, comma separated e.g. TEMPDESK,x'
GO
COMMENT ON COLUMN "pears"."staff"."SMTPSend" IS
'1=yes 0=no i.e. use the old default email settings'
GO
COMMENT ON COLUMN "pears"."staff"."IMAPFetch" IS
'+ve value=fetch frequency in minutes, any -ve value=manual fetch, 0=no i.e. use the old default email settings'
GO
COMMENT ON COLUMN "pears"."staff"."dashboardRefreshInterval" IS
'Seconds'
GO
COMMENT ON COLUMN "pears"."staff"."homepageRefreshInterval" IS
'Seconds'
GO
COMMENT ON COLUMN "pears"."staff"."tempdeskaccess" IS
'0=all 1=own 2=selected'
GO
COMMENT ON TABLE "pears"."staff" IS
'User records.'
GO
ALTER TABLE "pears"."staff"
ADD NOT NULL FOREIGN KEY "department" ("defaultdepartid" ASC)
REFERENCES "pears"."Department" ("departmentid")
GO
ALTER TABLE "pears"."staff"
ADD FOREIGN KEY "division" ("divisionid" ASC)
REFERENCES "pears"."Division" ("divisionid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."staff"
ADD FOREIGN KEY "agencydetails" ("agencyid" ASC)
REFERENCES "pears"."agencydetails" ("agencyid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."staff"
ADD FOREIGN KEY "tempdesk" ("tempdeskid" ASC)
REFERENCES "pears"."tempdesk" ("tempdeskid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."staff"
ADD FOREIGN KEY "IQXNetEmailDetails" ("IQXNetEmailDetailsID" ASC)
REFERENCES "pears"."IQXNetEmailDetails" ("IQXNetEmailDetailsID")
ON DELETE SET NULL
GO
CREATE INDEX "StaffName" ON "pears"."staff"
( "name" )
GO
CREATE UNIQUE INDEX "staff_userid" ON "pears"."staff"
( "userid" )
GO
CREATE TRIGGER "WPK_staff_STAFF" after INSERT,DELETE,UPDATE ORDER 1 ON
"pears"."staff"
FOR each statement
BEGIN
CALL "WPKTrackChange"('P','STAFF')
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."staff"."WPK_staff_STAFF" IS
{CREATE TRIGGER WPK_staff_STAFF
after INSERT,DELETE,UPDATE ORDER 1 ON
staff
FOR each statement
BEGIN
CALL WPKTrackChange('P','STAFF')
END
}
GO
CREATE TRIGGER "StaffAudit" BEFORE UPDATE OF "divisionaccess",
"defunct","divisionid","tempdeskid","agencyid",
"Defaultdepartid" ORDER 1 ON "pears"."Staff"
REFERENCING OLD AS "old_staff" NEW AS "new_staff"
FOR each ROW
WHEN(EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'User' AND "AuditFlag" = 1))
BEGIN
DECLARE "AuditList" long VARCHAR;
DECLARE "OldDescrip" CHAR(10);
DECLARE "NewDescrip" CHAR(10);
DECLARE "StaffName" CHAR(25);
SELECT "string"(',',"list"("ItemName"),',') INTO "AuditList" FROM "AuditItems" WHERE "AreaName" = 'User' AND "AuditFlag" = 1;
SELECT "userid" INTO "StaffName" FROM "staff" WHERE "staffid" = "new_staff"."staffid";
-- Division Access
IF "locate"("AuditList",',Division Access,') > 0 AND UPDATE("divisionaccess") THEN
SELECT CASE "old_staff"."divisionaccess" WHEN 0 THEN 'All' WHEN 1 THEN 'Own' WHEN 2 THEN 'Selected' END INTO "OldDescrip";
SELECT CASE "new_staff"."divisionaccess" WHEN 0 THEN 'All' WHEN 1 THEN 'Own' WHEN 2 THEN 'Selected' END INTO "NewDescrip";
CALL "AuditLog"('USER',"old_staff"."staffid","string"('Division Access Updated - ',"StaffName"),"OldDescrip","NewDescrip")
END IF;
IF "locate"("AuditList",',Not in Use,') > 0 AND UPDATE("defunct") THEN
CALL "AuditLog"('USER',"old_staff"."staffid","string"('Not In Use updated - ',"StaffName"),"old_staff"."defunct","new_staff"."defunct")
END IF;
IF "locate"("AuditList",',Division,') > 0 AND UPDATE("divisionid") THEN
CALL "AuditLog"('USER',"old_staff"."staffid","string"('Division updated - ',"StaffName"),(SELECT "name" FROM "division" AS "d" WHERE "d"."divisionid" = "old_staff"."divisionid"),(SELECT "name" FROM "division" AS "d" WHERE "d"."divisionid" = "new_staff"."divisionid"))
END IF;
IF "locate"("AuditList",',Default Department,') > 0 AND UPDATE("Defaultdepartid") THEN
CALL "AuditLog"('USER',"old_staff"."staffid","string"('Default Department updated - ',"StaffName"),"old_staff"."Defaultdepartid","new_staff"."Defaultdepartid")
END IF;
IF "locate"("AuditList",',Branch,') > 0 AND UPDATE("agencyid") THEN
CALL "AuditLog"('USER',"old_staff"."staffid","string"('Branch updated - ',"StaffName"),(SELECT "name" FROM "agencydetails" AS "a" WHERE "a"."agencyid" = "old_staff"."agencyid"),(SELECT "name" FROM "agencydetails" AS "a" WHERE "a"."agencyid" = "new_staff"."agencyid"))
END IF;
IF "locate"("AuditList",',Default Tempdesk,') > 0 AND UPDATE("tempdeskid") THEN
CALL "AuditLog"('USER',"old_staff"."staffid","string"('Default Tempdesk updated - ',"StaffName"),(SELECT "name" FROM "tempdesk" AS "t" WHERE "t"."tempdeskid" = "old_staff"."tempdeskid"),(SELECT "name" FROM "tempdesk" AS "t" WHERE "t"."tempdeskid" = "new_staff"."tempdeskid"))
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."staff"."StaffAudit" IS
{CREATE TRIGGER StaffAudit
BEFORE UPDATE OF divisionaccess, defunct,divisionid,tempdeskid,agencyid,Defaultdepartid
ORDER 1 ON pears.Staff
REFERENCING OLD AS old_staff NEW AS new_staff
FOR each ROW
WHEN(EXISTS(SELECT* FROM AuditItems WHERE AreaName = 'User' AND AuditFlag = 1))
BEGIN
DECLARE AuditList long VARCHAR;
DECLARE OldDescrip CHAR(10);
DECLARE NewDescrip CHAR(10);
DECLARE StaffName CHAR(25);
SELECT string(',',list(ItemName),',') INTO AuditList FROM AuditItems WHERE AreaName = 'User' AND AuditFlag = 1;
SELECT userid INTO StaffName FROM staff WHERE staffid = new_staff.staffid;
-- Division Access
IF locate(AuditList,',Division Access,') > 0 AND UPDATE(divisionaccess) THEN
SELECT CASE old_staff.divisionaccess WHEN 0 THEN 'All' WHEN 1 THEN 'Own' WHEN 2 THEN 'Selected' END INTO OldDescrip ;
SELECT CASE new_staff.divisionaccess WHEN 0 THEN 'All' WHEN 1 THEN 'Own' WHEN 2 THEN 'Selected' END INTO NewDescrip ;
CALL AuditLog('USER',old_staff.staffid,string('Division Access Updated - ',StaffName),OldDescrip,NewDescrip)
END IF;
IF locate(AuditList,',Not in Use,') > 0 AND UPDATE(defunct) THEN
CALL AuditLog('USER',old_staff.staffid,string('Not In Use updated - ',StaffName),old_staff.defunct,new_staff.defunct)
END IF;
IF locate(AuditList,',Division,') > 0 AND UPDATE(divisionid) THEN
CALL AuditLog('USER',old_staff.staffid,string('Division updated - ',StaffName),(SELECT name FROM division d WHERE d.divisionid =old_staff.divisionid),(SELECT name FROM division d WHERE d.divisionid =new_staff.divisionid))
END IF;
IF locate(AuditList,',Default Department,') > 0 AND UPDATE(Defaultdepartid) THEN
CALL AuditLog('USER',old_staff.staffid,string('Default Department updated - ',StaffName),old_staff.Defaultdepartid,new_staff.Defaultdepartid)
END IF;
IF locate(AuditList,',Branch,') > 0 AND UPDATE(agencyid) THEN
CALL AuditLog('USER',old_staff.staffid,string('Branch updated - ',StaffName),(SELECT name FROM agencydetails a WHERE a.agencyid =old_staff.agencyid),(SELECT name FROM agencydetails a WHERE a.agencyid =new_staff.agencyid))
END IF;
IF locate(AuditList,',Default Tempdesk,') > 0 AND UPDATE(tempdeskid) THEN
CALL AuditLog('USER',old_staff.staffid,string('Default Tempdesk updated - ',StaffName),(SELECT name FROM tempdesk t WHERE t.tempdeskid =old_staff.tempdeskid),(SELECT name FROM tempdesk t WHERE t.tempdeskid = new_staff.tempdeskid))
END IF;
END
}
GO
CREATE TRIGGER "StaffFuzeEmail" BEFORE UPDATE OF "email"
ORDER 2 ON "pears"."Staff"
REFERENCING OLD AS "old_staff"
FOR each ROW
BEGIN
DELETE FROM "FuzeUsers" WHERE "staffid" = "old_staff"."staffid"
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."staff"."StaffFuzeEmail" IS
{CREATE TRIGGER StaffFuzeEmail
BEFORE UPDATE OF email
ORDER 2 ON pears.Staff
REFERENCING OLD AS old_staff
FOR each ROW
BEGIN
DELETE FROM FuzeUsers WHERE staffid = old_staff.staffid;
END
}
GO
127.0.0.1