DELIMITER $$
CREATE PROCEDURE `sp_ins_student_admission_master`(IN `in_dml_mode` CHAR(1), IN `in_ay_id` CHAR(7), IN `in_inst_id` SMALLINT(5), IN `in_student_id` MEDIUMINT(8), IN `in_student_num` VARCHAR(50), IN `in_standard_id` CHAR(2), IN `in_admission_num` VARCHAR(50), IN `in_admission_ref` VARCHAR(25), IN `in_enrollment_num` VARCHAR(50), IN `in_enquiry_date` DATE,IN `in_enrollment_date` DATE, IN `in_enrolled_for_std` CHAR(2), IN `in_first_name` VARCHAR(20), IN `in_middle_name` VARCHAR(20), IN `in_last_name` VARCHAR(20), IN `in_birth_date` DATE, IN `in_gender` ENUM('M','F','O'), IN `in_blood_group` VARCHAR(10), IN `in_birth_place` VARCHAR(20), IN `in_nationality` VARCHAR(20), IN `in_language` VARCHAR(20), IN `in_religion` VARCHAR(20), IN `in_student_category` VARCHAR(20), IN `in_student_subcategory` VARCHAR(20), IN `in_consession_category` VARCHAR(20), IN `in_disability` VARCHAR(20), IN `in_address_line_1` VARCHAR(100), IN `in_address_line_2` VARCHAR(100), IN `in_locality` VARCHAR(25), IN `in_city` VARCHAR(25), IN `in_district` VARCHAR(20), IN `in_state` VARCHAR(20), IN `in_country` VARCHAR(25), IN `in_pincode` MEDIUMINT(8), IN `in_mobile_phone_main` VARCHAR(15), IN `in_email_main` VARCHAR(50), IN `in_identification_mark_1` VARCHAR(50), IN `in_aadhar_card_num` VARCHAR(12), IN `in_passport_num` VARCHAR(15), IN `in_prev_school_name` VARCHAR(50), IN `in_prev_school_address` VARCHAR(50), IN `in_prev_school_board` VARCHAR(20), IN `in_stud_status` VARCHAR(25), IN `in_status_desc` VARCHAR(100), IN `in_enrollment_num_gen_mode` VARCHAR(10), IN `in_admission_num_gen_mode` VARCHAR(10), IN `in_stud_num_gen_mode` VARCHAR(10), IN `in_is_active` TINYINT(1), IN `in_is_new_admission` TINYINT(1), IN `in_created_by` VARCHAR(20), IN `in_distance_from_school` SMALLINT(5), IN `in_special_interest` VARCHAR(20), IN `in_is_sibling` TINYINT(1), IN `in_sibling_detail` VARCHAR(20), IN `in_is_rte` TINYINT(1), IN `in_rte_category` VARCHAR(20), IN `in_boarding_type` VARCHAR(50), IN `in_course_group` VARCHAR(10), IN `in_gr_number` MEDIUMINT(8), IN `in_sts_number` VARCHAR(50),IN `in_admission_mode` VARCHAR(50), OUT `out_student_id` MEDIUMINT(8), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT)
    NO SQL
