国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專(zhuān)欄INFORMATION COLUMN

原生php導(dǎo)出csv文件

antyiwei / 375人閱讀

摘要:附上結(jié)果圖片下圖為方法中導(dǎo)出的練習(xí)數(shù)據(jù),頭部引入除過(guò)都是封裝了的方法類(lèi),導(dǎo)出主要參考下面兩個(gè)方法。

</>復(fù)制代碼

  1. require("./db/dbManager.php");
  2. require("./session.php");
  3. require("./department_utils.php");
  4. class ExportCSV{
  5. function __construct(){
  6. $this->db=new dbManager();
  7. $this->out = fopen("php://output", "w");
  8. }
  9. function output(&$row){
  10. for($i=0;$iout,$row);
  11. }
  12. function user($opts){
  13. if(isset($opts["department"]) && $opts["department"]>0){
  14. $department=(int)$opts["department"];
  15. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  16. if(!$name)
  17. return;
  18. $fileName=$name."-用戶(hù)列表";
  19. }else{
  20. $department=0;
  21. $fileName="所有部門(mén)-用戶(hù)列表";
  22. }
  23. if(isset($opts["download"])){
  24. //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
  25. $this->set_filename($fileName.".csv");
  26. }
  27. $h=["部門(mén)","用戶(hù)名","姓名","手機(jī)","工號(hào)","身份"];
  28. $this->output($h);
  29. $sql="SELECT COUNT(*) FROM users";
  30. if($department>0){
  31. $deps=department_with_child_sql($this->db,$department);
  32. $sql.=" WHERE DepartmentId IN ".$deps;
  33. }
  34. $total=$this->db->querySingle($sql);
  35. $sql="SELECT d.DepartmentName,u.Username,u.Name,u.Phone,u.empno,r.name FROM users u";
  36. $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
  37. $sql.=" LEFT JOIN role r ON r.id=u.role";
  38. if($department>0){
  39. $deps=department_with_child_sql($this->db,$department);
  40. $sql.=" WHERE DepartmentId IN ".$deps;
  41. }
  42. $sql.=" LIMIT ?,10";
  43. for($i=0;$i<$total;$i+=10){
  44. $arr=$this->db->queryAll($sql,[$i]);
  45. if(!$arr)
  46. break;
  47. for($j=0;$joutput($arr[$j]);
  48. }
  49. }
  50. function time_range($b,$e){
  51. return date("Ymd",$b)."-".date("Ymd",$e);
  52. }
  53. function exam($opts){
  54. if(isset($opts["department"]) && $opts["department"]>0){
  55. $department=(int)$opts["department"];
  56. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  57. if(!$name)
  58. return;
  59. }else{
  60. $department=0;
  61. $name="所有部門(mén)";
  62. }
  63. $begin=(int)$opts["begin"];
  64. $end=(int)$opts["end"];
  65. $begin_str=date("Y-m-d H:i:s",$begin);
  66. $end_str=date("Y-m-d H:i:s",$end);
  67. $fileName=$name."-考試-".$this->time_range($begin,$end);
  68. if(isset($opts["download"])){
  69. //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
  70. $this->set_filename($fileName.".csv");
  71. }
  72. $h=["部門(mén)","考試名","時(shí)間","時(shí)長(zhǎng)(分鐘)","總分","及格線(xiàn)(百分制)"];
  73. $this->output($h);
  74. $sql="SELECT COUNT(*) FROM exam e";
  75. $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
  76. if($department>0){
  77. $deps=department_with_child_sql($this->db,$department);
  78. $sql.=" AND DepartmentId IN ".$deps;
  79. }
  80. $total=$this->db->querySingle($sql,[$begin_str,$end_str]);
  81. $sql="SELECT d.DepartmentName,e.ExamName,e.ExamTime,e.ExamDuration,e.Score,e.PassScore FROM exam e";
  82. $sql.=" LEFT JOIN department d ON d.ID=e.DepartmentId";
  83. $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
  84. if($department>0){
  85. $deps=department_with_child_sql($this->db,$department);
  86. $sql.=" AND DepartmentId IN ".$deps;
  87. }
  88. $sql.=" LIMIT ?,10";
  89. for($i=0;$i<$total;$i+=10){
  90. $arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
  91. if(!$arr)
  92. break;
  93. for($j=0;$joutput($arr[$j]);
  94. }
  95. }
  96. }
  97. function grade($opts){
  98. if(isset($opts["department"]) && $opts["department"]>0){
  99. $department=(int)$opts["department"];
  100. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  101. if(!$name)
  102. return;
  103. }else{
  104. $department=0;
  105. $name="所有部門(mén)";
  106. }
  107. $begin=(int)$opts["begin"];
  108. $end=(int)$opts["end"];
  109. $begin_str=date("Y-m-d H:i:s",$begin);
  110. $end_str=date("Y-m-d H:i:s",$end);
  111. $fileName=$name."-考試成績(jī)-".$this->time_range($begin,$end);
  112. if(isset($opts["download"])){
  113. //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
  114. $this->set_filename($fileName.".csv");
  115. }
  116. $h=["部門(mén)","名字","考試名","時(shí)間","總分","分?jǐn)?shù)"];
  117. $this->output($h);
  118. $sql="SELECT COUNT(*) FROM grade g";
  119. $sql.=" LEFT JOIN exam e ON e.ID=g.ExamId";
  120. $sql.=" LEFT JOIN users u ON u.ID=g.UserId";
  121. $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
  122. if($department>0){
  123. $deps=department_with_child_sql($this->db,$department);
  124. $sql.=" AND u.DepartmentId IN ".$deps;
  125. }
  126. $total=$this->db->querySingle($sql,[$begin_str,$end_str]);
  127. $sql="SELECT d.DepartmentName,u.Name,e.ExamName,e.ExamTime,e.Score,g.Score FROM grade g";
  128. $sql.=" LEFT JOIN exam e ON e.ID=g.ExamId";
  129. $sql.=" LEFT JOIN users u ON u.ID=g.UserId";
  130. $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
  131. $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
  132. if($department>0){
  133. $deps=department_with_child_sql($this->db,$department);
  134. $sql.=" AND u.DepartmentId IN ".$deps;
  135. }
  136. $sql.=" LIMIT ?,10";
  137. for($i=0;$i<$total;$i+=10){
  138. $arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
  139. if(!$arr)
  140. break;
  141. for($j=0;$joutput($arr[$j]);
  142. }
  143. }
  144. }
  145. function exercise($opts){
  146. if(isset($opts["department"]) && $opts["department"]>0){
  147. $department=(int)$opts["department"];
  148. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  149. if(!$name)
  150. return;
  151. }else{
  152. $department=0;
  153. $name="所有部門(mén)";
  154. }
  155. $begin=(int)$opts["begin"];
  156. $end=(int)$opts["end"];
  157. $fileName=$name."-練習(xí)數(shù)據(jù)-".$this->time_range($begin,$end);
  158. if(isset($opts["download"])){
  159. //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
  160. $this->set_filename($fileName.".csv");
  161. }
  162. if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
  163. return;
  164. $sql="SELECT COUNT(*) FROM EX.exercise e";
  165. $sql.=" LEFT JOIN users u ON u.ID=e.uid";
  166. $sql.=" WHERE e.time BETWEEN $begin AND $end";
  167. if($department>0){
  168. $deps=department_with_child_sql($this->db,$department);
  169. $sql.=" AND u.DepartmentId IN ".$deps;
  170. }
  171. $total=$this->db->querySingle($sql);
  172. $h=["部門(mén)","身份","名字","時(shí)間","結(jié)果"];
  173. $this->output($h);
  174. $sql="SELECT d.DepartmentName,r.name,u.Name,datetime(e.Time,"unixepoch","localtime"),e.result FROM EX.exercise e";
  175. $sql.=" LEFT JOIN users u ON u.ID=e.uid";
  176. $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
  177. $sql.=" LEFT JOIN role r ON r.ID=u.role";
  178. $sql.=" WHERE e.time BETWEEN $begin AND $end";
  179. if($department>0){
  180. $deps=department_with_child_sql($this->db,$department);
  181. $sql.=" AND u.DepartmentId IN ".$deps;
  182. }
  183. $sql.=" ORDER BY e.time ASC LIMIT ?,10";
  184. for($i=0;$i<$total;$i+=10){
  185. $arr=$this->db->queryAll($sql,[$i]);
  186. if(!$arr)
  187. break;
  188. for($j=0;$joutput($arr[$j]);
  189. }
  190. }
  191. }
  192. function set_filename($fileName){
  193. $userAgent=$_SERVER["HTTP_USER_AGENT"];
  194. if($userAgent && strstr($userAgent,"Firefox")){
  195. header("Content-Disposition: attachment;filename*=""."utf8""".urlencode($fileName).""");
  196. }else{
  197. header("Content-Disposition: attachment;filename="".urlencode($fileName).""");
  198. }
  199. }
  200. function study($opts){
  201. if(isset($opts["department"]) && $opts["department"]>0){
  202. $department=(int)$opts["department"];
  203. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  204. if(!$name)
  205. return;
  206. }else{
  207. $department=0;
  208. $name="所有部門(mén)";
  209. }
  210. $begin=(int)$opts["begin"];
  211. $end=(int)$opts["end"];
  212. $fileName=$name."-學(xué)習(xí)時(shí)間-".$this->time_range($begin,$end);
  213. if(isset($opts["download"]))
  214. //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
  215. $this->set_filename($fileName.".csv");
  216. $sql="SELECT COUNT(*) FROM studyRecord s";
  217. $sql.=" LEFT JOIN users u ON u.ID=s.UserId";
  218. $sql.=" WHERE s.Begin BETWEEN $begin AND $end";
  219. if($department>0){
  220. $deps=department_with_child_sql($this->db,$department);
  221. $sql.=" AND u.DepartmentId IN ".$deps;
  222. }
  223. $total=$this->db->querySingle($sql);
  224. $h=["部門(mén)","身份","名字","時(shí)間","時(shí)長(zhǎng)(秒)","類(lèi)型"];
  225. $this->output($h);
  226. $sql="SELECT d.DepartmentName,r.name,u.Name,datetime(s.Begin,"unixepoch","localtime"),s.Duration,s.Type FROM studyRecord s";
  227. $sql.=" LEFT JOIN users u ON u.ID=s.UserId";
  228. $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
  229. $sql.=" LEFT JOIN role r ON r.ID=u.role";
  230. $sql.=" WHERE s.Begin BETWEEN $begin AND $end";
  231. $sql.=" LIMIT ?,10";
  232. for($i=0;$i<$total;$i+=10){
  233. $arr=$this->db->queryAll($sql,[$i]);
  234. if(!$arr)
  235. break;
  236. for($j=0;$joutput($arr[$j]);
  237. }
  238. }
  239. }
  240. // 每個(gè)學(xué)員學(xué)習(xí)時(shí)間要按學(xué)習(xí)類(lèi)型分組加起來(lái)
  241. function study2($opts){
  242. if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
  243. return;
  244. if(isset($opts["department"]) && $opts["department"]>0){
  245. $department=(int)$opts["department"];
  246. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  247. if(!$name)
  248. return;
  249. }else{
  250. $department=0;
  251. $name="所有部門(mén)";
  252. }
  253. $begin=(int)$opts["begin"];
  254. $end=(int)$opts["end"];
  255. $fileName=$name."-學(xué)習(xí)時(shí)間-".$this->time_range($begin,$end);
  256. if(isset($opts["download"]))
  257. $this->set_filename($fileName.".csv");
  258. $total=9999;
  259. $h=["部門(mén)","身份","名字","學(xué)習(xí)時(shí)間(秒)","練習(xí)時(shí)長(zhǎng)(秒)","練習(xí)數(shù)量","練習(xí)正確數(shù)量","練習(xí)正確率"];
  260. $this->output($h);
  261. $sql="SELECT d.DepartmentName,r.name,u.Name,u.ID FROM users u";
  262. $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
  263. $sql.=" LEFT JOIN role r ON r.ID=u.role";
  264. if($department>0){
  265. $deps=department_with_child_sql($this->db,$department);
  266. $sql.=" WHERE u.DepartmentId IN ".$deps;
  267. }
  268. $sql.=" LIMIT ?,10";
  269. for($i=0;$i<$total;$i+=10){
  270. $arr=$this->db->queryAll($sql,[$i]);
  271. if(!$arr)
  272. break;
  273. 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);
  274. if($temp){
  275. $arr[$j][]=(int)$temp[0];
  276. $arr[$j][]=(int)$temp[1];
  277. }else{
  278. $arr[$j][]=0;
  279. $arr[$j][]=0;
  280. }
  281. $temp=$this->db->query("SELECT COUNT(*),SUM(e.result) FROM EX.exercise e WHERE e.uid=?",$uid);
  282. if($temp){
  283. $arr[$j][]=(int)$temp[0];
  284. $arr[$j][]=(int)$temp[1];
  285. if($temp[0]>0){
  286. $arr[$j][]=sprintf("%.2f",$temp[1]/$temp[0]);
  287. }else{
  288. $arr[$j][]=0;
  289. }
  290. }
  291. $this->output($arr[$j]);
  292. }
  293. }
  294. }
  295. function get_libnames(){
  296. $sql="SELECT Name FROM question_lib group by Name";
  297. return $this->db->querySingleAll($sql);
  298. }
  299. function lib_error($opts){
  300. $begin=(int)$opts["begin"];
  301. $end=(int)$opts["end"];
  302. if(isset($opts["department"]) && $opts["department"]>0){
  303. $department=(int)$opts["department"];
  304. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  305. if(!$name)
  306. return;
  307. }else{
  308. $department=0;
  309. $name="所有部門(mén)";
  310. }
  311. $fileName=$name."-高頻錯(cuò)題-".$this->time_range($begin,$end);
  312. if(isset($opts["download"]))
  313. $this->set_filename($fileName.".csv");
  314. $libs=$this->get_libnames();
  315. if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
  316. return;
  317. $h=["題庫(kù)","題型","題目","選項(xiàng)","答案","答題數(shù)","答題正確數(shù)量","正確率"];
  318. $this->output($h);
  319. $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";
  320. $sql.=" LEFT JOIN users u ON u.ID=uid";
  321. $sql.=" WHERE time BETWEEN $begin AND $end";
  322. if($department>0){
  323. $deps=department_with_child_sql($this->db,$department);
  324. $sql.=" AND u.DepartmentId IN ".$deps;
  325. }
  326. $sql.=" GROUP BY qid";
  327. $ret=$this->db->exec($sql);
  328. if(!$ret){
  329. return;
  330. }
  331. $sql="SELECT q.LibName,q.Type,q.Question,q.Option,q.Answer,s.count,s.result,s.accuracy FROM stat s";
  332. $sql.=" LEFT JOIN EX.question q ON q.ID=s.qid";
  333. $sql.=" WHERE q.LibName=?";
  334. $sql.=" AND s.count>=3";
  335. $sql.=" ORDER BY s.accuracy ASC";
  336. $sql.=" LIMIT 0,10";
  337. for($i=0;$idb->queryAll($sql,$libs[$i]);
  338. if(!$temp)
  339. continue;
  340. for($j=0;$joutput($temp[$j]);
  341. }
  342. }
  343. }
  344. //過(guò)關(guān)記錄
  345. function manmul_lib($opts){
  346. $begin=(int)$opts["begin"];
  347. $end=(int)$opts["end"];
  348. if(isset($opts["department"]) && $opts["department"]>0){
  349. $department=(int)$opts["department"];
  350. $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
  351. if(!$name)
  352. return;
  353. }else{
  354. $department=0;
  355. $name="所有部門(mén)";
  356. }
  357. $fileName=$name."-過(guò)關(guān)記錄-".$this->time_range($begin,$end);
  358. if(isset($opts["download"]))
  359. $this->set_filename($fileName.".csv");
  360. $libs=$this->get_libnames();
  361. if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
  362. return;
  363. $h=["姓名","部門(mén)","過(guò)關(guān)記錄","時(shí)間","難度系數(shù)"];
  364. $this->output($h);
  365. }
  366. }
  367. header("Content-Type: text/plain; charset=GB18030");
  368. //header("Content-Type: application/octet-stream");
  369. if(!isset($_GET["op"]))
  370. return;
  371. $op=$_GET["op"];
  372. if(!isset($_SESSION["AdminId"]))
  373. return;
  374. $ec=new ExportCSV();
  375. if(!department_can_admin($ec->db,$_GET["department"],$_SESSION["AdminId"]))
  376. return;
  377. header("Cache-Control: max-age=0");
  378. if($op=="user")
  379. $ec->user($_GET);
  380. else if($op=="exam")
  381. $ec->exam($_GET);
  382. else if($op=="grade")
  383. $ec->grade($_GET);
  384. else if($op=="exercise")
  385. $ec->exercise($_GET);
  386. else if($op=="study")
  387. $ec->study2($_GET);
  388. else if($op=="libe")
  389. $ec->lib_error($_GET);
  390. else if($op=="manmul")
  391. $ec->manmul_lib($_GET);
  392. ?>

