摘要:附上结果图片下图为方法中导出的练习数据,头部引入除过都是封装了的方法类,导出主要参考下面两个方法。
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;$j output($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;$j output($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;$j output($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;$j output($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;$j output($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;$j db->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;$i db->queryAll($sql,$libs[$i]); if(!$temp) continue; for($j=0;$j output($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/51499.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要高效的多。二十万数据导出大概...
阅读 2781·2021-09-23 11:44
阅读 1673·2021-09-13 10:24
阅读 2622·2021-09-08 09:36
阅读 1231·2019-08-30 15:54
阅读 2249·2019-08-30 13:54
阅读 3308·2019-08-30 10:57
阅读 1847·2019-08-29 18:43
阅读 3611·2019-08-29 15:10