pears.InsertDiary

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