From 9514702050b6324a717c37590c3eb859d7389cda Mon Sep 17 00:00:00 2001 From: mehdi Date: Tue, 9 Aug 2016 15:03:29 -0400 Subject: [PATCH] Bug 17015 - New koha calendar --- .../atomicupdate/bug_17015_add_holidaytype.sql | 9 + .../prog/en/modules/tools/discrete_calendar.tt | 749 +++++++++++++++++++++ .../prog/en/modules/tools/tools-home.tt | 15 +- misc/cronjobs/add_days_discrete_calendar.pl | 117 ++++ misc/generate_discrete_calendar.pl | 172 +++++ tools/discrete_calendar.pl | 476 +++++++++++++ 6 files changed, 1533 insertions(+), 5 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17015_add_holidaytype.sql create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt create mode 100755 misc/cronjobs/add_days_discrete_calendar.pl create mode 100755 misc/generate_discrete_calendar.pl create mode 100755 tools/discrete_calendar.pl diff --git a/installer/data/mysql/atomicupdate/bug_17015_add_holidaytype.sql b/installer/data/mysql/atomicupdate/bug_17015_add_holidaytype.sql new file mode 100644 index 0000000..9f4aa30 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17015_add_holidaytype.sql @@ -0,0 +1,9 @@ +CREATE TABLE `discrete_calendar` ( + `date` datetime not NULL, + `branchcode` varchar(10) NOT NULL, + `isopened` tinyint(1) DEFAULT 1, + `holidaytype` varchar(1) default '', + `note` varchar(30) DEFAULT NULL, + `openhour` time, + `closehour` time, + PRIMARY KEY (`branchcode`,`date`)); 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 new file mode 100644 index 0000000..a3c8e24 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt @@ -0,0 +1,749 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Tools › [% branchname %] calendar +[% INCLUDE 'doc-head-close.inc' %] +[% INCLUDE 'calendar.inc' %] + + + +[% INCLUDE 'datatables.inc' %] + + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
+ +
+
+
+

[% branchname %] calendar

+
+
+ + + +

Calendar information

+
+
+
+ + +
+ + + + + + +
+
+ +
+

Edit this holiday

+ +
    +
  1. + Library: + +
  2. +
  3. + From date: + , + + [% IF ( dateformat == "us" ) %]//[% ELSIF ( dateformat == "metric") %]//[% ELSIF ( dateformat == "dmydot") %]..[% ELSE %]//[% END %] + + + + + + +
  4. +
  5. + To Date : + +
  6. +
  7. + +
  8. + [?] +
    This will delete this holiday only.
  9. +
  10. + [?] +
    This will delete all the holidays of this type expect floats. If it's a weekly holiday on the Mondays, this will remove all the upcoming Mondays holidays. If it's a yearly holiday on 25 December, + this will delete all the upcoming holidays on the 25 December
  11. +
  12. . + [?] +
    This will delete all the holidays between two given dates.
    +
  13. +
  14. + [?] +
    This will save changes to the holiday's title and description. If the information for a repeatable holiday is modified, it affects all of the dates on which the holiday is repeated.
  15. +
+
+ + + Cancel +
+
+
+
+ +
+
+ +
+

Copy holiday

+ +
    +
  1. + Library: + +
  2. +
  3. +

    Copy from :

    +
  4. +
  5. + From Date : + + To Date : + +
  6. +
  7. +

    Apply To :

    +
  8. +
  9. + From Date : + + To Date : + +
  10. + +
+
+ + + + + + + + + Cancel +
+
+
+ +
+
+

Copy entire calendar

+ + + + + +
+
+
+ + +
+
+ +
+

Add new holiday

