<?php
$sql = "SELECT * FROM results WHERE name_of_student =:student AND first_ca != :zero 
         AND second_ca != :zero  AND exam_score != :zero AND third_ca != :zero
         AND class =:class AND exam=:exam AND section =:section AND current_school_session =:year ORDER BY subject ASC";
$conn->query($sql);
$conn->bind(":student", $name);
$conn->bind(":zero", $zero);
$conn->bind(":class", $class);
$conn->bind(":exam", $exam);
$conn->bind(":section", $section);
$conn->bind(":year", $year);
$rowcount = $conn->rowCount();

if ($rowcount > 0) {
    $result = $conn->fetchMultiple();

    //fetch teachers name for the student class
    $sql = "SELECT teacher_name FROM classes WHERE name =:name";
    $conn->query($sql);
    $conn->bind(":name", $class);
    $teacher_name = $conn->fetchColumn();

    //fetch total students in class start
    $zero_value = 0;
    $sql = "SELECT DISTINCT name_of_student FROM results WHERE class =:class AND exam=:exam 
                AND section =:section AND current_school_session =:year AND  first_ca !=:zero 
                 AND second_ca != :zero AND third_ca != :zero
              AND exam_score != :zero";
    $conn->query($sql);
    $conn->bind(":class", $class);
    $conn->bind(":exam", $exam);
    $conn->bind(":section", $section);
    $conn->bind(":year", $year);
    $conn->bind(":zero", $zero_value);
    $total_students_in_class = $conn->rowCount();

    //fetch total students in class end



    class PDF extends TCPDF
    {
        public $conn;

        //students variables

        private $student_name;


        public function Header()
        {
            $conn = new Functions();
            //fetch all available school  information
            $sql = "SELECT * FROM general_settings";
            $conn->query($sql);
            $result_set = $conn->fetchSingle();
            $school_logo = $result_set->logo;
            $school_name = $result_set->school_name;
            $school_tagline = $result_set->school_tagline;
            $phone_no = $result_set->phone_no;
            $email = $result_set->email;
            $address = $result_set->address;
            $school_website = $result_set->school_website;
            global $total_marks_obtained;
            global $total_students_in_class;

            $imageFile = "../admin/upload/$school_logo";
            $this->Image($imageFile, 25, 2, 20, '', 'PNG', '', 'T', false, 300, '', false, false, 0, false, false, false);
            $this->Ln(2);
            $this->SetFont('helvetica', 'B', 20);
            $this->cell(205, 2, "$school_name", 0, 1, "C");
            $this->SetFont('helvetica', 'B', 10);
            $this->cell(190, 3, "$school_tagline", 0, 1, "C");


            if(!empty($school_website)){
                $this->SetFont('helvetica', 'B', 10);
                $this->cell(190, 2, "Website:    $school_website", 0, 1, "C");
                $this->SetFont('helvetica', '');
                $this->cell(86, 0, '__________________________________________________________________________________________________', 0, '', '', '');

            }
            $this->Ln();
            $this->SetFont('helvetica', '');
            $conn = new Functions();
            global  $name;
            global $class;
            global $exam;
            global $section;
            global $year;
            global $new_year;
            $this->student_name = $name;
            $sql = "SELECT * FROM results WHERE name_of_student =:name AND class =:class 
                                AND exam=:exam AND section =:section AND current_school_session =:year";
            $conn->query($sql);
            $conn->bind(":name", $this->student_name);
            $conn->bind(":class", $class);
            $conn->bind(":exam", $exam);
            $conn->bind(":section", $section);
            $conn->bind(":year", $year);
            $result = $conn->fetchSingle();
            $class = $result->class;
            $name_student = $result->name_of_student;
            $current_school_session = $result->current_school_session;

            //fetch reg no
            $sql = "SELECT registerNO FROM student WHERE name =:name";
            $conn->query($sql);
            $conn->bind(":name", $name_student);
            $reg_no = $conn->fetchColumn();
            $new_section = ucfirst($section);
            $new_exam = ucfirst($exam);

            //fetch student profile image
            $sql = "SELECT photo FROM student WHERE name =:name";
            $conn->query($sql);
            $conn->bind(":name", $name_student);
            $photo = $conn->fetchColumn();
            if(empty($photo)){
                $img_file = '../admin/upload/default.png';
            }else{
                $img_file = "../admin/upload/$photo";
            }


            $html = "
              <div class='row'>
                 <div class='col-md-3'>
                   <h3>$school_name</h3>   
                   <span>$address</span>
                   <p><strong>Phone:</strong> $phone_no</p>  
                   <p><strong>Email:</strong> $email</p>  
                </div>
              </div>
              <style>
                h3{font-size: 12px; text-transform: lowercase !important;}  
                p{font-size: 11px; line-height: 10px;} 
                span{font-size: 12px;}
             </style>
            ";
            $html2 = "
              <div class='row'>
                 <div class='col-md-3'>
                   <p><strong>Name:</strong> $name_student</p>
                   <p><strong>Academic Year:</strong> $current_school_session</p>
                  <p><strong>Reg No:</strong> $reg_no </p>
                  <p><strong>Class:</strong> $class &nbsp; <strong>Section:</strong> $new_section</p>
                   <p><strong>Term:</strong> $new_exam </p>
                   
                  
                </div>
              </div>
              <style>
                h3{font-size: 12px;}  
                h3 span{font-weight: 400 !important; font-size: 11px;}
                p{font-size: 11px; line-height: 10px;} 
                div{padding-right: 20px;}
             </style>
            ";

            //fetch marks obtainable
            $zero = 0;
            $sql = "SELECT DISTINCT(subject) FROM results WHERE name_of_student =:name AND current_school_session =:year AND first_ca != :zero AND exam_score != :zero AND second_ca != :zero AND third_ca !=:zero AND class =:class AND exam =:exam";
            $conn->query($sql);
            $conn->bind(":name", $name_student);
            $conn->bind(":year", $year);
            $conn->bind(":zero", $zero);
            $conn->bind(":class", $class);
            $conn->bind(":exam", $exam);
            $rowcount = $conn->rowCount();
            $obtainable_mark = $rowcount * 100;

            //fetch student gender
            $sql = "SELECT sex FROM student WHERE name =:student_name";
            $conn->query($sql);
            $conn->bind(":student_name", $name_student);
            $gender = $conn->fetchColumn();
            $html3 = "
              <div class='row'>
                 <div class='col-md-3'>
                  <p><strong>Mark Obtainable:</strong> $obtainable_mark</p>
                  <p><strong>Gender:</strong> $gender <strong></p>
                  <p><strong>No.of students:</strong> $total_students_in_class <strong></p>
                </div>
              </div>
              <style> 
                p{font-size: 11px; line-height: 10px;} 
                div{padding-right: 20px;}
             </style>
            ";


            $this->WriteHtmlCell(50, 10, '', 15, $html);
            $this->WriteHtmlCell(40, 20, 70, 16, $html2);
            $this->WriteHtmlCell(40, 20, 120, 16, $html3);
            $this->Image($img_file, 170, 28, 32, 32, '', '', '', false, 300, '', false, false, 0);


        }

        public function Footer()
        {

            $this->SetY(-245);
            $this->Ln(9);
            $this->SetFont('times', 'B', '10');
            $this->MultiCell(189, 5, 'TERMINAL REPORT', 0, 'L', 0, 1, '', '', true);

        }


    }


// create new PDF document
    $pdf = new PDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// set document information
    global $school_name;
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor("$school_name");
    $pdf->SetTitle('Terminal Report');


// set default header data
    $pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE, PDF_HEADER_STRING);

