休假申请月报

2021-12-25 00:00:00 php pdo mysqli

我有两个表，一个是 tblemployee，其中包含员工姓名、员工 ID，另一个表 tblleaves 包含 empid、Leave_Date、fromDate、toDate、Description.

I have two tables one is tblemployee having employee name, employee id and another table tblleaves having empid,Leave_Date, fromDate, toDate, Description.

如果员工选择一个休假日期，它会将日期值存储到 Leave_Date，如果员工选择多个日期，它会存储从日期和截止日期的值.

If employee choose one date leave it stores the date value to Leave_Date and if employee choose multiple dates it store value of from date and to date.

在输出页面中，我想要一个员工姓名、休假天数和休假日期.休假日期包含 Leave_date、FromDate 和 ToDate 中的日期.

In the output page I want an employee name, Leave Days and Leave Dates. Leave Dates have dates from Leave_date, FromDate and ToDate.

     <?php 


        if(isset($_POST['apply'])){

        $ym=$_POST['month'];
        list($Year, $Month) = explode("-", "$ym", 2);

        $sql = "SELECT 
       tblemployees.FirstName,
       tblemployees.LastName,
       count(tblleaves.empid) as Leave_Days,
       GROUP_CONCAT( tblleaves.Leave_Date SEPARATOR ', ' ) AS leave_dates
    FROM
       tblleaves
       JOIN tblemployees
          ON tblleaves.empid = tblemployees.id
    WHERE YEAR(Leave_Date) = $Year
       AND MONTH(Leave_Date) = $Month
    GROUP BY tblemployees.EmpId";

        $query = $dbh -> prepare($sql);
        $query->execute();
        $results=$query->fetchAll(PDO::FETCH_OBJ);

        $cnt=1;
        if($query->rowCount() > 0)
        {
        foreach($results as $result)
        {               ?>  
          <tr>
            <td> <?php echo htmlentities($cnt);?></td>
              <td><?php echo htmlentities($result->FirstName);?>&nbsp;<?php echo htmlentities($result->LastName);?></td>
               <td><?php echo htmlentities($result->Leave_Days);
     ?></td>
<td><?php echo htmlentities($result->leave_dates);

    ?></td><?php $cnt++;}}}?>
</tr>
</tbody>
</table>

我希望页面的输出是

employee name     Leave Days      Leave Dates 
KrishnanR            3              12-06-2019, 13-06-2019, 14-06-2019
                                     (FromDate and ToDate)
PrakashR             1              12-06-2019
                                     (Leave_Date)

SelvaK               3        12-06-2019,13-06-2019&14-06-2019,|  14-06-2019
                                      (FromDate and ToDate) |  (Leave_Date)

推荐答案

考虑下面这个粗略的例子...

Consider the following crude example...

示例模式(借用并改编自 P.Salmon):

Sample schema (borrowed and adapted from P.Salmon):

DROP TABLE IF EXISTS employee_leave;
CREATE TABLE employee_leave
(leave_id SERIAL PRIMARY KEY
,employee_id INT NOT NULL
,leave_from DATE NOT NULL
,leave_to DATE NOT NULL
);

INSERT INTO employee_leave
(employee_id
,leave_from
,leave_to
) VALUES
(11,'2019-05-30','2019-06-02'),
(11,'2019-06-05','2019-06-05'),
(11,'2019-06-06','2019-06-06'),
(11,'2019-06-30','2019-07-11'),
(12,'2019-05-30','2019-07-11'),
(13,'2019-05-11','2019-05-12');

示例代码:

<?php

include('path/to/connection/stateme.nts');

$query = "
SELECT employee_id
     , leave_from
     , leave_to
     , datediff(leave_to,leave_from)+1 days
  FROM employee_leave
 ORDER
    BY employee_id
     , leave_from; -- ORDER BY is not strictly necessary, as the ordering can be done in presentation code.
";

$result = mysqli_query($conn,$query);

$array = array();

while($row = mysqli_fetch_assoc($result)){
  $array[] = $row;
}

$new_array = array();

foreach($array as $k=>$v){
  if($v['days']>1){
    $days = ' days'; } else { $days = ' day'; }
  $new_array[$v['employee_id']][] = $v['leave_from'].' - '.$v['leave_to'].' ('.$v['days'].$days.')';
}

print_r($new_array);
?>

使用上面的架构，此代码输出...

Using the schema above, this code outputs...

Array
(
    [11] => Array
        (
            [0] => 2019-05-30 - 2019-06-02 (4 days)
            [1] => 2019-06-05 - 2019-06-05 (1 day)
            [2] => 2019-06-06 - 2019-06-06 (1 day)
            [3] => 2019-06-30 - 2019-07-11 (12 days)
        )

    [12] => Array
        (
            [0] => 2019-05-30 - 2019-07-11 (43 days)
        )

    [13] => Array
        (
            [0] => 2019-05-11 - 2019-05-12 (2 days)
        )

)

请注意，此结果将所有天视为工作日

Note that this result considers all days as working days

相关文章