摘要：附上结果图片下图为方法中导出的练习数据，头部引入除过都是封装了的方法类，导出主要参考下面两个方法。
require("./db/dbManager.php");
require("./session.php");
require("./department_utils.php");
class ExportCSV{
function __construct(){
$this->db=new dbManager();
$this->out = fopen("php://output", "w");
}
function output(&$row){
for($i=0;$iout,$row);
}
function user($opts){
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
$fileName=$name."-用户列表";
}else{
$department=0;
$fileName="所有部门-用户列表";
}
if(isset($opts["download"])){
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
}
$h=["部门","用户名","姓名","手机","工号","身份"];
$this->output($h);
$sql="SELECT COUNT(*) FROM users";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" WHERE DepartmentId IN ".$deps;
}
$total=$this->db->querySingle($sql);
$sql="SELECT d.DepartmentName,u.Username,u.Name,u.Phone,u.empno,r.name FROM users u";
$sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
$sql.=" LEFT JOIN role r ON r.id=u.role";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" WHERE DepartmentId IN ".$deps;
}
$sql.=" LIMIT ?,10";
for($i=0;$i<$total;$i+=10){
$arr=$this->db->queryAll($sql,[$i]);
if(!$arr)
break;
for($j=0;$joutput($arr[$j]);
}
}
function time_range($b,$e){
return date("Ymd",$b)."-".date("Ymd",$e);
}
function exam($opts){
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$begin_str=date("Y-m-d H:i:s",$begin);
$end_str=date("Y-m-d H:i:s",$end);
$fileName=$name."-考试-".$this->time_range($begin,$end);
if(isset($opts["download"])){
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
}
$h=["部门","考试名","时间","时长(分钟)","总分","及格线(百分制)"];
$this->output($h);
$sql="SELECT COUNT(*) FROM exam e";
$sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND DepartmentId IN ".$deps;
}
$total=$this->db->querySingle($sql,[$begin_str,$end_str]);
$sql="SELECT d.DepartmentName,e.ExamName,e.ExamTime,e.ExamDuration,e.Score,e.PassScore FROM exam e";
$sql.=" LEFT JOIN department d ON d.ID=e.DepartmentId";
$sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND DepartmentId IN ".$deps;
}
$sql.=" LIMIT ?,10";
for($i=0;$i<$total;$i+=10){
$arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
if(!$arr)
break;
for($j=0;$joutput($arr[$j]);
}
}
}
function grade($opts){
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$begin_str=date("Y-m-d H:i:s",$begin);
$end_str=date("Y-m-d H:i:s",$end);
$fileName=$name."-考试成绩-".$this->time_range($begin,$end);
if(isset($opts["download"])){
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
}
$h=["部门","名字","考试名","时间","总分","分数"];
$this->output($h);
$sql="SELECT COUNT(*) FROM grade g";
$sql.=" LEFT JOIN exam e ON e.ID=g.ExamId";
$sql.=" LEFT JOIN users u ON u.ID=g.UserId";
$sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND u.DepartmentId IN ".$deps;
}
$total=$this->db->querySingle($sql,[$begin_str,$end_str]);
$sql="SELECT d.DepartmentName,u.Name,e.ExamName,e.ExamTime,e.Score,g.Score FROM grade g";
$sql.=" LEFT JOIN exam e ON e.ID=g.ExamId";
$sql.=" LEFT JOIN users u ON u.ID=g.UserId";
$sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
$sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND u.DepartmentId IN ".$deps;
}
$sql.=" LIMIT ?,10";
for($i=0;$i<$total;$i+=10){
$arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
if(!$arr)
break;
for($j=0;$joutput($arr[$j]);
}
}
}
function exercise($opts){
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$fileName=$name."-练习数据-".$this->time_range($begin,$end);
if(isset($opts["download"])){
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
}
if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
return;
$sql="SELECT COUNT(*) FROM EX.exercise e";
$sql.=" LEFT JOIN users u ON u.ID=e.uid";
$sql.=" WHERE e.time BETWEEN $begin AND $end";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND u.DepartmentId IN ".$deps;
}
$total=$this->db->querySingle($sql);
$h=["部门","身份","名字","时间","结果"];
$this->output($h);
$sql="SELECT d.DepartmentName,r.name,u.Name,datetime(e.Time,"unixepoch","localtime"),e.result FROM EX.exercise e";
$sql.=" LEFT JOIN users u ON u.ID=e.uid";
$sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
$sql.=" LEFT JOIN role r ON r.ID=u.role";
$sql.=" WHERE e.time BETWEEN $begin AND $end";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND u.DepartmentId IN ".$deps;
}
$sql.=" ORDER BY e.time ASC LIMIT ?,10";
for($i=0;$i<$total;$i+=10){
$arr=$this->db->queryAll($sql,[$i]);
if(!$arr)
break;
for($j=0;$joutput($arr[$j]);
}
}
}
function set_filename($fileName){
$userAgent=$_SERVER["HTTP_USER_AGENT"];
if($userAgent && strstr($userAgent,"Firefox")){
header("Content-Disposition: attachment;filename*=""."utf8""".urlencode($fileName).""");
}else{
header("Content-Disposition: attachment;filename="".urlencode($fileName).""");
}
}
function study($opts){
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$fileName=$name."-学习时间-".$this->time_range($begin,$end);
if(isset($opts["download"]))
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
$sql="SELECT COUNT(*) FROM studyRecord s";
$sql.=" LEFT JOIN users u ON u.ID=s.UserId";
$sql.=" WHERE s.Begin BETWEEN $begin AND $end";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND u.DepartmentId IN ".$deps;
}
$total=$this->db->querySingle($sql);
$h=["部门","身份","名字","时间","时长(秒)","类型"];
$this->output($h);
$sql="SELECT d.DepartmentName,r.name,u.Name,datetime(s.Begin,"unixepoch","localtime"),s.Duration,s.Type FROM studyRecord s";
$sql.=" LEFT JOIN users u ON u.ID=s.UserId";
$sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
$sql.=" LEFT JOIN role r ON r.ID=u.role";
$sql.=" WHERE s.Begin BETWEEN $begin AND $end";
$sql.=" LIMIT ?,10";
for($i=0;$i<$total;$i+=10){
$arr=$this->db->queryAll($sql,[$i]);
if(!$arr)
break;
for($j=0;$joutput($arr[$j]);
}
}
}
// 每个学员学习时间要按学习类型分组加起来
function study2($opts){
if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
return;
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$fileName=$name."-学习时间-".$this->time_range($begin,$end);
if(isset($opts["download"]))
$this->set_filename($fileName.".csv");
$total=9999;
$h=["部门","身份","名字","学习时间(秒)","练习时长(秒)","练习数量","练习正确数量","练习正确率"];
$this->output($h);
$sql="SELECT d.DepartmentName,r.name,u.Name,u.ID FROM users u";
$sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
$sql.=" LEFT JOIN role r ON r.ID=u.role";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" WHERE u.DepartmentId IN ".$deps;
}
$sql.=" LIMIT ?,10";
for($i=0;$i<$total;$i+=10){
$arr=$this->db->queryAll($sql,[$i]);
if(!$arr)
break;
for($j=0;$jdb->query("SELECT sum(-s.Duration*(s.Type-1)),sum(s.Duration*s.Type) FROM studyRecord s WHERE s.UserId=? AND s.Begin BETWEEN $begin AND $end",$uid);
if($temp){
$arr[$j][]=(int)$temp[0];
$arr[$j][]=(int)$temp[1];
}else{
$arr[$j][]=0;
$arr[$j][]=0;
}
$temp=$this->db->query("SELECT COUNT(*),SUM(e.result) FROM EX.exercise e WHERE e.uid=?",$uid);
if($temp){
$arr[$j][]=(int)$temp[0];
$arr[$j][]=(int)$temp[1];
if($temp[0]>0){
$arr[$j][]=sprintf("%.2f",$temp[1]/$temp[0]);
}else{
$arr[$j][]=0;
}
}
$this->output($arr[$j]);
}
}
}
function get_libnames(){
$sql="SELECT Name FROM question_lib group by Name";
return $this->db->querySingleAll($sql);
}
function lib_error($opts){
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$fileName=$name."-高频错题-".$this->time_range($begin,$end);
if(isset($opts["download"]))
$this->set_filename($fileName.".csv");
$libs=$this->get_libnames();
if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
return;
$h=["题库","题型","题目","选项","答案","答题数","答题正确数量","正确率"];
$this->output($h);
$sql="CREATE TEMP VIEW stat AS SELECT qid,count(*) as count,sum(result) as result,sum(result)*1.0/count(*)*1.0 as accuracy FROM EX.exercise";
$sql.=" LEFT JOIN users u ON u.ID=uid";
$sql.=" WHERE time BETWEEN $begin AND $end";
if($department>0){
$deps=department_with_child_sql($this->db,$department);
$sql.=" AND u.DepartmentId IN ".$deps;
}
$sql.=" GROUP BY qid";
$ret=$this->db->exec($sql);
if(!$ret){
return;
}
$sql="SELECT q.LibName,q.Type,q.Question,q.Option,q.Answer,s.count,s.result,s.accuracy FROM stat s";
$sql.=" LEFT JOIN EX.question q ON q.ID=s.qid";
$sql.=" WHERE q.LibName=?";
$sql.=" AND s.count>=3";
$sql.=" ORDER BY s.accuracy ASC";
$sql.=" LIMIT 0,10";
for($i=0;$idb->queryAll($sql,$libs[$i]);
if(!$temp)
continue;
for($j=0;$joutput($temp[$j]);
}
}
}
//过关记录
function manmul_lib($opts){
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
if(isset($opts["department"]) && $opts["department"]>0){
$department=(int)$opts["department"];
$name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
if(!$name)
return;
}else{
$department=0;
$name="所有部门";
}
$fileName=$name."-过关记录-".$this->time_range($begin,$end);
if(isset($opts["download"]))
$this->set_filename($fileName.".csv");
$libs=$this->get_libnames();
if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
return;
$h=["姓名","部门","过关记录","时间","难度系数"];
$this->output($h);
}
}
header("Content-Type: text/plain; charset=GB18030");
//header("Content-Type: application/octet-stream");
if(!isset($_GET["op"]))
return;
$op=$_GET["op"];
if(!isset($_SESSION["AdminId"]))
return;
$ec=new ExportCSV();
if(!department_can_admin($ec->db,$_GET["department"],$_SESSION["AdminId"]))
return;
header("Cache-Control: max-age=0");
if($op=="user")
$ec->user($_GET);
else if($op=="exam")
$ec->exam($_GET);
else if($op=="grade")
$ec->grade($_GET);
else if($op=="exercise")
$ec->exercise($_GET);
else if($op=="study")
$ec->study2($_GET);
else if($op=="libe")
$ec->lib_error($_GET);
else if($op=="manmul")
$ec->manmul_lib($_GET);
?>
一个模块的代码。附上结果图片
下图为study方法中导出的练习数据，头部引入除过session都是封装了crud的db方法类，导出主要参考下面两个方法。
方法:
function set_filename($fileName){
$userAgent=$_SERVER["HTTP_USER_AGENT"];
if($userAgent && strstr($userAgent,"Firefox")){
header("Content-Disposition: attachment;filename*=""."utf8""".urlencode($fileName).""");
}else{
header("Content-Disposition: attachment;filename="".urlencode($fileName).""");
}
}
方法：
function output(&$row){
for($i=0;$iout,$row);
}
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/26073.html
摘要：附上结果图片下图为方法中导出的练习数据，头部引入除过都是封装了的方法类，导出主要参考下面两个方法。 require(./db/dbManager.php); require(./session.php); require(./department_utils.php); class ExportCSV{ function __construct(){ $thi...
摘要：开发中经常遇到这样的场景产品汪我要在后台做一个功能，可以导出自定义时间范围的订单信息。结果，第二天一上班产品汪过来就是拍桌子，我想把去年一整年的订单都导出来，结果后台直接就挂了开发小哥一查，原来是内存溢出了，一年下来的的订单量足足有条。 开发中经常遇到这样的场景 产品汪：我要在后台做一个功能，可以导出自定义时间范围的订单信息。开发小哥二话不说，半天就把功能做完并上线了。结果，第二天一上...
摘要：导出功能在管理后台算是比较常见的了。但是如果用来打开，超过行的数据都会看不见，这是程序的问题。本次导出数据量很大。原因数值显示精度为位造成精度丢失。 导出功能在管理后台算是比较常见的了。在实现导出表格类信息的功能时，可以选择两种实现方式： 导出为excel 导出为csv文件格式 用csv方式导出，则可以像导出txt一样，以文本流的方式进行流式处理，不但能导出海量信息，而且流式处理占...
摘要：，是逗号分隔值的英文缩写，通常都是纯文本文件。如果你导出的没有什么高级用法的话，只是做导出数据用那么建议使用本方法要比要高效的多。二十万数据导出大概需要到秒。 CSV，是Comma Separated Value（逗号分隔值）的英文缩写，通常都是纯文本文件。如果你导出的Excel没有什么高级用法的话，只是做导出数据用那么建议使用本方法,要比PHPexcel要高效的多。二十万数据导出大概...
阅读 1134·2019-08-30 12:44
阅读 641·2019-08-29 13:03
阅读 2549·2019-08-28 18:15
阅读 2417·2019-08-26 10:41
阅读 3080·2019-08-26 10:28
阅读 3027·2019-08-23 16:54
阅读 1982·2019-08-23 15:16
阅读 800·2019-08-23 14:55
