@@ -, +, @@
---
.../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
--- a/installer/data/mysql/atomicupdate/bug_17015_add_holidaytype.sql
+++ a/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`));
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt
+++ a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/discrete_calendar.tt
@@ -0,0 +1,749 @@
+[% INCLUDE 'doc-head-open.inc' %]
+
+
+
+
+
+
[% branchname %] calendar
+
+
+
+
+
+
Calendar information
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
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
+
+
+
+ Day of week |
+ Title |
+
+
+
+ [% FOREACH WEEK_DAYS_LOO IN week_days %]
+
+ [% WEEK_DAYS_LOO.weekday %] |
+
+ [% WEEK_DAYS_LOO.note %] |
+
+ [% END %]
+
+
+[% END %]
+
+[% IF ( repeatable_holidays ) %]
+
Yearly - Repeatable holidays
+
+
+
+ [% IF ( dateformat == "metric" ) %]
+ Day/month |
+ [% ELSE %]
+ Month/day |
+ [% END %]
+ Title |
+
+
+
+ [% FOREACH DAY_MONTH_HOLIDAYS_LOO IN repeatable_holidays %]
+
+ [%DAY_MONTH_HOLIDAYS_LOO.month%]/[% DAY_MONTH_HOLIDAYS_LOO.day %] |
+ [% DAY_MONTH_HOLIDAYS_LOO.note %] |
+
+ [% END %]
+
+
+[% END %]
+
+[% IF ( HOLIDAYS_LOOP ) %]
+
Unique upcoming holidays
+
+
+
+ Date |
+ Title |
+
+
+
+ [% FOREACH HOLIDAYS_LOO IN HOLIDAYS_LOOP %]
+
+ [% HOLIDAYS_LOO.outputdate %] |
+ [% HOLIDAYS_LOO.note %] |
+
+ [% END %]
+
+
+[% END %]
+
+
+
+
+
+
+
+[% INCLUDE 'tools-menu.inc' %]
+
+
+[% INCLUDE 'intranet-bottom.inc' %]
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt
+++ a/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
--- a/misc/cronjobs/add_days_discrete_calendar.pl
+++ a/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);
+ }
+}
--- a/misc/generate_discrete_calendar.pl
+++ a/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);
+ }
+ }
+#}
--- a/tools/discrete_calendar.pl
+++ a/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;
--