/*Kishan july 9, 2018*/

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 ;

/*Aswin 10-07-2018*/

alter table question_paper_pool modify column question longtext not null;


/*Aswin 11-07-2018*/
create table batch_timetable_day_order_date (id int primary key not null auto_increment,dayId int not null,dayOrder int not null,courseTypeId int not null,batchStartYear varchar(45) not null,date date,constraint btdy unique (dayId,dayOrder,courseTypeId,batchStartYear,date));
/*Kishan july 11,2018*/

ALTER TABLE `studentaccount` 
ADD COLUMN `campusNumber` VARCHAR(50) NULL DEFAULT NULL AFTER `districtID`;


/*Aswin 12-07-2018*/

alter table batch_timetable_date add column dayOrderDate int default 0 not null;

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;
  DECLARE batch_StartYear int default 0;
 DECLARE course_TypeID int default 0;
 DECLARE day_Order int default 0;
  
   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;
 
 SELECT b.batchStartYear,b.courseTypeId INTO batch_StartYear,course_TypeID FROM batches b WHERE b.batchID=batch_ID AND b.semID=sem_ID;
 
 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;
 
 SET day_Order = 0;

SELECT btdo.dayOrder INTO day_Order FROM batch_timetable_day_order btdo WHERE btdo.batchStartYear = batch_StartYear AND btdo.courseTypeId=course_TypeID AND btdo.dayId = dayID ; 

IF (day_Order IS NULL AND dayID IS NOT NULL) THEN
SET day_Order = dayID;
END IF;
 
 
 
INSERT INTO batch_timetable_date(batchId,subjectId,dayId,hourId,semId,sbsId,is_allowed,stratTime,endTime,date,dayOrderDate) select bt.batchID,bt.subjectID,bt.dayID,bt.hourID,bt.semID,bt.sbsID,bt.is_allowed,bt.stratTime,bt.endTime,_next,day_Order 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 ;



DROP procedure IF EXISTS `sp_daterangeUpdateAllBatches`;
DELIMITER $$


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_daterangeUpdateAllBatches`(IN batch_start_year INT,IN course_typeId 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;
 DECLARE day_Order int default 0;
 DECLARE batch_ID int default 0;
 DECLARE sem_ID int default 0;
 DECLARE bDone int;
 DECLARE batchList CURSOR FOR SELECT b.batchID,b.semID FROM batches b WHERE b.batchStartYear=batch_start_year AND b.courseTypeId=course_typeId;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone=1;
   
   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);  
 
 OPEN batchList;
 SET bDone=0;
 
 REPEAT
	FETCH batchList INTO batch_ID,sem_ID;
 IF (bDone=0) THEN
 
  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;
 
 SET day_Order = 0;

SELECT btdo.dayOrder INTO day_Order FROM batch_timetable_day_order btdo WHERE btdo.batchStartYear = batch_start_year AND btdo.courseTypeId=course_typeId AND btdo.dayId = dayID ; 

IF (day_Order IS NULL AND dayID IS NOT NULL) THEN
SET day_Order = dayID;
END IF;
 
 
 
INSERT INTO batch_timetable_date(batchId,subjectId,dayId,hourId,semId,sbsId,is_allowed,stratTime,endTime,date,dayOrderDate) select bt.batchID,bt.subjectID,bt.dayID,bt.hourID,bt.semID,bt.sbsID,bt.is_allowed,bt.stratTime,bt.endTime,_next,day_Order 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 IF;
UNTIL bDone END REPEAT;

CLOSE batchList;



END$$

DELIMITER ;

