Monthly Date and Hour wise report generation using PHP MYSQL - Phpmyadmin

Gadde
July 8, 2019
174 views
0 votes
2 Answers

Attendance Portal in PHP

I am facing problem at Final Attendance Report Generation

I’m stuck at the final stage of the Project:

I have a Table- attendance, If a student is Present I mark it as 1 if absent 0.
we have date,dayorder,hour (we are following Day Order Time Table)

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

Table attendance:

I must generate a Monthly report like a register as below

Code I have Tried:

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

OUTPUT in PHPMYADMIN

But I am not able to show up as a HTML Page using PHP and MY SQL

Answers

Chosen as BEST ANSWER

I did it in 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";
        }
}
?>

(Edit)

I write an example for PHP MySQL your case but didn’t get your SQL exactly why you have day in description also on the first head no idea. Also, you have to add your code for hours that’s just a correct way to PHP MySQL example also I suggest you to use ez_SQL better object programming with oho MySQL if you stuck don’t hesitate to comment

    <?php
    $sql="SELECT * FROM attendance ORDER BY student_id ASC, date ASC";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        // output data of each row
        echo "<table><thead><tr><td># Student ID</td><td>Dept No</td><td>Description</td>";
        for ($i=0; $i < $day; $i++) { 
            echo "<td>".$i."</td>";
        }
        echo "</tr></thead><body><tr>";

        while($row = $result->fetch_assoc()) {
            $student_id = $row->student_id;
            $deptno = $row->deptno;
            $decp = $row->date." ".$row->semester." ".$row->day;
            $absence = $row->present;
            if(isset($temp) and $temp != $student_id){
                echo "</tr>";
                echo "<tr>";
                echo "<td>".$student_id."</td><td>".$deptno."</td><td>".$decp."</td>"
            }elseif(!isset($temp)){
                echo "<td>".$student_id."</td><td>".$deptno."</td><td>".$decp."</td>";
            }

            echo "<td>".$absence."</td>";
            $temp = $student_id;

        }
echo "</tr></tbody></table>";
    } else {
        echo "0 attendance taken";
    }

Please signup or login to give your own answer.

Click here to cancel reply.

Monthly Date and Hour wise report generation using PHP MYSQL – Phpmyadmin

Answers