====== pears.diary ======
<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 =====
Appointment diary records.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **diaryid** | char(20) | NOT NULL |  |  |
| staffid | char(20) | NOT NULL |  |  |
| personid | char(20) | NULL |  |  |
| vacancyid | char(20) | NULL |  |  |
| employmentid | char(20) | NULL |  |  |
| progressid | char(20) | NULL |  |  |
| placementid | char(20) | NULL |  |  |
| diaryfrom | timestamp | NOT NULL |  |  |
| diaryto | timestamp | NULL |  |  |
| description | char(100) | NULL |  |  |
| notes | long varchar | NULL |  |  |
| DurationType | char(1) | NULL |  | R=Reminder, M=Minutes, H=Hours, D=Days, W=Weeks |
| Duration | double | NULL |  |  |
| PopupMinutes | smallint | NULL |  |  |
| PopupTime | timestamp | 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 |
| OriginalDiaryID | char(20) | NULL |  | Used to prevent circular escalations |
| groupid | char(20) | NULL |  | link multiple staff to diary |
| RecurDays | integer | NULL | 0 |  |
| priority | smallint | NULL | 5 | 1 - high, 5 - low |
| CanEscalate | integer | NULL |  |  |
| ICSFileUID | char(20) | NULL |  |  |
| collectionid | char(20) | NULL |  |  |
| ContactEventID | char(20) | NULL |  |  |

===== Primary Key =====
  * diaryid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| staff | staffid | [[database:tables:pears_staff|pears.staff (staffid)]] | NOT NULL;  |
| person | personid | [[database:tables:pears_person|pears.Person (personid)]] | ON DELETE SET NULL |
| vacancy | vacancyid | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] | ON DELETE SET NULL |
| employment | employmentid | [[database:tables:pears_employment|pears.employment (employmentid)]] | ON DELETE SET 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 |
| collection | collectionid | [[database:tables:pears_collection|pears.Collection (CollectionID)]] | ON DELETE SET NULL |
| contactevent | ContactEventID | [[database:tables:pears_contactevent|pears.contactevent (contacteventid)]] | ON DELETE CASCADE |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_iqxnetnotificationcascade|pears.IQXNetNotificationCascade]] | Diary | DiaryID | diaryid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| diary_from | Index | diaryfrom |  |
| diary_stafffrom | Index | staffid, diaryfrom |  |
| diary_to | Index | diaryto |  |
| diary_staffto | Index | staffid, diaryto |  |
| diary_popup | Index | PopupTime |  |
| diary_whenentered | Index | WhenEntered |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| Diary_Insert | before | insert order 5 |
| Diary_Insert_2 | before | insert order 6 |
| Diary_Update | before | update order 1 |
| Diary_Update_After_TimeSynch | after | update of "diaryfrom", "diaryto" order 3 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."diary"
-- Table comment: Appointment diary records.
-- Statement count: 29

CREATE TABLE "pears"."diary" (
    "diaryid"                        char(20) NOT NULL
   ,"staffid"                        char(20) NOT NULL
   ,"personid"                       char(20) NULL
   ,"vacancyid"                      char(20) NULL
   ,"employmentid"                   char(20) NULL
   ,"progressid"                     char(20) NULL
   ,"placementid"                    char(20) NULL
   ,"diaryfrom"                      timestamp NOT NULL
   ,"diaryto"                        timestamp NULL
   ,"description"                    char(100) NULL
   ,"notes"                          long varchar NULL
   ,"DurationType"                   char(1) NULL
   ,"Duration"                       double NULL
   ,"PopupMinutes"                   smallint NULL
   ,"PopupTime"                      timestamp NULL COMPUTE ("dateadd"("minute",0-"PopupMinutes","DiaryFrom"))
   ,"WhenEntered"                    timestamp NULL DEFAULT current timestamp
   ,"WhoEntered"                     char(20) NULL
   ,"OriginalDiaryID"                char(20) NULL
   ,"groupid"                        char(20) NULL
   ,"RecurDays"                      integer NULL DEFAULT 0
   ,"priority"                       smallint NULL DEFAULT 5
   ,"CanEscalate"                    integer NULL
   ,"ICSFileUID"                     char(20) NULL
   ,"collectionid"                   char(20) NULL
   ,"ContactEventID"                 char(20) NULL
   ,PRIMARY KEY ("diaryid" ASC) 
)
go


