[Summary view]

Differences Between: [Versions 400 and 401]

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