CREATE TABLE admin.user_master ( user_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, user_name VARCHAR(30) NOT NULL, user_password VARCHAR(40) NOT NULL, pwdsalt VARCHAR(40) NOT NULL, roles TEXT NOT NULL, student_id BIGINT(20) UNSIGNED DEFAULT NULL, staff_id BIGINT(20) UNSIGNED DEFAULT NULL, status TINYINT(1) NOT NULL DEFAULT '0', inserted_by BIGINT(20) UNSIGNED DEFAULT NULL, inserted_date DATETIME NOT NULL, updated_by BIGINT(20) UNSIGNED DEFAULT NULL, updated_date DATETIME NOT NULL, PRIMARY KEY (user_id), UNIQUE KEY (user_name), UNIQUE KEY (student_id), UNIQUE KEY (staff_id));



INSERT INTO admin.user_master(user_id,user_name,user_password,pwdsalt,roles,student_id, staff_id,status,inserted_by, inserted_date, updated_by, updated_date) VALUES('1','admin',SHA1(SHA1(MD5(CONCAT(SHA1('random value'),'private_key_start','password','private_key_end',SHA1('random value'))))), SHA1('random value'), '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35', NULL,'1',1, NULL,NOW(),NULL,NOW());



CREATE TABLE admin.resource_master ( resource_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, label VARCHAR(150) NOT NULL, link VARCHAR(300) DEFAULT NULL COMMENT 'hyperlink to pages', img VARCHAR(450) DEFAULT NULL COMMENT 'Material Design Icon Image Name', extraparameters VARCHAR(500) DEFAULT NULL, parent_id BIGINT(20) UNSIGNED NOT NULL DEFAULT '0', sortorder INT(11) UNSIGNED DEFAULT NULL, critical INT(1) UNSIGNED DEFAULT '0', 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 (resource_id));



