====== pears.progress ======
<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 =====
Progress state of candidates under consideration / placed / rejected in vacancies. Links candidates to vacancies.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **progressid** | char(20) | NOT NULL |  |  |
| vacancyid | char(20) | NOT NULL |  |  |
| status | char(4) | NOT NULL |  |  |
| note | long varchar | NULL |  |  |
| actiondate | date | NULL |  |  |
| rejectionreason | char(100) | NULL |  |  |
| staffid | char(20) | NOT NULL |  |  |
| personid | char(20) | NOT NULL |  |  |
| placementid | char(20) | NULL |  |  |
| InterviewDate | date | NULL |  |  |
| InterviewTime | time | NULL |  |  |
| actiontime | time | NULL |  |  |
| priority | smallint | NULL | 5 | 1 - high, 5 - low |
| noreemploy | smallint | NULL |  |  |
| score | smallint | NULL |  |  |
| defunct | tinyint | NULL |  |  |

===== Primary Key =====
  * progressid

===== 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)]] | NOT NULL; ON DELETE CASCADE |
| placement | placementid | [[database:tables:pears_placement|pears.Placement (placementid)]] | ON DELETE SET NULL |
| vacancy | vacancyid | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] | NOT NULL;  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_contactevent|pears.contactevent]] | progress | progressid | progressid |
| [[database:tables:pears_diary|pears.diary]] | progress | progressid | progressid |
| [[database:tables:pears_progresshistory|pears.ProgressHistory]] | Progress | ProgressID | progressid |
| [[database:tables:pears_sentcvs|pears.SentCVs]] | progress | progressid | progressid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| progress_actiondate | Index | actiondate |  |
| progressstatus | Index | status |  |
| progress_notestext | Text index | note | CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| ProgressInsert | after | insert order 1 |
| ProgressUpdate | after | update of "status", "InterviewDate","InterviewTime", "Note" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."progress"
-- Table comment: Progress state of candidates under consideration / placed / rejected in vacancies. Links candidates to vacancies.
-- Statement count: 14

CREATE TABLE "pears"."progress" (
    "progressid"                     char(20) NOT NULL
   ,"vacancyid"                      char(20) NOT NULL
   ,"status"                         char(4) NOT NULL
   ,"note"                           long varchar NULL
   ,"actiondate"                     date NULL
   ,"rejectionreason"                char(100) NULL
   ,"staffid"                        char(20) NOT NULL
   ,"personid"                       char(20) NOT NULL
   ,"placementid"                    char(20) NULL
   ,"InterviewDate"                  date NULL
   ,"InterviewTime"                  time NULL
   ,"actiontime"                     time NULL
   ,"priority"                       smallint NULL DEFAULT 5
   ,"noreemploy"                     smallint NULL
   ,"score"                          smallint NULL
   ,"defunct"                        tinyint NULL
   ,PRIMARY KEY ("progressid" ASC) 
)
go


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


COMMENT ON TABLE "pears"."progress" IS 
	'Progress state of candidates under consideration / placed / rejected in vacancies. Links candidates to vacancies.'
go


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


