php - 显示从休假日期开始的日期以及从和结束日期

Question

这个问题已经有答案了:

Monthly report for leave apply (2 个回答)

已关闭 4 年前。

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

如果员工选择一个休假日期，则会将日期值存储到 Leave_Date；如果员工选择多个日期，则会存储起始日期和截止日期的值。

输出页面我想要员工姓名、休假天数和休假日期。休假日期包含 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)

Answer 1

无法帮助您处理 php，但 mysql 查询可以解决问题。

DROP TABLE IF EXISTS T;
CREATE TABLE T
(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(10),LEAVE_DATE DATE, LEAVE_FROM DATE, LEAVE_TO DATE);
INSERT INTO T (NAME,LEAVE_DATE,LEAVE_FROM,LEAVE_TO) VALUES
('A','2019-05-30','2019-05-30','2019-06-02'),
('A','2019-06-05',NULL,NULL),
('A','2019-06-06',NULL,NULL),
('A','2019-06-30','2019-06-30','2019-07-11'),
('B','2019-05-30','2019-05-30','2019-07-11'),
('C','2019-05-11','2019-05-11','2019-05-12')
;

SELECT NAME,
         SUM(CASE WHEN LEAVE_FROM IS NULL THEN 1 
                ELSE DATEDIFF(IF(LEAVE_TO > '2019-06-30','2019-06-30',LEAVE_TO),IF(LEAVE_FROM < '2019-06-01','2019-06-01',LEAVE_FROM)) + 1 
              END) LEAVEDAYS,
         GROUP_CONCAT(CASE WHEN LEAVE_FROM IS NULL THEN LEAVE_DATE 
                            ELSE CONCAT(IF(LEAVE_FROM < '2019-06-01','<<2019-06-01',LEAVE_FROM),' & ',IF(LEAVE_TO > '2019-06-30','2019-06-30>>',LEAVE_TO)) 
                            END) AS LEAVEDATES 
FROM T
WHERE (LEAVE_DATE BETWEEN '2019-06-01' AND '2019-06-30') OR
        (LEAVE_FROM BETWEEN '2019-06-01' AND '2019-06-30') OR
        (LEAVE_TO   BETWEEN '2019-06-01' AND '2019-06-30') OR
        (LEAVE_FROM < '2019-06-01' AND LEAVE_TO > '2019-06-30')
GROUP BY NAME
ORDER BY ID;

请注意对休假日期顶部和底部的调整，以确保我们只计算该期间内的日期。另请注意，我已在休假日期中添加了指示符(<< 或 >>)，以指示在所需期间开始日期之前开始或在所需期间结束日期之后延长的休假日期。

+------+-----------+---------------------------------------------------------------------------+
| NAME | LEAVEDAYS | LEAVEDATES                                                                |
+------+-----------+---------------------------------------------------------------------------+
| A    |         5 | <<2019-06-01 & 2019-06-02,2019-06-05,2019-06-06,2019-06-30 & 2019-06-30>> |
| B    |        30 | <<2019-06-01 & 2019-06-30>>                                               |
+------+-----------+---------------------------------------------------------------------------+
2 rows in set (0.08 sec)