pears.contactevent [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.contactevent ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Details of Contacts with candidate & client contacts. Optional links to vacancies - progress etc etc.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **contacteventid** | char(20) | NOT NULL |  |  |
| personid | char(20) | NULL |  |  |
| staffid | char(20) | NOT NULL |  |  |
| vacancyid | char(20) | NULL |  |  |
| employmentid | char(20) | NULL |  |  |
| progressid | char(20) | NULL |  |  |
| placementid | char(20) | NULL |  |  |
| contactdate | date | NOT NULL |  |  |
| contacttime | time | NULL |  |  |
| description | char(100) | NULL |  |  |
| outcome | char(50) | NULL |  |  |
| classcode | char(2) | NOT NULL |  |  |
| who | char(1) | NULL |  |  |
| notes | long varchar | NULL |  |  |
| lettertext | long varchar | NULL |  |  |
| followup | smallint | NULL | 0 |  |
| wpdoc | char(10) | NULL |  |  |
| callbackdate | date | NULL |  |  |
| divisionid | char(20) | NULL |  |  |
| WhenEntered | timestamp | NULL | current timestamp |  |
| WhoEntered | char(20) | NULL |  | Auto-entered staffid, but no ref integ because it would break numerous key joins by making them ambiguous |
| callbacktime | time | NULL |  |  |
| priority | smallint | NULL | 5 | 1 - high, 5 - low |
| MIMEMessageID | char(20) | NULL |  |  |
| SynetyGUID | char(100) | NULL |  |  |
| speciality | char(50) | NULL |  |  |
| forenames | char(30) | NULL |  |  |
| surname | char(30) | NULL |  |  |

===== Primary Key =====
  * contacteventid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| person | personid | [[database:tables:pears_person|pears.Person (personid)]] |  |
| staff | staffid | [[database:tables:pears_staff|pears.staff (staffid)]] | NOT NULL;  |
| progress | progressid | [[database:tables:pears_progress|pears.progress (progressid)]] | ON DELETE SET NULL |
| placement | placementid | [[database:tables:pears_placement|pears.Placement (placementid)]] | ON DELETE SET NULL |
| contactclass | classcode | [[database:tables:pears_contactclass|pears.contactclass (classcode)]] | NOT NULL;  |
| employment | employmentid | [[database:tables:pears_employment|pears.employment (employmentid)]] | ON DELETE SET NULL |
| division | divisionid | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |
| vacancy | vacancyid | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] |  |
| MIMEMessage | MIMEMessageID | [[database:tables:pears_mimemessage|pears.MIMEMessage (MIMEMessageID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_contacteventemaillog|pears.ContactEventEmailLog]] | ContactEvent | ContactEventID | contacteventid |
| [[database:tables:pears_diary|pears.diary]] | contactevent | ContactEventID | contacteventid |
| [[database:tables:pears_sentcvs|pears.SentCVs]] | contactevent | contacteventid | contacteventid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| contactevent_contactdate | Index | contactdate |  |
| Exp1 | Index | contactdate, personid |  |
| Exp2 | Index | contactdate, contacttime |  |
| Exp3 | Index | contactdate, contacttime, staffid |  |
| Test1 | Index | staffid |  |
| contactevent_callbackdate | Index | callbackdate |  |
| contactevent_StaffContactDate | Index | staffid, contactdate |  |
| contactevent_staffCallbackdate | Index | staffid, callbackdate |  |
| contactevent_WhenEntered | Index | WhenEntered |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| ContactEvent_Insert | before | insert order 5 |
| ContactEvent_Insert2 | before | insert order 6 |
| ContactEventAudit | before | update of "callbackdate", "classcode","description" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."contactevent"
-- Table comment: Details of Contacts with candidate & client contacts. Optional links to vacancies - progress etc etc.
-- Statement count: 28

CREATE TABLE "pears"."contactevent" (
    "contacteventid"                 char(20) NOT NULL
   ,"personid"                       char(20) NULL
   ,"staffid"                        char(20) NOT NULL
   ,"vacancyid"                      char(20) NULL
   ,"employmentid"                   char(20) NULL
   ,"progressid"                     char(20) NULL
   ,"placementid"                    char(20) NULL
   ,"contactdate"                    date NOT NULL
   ,"contacttime"                    time NULL
   ,"description"                    char(100) NULL
   ,"outcome"                        char(50) NULL
   ,"classcode"                      char(2) NOT NULL
   ,"who"                            char(1) NULL
   ,"notes"                          long varchar NULL
   ,"lettertext"                     long varchar NULL
   ,"followup"                       smallint NULL DEFAULT 0
   ,"wpdoc"                          char(10) NULL
   ,"callbackdate"                   date NULL
   ,"divisionid"                     char(20) NULL
   ,"WhenEntered"                    timestamp NULL DEFAULT current timestamp
   ,"WhoEntered"                     char(20) NULL
   ,"callbacktime"                   time NULL
   ,"priority"                       smallint NULL DEFAULT 5
   ,"MIMEMessageID"                  char(20) NULL
   ,"SynetyGUID"                     char(100) NULL
   ,"speciality"                     char(50) NULL
   ,"forenames"                      char(30) NULL
   ,"surname"                        char(30) NULL
   ,PRIMARY KEY ("contacteventid" ASC) 
)
go


COMMENT ON COLUMN "pears"."contactevent"."WhoEntered" IS 
	'Auto-entered staffid, but no ref integ because it would break numerous key joins by making them ambiguous'
go


COMMENT ON COLUMN "pears"."contactevent"."priority" IS 
	'1 - high, 5 - low'
go


COMMENT ON TABLE "pears"."contactevent" IS 
	'Details of Contacts with candidate & client contacts. Optional links to vacancies - progress etc etc.'
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "person" ("personid" ASC)
    REFERENCES "pears"."Person" ("personid")
go


ALTER TABLE "pears"."contactevent"
    ADD NOT NULL FOREIGN KEY "staff" ("staffid" ASC)
    REFERENCES "pears"."staff" ("staffid")
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "progress" ("progressid" ASC)
    REFERENCES "pears"."progress" ("progressid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "placement" ("placementid" ASC)
    REFERENCES "pears"."Placement" ("placementid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."contactevent"
    ADD NOT NULL FOREIGN KEY "contactclass" ("classcode" ASC)
    REFERENCES "pears"."contactclass" ("classcode")
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "employment" ("employmentid" ASC)
    REFERENCES "pears"."employment" ("employmentid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "division" ("divisionid" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "vacancy" ("vacancyid" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
go


ALTER TABLE "pears"."contactevent"
    ADD FOREIGN KEY "MIMEMessage" ("MIMEMessageID" ASC)
    REFERENCES "pears"."MIMEMessage" ("MIMEMessageID")
    ON DELETE SET NULL
go


CREATE INDEX "contactevent_contactdate" ON "pears"."contactevent"
    ( "contactdate" DESC )
go


CREATE INDEX "Exp1" ON "pears"."contactevent"
    ( "contactdate" DESC,"personid" )
go


CREATE INDEX "Exp2" ON "pears"."contactevent"
    ( "contactdate" DESC,"contacttime" DESC )
go


CREATE INDEX "Exp3" ON "pears"."contactevent"
    ( "contactdate" DESC,"contacttime" DESC,"staffid" )
go


CREATE INDEX "Test1" ON "pears"."contactevent"
    ( "staffid" )
go


CREATE INDEX "contactevent_callbackdate" ON "pears"."contactevent"
    ( "callbackdate" )
go


CREATE INDEX "contactevent_StaffContactDate" ON "pears"."contactevent"
    ( "staffid","contactdate" DESC )
go


CREATE INDEX "contactevent_staffCallbackdate" ON "pears"."contactevent"
    ( "staffid","callbackdate" )
go


CREATE INDEX "contactevent_WhenEntered" ON "pears"."contactevent"
    ( "WhenEntered" )
go


create trigger "ContactEvent_Insert" before insert order 5 on
"pears"."contactevent"
referencing new as "new_ce"
for each row
when("new_ce"."whoentered" is null)
begin
  set "new_ce"."whoentered" = "userstaffid"
exception
  when others then
    set "new_ce"."whoentered" = null
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."contactevent"."ContactEvent_Insert" IS 
{create trigger ContactEvent_Insert 
 before insert order 5 on
pears.contactevent
referencing new as new_ce
for each row
when(new_ce.whoentered is null)
begin
  set new_ce.whoentered=userstaffid
exception
  when others then
    set new_ce.whoentered=null
end
}
go


create trigger "ContactEvent_Insert2" before insert order 6 on
"pears"."contactevent"
referencing new as "new_ce"
for each row
begin
  declare "newwhen" timestamp;
  set "newwhen" = "new_ce"."contactdate"+"new_ce"."contacttime";
  if "newwhen" < current date+10 then
    if "new_ce"."personid" is not null then update "person" set "lastcontactevent" = "newwhen"
        where "personid" = "new_ce"."personid" and "newwhen" > "isnull"("lastcontactevent",'2001-01-01')
    end if;
    if "new_ce"."vacancyid" is not null then update "vacancy" set "lastcontactevent" = "newwhen"
        where "vacancyid" = "new_ce"."vacancyid" and "newwhen" > "isnull"("lastcontactevent",'2001-01-01')
    end if;
    if "new_ce"."placementid" is not null then update "placement" set "lastcontactevent" = "newwhen"
        where "placementid" = "new_ce"."placementid" and "newwhen" > "isnull"("lastcontactevent",'2001-01-01')
    end if;
    if "new_ce"."employmentid" is not null then update "employment" set "lastcontactevent" = "newwhen"
        where "employmentid" = "new_ce"."employmentid" and "newwhen" > "isnull"("lastcontactevent",'2001-01-01');
      update "company" key join "employment" set "company"."lastcontactevent" = "newwhen"
        where "employmentid" = "new_ce"."employmentid" and "newwhen" > "isnull"("company"."lastcontactevent",'2001-01-01');
      update "person" key join "employment" set "person"."lastcontactevent" = "newwhen"
        where "employmentid" = "new_ce"."employmentid" and "newwhen" > "isnull"("person"."lastcontactevent",'2001-01-01')
    end if end if;
  if "WPKMaintainGetSwitchValue"('CESTAFF','','L') = 'N' then
    set "new_ce"."divisionid" = "isnull"((select "divisionid" from "person" where "personid" = "new_ce"."personid"),
      (select "company"."divisionid" from "company" key join "employment" where "employmentid" = "new_ce"."employmentid"),
      (select "company"."divisionid" from "vacancy" key join "employment" key join "company" where "vacancyid" = "new_ce"."vacancyid"),
      (select "company"."divisionid" from "placement" key join "vacancy" key join "employment" key join "company" where "placementid" = "new_ce"."placementid"))
  end if;
  if "new_ce"."personid" is not null and "new_ce"."divisionid" is not null then
    if "new_ce"."classcode" = any(select "classcode" from "ContClassPersonDivFilter" where "divisionid" = "new_ce"."divisionid") then
      update "person" set "FilteredLastContactEvent" = "newwhen" where "personid" = "new_ce"."personid" and "newwhen" > "isnull"("FilteredLastContactEvent",'2001-01-01')
    end if end if;
  if "WPKUserPerformsRole"("new_ce"."whoentered",'CEAVAILALL') = 1 then
    set "new_ce"."divisionid" = null
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."contactevent"."ContactEvent_Insert2" IS 
{create trigger ContactEvent_Insert2 
 before insert order 6 on
pears.contactevent
referencing new as new_ce
for each row
begin
  declare newwhen timestamp;
  set newwhen = new_ce.contactdate+new_ce.contacttime;
  if newwhen < current date+10 then
    if new_ce.personid is not null then update person set lastcontactevent = newwhen
        where personid = new_ce.personid and newwhen > isnull(lastcontactevent,'2001-01-01')
    end if;
    if new_ce.vacancyid is not null then update vacancy set lastcontactevent = newwhen
        where vacancyid = new_ce.vacancyid and newwhen > isnull(lastcontactevent,'2001-01-01')
    end if;
    if new_ce.placementid is not null then update placement set lastcontactevent = newwhen
        where placementid = new_ce.placementid and newwhen > isnull(lastcontactevent,'2001-01-01')
    end if;
    if new_ce.employmentid is not null then update employment set lastcontactevent = newwhen
        where employmentid = new_ce.employmentid and newwhen > isnull(lastcontactevent,'2001-01-01');
      update company key join employment set company.lastcontactevent = newwhen
        where employmentid = new_ce.employmentid and newwhen > isnull(company.lastcontactevent,'2001-01-01');
      update person key join employment set person.lastcontactevent = newwhen
        where employmentid = new_ce.employmentid and newwhen > isnull(person.lastcontactevent,'2001-01-01')
    end if
  end if;
  if WPKMaintainGetSwitchValue('CESTAFF','','L') = 'N' then
    set new_ce.divisionid = isnull((select divisionid from person where personid = new_ce.personid),
      (select company.divisionid from company key join employment where employmentid = new_ce.employmentid),
      (select company.divisionid from vacancy key join employment key join company where vacancyid = new_ce.vacancyid),
      (select company.divisionid from placement key join vacancy key join employment key join company where placementid = new_ce.placementid))
  end if;
  if new_ce.personid is not null and new_ce.divisionid is not null then
    if new_ce.classcode = any(select classcode from ContClassPersonDivFilter where divisionid = new_ce.divisionid) then
      update person set FilteredLastContactEvent = newwhen where personid = new_ce.personid and newwhen > isnull(FilteredLastContactEvent,'2001-01-01')
    end if
  end if;
  if WPKUserPerformsRole(new_ce.whoentered,'CEAVAILALL') = 1 then
    set new_ce.divisionid = null
  end if
end
}
go


create trigger "ContactEventAudit" before update of "callbackdate",
"classcode","description" order 1 on
"pears"."contactevent"
referencing old as "old_ce" new as "new_ce"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Contact Event' 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" = 'Contact Event' and "AuditFlag" = 1;
  if "locate"("AuditList",',Callback Date,') > 0 and update("callbackdate") then
    select "old_ce"."callbackdate" into "OldDescrip";
    select "new_ce"."callbackdate" into "NewDescrip";
    select "userid" into "staffname" from "staff" where "staffid" = "old_ce"."staffid";
    call "AuditLog"('CEVENT',"old_ce"."contacteventid","string"("old_ce"."description",' Updated - Callback Date ',"old_ce"."contactdate",' by ',"staffname"),"OldDescrip","NewDescrip")
  end if;
  if "locate"("AuditList",',Type,') > 0 and update("classcode") then
    select "classdescrip" into "OldDescrip" from "contactclass" where "classcode" = "old_ce"."classcode";
    select "classdescrip" into "NewDescrip" from "contactclass" where "classcode" = "new_ce"."classcode";
    select "userid" into "staffname" from "staff" where "staffid" = "old_ce"."staffid";
    call "AuditLog"('CEVENT',"old_ce"."contacteventid","string"("old_ce"."description",' Updated - Type ',"olddescrip",' by ',"staffname"),"OldDescrip","NewDescrip")
  end if;
  if "locate"("AuditList",',Summary,') > 0 and update("description") then
    select "old_ce"."description" into "OldDescrip";
    select "new_ce"."description" into "NewDescrip";
    select "userid" into "staffname" from "staff" where "staffid" = "old_ce"."staffid";
    call "AuditLog"('CEVENT',"old_ce"."contacteventid","string"("old_ce"."description",' Updated - Summary ',"olddescrip",' by ',"staffname"),"OldDescrip","NewDescrip")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."contactevent"."ContactEventAudit" IS 
{create trigger ContactEventAudit 
 before update of callbackdate,classcode,description,
 order 1 on pears.contactevent
referencing old as old_ce new as new_ce
for each row
when(exists(select* from AuditItems where AreaName = 'Contact Event' 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 = 'Contact Event' and AuditFlag = 1;
  if locate(AuditList,',Callback Date,') > 0 and update(callbackdate) then
    select old_ce.callbackdate into OldDescrip ;
    select new_ce.callbackdate into NewDescrip ;
    select userid into staffname from staff where staffid = old_ce.staffid ;
    call AuditLog('CEVENT',old_ce.contacteventid,string(old_ce.description, ' Updated - Callback Date ',old_ce.contactdate,' by ',staffname),OldDescrip,NewDescrip)
  end if;
  if locate(AuditList,',Type,') > 0 and update(classcode) then
    select classdescrip into OldDescrip from contactclass where classcode = old_ce.classcode ;
    select classdescrip into NewDescrip from contactclass where classcode = new_ce.classcode ;
    select userid into staffname from staff where staffid = old_ce.staffid ;
    call AuditLog('CEVENT',old_ce.contacteventid,string(old_ce.description, ' Updated - Type ',olddescrip,' by ',staffname),OldDescrip,NewDescrip)
  end if;
  if locate(AuditList,',Summary,') > 0 and update(description) then
    select old_ce.description into OldDescrip ;
    select new_ce.description into NewDescrip ;
    select userid into staffname from staff where staffid = old_ce.staffid ;
    call AuditLog('CEVENT',old_ce.contacteventid,string(old_ce.description, ' Updated - Summary ',olddescrip,' by ',staffname),OldDescrip,NewDescrip)
  end if;
end
}
go
</code>