pears.InsertDiary

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."InsertDiary" IS 
{CREATE FUNCTION 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
}