Mysql

使用 PHP MYSQL 生成月度日期和小時報表

  • August 1, 2019

PHP 中的考勤門戶

我在生成最終出勤報告時遇到問題

我被困在項目的最後階段：

我有一個表 - 出勤，如果學生在場，我將其標記為 1，如果缺席 0。我們有日期、日順序、小時（我們遵循日順序時間表）

Table 
   dayorder:
       id day
       1 day1
       2 day2
       3 day3
       4 day4
       5 day5
       6 SAT
       7 day6
Table 
Hour:
  id
   1
   2
   3
   4
   5
   6
   7

餐桌出席： 在此處輸入圖像描述

在此處輸入圖像描述 我必須生成一個月度報告，如我嘗試過的程式碼如下所示的寄存器 ：

SELECT student_id,deptno,Month, Year_c,
branch.description as bdesc,
course.coursecode as ccd, 
users.firstname as ufn, 
users.lastname as uln,
course.description as ccdes,schedules.hour as hhour,
h,dayorder,AttdDate,
CONCAT(AttdDate,h,dayorder) AS fate,
IF(Day_c=1, p, " ") AS '1',
IF(Day_c=2, p, " ") AS '2',
IF(Day_c=3, p, " ") AS '3',
IF(Day_c=4, p, " ") AS '4',
IF(Day_c=5, p, " ") AS '5',
IF(Day_c=6, p, " ") AS '6',
IF(Day_c=7, p, " ") AS '7',
IF(Day_c=8, p, " ") AS '8',
IF(Day_c=9, p, " ") AS '9',
IF(Day_c=10, p, " ") AS '10',
IF(Day_c=11, p, " ") AS '11',
IF(Day_c=12, p, " ") AS '12',
IF(Day_c=13, p, " ") AS '13',
IF(Day_c=14, p, " ") AS '14',
IF(Day_c=15, p, " ") AS '15',
IF(Day_c=16, p, " ") AS '16',
IF(Day_c=17, p, " ") AS '17',
IF(Day_c=18, p, " ") AS '18',
IF(Day_c=19, p, " ") AS '19',
IF(Day_c=20, p, " ") AS '20',
IF(Day_c=21, p, " ") AS '21',
IF(Day_c=22, p, " ") AS '22',
IF(Day_c=23, p, " ") AS '23',
IF(Day_c=24, p, " ") AS '24',
IF(Day_c=25, p, " ") AS '25',
IF(Day_c=26, p, " ") AS '26',
IF(Day_c=27, p, " ") AS '27',
IF(Day_c=28, p, " ") AS '28',
IF(Day_c=29, p, " ") AS '29',
IF(Day_c=30, p, " ") AS '30',
IF(Day_c=31, p, " ") AS '31'
FROM
(SELECT *,DAY(date) AS Day_c, 
MONTHNAME(date) AS Month, 
Year(date) AS Year_c,
date(date) AS AttdDate,hour as h, day as dayorder,
(CASE  WHEN present = 1 
   THEN 'P'
   WHEN present = 0 
   THEN 'A'
   WHEN present is null   
   THEN ' '
END) AS p
FROM attendance a 
WHERE date between '$from' AND '$to' And branch = $branchid AND coursecode = $courseid AND batch = $batchid
GROUP BY student_id
ORDER BY student_rollno ASC 
)
as report 
LEFT JOIN branch on branch.id = report.branch
LEFT JOIN course on course.id = report.coursecode
LEFT JOIN users on users.id = report.user
LEFT JOIN schedules on schedules.id = report.hour
ORDER BY Month DESC, Year_c DESC

PHPMYADMIN 中的輸出： 在此處輸入圖像描述 但我無法使用 PHP 和 MY SQL 顯示為 HTML 頁面。如何獲取沒有日期但沒有記錄的數據？

我是用 PHP 做的

<?php 
include 'includes/session.php';

