From 365ecbe878db5e0780632b87ff57b731b1fa313b 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 | 20 + ...ug_17015_part2_fill_discrete_calendar.perl | 150 ++++ .../bug_17015_part3_drop_calendar.perl | 11 + installer/data/mysql/updatedatabase.pl | 6 +- .../en/modules/tools/discrete_calendar.tt | 705 +++++++++--------- misc/cronjobs/staticfines.pl | 2 +- tools/discrete_calendar.pl | 14 +- 7 files changed, 546 insertions(+), 362 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..f276da11a3 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_part1_create_discrete_calendar.perl @@ -0,0 +1,20 @@ +$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 '', + `description` mediumtext 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 adbd5e4c50..508d1dfaa2 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -17806,7 +17806,7 @@ if ( CheckVersion($DBversion) ) { $DBversion = '16.12.00.032'; if ( CheckVersion($DBversion) ) { - require Koha::Calendar; + require Koha::DiscreteCalendar; $dbh->do( q{ @@ -17839,10 +17839,10 @@ if ( CheckVersion($DBversion) ) { my $expirationdate = dt_from_string( $hold->{waitingdate} ); if ( C4::Context->preference("ExcludeHolidaysFromMaxPickUpDelay") ) { - my $calendar = Koha::Calendar->new( + 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 ); diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt index 78f0a5e44a..0c814426ce 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt @@ -1,9 +1,15 @@ [% USE raw %] [% USE Asset %] [% USE Branches %] +[% PROCESS 'i18n.inc' %] [% SET footerjs = 1 %] [% INCLUDE 'doc-head-open.inc' %] -[% Branches.GetName( branch ) | html %] calendar › Tools › Koha +[% FILTER collapse %] + [% tx("{library} calendar", { library = Branches.GetName( branch ) }) | html %] + › [% t("Tools") | html %] › [% t("Koha") | html %] + [% END %] [% INCLUDE 'doc-head-close.inc' %] [% Asset.css("css/calendar.css") | $raw %] @@ -19,365 +25,362 @@ Tools [% END %] [% WRAPPER breadcrumb_item bc_active= 1 %] - [% Branches.GetName( branch ) | html %] calendar + [% tx("{library} calendar", { library = Branches.GetName( branch ) }) | html %] [% END %] [% END #/ WRAPPER breadcrumbs %] [% END #/ WRAPPER sub-header.inc %] -
-
-
-
- [% IF no_branch_selected %] -
- No library set! -
- [% END %] - - [% UNLESS datesInfos %] -
- Error! You have to run add_days_discrete_calendar.pl in order to use Discrete Calendar. -
- [% END %] +[% WRAPPER 'main-container.inc' aside='tools-menu' %] + [% IF no_branch_selected %] +
+ No library set! +
+ [% END %] + + [% UNLESS datesInfos %] +
+ Error! You have to run add_days_discrete_calendar.pl in order to use Discrete Calendar. +
+ [% END %] + + [% IF date_format_error %] +
+ Error! Date format error. Please try again. +
+ [% END %] + + [% IF cannot_edit_past_dates %] +
+ Error! You cannot edit the past. The date '[% error_date | html %]' was out of range for the requested action. +
+ [% END %] + +

[% tx("{library} calendar", { library = Branches.GetName( branch ) }) | html %]

- [% IF date_format_error %] -
- Error! Date format error. Please try again. -
- [% END %] - - [% IF cannot_edit_past_dates %] -
- Error! You cannot edit the past. The date '[% error_date | html %]' was out of range for the requested action. -
- [% END %] - -

[% Branches.GetName( branch ) | html %] calendar

- -
-
-
- -
- - Copy calendar to - + [% PROCESS options_for_libraries libraries => Branches.all( selected => branch ) %] + + + + + +
+ +

Calendar information

+ +
+ + + +
+
+ [% INCLUDE 'csrf-token.inc' %] +
+

Edit date details

+ +
    +
  1. + Library: + + +
  2. +
  3. + From date: + , + + [% IF ( dateformat == "us" ) %] + // + [% ELSIF ( dateformat == "metric" ) %] + // + [% ELSIF ( dateformat == "dmydot" ) %] + .. + [% ELSE %] + // [% END %] - [% END %] - - - -
  4. - -

    Calendar information

    - -
    - - - -
    -
    -
    -

    Edit date details

    - + + + + + + +
  5. + To date: + +
  6. +
  7. + + +
  8. +
  9. + + +
  10. +
  11. + + + [?] +
      -
    1. - Library: - - -
    2. -
    3. - From date: - , - - [% IF ( dateformat == "us" ) %] - // - [% ELSIF ( dateformat == "metric" ) %] - // - [% ELSIF ( dateformat == "dmydot" ) %] - .. - [% ELSE %] - // - [% END %] - - - - - -
    4. -
    5. - To date: - -
    6. -
    7. - - -
    8. -
    9. - - -
    10. -
    11. - - - [?] -
      -
        -
      1. Working day: the library is open on that day.
      2. -
      3. Unique holiday: make a single holiday. For example, selecting August 1, 2012 will make it a holiday, but will not affect August 1 in other years.
      4. -
      5. Weekly holiday: make this weekday a holiday, every week. For example, if your library is closed on Saturdays, use this option to make every Saturday a holiday.
      6. -
      7. Repeatable holiday: this will take this day and month as a reference to make it a holiday. Through this option, you can repeat this rule for every year. For example, selecting August 1 will make August 1 a holiday every year.
      8. -
      9. Floating holiday: this will take this day and month as a reference to make it a floating holiday. Through this option, you can add a holiday that repeats every year but not necessarily on the exact same day. On subsequent years the date will need validation.
      10. -
      11. Need validation: this holiday has been added automatically, but needs to be validated.
      12. -
      -
      -
    12. -
    13. - - -
    14. -
    15. - - [?] -
      Remove all repeated or weekly holidays of the selected date or week day
      if working day is selected.
      -
    16. -
    17. - -
    18. -
    19. - -
    20. -
    21. - - -
    22. -
    23. - - -
    24. -
    25. - - - - -
    26. -
    27. - - . - [?] -
      If checked, this holiday will be copied to all libraries.
      -
    28. +
    29. Working day: the library is open on that day.
    30. +
    31. Unique holiday: make a single holiday. For example, selecting August 1, 2012 will make it a holiday, but will not affect August 1 in other years.
    32. +
    33. Weekly holiday: make this weekday a holiday, every week. For example, if your library is closed on Saturdays, use this option to make every Saturday a holiday.
    34. +
    35. Repeatable holiday: this will take this day and month as a reference to make it a holiday. Through this option, you can repeat this rule for every year. For example, selecting August 1 will make August 1 a holiday every year.
    36. +
    37. Floating holiday: this will take this day and month as a reference to make it a floating holiday. Through this option, you can add a holiday that repeats every year but not necessarily on the exact same day. On subsequent years the date will need validation.
    38. +
    39. Need validation: this holiday has been added automatically, but needs to be validated.
    - - - - - - - - - -
    - - Cancel -
    -
  12. -
    -
    -
    -
-
- -
-
-
-

Hints

-
    -
  • Search in the calendar the day you want to set as holiday.
  • -
  • Click the date to add or edit a holiday.
  • -
  • Enter a title and description for the holiday.
  • -
  • Specify how the holiday should repeat.
  • -
  • Click Save to finish.
  • -
  • PS: -
      -
    • Past dates cannot be changed
    • -
    • Weekly holidays change open/close hours for all the days affected unless inputs are empty
    • -
    +
- -

Key

-

- Working day - Unique holiday - Holiday repeating weekly - Holiday repeating yearly - Floating holiday - Need validation -

-
- -
- [% IF ( NEED_VALIDATION_HOLIDAYS ) %] -

Need validation holidays

- - - - - - - - - - [% FOREACH need_validation_holiday IN NEED_VALIDATION_HOLIDAYS %] - - - - - - [% END %] - - - [% END # /IF ( EXCEPTION_HOLIDAYS_LOOP ) %] - - [% IF ( WEEKLY_HOLIDAYS ) %] -

Weekly - Repeatable holidays

- - - - - - - - - - [% FOREACH WEEK_DAYS_LOO IN WEEKLY_HOLIDAYS %] - - - - - - [% END %] - - - [% END # / IF ( WEEKLY_HOLIDAYS ) %] - - [% IF ( REPEATABLE_HOLIDAYS ) %] -

Yearly - Repeatable holidays

- - - - [% IF ( dateformat == "metric" ) %] - - [% ELSE %] - - [% END %] - - - - - - [% FOREACH DAY_MONTH_HOLIDAYS_LOO IN REPEATABLE_HOLIDAYS %] - - [% IF ( dateformat == "metric" ) %] - - [% ELSE %] - - [% END %] - - - - [% END %] - - - [% END # /IF ( REPEATABLE_HOLIDAYS ) %] - - [% IF ( UNIQUE_HOLIDAYS ) %] -

Unique holidays

- - - - - - - - - - - [% FOREACH HOLIDAYS_LOO IN UNIQUE_HOLIDAYS %] - - - - - - [% END %] - - - [% END # /IF ( UNIQUE_HOLIDAYS ) %] - - [% IF ( FLOAT_HOLIDAYS ) %] -

Floating holidays

- - - - - - - - - - - [% FOREACH float_holiday IN FLOAT_HOLIDAYS %] - - - - - - [% END %] - - - [% END # /IF ( FLOAT_HOLIDAYS ) %] -
-
-
-
-
-
- -
- -
-
+
  • + + +
  • +
  • + + [?] +
    Remove all repeated or weekly holidays of the selected date or week day
    if working day is selected.
    +
  • +
  • + +
  • +
  • + +
  • +
  • + + +
  • +
  • + + +
  • +
  • + + + + +
  • +
  • + + . + [?] +
    If checked, this holiday will be copied to all libraries.
    +
  • + + + + + + + + + + +
    + + Cancel +
    + + +
    + + + + +
    +
    +
    +

    Hints

    +
      +
    • Search in the calendar the day you want to set as holiday.
    • +
    • Click the date to add or edit a holiday.
    • +
    • Enter a title and description for the holiday.
    • +
    • Specify how the holiday should repeat.
    • +
    • Click Save to finish.
    • +
    • PS: +
        +
      • Past dates cannot be changed
      • +
      • Weekly holidays change open/close hours for all the days affected unless inputs are empty
      • +
      +
    • +
    +

    Key

    +

    + Working day + Unique holiday + Holiday repeating weekly + Holiday repeating yearly + Floating holiday + Need validation +

    +
    + +
    + [% IF ( NEED_VALIDATION_HOLIDAYS ) %] +

    Need validation holidays

    + + + + + + + + + + [% FOREACH need_validation_holiday IN NEED_VALIDATION_HOLIDAYS %] + + + + + + [% END %] + + + [% END # /IF ( EXCEPTION_HOLIDAYS_LOOP ) %] + + [% IF ( WEEKLY_HOLIDAYS ) %] +

    Weekly - Repeatable holidays

    + + + + + + + + + + [% FOREACH WEEK_DAYS_LOO IN WEEKLY_HOLIDAYS %] + + + + + + [% END %] + + + [% END # / IF ( WEEKLY_HOLIDAYS ) %] + + [% IF ( REPEATABLE_HOLIDAYS ) %] +

    Yearly - Repeatable holidays

    + + + + [% IF ( dateformat == "metric" ) %] + + [% ELSE %] + + [% END %] + + + + + + [% FOREACH DAY_MONTH_HOLIDAYS_LOO IN REPEATABLE_HOLIDAYS %] + + [% IF ( dateformat == "metric" ) %] + + [% ELSE %] + + [% END %] + + + + [% END %] + + + [% END # /IF ( REPEATABLE_HOLIDAYS ) %] + + [% IF ( UNIQUE_HOLIDAYS ) %] +

    Unique holidays

    + + + + + + + + + + + [% FOREACH HOLIDAYS_LOO IN UNIQUE_HOLIDAYS %] + + + + + + [% END %] + + + [% END # /IF ( UNIQUE_HOLIDAYS ) %] + + [% IF ( FLOAT_HOLIDAYS ) %] +

    Floating holidays

    + + + + + + + + + + + [% FOREACH float_holiday IN FLOAT_HOLIDAYS %] + + + + + + [% END %] + + + [% END # /IF ( FLOAT_HOLIDAYS ) %] +
    + +
    + +
    + + + +[% END %] [% MACRO jsinclude BLOCK %] [% INCLUDE 'calendar.inc' %] diff --git a/misc/cronjobs/staticfines.pl b/misc/cronjobs/staticfines.pl index 0cbee81582..b060497d17 100755 --- a/misc/cronjobs/staticfines.pl +++ b/misc/cronjobs/staticfines.pl @@ -32,12 +32,12 @@ use Date::Calc qw( Date_to_Days ); use Koha::Script -cron; use C4::Context; use C4::Overdues qw( CalcFine checkoverdues GetFine Getoverdues ); -use C4::DiscreteCalendar qw(); # don't need any exports from Calendar use C4::Log qw( cronlogaction ); use Getopt::Long qw( GetOptions ); use List::MoreUtils qw( none ); use Koha::DateUtils qw( dt_from_string output_pref ); use Koha::Patrons; +use Koha::DiscreteCalendar; my $help = 0; my $verbose = 0; diff --git a/tools/discrete_calendar.pl b/tools/discrete_calendar.pl index 71c46a9e8d..4633bc9df6 100755 --- a/tools/discrete_calendar.pl +++ b/tools/discrete_calendar.pl @@ -20,21 +20,21 @@ use Modern::Perl; use CGI qw ( -utf8 ); -use C4::Auth qw( get_template_and_user ); +use C4::Auth qw( get_template_and_user ); use C4::Output qw( output_html_with_http_headers ); -use Koha::DateUtils qw ( dt_from_string output_pref ); +use Koha::DateUtils qw( dt_from_string output_pref ); use Koha::DiscreteCalendar; my $input = CGI->new; # Get the template to use -my ($template, $loggedinuser, $cookie) = get_template_and_user( +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( { template_name => "tools/discrete_calendar.tt", - type => "intranet", - query => $input, - flagsrequired => {tools => 'edit_calendar'}, + type => "intranet", + query => $input, + flagsrequired => { tools => 'edit_calendar' }, } ); @@ -57,7 +57,7 @@ my $action = $input->param('action') || ''; # if the url has an invalid date default to 'now.' # FIXME There is something to improve in the date handling here my $calendarinput_dt = eval { dt_from_string( scalar $input->param('calendardate') ); } || dt_from_string; -my $calendardate = output_pref( { dt => $calendarinput_dt, dateonly => 1 } ); +my $calendardate = output_pref( { dt => $calendarinput_dt, dateonly => 1 } ); if ($action eq 'copyBranch') { my $new_branch = scalar $input->param('newBranch'); -- 2.43.0