ESH/pending_medical_examination_list_dynamic_excel.php

<?php
include('includes/config/config.php');
include_once('includes/functions.php');
header('Content-Type: application/force-download');
header('Content-disposition: attachment; filename=pending_medical_examination_excel.xls');
?>

<center>

    <?php
    $checkup_type_id = $_REQUEST['checkup_type_id_m'];
    error_log("checkup_type_id " . $checkup_type_id);
    $start_date_excel = $_REQUEST['start_date_excel'];

    $end_date_excel = $_REQUEST['end_date_excel'];

    $dates =  "and date(checkup_date)  >= '" . $start_date_excel . "' and date(checkup_date)  <=  '" . $end_date_excel . "'";

    $sql210 = "select * from checkup_type where checkup_type_id='" . $checkup_type_id . "'";
    error_log("getting checkup type details : ̥" . $sql210);
    $result210 = @mysqli_query($conn, $sql210);
    while ($row210 = mysqli_fetch_array($result210)) {
        $checkup = $row210['checkup_form_section_ids'];
    }
    $sql = 'select distinct section_id,section_name,count(*) as count from (select* from checkup_form_section where Status="Active"and section_id in (' . $checkup . ') ) as c left JOIN checkup_parameter  on checkup_parameter.checkup_form_section_id=c.section_id and enabled="Y" group by section_id ';
    error_log("sql 1st" . $sql);
    $result = mysqli_query($conn, $sql);


    $sql1 = 'select parameter_value,input_type,section_id,column_order,checkup_parameter_id,parameter_name,column_name from (select checkup_form_section_id,column_order,checkup_parameter_id ,parameter_name,input_type,column_name,parameter_value from checkup_parameter WHERE enabled="Y" and checkup_form_section_id in (' . $checkup . ')) as c inner JOIN checkup_form_section on checkup_form_section.section_id=c.checkup_form_section_id and Status="Active"  order by checkup_form_section_id,column_order';
    $result1 = mysqli_query($conn, $sql1);

    error_log("heading query " . $sql1);


    $sql2 = "select c.*,p.* from checkup_form c left join patient_master p on c.emp_id = p.id where c.checkup_type_id='" . $checkup_type_id . "' and (c.current_status='MDP' or c.current_status='MDA') $dates";
    $result2 = mysqli_query($conn, $result2);
    error_log("Fetched result Value " . $sql2);
    ?>
    <table border="1">
        <thead>
            <tr>
                <th rowspan="2">Examination date </th>
                <th rowspan="2">Examination Type</th>
                <th colspan="11">Patient details</th>
                <?php
                while ($row1 = mysqli_fetch_array($result)) {
                ?>
                    <th colspan="<?= $row1['count'] ?>">
                        <?= $row1['section_name'] ?>
                    </th>
                <?php
                }
                ?>
            </tr>

            <tr>
                <th>Employee Code</th>
                <th>Patient name</th>
                <th>Father name</th>
                <th>DOB</th>
                <th>Ticket No</th>
                <th>Gender</th>
                <th>Blood Group</th>
                <th>Designation</th>
                <th>Department</th>
                <th>Adhaar Number</th>
                <th>Phone</th>
                <?php
                mysqli_free_result($result1);
                $result1 = mysqli_query($conn, $sql1);
                while ($row2 = mysqli_fetch_array($result1)) {
                ?>
                    <th><?= $row2['parameter_name'] ?></th>
                <?php
                }

                ?>
            </tr>
        </thead>
        <?php
        mysqli_free_result($result2);
        $result2 = mysqli_query($conn, $sql2);
        while ($row3 = mysqli_fetch_assoc($result2)) {
        ?>
            <tr>
                <td><?= $row3['checkup_date'] ?></td>
                <td><?= getFieldFromTable('checkup_type_name', 'checkup_type', 'checkup_type_id', $checkup_type_id) ?></td>
                <td><?= $row3['emp_code'] ?></td>
                <td><?= $row3['patient_name'] ?></td>
                <td><?= $row3['father_name'] ?></td>
                <td><?= $row3['dob'] ?></td>
                <td><?= $row3['ticket_no']?></td>
                <td><?= $row3['gender'] ?></td>
                <td><?= $row3['blood_group'] ?></td>
                <td><?= getFieldFromTable('designation_name', 'designation', 'designation_id', $row3['designation_id']) ?></td>
                <td><?= getFieldFromTable('dept_name', 'department', 'dept_id', $row3['dept_id']) ?></td>
                <td><?= $row3['aadhar_no'] ?></td>
                <td><?= $row3['primary_phone'] ?></td>

                <?php
                $data = "select distinct cf.checkup_form_key,cf.checkup_form_value,cf.checkup_form_id,c.* ,cp.input_type  from checkup_form c left join checkup_form_key_value cf on c.checkup_id=cf.checkup_form_id left join checkup_parameter cp on cp.`column_name`=cf.checkup_form_key where checkup_form_section_id in (" . $checkup . ") and c.checkup_type_id='" . $checkup_type_id . "' and emp_id='" . $row3['emp_id'] . "' and c.checkup_id='" . $row3['checkup_id'] . "' and enabled='Y' $dates order by checkup_form_section_id,column_order";
                error_log("data query " . $data);
                $result3 = mysqli_query($conn, $data);
                while ($row_data = mysqli_fetch_assoc($result3)) {
                ?>
                    <?php
                    if ($row_data['input_type'] == 'select') {
                    ?>
                        <td><?= getFieldFromTable('parameter_value_name', 'checkup_parameter_value', 'parameter_value_id', $row_data['checkup_form_value']) ?></td>
                    <?php
                    } else if ($row_data['input_type'] == 'multiselect') {
                    ?>
                        <td><?= getCommaSeperatedValuesForInClause("select parameter_value_name from checkup_parameter_value", 'parameter_value_id', $row_data['checkup_form_value']) ?></td>
                    <?php
                    } else {
                    ?>
                        <td>
                            <?= $row_data['checkup_form_value'] ?>
                        </td>
                <?php
                    }
                }
                ?>
            </tr>
        <?php
        }
        ?>
    </table>
</center>