CREATE TABLE camps.student_admission_master ( sam_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, old_admission_no bigint(20) unsigned DEFAULT NULL, student_id bigint(20) unsigned DEFAULT NULL, doa date DEFAULT NULL, branch_id int(10) unsigned DEFAULT NULL, sp_id bigint(20) unsigned DEFAULT NULL, student_status enum('CONTINUING','DISCONTINUED','DECEASED','DEBARRED','COMPLETED','LONG ABSENT') DEFAULT NULL, inserted_by bigint(20) unsigned DEFAULT NULL, inserted_date datetime DEFAULT NULL, updated_by bigint(20) unsigned DEFAULT NULL, updated_date datetime DEFAULT NULL, PRIMARY KEY (sam_id), FOREIGN KEY (student_id) REFERENCES camps.student_master (student_id), FOREIGN KEY (branch_id) REFERENCES camps.master_branch (branch_id));



CREATE TABLE camps.student_promotion ( sp_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, term_id INT(10) UNSIGNED DEFAULT NULL, branch_id INT(10) UNSIGNED DEFAULT NULL, student_id BIGINT(20) UNSIGNED DEFAULT NULL, prog_period_id INT(10) UNSIGNED DEFAULT NULL, roll_no VARCHAR(50) DEFAULT NULL, section VARCHAR(10) DEFAULT NULL, STATUS INT(1) UNSIGNED DEFAULT '1', inserted_by BIGINT(20) UNSIGNED DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) UNSIGNED DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (sp_id), UNIQUE KEY (term_id,student_id),FOREIGN KEY (term_id) REFERENCES master_term (term_id), FOREIGN KEY (student_id) REFERENCES student_master (student_id), FOREIGN KEY (branch_id) REFERENCES master_branch (branch_id), FOREIGN KEY (prog_period_id) REFERENCES master_programme_period_det (prog_period_id));



ALTER TABLE camps.student_admission_master add FOREIGN KEY (sp_id) REFERENCES camps.student_promotion (sp_id);



