/*** Ranjith Balachandran 30-11-2017 Onwards ***/

ALTER TABLE staff_forgotPassword drop column createdDate;
  
ALTER TABLE staff_forgotPassword drop column updatedDate;

ALTER TABLE staff_forgotPassword ADD COLUMN createdDate DATETIME NOT NULL;
  
ALTER TABLE staff_forgotPassword ADD COLUMN updatedDate DATETIME NOT NULL;


/***********kishan dec 1,2017 *****************/

ALTER TABLE `normalization_rule` 
ADD COLUMN `attnPercentFromAcadFlag` INT(11) NULL DEFAULT 0 AFTER `isapproved`;



/**************kishan december 4, 2017 ***********/

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;
 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 ;




/**************************kishan december 7,2017 *************************/
ALTER TABLE `student_mark_before_moderation` 
ADD COLUMN `moderation_rule_id` INT(11) NULL AFTER `date_and_time`;



/*** Author Ranjith Balachandran 06-12-2017 Onwards ****************/

ALTER TABLE exam_reg_studentsubject ADD unique index student_combo (studentID,examregID,subjectID);

ALTER table exam_reg_studentchallan ADD unique index student_combo (studentID,examregID);

ALTER TABLE exam_supplementary_student_subjects ADD unique index student_combo (studentID,exam_supplementary_id,examID);


ALTER TABLE exam_supplementary_student_details ADD unique index student_combo (studentID,exam_supplementary_id);






	
ALTER TABLE `failed_students` ADD COLUMN `failedDate` DATETIME NOT NULL;

DROP procedure IF EXISTS `move_failedstudents`;

DELIMITER ||

CREATE DEFINER=`root`@`localhost` PROCEDURE `move_failedstudents`(IN p_studentID INT,IN p_failedInSemester INT,IN p_hisSemestersInThisbatch varchar(100), IN p_isFailNow INT, IN failed_Date TEXT)
BEGIN
    DECLARE p_batchID INT;
    
    DECLARE done INT DEFAULT 0;

declare p_row  CURSOR for select  batchID from studentaccount where studentID=p_studentID;
        OPEN p_row;
            FETCH p_row INTO p_batchID;
            insert into failed_students (studentID,previousBatch,failedInSemester,hisSemestersInThisbatch,isFailNow,failedDate) values (p_studentID,p_batchID,p_failedInSemester,p_hisSemestersInThisbatch,p_isFailNow,failed_Date);
        CLOSE  p_row;
END;

||

DELIMITER ;