insert into admin.resource_master(resource_id,label,link,img,extraparameters,parent_id,sortorder,critical,inserted_by,inserted_date,updated_by,updated_date) values (1,'Admin','#','account_box',NULL,0,1,0,NULL,NULL,NULL,NULL),(2,'Dashboard','JSP/dashboard/admin.jsp','dashboard',NULL,1,2,0,NULL,NULL,NULL,NULL),(3,'Change Password','JSP/Welcome/change_password.jsp',NULL,NULL,1,1,0,NULL,NULL,NULL,NULL),(4,'Staff','#','person',NULL,0,2,0,NULL,NULL,NULL,NULL),(5,'Registration','JSP/staff/staff_registration.jsp','person_add',NULL,4,1,0,NULL,NULL,NULL,NULL),(7,'Leave Request','#','time_to_leave',NULL,99,1,0,NULL,NULL,NULL,NULL),(8,'Payroll','#',NULL,NULL,4,4,0,NULL,NULL,NULL,NULL),(9,'Reports','#','report',NULL,4,99,0,NULL,NULL,NULL,NULL),(10,'Biometric Attendance','#','touch_app',NULL,9,0,0,NULL,NULL,NULL,NULL),(11,'Leave Report','#',NULL,NULL,9,0,0,NULL,NULL,NULL,NULL),(12,'Activities Report','#',NULL,NULL,9,6,0,NULL,NULL,NULL,NULL),(13,'Student','#','group',NULL,0,3,0,NULL,NULL,NULL,NULL),(14,'Profile','JSP/student/student_det_view.jsp','person_outline',NULL,13,8,0,NULL,NULL,NULL,NULL),(15,'Leave Request','#','time_to_leave',NULL,13,9,0,NULL,NULL,NULL,NULL),(16,'Fees','#','monetization_on',NULL,13,1,0,NULL,NULL,NULL,NULL),(17,'Reports','#','report',NULL,13,99,0,NULL,NULL,NULL,NULL),(18,'Leave Report','#',NULL,NULL,17,1,0,NULL,NULL,NULL,NULL),(19,'Activities','#',NULL,NULL,17,1,0,NULL,NULL,NULL,NULL),(20,'Curriculum','#','import_contacts',NULL,0,4,0,NULL,NULL,NULL,NULL),(21,'Subject Entry','JSP/curriculum/subject_master.jsp',NULL,NULL,20,0,0,NULL,NULL,NULL,NULL),(22,'Syllabus Entry','JSP/curriculum/syllabus_entry.jsp','assignment',NULL,20,5,0,NULL,NULL,NULL,NULL),(23,'Reports','JSP/curriculum/curriculum_report.jsp','report',NULL,20,99,0,NULL,NULL,NULL,NULL),(24,'Academics','#','school',NULL,0,5,0,NULL,NULL,NULL,NULL),(25,'Attendance','#',NULL,NULL,24,1,0,NULL,NULL,NULL,NULL),(26,'Mark Entry','JSP/studentAcad/mark_entry.jsp',NULL,NULL,24,2,0,NULL,NULL,NULL,NULL),(27,'Feedback','#',NULL,NULL,24,3,0,NULL,NULL,NULL,NULL),(28,'Reports','#','report',NULL,24,99,0,NULL,NULL,NULL,NULL),(29,'Students Reports','#',NULL,NULL,28,1,0,NULL,NULL,NULL,NULL),(30,'Record Report','JSP/documentation/record_report.jsp',NULL,NULL,93,99,0,NULL,NULL,NULL,NULL),(31,'Staff Reports','#',NULL,NULL,28,1,0,NULL,NULL,NULL,NULL),(32,'Exams','#','kitchen',NULL,0,6,0,NULL,NULL,NULL,NULL),(33,'Result','JSP/curriculum/coe_result.jsp',NULL,NULL,32,1,0,NULL,NULL,NULL,NULL),(34,'Library','#','local_library',NULL,0,7,0,NULL,NULL,NULL,NULL),(35,'Book Search','#','find_in_page',NULL,34,98,0,NULL,NULL,NULL,NULL),(36,'Transactions','#',NULL,NULL,34,97,0,NULL,NULL,NULL,NULL),(37,'Reports','#','report',NULL,34,99,0,NULL,NULL,NULL,NULL),(38,'Accounts','#','list_alt',NULL,0,8,0,NULL,NULL,NULL,NULL),(39,'Masters','#',NULL,NULL,38,1,0,NULL,NULL,NULL,NULL),(40,'Transactions','#',NULL,NULL,38,1,0,NULL,NULL,NULL,NULL),(41,'Reports','#','report',NULL,38,99,0,NULL,NULL,NULL,NULL),(42,'Stores','#','forum',NULL,0,9,0,NULL,NULL,NULL,NULL),(43,'Item Master','#',NULL,NULL,42,1,0,NULL,NULL,NULL,NULL),(44,'Purchase Request','#',NULL,NULL,42,1,0,NULL,NULL,NULL,NULL),(45,'Purchase Order','#',NULL,NULL,42,1,0,NULL,NULL,NULL,NULL),(46,'Reports','#','report',NULL,42,99,0,NULL,NULL,NULL,NULL),(47,'Feedback','#','feedback',NULL,0,10,0,NULL,NULL,NULL,NULL),(48,'Submission','#',NULL,NULL,47,1,0,NULL,NULL,NULL,NULL),(49,'Responses','#',NULL,NULL,47,1,0,NULL,NULL,NULL,NULL),(50,'Reports','#','report',NULL,47,99,0,NULL,NULL,NULL,NULL),(51,'Management','#','accessibility',NULL,0,7,0,NULL,NULL,NULL,NULL),(52,'Request','#',NULL,NULL,51,1,0,NULL,NULL,NULL,NULL),(53,'Reports','#','report',NULL,51,99,0,NULL,NULL,NULL,NULL),(54,'Profile Update','JSP/student/student_det_update.jsp','update',NULL,13,2,0,NULL,NULL,NULL,NULL),(55,'Name List','JSP/studentAcad/name_list.jsp','view_list',NULL,24,4,0,NULL,NULL,NULL,NULL),(56,'Name List','JSP/staff/staff_list.jsp','view_list',NULL,4,2,0,NULL,NULL,NULL,NULL),(57,'Profile Update','JSP/staff/staff_profile_update.jsp','update',NULL,4,3,0,NULL,NULL,NULL,NULL),(58,'Student Incharge Mapping','JSP/studentAcad/student_incharge_mapping.jsp','person_pin',NULL,13,10,0,NULL,NULL,NULL,NULL),(59,'Hostel Allocation','JSP/student/hostel_allocation.jsp','store',NULL,13,12,0,NULL,NULL,NULL,NULL),(60,'TC Details','JSP/student/assign_transfer_certificate.jsp','local_library',NULL,13,11,0,NULL,NULL,NULL,NULL),(61,'Promotion ','JSP/student/promotion_process.jsp','transfer_within_a_station',NULL,13,13,0,NULL,NULL,NULL,NULL),(62,'Log View','JSP/staff/log_view.jsp','how_to_reg',NULL,4,5,0,NULL,NULL,NULL,NULL),(63,'Student Profile Report','JSP/student/student_profile_report.jsp','report',NULL,17,5,0,NULL,NULL,NULL,NULL),(64,'Leave Request','JSP/staff/leave_request.jsp','arrow_forward',NULL,99,1,0,NULL,NULL,NULL,NULL),(65,'Leave Approval','JSP/staff/leave_approval.jsp','done_all',NULL,99,2,0,NULL,NULL,NULL,NULL),(66,'Leave Report','JSP/staff/staff_leave_report.jsp','report',NULL,97,1,0,NULL,NULL,NULL,NULL),(67,'Record Entry','JSP/documentation/record_entry.jsp','import_contacts',NULL,93,7,0,NULL,NULL,NULL,NULL),(68,'Report','#','report',NULL,20,0,0,NULL,NULL,NULL,NULL),(69,'Room Entry','JSP/curriculum/room_entry.jsp','store',NULL,20,1,0,NULL,NULL,NULL,NULL),(70,'Registration','JSP/student/student_registration.jsp','person_add',NULL,13,1,0,NULL,NULL,NULL,NULL),(71,'Daily Attendance','JSP/staff/staff_daily_att_report.jsp','fingerprint',NULL,4,6,0,NULL,NULL,NULL,NULL),(72,'Fee Fixation','JSP/student_fees/fee_fixation.jsp','money',NULL,16,2,0,NULL,NULL,NULL,NULL),(73,'Fee Header Entry','JSP/student_fees/fee_header_entry.jsp','attach_money',NULL,16,1,0,NULL,NULL,NULL,NULL),(74,'Demand Generation','JSP/student_fees/demand_generation.jsp','receipt',NULL,16,3,0,NULL,NULL,NULL,NULL),(75,'Branch-Wise Update','JSP/student_fees/stud_program_update.jsp','update',NULL,16,4,0,NULL,NULL,NULL,NULL),(76,'Student-Wise Update','JSP/student_fees/fee_update_log.jsp','update',NULL,16,5,0,NULL,NULL,NULL,NULL),(77,'Fee Reports','JSP/student_fees/fee_reports.jsp','report',NULL,16,7,0,NULL,NULL,NULL,NULL),(78,'Transactions','JSP/student_fees/stud_fee_history.jsp','history',NULL,16,6,0,NULL,NULL,NULL,NULL),(79,'Promotion','JSP/staff/staff_promotion.jsp','transfer_within_a_station',NULL,4,7,0,NULL,NULL,NULL,NULL),(80,'Subject Allocation','#','local_library',NULL,24,5,0,NULL,NULL,NULL,NULL),(81,'Core Subject Allocation','JSP/studentAcad/core_subject_allocation.jsp','perm_identity',NULL,80,1,0,NULL,NULL,NULL,NULL),(82,'Non Core Subject Allocation','JSP/studentAcad/noncore_subject_allocation.jsp','supervisor_account',NULL,80,2,0,NULL,NULL,NULL,NULL),(83,'Master Attendance','JSP/studentAcad/class_attendance_master.jsp','assignment',NULL,24,2,0,NULL,NULL,NULL,NULL),(84,'Staff Subject Allocation','JSP/studentAcad/staff_subject_allocation.jsp','supervised_user_circle',NULL,80,3,0,NULL,NULL,NULL,NULL),(85,'Time Table Allocation','JSP/studentAcad/time_table_allocation.jsp','person_pin_circle',NULL,80,4,0,NULL,NULL,NULL,NULL),(86,'Class Attendance','JSP/studentAcad/class_attendance.jsp','assignment_ind',NULL,24,2,0,NULL,NULL,NULL,NULL),(87,'Day Order Mapping','JSP/studentAcad/day_order_mapping.jsp','calendar_today',NULL,24,6,0,NULL,NULL,NULL,NULL),(88,'Consolidated Attendance Report','JSP/dashboard/student_acad.jsp','report',NULL,28,1,0,NULL,NULL,NULL,NULL),(89,'Student Report','JSP/studentAcad/student_report.jsp','description',NULL,28,99,0,NULL,NULL,NULL,NULL),(90,'Time Table Report','JSP/studentAcad/time_table_report.jsp','report',NULL,28,98,0,NULL,NULL,NULL,NULL),(91,'Student Att & Mark','JSP/studentAcad/student_att_mark.jsp','description',NULL,28,100,0,NULL,NULL,NULL,NULL),(92,'Alter Acceptance','JSP/staff/alter_acceptance.jsp','people_outline',NULL,99,3,0,NULL,NULL,NULL,NULL),(93,'Documentation','#','storage',NULL,0,8,0,NULL,NULL,NULL,NULL),(94,'Submit / View Document','JSP/documentation/doc_proof.jsp','cloud_done',NULL,93,1,0,NULL,NULL,NULL,NULL),(95,'Subject EP Mapping','JSP/curriculum/subject_ep_mapping.jsp',NULL,NULL,20,2,0,NULL,NULL,NULL,NULL),(96,'Consolidatd Report','JSP/staff/leave_consolidated_report.jsp',NULL,NULL,97,2,0,NULL,NULL,NULL,NULL),(97,'Reports','#','report',NULL,4,99,0,NULL,NULL,NULL,NULL),(98,'Alter Acceptance (Admin)','JSP/staff/alter_acceptance_admin.jsp',NULL,NULL,99,4,0,NULL,NULL,NULL,NULL),(99,'Leave','#',NULL,NULL,4,2,0,NULL,NULL,NULL,NULL),(100,'Transport','#','directions_bus',NULL,0,8,0,NULL,NULL,NULL,NULL),(101,'Master','JSP/Transport/trans_master.jsp','traffic',NULL,100,1,0,NULL,NULL,NULL,NULL),(102,'Bus Travel Request','JSP/Transport/travel_request_entry.jsp','departure_board',NULL,100,2,0,NULL,NULL,NULL,NULL),(103,'Current Count','JSP/dashboard/current_student_count.jsp',NULL,NULL,17,16,0,NULL,NULL,NULL,NULL),(104,'Hostel Count','JSP/dashboard/current_hostel_count.jsp','house',NULL,17,18,0,NULL,NULL,NULL,NULL),(105,'Revoke','JSP/studentAcad/staff_revoke_att_mark.jsp',NULL,NULL,24,18,0,NULL,NULL,NULL,NULL),(106,'Bonafide Certificate','JSP/student/student_bonafide_certificate.jsp',NULL,NULL,17,6,0,NULL,NULL,NULL,NULL),(107,'University Report','JSP/studentAcad/student_report_university.jsp',NULL,NULL,28,102,0,NULL,NULL,NULL,NULL),(108,'Syllabus Entry','#','local_library',NULL,20,0,0,NULL,NULL,NULL,NULL),(109,'Seminar Hall','#','meeting_room',NULL,0,10,0,NULL,NULL,NULL,NULL),(110,'Enquiry/Booking','JSP/documentation/seminar_hall_booking.jsp','house',NULL,109,1,0,NULL,NULL,NULL,NULL),(111,'PO Entry','JSP/curriculum/po_entry.jsp','speaker_notes',NULL,20,3,0,NULL,NULL,NULL,NULL),(112,'PEO Entry','JSP/curriculum/peo_entry.jsp','question_answer',NULL,20,4,0,NULL,NULL,NULL,NULL),(113,'Resource Entry','JSP/library/resource_entry.jsp','library_add',NULL,34,1,0,NULL,NULL,NULL,NULL),(114,'Author Entry','JSP/library/author_entry.jsp','person_add',NULL,117,1,0,NULL,NULL,NULL,NULL),(115,'Attribute Mapping','JSP/documentation/attribute_entity_mapping.jsp',NULL,NULL,93,7,0,NULL,NULL,NULL,NULL),(116,'Publisher Entry','JSP/library/publisher_entry.jsp','person_add',NULL,117,2,0,NULL,NULL,NULL,NULL),(117,'Master','#','domain',NULL,34,1,0,NULL,NULL,NULL,NULL),(118,'Reports','#','report',NULL,0,99,0,NULL,NULL,NULL,NULL),(119,'Vendor Entry','JSP/library/vendor_entry.jsp','person_add',NULL,117,3,0,NULL,NULL,NULL,NULL),(120,'Year wise Community Count','JSP/dashboard/current_student_community.jsp',NULL,NULL,17,10,0,NULL,NULL,NULL,NULL),(121,'Resource Update','JSP/library/resource_update.jsp','update',NULL,34,2,0,NULL,NULL,NULL,NULL),(122,'Staff Holiday Entry','JSP/staff/staff_holiday_entry.jsp',NULL,NULL,4,8,0,NULL,NULL,NULL,NULL),(123,'Staff Relieved List','JSP/staff/staff_relieved_list.jsp',NULL,NULL,97,15,0,NULL,NULL,NULL,NULL),(124,'Resource Location Mapping','JSP/library/location_entry.jsp','add_location',NULL,34,3,0,NULL,NULL,NULL,NULL),(125,'Placement','#','people',NULL,0,9,0,NULL,NULL,NULL,NULL),(126,'Industry Master','JSP/placement/industry_master.jsp','location_city',NULL,125,1,0,NULL,NULL,NULL,NULL),(127,'Recruitment Master','JSP/placement/recruitment_master.jsp','person_add',NULL,125,2,0,NULL,NULL,NULL,NULL),(128,'Recruitment Student Mapping','JSP/placement/recruitment_student_mapping.jsp','plus_one',NULL,125,3,0,NULL,NULL,NULL,NULL),(129,'Recruitment Report','JSP/placement/recruitment_report.jsp','report',NULL,125,4,0,NULL,NULL,NULL,NULL),(130,'Lesson Plan','JSP/curriculum/lesson_plan.jsp',NULL,NULL,20,3,0,NULL,NULL,NULL,NULL),(131,'Audit / Feedback','JSP/studentAcad/audit_entry.jsp',NULL,NULL,24,10,0,NULL,NULL,NULL,NULL),(132,'Tariff Entry','JSP/library/tariff_entry.jsp',NULL,NULL,117,4,0,NULL,NULL,NULL,NULL),(133,'Fine Entry','JSP/library/fine_entry.jsp',NULL,NULL,117,5,0,NULL,NULL,NULL,NULL);



