php - 每月或每年的个人假期总天数

Question

我有假期表:

`holiday` (`ID_holiday`, `holiday_start`, `holiday_end`, `holiday_typ`, `ID_empl`)
(18, '2019-07-07', '2019-07-18', 1, 6),
(19, '2019-06-25', '2019-06-25', 3, 5),
(20, '2019-05-30', '2019-06-05', 1, 6),
(21, '2019-05-30', '2019-05-30', 1, 5),
(22, '2019-06-30', '2019-06-30', 1, 5),
(23, '2019-06-30', '2019-06-30', 1, 6),

我需要每月和每年的假期天数(总和)。
假期是在周末和公共(public)假期输入的 - 我只需要工作日的总和。

holiday_typ 1 .. 1 天
Holiday_typ 3 .. 0.5 天

是否可以通过mysql查询或者mysql+php解决？谢谢

我尝试..为一位雇主:

for ($Day = 1; $Day <= $last_day_month; $Day++) {
      $date = $YYYY.'-'.$Month.'-'.$Day;
      $cz_svatek = cz_svatek ($Month, $Day, $YYYY);  // return name of public holiday or ''

      $sql = "SELECT *
              FROM holiday
              JOIN holiday_typ  USING (ID_holiday_typ)
              WHERE ID = ".$ID."
                AND holiday_start <= '".$date."'
                AND holiday_end >= '".$date."'";
      $result = MySQL_Query($sql);

      while ($dbMuf = MySQL_Fetch_Array($result)){
        if( date("N", mktime(12, 0, 0, $Month, $Day, $YYYY)) < 6 && $cz_svatek == '') $holiday[$dbMuf["ID_dov_typ"]]++;
      }
$sum_holiday = $holiday[1]+0.5*$holiday[3];

Answer 1

我是这样做的(目前):

('Hol_len' 来自表 Holiday_typ ... 0.5 || 1.0 || 0.0 )(ID = ID_Empl)

$frs_day_y = mktime( 0, 0, 0, 1, 1, $YYYY );
$lst_day_y = mktime( 23, 59, 59, 12, 31, $YYYY );

for ($Day_mk = $frs_day_y; $Day_mk <= $lst_day_y; $Day_mk += 86400 ) {
    $Day   = date('d', $Day_mk);
    $Month = date('n', $Day_mk);
    $date  = date('Y-m-d', $Day_mk);

$sql = "SELECT *
              FROM dovolena
              JOIN holiday_typ  USING (ID_holiday_typ)
              WHERE Hol_len > 0
                AND holiday_start <= '".$date."'
                AND holiday_end >= '".$date."'";
    $result = MySQL_Query($sql);

    while ($dbMuf = MySQL_Fetch_Array($result)){
      if( date("N", $Day_mk) < 6 && cz_svatek ($Month, $Day, $YYYY) == '') {

        if( isset($Hol_sum[$Month][$dbMuf["ID"]]) ) $Hol_sum[$Month][$dbMuf["ID"]] += $dbMuf["Dov_len"];       //$dbMuf["ID_dov_typ"]
        else $Hol_sum[$Month][$dbMuf["ID"]] = $dbMuf["Hol_len"]+0;

        if( isset($Hol_sum_y[$dbMuf["ID"]]) ) $Hol_sum_y[$dbMuf["ID"]] += $dbMuf["Hol_len"];       //$dbMuf["ID_dov_typ"]
        else $Hol_sum_y[$dbMuf["ID"]] = $dbMuf["Hol_len"];

        $id[$dbMuf["ID"]] = 1;
      }
    }
  }