+
    +
  1. + Library: + + +
  2. +
  3. + From date: + , + + [% IF ( dateformat == "us" ) %]//[% ELSIF ( dateformat == "metric" ) %]//[% ELSIF ( dateformat == "dmydot" ) %]..[% ELSE %]//[% END %] + + + + + + +
  4. +
  5. + To date: + +
  6. +
  7. +
  8. + . + [?] +
    Make a single holiday. For example, selecting August 1, 2012 will make it a holiday, but will not affect August 1 in other years.
    +
  9. +
  10. + . + [?] +
    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.
    +
  11. +
  12. + . + [?] +
    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.
    +
  13. +
  14. + . + [?] +
    Make a single holiday on a range. For example, selecting August 1, 2012 and August 10, 2012 will make all days between August 1 and 10 a holiday, but will not affect August 1-10 in other years.
    +
  15. +
  16. + . + [?] +
    Make a single holiday on a range repeated yearly. For example, selecting August 1, 2012 and August 10, 2012 will make all days between August 1 and 10 a holiday, and will affect August 1-10 in other years.
    +
  17. +
  18. + + . + [?] +
    If checked, this holiday will be copied to all libraries. If the holiday already exists for a library, no change is made.
    +
+
+ + + Cancel +
+
+
+
+ + + + + + + + + +
+
+
+

Hints

+
    +
  • Search in the calendar the day you want to set as holiday.
  • +
  • Click the date to add or edit a holiday.
  • +
  • Specify how the holiday should repeat.
  • +
  • Click Save to finish.
  • +
+

Key

+

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

+
+
+ +[% IF ( week_days ) %] +

Weekly - Repeatable holidays

+ + + + + + + + + [% FOREACH WEEK_DAYS_LOO IN week_days %] + + + + + + [% END %] + +
Day of weekTitle
[% WEEK_DAYS_LOO.weekday %][% WEEK_DAYS_LOO.note %]
+[% END %] + +[% IF ( repeatable_holidays ) %] +

Yearly - Repeatable holidays

+ + + + [% IF ( dateformat == "metric" ) %] + + [% ELSE %] + + [% END %] + + + + + [% FOREACH DAY_MONTH_HOLIDAYS_LOO IN repeatable_holidays %] + + + + + [% END %] + +
Day/monthMonth/dayTitle
[%DAY_MONTH_HOLIDAYS_LOO.month%]/[% DAY_MONTH_HOLIDAYS_LOO.day %][% DAY_MONTH_HOLIDAYS_LOO.note %]
+[% END %] + +[% IF ( HOLIDAYS_LOOP ) %] +

Unique upcoming holidays

