产品三部互联网工程系OA系统项目功能SQL功能总结文档

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;