INSERT INTO camps.student_admission_master(student_id,doa,branch_id) SELECT @sno:=@sno+1 student_id, doa,branch_id FROM ( SELECT cnt,programme_level_id,MAKEDATE(1995+ay_id - CASE WHEN programme_level_id =5 THEN 17 ELSE 21 END,ROUND((RAND()*(365-ROUND(RAND()*2+2))))) dob,DATE_ADD(MAKEDATE(1995+ay_id,130), INTERVAL ROUND(RAND()*50) DAY) doa,branch_id,ay_id FROM (SELECT ay_id,programme_level_id,programme_id,programme_name,branch_id,branch_name,CASE WHEN @actual


INSERT INTO camps.student_promotion(`term_id`,`branch_id`,`student_id`,`prog_period_id`,`STATUS`,`inserted_by`,`inserted_date`)SELECT term_id,branch_id,student_id,prog_period_id,1,1,IF(@tm=term_id,@inserted_date,CONCAT(@inserted_date:=inserted_date,@tm:=term_id)) FROM (SELECT term_id,branch_id,student_id,prog_period_id,acyear,inserted_date FROM (SELECT *,CASE WHEN (FIND_IN_SET(term_id,@term_id)=0 && @student_id=student_id) THEN CONCAT(@count:=@count+1,@term_id:=CONCAT(@term_id,',',term_id)) WHEN @student_id<>student_id THEN CONCAT(@count:=1,@term_id:=term_id,@student_id:=student_id) END a,@term_id,@count cnt FROM (SELECT student_id,term_id,sam.branch_id,may1.ay_id,SUBSTR(may1.acc_year,1,4) acyear, DATE(CONCAT(SUBSTR(may1.acc_year,1,4),"-",CASE WHEN (term_id MOD 2)=0 THEN ROUND(RAND()*(12-11)+11) ELSE ROUND(RAND()*(07-06)+06) END,'-',ROUND(RAND()*(28-01)+01))) + INTERVAL RAND()*(58-01)+01 MINUTE + INTERVAL RAND()*(19-08)+08 HOUR + INTERVAL RAND()*(58-01)+01 SECOND inserted_date, term_name,mpp.prog_pattern_id,@term_id:=0,@ay:=0,@student_id:=0,@count:=0 FROM camps.`student_admission_master` sam INNER JOIN camps.`master_branch` mb ON sam.branch_id=mb.branch_id INNER JOIN camps.`master_programme_pattern` mpp ON mpp.prog_pattern_id=mb.prog_pattern_id INNER JOIN camps.`master_academic_year` may ON SUBSTR(may.acc_year,1,4)=YEAR(sam.`doa`) INNER JOIN camps.`master_academic_year` may1 ON may1.ay_id>=may.ay_id AND SUBSTR(may1.acc_year,6,9)<=(YEAR(sam.`doa`)+mpp.`minimum_years`) INNER JOIN camps.`master_term` mt ON mt.`ay_id`=may1.ay_id ORDER BY student_id,term_id)a ORDER BY student_id,term_id)a INNER JOIN camps.`master_programme_period_det` mppd ON mppd.`prog_pattern_id`=a.`prog_pattern_id` AND mppd.period=cnt ORDER BY ay_id, term_id,branch_id)a



UPDATE camps.student_master sm INNER JOIN camps.student_admission_master sam ON sm.student_id=sam.student_id INNER JOIN camps.master_programme_intake mpi ON mpi.`branch_id`=sam.branch_id INNER JOIN camps.master_academic_year may ON may.ay_id = mpi.`ay_id` AND SUBSTR(may.acc_year,1,4)=YEAR(sam.`doa`) INNER JOIN camps.master_branch mb ON mb.branch_id=sam.branch_id INNER JOIN camps.master_programme mp ON mp.programme_id=mb.programme_id SET dob=MAKEDATE(1995+may.ay_id - CASE WHEN programme_level_id =5 THEN 17 ELSE 21 END,ROUND(RAND()*(365-2)+2));



UPDATE camps.master_data md INNER JOIN (SELECT branch_id,ay2_id ay_id, tot_student, ROUND(tot_student/15) staff_req FROM (SELECT mpi.`branch_id`,ay2_id,mt.ay_id,SUM(mpi.`intake_regular`) tot_student FROM (SELECT MIN(sp.`student_id`) student_id,sp.`branch_id`,sp.`prog_period_id`,sp.`term_id`,mt.`ay_id` ay2_id FROM camps.`student_promotion` sp INNER JOIN camps.`master_term` mt ON mt.`term_id`=sp.`term_id` AND mt.`term_name`='odd' GROUP BY sp.`branch_id`,sp.`prog_period_id`,sp.`term_id`)a INNER JOIN (SELECT sp.`student_id`,sp.`branch_id`,sp.`prog_period_id`,MIN(sp.`term_id`) term_id FROM camps.`student_promotion` sp GROUP BY sp.`student_id`) b ON a.student_id=b.student_id INNER JOIN camps.`master_term` mt ON mt.`term_id`=b.`term_id` INNER JOIN camps.`master_programme_intake` mpi ON mpi.`branch_id`=a.branch_id AND mpi.`ay_id`=mt.`ay_id` GROUP BY branch_id,ay2_id ORDER BY branch_id,ay2_id,ay_id) a )b ON b.branch_id=md.`branch_id` AND md.`ay_id`=b.ay_id SET tot_staff_required=b.staff_req; /*total staff required for an academic year*/



UPDATE camps.master_data SET staff_relieve_count= ROUND(RAND()*(tot_staff_required/5));



UPDATE camps.master_data md1 INNER JOIN ( SELECT branch_id, ay_id,IF(@branch_id<>branch_id,CONCAT(@relieve_count:=staff_relieve_count,@branch_id:=branch_id,@total_previous:=tot_staff_required,@required_count:=tot_staff_required,@total_recruit:=tot_staff_required),CONCAT(@required_count:=tot_staff_required-@total_previous,@total_recruit:=@required_count+@relieve_count,@total_previous:=tot_staff_required,@relieve_count:=staff_relieve_count)) a,@required_count,@relieve_count,@total_recruit total_recruit FROM (SELECT *,@total_previous:=0,@required_count=0,@relieve_count=0,@total_recruit:=0, @branch_id:='' FROM camps.master_data md ORDER BY branch_id, ay_id)a ORDER BY branch_id, ay_id ) b ON md1.`branch_id`=b.branch_id AND md1.`ay_id`=b.ay_id SET md1.staff_recruit_count=total_recruit



CREATE TABLE camps.student_transfer_certificate (stc_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, student_id BIGINT(20) UNSIGNED DEFAULT NULL, tc_date DATE DEFAULT NULL, last_class_date DATE DEFAULT NULL, reason_for_leaving VARCHAR(250) DEFAULT NULL, tc_conduct VARCHAR(150) DEFAULT NULL, next_higher_class VARCHAR(150) DEFAULT NULL, description TEXT, previous_tc_no BIGINT(20) UNSIGNED DEFAULT NULL, inserted_by BIGINT(20) UNSIGNED DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) UNSIGNED DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (stc_id), UNIQUE KEY student_id (student_id,previous_tc_no), FOREIGN KEY (student_id) REFERENCES student_master (student_id));



ALTER TABLE camps.`master_programme` ADD COLUMN (programme_level VARCHAR(50));




Download Queries