[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   7  
   8  class WorkDay
   9  {
  10      /**
  11       * WORKDAY.
  12       *
  13       * Returns the date that is the indicated number of working days before or after a date (the
  14       * starting date). Working days exclude weekends and any dates identified as holidays.
  15       * Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected
  16       * delivery times, or the number of days of work performed.
  17       *
  18       * Excel Function:
  19       *        WORKDAY(startDate,endDays[,holidays[,holiday[,...]]])
  20       *
  21       * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer),
  22       *                                        PHP DateTime object, or a standard date string
  23       * @param int $endDays The number of nonweekend and nonholiday days before or after
  24       *                                        startDate. A positive value for days yields a future date; a
  25       *                                        negative value yields a past date.
  26       * @param mixed $dateArgs
  27       *
  28       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  29       *                        depending on the value of the ReturnDateType flag
  30       */
  31      public static function date($startDate, $endDays, ...$dateArgs)
  32      {
  33          //    Retrieve the mandatory start date and days that are referenced in the function definition
  34          try {
  35              $startDate = Helpers::getDateValue($startDate);
  36              $endDays = Helpers::validateNumericNull($endDays);
  37              $dateArgs = Functions::flattenArray($dateArgs);
  38              $holidayArray = [];
  39              foreach ($dateArgs as $holidayDate) {
  40                  $holidayArray[] = Helpers::getDateValue($holidayDate);
  41              }
  42          } catch (Exception $e) {
  43              return $e->getMessage();
  44          }
  45  
  46          $startDate = (float) floor($startDate);
  47          $endDays = (int) floor($endDays);
  48          //    If endDays is 0, we always return startDate
  49          if ($endDays == 0) {
  50              return $startDate;
  51          }
  52          if ($endDays < 0) {
  53              return self::decrementing($startDate, $endDays, $holidayArray);
  54          }
  55  
  56          return self::incrementing($startDate, $endDays, $holidayArray);
  57      }
  58  
  59      /**
  60       * Use incrementing logic to determine Workday.
  61       *
  62       * @return mixed
  63       */
  64      private static function incrementing(float $startDate, int $endDays, array $holidayArray)
  65      {
  66          //    Adjust the start date if it falls over a weekend
  67  
  68          $startDoW = self::getWeekDay($startDate, 3);
  69          if (self::getWeekDay($startDate, 3) >= 5) {
  70              $startDate += 7 - $startDoW;
  71              --$endDays;
  72          }
  73  
  74          //    Add endDays
  75          $endDate = (float) $startDate + ((int) ($endDays / 5) * 7);
  76          $endDays = $endDays % 5;
  77          while ($endDays > 0) {
  78              ++$endDate;
  79              //    Adjust the calculated end date if it falls over a weekend
  80              $endDow = self::getWeekDay($endDate, 3);
  81              if ($endDow >= 5) {
  82                  $endDate += 7 - $endDow;
  83              }
  84              --$endDays;
  85          }
  86  
  87          //    Test any extra holiday parameters
  88          if (!empty($holidayArray)) {
  89              $endDate = self::incrementingArray($startDate, $endDate, $holidayArray);
  90          }
  91  
  92          return Helpers::returnIn3FormatsFloat($endDate);
  93      }
  94  
  95      private static function incrementingArray(float $startDate, float $endDate, array $holidayArray): float
  96      {
  97          $holidayCountedArray = $holidayDates = [];
  98          foreach ($holidayArray as $holidayDate) {
  99              if (self::getWeekDay($holidayDate, 3) < 5) {
 100                  $holidayDates[] = $holidayDate;
 101              }
 102          }
 103          sort($holidayDates, SORT_NUMERIC);
 104          foreach ($holidayDates as $holidayDate) {
 105              if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) {
 106                  if (!in_array($holidayDate, $holidayCountedArray)) {
 107                      ++$endDate;
 108                      $holidayCountedArray[] = $holidayDate;
 109                  }
 110              }
 111              //    Adjust the calculated end date if it falls over a weekend
 112              $endDoW = self::getWeekDay($endDate, 3);
 113              if ($endDoW >= 5) {
 114                  $endDate += 7 - $endDoW;
 115              }
 116          }
 117  
 118          return $endDate;
 119      }
 120  
 121      /**
 122       * Use decrementing logic to determine Workday.
 123       *
 124       * @return mixed
 125       */
 126      private static function decrementing(float $startDate, int $endDays, array $holidayArray)
 127      {
 128          //    Adjust the start date if it falls over a weekend
 129  
 130          $startDoW = self::getWeekDay($startDate, 3);
 131          if (self::getWeekDay($startDate, 3) >= 5) {
 132              $startDate += -$startDoW + 4;
 133              ++$endDays;
 134          }
 135  
 136          //    Add endDays
 137          $endDate = (float) $startDate + ((int) ($endDays / 5) * 7);
 138          $endDays = $endDays % 5;
 139          while ($endDays < 0) {
 140              --$endDate;
 141              //    Adjust the calculated end date if it falls over a weekend
 142              $endDow = self::getWeekDay($endDate, 3);
 143              if ($endDow >= 5) {
 144                  $endDate += 4 - $endDow;
 145              }
 146              ++$endDays;
 147          }
 148  
 149          //    Test any extra holiday parameters
 150          if (!empty($holidayArray)) {
 151              $endDate = self::decrementingArray($startDate, $endDate, $holidayArray);
 152          }
 153  
 154          return Helpers::returnIn3FormatsFloat($endDate);
 155      }
 156  
 157      private static function decrementingArray(float $startDate, float $endDate, array $holidayArray): float
 158      {
 159          $holidayCountedArray = $holidayDates = [];
 160          foreach ($holidayArray as $holidayDate) {
 161              if (self::getWeekDay($holidayDate, 3) < 5) {
 162                  $holidayDates[] = $holidayDate;
 163              }
 164          }
 165          rsort($holidayDates, SORT_NUMERIC);
 166          foreach ($holidayDates as $holidayDate) {
 167              if (($holidayDate <= $startDate) && ($holidayDate >= $endDate)) {
 168                  if (!in_array($holidayDate, $holidayCountedArray)) {
 169                      --$endDate;
 170                      $holidayCountedArray[] = $holidayDate;
 171                  }
 172              }
 173              //    Adjust the calculated end date if it falls over a weekend
 174              $endDoW = self::getWeekDay($endDate, 3);
 175              if ($endDoW >= 5) {
 176                  $endDate += -$endDoW + 4;
 177              }
 178          }
 179  
 180          return $endDate;
 181      }
 182  
 183      private static function getWeekDay(float $date, int $wd): int
 184      {
 185          $result = Week::day($date, $wd);
 186  
 187          return is_string($result) ? -1 : $result;
 188      }
 189  }