View | Details | Raw Unified | Return to bug 17015
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.perl (+19 lines)
Line 0 Link Here
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
    if ( !TableExists( 'discrete_calendar' ) ) {
4
        $dbh->do( qq{
5
            CREATE TABLE `discrete_calendar` (
6
                `date` datetime NOT NULL,
7
                `branchcode` varchar(10) NOT NULL,
8
                `is_opened` tinyint(1) DEFAULT 1,
9
                `holiday_type` varchar(1) DEFAULT '',
10
                `note` varchar(30) DEFAULT '',
11
                `open_hour` time NOT NULL,
12
                `close_hour` time NOT NULL,
13
                PRIMARY KEY (`branchcode`,`date`)
14
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
15
        } );
16
    }
17
18
    NewVersion( $DBversion, 17015, "New koha calendar Part 1 - Create discrete_calendar table to keep track of library day's information");
19
}
(-)a/installer/data/mysql/atomicupdate/bug_17015_part2_fill_discrete_calendar.perl (+150 lines)
Line 0 Link Here
1
#!/usr/bin/perl
2
3
#
4
#   Script that fills the discrete_calendar table with dates, using the other date-related tables
5
#
6
use Modern::Perl;
7
use DateTime;
8
use DateTime::Format::Strptime;
9
use Data::Dumper;
10
use Getopt::Long;
11
use C4::Context;
12
13
# Options
14
my $daysInFuture = 365;
15
16
my $dbh = C4::Context->dbh;
17
$dbh->{AutoCommit} = 0;
18
$dbh->{RaiseError} = 1;
19
20
my $currentDate = DateTime->today;
21
22
# two years ago
23
my $startDate = DateTime->new(
24
day       => $currentDate->day(),
25
month     => $currentDate->month(),
26
year      => $currentDate->year()-2,
27
time_zone => C4::Context->tz()
28
)->truncate( to => 'day' );
29
30
# a year into the future
31
my $endDate = DateTime->new(
32
day       => $currentDate->day(),
33
month     => $currentDate->month(),
34
year      => $currentDate->year(),
35
time_zone => C4::Context->tz()
36
)->truncate( to => 'day' );
37
$endDate->add(days => $daysInFuture);
38
39
# finds branches;
40
my $selectBranchesSt = 'SELECT branchname, branchcode FROM branches';
41
my $selectBranchesSth = $dbh->prepare($selectBranchesSt);
42
$selectBranchesSth->execute();
43
my @branches = ();
44
while (my @row = $selectBranchesSth->fetchrow_array ) {
45
    print "[$row[1]] $row[0]\n";
46
    push @branches, $row[1];
47
}
48
49
# finds what days are closed for each branch
50
my %repeatableHolidaysPerBranch = ();
51
my %specialHolidaysPerBranch = ();
52
my $selectWeeklySt;
53
my $selectWeeklySth;
54
55
foreach my $branch (@branches){
56
    if ( TableExists( 'repeatable_holidays' ) ) {
57
        $selectWeeklySt = 'SELECT weekday, title, day, month FROM repeatable_holidays WHERE branchcode = ?';
58
        $selectWeeklySth = $dbh->prepare($selectWeeklySt);
59
        $selectWeeklySth->execute($branch);
60
61
        my @weeklyHolidays = ();
62
63
        while ( my ($weekDay, $title, $day, $month) = $selectWeeklySth->fetchrow_array ) {
64
            push @weeklyHolidays,{weekday => $weekDay, title => $title, day => $day, month => $month};
65
66
        }
67
68
        $repeatableHolidaysPerBranch{$branch} = \@weeklyHolidays;
69
    }
70
71
    if ( TableExists( 'repeatable_holidays' ) ) {
72
        my $selectSpecialHolidayDateSt = 'SELECT day,month,year,title FROM special_holidays WHERE branchcode = ? AND isexception = 0';
73
        my $specialHolidayDatesSth = $dbh->prepare($selectSpecialHolidayDateSt);
74
        $specialHolidayDatesSth -> execute($branch);
75
        # Tranforms dates from specialHolidays table in DateTime for our new table
76
        my @specialHolidayDates = ();
77
        while ( my ($day, $month, $year, $title) = $specialHolidayDatesSth->fetchrow_array ) {
78
79
            my $specialHolidayDate = DateTime->new(
80
            day       => $day,
81
            month     => $month,
82
            year      => $year,
83
            time_zone => C4::Context->tz()
84
            )->truncate( to => 'day' );
85
            push @specialHolidayDates,{date=>$specialHolidayDate, title=> $title};
86
        }
87
88
        $specialHolidaysPerBranch{$branch} = \@specialHolidayDates;
89
    }
90
}
91
92
# Fills table with dates and sets 'is_opened' according to the branch's weekly restrictions (repeatable_holidays)
93
my $insertDateSt;
94
my $insertDateSth;
95
96
# Loop that does everything in the world
97
for (my $tempDate = $startDate->clone(); $tempDate <= $endDate;$tempDate->add(days => 1)){
98
    foreach my $branch (@branches){
99
        my $dayOfWeek = $tempDate->day_of_week;
100
        # Representation fix
101
        # DateTime object dow (1-7) where Monday is 1
102
        # Arrays are 0-based where 0 = Sunday, not 7.
103
        my $open_hour = "09:00:00";
104
        my $close_hour = "17:00:00";
105
106
        # Finds closed days
107
        my $is_opened =1;
108
        my $specialDescription = "";
109
        my $holiday_type ='';
110
        $dayOfWeek = $tempDate->day_of_week % 7;
111
        foreach my $holidayWeekDay ( @{$repeatableHolidaysPerBranch{$branch}} ) {
112
            if (defined($holidayWeekDay) && $dayOfWeek == $holidayWeekDay->{weekday}) {
113
                $is_opened = 0;
114
                $specialDescription = $holidayWeekDay->{title};
115
                $holiday_type = 'W';
116
            } elsif ($holidayWeekDay->{day} && $holidayWeekDay->{month}) {
117
                my $date = DateTime->new(
118
                day       => $holidayWeekDay->{day},
119
                month     => $holidayWeekDay->{month},
120
                year      => $tempDate->year(),
121
                time_zone => C4::Context->tz()
122
                )->truncate( to => 'day' );
123
124
                if ($tempDate == $date) {
125
                    $is_opened = 0;
126
                    $specialDescription = $holidayWeekDay->{title};
127
                    $holiday_type = 'R';
128
                }
129
            }
130
        }
131
132
        foreach my $specialDate (@{$specialHolidaysPerBranch{$branch}}){
133
            if ($tempDate->datetime() eq $specialDate->{date}->datetime()) {
134
                $is_opened = 0;
135
                $specialDescription = $specialDate->{title};
136
                $holiday_type = 'E';
137
            }
138
        }
139
        #final insert statement
140
141
        $insertDateSt = 'INSERT IGNORE INTO discrete_calendar (date,branchcode,is_opened,holiday_type,note,open_hour,close_hour) VALUES (?,?,?,?,?,?,?)';
142
        $insertDateSth = $dbh->prepare($insertDateSt);
143
        $insertDateSth->execute($tempDate,$branch,$is_opened,$holiday_type,$specialDescription,$open_hour,$close_hour);
144
    }
145
}
146
147
# If everything went well we commit to the database
148
$dbh->commit();
149
$dbh->{AutoCommit} = 1;
150
$dbh->{RaiseError} = 0;
(-)a/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.perl (+11 lines)
Line 0 Link Here
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
    if ( TableExists( 'repeatable_holidays' ) ) {
4
        $dbh->do( "DROP TABLE IF EXISTS `repeatable_holidays`;" );
5
    }