ALTER TABLE "pears"."progress"
    ADD NOT NULL FOREIGN KEY "person" ("personid" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
go


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


ALTER TABLE "pears"."progress"
    ADD NOT NULL FOREIGN KEY "vacancy" ("vacancyid" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
go


CREATE INDEX "progress_actiondate" ON "pears"."progress"
    ( "actiondate" DESC )
go


CREATE INDEX "progressstatus" ON "pears"."progress"
    ( "status" )
go


CREATE TEXT INDEX "progress_notestext" ON "pears"."progress"
    ( "note" ) CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH
go


create trigger "ProgressInsert" after insert order 1 on
"pears"."progress"
referencing new as "new_prog"
for each row
begin
  declare @ContactEventClass char(2);
  declare @ProgressName char(40);
  insert into "progresshistory"( "progresshistoryid","progressid","status","historydate","historytime","staffid","InterviewDate","InterviewTime","Note" ) values
    ( "uniquekey"("new_prog"."progressid"),"new_prog"."progressid","new_prog"."status",current date,current time,"new_prog"."staffid","new_prog"."InterviewDate","new_prog"."InterviewTime","new_prog"."Note" ) ;
  select "ContactEventClass","Name" into @ContactEventClass,@ProgressName from "Status" where "Status"."Type" = 'R' and "new_prog"."Status" = "Status"."Status";
  if @ContactEventClass is not null then
    insert into "ContactEvent"( "ContactEventID","ClassCode","Personid","staffid","vacancyid","employmentid","progressid","contactdate","contacttime","description","who","notes","DivisionID" ) 
      select "uniquekey"('X'),
        @ContactEventClass,
        "new_prog"."PersonID",
        "new_prog"."StaffID",
        "new_prog"."VacancyID",
        (select "EmploymentID" from "Vacancy" where "Vacancy"."VacancyID" = "new_prog"."VacancyID"),
        "new_prog"."ProgressID",
        current date,
        current time,
        @ProgressName,'A','Automatically generated from Progress record',
        (select "DivisionID" from "Company" key join "Employment" key join "Vacancy" where "Vacancy"."VacancyID" = "new_prog"."VacancyID" and "Company"."CompanyID" = "Employment"."CompanyID")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."progress"."ProgressInsert" IS 
{create trigger ProgressInsert 
 after insert order 1 on
pears.progress
referencing new as new_prog
for each row
begin
  declare @ContactEventClass char(2);
  declare @ProgressName char(40);
  insert into progresshistory( progresshistoryid,progressid,status,historydate,historytime,staffid,InterviewDate,InterviewTime,Note) values( 
    uniquekey(new_prog.progressid),new_prog.progressid,new_prog.status,current date,current time,new_prog.staffid,new_prog.InterviewDate,new_prog.InterviewTime,new_prog.Note) ;
  select ContactEventClass,Name into @ContactEventClass,@ProgressName from Status where Status.Type = 'R' and new_prog.Status = Status.Status;
  if @ContactEventClass is not null then
    insert into ContactEvent( ContactEventID,ClassCode,Personid,staffid,vacancyid,employmentid,progressid,contactdate,contacttime,description,who,notes,DivisionID) 
      select uniquekey('X'),
        @ContactEventClass,
        new_prog.PersonID,
        new_prog.StaffID,
        new_prog.VacancyID,
        (select EmploymentID from Vacancy where Vacancy.VacancyID = new_prog.VacancyID),
        new_prog.ProgressID,
        current date,
        current time,
        @ProgressName,'A','Automatically generated from Progress record',
        (select DivisionID from Company key join Employment key join Vacancy where Vacancy.VacancyID = new_prog.VacancyID and Company.CompanyID = Employment.CompanyID)
  end if
end
}
go


create trigger "ProgressUpdate" after update of "status",
"InterviewDate","InterviewTime",
"Note" order 1 on "pears"."progress"
referencing new as "new_prog"
for each row
begin
  declare @ContactEventClass char(2);
  declare @ProgressName char(40);
  insert into "progresshistory"( "progresshistoryid","progressid","status","historydate","historytime","staffid","InterviewDate","InterviewTime","Note" ) values
    ( "uniquekey"("new_prog"."progressid"),"new_prog"."progressid","new_prog"."status",current date,current time,"new_prog"."staffid","new_prog"."InterviewDate","new_prog"."InterviewTime","new_prog"."Note" ) ;
  select "ContactEventClass","Name" into @ContactEventClass,@ProgressName from "Status" where "Status"."Type" = 'R' and "new_prog"."Status" = "Status"."Status";
  if @ContactEventClass is not null then
    insert into "ContactEvent"( "ContactEventID","ClassCode","Personid","staffid","vacancyid","employmentid","progressid","contactdate","contacttime","description","who","notes","DivisionID" ) 
      select "uniquekey"('X'),
        @ContactEventClass,
        "new_prog"."PersonID",
        "new_prog"."StaffID",
        "new_prog"."VacancyID",
        (select "EmploymentID" from "Vacancy" where "Vacancy"."VacancyID" = "new_prog"."VacancyID"),
        "new_prog"."ProgressID",
        current date,
        current time,
        @ProgressName,'A','Automatically generated from Progress record',
        (select "DivisionID" from "Company" key join "Employment" key join "Vacancy" where "Vacancy"."VacancyID" = "new_prog"."VacancyID" and "Company"."CompanyID" = "Employment"."CompanyID")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."progress"."ProgressUpdate" IS 
{create trigger ProgressUpdate 
 after update of status,
InterviewDate,InterviewTime,
Note order 1 on pears.progress
referencing new as new_prog
for each row
begin
  declare @ContactEventClass char(2);
  declare @ProgressName char(40);
  insert into progresshistory( progresshistoryid,progressid,status,historydate,historytime,staffid,InterviewDate,InterviewTime,Note) values( 
    uniquekey(new_prog.progressid),new_prog.progressid,new_prog.status,current date,current time,new_prog.staffid,new_prog.InterviewDate,new_prog.InterviewTime,new_prog.Note) ;
  select ContactEventClass,Name into @ContactEventClass,@ProgressName from Status where Status.Type = 'R' and new_prog.Status = Status.Status;
  if @ContactEventClass is not null then
    insert into ContactEvent( ContactEventID,ClassCode,Personid,staffid,vacancyid,employmentid,progressid,contactdate,contacttime,description,who,notes,DivisionID) 
      select uniquekey('X'),
        @ContactEventClass,
        new_prog.PersonID,
        new_prog.StaffID,
        new_prog.VacancyID,
        (select EmploymentID from Vacancy where Vacancy.VacancyID = new_prog.VacancyID),
        new_prog.ProgressID,
        current date,
        current time,
        @ProgressName,'A','Automatically generated from Progress record',
        (select DivisionID from Company key join Employment key join Vacancy where Vacancy.VacancyID = new_prog.VacancyID and Company.CompanyID = Employment.CompanyID)
  end if
end
}
go
</code>