====== pears.InsertDiary ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create function "pears"."InsertDiary"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @StaffID char(20),in @DiaryFrom timestamp,in @DurationType char(1),in @Duration double,
  in @PopupMinutes smallint default null,in @WhoEntered char(20),
  in @PersonID char(20) default null,
  in @VacancyID char(20) default null,
  in @EmploymentID char(20) default null,
  in @ProgressID char(20) default null,
  in @PlacementID char(20) default null,in @Description char(100),
  in @Notes varchar(8000) default null ) 
returns char(250)
begin
  -- for immediate pop up specify @PopupMinutes=0, for no pop up leave as null
  declare @NewDiaryID char(20);
  declare @DiaryTo timestamp;
  -- Check missing ID''s
  if @Employmentid is null and(@VacancyID is not null or @Progressid is not null or @Placementid is not null) then
    return('2:If a diary entry is to be linked to a vacancy, Progress item or Placement, then the Progress or Placement and Vacancy and EmploymentIDs must be specified.')
  end if;
  if(@Progressid is not null and @VacancyID is null) then
    set @VacancyID = (select "VacancyID" from "Progress" where "Progress"."ProgressID" = @ProgressID)
  end if;
  if(@Placementid is not null and @VacancyID is null) then
    set @VacancyID = (select "VacancyID" from "Placement" where "Placement"."Placementid" = @Placementid)
  end if;
  -- Check Duration and Popup not negative
  if @Duration < 0 or @PopupMinutes < 0 then return('4:Neither @Duration nor @PopupMinutes can be negative')
  end if;
  -- Check Duration Type & calculate @DiaryTo
  case @DurationType when 'R' then
    set @DiaryTo = @DiaryFrom when 'M' then
    set @DiaryTo = "dateadd"("minute",@Duration,@DiaryFrom) when 'H' then
    set @DiaryTo = "dateadd"("hour",@Duration,@DiaryFrom) when 'D' then
    set @DiaryTo = "dateadd"("day",@Duration,@DiaryFrom) when 'W' then
    set @DiaryTo = "dateadd"("week",@Duration,@DiaryFrom) else
    return('3:Duration Type must be one of R(eminder), M(inutes), H(ours), D(ays), W(eeks)')
  end case;
  -- Create new ID
  set @NewDiaryID = "uniquekey"(1);
  -- Insert record
  insert into "Diary"( "diaryid","staffid","personid","vacancyid","employmentid","progressid","placementid","diaryfrom","diaryto","description","notes","DurationType","Duration","PopupMinutes","WhoEntered" ) values
    ( @NewDiaryID,@StaffID,@PersonID,@VacancyID,@EmploymentID,@ProgressID,@PlacementID,@DiaryFrom,@DiaryTo,@Description,@Notes,@DurationType,@Duration,@PopupMinutes,@WhoEntered ) ;
  if @@rowcount <> 1 then return('1:Error inserting diary record')
  end if;
  return("string"('0:Diary record inserted~',@NewDiaryID))
exception
  when others then return('1:Error inserting diary record')
end
</code>