CREATE TABLE admin.role_master (role_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, role_name VARCHAR(100) NOT NULL, description VARCHAR(255) DEFAULT NULL, startuppage VARCHAR(255) DEFAULT NULL, status TINYINT(1) DEFAULT '1' COMMENT 'record status', inserted_by BIGINT(20) UNSIGNED DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) UNSIGNED DEFAULT NULL, updated_date DATE DEFAULT NULL, PRIMARY KEY (role_id), UNIQUE KEY (role_name));



insert into admin.`role_master`(`role_id`,`role_name`,`description`,`startuppage`,`status`,`inserted_by`,`inserted_date`,`updated_by`,`updated_date`) values (1,'Super Admin','description-1',NULL,1,NULL,'2012-02-10 14:09:57',NULL,NULL),(2,'Admin','description-2',NULL,1,NULL,'2012-01-25 21:42:36',NULL,NULL),(3,'Common','description-3',NULL,1,NULL,'2012-04-16 14:30:26',NULL,NULL),(4,'Non-Teaching Staff','description-4',NULL,1,NULL,'2012-04-16 14:32:17',NULL,NULL),(5,'Security','description-5',NULL,1,NULL,'2012-04-16 14:33:18',NULL,NULL),(6,'BoS Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL),(7,'Head of the Department',NULL,NULL,1,NULL,NULL,NULL,NULL),(8,'Principal',NULL,'JSP/dashboard/current_student_count.jsp',1,NULL,NULL,NULL,NULL),(9,'Accounts Manager',NULL,NULL,1,NULL,NULL,NULL,NULL),(10,'Accounts Officer',NULL,NULL,1,NULL,NULL,NULL,NULL),(11,'Accounts Clerk',NULL,NULL,1,NULL,NULL,NULL,NULL),(12,'Stores Manager',NULL,NULL,1,NULL,NULL,NULL,NULL),(13,'Controller of Examination',NULL,NULL,1,NULL,NULL,NULL,NULL),(14,'Academic Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL),(15,'Student Affairs - Manager',NULL,NULL,1,NULL,NULL,NULL,NULL),(16,'Staff Affairs - Manager',NULL,NULL,1,NULL,NULL,NULL,NULL),(17,'Student Affairs - Clerk',NULL,NULL,1,NULL,NULL,NULL,NULL),(18,'Staff Affairs - Clerk',NULL,NULL,1,NULL,NULL,NULL,NULL),(19,'Hostel Warden',NULL,NULL,1,NULL,NULL,NULL,NULL),(20,'Class Mentor',NULL,NULL,1,NULL,NULL,NULL,NULL),(21,'Transport Manager',NULL,NULL,1,NULL,NULL,NULL,NULL),(22,'Transport - Clerk',NULL,NULL,1,NULL,NULL,NULL,NULL),(23,'Principal Office - Clerk',NULL,NULL,1,NULL,NULL,NULL,NULL),(24,'Hostel Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL),(25,'Teaching Staff',NULL,'JSP/studentAcad/class_attendance.jsp',1,NULL,NULL,NULL,NULL),(26,'Student Fees',NULL,NULL,1,NULL,NULL,NULL,NULL),(27,'Subject Allocation',NULL,NULL,1,NULL,NULL,NULL,NULL),(28,'Time Table Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL),(29,'Chairman',NULL,'JSP/dashboard/current_student_count.jsp',1,NULL,NULL,NULL,NULL),(30,'Student(Portal)',NULL,'JSP/studentAcad/audit_entry.jsp',1,NULL,NULL,NULL,NULL),(31,'Librarian',NULL,NULL,1,NULL,NULL,NULL,NULL),(32,'Documentation Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL),(33,'Report Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL),(34,'Vice Principal',NULL,NULL,1,NULL,NULL,NULL,NULL),(35,'Placement Incharge',NULL,NULL,1,NULL,NULL,NULL,NULL);



