php - 插入 MySQL 之前检查 MySQL 日期范围

Question

这不是重复问题，我在 fiddle 演示中的查询处理数据库中的重叠日期。我所追求的是一种在将日期插入 MySQL 之前在日期范围 $startdate 和 $enddate 上运行查询的方法。基本上我想检查每个员工在那一年的假期权利中是否还有足够的假期。

我尝试在插入 MYSQL 之前检查日期范围内的每个日期所在的年份。每个员工的每一年都在不同的一天开始

Eg. jon started on 2013-10-10 so his year is  
2013-10-10 to 2014-10-10 
2014-10-10 to 2015-10-10

目前我的查询检查它们已经插入到 MYSQL 中的日期 (h.date) e.startdate 从它们开始时每年计算。在插入日期之前，我应该检查 $startdate 和 $enddate 之间的每个日期。

我已经设置了一个 Fiddle 演示，当日期已经在数据库中时它可以工作

$startdate ='2015-10-05';
$enddate ='2015-10-20';

    SELECT 
            e.name AS Employee,
            CONCAT(
                CEIL(DATEDIFF(h.date, e.startdate)/365), 
                ' (',
                DATE_ADD(e.startdate, INTERVAL FLOOR(DATEDIFF(h.date, e.startdate)/365) YEAR), ' to ',        
                DATE_ADD(e.startdate, INTERVAL CEIL(DATEDIFF(h.date, e.startdate)/365) YEAR),
                ')'
                ) as Year,
            COUNT(h.date) AS Holidays_Taken,
            SUM(h.hours) AS Hours
        FROM employees AS e
        LEFT JOIN holidays_taken AS h ON e.id = h.empid
        WHERE e.id = 1
        GROUP BY Year

[Fiddle Demo]

希望有人能帮忙谢谢

谢谢，我忘了在员工每天的工作时间提到不同，所以我使用这个查询来插入假期每天的时间，例如。 jon (empId 1) 周一工作 10 小时，周二工作 6 小时，依此类推。

    $sql= "select  empId,
'".$holidayStart."' as date,
case dayname('".$holidayStart."')
    when 'Sunday' then Sun
    when 'Monday' then Mon
    when 'Tuesday' then Tue
    when 'Wednesday' then Wed
    when 'Thursday' then Thu
    when 'Friday' then Fri
    when 'Saturday' then Sat
else 0 end as hours
from employees where empId = '".$employee."'"  ;
 $result = $mysqli->query($sql);

然后是数据库

id   empid   Mon  Tue Wed Thu Fri Sat Sun
102    1      10   6   4   10  10  0   0

Answer 1

不确定我是否正确地解决了问题(我找不到问号 :-))。希望以下查询对您有所帮助:

SET @hoursPerDay := 8;
SET @startdate := DATE('2015-10-05');
SET @enddate   := DATE('2015-10-20');
SET @empid := 1;

SELECT years.*, holidays.*,
    GREATEST(holidays.startdate, years.yearStartDay) AS holidaysStartDay,
    LEAST(holidays.enddate, years.yearEndDay) AS holidaysEndDay
FROM (
    SELECT e.name,
        TIMESTAMPDIFF(YEAR, e.startdate, h.date) AS yearNr,
        DATE_ADD(
            e.startdate, INTERVAL TIMESTAMPDIFF(YEAR, e.startdate, h.date) YEAR
        ) AS yearStartDay,
        DATE_ADD(
            DATE_ADD(e.startdate, 
                INTERVAL TIMESTAMPDIFF(YEAR, e.startdate, h.date) +1 YEAR
            ), INTERVAL -1 DAY
        ) AS yearEndDay,
        e.holidays,
        SUM(h.hours) AS hoursTaken,
        e.holidays * @hoursPerDay - SUM(h.hours) AS hoursLeft
    FROM (
        SELECT empid, DATE, hours
        FROM holidays_taken h 
        -- need union all if didnt have holidays in that year yet
        UNION ALL SELECT @empid AS empid, @startdate AS DATE, 0 AS hours
        UNION ALL SELECT @empid AS empid, @enddate   AS DATE, 0 AS hours
    ) h
    JOIN employees e ON e.id = h.empid
    WHERE h.empid = @empid /* @todo replace with correct employeeID */
    GROUP BY e.id, e.name, e.holidays, yearStartDay, yearEndDay
) years
JOIN (SELECT @startdate AS startdate, @enddate AS enddate) holidays /* @todo replace with $startdate and $enddate */
WHERE holidays.startdate BETWEEN years.yearStartDay AND years.yearEndDay
   OR holidays.enddate   BETWEEN years.yearStartDay AND years.yearEndDay

http://sqlfiddle.com/#!9/10a1d/15

您不需要使用 session 变量。只需将它们替换为 PHP 中的值即可。删除外部选择中不需要的所有内容。

那么这个查询做了什么:内部选择与您的查询几乎相同，但是

• 可以处理员工年从 2 月 29 日开始的情况
• UNION ALL 部分创建新的一年，如果员工在那一年没有假期的话。
• 一年的最后一天与下一年的第一天不重叠。

然后它会查找新定义的假期和所有员工年份之间的交集。然后计算每个员工年的新假期的日期范围。

要测试更多情况，请将 session 变量定义替换为:

-- sample 2: first time taking holidays in a year
SET @startdate := DATE('2017-10-05');
SET @enddate   := DATE('2017-10-20');
SET @empid := 1;


-- sample 3: first year of employee who starts on Feb. 29th
SET @startdate := DATE('2016-10-05');
SET @enddate   := DATE('2016-10-20');
SET @empid := 2;


-- sample 4: second year of employee who starts on Feb. 29th
SET @startdate := DATE('2017-10-05');
SET @enddate   := DATE('2017-10-20');
SET @empid := 2;