<html>
    <head>
        <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/1.12.0/js/jquery.dataTables.min.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js"></script>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js"></script>
        <link href="https://cdn.datatables.net/1.12.0/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css"/>
        <link href="https://cdn.datatables.net/buttons/2.2.3/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css"/>
    </head>
    <body>
        <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Applicant ID</th>
                <th>Registered On</th>
                <th>Fname</th>
                <th>Mname</th>
                <th>Lname</th>
                <th>Gender</th>
                <th>DOB</th>
                <th>Age</th>
                <th>Nationality</th>
                <th>National ID</th>
                <th>Region</th>
                <th>Wilayat</th>
                <th>Other Nationality</th>
                <th>Passport No</th>
                <th>Present Country</th>
                <th>GSM Code</th>
                <th>GSM</th>
                <th>Email</th>
                <th>Qualification</th>
                <th>Discipline</th>
                <th>Other Discipline</th>
                <th>University</th>
                <th>Institute</th>
                <th>Current Employer</th>
                <th>Current Position</th>
                <th>Salary</th>
                <th>Availability</th>
                <th>Experience</th>
                <th>Duplicate</th>
                <th>Position Applying</th>
                <th>Current Status</th>
                <th>Current Salary</th>
                <th>Currency Type</th>
                <th>Activity Remarks</th>
            </tr>
        </thead>
        <tbody>
            <?php
                include('../dbinfo.php');

                $rsCandidate = mysqli_query($conn,"SELECT a.*, b.joborder_id, b.status, b.remarks, b.final_comment2 FROM candidate a, candidate_joborder b WHERE a.candidate_id=b.candidate_id AND a.site_id=1 AND a.site_id=b.site_id AND b.joborder_id!=0 AND a.date_created NOT LIKE '0000%' order by a.candidate_id desc");

                $ref = 0;
                while($rwCandidate = mysqli_fetch_array($rsCandidate)){
                    $candidate_id = $rwCandidate['candidate_id'];
                    $joborder_id = $rwCandidate['joborder_id'];

                    $gen = $rwCandidate['gender'];
                    if($gen ==1){
                        $gender = 'Male';
                    }else if($gen ==2){
                        $gender = 'Female';
                    }

                    $dup_chk = $rwCandidate['dup_chk'];
                    if($dup_chk == 1){
                        $dupchk = 'Duplicate';
                    }else {
                        $dupchk = '';
                    }

                    $univ = $rwCandidate['university'];
                    if($univ ==1){
                        $univers = 'Omani University';
                    }else if($univ ==2){
                        $univers = 'Other University';
                    }   

                    $natio = $rwCandidate['nationality'];
                    $rsNat = mysqli_query($conn,"SELECT * FROM cm_nationality WHERE nat_code='$natio'");
                    $rwNat = mysqli_fetch_array($rsNat);
                    $nati = $rwNat['nat_name'];

                    if($natio == 249){
                        $national_id = $rwCandidate['national_id'];
                        $regi = $rwCandidate['region'];
                        $wila = $rwCandidate['wilayat'];
                        $passport = '';
                    }else{
                        $national_id = '';
                        $regi = '';
                        $wila = '';
                        $passport = $rwCandidate['passport'];
                    }

                    $o_nat =  $rwCandidate['o_nat'];
                    $disc = $rwCandidate['discipline'];
                    if($disc=='others'){
                        $discip = "Others";
                        $other_disc = $rwCandidate['o_disc'];
                    }else{
                        $discip = getDisciplineName($disc);
                        $other_disc = "";
                    }

                    $cand_status = $rwCandidate['status'];
                    $rsStatus = mysqli_query($conn,"SELECT * FROM candidate_joborder_status WHERE candidate_joborder_status_id='$cand_status' AND is_enabled=1");
                    $rwStatus = mysqli_fetch_assoc($rsStatus );
                    $status = $rwStatus['short_description'];

                    $rsAttendance = mysqli_query($conn,"SELECT * FROM assessment_attendance WHERE candidate_id='$candidate_id'");
                    $rwAttendance = mysqli_fetch_array($rsAttendance);

                    $dob = $rwCandidate['dob'];

                    $age = get_age($dob);
                    $cou = $rwCandidate['country'];
                    if($cou==166){
                        $countr = "Oman";
                    }else{
                        $countr = "Outside Oman";
                    }

                    $qua = $rwCandidate['qualification'];
                    $qual = getQualification($qua);
            ?>
            <tr>
                <td><?php echo $rwCandidate['applicant_id']; ?></td>
                <td><?php echo substr($rwCandidate['date_created'],0,10); ?></td>
                <td><?php echo $rwCandidate['first_name'] ?></td>
                <td><?php echo $rwCandidate['middle_name'] ?></td>
                <td><?php echo $rwCandidate['last_name']; ?></td>
                <td><?php echo $gender; ?></td>
                <td><?php echo $rwCandidate['dob']; ?></td>
                <td><?php echo $age; ?></td>
                <td><?php echo $nati; ?></td>
                <td><?php echo $national_id; ?></td>
                <td><?php echo getRegionName($regi); ?></td>
                <td><?php echo getWilayatName($wila); ?></td>
                <td><?php echo $o_nat; ?></td>
                <td><?php echo $passport; ?></td>
                <td><?php echo $countr; ?></td>
                <td><?php echo $rwCandidate['gsm_code']; ?></td>
                <td><?php echo $rwCandidate['gsm']; ?></td>
                <td><?php echo $rwCandidate['email1']; ?></td>
                <td><?php echo $qual; ?></td>
                <td><?php echo $discip; ?></td>
                <td><?php echo $other_disc; ?></td>
                <td><?php echo $univers; ?></td>
                <td><?php echo $rwCandidate['institute']; ?></td>
                <td><?php echo $rwCandidate['cur_emp']; ?></td>
                <td><?php echo $rwCandidate['cur_pos']; ?></td>
                <td><?php echo $rwCandidate['salary']; ?></td>
                <td><?php echo $rwCandidate['avail_join']; ?></td>
                <td><?php echo $fullexp; ?></td>
                <td><?php echo $dupchk; ?></td>
                <td><?php echo getJobName($joborder_id); ?></td>
                <td><?php echo $status; ?></td>
                <td><?php echo $rwCandidate['current_salary']; ?></td>
                <td><?php echo $rwCandidate['currency_type']; ?></td>
                <td><?php echo getActivity($joborder_id,$candidate_id); ?></td>
            </tr>
            <?php
                }
                $ref++;
            ?>
        </tbody>
        <tfoot>
            <tr>
                <th>Applicant ID</th>
                <th>Registered On</th>
                <th>Fname</th>
                <th>Mname</th>
                <th>Lname</th>
                <th>Gender</th>
                <th>DOB</th>
                <th>Age</th>
                <th>Nationality</th>
                <th>National ID</th>
                <th>Region</th>
                <th>Wilayat</th>
                <th>Other Nationality</th>
                <th>Passport No</th>
                <th>Present Country</th>
                <th>GSM Code</th>
                <th>GSM</th>
                <th>Email</th>
                <th>Qualification</th>
                <th>Discipline</th>
                <th>Other Discipline</th>
                <th>University</th>
                <th>Institute</th>
                <th>Current Employer</th>
                <th>Current Position</th>
                <th>Salary</th>
                <th>Availability</th>
                <th>Experience</th>
                <th>Duplicate</th>
                <th>Position Applying</th>
                <th>Current Status</th>
                <th>Current Salary</th>
                <th>Currency Type</th>
                <th>Activity Remarks</th>
            </tr>
        </tfoot>
    </table>
    <?php
        function getRegionName($id){
            include('../dbinfo.php');
            $rsReg = mysqli_query($conn,"SELECT * FROM cm_region WHERE id='$id'");
            $rwReg = mysqli_fetch_array($rsReg);
            $region = $rwReg['region'];
            return $region;
        }

        function getDisciplineName($id){
            include('../dbinfo.php');
            $rsDisc = mysqli_query($conn,"SELECT * FROM cm_discipline WHERE id='$id'");
            $rwDisc = mysqli_fetch_assoc($rsDisc);
            $discipline = $rwDisc['discipline'];
            return $discipline;
        }

        function getWilayatName($id){
            include('../dbinfo.php');
            $rsWila = mysqli_query($conn,"SELECT * FROM cm_wilayat WHERE id='$id'");
            $rwWila = mysqli_fetch_assoc($rsWila);
            $wilayat = $rwWila['wilayat'];
            return $wilayat;
        }

        function getJobName($id){
            include('../dbinfo.php');
            $rsJobOrderName = mysqli_query($conn,"SELECT * FROM joborder WHERE joborder_id='$id'");
            $rwJobOrderName = mysqli_fetch_assoc($rsJobOrderName);
            $jobordername = $rwJobOrderName['title'];
            return $jobordername;
        }

        function getCountryName($id){
            include('../dbinfo.php');
            $rsCountry = mysqli_query($conn,"SELECT * FROM cm_country WHERE country_id='$id'");
            $rwCountry = mysqli_fetch_assoc($rsCountry);
            $country = $rwCountry['short_name'];
            return $country;
        }

        function getQualification($id){
            include('../dbinfo.php');
            $rsQuali = mysqli_query($conn,"SELECT * FROM cm_qualification WHERE id='$id'");
            $rwQuali = mysqli_fetch_assoc($rsQuali);
            $quali = $rwQuali['quali'];
            return $quali;
        }

        function getActivity($joborder_id,$candidate_id){
            include('../dbinfo.php');
            $rsRemark = mysqli_query($conn,"SELECT * FROM activity WHERE joborder_id='$joborder_id' AND data_item_id='$candidate_id' AND site_id=1");
            while($rwRemark = mysqli_fetch_array($rsRemark)){
                $remark[] = $rwRemark['notes'];
            }
            $remarks = @implode("\n",$remark);
            return $remarks;
            }

        function get_age($birth_date){
            return floor((time() - strtotime($birth_date))/31556926);
        }
    ?>
    </body>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#example').DataTable( {
                dom: 'Bfrtip',
                buttons: [
                    'copyHtml5',
                    'excelHtml5',
                    'csvHtml5'
                ]
             } );
        } );
    </script>
</html>