COMMENT ON COLUMN "pears"."diary"."DurationType" IS 
	'R=Reminder, M=Minutes, H=Hours, D=Days, W=Weeks'
go


COMMENT ON COLUMN "pears"."diary"."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"."diary"."OriginalDiaryID" IS 
	'Used to prevent circular escalations'
go


COMMENT ON COLUMN "pears"."diary"."groupid" IS 
	'link multiple staff to diary'
go


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


COMMENT ON TABLE "pears"."diary" IS 
	'Appointment diary records.'
go


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


ALTER TABLE "pears"."diary"
    ADD FOREIGN KEY "person" ("personid" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."diary"
    ADD FOREIGN KEY "vacancy" ("vacancyid" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
    ON DELETE SET NULL
go


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


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


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


ALTER TABLE "pears"."diary"
    ADD FOREIGN KEY "collection" ("collectionid" ASC)
    REFERENCES "pears"."Collection" ("CollectionID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."diary"
    ADD FOREIGN KEY "contactevent" ("ContactEventID" ASC)
    REFERENCES "pears"."contactevent" ("contacteventid")
    ON DELETE CASCADE
go


CREATE INDEX "diary_from" ON "pears"."diary"
    ( "diaryfrom" )
go


CREATE INDEX "diary_stafffrom" ON "pears"."diary"
    ( "staffid","diaryfrom" )
go


CREATE INDEX "diary_to" ON "pears"."diary"
    ( "diaryto" )
go


CREATE INDEX "diary_staffto" ON "pears"."diary"
    ( "staffid","diaryto" )
go


CREATE INDEX "diary_popup" ON "pears"."diary"
    ( "PopupTime" )
go


CREATE INDEX "diary_whenentered" ON "pears"."diary"
    ( "WhenEntered" )
go


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


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


create trigger "Diary_Insert_2" before insert order 6 on
"pears"."diary"
referencing new as "new_diary"
for each row
begin
  if "new_diary"."durationtype" in( 'M','H','D','W','R' ) then
    if "isnull"("new_diary"."duration",0) = 0 then
      set "new_diary"."durationtype" = 'R';
      set "new_diary"."duration" = 0.0
    end if;
    case "new_diary"."durationtype" when 'M' then
      set "new_diary"."diaryto" = "dateadd"("minute","new_diary"."duration","new_diary"."diaryfrom") when 'H' then
      set "new_diary"."diaryto" = "dateadd"("minute",("new_diary"."duration")*60,"new_diary"."diaryfrom") when 'D' then
      set "new_diary"."diaryto" = "dateadd"("hour",("new_diary"."duration")*24,"new_diary"."diaryfrom") when 'W' then
      set "new_diary"."diaryto" = "dateadd"("day",("new_diary"."duration")*7,"new_diary"."diaryfrom") else
      set "new_diary"."diaryto" = "new_diary"."diaryfrom"
    end case
  else
    if "new_diary"."diaryto" is null then
      set "new_diary"."diaryto" = "new_diary"."diaryfrom"
    end if;
    if "datediff"("minute","new_diary"."diaryfrom","new_diary"."diaryto") < 1 then
      set "new_diary"."durationtype" = 'R';
      set "new_diary"."duration" = 0
    elseif "datediff"("hour","new_diary"."diaryfrom","new_diary"."diaryto") < 1 then
      set "new_diary"."durationtype" = 'M';
      set "new_diary"."duration" = "datediff"("minute","new_diary"."diaryfrom","new_diary"."diaryto")
    elseif "datediff"("day","new_diary"."diaryfrom","new_diary"."diaryto") < 1 then
      set "new_diary"."durationtype" = 'H';
      set "new_diary"."duration" = "round"("datediff"("minute","new_diary"."diaryfrom","new_diary"."diaryto")/60.0,2)
    else set "new_diary"."durationtype" = 'D';
      set "new_diary"."duration" = "round"("datediff"("hour","new_diary"."diaryfrom","new_diary"."diaryto")/24.0,2)
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."diary"."Diary_Insert_2" IS 
{create trigger Diary_Insert_2 
 before insert order 6 on
pears.diary
referencing new as new_diary
for each row
begin
  if new_diary.durationtype in( 'M','H','D','W','R') then
    if isnull(new_diary.duration,0) = 0 then
      set new_diary.durationtype='R';
      set new_diary.duration=0.0
    end if;
    case new_diary.durationtype when 'M' then
      set new_diary.diaryto=dateadd(minute,new_diary.duration,new_diary.diaryfrom) when 'H' then
      set new_diary.diaryto=dateadd(minute,(new_diary.duration)*60,new_diary.diaryfrom) when 'D' then
      set new_diary.diaryto=dateadd(hour,(new_diary.duration)*24,new_diary.diaryfrom) when 'W' then
      set new_diary.diaryto=dateadd(day,(new_diary.duration)*7,new_diary.diaryfrom) else
      set new_diary.diaryto=new_diary.diaryfrom
    end case
  else
    if new_diary.diaryto is null then
      set new_diary.diaryto=new_diary.diaryfrom
    end if;
    if datediff(minute,new_diary.diaryfrom,new_diary.diaryto) < 1 then
      set new_diary.durationtype='R';
      set new_diary.duration=0
    elseif datediff(hour,new_diary.diaryfrom,new_diary.diaryto) < 1 then
      set new_diary.durationtype='M';
      set new_diary.duration=datediff(minute,new_diary.diaryfrom,new_diary.diaryto)
    elseif datediff(day,new_diary.diaryfrom,new_diary.diaryto) < 1 then
      set new_diary.durationtype='H';
      set new_diary.duration=round(datediff(minute,new_diary.diaryfrom,new_diary.diaryto)/60.0,2)
    else set new_diary.durationtype='D';
      set new_diary.duration=round(datediff(hour,new_diary.diaryfrom,new_diary.diaryto)/24.0,2)
    end if
  end if
end
}
go


create trigger "Diary_Update" before update order 1 on
"pears"."diary"
referencing old as "old_diary" new as "new_diary"
for each row
begin
  if "new_diary"."durationtype" in( 'M','H','D','W','R' ) then
    if "isnull"("new_diary"."duration",0) = 0 then
      set "new_diary"."durationtype" = 'R';
      set "new_diary"."duration" = 0.0
    end if;
    case "new_diary"."durationtype" when 'M' then
      set "new_diary"."diaryto" = "dateadd"("minute","new_diary"."duration","new_diary"."diaryfrom") when 'H' then
      set "new_diary"."diaryto" = "dateadd"("minute",("new_diary"."duration")*60,"new_diary"."diaryfrom") when 'D' then
      set "new_diary"."diaryto" = "dateadd"("hour",("new_diary"."duration")*24,"new_diary"."diaryfrom") when 'W' then
      set "new_diary"."diaryto" = "dateadd"("day",("new_diary"."duration")*7,"new_diary"."diaryfrom") else
      set "new_diary"."diaryto" = "new_diary"."diaryfrom"
    end case
  else
    if "new_diary"."diaryto" is null then
      set "new_diary"."diaryto" = "new_diary"."diaryfrom"
    end if;
    if "datediff"("minute","new_diary"."diaryfrom","new_diary"."diaryto") < 1 then
      set "new_diary"."durationtype" = 'R';
      set "new_diary"."duration" = 0
    elseif "datediff"("hour","new_diary"."diaryfrom","new_diary"."diaryto") < 1 then
      set "new_diary"."durationtype" = 'M';
      set "new_diary"."duration" = "datediff"("minute","new_diary"."diaryfrom","new_diary"."diaryto")
    elseif "datediff"("day","new_diary"."diaryfrom","new_diary"."diaryto") < 1 then
      set "new_diary"."durationtype" = 'H';
      set "new_diary"."duration" = "round"("datediff"("minute","new_diary"."diaryfrom","new_diary"."diaryto")/60.0,2)
    else set "new_diary"."durationtype" = 'D';
      set "new_diary"."duration" = "round"("datediff"("hour","new_diary"."diaryfrom","new_diary"."diaryto")/24.0,2)
    end if end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."diary"."Diary_Update" IS 
{create trigger Diary_Update 
 before update order 1 on
pears.diary
referencing old as old_diary new as new_diary
for each row
begin
  if new_diary.durationtype in( 'M','H','D','W','R') then
    if isnull(new_diary.duration,0) = 0 then
      set new_diary.durationtype='R';
      set new_diary.duration=0.0
    end if;
    case new_diary.durationtype when 'M' then
      set new_diary.diaryto=dateadd(minute,new_diary.duration,new_diary.diaryfrom) when 'H' then
      set new_diary.diaryto=dateadd(minute,(new_diary.duration)*60,new_diary.diaryfrom) when 'D' then
      set new_diary.diaryto=dateadd(hour,(new_diary.duration)*24,new_diary.diaryfrom) when 'W' then
      set new_diary.diaryto=dateadd(day,(new_diary.duration)*7,new_diary.diaryfrom) else
      set new_diary.diaryto=new_diary.diaryfrom
    end case
  else
    if new_diary.diaryto is null then
      set new_diary.diaryto=new_diary.diaryfrom
    end if;
    if datediff(minute,new_diary.diaryfrom,new_diary.diaryto) < 1 then
      set new_diary.durationtype='R';
      set new_diary.duration=0
    elseif datediff(hour,new_diary.diaryfrom,new_diary.diaryto) < 1 then
      set new_diary.durationtype='M';
      set new_diary.duration=datediff(minute,new_diary.diaryfrom,new_diary.diaryto)
    elseif datediff(day,new_diary.diaryfrom,new_diary.diaryto) < 1 then
      set new_diary.durationtype='H';
      set new_diary.duration=round(datediff(minute,new_diary.diaryfrom,new_diary.diaryto)/60.0,2)
    else set new_diary.durationtype='D';
      set new_diary.duration=round(datediff(hour,new_diary.diaryfrom,new_diary.diaryto)/24.0,2)
    end if
  end if;
end
}
go


create trigger "Diary_Update_After_TimeSynch" after update of "diaryfrom",
"diaryto" order 3 on
"pears"."diary"
referencing old as "old_diary" new as "new_diary"
for each row
begin
  if "isnull"("new_diary"."groupid",'') <> '' then
    update "diary" set "diaryfrom" = "new_diary"."diaryfrom","diaryto" = "new_diary"."diaryto" where "groupid"
       = "new_diary"."groupid" and "diaryid" <> "new_diary"."diaryid"
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."diary"."Diary_Update_After_TimeSynch" IS 
{create trigger Diary_Update_After_TimeSynch 
 after update  of diaryfrom, diaryto order 3 on
pears.diary
referencing old as old_diary new as new_diary
for each row
begin
  if isnull(new_diary.groupid,'') <> '' then
update diary set diaryfrom = new_diary.diaryfrom, diaryto = new_diary.diaryto where groupid 
  = new_diary.groupid and diaryid <> new_diary.diaryid
    end if
end
}
go
</code>