====== pears.oledocument ======
<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 =====
Pointers to documents belonging to People & Companies.  Stored in the Blobstore (Class O)

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **oledocumentid** | char(20) | NOT NULL |  |  |
| description | char(50) | NULL |  |  |
| ownertype | char(2) | NULL |  |  |
| ownerid | char(20) | NULL |  |  |
| RawFile | tinyint | NULL |  | Set if storing the original file NOT in an OLE wrapper |
| FileExtension | char(5) | NULL |  | If RawFile |
| DocumentTypeID | char(20) | NULL |  |  |
| ExternalFilePath | long varchar | NULL |  |  |
| LinkCreatedBy | char(25) | NULL |  |  |
| LinkCreatedDateTime | timestamp | NULL |  |  |
| WhenArchived | timestamp | NULL |  |  |
| WhoArchived | char(20) | NULL |  |  |
| WhenRedacted | timestamp | NULL |  |  |
| WhoRedacted | char(20) | NULL |  |  |
| GlobalDoc | smallint | NULL |  |  |
| Defunct | tinyint | NULL | 0 |  |
| deletiondate | date | NULL |  |  |
| Signed | smallint | NULL |  |  |
| ExpiryDate | date | NULL |  |  |

===== Primary Key =====
  * oledocumentid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| DocumentType | DocumentTypeID | [[database:tables:pears_documenttype|pears.DocumentType (DocumentTypeID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_emailtosendattachment|pears.EmailToSendAttachment]] | oledocument | OLEDocumentID | oledocumentid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| oledoc_owner | Index | ownertype, ownerid |  |
| oledocumentOwneridDocumentTypeID | Index | ownertype, ownerid, DocumentTypeID |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| oledocumentauditinsdel | before | insert,delete order 1 |
| oledocumentautoarchive | after | insert order 2 |
| oledocument_updateaudit | after | update of "deletiondate" order 3 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."oledocument"
-- Table comment: Pointers to documents belonging to People & Companies.  Stored in the Blobstore (Class O)
-- Statement count: 13

CREATE TABLE "pears"."oledocument" (
    "oledocumentid"                  char(20) NOT NULL
   ,"description"                    char(50) NULL
   ,"ownertype"                      char(2) NULL
   ,"ownerid"                        char(20) NULL
   ,"RawFile"                        tinyint NULL
   ,"FileExtension"                  char(5) NULL
   ,"DocumentTypeID"                 char(20) NULL
   ,"ExternalFilePath"               long varchar NULL
   ,"LinkCreatedBy"                  char(25) NULL
   ,"LinkCreatedDateTime"            timestamp NULL
   ,"WhenArchived"                   timestamp NULL
   ,"WhoArchived"                    char(20) NULL
   ,"WhenRedacted"                   timestamp NULL
   ,"WhoRedacted"                    char(20) NULL
   ,"GlobalDoc"                      smallint NULL
   ,"Defunct"                        tinyint NULL DEFAULT 0
   ,"deletiondate"                   date NULL
   ,"Signed"                         smallint NULL
   ,"ExpiryDate"                     date NULL
   ,PRIMARY KEY ("oledocumentid" ASC) 
)
go


COMMENT ON COLUMN "pears"."oledocument"."RawFile" IS 
	'Set if storing the original file NOT in an OLE wrapper'
go


COMMENT ON COLUMN "pears"."oledocument"."FileExtension" IS 
	'If RawFile'
go


COMMENT ON TABLE "pears"."oledocument" IS 
	'Pointers to documents belonging to People & Companies.  Stored in the Blobstore (Class O)'
go


ALTER TABLE "pears"."oledocument"
    ADD FOREIGN KEY "DocumentType" ("DocumentTypeID" ASC)
    REFERENCES "pears"."DocumentType" ("DocumentTypeID")
    ON DELETE SET NULL
go


CREATE INDEX "oledoc_owner" ON "pears"."oledocument"
    ( "ownertype","ownerid" )
go


CREATE INDEX "oledocumentOwneridDocumentTypeID" ON "pears"."oledocument"
    ( "ownertype","ownerid","DocumentTypeID" )
go


create trigger "oledocumentauditinsdel" before insert,delete order 1 on
"pears"."oledocument"
referencing old as "old_d" new as "new_d"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Document' and "AuditFlag" = 1))
begin
  declare "OwnerType" char(20);
  declare "OwnerName" char(100);
  if inserting and exists(select * from "AuditItems" where "AreaName" = 'Document' and "AuditFlag" = 1 and "ItemName" = 'Add') then
    select case "new_d"."ownertype" when 'P' then 'Person' when 'C' then 'Company' when 'L' then 'Placement' when 'V' then 'Vacancy' end into "OwnerType";
    select case "new_d"."ownertype"
      when 'P' then(select "name" from "person" where "personid" = "new_d"."ownerid")
      when 'C' then(select "name" from "company" where "companyid" = "new_d"."ownerid")
      when 'L' then(select "refcode" from "placement" where "placementid" = "new_d"."ownerid")
      when 'V' then(select "string"("Name",' - ',"v"."position",'(',"v"."RefCode",')') from "Company" key join "Employment" key join "vacancy" as "v" where "vacancyID" = "new_d"."ownerid") end
      into "OwnerName";
    call "AuditLog"('DOCUMENT',"new_d"."ownerid","string"("OwnerType",' ',"OwnerName",' Document Inserted.'),'',"new_d"."description")
  end if;
  if deleting and exists(select * from "AuditItems" where "AreaName" = 'Document' and "AuditFlag" = 1 and "ItemName" = 'Remove') then
    select case "old_d"."ownertype" when 'P' then 'Person' when 'C' then 'Company' when 'L' then 'Placement' when 'V' then 'Vacancy' end into "OwnerType";
    select case "old_d"."ownertype"
      when 'P' then(select "name" from "person" where "personid" = "old_d"."ownerid")
      when 'C' then(select "name" from "company" where "companyid" = "old_d"."ownerid")
      when 'L' then(select "refcode" from "placement" where "placementid" = "old_d"."ownerid")
      when 'V' then(select "string"("Name",' - ',"v"."position",'(',"v"."RefCode",')') from "Company" key join "Employment" key join "vacancy" as "v" where "vacancyID" = "old_d"."ownerid") end
      into "OwnerName";
    call "AuditLog"('DOCUMENT',"old_d"."ownerid","string"("OwnerType",' ',"OwnerName",' Document Deleted. '),"old_d"."description",'')
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."oledocument"."oledocumentauditinsdel" IS 
{create trigger oledocumentauditinsdel 
 before insert,delete order 1 on
pears.oledocument
referencing old as old_d new as new_d
for each row
when(exists(select * from AuditItems where AreaName = 'Document' and AuditFlag = 1))
begin
  declare OwnerType char(20);
  declare OwnerName char(100);
  if inserting and exists(select * from AuditItems where AreaName = 'Document' and AuditFlag = 1 and ItemName = 'Add') then
    select case new_d.ownertype when 'P' then 'Person' when 'C' then 'Company' when 'L' then 'Placement' when 'V' then 'Vacancy' end into OwnerType;
    select case new_d.ownertype 
      when 'P' then (select name from person where personid = new_d.ownerid) 
      when 'C' then (select name from company where companyid = new_d.ownerid) 
      when 'L' then (select refcode from placement where placementid = new_d.ownerid) 
      when 'V' then (select string(Name,' - ',v.position,'(',v.RefCode,')') from Company key join Employment key join vacancy v where vacancyID = new_d.ownerid) 
    end into OwnerName;
    call AuditLog('DOCUMENT',new_d.ownerid,string(OwnerType,' ',OwnerName,' Document Inserted.'),'',new_d.description)
  end if;
  if deleting  and exists(select * from AuditItems where AreaName = 'Document' and AuditFlag = 1 and ItemName = 'Remove') then
    select case old_d.ownertype when 'P' then 'Person' when 'C' then 'Company' when 'L' then 'Placement' when 'V' then 'Vacancy' end  into OwnerType;
    select case old_d.ownertype 
      when 'P' then (select name from person where personid = old_d.ownerid) 
      when 'C' then (select name from company where companyid = old_d.ownerid) 
      when 'L' then (select refcode from placement where placementid = old_d.ownerid) 
      when 'V' then (select string(Name,' - ',v.position,'(',v.RefCode,')') from Company key join Employment key join vacancy v where vacancyID = old_d.ownerid) 
    end into OwnerName;
    call AuditLog('DOCUMENT',old_d.ownerid,string(OwnerType,' ',OwnerName,' Document Deleted. '),old_d.description,'')
  end if
end
}
go


create trigger "oledocumentautoarchive" after insert order 2 on
"pears"."oledocument"
referencing new as "new_d"
for each row
begin
  declare "staffid" char(20);
  if "varexists"('userstaffid') <> 0 then
    set "staffid" = "userstaffid"
  else set "staffid" = null
  end if;
  if exists(select * from "documenttype" where "isnull"("iscompliance",0) = 1 and "documenttypeid" = "new_d"."documenttypeid") then
    if "new_d"."whenredacted" is not null then
      update "oledocument" set "whenarchived" = current timestamp,"whoarchived" = "staffid" where "whenredacted" is not null and "whenarchived" is null and "documenttypeid" = "new_d"."documenttypeid"
        and "oledocumentid" <> "new_d"."oledocumentid" and "ownerid" = "new_d"."ownerid" and "ownertype" = "new_d"."ownertype"
    end if;
    if "new_d"."whenredacted" is null then
      update "oledocument" set "whenarchived" = current timestamp,"whoarchived" = "staffid" where "whenredacted" is null and "whenarchived" is null and "documenttypeid" = "new_d"."documenttypeid"
        and "oledocumentid" <> "new_d"."oledocumentid" and "ownerid" = "new_d"."ownerid" and "ownertype" = "new_d"."ownertype"
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."oledocument"."oledocumentautoarchive" IS 
{create trigger oledocumentautoarchive 
 after insert order 2 on
pears.oledocument
referencing new as new_d
for each row
begin
  declare staffid char(20);
  if varexists('userstaffid') <> 0 then
    set staffid=userstaffid
  else set staffid=null
  end if;
  if exists(select * from documenttype where isnull(iscompliance,0) = 1 and documenttypeid = new_d.documenttypeid) then
    if new_d.whenredacted is not null then
    update oledocument set whenarchived = current timestamp,whoarchived = staffid where whenredacted is not null and whenarchived is null and documenttypeid = new_d.documenttypeid
      and oledocumentid <> new_d.oledocumentid and ownerid = new_d.ownerid and ownertype=new_d.ownertype
    end if;
    if new_d.whenredacted is null then
      update oledocument set whenarchived = current timestamp,whoarchived = staffid where whenredacted is null and whenarchived is null and documenttypeid = new_d.documenttypeid
        and oledocumentid <> new_d.oledocumentid and ownerid = new_d.ownerid and ownertype=new_d.ownertype
    end if
  end if
end
}
go


create trigger "oledocument_updateaudit" after update of "deletiondate"
order 3 on "pears"."oledocument"
referencing old as "old_d" new as "new_d"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Document' and "AuditFlag" = 1))
begin
  declare "OwnerType" char(20);
  declare "OwnerName" char(100);
  if exists(select * from "AuditItems" where "AreaName" = 'Document' and "AuditFlag" = 1 and "ItemName" = 'Deletion Date') then
    select case "old_d"."ownertype" when 'P' then 'Person' when 'C' then 'Company' when 'L' then 'Placement' when 'V' then 'Vacancy' end into "OwnerType";
    select case "old_d"."ownertype"
      when 'P' then(select "name" from "person" where "personid" = "old_d"."ownerid")
      when 'C' then(select "name" from "company" where "companyid" = "old_d"."ownerid")
      when 'L' then(select "refcode" from "placement" where "placementid" = "old_d"."ownerid")
      when 'V' then(select "string"("Name",' - ',"v"."position",'(',"v"."RefCode",')') from "Company" key join "Employment" key join "vacancy" as "v" where "vacancyID" = "old_d"."ownerid") end
      into "OwnerName";
    call "AuditLog"('DOCUMENT',"old_d"."ownerid","string"("OwnerType",' ',"OwnerName",' - ',"old_d"."description",' Deletion Date Changed. '),"old_d"."deletiondate","new_d"."deletiondate")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."oledocument"."oledocument_updateaudit" IS 
{create trigger oledocument_updateaudit 
 after update of deletiondate
order 3 on pears.oledocument
referencing old as old_d new as new_d
for each row
when(exists(select * from AuditItems where AreaName = 'Document' and AuditFlag = 1))
begin
  declare OwnerType char(20);
  declare OwnerName char(100);
  if exists(select * from AuditItems where AreaName = 'Document' and AuditFlag = 1 and ItemName = 'Deletion Date') then
  select case old_d.ownertype when 'P' then 'Person' when 'C' then 'Company' when 'L' then 'Placement' when 'V' then 'Vacancy' end  into OwnerType;
  select case old_d.ownertype 
      when 'P' then (select name from person where personid = old_d.ownerid) 
      when 'C' then (select name from company where companyid = old_d.ownerid) 
      when 'L' then (select refcode from placement where placementid = old_d.ownerid) 
      when 'V' then (select string(Name,' - ',v.position,'(',v.RefCode,')') from Company key join Employment key join vacancy v where vacancyID = old_d.ownerid) 
   end into OwnerName;
  call AuditLog('DOCUMENT',old_d.ownerid,string(OwnerType,' ',OwnerName,' - ',old_d.description, ' Deletion Date Changed. '),old_d.deletiondate,new_d.deletiondate) 
  end if
end
}
go
</code>