1登陆
查询是否有登录人
Table('oa_user')->where("u_name='$u_name'")->find();
2违纪管理sql语句总结(ViolateController)
验证学生姓名
select * from oa_user inner join oa_class on oa_user.class_id=oa_class.class_idwhere u_name='$v_name
查询学生违纪、班级值和分页字段
select * from oa_violate INNER JOIN violate_type on oa_violate.type_id=violate_type.type_id INNER JOIN oa_class on oa_violate.class_id=oa_class.class_id limit $page_limit,$page_size
批量删除
violate_id in($violate_id)
delete from oa_violate where violate_id in($violate_id) ;
审核
update oa_violate set v_state=1 where violate_id='$violate_id'
搜索
if($v_name==""){
$where="1 ";
}else{
$where="v_name like '%$v_name%' ";
}
if($class_id!=''){
$where.="and oa_class.class_id='$class_id' ";
}
if($type_id!=''){
$where.="and violate_type.type_id='$type_id' ";
}
if($time1!=''){
$where.="and v_time>'$time1' ";
}
if($time2!=''){
$where.="and v_time<'$time2' ";
}
$data=$obj->query("select * from oa_violate inner joinviolate_type on oa_violate.type_id=violate_type.type_id inner join oa_class onoa_violate.class_id=oa_class.class_id where $where");
3班级管理sql语句总结(ClassesController)
查询角色是班主任的用户
select u_name from oa_user inner join u_r on oa_user.user_id=u_r.user_id inner join oa_role on u_r.role_id=oa_role.role_id where oa_role.r_name='班主任'
查询角色是讲师的用户
select u_name from oa_user inner join u_r on oa_user.user_id=u_r.user_id inner join oa_role on u_r.role_id=oa_role.role_id where oa_role.r_name='讲师'
查询所有课程
select * from course
验证班级名
select * from oa_class where c_name='$data'
查询所有班级表的信息
select * from oa_class
根据班级ID进行删除
delete from oa_class where class_id='$id'
分页查询班级信息以及班级学生人数
select oa_class.class_id,c_name,teacher_name,lecture_name,
count(oa_user.user_id) as num from oa_class
left JOIN oa_user on oa_class.class_id=oa_user.class_id
GROUP BY oa_class.class_id limit $page_limit,$ye
查询所有的班级信息
select * oa_class
根据班级ID进行即点即改
update oa_class set c_name='$data' where class_id='$id'
模糊查询班级名称
select * from oa_class where c_name like '%$id%'
分页后模糊搜索班级
select * from oa_class where c_name like '%$id%' limit $page_limit,$ye
根据ID进行要修改的班级信息查询
select * from oa_class where class_id='%id'
根据ID进行班级信息的修改
update oa_class set c_name=$data['$c_name'] and teacher_name=$data['teacher_name'] and lecture_name=$data['lecture_name'] where class_id='$xid'
查询所有班级信息
select * from oa_class
查询要修改的单条数据
select * from oa_class where class_id='$xid'
4违纪统计sql语句总结(GraphController)
添加违纪
Return $this->Table('oa_violate')->add($data);
阶段违纪对比
select v_stage,count(violate_id) as num from
oa_violate group by v_stage
查询违纪类型
select violate_type.t_name,count(violate_id) as num from oa_violate inner join violate_type on oa_violate.type_id=violate_type.type_id group by violate_type.t_name"
班级违纪排名
SELECT c_name,count(oa_violate.class_id) as num from oa_violate right JOIN oa_class
on oa_class.class_id=oa_violate.class_id
GROUP BY oa_class.c_name
ORDER BY num DESC
老师互查违纪排名
select v_author,count(v_author) as num from oa_violate GROUP BY v_author ORDER BY num desc
班主任违纪KPI
select oa_class.teacher_name,count(oa_user.class_id) as s_num from user INNER JOIN oa_class on user.class_id=oa_class.class_id GROUP BY class.class_id
每个班主任所带班级的总违纪人数
select oa_class.teacher_name,count(oa_violate.class_id) as v_num from oa_violate RIGHT JOIN oa_class on oa_violate.class_id=oa_class.class_id GROUP BY oa_class.class_id
讲师KPI
select oa_class.lecture_name,count(user.class_id) as s_num from use INNER JOIN oa_class on user.class_id=oa_class.class_id GROUP BY oa_class.class_id
每个讲师所带班级的总违纪人数
select count(oa_violate.class_id) as v_num from
oa_violate RIGHT JOIN oa_class on oa_violate.class_id=class.class_id GROUP BY oa_class.class_id
5作业管理sql语句总结(HomeworkController)
Jquery删除
delete from oa_homework where homework_id in($homework_id)
ajax批量删除
delete from oa_homework where homework_id in($homework_id)
搜索总条数
query("select count(*) from oa_homework inner join oa_class on oa_homework.class_id=oa_class.class_id where oa_homework.h_title like ' %$search%' and oa_class.c_name like ' %$name%' ");
ajax搜索后分页
select * from oa_homework inner join oa_class on oa_homework.class_id=oa_class.class_id where oa_homework.h_title like ' %$search%' and oa_class.c_name like ' %$name%' limit $page_limit,$page_size
查询作业内容
select (h_content) from oa_homework where homework_id='$homework_id'
6日志管理sql语句总结(Logcontroller)
查询显示列表根据倒叙排列
select * from oa_log as l inner join oa_class as c on l.class_id=c.class_id order by l_time desc
学生日志列表
select oa_class.class_id,oa_user.u_name,oa_user.user_id,c_name from oa_class inner join oa_user on oa_class.class_id=oa_user.class_id where lecture_name='$name'
学生日志列表倒序排列
select * from oa_user inner join oa_class on oa_log.class_id=oa_class.class_id order by l_time desc limit $limit,2;
删除
Delete from oa_log where log_id in($log_id);
批量删除
Delete from oa_log where l_author='$s';
7学生管理sql语句总结(StudentController)
查询班级信息列表
query("select * from oa_class");
学生修改
Update oa_user set $arr where user_id='$user_id'
学号唯一性
Select * from oa_user where student_num='$student_num';
搜索后分页
$re2=$User->join("oa_class on oa_class.class_id=oa_user.class_id")->limit($limit,$length)->select();
学生信息列表
$count = $User->join("oa_class on oa_user.class_id=oa_class.class_id")->where("oa_user.u_name like '%$u_name%' and oa_class.c_name like '%$class_id%'")->count();
判断传过来的值
if($class_id!=''&&$u_name!=''){
$res=$User->query("select * from oa_user inner join oa_class on oa_user.class_id=oa_class.class_id where u_name like '%$u_name%' and c_name like '%$class_id%' limit $limit,$length");
}else if($u_name!=''){
$res=$User->query("select * from oa_user inner join oa_class on oa_user.class_id=oa_class.class_id where u_name like '%$u_name%' limit $limit,$length");
}else if($class_id!=''){
$res=$User->query("select * from oa_user inner join oa_class on oa_user.class_id=oa_class.class_id where c_name like '%$class_id%' limit $limit,$length");
}else if($class_id==''&&$u_name==''){
$res=$User->query("select * from oa_user inner join oa_class on oa_user.class_id=oa_class.class_id limit $limit,$length");
}
搜索
$re1=$obj->join("oa_class on oa_user.class_id=oa_class.class_id")->where("user_id='$user_id'")->select();
删除
$re=$obj->where("user_id in($user_id)")->delete();
修改
$re=$obj->where("user_id='$id'")->save($arr);
8权限管理sql语句总结(PowerController)
用户列表删除
Delect from oa_user where user_id='$id';
角色列表删除
Delete from oa_role where role_id='$id';
权限功能删除
Delete from r_p where role_id='$role_id';
各班昨日违纪柱状图
select c_name,count(violate_id) as num FROM oa_violate right JOIN oa_class on oa_class.class_id=oa_violate.class_id and oa_violate.v_time<'$today' and oa_violate.v_time>'$yesterday' GROUP BY oa_class.class_id order by num desc
添加用户
$this->Table('oa_user')->add($data);
用户角色关联表
for($i=0;$i<count($r_id);$i++){
$this->query("insert into u_r values('$u_id','$r_id[$i]')");
}
权限添加
return $this->Table('oa_power')->add($data);
查询权限
select distinct(oa_power.power_id),(oa_power.p_name),(oa_power.p_path),(oa_power.method) from oa_user inner join u_r on oa_user.user_id=u_r.user_id inner join oa_role on u_r.role_id=oa_role.role_id
inner join r_p on oa_role.role_id=r_p.role_id inner join oa_power on r_p.power_id=oa_power.power_id where oa_user.user_id=$id
主页面显示
Select lecture_name,course_name,teacher_name,oa_class.c_n
ame,count(*) as c from oa_role inner join u_r on oa_role.role_id=u_r.role_id INNER JOIN oa_user on oa_user.user_id=u_r.user_id
INNER JOIN oa_class on oa_class.class_id=oa_user.class_id inner join oa_course on oa_class.course_id=oa_course.course_id
where u_r.role_id=6 GROUP BY oa_class.c_name
查询角色
$this->table('oa_role')->add($data);
查询角色表数据条数
$this->Table('oa_role')->count();
角色列表分页
Select * from oa_role limit $a,$page_sizes;
用户添加验证唯一
Select * from oa_user where u_name='$u_name';
角色添加验证唯一
Select * from oa_role where r_name='$r_name';
查询用户表条数
$this->Table('oa_user')->count();
用户列表分页
$this->query("select * from oa_user limit $a,$page_sizes");
带条件搜索用户表条数
$this->Table('oa_user')->where("u_name='$name'")->count();
用户表搜索后分页
select * from oa_user where u_name like '%$name%' limit $a,$page_sizes"
用户列表锁定不锁定
if($lock==0){
return $this->query("update oa_user set u_lock=1 where user_id='$id'");
}else{
return $this->query("update oa_user set u_lock=0 where user_id='$id'");
}
角色列表锁定不锁定
if($state==0){
return $this->query("update oa_role set r_is_use=1 where role_id='$id'");
}else{
return $this->query("update oa_role set r_is_use=0 where role_id='$id'");
}
查询权限
$this->query("select * from oa_power");
分配权限动态获取角色
Select * from oa_role;