From 49308e303830449e1bf533de5370fdebfc4558b3 Mon Sep 17 00:00:00 2001
From: Charles Farmer <charles.farmer@inLibro.com>
Date: Wed, 4 Sep 2019 14:57:19 -0400
Subject: [PATCH] Bug 17015: Install scripts for DiscreteCalendar
---
.../bug_17015_part1_create_discrete_calendar.sql | 14 ++
.../bug_17015_part2_fill_discrete_calendar.perl | 147 +++++++++++++++++++++
.../atomicupdate/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 0000000..9956b4d
--- /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 0000000..6cc1265
--- /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 0000000..68da78b
--- /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 341cdcb..ebba260 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -14347,7 +14347,7 @@ if( CheckVersion( $DBversion ) ) {
$DBversion = '16.12.00.032';
if( CheckVersion( $DBversion ) ) {
- require Koha::Calendar;
+ require Koha::DiscreteCalendar;
require Koha::Holds;
$dbh->do(q{
@@ -14366,7 +14366,7 @@ if( CheckVersion( $DBversion ) ) {
my $expirationdate = dt_from_string($hold->waitingdate);
if ( C4::Context->preference("ExcludeHolidaysFromMaxPickUpDelay") ) {
- my $calendar = Koha::Calendar->new( branchcode => $hold->branchcode );
+ my $calendar = Koha::DiscreteCalendar->new({ branchcode => $hold->branchcode });
$expirationdate = $calendar->days_forward( $expirationdate, $max_pickup_delay );
} else {
$expirationdate->add( days => $max_pickup_delay );
--
2.7.4