CREATE TABLE student_reverted_special_exam_mark_details(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
studentaccount_id INT(8) DEFAULT NULL,
special_exam_id INT(8) DEFAULT NULL,
regularExamId INT(8) DEFAULT NULL,
supplyExamId INT(8) DEFAULT NULL,
createdBy INT(8) NOT NULL,
updatedBy INT(8) NOT NULL,
createdDate datetime NOT NULL,
updatedDate datetime NOT NULL
) ENGINE=INNODB;

ALTER TABLE `pseudosubjects` 
ADD COLUMN `isFinalized` INT NULL DEFAULT 0 AFTER `hdl_deptID`;


CREATE TABLE `sms_configure` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sms_to_send` VARCHAR(160) NULL,
  `examType` VARCHAR(45) NULL,
  `published` INT(1) NULL,
  `created` INT(1) NULL,
  PRIMARY KEY (`id`));
  
  
  
ALTER TABLE `sms_configure` 
ADD COLUMN `examregID` VARCHAR(45) NULL AFTER `created`;

ALTER TABLE `sms_configure` 
ADD COLUMN `phoneType` VARCHAR(45) NULL AFTER `examregID`;

ALTER TABLE student_tc_details ADD COLUMN studentPassedStatus INT DEFAULT NULL;

ALTER TABLE student_tc_details ADD COLUMN studentApplicationTcDate date DEFAULT NULL;

DROP PROCEDURE student_tc_details_add;    

DELIMITER ||

CREATE PROCEDURE `student_tc_details_add`(IN p_studentaccountID INT, IN p_campus_type_ID INT, IN p_admisson_courseID INT, IN p_admission_deptID INT, IN p_admission_semID INT, IN p_leavingDate DATE, IN p_leaving_courseID INT, IN p_leaving_depID INT, IN p_leaving_semID INT, IN p_studied_patterndeptID INT, IN p_studied_subsID INT, IN p_leaving_reason TEXT, IN p_lst_exm_status TEXT, IN p_lst_exm_courseID INT, IN p_lst_deptID INT, IN p_lst_semID INT, IN p_lst_month TEXT, IN p_lst_year year, IN p_hasAppeared INT, IN p_has_cleared_all_dues INT, IN p_remarks TEXT, IN p_tc_issue_date DATE,IN p_is_alumini INT, IN joinDate DATE, IN status TEXT, IN tc_appln_date DATE, OUT pre INT)
BEGIN

  DECLARE prefix_ID INT;
  DECLARE min_ID INT;
  DECLARE tcNo INT;

  SELECT id INTO prefix_ID FROM tc_prefix WHERE campus_typeID = p_campus_type_ID AND p_tc_issue_date BETWEEN fromDate AND toDate;

  SELECT id, MIN(tc_No) INTO min_ID, tcNo FROM student_tc_details WHERE tc_prefixID = prefix_ID AND is_deleted = 1;

  IF min_ID IS NOT NULL THEN
    
    UPDATE student_tc_details SET studentaccountID = p_studentaccountID, admisson_courseID = p_admisson_courseID, admission_deptID = p_admission_deptID, admission_semID = p_admission_semID, leavingDate = p_leavingDate, leaving_courseID = p_leaving_courseID, leaving_depID = p_leaving_depID, leaving_semID = p_leaving_semID, studied_patterndeptID = p_studied_patterndeptID, studied_subsID = p_studied_subsID, leaving_reason = p_leaving_reason, lst_exm_status = p_lst_exm_status,lst_exm_courseID = p_lst_exm_courseID, lst_exm_deptID = p_lst_deptID, lst_exm_semID = p_lst_semID, lst_exm_month = p_lst_month, lst_exm_year = p_lst_year, has_appeared = p_hasAppeared, has_cleared_all_dues = p_has_cleared_all_dues, tc_issue_date = p_tc_issue_date, remarks = p_remarks, is_deleted = 0, is_alumini = p_is_alumini, studentJoindate = joinDate, studentPassedStatus = status, studentApplicationTcDate = tc_appln_date WHERE id = min_ID;

  ELSE

    SELECT MAX(tc_No)+1 INTO tcNo FROM student_tc_details WHERE tc_prefixID = prefix_ID AND is_deleted = 0;
    IF tcNo IS NULL THEN

      SET tcNo=1;

    END IF;
    INSERT INTO student_tc_details (studentaccountID, tc_No, tc_prefixID, admisson_courseID, admission_deptID, admission_semID, leavingDate, leaving_courseID, leaving_depID, leaving_semID, studied_patterndeptID, studied_subsID, leaving_reason, lst_exm_status, lst_exm_courseID, lst_exm_deptID, lst_exm_semID, lst_exm_month, lst_exm_year, has_appeared, has_cleared_all_dues, tc_issue_date, remarks, is_alumini, studentJoindate,studentPassedStatus,studentApplicationTcDate) VALUES (p_studentaccountID, tcNo, prefix_ID, p_admisson_courseID, p_admission_deptID, p_admission_semID, p_leavingDate, p_leaving_courseID, p_leaving_depID, p_leaving_semID, p_studied_patterndeptID, p_studied_subsID, p_leaving_reason, p_lst_exm_status, p_lst_exm_courseID, p_lst_deptID, p_lst_semID, p_lst_month, p_lst_year, p_hasAppeared, p_has_cleared_all_dues, p_tc_issue_date, p_remarks, p_is_alumini, joinDate,status,tc_appln_date);

  END IF;

  SELECT tcNo INTO pre;

END;

||
DELIMITER ;

CREATE TABLE student_tc_templates(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
templateName varchar(50) DEFAULT NULL,
templateUrl varchar(50) DEFAULT NULL,
templatePrintUrl varchar(50) DEFAULT NULL,
isActiveTemplate INT DEFAULT NULL
) ENGINE=INNODB;


/** Queries to be executed **/

/** in db_dgmmes **/

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template2','studentTcTemplate2','studentTcTemplatePrint2',1);

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template1','studentTcTemplate1','studentTcTemplatePrint1',0);


/** in db_stthomas ***/


insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template2','studentTcTemplate2','studentTcTemplatePrint2',0);

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template1','studentTcTemplate1','studentTcTemplatePrint1',1);

/** in db_stjosephs **/

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template2','studentTcTemplate2','studentTcTemplatePrint2',0);

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template1','studentTcTemplate1','studentTcTemplatePrint1',1);

/** db_vimala ***/

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template2','studentTcTemplate2','studentTcTemplatePrint2',0);

insert into student_tc_templates (templateName,templateUrl,templatePrintUrl,isActiveTemplate) values ('Template1','studentTcTemplate1','studentTcTemplatePrint1',1);


