From 49ced31d8e5e6b8f364cfd83cbd9673d60fed9ed Mon Sep 17 00:00:00 2001 From: Charles Farmer Date: Wed, 4 Sep 2019 14:57:19 -0400 Subject: [PATCH] Bug 17015: Install scripts for DiscreteCalendar --- ...g_17015_part1_create_discrete_calendar.sql | 14 ++ ...ug_17015_part2_fill_discrete_calendar.perl | 147 ++++++++++++++++++ .../bug_17015_part3_drop_calendar.sql | 5 + installer/data/mysql/updatedatabase.pl | 4 +- 4 files changed, 168 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.sql create mode 100755 installer/data/mysql/atomicupdate/bug_17015_part2_fill_discrete_calendar.perl create mode 100644 installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.sql diff --git a/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.sql b/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.sql new file mode 100644 index 0000000000..9956b4dcdf --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.sql @@ -0,0 +1,14 @@ +-- Bugzilla 17015 +-- New koha calendar +-- Create discrete_calendar table to keep track of library day's information + +CREATE TABLE `discrete_calendar` ( + `date` datetime NOT NULL, + `branchcode` varchar(10) NOT NULL, + `is_opened` tinyint(1) DEFAULT 1, + `holiday_type` varchar(1) DEFAULT '', + `note` varchar(30) DEFAULT '', + `open_hour` time NOT NULL, + `close_hour` time NOT NULL, + PRIMARY KEY (`branchcode`,`date`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; diff --git a/installer/data/mysql/atomicupdate/bug_17015_part2_fill_discrete_calendar.perl b/installer/data/mysql/atomicupdate/bug_17015_part2_fill_discrete_calendar.perl new file mode 100755 index 0000000000..6cc1265c82 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part2_fill_discrete_calendar.perl @@ -0,0 +1,147 @@ +#!/usr/bin/perl + +# +# Script that fills the discrete_calendar table with dates, using the other date-related tables +# +use Modern::Perl; +use DateTime; +use DateTime::Format::Strptime; +use Data::Dumper; +use Getopt::Long; +use C4::Context; + +# Options +my $daysInFuture = 365; + +my $dbh = C4::Context->dbh; +$dbh->{AutoCommit} = 0; +$dbh->{RaiseError} = 1; + +my $currentDate = DateTime->today; + +# two years ago +my $startDate = DateTime->new( +day => $currentDate->day(), +month => $currentDate->month(), +year => $currentDate->year()-2, +time_zone => C4::Context->tz() +)->truncate( to => 'day' ); + +# a year into the future +my $endDate = DateTime->new( +day => $currentDate->day(), +month => $currentDate->month(), +year => $currentDate->year(), +time_zone => C4::Context->tz() +)->truncate( to => 'day' ); +$endDate->add(days => $daysInFuture); + +#Added a default (standard) branch. +my $add_default_branch = 'INSERT IGNORE INTO branches (branchname, branchcode) VALUES(?,?)'; +my $add_Branch_Sth = $dbh->prepare($add_default_branch); +$add_Branch_Sth->execute('Default', ''); +# finds branches; +my $selectBranchesSt = 'SELECT branchname, branchcode FROM branches'; +my $selectBranchesSth = $dbh->prepare($selectBranchesSt); +$selectBranchesSth->execute(); +my @branches = (); +while (my @row = $selectBranchesSth->fetchrow_array ) { + print "[$row[1]] $row[0]\n"; + push @branches, $row[1]; +} + +# finds what days are closed for each branch +my %repeatableHolidaysPerBranch = (); +my %specialHolidaysPerBranch = (); +my $selectWeeklySt; +my $selectWeeklySth; + +foreach my $branch (@branches){ + + $selectWeeklySt = 'SELECT weekday, title, day, month FROM repeatable_holidays WHERE branchcode = ?'; + $selectWeeklySth = $dbh->prepare($selectWeeklySt); + $selectWeeklySth->execute($branch); + + my @weeklyHolidays = (); + + while ( my ($weekDay, $title, $day, $month) = $selectWeeklySth->fetchrow_array ) { + push @weeklyHolidays,{weekday => $weekDay, title => $title, day => $day, month => $month}; + + } + + $repeatableHolidaysPerBranch{$branch} = \@weeklyHolidays; + + my $selectSpecialHolidayDateSt = 'SELECT day,month,year,title FROM special_holidays WHERE branchcode = ? AND isexception = 0'; + my $specialHolidayDatesSth = $dbh->prepare($selectSpecialHolidayDateSt); + $specialHolidayDatesSth -> execute($branch); + # Tranforms dates from specialHolidays table in DateTime for our new table + my @specialHolidayDates = (); + while ( my ($day, $month, $year, $title) = $specialHolidayDatesSth->fetchrow_array ) { + + my $specialHolidayDate = DateTime->new( + day => $day, + month => $month, + year => $year, + time_zone => C4::Context->tz() + )->truncate( to => 'day' ); + push @specialHolidayDates,{date=>$specialHolidayDate, title=> $title}; + } + + $specialHolidaysPerBranch{$branch} = \@specialHolidayDates; +} +# Fills table with dates and sets 'is_opened' according to the branch's weekly restrictions (repeatable_holidays) +my $insertDateSt; +my $insertDateSth; + +# Loop that does everything in the world +for (my $tempDate = $startDate->clone(); $tempDate <= $endDate;$tempDate->add(days => 1)){ + foreach my $branch (@branches){ + my $dayOfWeek = $tempDate->day_of_week; + # Representation fix + # DateTime object dow (1-7) where Monday is 1 + # Arrays are 0-based where 0 = Sunday, not 7. + my $open_hour = "09:00:00"; + my $close_hour = "17:00:00"; + + # Finds closed days + my $is_opened =1; + my $specialDescription = ""; + my $holiday_type =''; + $dayOfWeek = $tempDate->day_of_week % 7; + foreach my $holidayWeekDay ( @{$repeatableHolidaysPerBranch{$branch}} ) { + if (defined($holidayWeekDay) && $dayOfWeek == $holidayWeekDay->{weekday}) { + $is_opened = 0; + $specialDescription = $holidayWeekDay->{title}; + $holiday_type = 'W'; + } elsif ($holidayWeekDay->{day} && $holidayWeekDay->{month}) { + my $date = DateTime->new( + day => $holidayWeekDay->{day}, + month => $holidayWeekDay->{month}, + year => $tempDate->year(), + time_zone => C4::Context->tz() + )->truncate( to => 'day' ); + + if ($tempDate == $date) { + $is_opened = 0; + $specialDescription = $holidayWeekDay->{title}; + $holiday_type = 'R'; + } + } + } + + foreach my $specialDate (@{$specialHolidaysPerBranch{$branch}}){ + if ($tempDate->datetime() eq $specialDate->{date}->datetime()) { + $is_opened = 0; + $specialDescription = $specialDate->{title}; + $holiday_type = 'E'; + } + } + #final insert statement + + $insertDateSt = 'INSERT IGNORE INTO discrete_calendar (date,branchcode,is_opened,holiday_type,note,open_hour,close_hour) VALUES (?,?,?,?,?,?,?)'; + $insertDateSth = $dbh->prepare($insertDateSt); + $insertDateSth->execute($tempDate,$branch,$is_opened,$holiday_type,$specialDescription,$open_hour,$close_hour); + } +} +# If everything went well we commit to the database +$dbh->commit(); diff --git a/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.sql b/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.sql new file mode 100644 index 0000000000..68da78b7a8 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.sql @@ -0,0 +1,5 @@ +-- Bugzilla 17015 +-- New koha calendar +-- Drop deprecated calendar-related tables after creating and filling discrete_calendar +DROP TABLE IF EXISTS `repeatable_holidays`; +DROP TABLE IF EXISTS `special_holidays`; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 40e6a11a3f..88f8f6a756 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -14343,7 +14343,7 @@ if( CheckVersion( $DBversion ) ) { $DBversion = '16.12.00.032'; if( CheckVersion( $DBversion ) ) { - require Koha::Calendar; + require Koha::DiscreteCalendar; $dbh->do(q{ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) @@ -14370,7 +14370,7 @@ if( CheckVersion( $DBversion ) ) { my $expirationdate = dt_from_string($hold->{waitingdate}); if ( C4::Context->preference("ExcludeHolidaysFromMaxPickUpDelay") ) { - my $calendar = Koha::Calendar->new( branchcode => $hold->{branchcode}, days_mode => C4::Context->preference('useDaysMode') ); + my $calendar = Koha::DiscreteCalendar->new( branchcode => $hold->{branchcode}, days_mode => C4::Context->preference('useDaysMode') ); $expirationdate = $calendar->days_forward( $expirationdate, $max_pickup_delay ); } else { $expirationdate->add( days => $max_pickup_delay ); -- 2.17.1