CREATE TABLE admin.role_resource_mapping (role_id INT(10) UNSIGNED NOT NULL, resources TEXT NOT NULL COMMENT 'list of authorized resources', 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 (role_id), FOREIGN KEY (role_id) REFERENCES admin.role_master (role_id));



INSERT INTO admin.role_resource_mapping(role_id, resources) VALUES (1,'1,2,3,4,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,79,98,99,109,110,67,93'),(2,'2,4,66,96,97,100,101,102,106,54,122,123'),(3,'55,24,13,14,4,56,57,58,62,63,64,65,66,17,92,97,99,109,110,67'),(4,'3,4,64,57,62,66,97,99'),(5,'2,3,4,5,7,8,9,10,11,12,13,14,15'),(6,'22'),(7,'83,22,111,112'),(8,'88,28,83,98,103,104,105,106,54,120'),(11,'13,16,74,75,76,77,78'),(13,'95'),(15,'70,54'),(16,'57,5,58,60,61,71,79'),(21,'100,101,102,103'),(22,'100,102'),(24,'59,106,103'),(25,'86,28,89,90,91,93,94,26,106,107,20,23'),(26,'16,72,73,74,75,76,77,78'),(27,'80,81,82,87,69'),(28,'80,84,85,81,82'),(29,'103,13,17,96,97,4,104,56,71,88,28,24,91'),(30,'13,14,20,23,24,28,50,90,91,93,94'),(31,'34,113,114,116,117,37,119,121,117,124'),(32,'115'),(33,'120'),(34,'83,22,111,112'),(35,'125,126,127,128');