if(isset($_POST["from"], $_POST["to"],$_POST["branchid"],$_POST["courseid"],$_POST["batchid"])){
       $from = $_POST["from"];
       $to = $_POST["to"];
       $branchid = $_POST["branchid"];
       $courseid = $_POST["courseid"];
       $batchid = $_POST["batchid"];
       $presentd = array();
       $datea = array();
       $daya = array();
       $houra = array();
       $TotStudents = array();
       $th="";
       $td="";
       $perpre = "";
       $A = array();
       $Studenttblbodypresent = "<table id='Studentpresent' class='table table-bordered dataTable no-footer '>
       <thead class = 'thead-present'><tr><th>DeptNo</th>";

       $Studenttblbody = "";
       $stdsql =   "SELECT deptno,name
                   FROM attendance a WHERE
                   date between '$from' AND '$to' AND a.branch = $branchid AND a.coursecode = $courseid AND a.batch = $batchid
                   GROUP BY student_id
                   ORDER BY student_rollno ASC";
       $querystd = $conn->query($stdsql)or die($conn->error);
       $studentNo = $querystd->num_rows;
       if($studentNo >0){
       // report common header
       $sql =  "SELECT MONTHNAME(date) AS Month,
                       branch.description as bdesc,
                       course.coursecode as ccd, 
                       users.firstname as ufn, 
                       users.lastname as uln,
                       course.description as ccdes,
                       schedules.hour as hhour,
                       day.description as dday,
                       a.hour as hourid,
                       day,course.semester as sem,date(date) AS AttdDate
                FROM attendance a
                   LEFT JOIN branch on branch.id = a.branch
                   LEFT JOIN course on course.id = a.coursecode
                   LEFT JOIN users on users.id = a.user
                   LEFT JOIN schedules on schedules.id = a.hour
                   LEFT JOIN day on day.id = a.day
                WHERE date between '$from' AND '$to' AND a.branch = $branchid AND a.coursecode = $courseid AND a.batch = $batchid
                GROUP BY AttdDate";

           $query = $conn->query($sql)or die($conn->error);                
           $dateNo = $query->num_rows;
           $rowd = $query->fetch_assoc();
           if($rowd['sem'] == 6){$sem = 'VI';}
           else if($rowd['sem'] == 5){$sem = 'V';}
           else if($rowd['sem'] == 4){$sem = 'IV';}
           else if($rowd['sem'] == 3){$sem = 'III';}
           else if($rowd['sem'] == 2){$sem = 'II';}
           else if($rowd['sem'] == 1){$sem = 'I';}
           else {$sem = "";}
           //report header
           $RepHdrtblbody =    "<tr role='row'>
                               <td>".$rowd['Month']."</td>
                               <td>".$sem."</td> 
                               <td>".$rowd['bdesc']."</td>
                               <td>".$rowd['ccdes']."</td> 
                               <td>".$rowd['ccd']."</td>
                               <td>".$rowd['ufn']." ".$rowd['uln']."</td>
                               <td>".$dateNo."</td>
                               </tr>";
               $th ='<td class="dispdates"><span>'.$rowd['AttdDate'].'<br>'.$rowd['hhour'].'<br>'.$rowd['dday'].'</span></td>';
               $datea[] = $rowd['AttdDate'];
               $daya[] = $rowd['day'];
               $houra[] = $rowd['hourid'];$hhour ='';$day = '';
               while($prow = $query->fetch_assoc())
               {   
                   $date = $prow['AttdDate'];
                   $hhour = $prow['hhour'];
                   $day = $prow['dday'];
                   $hourid = $prow['hourid'];
                   $datea[] = $prow['AttdDate'];
                   $daya[] = $prow['day'];
                   $houra[] = $prow['hourid'];
                   //student table header
                   $th.= '<td class="dispdates"><span>'.$date.'<br>'.$hhour.'<br>'.$day.'</span></td>';
               }
               $th.= "<td>Present%</td></tr>";
               $Studenttblbodypresent.=$th."</thead>";

               while($prowstd = $querystd->fetch_assoc())
               {
                   //student list
                   //$Studenttblbody.= '<tr role="row"><td>'.$prowstd['deptno'].'</td><td>'.$prowstd['name'].'</td></tr>';
                   $TotStudents[] = $prowstd['deptno'];
                   $datei=$datea;
                   $dayi=$daya;
                   $houri=$houra;
                   $curStud = $prowstd['deptno'];

                   $countsql = "SELECT a.student_id as regno,
                   a.student_rollno AS rollno,
                   a.name,
                   a.deptno,
                   a.year,
                   branch.description as bdesc,
                   course.coursecode as ccd, 
                   users.firstname as ufn, 
                   users.lastname as uln,
                   course.description as ccdes 
                        , SUM(1)   AS tot
                        , SUM(a.present = 1) AS P
                        , SUM(a.present = 0 ) AS A 

                        , 100.0 
                        * SUM(a.present = 1)
                        / SUM(1) AS perpre

                        , 100.0 
                        * SUM(a.present = 0 )
                        / SUM(1) AS perabs 

                        FROM attendance a
                        LEFT JOIN branch on branch.id = a.branch
                        LEFT JOIN course on course.id = a.coursecode
                        LEFT JOIN users on users.id = a.user
                        where a.date BETWEEN '$from' AND '$to' AND
                        a.branch = $branchid and a.coursecode = $courseid and a.batch = $batchid 
                        AND a.active = 1 AND a.user = $usnid AND a.deptno = '$curStud'
                        GROUP BY a.student_id
                        ORDER BY a.student_rollno ASC";

                   $countquery = $conn->query($countsql);
                   $countrow = $countquery->fetch_assoc();


                   $perpre = $countrow['perpre'];
                   $h = array();
                   $da = array();
                   $d = array();
                   //$th = "";
                   $td = "<tr class='student'><th class = 'thead-present'>".$curStud."</th>";
                   for ($i=0;$i < $dateNo;$i++)
                       {
                           $h = $houri[$i];
                           $da = $datei[$i];
                           $d = $dayi[$i];
                           $b = $branchid;
                           $c = $courseid;
                           $ba = $batchid; 

                           $sqls = "SELECT *,deptno,AttdDate,p
                                       FROM
                                       (SELECT *,DAY(date) AS Day_c, 
                                       MONTHNAME(date) AS Month, 
                                       Year(date) AS Year_c,
                                       date(date) AS AttdDate,hour as h, day as dayorder,
                                       (CASE  WHEN present = 1 
                                           THEN ''
                                           WHEN present = 0 
                                           THEN 'A'
                                       END) AS p
                                       FROM attendance a 
                                       WHERE a.hour = $h AND a.date = '$da' AND a.day = '$d' AND a.branch = $b AND a.coursecode = $c 
                                       AND a.batch = $ba AND deptno = '$curStud'
                                       ORDER BY student_rollno ASC 
                                       )
                                       as report 
                                       LEFT JOIN schedules on schedules.id = report.hour
                                       ORDER BY Month DESC, Year_c DESC,student_rollno ASC,AttdDate ASC";

                                       $querys = $conn->query($sqls)or die($conn->error);
                                       while($prows = $querys->fetch_assoc())
                                       {   

                                           $td.= "<td>".$prows['p']."</td>";
                                       }


                       }
                       $td.="<td>".$perpre."</td></tr>";
                       $Studenttblbodypresent.= $td;
               }



               echo json_encode(array('RepHdrtblbody'=>$RepHdrtblbody,
                                       'present'=>$Studenttblbodypresent,
                                       ));

       }       
       else{
           echo "You have not given attendance";
       }
}
?>

在此處輸入圖像描述

如何獲取沒有日期但沒有記錄的數據？

這是一個常見的問題。通用解決方案是

  1. 建立一個包含所有可能日期的表格（至少在所需的範圍內）。
  2. LEFT JOIN從那張桌子到你的桌子。（或者，如果是複雜查詢，則使用派生表： LEFT JOIN (SELECT ...) AS x ON dates.day = x.day.
  3. 如果需要，使用COALESCE(...)將 NULL 轉換為 0 或空白。

引用自：https://dba.stackexchange.com/questions/243225

相關問答

Mysql

優化一個查詢多個表和多個條件

  • August 11, 2022
檢視問答
Mysql

根據條件選擇表中不存在的行

  • July 26, 2022
檢視問答
Mysql

選擇表中不存在的行

  • July 26, 2022
檢視問答
Sql-Server

您可以在匯總摘要中創建新欄位還是需要嵌套查詢？

  • July 21, 2022
檢視問答
Postgresql

如何在結果表定義未知的情況下生成旋轉的 CROSS JOIN？

  • March 23, 2022
檢視問答
Mysql

如何為以下問題編寫mysql查詢？

  • March 11, 2022
檢視問答