From f05b0b65d3f4040c6774fa6512eb622a87d79932 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 Signed-off-by: Michal Denar --- ..._17015_part1_create_discrete_calendar.perl | 19 +++ ...ug_17015_part2_fill_discrete_calendar.perl | 150 ++++++++++++++++++ .../bug_17015_part3_drop_calendar.perl | 11 ++ installer/data/mysql/updatedatabase.pl | 4 +- 4 files changed, 182 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.perl 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.perl diff --git a/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.perl b/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.perl new file mode 100644 index 0000000000..0e5d16ed65 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.perl @@ -0,0 +1,19 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + if ( !TableExists( 'discrete_calendar' ) ) { + $dbh->do( qq{ + 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; + } ); + } + + NewVersion( $DBversion, 17015, "New koha calendar Part 1 - Create discrete_calendar table to keep track of library day's information"); +} 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..1b4282af63 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part2_fill_discrete_calendar.perl @@ -0,0 +1,150 @@ +#!/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); + +# 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){ + if ( TableExists( 'repeatable_holidays' ) ) { + $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; + } + + if ( TableExists( 'repeatable_holidays' ) ) { + 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) && defined($holidayWeekDay->{weekday}) && $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(); +$dbh->{AutoCommit} = 1; +$dbh->{RaiseError} = 0; diff --git a/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.perl b/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.perl new file mode 100644 index 0000000000..a7e96b0ae5 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part3_drop_calendar.perl @@ -0,0 +1,11 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + if ( TableExists( 'repeatable_holidays' ) ) { + $dbh->do( "DROP TABLE IF EXISTS `repeatable_holidays`;" ); + } + if ( TableExists( 'special_holidays' ) ) { + $dbh->do( "DROP TABLE IF EXISTS `special_holidays`;" ); + } + + NewVersion( $DBversion, 17015, "New koha calendar Part 3 - Drop deprecated calendar-related tables after creating and filling discrete_calendar"); +} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index c95fdf3e3c..74ad65fc1a 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -14344,7 +14344,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) @@ -14371,7 +14371,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.25.1