CREATE TABLE admin.role_resource_history ( role_id int(10) unsigned NOT NULL, resources text NOT NULL, doc datetime DEFAULT NULL COMMENT 'date of change i.e. effective date',FOREIGN KEY (role_id) REFERENCES admin.role_resource_mapping (role_id));



CREATE TABLE admin.user_role_history ( user_id BIGINT(20) UNSIGNED DEFAULT NULL, roles TEXT, doc DATETIME DEFAULT NULL COMMENT 'date of change i.e. effective assignment date',FOREIGN KEY (user_id) REFERENCES admin.user_master (user_id));



CREATE TABLE admin.authentication_log ( ip_address VARCHAR(255) NOT NULL, login_time DATETIME NOT NULL, user_name VARCHAR(300) DEFAULT NULL, status VARCHAR(10) NOT NULL);



CREATE TABLE admin.authorization_log (resource TEXT, path TEXT, accesstime DATETIME DEFAULT NULL, status VARCHAR(255) DEFAULT NULL, user_id BIGINT(15) DEFAULT NULL, roles TEXT);



INSERT INTO admin.authorization_log(resource, path,accesstime,status,user_id,roles) VALUES ('page','JSP/student/student_det_view.jsp','2020-02-07 12:52:28','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:52:29','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:52:29','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:52:29','AJAX','1333','30'),('page','JSP/curriculum/curriculum_report.jsp','2020-02-07 12:53:33','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:53:37','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:53:37','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:53:37','AJAX','1333','30'),('page','JSP/student/student_det_view.jsp','2020-02-07 12:53:37','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:53:38','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:53:38','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:53:38','AJAX','1333','30'),('page','JSP/documentation/doc_proof.jsp','2020-02-07 12:54:37','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/documentation/doc_proof.jsp','JSP/documentation/event_process.do','2020-02-07 12:54:38','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:54:42','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:54:42','AJAX','1333','30'),('page','JSP/student/student_det_view.jsp','2020-02-07 12:54:42','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:54:42','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:54:42','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:54:42','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:54:42','AJAX','1333','30'),('page','JSP/student/student_det_view.jsp','2020-02-07 12:55:37','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:55:37','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:55:37','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:55:37','AJAX','1333','30'),('page','JSP/student/student_det_view.jsp','2020-02-07 12:56:09','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:56:10','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:56:10','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/student/student_det_view.jsp','JSP/student/student_details.do','2020-02-07 12:56:10','AJAX','1333','30'),('page','JSP/curriculum/curriculum_report.jsp','2020-02-07 12:56:18','Authorized','1333','30'),('page','JSP/studentAcad/student_att_mark.jsp','2020-02-07 12:56:26','Authorized','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/studentAcad/student_att_mark.jsp','JSP/studentAcad/student_report.do','2020-02-07 12:56:26','AJAX','1333','30'),('http://demo.campusstack.org/CAMPUSSTACK/JSP/studentAcad/student_att_mark.jsp','JSP/studentAcad/student_report.do','2020-02-07 12:56:26','AJAX','1333','30'),('page','JSP/admin/admin_report.jsp','2020-02-07 12:58:26','Not Authorized','1333','30');