6
    if ( TableExists( 'special_holidays' ) ) {
7
        $dbh->do( "DROP TABLE IF EXISTS `special_holidays`;" );
8
    }
9
10
    NewVersion( $DBversion, 17015, "New koha calendar Part 3 - Drop deprecated calendar-related tables after creating and filling discrete_calendar");
11
}
(-)a/installer/data/mysql/updatedatabase.pl (-3 / +2 lines)
Lines 14345-14351 if( CheckVersion( $DBversion ) ) { Link Here
14345
14345
14346
$DBversion = '16.12.00.032';
14346
$DBversion = '16.12.00.032';
14347
if( CheckVersion( $DBversion ) ) {
14347
if( CheckVersion( $DBversion ) ) {
14348
    require Koha::Calendar;
14348
    require Koha::DiscreteCalendar;
14349
14349
14350
    $dbh->do(q{
14350
    $dbh->do(q{
14351
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) 
14351
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) 
Lines 14372-14378 if( CheckVersion( $DBversion ) ) { Link Here
14372
14372
14373
        my $expirationdate = dt_from_string($hold->{waitingdate});
14373
        my $expirationdate = dt_from_string($hold->{waitingdate});
14374
        if ( C4::Context->preference("ExcludeHolidaysFromMaxPickUpDelay") ) {
14374
        if ( C4::Context->preference("ExcludeHolidaysFromMaxPickUpDelay") ) {
14375
            my $calendar = Koha::Calendar->new( branchcode => $hold->{branchcode}, days_mode => C4::Context->preference('useDaysMode') );
14375
            my $calendar = Koha::DiscreteCalendar->new( branchcode => $hold->{branchcode}, days_mode => C4::Context->preference('useDaysMode') );
14376
            $expirationdate = $calendar->days_forward( $expirationdate, $max_pickup_delay );
14376
            $expirationdate = $calendar->days_forward( $expirationdate, $max_pickup_delay );
14377
        } else {
14377
        } else {
14378
            $expirationdate->add( days => $max_pickup_delay );
14378
            $expirationdate->add( days => $max_pickup_delay );
14379
- 

Return to bug 17015