摘要:附上結(jié)果圖片下圖為方法中導(dǎo)出的練習(xí)數(shù)據(jù),頭部引入除過(guò)都是封裝了的方法類(lèi),導(dǎo)出主要參考下面兩個(gè)方法。
</>復(fù)制代碼
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."-用戶(hù)列表";
}else{
$department=0;
$fileName="所有部門(mén)-用戶(hù)列表";
}
if(isset($opts["download"])){
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
}
$h=["部門(mén)","用戶(hù)名","姓名","手機(jī)","工號(hào)","身份"];
$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="所有部門(mén)";
}
$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=["部門(mén)","考試名","時(shí)間","時(shí)長(zhǎng)(分鐘)","總分","及格線(xiàn)(百分制)"];
$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="所有部門(mén)";
}
$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."-考試成績(jī)-".$this->time_range($begin,$end);
if(isset($opts["download"])){
//header("Content-Disposition: attachment;filename="".$fileName.".csv"");
$this->set_filename($fileName.".csv");
}
$h=["部門(mén)","名字","考試名","時(shí)間","總分","分?jǐn)?shù)"];
$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="所有部門(mén)";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$fileName=$name."-練習(xí)數(shù)據(jù)-".$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=["部門(mén)","身份","名字","時(shí)間","結(jié)果"];
$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="所有部門(mén)";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$fileName=$name."-學(xué)習(xí)時(shí)間-".$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=["部門(mén)","身份","名字","時(shí)間","時(shí)長(zhǎng)(秒)","類(lèi)型"];
$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]);
}
}
}
// 每個(gè)學(xué)員學(xué)習(xí)時(shí)間要按學(xué)習(xí)類(lèi)型分組加起來(lái)
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="所有部門(mén)";
}
$begin=(int)$opts["begin"];
$end=(int)$opts["end"];
$fileName=$name."-學(xué)習(xí)時(shí)間-".$this->time_range($begin,$end);
if(isset($opts["download"]))
$this->set_filename($fileName.".csv");
$total=9999;
$h=["部門(mén)","身份","名字","學(xué)習(xí)時(shí)間(秒)","練習(xí)時(shí)長(zhǎng)(秒)","練習(xí)數(shù)量","練習(xí)正確數(shù)量","練習(xí)正確率"];
$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="所有部門(mén)";
}
$fileName=$name."-高頻錯(cuò)題-".$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=["題庫(kù)","題型","題目","選項(xiàng)","答案","答題數(shù)","答題正確數(shù)量","正確率"];
$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]);
}
}
}
//過(guò)關(guān)記錄
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="所有部門(mén)";
}
$fileName=$name."-過(guò)關(guān)記錄-".$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=["姓名","部門(mén)","過(guò)關(guān)記錄","時(shí)間","難度系數(shù)"];
$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);
?>
一個(gè)模塊的代碼。附上結(jié)果圖片
下圖為study方法中導(dǎo)出的練習(xí)數(shù)據(jù),頭部引入除過(guò)session都是封裝了crud的db方法類(lèi),導(dǎo)出主要參考下面兩個(gè)方法。
方法:
</>復(fù)制代碼
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).""");
}
}
方法:
</>復(fù)制代碼
function output(&$row){
for($i=0;$iout,$row);
}
文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址:http://m.specialneedsforspecialkids.com/yun/26073.html
摘要:附上結(jié)果圖片下圖為方法中導(dǎo)出的練習(xí)數(shù)據(jù),頭部引入除過(guò)都是封裝了的方法類(lèi),導(dǎo)出主要參考下面兩個(gè)方法。 require(./db/dbManager.php); require(./session.php); require(./department_utils.php); class ExportCSV{ function __construct(){ $thi...
摘要:開(kāi)發(fā)中經(jīng)常遇到這樣的場(chǎng)景產(chǎn)品汪我要在后臺(tái)做一個(gè)功能,可以導(dǎo)出自定義時(shí)間范圍的訂單信息。結(jié)果,第二天一上班產(chǎn)品汪過(guò)來(lái)就是拍桌子,我想把去年一整年的訂單都導(dǎo)出來(lái),結(jié)果后臺(tái)直接就掛了開(kāi)發(fā)小哥一查,原來(lái)是內(nèi)存溢出了,一年下來(lái)的的訂單量足足有條。 開(kāi)發(fā)中經(jīng)常遇到這樣的場(chǎng)景 產(chǎn)品汪:我要在后臺(tái)做一個(gè)功能,可以導(dǎo)出自定義時(shí)間范圍的訂單信息。開(kāi)發(fā)小哥二話(huà)不說(shuō),半天就把功能做完并上線(xiàn)了。結(jié)果,第二天一上...
摘要:導(dǎo)出功能在管理后臺(tái)算是比較常見(jiàn)的了。但是如果用來(lái)打開(kāi),超過(guò)行的數(shù)據(jù)都會(huì)看不見(jiàn),這是程序的問(wèn)題。本次導(dǎo)出數(shù)據(jù)量很大。原因數(shù)值顯示精度為位造成精度丟失。 導(dǎo)出功能在管理后臺(tái)算是比較常見(jiàn)的了。在實(shí)現(xiàn)導(dǎo)出表格類(lèi)信息的功能時(shí),可以選擇兩種實(shí)現(xiàn)方式: 導(dǎo)出為excel 導(dǎo)出為csv文件格式 用csv方式導(dǎo)出,則可以像導(dǎo)出txt一樣,以文本流的方式進(jìn)行流式處理,不但能導(dǎo)出海量信息,而且流式處理占...
摘要:,是逗號(hào)分隔值的英文縮寫(xiě),通常都是純文本文件。如果你導(dǎo)出的沒(méi)有什么高級(jí)用法的話(huà),只是做導(dǎo)出數(shù)據(jù)用那么建議使用本方法要比要高效的多。二十萬(wàn)數(shù)據(jù)導(dǎo)出大概需要到秒。 CSV,是Comma Separated Value(逗號(hào)分隔值)的英文縮寫(xiě),通常都是純文本文件。如果你導(dǎo)出的Excel沒(méi)有什么高級(jí)用法的話(huà),只是做導(dǎo)出數(shù)據(jù)用那么建議使用本方法,要比PHPexcel要高效的多。二十萬(wàn)數(shù)據(jù)導(dǎo)出大概...
閱讀 1146·2019-08-30 12:44
閱讀 655·2019-08-29 13:03
閱讀 2562·2019-08-28 18:15
閱讀 2431·2019-08-26 10:41
閱讀 3092·2019-08-26 10:28
閱讀 3040·2019-08-23 16:54
閱讀 1992·2019-08-23 15:16
閱讀 817·2019-08-23 14:55
极致性价比!云服务器续费无忧!
Tesla A100/A800、Tesla V100S等多种GPU云主机特惠2折起,不限台数,续费同价。
NVIDIA RTX 40系,高性价比推理显卡,满足AI应用场景需要。
乌兰察布+上海青浦,满足东推西训AI场景需要