一個(gè)模塊的代碼。附上結(jié)果圖片

下圖為study方法中導(dǎo)出的練習(xí)數(shù)據(jù),頭部引入除過(guò)session都是封裝了crud的db方法類(lèi),導(dǎo)出主要參考下面兩個(gè)方法。

方法:

</>復(fù)制代碼

  1. function set_filename($fileName){
  2. $userAgent=$_SERVER["HTTP_USER_AGENT"];
  3. if($userAgent && strstr($userAgent,"Firefox")){
  4. header("Content-Disposition: attachment;filename*=""."utf8""".urlencode($fileName).""");
  5. }else{
  6. header("Content-Disposition: attachment;filename="".urlencode($fileName).""");
  7. }
  8. }

方法:

</>復(fù)制代碼

  1. function output(&$row){
  2. for($i=0;$iout,$row);
  3. }

文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址:http://m.specialneedsforspecialkids.com/yun/26073.html

相關(guān)文章

  • 原生php導(dǎo)出csv文件

    摘要:附上結(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...

    yankeys 評(píng)論0 收藏0
  • PHP+MySQL導(dǎo)出大量數(shù)據(jù)(Iterator yield)

    摘要:開(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é)果,第二天一上...

    codergarden 評(píng)論0 收藏0
  • 關(guān)于PHP導(dǎo)出CSV文件的實(shí)現(xiàn)過(guò)程以及一些經(jīng)常遇到的問(wèn)題研究

    摘要:導(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ù)83 評(píng)論0 收藏0
  • PHP高效導(dǎo)出Excel(CSV)

    摘要:,是逗號(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)出大概...

    DoINsiSt 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

antyiwei

|高級(jí)講師

TA的文章

閱讀更多
最新活動(dòng)
閱讀需要支付1元查看
<