/*********SUJESH T PROCEDURE FOR DATE RANGE UPDATE*************************/



DROP procedure IF EXISTS `sp_daterangeUpdate`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_daterangeUpdate`(IN batch_ID INT,IN sem_ID INT,IN dateRangeStr text,startDate text,endDate text)
BEGIN


 DECLARE dayID INT DEFAULT 0;
  DECLARE _next TEXT DEFAULT NULL;
  DECLARE _nextlen INT DEFAULT NULL;
  DECLARE _value TEXT DEFAULT NULL;    
 DECLARE currentDateRangeStr TEXT DEFAULT NULL;
  
   DROP TEMPORARY TABLE IF EXISTS normal_day_order;
  
  CREATE TEMPORARY TABLE normal_day_order (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
    , udayOrder INT(12) NOT NULL 
    );
  INSERT INTO normal_day_order(udayOrder) VALUES(1),(2),(3),(4),(5),(6),(7);  
 
  DELETE FROM batch_timetable_date where batchId = batch_ID and semId = sem_ID and date between startDate and endDate;
 SET currentDateRangeStr = dateRangeStr;
 date_range_loop:LOOP
 IF LENGTH(TRIM(currentDateRangeStr)) = 0 OR currentDateRangeStr IS NULL THEN
 LEAVE date_range_loop;
 END IF;
 SET _next = SUBSTRING_INDEX(currentDateRangeStr,',',1);
 SET _nextlen = LENGTH(_next);
 SET _value = TRIM(_next);
        
SELECT udayOrder into dayID from normal_day_order where udayOrder = weekday(_next) +1;
 IF (dayID IS NULL) THEN
 select "no match";
 END IF;
        
INSERT INTO batch_timetable_date(batchId,subjectId,dayId,hourId,semId,sbsId,is_allowed,stratTime,endTime,date) select bt.batchID,bt.subjectID,bt.dayID,bt.hourID,bt.semID,bt.sbsID,bt.is_allowed,bt.stratTime,bt.endTime,_next from batch_timetable bt where bt.dayID=weekday(_next)+1 and bt.batchID = batch_ID and bt.semID = sem_ID;
        
SET currentDateRangeStr = INSERT(currentDateRangeStr,1,_nextlen + 1,'');    

END LOOP date_range_loop;
        
 
END$$

DELIMITER ;


  



/**********************SUJESH T PROCEDURE FOR SWAP TIMETABLE*******************************/




DROP procedure IF EXISTS `sp_swaptimetable`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_swaptimetable`(IN courseType_Id INT,IN courseyear text,IN swapDayStr text)
BEGIN
  DECLARE _next TEXT DEFAULT NULL;
  DECLARE _nextlen INT DEFAULT NULL;
  DECLARE _value TEXT DEFAULT NULL;        
 DECLARE batch_ID INT;
  DECLARE sem_ID INT;
  DECLARE i INT DEFAULT 1;
  DECLARE currentSwapDayStr VARCHAR(255);
  
 DECLARE done INT DEFAULT FALSE;
  
 
  DECLARE cursor_i CURSOR FOR SELECT b.batchID, b.semID FROM batches b WHERE b.courseTypeID = courseType_Id AND b.batchStartYear = courseyear;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  DROP TEMPORARY TABLE IF EXISTS userswaptable;
  CREATE TEMPORARY TABLE userswaptable (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
    , udayOrder INT(12) NOT NULL
    ,  INDEX `index2` (`id` ASC)
    );
  
    CREATE TEMPORARY TABLE currentorder (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
    , timetableID INT(12) NOT NULL
    ,currentDayID INT(12) NOT NULL
    ,batchID INT(12) NOT NULL
    ,semID INT(12) NOT NULL
   , INDEX `index2` (`timetableID` ASC, `currentDayID` ASC)
    );  
   
 SET currentSwapDayStr = swapDayStr;
  iterator:LOOP
  IF LENGTH(TRIM(currentSwapDayStr)) = 0 OR currentSwapDayStr IS NULL THEN
  LEAVE iterator;
  END IF;
  SET _next = SUBSTRING_INDEX(currentSwapDayStr,',',1);
  SET _nextlen = LENGTH(_next);
  SET _value = TRIM(_next);
  
 INSERT INTO userswaptable(udayOrder) VALUES(_next);
  SET currentSwapDayStr = INSERT(currentSwapDayStr,1,_nextlen + 1,'');
  END LOOP iterator;
  
 
OPEN cursor_i;
read_loop: LOOP
    FETCH cursor_i INTO batch_ID, sem_ID;
    IF done THEN
      LEAVE read_loop;
    END IF;
    

 INSERT INTO currentorder (timetableID,currentDayID,batchID,semID) select timetableID,dayID,batchID,semID from batch_timetable btc where btc.batchId=batch_ID and btc.semId=sem_ID;
  
  update batch_timetable bt inner join  currentorder co on bt.dayId = co.currentDayID and bt.timetableId = co.timetableID inner join userswaptable ust on ust.id = co.currentDayID set  bt.dayID=ust.udayOrder
where bt.batchId = batch_ID and bt.semId = sem_ID;




END LOOP read_loop;
  
CLOSE cursor_i;
  
 
 SET currentSwapDayStr = swapDayStr;
  iterator:LOOP
  IF LENGTH(TRIM(currentSwapDayStr)) = 0 OR currentSwapDayStr IS NULL THEN
  LEAVE iterator;
  END IF;
  SET _next = SUBSTRING_INDEX(currentSwapDayStr,',',1);
  SET _nextlen = LENGTH(_next);
  SET _value = TRIM(_next);
  
 UPDATE batch_timetable_day_order SET dayOrder=_next,courseTypeId = courseType_Id,batchStartYear = courseyear where dayId=i and batchStartYear = courseyear and courseTypeId = courseType_Id;
  SET i= i + 1;
  
 SET currentSwapDayStr = INSERT(currentSwapDayStr,1,_nextlen + 1,'');
  END LOOP iterator;

 
 DROP TEMPORARY TABLE userswaptable;
  DROP TEMPORARY TABLE currentorder;    

END$$

DELIMITER ;

