/* Navicat MySQL Data Transfer Source Server : qnear.org Source Server Version : 50711 Source Host : qnear.org:3306 Source Database : ddpf Target Server Type : MYSQL Target Server Version : 50711 File Encoding : 65001 Date: 2016-07-03 15:52:09 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for t_accident -- ---------------------------- DROP TABLE IF EXISTS `t_accident`; CREATE TABLE `t_accident` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表Id', `accident_year` varchar(255) DEFAULT NULL COMMENT '事故学年', `accident_term` varchar(255) DEFAULT NULL COMMENT '事故学期', `teacher_name` varchar(255) DEFAULT NULL COMMENT '教师名', `teacher_college` varchar(255) DEFAULT NULL COMMENT '教师学院', `accident_type` varchar(255) DEFAULT NULL COMMENT '事故类型', `accident_comment` varchar(500) DEFAULT NULL COMMENT '事故内容', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_aduit_status -- ---------------------------- DROP TABLE IF EXISTS `t_aduit_status`; CREATE TABLE `t_aduit_status` ( `t_id` bigint(20) NOT NULL AUTO_INCREMENT, `year` varchar(255) COLLATE utf8_bin NOT NULL, `college_status` varchar(255) COLLATE utf8_bin NOT NULL, `graduate_status` varchar(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Table structure for t_course -- ---------------------------- DROP TABLE IF EXISTS `t_course`; CREATE TABLE `t_course` ( `t_id` int(50) NOT NULL AUTO_INCREMENT COMMENT '表ID', `course_year` varchar(255) DEFAULT NULL COMMENT '课表年份', `course_term` varchar(255) DEFAULT NULL COMMENT '课表学期', `course_time` varchar(255) DEFAULT NULL COMMENT '上课时间', `course_week_rank` varchar(255) DEFAULT NULL COMMENT '起止周', `course_name` varchar(255) DEFAULT NULL COMMENT '课程名称', `teacher_account` varchar(255) DEFAULT NULL COMMENT '教师职工号', `teacher_name` varchar(255) DEFAULT NULL COMMENT '教师姓名', `teacher_department` varchar(255) DEFAULT NULL COMMENT '教师所属学院', `class_room` varchar(255) DEFAULT NULL COMMENT '上课教室', `campus_name` varchar(255) DEFAULT NULL COMMENT '校区名称', `student_count` varchar(255) DEFAULT NULL COMMENT '上课人数', `student_class` varchar(255) DEFAULT NULL COMMENT '上课班级', `course_college` varchar(255) DEFAULT NULL COMMENT '开课学院', `arrange_status` int(11) NOT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=170961 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_excellent -- ---------------------------- DROP TABLE IF EXISTS `t_excellent`; CREATE TABLE `t_excellent` ( `t_id` bigint(20) NOT NULL AUTO_INCREMENT, `teacher_account` varchar(255) DEFAULT NULL, `year` varchar(255) DEFAULT NULL, `term` varchar(255) DEFAULT NULL, `teacher_college` varchar(255) DEFAULT NULL, `teacher_name` varchar(255) DEFAULT NULL, `teacher_title_name` varchar(255) DEFAULT NULL, `teacher_job` varchar(255) DEFAULT NULL, `declare_level` varchar(255) DEFAULT NULL, `teacher_rank` varchar(255) DEFAULT NULL, `spv_score` varchar(255) DEFAULT NULL, `stu_score` varchar(255) DEFAULT NULL, `total_score` varchar(255) DEFAULT NULL, `independce_course` varchar(255) DEFAULT NULL, `up__independce_course` varchar(255) DEFAULT NULL, `total_class_hour` varchar(255) DEFAULT NULL, `up__total_class_hour` varchar(255) DEFAULT NULL, `bachelor_hour` varchar(255) DEFAULT NULL, `up__bachelor_hour` varchar(255) DEFAULT NULL, `gradute_hour` varchar(255) DEFAULT NULL, `up__gradute_hour` varchar(255) DEFAULT NULL, `job_reduce` varchar(255) DEFAULT NULL, `total_hour` varchar(255) DEFAULT NULL, `audit_college_status` varchar(255) DEFAULT NULL, `audit_graduate_status` varchar(255) DEFAULT NULL, `audit_final_status` varchar(255) DEFAULT NULL, `college_comform_status` varchar(255) DEFAULT NULL, `accident` varchar(255) DEFAULT NULL, `audit_final_commit_status` varchar(255) DEFAULT NULL COMMENT '最终提交状态', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=309 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_spv_score -- ---------------------------- DROP TABLE IF EXISTS `t_spv_score`; CREATE TABLE `t_spv_score` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表Id', `f_spv` int(20) NOT NULL COMMENT '链接spv表的外键', `should_num` varchar(255) DEFAULT '' COMMENT '应到人数', `actual_num` varchar(255) DEFAULT '' COMMENT '实到人数', `late_num` varchar(255) DEFAULT '' COMMENT '迟到人数', `truant_num` varchar(255) DEFAULT '' COMMENT '旷课人数', `course_content` varchar(255) DEFAULT '' COMMENT '课程内容', `attitude_score` varchar(255) DEFAULT '' COMMENT '教学态度分数', `ability_score` varchar(255) DEFAULT '' COMMENT '教学能力分数', `effect_score` varchar(255) DEFAULT '' COMMENT '教学效果分数', `spv_appraise` varchar(1000) DEFAULT '' COMMENT '评价与建议', `spv_comment` varchar(500) DEFAULT '' COMMENT '备注', PRIMARY KEY (`t_id`), KEY `FK_score_to_spv` (`spv_comment`(255)), KEY `f_score_spv` (`f_spv`), CONSTRAINT `f_score_spv` FOREIGN KEY (`f_spv`) REFERENCES `t_supersior` (`t_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4788 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_stu_score -- ---------------------------- DROP TABLE IF EXISTS `t_stu_score`; CREATE TABLE `t_stu_score` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表id', `course_name` varchar(255) DEFAULT NULL COMMENT '课程名称', `teacher_name` varchar(255) DEFAULT NULL COMMENT '教师名', `teacher_college` varchar(255) DEFAULT NULL COMMENT '教师所在学院', `course_college` varchar(255) DEFAULT NULL COMMENT '开课学院', `course_first_score` varchar(255) DEFAULT NULL COMMENT '课程评分(系统)', `course_final_score` varchar(255) DEFAULT NULL COMMENT '最终分数(修改后)', `course_year` varchar(255) DEFAULT NULL COMMENT '课程学年', `course_term` varchar(255) DEFAULT NULL COMMENT '课程学期', `weight_score` varchar(255) DEFAULT NULL COMMENT '加权平均分', `teacher_account` varchar(255) DEFAULT NULL COMMENT '教师账号', `join_number` varchar(255) DEFAULT NULL COMMENT '参评人数', `count_number` varchar(255) DEFAULT NULL COMMENT '计分人数', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=40271 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_supersior -- ---------------------------- DROP TABLE IF EXISTS `t_supersior`; CREATE TABLE `t_supersior` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表ID', `f_course` int(20) DEFAULT NULL, `f_spv_score` int(20) DEFAULT NULL, `spv_year` varchar(255) DEFAULT NULL, `spv_term` varchar(30) DEFAULT NULL COMMENT '听课学期', `class_teacher` varchar(255) DEFAULT NULL COMMENT '授课教师', `teacher_college` varchar(255) DEFAULT NULL COMMENT '教师所属学院', `course_name` varchar(255) DEFAULT NULL COMMENT '课程名称', `listen_date` varchar(255) DEFAULT NULL COMMENT '听课日期', `listen_week` varchar(255) DEFAULT NULL COMMENT '周次', `listen_day` varchar(255) DEFAULT NULL COMMENT '星期', `listen_section` varchar(255) DEFAULT NULL COMMENT '节次', `class_room` varchar(255) DEFAULT NULL COMMENT '授课地点', `course_class` varchar(255) DEFAULT NULL COMMENT '授课班级', `spv_first_score` varchar(255) DEFAULT NULL COMMENT '评分(系统)', `spv_final_score` varchar(255) DEFAULT NULL COMMENT '评分(修改后)', `spv_level` varchar(255) DEFAULT NULL COMMENT '等级', `spv_comment` varchar(500) DEFAULT NULL COMMENT '备注', `spv_name` varchar(255) DEFAULT NULL COMMENT '督导名', `spv_account` varchar(20) DEFAULT NULL COMMENT '督导工号', `commit_status` varchar(255) DEFAULT '' COMMENT '评价表提交状态', `course_college` varchar(255) DEFAULT NULL COMMENT '开课学院', `to_exc_status` varchar(255) DEFAULT '未推优' COMMENT '推优状态', `class_stu_num` varchar(255) DEFAULT NULL, PRIMARY KEY (`t_id`), KEY `f_spv_to_score` (`f_spv_score`), KEY `f_spv_to_course` (`f_course`), CONSTRAINT `f_spv_to_course` FOREIGN KEY (`f_course`) REFERENCES `t_course` (`t_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `f_spv_to_score` FOREIGN KEY (`f_spv_score`) REFERENCES `t_spv_score` (`t_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=120816 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_sys_appraise -- ---------------------------- DROP TABLE IF EXISTS `t_sys_appraise`; CREATE TABLE `t_sys_appraise` ( `t_id` int(20) NOT NULL COMMENT '表ID', `first_rank_per` varchar(255) DEFAULT NULL COMMENT '一等奖占百分比(排名)', `second_rank_per` varchar(255) DEFAULT NULL COMMENT '二等奖占百分比(排名)', `first_num_per` varchar(255) DEFAULT NULL COMMENT '一等奖占百分比(人数)', `second_num_per` varchar(255) DEFAULT NULL COMMENT '二等奖占百分比(人数)', `least_idpd_course` varchar(255) DEFAULT NULL COMMENT '最少独立承担课程门数', `spv_per` varchar(255) DEFAULT NULL COMMENT '督导评分占评优比例', `stu_per` varchar(255) DEFAULT NULL COMMENT '学生评分占评优比例', `spv_score` varchar(255) DEFAULT NULL COMMENT '评优分数线督导得分', `stu_score` varchar(255) DEFAULT NULL COMMENT '评优分数线学生得分', `least_class_hour` varchar(255) DEFAULT NULL COMMENT '最少教学学时', `start_time` varchar(255) DEFAULT NULL COMMENT '设置推荐评优起始时间', `end_time` varchar(255) DEFAULT NULL COMMENT '设置推荐评优结束时间', `open_time` varchar(255) DEFAULT '' COMMENT '是否开启评优', `toexc_start` varchar(255) DEFAULT NULL COMMENT '推优开启时间', `toexc_end` varchar(255) DEFAULT NULL COMMENT '推优结束时间', `less_total` varchar(255) DEFAULT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_sys_reduce -- ---------------------------- DROP TABLE IF EXISTS `t_sys_reduce`; CREATE TABLE `t_sys_reduce` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表Id', `admin_name` varchar(255) DEFAULT NULL COMMENT '职称', `admin_job` varchar(255) DEFAULT NULL COMMENT '行政职务', `admin_reduce` int(20) DEFAULT NULL COMMENT '职务减免', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_sys_yearterm -- ---------------------------- DROP TABLE IF EXISTS `t_sys_yearterm`; CREATE TABLE `t_sys_yearterm` ( `t_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增Id', `sys_year` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '学年', `sys_term` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '学期', `sys_status` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '是否当前学年学期', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=189 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Table structure for t_teacher_listened -- ---------------------------- DROP TABLE IF EXISTS `t_teacher_listened`; CREATE TABLE `t_teacher_listened` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表ID', `listened_year` varchar(255) DEFAULT NULL COMMENT '学年', `listened_term` varchar(255) DEFAULT NULL COMMENT '学期', `teacher_name` varchar(255) DEFAULT NULL COMMENT '老师名字', `teacher_college` varchar(255) DEFAULT NULL COMMENT '老师所属学院', `cur_listened` int(20) DEFAULT NULL COMMENT '当前被听次数', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2668 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_user -- ---------------------------- DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `t_id` int(20) NOT NULL AUTO_INCREMENT COMMENT '表Id', `user_account` varchar(20) DEFAULT NULL COMMENT '学工账号', `user_password` varchar(255) DEFAULT NULL COMMENT '学工密码', `user_name` varchar(255) DEFAULT NULL COMMENT '学工姓名', `user_department` varchar(255) DEFAULT NULL COMMENT '所在部门', `user_role` varchar(255) DEFAULT NULL COMMENT '学工身份', `user_formation` varchar(255) DEFAULT NULL COMMENT '学工编制', `user_max_listened` int(20) DEFAULT NULL COMMENT '老师最大被听次数', `user_job` varchar(255) DEFAULT NULL COMMENT '学工职务', `user_title` varchar(255) DEFAULT NULL COMMENT '学工职称', `audit_privilege` varchar(255) DEFAULT NULL COMMENT '审核权限', PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=14680 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t__college_num -- ---------------------------- DROP TABLE IF EXISTS `t__college_num`; CREATE TABLE `t__college_num` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `college` varchar(255) NOT NULL, `num` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; -- ---------------------------- -- View structure for score_stu_spv -- ---------------------------- DROP VIEW IF EXISTS `score_stu_spv`; CREATE VIEW `score_stu_spv` AS select `user`.`user_account` AS `account`,`user`.`user_name` AS `teacher_name`,`spv`.`course_name` AS `course_name`,`stu`.`course_final_score` AS `stu_score`,`spv`.`spv_final_score` AS `spv_score`,`spv`.`spv_name` AS `spvname`,`spv`.`spv_year` AS `course_year`,`spv`.`spv_term` AS `course_term`,`spv`.`course_college` AS `course_college`,`spv`.`to_exc_status` AS `to_exc_status`,`user`.`user_department` AS `teacher_college`,((`spv`.`spv_final_score` * `sys`.`spv_per`) + (`stu`.`course_final_score` * `sys`.`stu_per`)) AS `total` from (((`t_stu_score` `stu` join `t_supersior` `spv`) join `t_user` `user`) join `t_sys_appraise` `sys`) where ((`stu`.`course_name` = `spv`.`course_name`) and (`spv`.`teacher_college` = `stu`.`teacher_college`) and (`user`.`user_department` like concat(`spv`.`teacher_college`,_utf8'%')) and (`spv`.`class_teacher` = `stu`.`teacher_name`) and (`spv`.`class_teacher` = `user`.`user_name`) and (`spv`.`spv_year` = `stu`.`course_year`) and (`spv`.`spv_term` = `stu`.`course_term`)) ; -- ---------------------------- -- View structure for score_stu_spv2 -- ---------------------------- DROP VIEW IF EXISTS `score_stu_spv2`; CREATE VIEW `score_stu_spv2` AS select `stu`.`course_year` AS `course_year`,`stu`.`course_term` AS `course_term`,`stu`.`course_name` AS `course_name`,`stu`.`teacher_account` AS `account`,`stu`.`teacher_name` AS `teacher_name`,`stu`.`teacher_college` AS `teacher_college`,`stu`.`course_final_score` AS `stu_score`,`spv`.`spv_final_score` AS `spv_score`,((`stu`.`course_first_score` * `sys`.`stu_per`) + (`spv`.`spv_final_score` * `sys`.`spv_per`)) AS `total` from ((`t_stu_score` `stu` join `t_supersior` `spv`) join `t_sys_appraise` `sys`) where ((`stu`.`course_year` = `spv`.`spv_year`) and (`stu`.`course_term` = `spv`.`spv_term`) and (`stu`.`course_name` = `spv`.`course_name`) and (`stu`.`teacher_name` = `spv`.`class_teacher`) and (`stu`.`teacher_college` = `spv`.`teacher_college`)) ; -- ---------------------------- -- View structure for v_listened -- ---------------------------- DROP VIEW IF EXISTS `v_listened`; CREATE VIEW `v_listened` AS select `l`.`listened_year` AS `listened_year`,`l`.`listened_term` AS `listened_term`,concat(`u`.`user_name`,_utf8'-',`l`.`teacher_college`) AS `listened_key`,(case when ((`u`.`user_max_listened` - `l`.`cur_listened`) > 0) then (`u`.`user_max_listened` - `l`.`cur_listened`) else 0 end) AS `residue_course` from (`t_user` `u` join `t_teacher_listened` `l`) where ((`u`.`user_name` = `l`.`teacher_name`) and (`u`.`user_department` like concat(`l`.`teacher_college`,_utf8'%'))) union select NULL ,NULL ,concat(`u`.`user_name`,_utf8'-',(case when (locate(_utf8'(',`u`.`user_department`) > 0) then substr(`u`.`user_department`,1,(locate(_utf8'(',`u`.`user_department`) - 1)) else `u`.`user_department` end)) AS `listened_key`,`u`.`user_max_listened` AS `user_max_listened` from `t_user` `u` ; -- ---------------------------- -- View structure for v_tea_listened -- ---------------------------- DROP VIEW IF EXISTS `v_tea_listened`; CREATE VIEW `v_tea_listened` AS select `t_user`.`user_account` AS `user_account`,`t_teacher_listened`.`listened_year` AS `listened_year`,`t_teacher_listened`.`listened_term` AS `listened_term`,`t_teacher_listened`.`cur_listened` AS `cur_listened`,`t_user`.`user_max_listened` AS `user_max_listened`,`t_user`.`user_name` AS `user_name`,`t_user`.`user_department` AS `user_department` from (`t_user` join `t_teacher_listened`) where ((`t_user`.`user_name` = `t_teacher_listened`.`teacher_name`) and (`t_user`.`user_department` like concat(`t_teacher_listened`.`teacher_college`,'%')) and (`t_user`.`user_role` like '%教师%')) ; -- ---------------------------- -- View structure for v_to_excellent -- ---------------------------- DROP VIEW IF EXISTS `v_to_excellent`; CREATE VIEW `v_to_excellent` AS select `user`.`user_account` AS `user_account`,`spv`.`spv_year` AS `year`,`user`.`user_name` AS `teacher_name`,`user`.`user_department` AS `teacher_college`,avg(`stu`.`course_final_score`) AS `stu_score`,avg(`spv`.`spv_final_score`) AS `spv_score`,((avg(`stu`.`course_final_score`) * `sys`.`stu_per`) + (avg(`spv`.`spv_final_score`) * `sys`.`spv_per`)) AS `total`,`spv`.`to_exc_status` AS `status` from (((`t_stu_score` `stu` join `t_supersior` `spv`) join `t_user` `user`) join `t_sys_appraise` `sys`) where ((`spv`.`class_teacher` = `stu`.`teacher_name`) and (`spv`.`teacher_college` = `stu`.`teacher_college`) and (`spv`.`class_teacher` = `user`.`user_name`) and (`user`.`user_department` like concat(`spv`.`teacher_college`,_utf8'%')) and (`spv`.`spv_year` = `stu`.`course_year`) and (`spv`.`spv_final_score` > 0)) group by `spv`.`spv_year`,`user`.`user_name`,`user`.`user_department` ; -- ---------------------------- -- Procedure structure for count_sum_score -- ---------------------------- DROP PROCEDURE IF EXISTS `count_sum_score`; DELIMITER ;; CREATE PROCEDURE `count_sum_score`(OUT `A_RESULT` int,OUT `B_RESULT` int,OUT `C_RESULT` int,OUT `ARG_A` int,OUT `ARG_B` int) BEGIN DECLARE V_STUPROPORTION FLOAT DEFAULT 0.0 ; DECLARE V_SUPPROPORTION FLOAT DEFAULT 0.0 ; -- 系统参数 学生、督导评分比例 SELECT STUPROPORTION INTO V_STUPROPORTION FROM t_system ORDER BY TBLE_ID LIMIT 0,1; SELECT SUPPROPORTION INTO V_SUPPROPORTION FROM t_system ORDER BY TBLE_ID LIMIT 0,1; IF V_STUPROPORTION<=0 OR V_STUPROPORTION>1 OR V_STUPROPORTION IS NULL then SET V_STUPROPORTION=1; end if; IF V_SUPPROPORTION<=0 OR V_SUPPROPORTION>1 OR V_SUPPROPORTION is NULL then SET V_SUPPROPORTION=1; end if; start transaction; UPDATE t_course_score SET TOTAL_SCORE=( IFNULL(SUOERVISON_SCORE,0)*V_SUPPROPORTION+ IFNULL(STU_SCORE,0) *V_STUPROPORTION); -- 更新t_course_score过程 结束********** UPDATE t_appraise SET SUM_GRADE=( IFNULL(STUDENT_GRADE,0) *V_STUPROPORTION+ IFNULL(SUPERVISOR_GRADE,0) *V_SUPPROPORTION); -- 更新t_appraise过程 结束********** UPDATE t_appraise_first SET SUM_GRADE=( IFNULL(STUDENT_GRADE,0) *V_STUPROPORTION+ IFNULL(SUPERVISOR_GRADE,0) *V_SUPPROPORTION); -- 更新t_appraise_first过程 结束********** SET A_RESULT=1;-- 更新t_course_score过程结果 SET B_RESULT=1;-- 更新t_appraise过程结果 SET C_RESULT=1;-- 更新t_appraise_first过程结果 commit; END ;; DELIMITER ;