CREATE TABLE camps.notification_master ( nm_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, nm_title VARCHAR(60) DEFAULT NULL, nm_desc VARCHAR(150) DEFAULT NULL, nm_link TEXT, nm_type ENUM('All','role','user') DEFAULT NULL, start_date DATETIME DEFAULT NULL, end_date DATETIME DEFAULT NULL, repeat_after DOUBLE DEFAULT '0', notify ENUM('once','until view','repeat') DEFAULT NULL, trigger_type ENUM('direct','query') DEFAULT NULL, STATUS INT(1) 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 (nm_id));



INSERT INTO camps.notification_master (nm_title,nm_desc,nm_link,nm_type,start_date,end_date,repeat_after,notify,trigger_type,status) VALUES ('Welcome', 'Welcome Admin','#','role','1996-01-01 05:05:05','2050-01-01 05:05:13',0,'repeat','query','1');



CREATE TABLE camps.notification_query ( nq_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, nm_id bigint(20) unsigned NOT NULL, query text, status int(1) DEFAULT NULL, inserted_date datetime DEFAULT NULL, inserted_by bigint(20) unsigned DEFAULT NULL, updated_date datetime DEFAULT NULL, updated_by bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (nq_id), FOREIGN KEY (nm_id) REFERENCES camps.notification_master (nm_id));