+ + + + + + + + + [% FOREACH HOLIDAYS_LOO IN HOLIDAYS_LOOP %] + + + + + [% END %] + +
DateTitle
[% HOLIDAYS_LOO.outputdate %][% HOLIDAYS_LOO.note %]
+[% END %] +
+
+
+
+
+ +
+[% INCLUDE 'tools-menu.inc' %] +
+
+[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt index 09bc4b6..a4b808b 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt @@ -24,7 +24,7 @@
Comments [% IF ( pendingcomments ) %][% pendingcomments %][% END %]
Moderate patron comments.
[% END %] - + [% IF ( CAN_user_tools_import_patrons ) %]
Import patrons
Import patron data
@@ -39,7 +39,7 @@
Overdue notice/status triggers
Set notice/status triggers for overdue items
[% END %] - + [% IF ( CAN_user_tools_label_creator ) %]
Patron card creator
Create printable patron cards
@@ -74,6 +74,11 @@
Define days when the library is closed
[% END %] + [% IF ( CAN_user_tools_edit_calendar ) %] +
Discrete calendar
+
Define days when the library is closed
+ [% END %] + [% IF ( CAN_user_tools_manage_csv_profiles ) %]
CSV profiles
Manage CSV export profiles
@@ -93,7 +98,7 @@
Task scheduler
Schedule tasks to run
[% END %] - + [% IF ( CAN_user_tools_edit_quotes ) %]
Edit quotes for QOTD feature
Quote editor for Quote-of-the-day feature in OPAC
@@ -118,7 +123,7 @@
Batch item deletion
Delete a batch of items
[% END %] - + [% IF ( CAN_user_tools_items_batchmod ) %]
Batch item modification
Modify items in a batch
@@ -153,7 +158,7 @@
Label creator
Create printable labels and barcodes from catalog data
[% END %] - + [% IF ( CAN_user_tools_label_creator ) %]
Quick spine label creator
Enter a barcode to generate a printable spine label. For use with dedicated label printers
diff --git a/misc/cronjobs/add_days_discrete_calendar.pl b/misc/cronjobs/add_days_discrete_calendar.pl new file mode 100755 index 0000000..714338f --- /dev/null +++ b/misc/cronjobs/add_days_discrete_calendar.pl @@ -0,0 +1,117 @@ +#!/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; +my $query = "select distinct weekday(date), note from discrete_calendar where holidaytype='W'"; +my $stmt = $dbh->prepare($query); +$stmt->execute(); +my @week_days_discrete; +while (my ($weekday,$note) = $stmt->fetchrow_array){ + push @week_days_discrete, {weekday => $weekday, note => $note}; +} + +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 +$query = "select max(date) from discrete_calendar"; +$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(); +$endDate->add(days => 20); +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 $last_year = 'select isopened,holidaytype,note 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($last_year); + $stmt->execute($yearAgo,$branchCode); + my ($isOpened, $holidaytype, $note) = $stmt->fetchrow_array; + if ($holidaytype && $holidaytype eq "W" || $holidaytype eq 'E'){ + $isOpened = 1; + $note =''; + }elsif ($holidaytype && $holidaytype eq "F"){ + $holidaytype = 'N'; + } + $holidaytype = '' if $isOpened; + my $add_Day = 'INSERT INTO discrete_calendar (date,branchcode,isopened,holidaytype, note,openhour,closehour) VALUES (?,?,?,?,?,?,?)'; + $stmt = $dbh->prepare($add_Day); + $stmt->execute($newDay,$branchCode,$isOpened,$holidaytype,$note, $openhour,$closehour); + } +} diff --git a/misc/generate_discrete_calendar.pl b/misc/generate_discrete_calendar.pl new file mode 100755 index 0000000..5f5f5f0 --- /dev/null +++ b/misc/generate_discrete_calendar.pl @@ -0,0 +1,172 @@ +#!/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, 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 '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)){ + 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 + my $isOpened =1; + my $specialDescription = ""; + my $holidaytype =''; + $dayOfWeek = $tempDate->day_of_week; + foreach my $holidayWeekDay (@{$repeatableHolidaysPerBranch{$branch}}){ + if($holidayWeekDay->{weekday} && $dayOfWeek == $holidayWeekDay->{weekday}){ + $isOpened = 0; + $specialDescription = $holidayWeekDay->{title}; + $holidaytype = '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) { + $isOpened = 0; + $specialDescription = $holidayWeekDay->{title}; + $holidaytype = 'R'; + } + } + } + + foreach my $specialDate (@{$specialHolidaysPerBranch{$branch}}){ + if($tempDate->datetime() eq $specialDate->{date}->datetime() ){ + $isOpened = 0; + $specialDescription = $specialDate->{title}; + $holidaytype = 'E'; + } + } + #final insert statement + + $insertDateSt = 'INSERT INTO discrete_calendar (date,branchcode,isopened,holidaytype,note,openhour,closehour) VALUES (?,?,?,?,?,?,?)'; + $insertDateSth = $dbh->prepare($insertDateSt); + $insertDateSth->execute($tempDate,$branch,$isOpened,$holidaytype,$specialDescription,$openhour,$closehour); + } + } +#} diff --git a/tools/discrete_calendar.pl b/tools/discrete_calendar.pl new file mode 100755 index 0000000..abfa6be --- /dev/null +++ b/tools/discrete_calendar.pl @@ -0,0 +1,476 @@ +#!/usr/bin/perl + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +#####Sets holiday periods for each branch. Datedues will be extended if branch is closed -TG +use strict; +use warnings; + +use CGI qw ( -utf8 ); +use DateTime; +use DateTime::Format::Strptime; + +use C4::Auth; +use C4::Branch; # GetBranches +use C4::Output; +use Koha::DateUtils; + +my $input = new CGI; +my $today = DateTime->today; +my $dbh = C4::Context->dbh(); + +my $branchcode = $input->param('BranchName'); +my $weekday = $input->param('Weekday'); +my $day = $input->param('Day'); +my $month = $input->param('Month'); +my $year = $input->param('Year'); +my $dateofrange = $input->param('dateofrange'); +my $holidaytype = $input->param('HolidayType'); +my $allbranches = $input->param('allBranches'); +my $operation = $input->param('Operation'); +my $title = $input->param('Title'); +my $first_dt = DateTime->new(year => $year, month => $month, day => $day) if $year && $month && $day; +my $end_dt = eval { dt_from_string( $dateofrange ); }; + +my $query; +my $stmt; +my $action = $input->param('action') || ''; +# Get the template to use +my ($template, $loggedinuser, $cookie) + = get_template_and_user({template_name => "tools/discrete_calendar.tt", + type => "intranet", + query => $input, + authnotrequired => 0, + flagsrequired => {tools => 'edit_calendar'}, + debug => 1, + }); +my $branch= $input->param('branch') || C4::Context->userenv->{'branch'}; + + + +# calendardate - date passed in url for human readability (syspref) +# if the url has an invalid date default to 'now.' +my $calendarinput_dt = eval { dt_from_string( scalar $input->param('calendardate') ); } || dt_from_string; +my $calendardate = output_pref( { dt => $calendarinput_dt, dateonly => 1 } ); + +if($action eq 'add'){ + if($allbranches) { + my $branch; + my @branchcodes = split(/\|/, $input->param('branchCodes')); + foreach $branch (@branchcodes) { + add_Holiday($operation, $branch, $weekday, $day, $month, $title); + } + } else { + add_Holiday($operation, $branchcode, $weekday, $day, $month, $title); + } +}elsif($action eq 'copy'){ + + if($operation eq 'copyBranch'){ + copyToBranch(); + }else{ + copyHoliday($branch); + } +}elsif($action eq 'hourschange'){ + changeHours(); +}elsif( $action eq 'edit'){ + delete_holidays($branchcode, $weekday, $day, $month); +} +sub isOpened{ + my ($branchcode, $date) = @_; + my $query = "select isopened from discrete_calendar where branchcode=? and date=?"; + my $stmt = $dbh->prepare($query); + $stmt->execute($branchcode, $date); + my $isopened = $stmt->fetchrow_array; + + return $isopened; +} + +sub add_Holiday{ + my ($operation, $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($operation eq 'holiday'){ + if (isOpened($branchcode,$startDate)) { + insert_single_day($branchcode, $title); + } + }elsif($operation eq 'weekday'){ + if (isOpened($branchcode,$startDate)) { + insert_weekday_day($branchcode, $weekday, $title); + } + }elsif($operation eq 'repeatable'){ + if (isOpened($branchcode,$startDate)) { + insert_yearly_holiday($branchcode, $day, $month, $title); + } + }elsif($operation eq 'holidayrange'){ + insert_range_holiday($branchcode, $title); + }elsif($operation 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 $query = "update discrete_calendar set isOpened=0, note=?, holidaytype='E' where branchcode=? and isOpened=1 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=?, holidaytype='W' where branchcode=? and isOpened=1 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=?, holidaytype='R' where branchcode=? and isOpened=1 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=?, holidaytype='E' where branchcode=? and isOpened=1 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=?, holidaytype='R' 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); +} + +sub copyHoliday{ + # ADD BRANCH CODE TO QUEIRES + my ($branch) = @_; + my $from_copyFrom = $input->param('from_copyFrom') || ''; + my $from_copyTo = $input->param('from_copyTo') || ''; + my $to_copyFrom = $input->param('to_copyFrom') || ''; + my $to_copyTo = $input->param('to_copyTo') || ''; + my $daysnumber= $input->param('daysnumber'); + my $copyFromType = $from_copyFrom && !$from_copyTo ? 'oneDay': 'range'; + + if ($copyFromType eq 'oneDay'){ + $query = "select isopened,holidaytype,note, openhour, closehour from discrete_calendar where date=? and branchcode=?"; + $stmt = $dbh->prepare($query); + $stmt->execute($from_copyFrom,$branch); + + my @data = $stmt->fetchrow_array; + push @data, $to_copyFrom; + push @data, $to_copyTo if $to_copyTo; + push @data, $branch; + $query = "update discrete_calendar set isopened=?, holidaytype=?, note=?, openhour=?, closehour=? where date=? and branchcode=?"; + $query = "update discrete_calendar set isopened=?, holidaytype=?, note=?, openhour=?, closehour=? where date between ? and ? and branchcode=?" if $to_copyTo; + $stmt = $dbh->prepare($query); + $stmt->execute(@data); + + }else{ + my $parser = DateTime::Format::Strptime->new( + pattern => '%Y-%m-%d', + on_error => 'croak', + ); + my $startDate = $parser->parse_datetime($from_copyFrom); + my $endDate = $parser->parse_datetime($from_copyTo); + + if($daysnumber ==6){ + for (my $tempDate = $startDate->clone(); $tempDate <= $endDate;$tempDate->add(days => 1)){ + $query = "select DAYOFWEEK(date),isopened,holidaytype,note, openhour, closehour from discrete_calendar where date=? and branchcode=?"; + $stmt = $dbh->prepare($query); + $stmt->execute($tempDate,$branch); + my @data = $stmt->fetchrow_array; + my $weekday = shift @data; + + push @data ,($to_copyFrom, $to_copyTo, $weekday,$branch); + $query ="update discrete_calendar set isopened=?, holidaytype=?, note=?, openhour=?, closehour=? where date between ? and ? and DAYOFWEEK(date)=? and branchcode=?"; + + $stmt = $dbh->prepare($query); + $stmt->execute(@data); + } + }else{ + my $to_startDate = $parser->parse_datetime($to_copyFrom); + my $to_endDate = $parser->parse_datetime($to_copyTo); + for (my $tempDate = $startDate->clone(); $tempDate <= $endDate;$tempDate->add(days => 1)){ + + $query = "select isopened,holidaytype,note, openhour, closehour from discrete_calendar where date=? and branchcode=?"; + $stmt = $dbh->prepare($query); + $stmt->execute($tempDate, $branch); + my @data = $stmt->fetchrow_array; + + push @data ,($to_startDate, $branch); + $query ="update discrete_calendar set isopened=?, holidaytype=?, note=?, openhour=?, closehour=? where date=? and branchcode=?"; + $stmt = $dbh->prepare($query); + $stmt->execute(@data); + $to_startDate->add(days =>1); + } + } + + +} +} +sub copyToBranch{ + my $newBranch = $input->param('newBranch'); + $query = "select isopened, holidaytype, note,openhour, closehour, date from discrete_calendar where branchcode=?"; + $stmt = $dbh->prepare($query); + $stmt->execute($branch); + my @data; + while (@data = $stmt->fetchrow_array) { + push @data, $newBranch; + $query = "update discrete_calendar set isopened=?, holidaytype=?, note=?, openhour=?, closehour=? where date=? and branchcode=?"; + my $newstmt = $dbh->prepare($query); + $newstmt->execute(@data); + } + +} + +sub delete_holidays{ + my ($branchcode, $weekday, $day, $month) = @_; + $weekday+=1; + my $startDate = DateTime->new(year => $year, month => $month, day => $day); + my $endDate = dt_from_string( scalar $input->param('datecancelrange') ) || '' if $input->param('datecancelrange'); + my $query; + my $stmt; + + if($operation eq 'edit'){ + edit_holiday($branchcode, $title, $weekday, $day, $month); + }else{ + if($operation eq 'delete'){ + $query = "update discrete_calendar set isOpened=1, holidaytype='', note='' where branchcode=? and date=? and isopened=0"; + $stmt = $dbh->prepare($query); + + $stmt->execute($branchcode, $startDate); + }elsif($operation eq 'deleterange'){ + $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, holidaytype='', note='' where branchcode=? and (date between ? and ?) and isopened=0"; + $stmt = $dbh->prepare($query); + + $stmt->execute($branchcode, $startDate, $endDate); + }else{ + if($holidaytype eq 'weekday') { + #This one deletes a weekly repeated holiday from the full table + $query = "update discrete_calendar set isOpened=1, holidaytype='', note='' where branchcode=? and holidaytype='W' and DAYOFWEEK(date)=? and date >=? and isopened=0"; + $stmt = $dbh->prepare($query); + + $stmt->execute($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 isOpened=1, holidaytype='', note='' where branchcode=? and MONTH(date)=? and DAY(date)=? and date >=? and isopened=0"; + $stmt = $dbh->prepare($query); + $stmt->execute($branchcode, $month, $day, $today); + } + } + } +} + +sub edit_holiday{ + my ($branchcode, $title, $weekday, $day, $month) = @_; + my $today = DateTime->today; + my $startDate = DateTime->new(year => $year, month => $month, day => $day); + 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 >=? and isopened=0"; + $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 >=? and isopened=0"; + $stmt = $dbh->prepare($query); + $stmt->execute($title, $branchcode, $month, $day, $today); + } elsif ($holidaytype eq 'ymd' || $holidaytype eq 'float' || $holidaytype eq 'N') { + #This one deletes only a holiday based on a given date + $query = "update discrete_calendar set note=? where branchcode=? and date=? and isopened=0"; + $stmt = $dbh->prepare($query); + + $stmt->execute($title,$branchcode, $startDate); + } +} + +sub changeHours{ + my @params; + my $openhour = $input->param('openHour') || ''; + my $closehour = $input->param('closeHour') || ''; + my $fromDate = $input->param('fromDate') || ''; + my $toDate = $input->param('toDate') || ''; + my $holidaytype = $input->param('holidayType') || ''; + my $isopened = 0; + + if( $holidaytype eq 'none'){ + $isopened = 1; + $holidaytype=''; + } + if($openhour && $closehour){ + $query = "update discrete_calendar set openhour=?, closehour=?, isopened =?, holidaytype=? where branchcode=?"; + push @params, ($openhour, $closehour, $isopened, $holidaytype, $branchcode); + }elsif($openhour && !$closehour){ + $query = "update discrete_calendar set openhour=?, isopened =?, holidaytype=? where branchcode=?"; + push @params, ($openhour, $isopened, $holidaytype, $branchcode); + }elsif(!$openhour && $closehour){ + $query = "update discrete_calendar set closehour=?, isopened =?, holidaytype=? where branchcode=?"; + push @params,($closehour, $isopened, $holidaytype, $branchcode); + } + if ($toDate && $fromDate) { + $query .= " and date between ? and ?"; + push @params, ($fromDate, $toDate); + } else { + $query .= " and date=?"; + push @params, $fromDate; + } + + $stmt = $dbh->prepare($query); + $stmt->execute(@params); +} +# keydate - date passed to calendar.js. calendar.js does not process dashes within a date. + +my $keydate = output_pref( { dt => $calendarinput_dt, dateonly => 1, dateformat => 'iso' } ); +$keydate =~ s/-/\//g; + +# Set all the branches. +my $onlymine = + ( C4::Context->preference('IndependentBranches') + && C4::Context->userenv + && !C4::Context->IsSuperLibrarian() + && C4::Context->userenv->{branch} ? 1 : 0 ); +if ( $onlymine ) { + $branch = C4::Context->userenv->{'branch'}; +} +my $branchname = GetBranchName($branch); +my $branches = GetBranches($onlymine); +my @branchloop; +for my $thisbranch ( + sort { $branches->{$a}->{branchname} cmp $branches->{$b}->{branchname} } + keys %{$branches} ) { + push @branchloop, + { value => $thisbranch, + selected => $thisbranch eq $branch, + branchname => $branches->{$thisbranch}->{'branchname'}, + }; +} + +# branches calculated - put branch codes in a single string so they can be passed in a form +my $branchcodes = join '|', keys %{$branches}; + +# check if it's changing hours + + +# Get all the holidays + +#discrete_calendar weekdays +$query = "select distinct DAYOFWEEK(date), note from discrete_calendar where branchcode =? and holidaytype='W' order by DAYOFWEEK(date)"; +$stmt = $dbh->prepare($query); +$stmt->execute($branch); +my @week_days; +while (my ($weekday,$note) = $stmt->fetchrow_array){ + push @week_days, {weekday => ($weekday-1), note => $note}; +} + +#discrete_calendar repeatable +$query = "select distinct day(date), month(date) from discrete_calendar where branchcode =? and holidaytype='R'"; +$stmt = $dbh->prepare($query); +$stmt->execute($branch); +my @repeatable_holidays; +while (my ($day, $month, $note) = $stmt->fetchrow_array){ + push @repeatable_holidays, {day=> $day, month => $month , note => $note}; +} + +#single hildays (exceptions) +$query = "select DATE(date), note from discrete_calendar where branchcode =? and holidaytype='E'"; +$stmt = $dbh->prepare($query); + +$stmt->execute($branch); + +my @single_holidays; +while (my ($date,$note) = $stmt->fetchrow_array){ + my $outputdate = dt_from_string($date); + $outputdate = output_pref( { dt => $outputdate, dateonly => 1 } ); + push @single_holidays, {date => $date, outputdate => $outputdate, note => $note}; +} +#Calendar maximum date +my $minDate; +$query = "select distinct DATE(date) from discrete_calendar where date = (select min(date) from discrete_calendar)"; +$stmt = $dbh->prepare($query); +$stmt->execute(); +$minDate = $stmt->fetchrow_array; +#Calendar minimum date +my $maxDate; +$query = "select distinct DATE(date) from discrete_calendar where date = (select max(date) from discrete_calendar)"; +$stmt = $dbh->prepare($query); +$stmt->execute(); +$maxDate = $stmt->fetchrow_array; + +my @datesInfos =(); +$query = "select DATE(date), holidaytype, openhour, closehour, note from discrete_calendar where branchcode=?"; +$stmt = $dbh->prepare($query); +$stmt->execute($branch); +while (my ($date, $holidaytype, $openhour, $closehour, $note) = $stmt->fetchrow_array){ + my $outputdate = dt_from_string($date); + $outputdate = output_pref( { dt => $outputdate, dateonly => 1 } ); + push @datesInfos, {date=> $date, outputdate => $outputdate, holidaytype => $holidaytype , openhour => $openhour, closehour => $closehour, note => $note}; +} + +#Not finished yet +my $timeFormat = C4::Context->preference('TimeFormat') || '24hr'; +if($timeFormat eq '24hr'){ + $timeFormat = 'HH:mm:ss'; +}else{ + $timeFormat = 'HH:mm:ss'; +} +$template->param( + branchloop => \@branchloop, + HOLIDAYS_LOOP => \@single_holidays, + calendardate => $calendardate, + keydate => $keydate, + branchcodes => $branchcodes, + branch => $branch, + branchname => $branchname, + branch => $branch, + week_days => \@week_days, + repeatable_holidays => \@repeatable_holidays, + minDate => $minDate, + maxDate => $maxDate, + datesInfos => \@datesInfos, + timeFormat => $timeFormat, +); + +# Shows the template with the real values replaced +output_html_with_http_headers $input, $cookie, $template->output; -- 1.9.1