begin

  	declare l_routine_name varchar(50) default 'sp_ins_student_admission_master';
  declare l_err_code char(5) default '00000';
  declare l_err_desc text default '';    	
  declare l_routine_block varchar(15);
  declare l_student_id mediumint(8);
  declare l_enrollment_num varchar(50);
  declare t_enrollment_num varchar(50);
  declare l_admission_num varchar(50);
  declare t_admission_num varchar(50);
  declare l_stud_num varchar(50);
  declare t_stud_num varchar(50);
  declare t_stud_status varchar(25);
    	
  declare exit handler for sqlexception
	  begin
      rollback;
      
     
     get diagnostics condition 1
        l_err_code = RETURNED_SQLSTATE, l_err_desc = MESSAGE_TEXT;

      insert into db_error_log(ay_id, inst_id, routine_name, routine_block, err_code, 
        err_desc, err_type, err_severity, created_by) 
      values 
        (in_ay_id, in_inst_id, l_routine_name, l_routine_name, l_err_code, l_err_desc, 
        'ERROR', 0, in_created_by);

      set out_err_code = l_err_code;
      set out_err_desc = l_err_desc;

		end;
        
    start transaction;
    
      case in_dml_mode 
        when 'I' then
          begin
          set l_routine_block = 'insert';
           set l_student_id = fn_sequence_generator('STUDENT_MASTER');
           
				IF ( in_enrollment_num_gen_mode='Y' AND length(trim(in_enrollment_num)) = 0 AND  in_stud_status>0 ) THEN 
					call sp_custom_seq_generator(in_inst_id,'STUD_ENROLLMENT_NUM',in_ay_id,in_created_by,l_enrollment_num) ;
					IF length(trim(l_enrollment_num)) = 0 THEN
					   SIGNAL SQLSTATE '10004' 
				      SET MESSAGE_TEXT = 'An error occurred while generating Student Enrollment Num';
			     END IF;
				ELSE
					select enrollment_num INTO t_enrollment_num FROM student_master where enrollment_num=in_enrollment_num and inst_id=in_inst_id and is_active=1 and is_deleted=0;
					IF (t_enrollment_num=in_enrollment_num) THEN
						SIGNAL SQLSTATE '10005' 
				      SET MESSAGE_TEXT = 'Enrollment Number is already present';
				   ELSE   
						set l_enrollment_num =in_enrollment_num;
					END IF;	
				END IF;
        
            insert into student_master(ay_id, inst_id,student_id,standard_id,admission_num,
              admission_ref, enrollment_num,enquiry_date,enrollment_date,enrolled_for_std,first_name,
              middle_name,last_name, birth_date,gender,blood_group,birth_place,nationality,
              language,religion,student_category,student_subcategory,consession_category,
              disability,address_line_1, address_line_2,locality,city ,district,state,
              country,pincode,mobile_phone_main,email_main,identification_mark_1,
              aadhar_card_num,passport_num,prev_school_name,prev_school_address,
              prev_school_board,stud_status, status_desc, is_new_admission, is_active,
              created_by, updated_by, distance_from_school, special_interest, 
              is_sibling, sibling_detail, is_rte, rte_category,
              boarding_type,course_group, gr_number, sts_number,password) 
    	 			values(in_ay_id, in_inst_id,l_student_id,in_standard_id,in_admission_num,
              in_admission_ref, l_enrollment_num,in_enquiry_date,in_enrollment_date,in_enrolled_for_std,
              in_first_name,in_middle_name, in_last_name, in_birth_date,in_gender,
              in_blood_group,in_birth_place,in_nationality,in_language,in_religion,
              in_student_category,in_student_subcategory,in_consession_category,
              in_disability,in_address_line_1,in_address_line_2,in_locality,in_city ,
              in_district,in_state,in_country,in_pincode,in_mobile_phone_main,in_email_main,
              in_identification_mark_1,in_aadhar_card_num,in_passport_num,
              in_prev_school_name,in_prev_school_address,in_prev_school_board,
              in_stud_status,in_status_desc, in_is_new_admission, in_is_active,
              in_created_by, in_created_by,
              in_distance_from_school, in_special_interest, 
              in_is_sibling, in_sibling_detail, in_is_rte, in_rte_category,
              in_boarding_type,in_course_group, in_gr_number, in_sts_number,in_admission_mode);
            
          end;    
  
  	    when 'U' then
       set l_routine_block = 'update';   
         begin
			
			set l_admission_num =in_admission_num;
			
			IF ( in_admission_num_gen_mode='Y' AND length(trim(in_admission_num)) = 0 AND  in_stud_status>=90 ) THEN 
				call sp_custom_seq_generator(in_inst_id,'STUD_ADMISSION_NUM',in_ay_id,in_created_by,l_admission_num) ;
				IF length(trim(l_admission_num)) = 0 THEN
				   SIGNAL SQLSTATE '10006' 
				  SET MESSAGE_TEXT = 'An error occurred while generating Student Admission Num';
				END IF;
			END IF;		
			
			IF ( in_admission_num_gen_mode='N' AND length(trim(in_admission_num)) > 0 AND  in_stud_status>=90 ) THEN 
				SELECT admission_num INTO t_admission_num  FROM student_master where student_id=in_student_id  and inst_id=in_inst_id and is_active=1 and is_deleted=0;
				IF (length(trim(t_admission_num)) = 0  ) THEN
								select admission_num INTO t_admission_num FROM student_master where admission_num=in_admission_num and inst_id=in_inst_id and is_active=1 and is_deleted=0;
								IF (t_admission_num=in_admission_num) THEN
									SIGNAL SQLSTATE '10007'
									SET MESSAGE_TEXT = 'Admission Number is already present';
								END IF;
				END IF;
			ELSEIF ( in_admission_num_gen_mode='N' AND  in_stud_status>=90  AND length(trim(in_admission_num)) = 0 ) THEN 
				SIGNAL SQLSTATE '10008'
				SET MESSAGE_TEXT = 'Please Enter Admission Number';
			END IF;			

			set l_stud_num= in_student_num;

			IF ( in_stud_num_gen_mode='Y' AND ( length(trim(in_student_num)) = 0  OR  in_student_num<=0  ) AND  in_stud_status>=100 ) THEN 
					call sp_custom_seq_generator(in_inst_id,'STUD_GUID',in_ay_id,in_created_by,l_stud_num) ;
					IF length(trim(l_stud_num)) = 0 THEN
					   SIGNAL SQLSTATE '10009' 
					    SET MESSAGE_TEXT = 'An error occurred while generating Student Num';
					END IF;
			END IF;

         update student_master set ay_id=in_ay_id, 
        		standard_id=in_standard_id,
        		student_num=l_stud_num,
            admission_num=l_admission_num,
            admission_ref=in_admission_ref, 
            enrollment_num=in_enrollment_num,
			enquiry_date=in_enquiry_date,
            enrollment_date=in_enrollment_date,
            enrolled_for_std=in_enrolled_for_std,
            first_name=in_first_name,
            middle_name=in_middle_name,
            last_name=in_last_name,
            birth_date=in_birth_date,
            gender=in_gender,
            blood_group=in_blood_group,
            birth_place=in_birth_place,
            nationality=in_nationality,
            language=in_language,
            religion=in_religion,
            student_category=in_student_category,
            student_subcategory=in_student_subcategory,
            consession_category=in_consession_category,
            disability=in_disability,
            address_line_1=in_address_line_1,
            address_line_2=in_address_line_2,
            locality=in_locality,
            city=in_city,
            district=in_district,
            state=in_state,
            country=in_country,
            pincode=in_pincode,
            mobile_phone_main=in_mobile_phone_main,
            email_main=in_email_main,
            identification_mark_1=in_identification_mark_1,
            aadhar_card_num=in_aadhar_card_num,
            passport_num=in_passport_num,
            prev_school_name=in_prev_school_name,
            prev_school_address=in_prev_school_address,
            prev_school_board=in_prev_school_board,
            stud_status=in_stud_status,
            status_desc=in_status_desc,
            is_new_admission=in_is_new_admission,
            is_active=in_is_active,
            created_by=in_created_by, 
            updated_by=in_created_by,
            distance_from_school=in_distance_from_school,
            special_interest=in_special_interest,
            is_sibling=in_is_sibling,
            sibling_detail=in_sibling_detail,
            is_rte=in_is_rte,
            rte_category=in_rte_category,
            boarding_type=in_boarding_type,
            course_group=in_course_group,
            gr_number=in_gr_number,
            sts_number=in_sts_number,
			password=in_admission_mode
          	where inst_id =in_inst_id and student_id=in_student_id and is_active=1 and is_deleted=0;

        call sp_calculate_student_score_in_bulk (in_ay_id, in_inst_id, in_student_id, in_created_by, 
            l_err_code, l_err_desc);
       

     end;    
      end case;
    
    commit;
    set out_student_id =l_student_id;
    set out_err_code = l_err_code;
    set out_err_desc = l_err_desc;
end$$
DELIMITER ;