@@ -, +, @@ --- .../bug_17015_table_discrete_calendar.sql | 9 ++ misc/cronjobs/add_days_discrete_calendar.pl | 103 +++++++++++++ misc/generate_discrete_calendar.pl | 159 +++++++++++++++++++++ tools/exceptionHolidays.pl | 98 ++++++++++++- tools/newHolidays.pl | 102 ++++++++++++- 5 files changed, 464 insertions(+), 7 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17015_table_discrete_calendar.sql create mode 100755 misc/cronjobs/add_days_discrete_calendar.pl create mode 100755 misc/generate_discrete_calendar.pl --- a/installer/data/mysql/atomicupdate/bug_17015_table_discrete_calendar.sql +++ a/installer/data/mysql/atomicupdate/bug_17015_table_discrete_calendar.sql @@ -0,0 +1,9 @@ +CREATE TABLE `discrete_calendar` ( + `date` datetime DEFAULT NULL, + `branchcode` varchar(10) NOT NULL, + `isopened` tinyint(1) DEFAULT 1, + `note` varchar(30) DEFAULT NULL, + openhour time, + closehour time, + PRIMARY KEY (`branchcode`,`date`) +) --- a/misc/cronjobs/add_days_discrete_calendar.pl +++ a/misc/cronjobs/add_days_discrete_calendar.pl @@ -0,0 +1,103 @@ +#!/usr/bin/perl + +# +# This script adds one day into discrete_calendar table based on the same day from the week before +# +use strict; +use warnings; +use DateTime; +use DateTime::Format::Strptime; +use Getopt::Long; +use C4::Context; +# Options +my $help = 0; +GetOptions ( + 'help|?|h' => \$help); + +my $usage = << 'ENDUSAGE'; + +This script adds days into discrete_calendar table based on the same day from the week before. + +Examples : + The latest date on discrete_calendar is : 28-07-2017 + The current date : 01-08-2016 + The dates that will be added are : 29-07-2017, 30-07-2017, 31-07-2017, 01-08-2017 +Open close exemples : + Date added is : 29-07-2017 + Opening/closing hours will be base on : 22-07-2017 (- 7 days) + Library open or closed will be based on : 29-07-2017 (- 1 year) +This script has the following parameters: + -h --help: this message + +PS: This is for testing purposes, the method of knowing whether it's opened or not may be changed. + +ENDUSAGE +my $dbh = C4::Context->dbh; + +if ($help) { + print $usage; + exit; +} +#getting the all the branches +my $selectBranchesSt = 'SELECT branchcode FROM branches'; +my $selectBranchesSth = $dbh->prepare($selectBranchesSt); +$selectBranchesSth->execute(); +my @branches = (); +while ( my $branchCode = $selectBranchesSth->fetchrow_array ) { + + push @branches,$branchCode; +} + +#get the latest date in the table +my $query = "select max(date) from discrete_calendar"; +my $stmt = $dbh->prepare($query); +$stmt->execute(); +my $latestedDate = $stmt->fetchrow_array; +my $parser = DateTime::Format::Strptime->new( + pattern => '%Y-%m-%d %H:%M:%S', + on_error => 'croak', +); +$latestedDate = $parser->parse_datetime($latestedDate); + +my $endDate = DateTime->today; +$endDate->add(years => 1, days=>1); +my $newDay = $latestedDate->clone(); + +for ($newDay->add(days => 1);$newDay <= $endDate;$newDay->add(days => 1)){ + my $lastWeekDay = $newDay->clone(); + $lastWeekDay->add(days=> -8); + my $dayOfWeek = $lastWeekDay->day_of_week; + # Representation fix + # DateTime object dow (1-7) where Monday is 1 + # Arrays are 0-based where 0 = Sunday, not 7. + $dayOfWeek -= 1 unless $dayOfWeek == 7; + $dayOfWeek = 0 if $dayOfWeek == 7; + + #getting the close and opening hour of the same day from last week + my $openhour = "select openhour from openinghours where weekcode=?"; + $stmt = $dbh->prepare($openhour); + $stmt->execute($dayOfWeek); + $openhour = $stmt->fetchrow_array; + + my $closehour = "select closehour from openinghours where weekcode=?"; + $stmt = $dbh->prepare($closehour); + $stmt->execute($dayOfWeek); + $closehour = $stmt->fetchrow_array; + + #checking if it was open on the same day from last year + my $yearAgo = $newDay->clone(); + $yearAgo = $yearAgo->add(years => -1); + my $isOpened = "select isopened from discrete_calendar where date=? and branchcode=?"; + my $add_Day = 'INSERT INTO discrete_calendar (date,branchcode,isopened,openhour,closehour) VALUES (?,?,?,?,?)'; + + #insert into discrete_calendar for each branch + foreach my $branchCode(@branches){ + $stmt = $dbh->prepare($isOpened); + $stmt->execute($yearAgo,$branchCode); + $isOpened = $stmt->fetchrow_array; + + my $add_Day = 'INSERT INTO discrete_calendar (date,branchcode,isopened,openhour,closehour) VALUES (?,?,?,?,?)'; + $stmt = $dbh->prepare($add_Day); + $stmt->execute($newDay,$branchCode,$isOpened,$openhour,$closehour); + } +} --- a/misc/generate_discrete_calendar.pl +++ a/misc/generate_discrete_calendar.pl @@ -0,0 +1,159 @@ +#!/usr/bin/perl + +# +# Script that fills the discrete_calendar table with dates, using the other date-related tables +# +use strict; +use warnings; +use DateTime; +use DateTime::Format::Strptime; +use Getopt::Long; +use C4::Context; + +# Options +my $generate = 0; +my $help = 0; +my $daysInFuture = 365; +GetOptions ( + 'days|?|d=i' => \$daysInFuture, + 'generate' => \$generate, + 'help|?|h' => \$help); +my $usage = << 'ENDUSAGE'; + +Script that manages the discrete_calendar table. + +This script has the following parameters : + --days --d : how many days in the future will be created, by default it's 365 + -h --help: this message + --generate: fills discrete_calendar table with dates from the last two years and the next one + +ENDUSAGE + +if ($help) { + print $usage; + exit; +} +#if ($generate) { + + my $dbh = C4::Context->dbh; + + 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 branchcode FROM branches'; + my $selectBranchesSth = $dbh->prepare($selectBranchesSt); + $selectBranchesSth->execute(); + my @branches = (); + while ( my $branchCode = $selectBranchesSth->fetchrow_array ) { + + push @branches,$branchCode; + } + + # finds what days are closed for each branch + my %repeatableHolidaysPerBranch = (); + my %specialHolidaysPerBranch = (); + my $selectWeeklySt; + my $selectWeeklySth; + + + foreach my $branch (@branches){ + + $selectWeeklySt = 'SELECT weekday, title FROM repeatable_holidays WHERE branchcode = ? AND weekday IS NOT NULL'; + $selectWeeklySth = $dbh->prepare($selectWeeklySt); + $selectWeeklySth->execute($branch); + + my @weeklyHolidays = (); + + while ( my ($weekDay, $title) = $selectWeeklySth->fetchrow_array ) { + push @weeklyHolidays,{weekday => $weekDay, title=> $title}; + + } + + $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 'isopened' 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)){ + + my $isOpened; + my $specialDescription; + + 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. + $dayOfWeek -=1 unless $dayOfWeek ==7; + $dayOfWeek =0 if $dayOfWeek ==7; + + my $openhour = "select openhour from openinghours where branchcode=? and weekcode=?"; + my $stmt = $dbh->prepare($openhour); + $stmt->execute($branch,$dayOfWeek); + $openhour = $stmt->fetchrow_array; + my $closehour = "select closehour from openinghours where branchcode=? and weekcode=?"; + $stmt = $dbh->prepare($closehour); + $stmt->execute($branch,$dayOfWeek); + $closehour = $stmt->fetchrow_array; + + # Finds closed days + $isOpened =1; + $specialDescription = ""; + foreach my $holidayWeekDay (@{$repeatableHolidaysPerBranch{$branch}}){ + if($dayOfWeek == $holidayWeekDay->{weekday}){ + $isOpened = 0; + $specialDescription = $holidayWeekDay->{title}; + } + } + + foreach my $specialDate (@{$specialHolidaysPerBranch{$branch}}){ + if($tempDate->datetime() eq $specialDate->{date}->datetime() ){ + $isOpened = 0; + $specialDescription = $specialDate->{title}; + } + } + + #final insert statement + $insertDateSt = 'INSERT INTO discrete_calendar (date,branchcode,isopened,note,openhour,closehour) VALUES (?,?,?,?,?,?)'; + $insertDateSth = $dbh->prepare($insertDateSt); + $insertDateSth->execute($tempDate,$branch,$isOpened,$specialDescription,$openhour,$closehour); + } + } +#} --- a/tools/exceptionHolidays.pl +++ a/tools/exceptionHolidays.pl @@ -11,7 +11,7 @@ use DateTime; use C4::Calendar; use Koha::DateUtils; - +use DateTime::Format::Strptime; my $input = new CGI; my $dbh = C4::Context->dbh(); @@ -34,7 +34,96 @@ if ($description) { $description =~ s/\n/\\n/g; } else { $description = ''; -} +} + + +my $startDate = DateTime->new(year => $year, month => $month, day => $day); +my $endDate = dt_from_string( scalar $input->param('datecancelrange') ) || '' if $input->param('datecancelrange'); + +sub delete_holidays{ + my ($branchcode, $weekday, $day, $month) = @_; + $weekday+=1; + my $today = DateTime->today; + my $query; + my $stmt; + + if($holidaytype eq 'weekday') { + #This one deletes a weekly repeated holiday from the full table + $query = "update discrete_calendar set isOpened=1, note='' where branchcode=? and DAYOFWEEK(date)=? and date >=?"; + $stmt = $dbh->prepare($query); + + $stmt->execute($branchcode,$weekday,$today); + }elsif ($holidaytype eq 'ymd'){ + unless($input->param('showOperation') eq 'deleterange'){ + $query = "update discrete_calendar set isOpened=1, note='' where branchcode=? and date=?"; + $stmt = $dbh->prepare($query); + + $stmt->execute($branchcode, $startDate); + }else{ + $startDate = output_pref( { dt => $startDate, dateonly => 1, dateformat => 'iso' } ); + $endDate = output_pref( { dt => $endDate, dateonly => 1, dateformat => 'iso' } ); + $query = "update discrete_calendar set isOpened=1, note='' where branchcode=? and (date between ? and ?)"; + $stmt = $dbh->prepare($query); + + $stmt->execute($branchcode, $startDate, $endDate); + } + + }elsif ($holidaytype eq 'daymonth'){ + unless($endDate){ + #This one deletes a given holiday day and repeats for the ful calendar ex: 03 august of every year in the table + $query = "update discrete_calendar set isOpened=1, note='' where branchcode=? and MONTH(date)=? and DAY(date)=? and date >=?"; + $stmt = $dbh->prepare($query); + $stmt->execute($branchcode, $month, $day, $today); + }elsif($input->param('showOperation') eq 'deleterangerepeat'){ + #This one deletes a range from startDate to endDate and repeats it for the full table + my $parser = DateTime::Format::Strptime->new( + pattern => '%m-%d', + on_error => 'croak', + ); + $startDate = $parser->format_datetime($startDate); + $endDate = $parser->format_datetime($endDate); + + $query = "update discrete_calendar set isOpened=1, note='' where branchcode=? and (DATE_FORMAT(date,'\%m-\%d') BETWEEN ? AND ? ) and date > ?"; + $stmt = $dbh->prepare($query); + $stmt->execute($branchcode, $startDate, $endDate, $today); + } elsif($input->param('showOperation') eq 'deleterange'){ + #This one deletes a range from startDate to endDate + $startDate = output_pref( { dt => $startDate, dateonly => 1, dateformat => 'iso' } ); + $endDate = output_pref( { dt => $endDate, dateonly => 1, dateformat => 'iso' } ); + $query = "update discrete_calendar set isOpened=1, note='' where branchcode=? and (date between ? and ?)"; + $stmt = $dbh->prepare($query); + + $stmt->execute($branchcode, $startDate, $endDate); + } + } +} + +sub edit_holiday{ + my ($branchcode, $title, $weekday, $day, $month) = @_; + $weekday+=1; + my $today = DateTime->today; + my $query; + my $stmt; + + if($holidaytype eq 'weekday') { + #This one deletes a weekly repeated holiday from the full table + $query = "update discrete_calendar set note=? where branchcode=? and DAYOFWEEK(date)=? and date >=?"; + $stmt = $dbh->prepare($query); + + $stmt->execute($title, $branchcode,$weekday,$today); + } elsif ($holidaytype eq 'daymonth') { + #This one deletes a given holiday day and repeats for the ful calendar ex: 03 august of every year in the table + $query = "update discrete_calendar set note=? where branchcode=? and MONTH(date)=? and DAY(date)=? and date >=?"; + $stmt = $dbh->prepare($query); + $stmt->execute($title, $branchcode, $month, $day, $today); + } elsif ($holidaytype eq 'ymd') { + #This one deletes only a holiday based on a given date + $query = "update discrete_calendar set note=? where branchcode=? and date=?"; + $stmt = $dbh->prepare($query); + + $stmt->execute($title,$branchcode, $startDate); + } +} # We make an array with holiday's days my @holiday_list; @@ -89,11 +178,13 @@ if ($input->param('showOperation') eq 'exception') { title => $title, description => $description); } + edit_holiday($branchcode, $title, $weekday, $day, $month); } elsif ($input->param('showOperation') eq 'delete') { $calendar->delete_holiday(weekday => $weekday, day => $day, month => $month, year => $year); + delete_holidays($branchcode, $weekday, $day, $month); }elsif ($input->param('showOperation') eq 'deleterange') { if (@holiday_list){ foreach my $date (@holiday_list){ @@ -103,6 +194,7 @@ if ($input->param('showOperation') eq 'exception') { year => $date->{local_c}->{year}); } } + delete_holidays($branchcode, $weekday, $day, $month); }elsif ($input->param('showOperation') eq 'deleterangerepeat') { if (@holiday_list){ foreach my $date (@holiday_list){ @@ -111,6 +203,7 @@ if ($input->param('showOperation') eq 'exception') { month => $date->{local_c}->{month}); } } + delete_holidays($branchcode, $weekday, $day, $month); }elsif ($input->param('showOperation') eq 'deleterangerepeatexcept') { if (@holiday_list){ foreach my $date (@holiday_list){ @@ -120,5 +213,6 @@ if ($input->param('showOperation') eq 'exception') { year => $date->{local_c}->{year}); } } + delete_holidays($branchcode, $weekday, $day, $month); } print $input->redirect("/cgi-bin/koha/tools/holidays.pl?branch=$branchcode&calendardate=$calendardate"); --- a/tools/newHolidays.pl +++ a/tools/newHolidays.pl @@ -9,12 +9,11 @@ use CGI qw ( -utf8 ); use C4::Auth; use C4::Output; - use Koha::Cache; - use C4::Calendar; use DateTime; use Koha::DateUtils; +use DateTime::Format::Strptime; my $input = new CGI; my $dbh = C4::Context->dbh(); @@ -58,23 +57,116 @@ if ($end_dt){ if($allbranches) { my $branch; - my @branchcodes = split(/\|/, $input->param('branchCodes')); + my @branchcodes = split(/\|/, $input->param('branchCodes')); foreach $branch (@branchcodes) { add_holiday($newoperation, $branch, $weekday, $day, $month, $year, $title, $description); + addHoliday($newoperation, $branch, $weekday, $day, $month, $title); } } else { add_holiday($newoperation, $branchcode, $weekday, $day, $month, $year, $title, $description); + addHoliday($newoperation, $branchcode, $weekday, $day, $month, $title); } print $input->redirect("/cgi-bin/koha/tools/holidays.pl?branch=$originalbranchcode&calendardate=$calendardate"); +sub isOpened{ + my ($branchcode, $date) = @_; + my $isopened; + my $query = "select isopened from discrete_calendar where branchcode=? and date=?"; + my $stmt = $dbh->prepare($query); + $stmt->execute($branchcode, $date); + $isopened = $stmt->fetchrow_array; + + return $isopened; +} +sub addHoliday{ + my ($newoperation, $branchcode, $weekday, $day, $month, $title) = @_; + my $startDate = output_pref( { dt => $first_dt, dateonly => 1, dateformat => 'iso' } ); + my $endDate = output_pref( { dt => $end_dt, dateonly => 1, dateformat => 'iso' } ); + + if($newoperation eq 'holiday'){ + if (isOpened($branchcode,$startDate)) { + insert_single_day($branchcode, $title); + } + }elsif($newoperation eq 'weekday'){ + if (isOpened($branchcode,$startDate)) { + insert_weekday_day($branchcode, $weekday, $title); + } + }elsif($newoperation eq 'repeatable'){ + if (isOpened($branchcode,$startDate)) { + insert_yearly_holiday($branchcode, $day, $month, $title); + } + }elsif($newoperation eq 'holidayrange'){ + insert_range_holiday($branchcode, $title); + }elsif($newoperation eq 'holidayrangerepeat'){ + insert_range_holiday_repeat($branchcode, $title); + } +} + +sub insert_single_day{ + my ($branchcode,$title) = @_; + my $startDate = output_pref( { dt => $first_dt, dateonly => 1, dateformat => 'iso' } ); + my $today = DateTime->today; + my $query = "update discrete_calendar set isOpened=0, note=? where branchcode=? and date=? and date>=?"; + my $stmt = $dbh->prepare($query); + + $stmt->execute($title, $branchcode, $startDate, $today); + +} + +sub insert_weekday_day{ + my ($branchcode, $weekday, $title) = @_; + $weekday+=1; + my $startDate = output_pref( { dt => $first_dt, dateonly => 1, dateformat => 'iso' } ); + my $today = DateTime->today; + my $query = "update discrete_calendar set isOpened=0, note=? where branchcode=? and DAYOFWEEK(date)=? and date >=?"; + my $stmt = $dbh->prepare($query); + + $stmt->execute($title,$branchcode,$weekday,$today); +} + +sub insert_yearly_holiday{ + my ($branchcode, $day, $month, $title) = @_; + my $startDate = output_pref( { dt => $first_dt, dateonly => 1, dateformat => 'iso' } ); + my $today = DateTime->today; + my $query = "update discrete_calendar set isOpened=0, note=? where branchcode=? and MONTH(date)=? and DAY(date)=? and date >=?"; + my $stmt = $dbh->prepare($query); + + $stmt->execute($title,$branchcode,$month,$day,$today); +} + +sub insert_range_holiday{ + my ($branchcode, $title) = @_; + my $startDate = output_pref( { dt => $first_dt, dateonly => 1, dateformat => 'iso' } ); + my $endDate = output_pref( { dt => $end_dt, dateonly => 1, dateformat => 'iso' } ); + my $query = "update discrete_calendar set isOpened=0, note=? where branchcode=? and (date between ? and ?)"; + my $stmt = $dbh->prepare($query); + + $stmt->execute($title,$branchcode, $startDate, $endDate); +} + +sub insert_range_holiday_repeat{ + my ($branchcode, $title) = @_; + my $parser = DateTime::Format::Strptime->new( + pattern => '%m-%d', + on_error => 'croak', + ); + my $startDate = $parser->format_datetime($first_dt); + my $endDate = $parser->format_datetime($end_dt); + my $today = DateTime->today; + my $query = "update discrete_calendar set isOpened=0, note=? where branchcode=? and isOpened=1 and (DATE_FORMAT(date,'\%m-\%d') BETWEEN ? AND ? ) and date > ?"; + my $stmt = $dbh->prepare($query); + + $stmt->execute($title,$branchcode, $startDate, $endDate,$today); +} + #FIXME: move add_holiday() to a better place sub add_holiday { - ($newoperation, $branchcode, $weekday, $day, $month, $year, $title, $description) = @_; + ($newoperation, $branchcode, $weekday, $day, $month, $year, $title, $description) = @_; my $calendar = C4::Calendar->new(branchcode => $branchcode); if ($newoperation eq 'weekday') { - unless ( $weekday && ($weekday ne '') ) { + unless ( $weekday && ($weekday ne '') ) { # was dow calculated by javascript? original code implies it was supposed to be. # if not, we need it. $weekday = &Date::Calc::Day_of_Week($year, $month, $day) % 7 unless($weekday); --