时间戳

首页 > 解决方案 > 申请休假月度报告

php - 申请休假月度报告

问题描述

我有两个表，一个是具有员工姓名、员工 ID 的 tblemployee，另一个具有 empid、Leave_Date、fromDate、toDate、Description 的表 tblleaves。

如果员工选择一个日期休假，则将日期值存储到 Leave_Date，如果员工选择多个日期，则将日期值存储到日期和日期。

在输出页面中，我想要一个员工姓名、休假天数和休假日期。Leave Dates 的日期包括 Leave_date、FromDate 和 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)

标签： phpmysqlipdo

解决方案

根据您的要求更新了您的代码，没有对其进行测试。试一试吧。

<?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,
               IF( t2.FromDate IS NOT NULL AND t2.ToDate IS NOT NULL , 
                    CONCAT(t2.FromDate, ',', t2.ToDate, ',' ,t2.Leave_Date), 
                    t2.Leave_Date) 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)
{               
$leavedates = explode(',', $result->Leave_Dates);
$period = new DatePeriod(
     new DateTime($leavedates[0]),
     new DateInterval('P1D'),
     new DateTime($leavedates[1])
);
$listofleaves = [];
foreach ($period as $key => $value) {
    $listofleaves[] = $value->format('Y-m-d');       
}
$listofleaves[] = $leavedates[2];
    ?>  
  <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(implode(',' , $listofleaves);


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

推荐阅读