INSERT INTO camps.notification_query(nm_id,`query`,status,inserted_date,inserted_by,updated_date,updated_by) VALUES (1,'SELECT \'Welcome\' nm_title, \'Welcome -\' qdesc,\'#\' nm_link',1,NULL,NULL,NULL,NULL);



CREATE TABLE camps.notification_specific ( nm_id BIGINT(20) UNSIGNED NOT NULL, nm_type ENUM('role','user') DEFAULT NULL, type_value BIGINT(20) UNSIGNED NOT NULL, status INT(1) DEFAULT NULL, inserted_by BIGINT(20) DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (nm_id,type_value), FOREIGN KEY (nm_id) REFERENCES notification_master (nm_id));



CREATE TABLE camps.notification_history (nh_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, nm_id BIGINT(20) UNSIGNED DEFAULT NULL, user_id BIGINT(20) UNSIGNED DEFAULT NULL, delivery_date DATETIME DEFAULT NULL, viewed_date DATETIME DEFAULT NULL, PRIMARY KEY (nh_id), FOREIGN KEY (nm_id) REFERENCES notification_master (nm_id));



CREATE TABLE admin.exception_master ( exception_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, type text, request_uri text, message text, inserted_by bigint(20) unsigned DEFAULT NULL, inserted_date datetime DEFAULT NULL, PRIMARY KEY (exception_id));




Download Queries