// set header and footer fonts
    $pdf->setHeaderFont(array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
    $pdf->setFooterFont(array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// set default monospaced font
    $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
    $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
    $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
    $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

// set auto page breaks
    $pdf->SetAutoPageBreak(FALSE, PDF_MARGIN_BOTTOM);

// set image scale factor
    $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
    if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
        require_once(dirname(__FILE__) . '/lang/eng.php');
        $pdf->setLanguageArray($l);
    }

// ---------------------------------------------------------

// set font
    $pdf->SetFont('helvetica', '', 10);

// add a page
    $pdf->AddPage();
    $sql = "SELECT * FROM general_settings";
    $conn->query($sql);
    $result_details = $conn->fetchSingle();
    $terminal_report_bg = $result_details->terminal_report_bg_image;
    $principal_name = $result_details->principal_name;
    $school_stamp = $result_details->school_stamp;



    if(!empty($terminal_report_bg)){
        //add background image
        $url_bg = $admin_url."upload/$terminal_report_bg";
        $pdf->Image("$url_bg", 60, 70, 90);
    }

    //start fetch section category details

    $sql = "SELECT category FROM sections WHERE name = :name";
    $conn->query($sql);
    $conn->bind(":name", $section);
    $section_category = $conn->fetchColumn();

    //Fetch marks setting for selected class, exam and term
    $sql = "SELECT * FROM mark_setting WHERE term =:exam AND class =:class";
    $conn->query($sql);
    $conn->bind(":exam", $exam);
    $conn->bind(":class", $class);
    $result_details = $conn->fetchSingle();
    $firstca_test_max = $result_details->first_ca;
    $secondca_test_max = $result_details->second_ca;
    $thirdca_test_max = $result_details->third_ca;
    $exam_max = $result_details->exam;

    //end fetch section category details

    $pdf->Ln(40);
    $pdf->SetFont('helvetica', '', 9);
    $pdf->SetFillColor(224, 225, 255);
    $pdf->Cell(48, 4, 'SUBJECT', 1, 0, 'L', 1);
    $pdf->Cell(16, 4, "CA1 ($firstca_test_max)", 1, 0, 'L', 1);
    $pdf->Cell(16, 4, "CA2 ($secondca_test_max)", 1, 0, 'L', 1);
    $pdf->Cell(16, 4, "CA3 ($thirdca_test_max)", 1, 0, 'L', 1);
    $pdf->Cell(18, 4, "Exam ($exam_max)", 1, 0, 'L', 1);
    $pdf->Cell(18, 4, 'Total (100)', 1, 0, 'L', 1);
    $pdf->Cell(23, 4, '1st Term (100) %', 1, 0, 'L', 1);
    $pdf->Cell(10, 4, 'AVR.', 1, 0, 'L', 1);
    $pdf->Cell(17, 4, 'SUB POS', 1, 0, 'L', 1);
    $pdf->Cell(12, 4, 'Grade', 1, 0, 'L', 1);



    $total_first_ca_score = '';
    $total_second_ca_score  = '';
    $total_third_ca_score  = '';
    $total_exam_score = '';
    $total_subjects_offered = '';


    foreach ($result as $report_details) {
        $subject_offered = $report_details->subject;
        $first_ca_score = $report_details->first_ca;
        $second_ca_score = $report_details->second_ca;
        $third_ca_score = $report_details->third_ca;
        $exam_score = $report_details->exam_score;
        $total_score = $exam_score + $first_ca_score + $second_ca_score + $third_ca_score;
        $total_subjects_offered = $rowcount;
        $current_exam_year = $report_details->school_year;

        //start fetch of second term exam details
        //fetch total first CA score



        $sql = "SELECT SUM(first_ca) FROM results WHERE name_of_student = :student AND first_ca != :zero 
                 AND exam_score != :zero AND second_ca != :zero AND third_ca != :zero
                 AND class =:class AND exam=:exam AND section =:section AND current_school_session =:year";
        $conn->query($sql);
        $conn->bind(":student", $name);
        $conn->bind(":zero", $zero);
        $conn->bind(":class", $class);
        $conn->bind(":exam", $exam);
        $conn->bind(":section", $section);
        $conn->bind(":year", $year);

        $total_first_ca_score = $conn->fetchColumn();

        //fetch total second CA score

        $sql = "SELECT SUM(second_ca) FROM results WHERE name_of_student = :student AND first_ca != :zero
                AND exam_score != :zero AND second_ca != :zero AND third_ca != :zero
                AND class =:class AND exam=:exam AND section =:section AND current_school_session =:year";
        $conn->query($sql);
        $conn->bind(":student", $name);
        $conn->bind(":zero", $zero);
        $conn->bind(":class", $class);
        $conn->bind(":exam", $exam);
        $conn->bind(":section", $section);
        $conn->bind(":year", $year);

        $total_second_ca_score = $conn->fetchColumn();

        //fetch total third CA score

        $sql = "SELECT SUM(third_ca) FROM results WHERE name_of_student = :student AND first_ca != :zero
                AND exam_score != :zero AND second_ca != :zero AND third_ca != :zero
                AND class =:class AND exam=:exam AND section =:section AND current_school_session =:year";
        $conn->query($sql);
        $conn->bind(":student", $name);
        $conn->bind(":zero", $zero);
        $conn->bind(":class", $class);
        $conn->bind(":exam", $exam);
        $conn->bind(":section", $section);
        $conn->bind(":year", $year);

        $total_third_ca_score = $conn->fetchColumn();


        //fetch total exam score
        $sql = "SELECT SUM(exam_score) FROM results WHERE name_of_student = :student AND first_ca != :zero 
                AND second_ca != :zero AND exam_score != :zero AND third_ca != :zero
                AND class =:class AND exam=:exam AND section =:section AND current_school_session =:year";
        $conn->query($sql);
        $conn->bind(":student", $name);
        $conn->bind(":zero", $zero);
        $conn->bind(":class", $class);
        $conn->bind(":exam", $exam);
        $conn->bind(":section", $section);
        $conn->bind(":year", $year);

        $total_exam_score = $conn->fetchColumn();

        //fetch student subject position start
        $sql = "SELECT position FROM subject_positions WHERE name=:name AND class=:class AND section =:section
            AND exam =:exam AND exam_year =:exam_year AND subject =:subject";
        $conn->query($sql);
        $conn->bind(":name", $name);
        $conn->bind(":class", $class);
        $conn->bind(":exam", $exam);
        $conn->bind(":section", $section);
        $conn->bind(":exam_year", $current_exam_year);
        $conn->bind(":subject", $subject_offered);
        $subject_position = $conn->fetchColumn();
        //fetch exam grades and remarks

        $sql = "SELECT * FROM grade WHERE :student_score BETWEEN mark_from AND mark_upto";
        $conn->query($sql);
        $conn->bind(":student_score", $total_score);
        $result = $conn->fetchSingle();
        $grade = $result->grade_name;
        $grade_title = $result->note;
        //show the values
        $pdf->Ln(5); //this will reduce the line height of each subject
//            $pdf->SetTextColor(14, 93,117);
        $pdf->Cell(50, 4, $subject_offered, 0, 0, "L");
        $pdf->Cell(16, 4, $first_ca_score ?: '-', 0, 0, "L");
        $pdf->Cell(16, 4, $second_ca_score ?: '-', 0, 0, "L");
        $pdf->Cell(18, 4, $third_ca_score ?: '-', 0, 0, "L");
        $pdf->Cell(17, 4, $exam_score ?: '-', 0, 0, "L");
        $pdf->Cell(22, 4, $total_score ?: '-', 0, 0, "L");

        $first_term = '1st term';
        $sql = "SELECT * FROM results WHERE name_of_student =:student AND first_ca != :zero 
             AND second_ca != :zero AND exam_score != :zero AND third_ca != :zero
            AND class =:class AND exam=:first_term AND section =:section AND current_school_session =:year AND subject = :subject_offered ORDER BY subject";
        $conn->query($sql);
        $conn->bind(":student", $name);
        $conn->bind(":zero", $zero);
        $conn->bind(":class", $class);
        $conn->bind(":first_term", $first_term);
        $conn->bind(":section", $section);
        $conn->bind(":year", $year);
        $conn->bind(":subject_offered", $subject_offered);
        $first_term_details = $conn->fetchMultiple();

        $first_term_total_score = '';
        $first_term_second_term_average = '';

        if(!empty($first_term_details)){
            foreach ($first_term_details as $fetched_info):
                $first_term_first_ca_score = $fetched_info->first_ca;
                $first_term_second_ca_score = $fetched_info->second_ca;
                $first_term_third_ca_score = $fetched_info->third_ca;
                $first_term_exam_score = $fetched_info->exam_score;
                $first_term_total_score =  $first_term_first_ca_score + $first_term_second_ca_score +  $first_term_third_ca_score + $first_term_exam_score;
                $first_term_second_term_average = ($total_score + $first_term_total_score) / 2;

                //fetch first, second ca and exam totals for first term

                $sql = "SELECT SUM(first_ca) FROM results WHERE name_of_student = :student AND first_ca != :zero AND exam_score != :zero 
                     AND second_ca != :zero AND third_ca != :zero
                   AND class =:class AND exam=:first_term AND section =:section AND current_school_session =:year";
                $conn->query($sql);
                $conn->bind(":student", $name);
                $conn->bind(":zero", $zero);
                $conn->bind(":class", $class);
                $conn->bind(":first_term", $first_term);
                $conn->bind(":section", $section);
                $conn->bind(":year", $year);

                $first_term_total_first_ca_score = $conn->fetchColumn();

                //fetch total second CA score

                $sql = "SELECT SUM(second_ca) FROM results WHERE name_of_student = :student AND first_ca != :zero AND exam_score != :zero
                    AND second_ca != :zero AND third_ca !=:zero
                    AND class =:class AND exam=:first_term  AND section =:section AND current_school_session =:year";
                $conn->query($sql);
                $conn->bind(":student", $name);
                $conn->bind(":zero", $zero);
                $conn->bind(":class", $class);
                $conn->bind(":first_term", $first_term);
                $conn->bind(":section", $section);
                $conn->bind(":year", $year);

                $first_term_total_second_ca_score = $conn->fetchColumn();

                //fetch total third CA score

                $sql = "SELECT SUM(third_ca) FROM results WHERE name_of_student = :student AND first_ca != :zero AND exam_score != :zero
                    AND second_ca != :zero AND third_ca !=:zero
                    AND class =:class AND exam=:first_term  AND section =:section AND current_school_session =:year";
                $conn->query($sql);
                $conn->bind(":student", $name);
                $conn->bind(":zero", $zero);
                $conn->bind(":class", $class);
                $conn->bind(":first_term", $first_term);
                $conn->bind(":section", $section);
                $conn->bind(":year", $year);

                $first_term_total_third_ca_score = $conn->fetchColumn();

                //fetch total exam score
                $sql = "SELECT SUM(exam_score) FROM results WHERE name_of_student = :student AND first_ca != :zero 
                    AND second_ca != :zero AND exam_score != :zero AND third_ca !=:zero
                     AND class =:class AND exam=:first_term AND section =:section AND current_school_session =:year";
                $conn->query($sql);
                $conn->bind(":student", $name);
                $conn->bind(":zero", $zero);
                $conn->bind(":class", $class);
                $conn->bind(":first_term", $first_term);
                $conn->bind(":section", $section);
                $conn->bind(":year", $year);

                $first_term_total_exam_score = $conn->fetchColumn();
                //end  fetch first term exam details

            endforeach; // end first term loop
        }else{
            $first_term_total_score = '-';
            $first_term_second_term_average = '-';
        }

        //show subject position
        $pdf->Cell(18, 4, $first_term_total_score, 0, 0, "L");
        $pdf->Cell(12, 3, $first_term_second_term_average, 0, 0, "L");
        $pdf->Cell(15, 4, $subject_position ?: '-', 0, 0, "L");
        $pdf->Cell(15, 4, $grade ?: '-', 0, 0, "L");

    }
    $pdf->Ln(4);
    $pdf->MultiCell(194, 7, '____________________________________________________________________________________________________________', 0, 'L', 0, 1, '', '', true);
    $pdf->SetTextColor(14, 93,117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(116, 4, 'Total', 0, 0, "L");
    // $pdf->Cell(14, 4, $total_first_ca_score, 0, 0, "L");
    // $pdf->Cell(10, 4, $total_second_ca_score, 0, 0, "L");
    //$pdf->Cell(10, 4, $total_third_ca_score, 0, 0, "L");
    //$pdf->Cell(11, 4, $total_exam_score, 0, 0, "L");
    $pdf->Cell(22, 4, $total_exam_score + $total_first_ca_score + $total_second_ca_score + $total_third_ca_score, 0, 0, "L");
    $pdf->Cell(20, 4,  $first_term_total_first_ca_score  +  $first_term_total_second_ca_score + $first_term_total_third_ca_score + $first_term_total_exam_score , 0, 0, "L");



    $total_marks_obtained = $total_exam_score + $total_first_ca_score + $total_second_ca_score + $total_third_ca_score;
    global $total_marks_obtained;
    $pdf->Ln(8);
    $mark_average = round(($total_exam_score + $total_first_ca_score + $total_second_ca_score +  $total_third_ca_score) / $total_subjects_offered, 2);
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(21, 4, "Percentage: ", 0, 0, "L");
    $pdf->SetFont('times', 'N', '11');
    $pdf->Cell(40, 4, "$mark_average %", 0, 0, "L");

    //fetch class position visibility status

    $sql = "SELECT show_position FROM classes WHERE name =:name";
    $conn->query($sql);
    $conn->bind(":name", $class);
    $show_hide_position = $conn->fetchColumn();

    //fetch student position
    $sql = "SELECT position FROM student_class_positions WHERE name=:name AND class=:class AND section =:section
            AND exam =:exam AND exam_year =:exam_year";
    $conn->query($sql);
    $conn->bind(":name", $name);
    $conn->bind(":class", $class);
    $conn->bind(":exam", $exam);
    $conn->bind(":section", $section);
    $conn->bind(":exam_year", $current_exam_year);
    $student_position = $conn->fetchColumn();

    if($show_hide_position == 'yes'){
        //position in class start
        $pdf->SetTextColor(0, 0,0);
        $pdf->SetFont('times', 'B', '11');
        $pdf->Cell(30, 4, "Position in class: ", 0, 0, "L");
        $pdf->SetFont('times', 'N', '11');
        $pdf->Cell(40, 4, "$student_position" . " Out of ". $total_students_in_class . " total students", 0, 0, "L");
        //position in class end
    }

    $approximate_percentage = round($mark_average);

    //fetch comments if it exists
    $sql = "SELECT * FROM comments WHERE :student_percentage BETWEEN percent_from AND percent_upto";
    $conn->query($sql);
    $conn->bind(":student_percentage", $approximate_percentage);
    $result = $conn->fetchSingle();
    $teacher_comment = $result->class_teacher_comment;
    $principal_comment = $result->principal_comment;

    //add school stamp

    if(!empty($school_stamp)){
        //add school stamp image
        $url_bg = $admin_url."upload/$school_stamp";
        $pdf->Image("$url_bg", 170, '', 25);
    }



    $pdf->Ln(6);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(47, 4, "Class Teacher's Comment: ________________________________________________", 0, 0, "L");
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 4, "$teacher_comment", 0, 0, "L");

    //$pdf->Ln(6);
    //$pdf->SetFont('times', 'B', '11');
    //$pdf->Cell(40, 4, "Class Teacher's Name: ", 0, 0, "L");
    //$pdf->SetTextColor(14, 93,117);
    //$pdf->SetFont('times', 'N', '10');
    //$pdf->Cell(40, 5, "$teacher_name", 0, 0, "L");

    $pdf->Ln(6);
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(40, 4, "Principal's Comment: ____________________________________________________", 0, 0, "L");
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 4, "$principal_comment", 0, 0, "L");

    $pdf->Ln(6);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(40, 4, "Principal's Name: ", 0, 0, "L");
    $pdf->SetTextColor(14, 93,117);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 4, "$principal_name", 0, 0, "L");

    //fetch students' attendance details
    $sql = "SELECT * FROM attendance WHERE student_name=:name AND exam =:exam AND exam_year =:exam_year";
    $conn->query($sql);
    $conn->bind(":name", $name);
    $conn->bind(":exam", $exam);
    $conn->bind(":exam_year", $current_exam_year);
    $details = $conn->fetchSingle();
    $attendance = $details->attendance;

    $pdf->Ln(7);
    $pdf->SetTextColor(14, 93,117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(21, 5, "Attendance:", 0, 0, 'L', 0);
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(20, 6, "$attendance times", 0, 0, 'L', 0);


    $sql = "SELECT * FROM general_settings";
    $conn->query($sql);
    $result_set = $conn->fetchSingle();
    $next_term_begins =$result_set->next_term_begins;
    $next_term_begins1 = date_create($next_term_begins);
    $new_next_term_begins = date_format($next_term_begins1, 'F d, Y');
    $no_times_school_open = $result_set->no_times_school_open;
    $footer = $result_set->footer;
//        $pdf->Ln(10);
    $pdf->SetTextColor(14, 93,117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(30, 5, "Next term begins:", 0, 0, 'L', 0);
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(37, 5, "$new_next_term_begins", 0, 0, 'L', 0);
    $pdf->SetTextColor(14, 93,117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(45, 5, "No of times school opened:", 0, 0, 'L', 0);
    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(10, 6, "$no_times_school_open times", 0, 0, 'L', 0);

    $pdf->Ln(7);
    $pdf->SetTextColor(14, 93,117);
    $pdf->SetFont('times', 'N', '11');
    $pdf->Cell(33, 3, 'Meaning of Grades:', 0, 0, 'L');
    //fetch all available grades from the system
    $sql = "SELECT * FROM grade ORDER BY mark_from DESC LIMIT  0, 8";
    $conn->query($sql);
    $grade_result = $conn->fetchMultiple();


    foreach ($grade_result as $student_grading){
        $mark_upto = $student_grading->mark_upto;
        $mark_from = $student_grading->mark_from;
        $student_grade = $student_grading->grade_name;
        $pdf->SetTextColor(0, 0,0);
        $pdf->SetFont('times', 'N', '10');
        $pdf->Cell(20, 3, "$mark_from - $mark_upto($student_grade) ", 0, 0, "L");
    }

    $sql = "SELECT * FROM grade ORDER BY mark_from DESC LIMIT  8, 6";
    $conn->query($sql);
    $grade_result = $conn->fetchMultiple();
    $pdf->Ln(4);
    foreach ($grade_result as $student_grading){
        $mark_upto = $student_grading->mark_upto;
        $mark_from = $student_grading->mark_from;
        $student_grade = $student_grading->grade_name;
        $pdf->SetTextColor(0, 0,0);
        $pdf->SetFont('times', 'N', '10');
        $pdf->Cell(15, 7, "", 0, 0, "L");
        $pdf->Cell(4, 5, "$mark_from - $mark_upto($student_grade) ", 0, 0, "L");
    }

    //student weight  and more ....
    $sql = "SELECT * FROM student WHERE name =:name";
    $conn->query($sql);
    $conn->bind(":name", $name);
    $row_count = $conn->rowCount();

    if($row_count > 0){
        $student_info = $conn->fetchSingle();
        $dob = $student_info->dob;
        $dob1 = date_create($dob);
        $new_date = date_format($dob1, 'F, d Y');
        $weight = $student_info->weight;
        $height = $student_info->height;
        $pdf->Ln(5);
        if(!empty($weight)){
            $pdf->SetFont('times', 'N', '11');
            $pdf->Cell(15, 5, "Weight: ", 0, 0, "L");
            $pdf->SetTextColor(14, 93,117);
            $pdf->SetFont('times', 'N', '10');
            $pdf->Cell(13, 5, "$weight kg", 0, 0, "L");
        }
        if(!empty($height)){
            $pdf->SetTextColor(0, 0,0);
            $pdf->SetFont('times', 'N', '11');
            $pdf->Cell(13, 6, "Height: ", 0, 0, "L");
            $pdf->SetTextColor(14, 93,117);
            $pdf->SetFont('times', 'N', '10');
            $pdf->Cell(13, 6, "$height m", 0, 0, "L");
        }

        if(!empty($new_date)){
            // $pdf->SetTextColor(0, 0,0);
            // $pdf->SetFont('times', 'N', '11');
            //$pdf->Cell(23, 6, "Date of Birth: ", 0, 0, "L");
            //$pdf->SetTextColor(14, 93,117);
            //$pdf->SetFont('times', 'N', '10');
            // $pdf->Cell(30, 6, "$new_date", 0, 0, "L");
        }
    }

    //result analysis

    $pdf->SetTextColor(0, 0,0);
    $pdf->SetFont('times', 'N', '11');
    $pdf->Cell(28, 5, "Result Analysis: ", 0, 0, "L");
    $pass_percent = 60;
    if($mark_average >= $pass_percent){
        $pdf->SetTextColor(0, 128, 0);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(16, 5, "Passed", 0, 0, "L");
    }else{
        $pdf->SetTextColor(255, 0, 0);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(16, 5, "Failed", 0, 0, "L");
    }

    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(50, 5, "(based on 60% and above)", 0, 0, "L");
    $pdf->Ln(5);

    //insert psychomotor skills
    $sql = "SELECT * FROM students_psychomotor WHERE student_name =:student_name AND section =:section AND 
                        class =:class AND exam =:exam AND exam_year =:exam_year";
    $conn->query($sql);
    $conn->bind(':section', $section);
    $conn->bind(':class', $class);
    $conn->bind(':exam', $exam);
    $conn->bind(':exam_year', $current_exam_year);
    $conn->bind(':student_name', $name);
    $row_count = $conn->rowCount();

    if($row_count > 0){
        $result = $conn->fetchMultiple();
        $pdf->Ln(5);
        $pdf->SetTextColor(255, 0, 0);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(21, 4, "PSYCHOMOTOR SKILLS ", 0, 1, "L");
        $pdf->SetTextColor(255, 244, 255);
        $pdf->SetFont('times', 'B', '10');
        $pdf->SetFillColor(224, 210, 100);
        $pdf->Cell(50, 4, 'Name', 0, 0, 'L', 1);
        $pdf->Cell(18, 4, 'Value', 0, 0, 'L', 1);
        foreach($result as $data){
            $pdf->Ln(4);
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('helvetica', 'N', '8');
            $pdf->Cell(50, 4, $data->psychomotor_name, 1, 0, 'L', 0);
            $pdf->Cell(18, 4, $data->psychomotor_value, 1, 0, 'L', 0);
        }
        $pdf->Ln(5);
        $pdf->Cell(15, 4, 'Key: 1(Poor), 2(Fair), 3(Good), 4(V.Good), 5(Excellent)', 0, 0, 'L', 0);

    }

    //insert psychomotor skills
    $sql = "SELECT * FROM students_cognitive_area WHERE student_name =:student_name AND section =:section AND 
                        class =:class AND exam =:exam AND exam_year =:exam_year";
    $conn->query($sql);
    $conn->bind(':section', $section);
    $conn->bind(':class', $class);
    $conn->bind(':exam', $exam);
    $conn->bind(':exam_year', $current_exam_year);
    $conn->bind(':student_name', $name);
    $row_count = $conn->rowCount();

    if($row_count > 0){
        $result = $conn->fetchMultiple();
        $pdf->Ln(-33);
        $pdf->SetX(96);
        $pdf->SetTextColor(100, 10, 90);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(21, 4, "AFFECTIVE DOMAIN", 0, 1, "L");
        $pdf->SetX(96);
        $pdf->SetTextColor(255, 244, 255);
        $pdf->SetFont('times', 'B', '10');
        $pdf->SetFillColor(24, 60, 100);
        $pdf->Cell(50, 4, 'Name', 0, 0, 'L', 1);
        $pdf->Cell(18, 4, 'Value', 0, 0, 'L', 1);
        foreach($result as $data){
            $pdf->Ln(4);
            $pdf->SetX(96);
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('helvetica', 'N', '8');
            $pdf->Cell(50, 4, $data->cognitive_name, 1, 0, 'L', 0);
            $pdf->Cell(18, 4, $data->cognitive_value, 1, 0, 'L', 0);
        }
    }



//        if(!empty($school_stamp)){
//            //add school stamp image
//            $url_bg = $admin_url."upload/$school_stamp";
//            $pdf->Image("$url_bg", 85, 238, 30);
//        }
//


}else{
    //criteria was not meant, return back to homepage
    $admin_url = $conn->base_url();
    ?>
    <script>
        alert('Student results details have not been added yet for the selected term. Pls add marks before viewing terminal report.');
    </script>
    <meta http-equiv="refresh" content="0; <?php echo $admin_url; ?>">
    <?php
}