DROP FUNCTION IF EXISTS get_creditable_service_period;$$

CREATE FUNCTION  get_creditable_service_period
  (
    id INT
  )
  RETURNS FLOAT(4,1)
DETERMINISTIC
READS SQL DATA
  BEGIN
    DECLARE creditableServicePeriod FLOAT;
    DECLARE startDate datetime;
    DECLARE endDate datetime;
    DECLARE currentWorkStartDate datetime;
    DECLARE lastJobToCurrentJobGap INT;
    DECLARE lastJobLeftDate DATETIME;
    DECLARE num1 INT;
    DECLARE num2 INT;
    DECLARE nonWorkedDays INT;
    SET creditableServicePeriod = 0;
    SET lastJobToCurrentJobGap = 0;
    SET num1 = 0;
    SET num2 = 1;
    SET nonWorkedDays = 0;

    IF id IS NULL
    THEN
      RETURN NULL;
    END IF;

    SELECT MIN(eexp_from_date) INTO @minStartDate
    FROM hs_hr_emp_work_experience WHERE emp_number = id AND eexp_creditable = 1 AND eexp_to_date IS NOT NULL ORDER BY eexp_from_date;

    SELECT MAX(eexp_to_date) INTO @maxEndDate
    FROM hs_hr_emp_work_experience WHERE emp_number = id AND eexp_creditable = 1 AND eexp_to_date IS NOT NULL ORDER BY eexp_to_date;

    SELECT employee.joined_date INTO currentWorkStartDate
    FROM hs_hr_employee AS employee
    WHERE employee.emp_number =id;

    IF currentWorkStartDate IS NULL THEN
      SELECT @minStartDate INTO currentWorkStartDate;
    END IF;

    SELECT COUNT(eexp_from_date) INTO @workExperienceCount
    FROM hs_hr_emp_work_experience WHERE emp_number = id AND eexp_creditable = 1 AND eexp_to_date IS NOT NULL ORDER BY eexp_from_date;


    IF (@workExperienceCount > 0) THEN
      SELECT workExperience.eexp_to_date INTO lastJobLeftDate FROM hs_hr_emp_work_experience AS workExperience WHERE emp_number = id  AND eexp_creditable = 1 AND eexp_to_date IS NOT NULL ORDER BY eexp_to_date LIMIT num1,1;

      creditableServiceLoop : WHILE num1 < @workExperienceCount DO

        SELECT workExperience.eexp_to_date INTO endDate FROM hs_hr_emp_work_experience AS workExperience WHERE emp_number = id  AND eexp_creditable = 1 AND eexp_to_date IS NOT NULL ORDER BY eexp_to_date LIMIT num1,1;

        IF (endDate > lastJobLeftDate) THEN
          SELECT endDate INTO lastJobLeftDate;
        END IF;

        SELECT workExperience.eexp_from_date INTO startDate FROM hs_hr_emp_work_experience  AS workExperience WHERE emp_number = id AND eexp_creditable = 1 ORDER BY eexp_from_date LIMIT num2,1;

        IF (startDate > currentWorkStartDate) THEN
          LEAVE creditableServiceLoop;
        END IF ;

        IF (startDate > lastJobLeftDate) THEN
          SELECT DATEDIFF(startDate, lastJobLeftDate) INTO @temp;
          SET nonWorkedDays = nonWorkedDays + @temp;
        END IF ;

        SET num1 = num1 + 1;
        SET num2 = num2 + 1;

      END WHILE;

      #If there is a gap between last job's end date and current job's start date store that gap to lastJobToCurrentJobGap
      IF (lastJobLeftDate < currentWorkStartDate+1) THEN
        SELECT DATEDIFF(currentWorkStartDate, lastJobLeftDate) INTO lastJobToCurrentJobGap;
      END IF;

      SELECT LEAST(@minStartDate, currentWorkStartDate) INTO @experienceStartDate;

      #Dates between the start date of first job and now
      SELECT DATEDIFF(NOW(), @experienceStartDate) INTO @totalWorkedDays;

      #Deduct total non-worked days and gap between last job end date to current job start date to get total worked days
      SET @totalWorkedDays = @totalWorkedDays - nonWorkedDays - lastJobToCurrentJobGap;

      SELECT (@totalWorkedDays/30) INTO creditableServicePeriod;
      RETURN creditableServicePeriod;
    END IF;

    SELECT DATEDIFF(NOW(), currentWorkStartDate) INTO @currentJobPeriod;
    SELECT (@currentJobPeriod/30) INTO creditableServicePeriod;
    RETURN creditableServicePeriod;

  END;$$