|
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 NetworkDays |
|
9
|
|
|
{ |
|
10
|
|
|
/** |
|
11
|
|
|
* NETWORKDAYS. |
|
12
|
|
|
* |
|
13
|
|
|
* Returns the number of whole working days between start_date and end_date. Working days |
|
14
|
|
|
* exclude weekends and any dates identified in holidays. |
|
15
|
|
|
* Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days |
|
16
|
|
|
* worked during a specific term. |
|
17
|
|
|
* |
|
18
|
|
|
* Excel Function: |
|
19
|
|
|
* NETWORKDAYS(startDate,endDate[,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 mixed $endDate Excel date serial value (float), PHP date timestamp (integer), |
|
24
|
|
|
* PHP DateTime object, or a standard date string |
|
25
|
|
|
* @param mixed $dateArgs |
|
26
|
|
|
* |
|
27
|
|
|
* @return int|string Interval between the dates |
|
28
|
|
|
*/ |
|
29
|
29 |
|
public static function count($startDate, $endDate, ...$dateArgs) |
|
30
|
|
|
{ |
|
31
|
|
|
try { |
|
32
|
|
|
// Retrieve the mandatory start and end date that are referenced in the function definition |
|
33
|
29 |
|
$sDate = Helpers::getDateValue($startDate); |
|
34
|
28 |
|
$eDate = Helpers::getDateValue($endDate); |
|
35
|
27 |
|
$startDate = min($sDate, $eDate); |
|
36
|
27 |
|
$endDate = max($sDate, $eDate); |
|
37
|
|
|
// Get the optional days |
|
38
|
27 |
|
$dateArgs = Functions::flattenArray($dateArgs); |
|
39
|
|
|
// Test any extra holiday parameters |
|
40
|
27 |
|
$holidayArray = []; |
|
41
|
27 |
|
foreach ($dateArgs as $holidayDate) { |
|
42
|
5 |
|
$holidayArray[] = Helpers::getDateValue($holidayDate); |
|
43
|
|
|
} |
|
44
|
3 |
|
} catch (Exception $e) { |
|
45
|
3 |
|
return $e->getMessage(); |
|
46
|
|
|
} |
|
47
|
|
|
|
|
48
|
|
|
// Execute function |
|
49
|
26 |
|
$startDow = self::calcStartDow($startDate); |
|
50
|
26 |
|
$endDow = self::calcEndDow($endDate); |
|
51
|
26 |
|
$wholeWeekDays = (int) floor(($endDate - $startDate) / 7) * 5; |
|
52
|
26 |
|
$partWeekDays = self::calcPartWeekDays($startDow, $endDow); |
|
53
|
|
|
|
|
54
|
|
|
// Test any extra holiday parameters |
|
55
|
26 |
|
$holidayCountedArray = []; |
|
56
|
26 |
|
foreach ($holidayArray as $holidayDate) { |
|
57
|
4 |
|
if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) { |
|
58
|
4 |
|
if ((Week::day($holidayDate, 2) < 6) && (!in_array($holidayDate, $holidayCountedArray))) { |
|
59
|
4 |
|
--$partWeekDays; |
|
60
|
4 |
|
$holidayCountedArray[] = $holidayDate; |
|
61
|
|
|
} |
|
62
|
|
|
} |
|
63
|
|
|
} |
|
64
|
|
|
|
|
65
|
26 |
|
return self::applySign($wholeWeekDays + $partWeekDays, $sDate, $eDate); |
|
66
|
|
|
} |
|
67
|
|
|
|
|
68
|
26 |
|
private static function calcStartDow(float $startDate): int |
|
69
|
|
|
{ |
|
70
|
26 |
|
$startDow = 6 - (int) Week::day($startDate, 2); |
|
71
|
26 |
|
if ($startDow < 0) { |
|
72
|
5 |
|
$startDow = 5; |
|
73
|
|
|
} |
|
74
|
|
|
|
|
75
|
26 |
|
return $startDow; |
|
76
|
|
|
} |
|
77
|
|
|
|
|
78
|
26 |
|
private static function calcEndDow(float $endDate): int |
|
79
|
|
|
{ |
|
80
|
26 |
|
$endDow = (int) Week::day($endDate, 2); |
|
81
|
26 |
|
if ($endDow >= 6) { |
|
82
|
4 |
|
$endDow = 0; |
|
83
|
|
|
} |
|
84
|
|
|
|
|
85
|
26 |
|
return $endDow; |
|
86
|
|
|
} |
|
87
|
|
|
|
|
88
|
26 |
|
private static function calcPartWeekDays(int $startDow, int $endDow): int |
|
89
|
|
|
{ |
|
90
|
26 |
|
$partWeekDays = $endDow + $startDow; |
|
91
|
26 |
|
if ($partWeekDays > 5) { |
|
92
|
18 |
|
$partWeekDays -= 5; |
|
93
|
|
|
} |
|
94
|
|
|
|
|
95
|
26 |
|
return $partWeekDays; |
|
96
|
|
|
} |
|
97
|
|
|
|
|
98
|
26 |
|
private static function applySign(int $result, float $sDate, float $eDate): int |
|
99
|
|
|
{ |
|
100
|
26 |
|
return ($sDate > $eDate) ? -$result : $result; |
|
101
|
|
|
} |
|
102
|
|
|
} |
|
103
|
|
|
|
PHPOffice /
PhpSpreadsheet
