From dfa577ca7ce2d26b81b1baa6e237f3c2b1c085f4 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

Signed-off-by: Michal Denar <black23@gmail.com>
---
 ..._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..05c83a05e3
--- /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) && $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 811b7ee632..3a8af59f3c 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