資訊專欄INFORMATION COLUMN
摘要：附上結果圖片下圖為方法中導出的練習數據，頭部引入除過都是封裝了的方法類，導出主要參考下面兩個方法。
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);
}
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://m.specialneedsforspecialkids.com/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要高效的多。二十萬數據導出大概...
閱讀 2793·2021-09-23 11:44
閱讀 1681·2021-09-13 10:24
閱讀 2629·2021-09-08 09:36
閱讀 1238·2019-08-30 15:54
閱讀 2258·2019-08-30 13:54
閱讀 3317·2019-08-30 10:57
閱讀 1856·2019-08-29 18:43
閱讀 3622·2019-08-29 15:10
