DELIMITER $$
--
-- Procedures
--
CREATE PROCEDURE SP_ProcessTimesheetRate(p_FromDate date, p_ToDate date)
BEGIN
  DECLARE v_timeSheetId int;
  DECLARE v_finished int;
  DECLARE v_userID int;
  DECLARE v_date date;
  DECLARE timesheet_cursor CURSOR FOR select timeSheetId, userID, date from tbltimesheet where date >= p_FromDate and date <= p_ToDate;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
  OPEN timesheet_cursor;
  get_timesheet: LOOP
  FETCH timesheet_cursor INTO v_timeSheetId, v_userID, v_date;
  IF v_finished = 1 THEN
    LEAVE get_timesheet;
  END IF;
  Call SP_UpdateTimeSheetRate(v_timeSheetId, v_userID, v_date);
  END LOOP get_timesheet;
  CLOSE timesheet_cursor;
END

CREATE DEFINER=root@localhost PROCEDURE SP_UpdateTimeSheetRate(p_timeSheetId int, p_userID int, p_date date)
BEGIN
  DECLARE v_finished int;
  DECLARE v_fromDate date;
  DECLARE v_toDate date;
  DECLARE v_rate int;
  DECLARE userRate_cursor CURSOR FOR select fromDate, toDate, rate from tblusers_rate where userID=p_userID and fromDate <= p_date ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
  OPEN userRate_cursor;
  get_userRate: LOOP
  FETCH userRate_cursor INTO v_fromDate, v_toDate, v_rate;
  IF v_finished = 1 THEN
    LEAVE get_userRate;
  END IF;
  if ( v_toDate >= p_date or v_toDate is null or year(v_toDate) = 0 ) then
    Update tbltimesheet set employeeRate = v_rate where timeSheetID = p_timeSheetId;
  end if;
  END LOOP get_userRate;
  CLOSE userRate_cursor;
END$$

--
-- Functions
--
CREATE DEFINER=root@localhost FUNCTION FN_CurrentEmployeeRate(p_userID int) RETURNS int(11)
BEGIN
  DECLARE v_rate int;
  SET v_rate = 0;
  set v_rate = (Select rate from tblusers_rate where userID=p_userID and fromDate =
  (select max(fromDate) fromDate from tblusers_rate where userID = p_userID));
  return v_rate;
END$$

CREATE DEFINER=root@localhost FUNCTION FN_Difference_DateRange(v_ReportFrom date, v_ReportTo Date, v_DataFrom date, v_DataTo date) RETURNS int(11)
BEGIN
  declare v_days int;
  declare v_ReportToCorrected date;
  set v_days = 0;
  set v_ReportToCorrected = last_day(v_ReportTo);
  if v_DataFrom < v_ReportFrom then
    if v_DataTo >= v_ReportToCorrected then
      set v_days = datediff(v_ReportToCorrected, v_ReportFrom)+1;
    else
      if v_DataTo >= v_ReportFrom then
        set v_days = datediff(v_DataTo, v_ReportFrom)+1;
      else
        set v_days = 0;
      end if;
    end if;
  else
    if v_DataTo >= v_ReportToCorrected then
      if v_ReportToCorrected >= v_DataFrom then
        set v_days = datediff(v_ReportToCorrected, v_DataFrom)+1;
      else
        set v_days =0;
      end if;
    else
      set v_days = datediff(v_DataTo, v_DataFrom)+1;
    end if;
  end if;
  return v_days;
END$$

CREATE DEFINER=root@localhost FUNCTION FN_EmployeeWorkedAmountPeriod(v_ReportFrom date, v_ReportTo Date, v_UserId int) RETURNS int(11)
BEGIN
  declare v_amount int;
  SET v_amount = 0;
  select sum(FN_Difference_DateRange(v_ReportFrom,v_ReportTo, fromDate,
      if ( year(ifnull(toDate,date('0000-00-00'))) = 0, date('2100-01-01'),toDate))*rate) into v_amount from tblusers_rate where userId = v_UserId;
  return v_amount;
END$$

CREATE DEFINER=root@localhost FUNCTION FN_EmployeeWorkedPeriod(v_ReportFrom date, v_ReportTo Date, v_UserId int) RETURNS int(11)
BEGIN
  declare v_days int;
  SET v_days = 0;
  select sum(FN_Difference_DateRange(v_ReportFrom,v_ReportTo, fromDate,
      if ( year(ifnull(toDate,date('0000-00-00'))) = 0, date('2100-01-01'),toDate))) into v_days from tblusers_rate where userId = v_UserId;
  return v_days;
END$$

DELIMITER ;