$DBversion = "3.00.00.006";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
+ sanitize_zero_date('issues', 'issuedate');
print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
SetVersion ($DBversion);
}
print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
$dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
- $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
+
+ sanitize_zero_date('subscriptionhistory', 'enddate');
+
SetVersion ($DBversion);
}
# match the new type of the corresponding field
$dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
# System did not ensure budgetdate was valid historically
- $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|);
+ sanitize_zero_date('fundmapping', 'budgetdate');
+ $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate IS NULL|);
# We save the map in fundmapping in case you need later processing
$dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
# these can speed processing up
$DBversion = "3.01.00.130";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("UPDATE reserves SET expirationdate = NULL WHERE expirationdate = '0000-00-00'");
+ sanitize_zero_date('reserves', 'expirationdate');
print "Upgrade to $DBversion done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)\n";
SetVersion ($DBversion);
}
$DBversion = "3.07.00.002";
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
- $dbh->do("UPDATE borrowers SET debarred=NULL WHERE debarred='0000-00-00';");
+ sanitize_zero_date('borrowers', 'debarred');
print "Setting NULL to debarred where 0000-00-00 is stored (bug 7272)\n";
SetVersion($DBversion);
}
SET firstacquidate = ?
WHERE subscriptionid = ?
|);
+
+ sanitize_zero_date('subscription', 'firstacquidate');
my $get_subscriptions_sth = $dbh->prepare(qq|
SELECT subscriptionid, startdate
FROM subscription
} );
$dbh->do( q{
- ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type);
+ ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
} );
$dbh->do( q{
$DBversion = "3.21.00.009";
if ( CheckVersion($DBversion) ) {
+
+ sanitize_zero_date('aqorders', 'datecancellationprinted');
+
$dbh->do(q|
UPDATE aqorders SET orderstatus='cancelled'
- WHERE (datecancellationprinted IS NOT NULL OR
- datecancellationprinted<>'0000-00-00');
+ WHERE (datecancellationprinted IS NOT NULL)
|);
+
print "Upgrade to $DBversion done (Bug 13993: Correct orderstatus for transferred orders)\n";
SetVersion($DBversion);
}
$DBversion = "3.21.00.023";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q{
- UPDATE borrowers SET debarred=NULL WHERE debarred='0000-00-00'
- });
- $dbh->do(q{
- UPDATE borrowers SET dateexpiry=NULL where dateexpiry='0000-00-00'
- });
- $dbh->do(q{
- UPDATE borrowers SET dateofbirth=NULL where dateofbirth='0000-00-00'
- });
- $dbh->do(q{
- UPDATE borrowers SET dateenrolled=NULL where dateenrolled='0000-00-00'
- });
+
+ sanitize_zero_date('borrowers', 'debarred');
+ sanitize_zero_date('borrowers', 'dateexpiry');
+ sanitize_zero_date('borrowers', 'dateofbirth');
+ sanitize_zero_date('borrowers', 'dateenrolled');
+
print "Upgrade to $DBversion done (Bug 14717: Prevent 0000-00-00 dates in patron data)\n";
SetVersion($DBversion);
}
if ( CheckVersion($DBversion) ) {
# Fixup any pre-existing bad suggestedby, manageddate, accepteddate dates
- $dbh->do(
- "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"
- );
- $dbh->do(
- "UPDATE suggestions SET manageddate = '1970-01-01' WHERE manageddate = '0000-00-00';"
- );
- $dbh->do(
- "UPDATE suggestions SET accepteddate = '1970-01-01' WHERE accepteddate = '0000-00-00';"
- );
+ eval {
+ local $dbh->{PrintError} = 0;
+ $dbh->do(
+ "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"
+ );
+ $dbh->do(
+ "UPDATE suggestions SET manageddate = '1970-01-01' WHERE manageddate = '0000-00-00';"
+ );
+ $dbh->do(
+ "UPDATE suggestions SET accepteddate = '1970-01-01' WHERE accepteddate = '0000-00-00';"
+ );
+ };
# Add constraint for suggestedby
unless ( foreign_key_exists( 'suggestions', 'suggestions_ibfk_suggestedby' ) )
if( CheckVersion( $DBversion ) ) {
my @warnings;
- $dbh->do(q|
- UPDATE
- serial
- SET
- planneddate = NULL
- WHERE
- planneddate = '0000-00-00'
- |);
-
- $dbh->do(q|
- UPDATE
- serial
- SET
- publisheddate = NULL
- WHERE
- publisheddate = '0000-00-00'
- |);
-
- $dbh->do(q|
- UPDATE
- serial
- SET
- claimdate = NULL
- WHERE
- claimdate = '0000-00-00'
- |);
+ sanitize_zero_date('serial', 'planneddate');
+ sanitize_zero_date('serial', 'publisheddate');
+ sanitize_zero_date('serial', 'claimdate');
$dbh->do(q|
ALTER TABLE serial
unless ( foreign_key_exists( 'serial', 'serial_ibfk_1' ) ) {
my $serials = $dbh->selectall_arrayref(q|
+ SELECT serialid FROM serial JOIN subscription USING (subscriptionid) WHERE serial.biblionumber != subscription.biblionumber
+ |, { Slice => {} });
+ if ( @$serials ) {
+ push @warnings, q|WARNING - The following serials will be updated, they were attached to a different biblionumber than their related subscription: | . join ", ", map { $_->{serialid} } @$serials;
+ $dbh->do(q|
+ UPDATE serial JOIN subscription USING (subscriptionid) SET serial.biblionumber = subscription.biblionumber WHERE serial.biblionumber != subscription.biblionumber
+ |);
+ }
+ $serials = $dbh->selectall_arrayref(q|
SELECT serialid FROM serial WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
|, { Slice => {} });
if ( @$serials ) {
unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_1' ) ) {
$dbh->do(q|
+ UPDATE subscriptionhistory JOIN subscription USING (subscriptionid) SET subscriptionhistory.biblionumber = subscription.biblionumber WHERE subscriptionhistory.biblionumber != subscription.biblionumber
+ |);
+ $dbh->do(q|
DELETE FROM subscriptionhistory WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
|);
$dbh->do(q|
});
$dbh->do(q{
UPDATE letter SET
- content = REPLACE(content, "The following item [% biblio.title %] has correctly been renewed and is now due [% checkout.date_due %]", "The following item, [% biblio.title %], has correctly been renewed and is now due on [% checkout.date_due as_due_date => 1 %]
+ content = REPLACE(content, "The following item [% biblio.title %] has correctly been renewed and is now due [% checkout.date_due %]", "The following item, [% biblio.title %], has correctly been renewed and is now due on [% checkout.date_due | $KohaDates as_due_date => 1 %]
")
WHERE code = 'AUTO_RENEWALS';
});
$DBversion = '20.06.00.058';
if( CheckVersion( $DBversion ) ) {
+
+ # Adding the ON DELETE CASCASE ON UPDATE CASCADE, in case it's missing (from 9016 - 3.15.00.039)
+ $dbh->do( q{
+ ALTER TABLE letter DROP FOREIGN KEY message_transport_type_fk
+ } );
+
+ $dbh->do( q{
+ ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
+ } );
+
$dbh->do(q{
UPDATE message_transport_types SET message_transport_type = "itiva" WHERE message_transport_type = "phone"
});
NewVersion( $DBversion, 25552, "Add missing Claims Returned option to MarkLostItemsAsReturned");
}
+$DBversion = '20.12.00.009';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE systempreferences SET variable = 'UseICUStyleQUotes' WHERE variable = 'UseICU'" );
+ NewVersion( $DBversion, 27581, "Rename system preference 'UseICU' to 'UseICUStyleQuotes'");
+}
+
+$DBversion = '20.12.00.010';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ DELETE FROM systempreferences WHERE variable="OpacGroupResults"
+ });
+
+ NewVersion( $DBversion, 20410, "Remove OpacGroupResults");
+}
+
+$DBversion = '20.12.00.011';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do( q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES
+ ('OPACShibOnly','0','If ON enables shibboleth only authentication for the opac','','YesNo'),
+ ('staffShibOnly','0','If ON enables shibboleth only authentication for the staff client','','YesNo')
+ } );
+ NewVersion( $DBversion, 18506, "Add OPACShibOnly and staffShibOnly system preferences" );
+}
+
+$DBversion = '20.12.00.012';
+if( CheckVersion( $DBversion ) ) {
+ my $category_exists = $dbh->selectrow_array("SELECT count(category_name) FROM authorised_value_categories WHERE category_name='UPLOAD'");
+ my $description;
+ if( $category_exists ){
+ $description = "The UPLOAD authorized value category exists. Update the 'is_system' value to 1.";
+ $dbh->do( "UPDATE authorised_value_categories SET is_system = 1 WHERE category_name = 'UPLOAD'" );
+ } else {
+ $description = "The UPLOAD authorized value category does not exist. Create it.";
+ $dbh->do( "INSERT IGNORE INTO authorised_value_categories (category_name, is_system) VALUES ('UPLOAD', 1)" );
+ }
+
+ NewVersion( $DBversion, 27598, ["Add UPLOAD as a built-in system authorized value category", $description] );
+}
+
+$DBversion = '20.12.00.013';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences
+ (variable, value, explanation, options, type) VALUES
+ ('DefaultSaveRecordFileID', 'biblionumber', 'Defines whether the advanced cataloging editor will use the bibliographic record number or control number field to populate the name of the save file.', 'biblionumber|controlnumber', 'Choice')
+ });
+ NewVersion( $DBversion, 24108, "Add system preference DefaultSaveRecordFileID");
+}
+
+$DBversion = '20.12.00.014';
+if( CheckVersion( $DBversion ) ) {
+
+ sanitize_zero_date('aqorders', 'datecancellationprinted');
+ sanitize_zero_date('old_issues', 'returndate');
+
+ NewVersion( $DBversion, 7806, "Remove remaining possible 0000-00-00 values");
+}
+
+$DBversion = '20.12.00.015';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( "UPDATE search_marc_to_field SET sort = 1 WHERE sort IS NULL" );
+ $dbh->do( "ALTER TABLE search_marc_to_field MODIFY COLUMN sort tinyint(1) DEFAULT 1 NOT NULL COMMENT 'Sort defaults to 1 (Yes) and creates sort fields in the index, 0 (no) will prevent this'" );
+ NewVersion( $DBversion, 27316, "In Elastisearch mappings convert NULL (Undef) for sort to 1 (Yes)");
+}
+
+$DBversion = '20.12.00.016';
+if( CheckVersion( $DBversion ) ) {
+
+ unless ( column_exists( 'marc_subfield_structure', 'display_order' ) ) {
+ $dbh->do(q{
+ ALTER TABLE marc_subfield_structure
+ ADD COLUMN display_order INT(2) NOT NULL DEFAULT 0 AFTER maxlength
+ });
+ }
+
+ unless ( column_exists( 'auth_subfield_structure', 'display_order' ) ) {
+ $dbh->do(q{
+ ALTER TABLE auth_subfield_structure
+ ADD COLUMN display_order INT(2) NOT NULL DEFAULT 0 AFTER defaultvalue
+ });
+ }
+
+ NewVersion( $DBversion, 8976, "Allow setting a default sequence of subfields in cataloguing editor" );
+}
+
+$DBversion = '20.12.00.017';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES ('CheckPrevCheckoutDelay','0', 'Maximum number of days that will trigger a warning if the patron has borrowed that item in the past when CheckPrevCheckout is enabled. Disabled if 0 or empty.', NULL, 'free')
+ |);
+
+ NewVersion( $DBversion, 26937, "Add CheckPrevCheckoutDelay system preference)" );
+}
+
+$DBversion = '20.12.00.018';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q|
+ UPDATE items
+ LEFT JOIN issues ON issues.itemnumber=items.itemnumber
+ SET items.onloan=CAST(issues.date_due AS DATE)
+ WHERE items.onloan IS NULL AND issues.issue_id IS NOT NULL
+ |);
+
+ NewVersion( $DBversion, 27808, "Adjust items.onloan if needed" );
+}
+
+$DBversion = '20.12.00.019';
+if( CheckVersion( $DBversion ) ) {
+
+ if( !column_exists( 'branchtransfers', 'datecancelled' ) ) {
+ $dbh->do(q|
+ ALTER TABLE `branchtransfers`
+ ADD COLUMN `datecancelled` datetime default NULL AFTER `datearrived`
+ |);
+ }
+
+ if( !column_exists( 'branchtransfers', 'cancellation_reason' ) ) {
+ $dbh->do(q|
+ ALTER TABLE `branchtransfers`
+ ADD COLUMN `cancellation_reason` ENUM('Manual', 'StockrotationAdvance', 'StockrotationRepatriation', 'ReturnToHome', 'ReturnToHolding', 'RotatingCollection', 'Reserve', 'LostReserve', 'CancelReserve') DEFAULT NULL AFTER `reason`
+ |);
+ }
+
+ NewVersion( $DBversion, 26057, "Add datecancelled field to branchtransfers");
+}
+
+$DBversion = '20.12.00.020';
+if ( CheckVersion($DBversion) ) {
+
+ # Update daterequested from datesent for stockrotation
+ $dbh->do(q|
+ UPDATE `branchtransfers`
+ SET
+ `daterequested` = `datesent`,
+ `datesent` = NULL
+ WHERE `reason` LIKE 'Stockrotation%'
+ AND `datearrived` IS NULL
+ |);
+
+ NewVersion( $DBversion, 24446, "Update stockrotation 'daterequested' field in transfers table" );
+}
+
+$DBversion = '20.12.00.021';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ UPDATE systempreferences SET type="Free" WHERE variable="OverDriveClientSecret" OR variable="RecordedBooksClientSecret"
+ });
+ $dbh->do(q{
+ UPDATE systempreferences SET type="integer" WHERE variable="UsageStats"
+ });
+ $dbh->do(q{
+ UPDATE systempreferences
+ SET value="0"
+ WHERE ( ( type = "YesNo" AND ( value NOT IN ( "1", "0" ) OR value IS NULL ) ) )
+ });
+
+ NewVersion( $DBversion, 22824, "Update syspref values for YesNo");
+}
+
+$DBversion = '20.12.00.022';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{ INSERT IGNORE INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type) VALUES
+ ('circulation','CHECKINSLIP','','Checkin slip',1,'Checkin slip',
+"<h3>[% branch.branchname %]</h3>
+Checked in items for [% borrower.title %] [% borrower.firstname %] [% borrower.initials %] [% borrower.surname %] <br />
+([% borrower.cardnumber %]) <br />
+
+[% today | $KohaDates %]<br />
+
+<h4>Checked in today</h4>
+[% FOREACH checkin IN old_checkouts %]
+[% SET item = checkin.item %]
+<p>
+[% item.biblio.title %] <br />
+Barcode: [% item.barcode %] <br />
+</p>
+[% END %]",
+ 'print')
+ });
+
+ NewVersion( $DBversion, 12224, "Add CHECKINSLIP notice" );
+}
+
+$DBversion = '20.12.00.023';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ UPDATE systempreferences
+ SET value=REPLACE(value, '|', ',')
+ WHERE variable="OPACHoldsIfAvailableAtPickupExceptions"
+ OR variable="BatchCheckoutsValidCategories"
+ });
+ NewVersion( $DBversion, 27652, "Separate values for OPACHoldsIfAvailableAtPickupExceptions and BatchCheckoutsValidCategories with comma");
+}
+
+$DBversion = '20.12.00.024';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do( q{
+ INSERT IGNORE INTO letter (module, code, name, title, content, message_transport_type) VALUES ('circulation', 'AUTO_RENEWALS_DGST', 'Notification on auto renewals', 'Auto renewals (Digest)',
+ "Dear [% borrower.firstname %] [% borrower.surname %],
+ [% IF error %]
+ There were [% error %] items that were not renewed.
+ [% END %]
+ [% IF success %]
+ There were [% success %] items that were renewed.
+ [% END %]
+ [% FOREACH checkout IN checkouts %]
+ [% checkout.item.biblio.title %] : [% checkout.item.barcode %]
+ [% IF !checkout.auto_renew_error %]
+ was renewed until [% checkout.date_due | $KohaDates as_due_date => 1%]
+ [% ELSIF checkout.auto_renew_error == 'too_many' %]
+ You have reached the maximum number of checkouts possible.
+ [% ELSIF checkout.auto_renew_error == 'on_reserve' %]
+ This item is on hold for another patron.
+ [% ELSIF checkout.auto_renew_error == 'restriction' %]
+ You are currently restricted.
+ [% ELSIF checkout.auto_renew_error == 'overdue' %]
+ You have overdue items.
+ [% ELSIF checkout.auto_renew_error == 'auto_too_late' %]
+ It's too late to renew this item.
+ [% ELSIF checkout.auto_renew_error == 'auto_too_much_oweing' %]
+ Your total unpaid fines are too high.
+ [% ELSIF checkout.auto_renew_error == 'too_unseen' %]
+ This item must be renewed at the library.
+ [% END %]
+ [% END %]
+ ", 'email');
+ });
+
+ $dbh->do( q{
+ INSERT IGNORE INTO `message_attributes`
+ (`message_attribute_id`, message_name, `takes_days`)
+ VALUES (9, 'Auto_Renewals', 0)
+ });
+
+ $dbh->do( q{
+ INSERT IGNORE INTO `message_transports`
+ (`message_attribute_id`, `message_transport_type`, `is_digest`, `letter_module`, `letter_code`)
+ VALUES (9, 'email', 0, 'circulation', 'AUTO_RENEWALS'),
+ (9, 'sms', 0, 'circulation', 'AUTO_RENEWALS'),
+ (9, 'email', 1, 'circulation', 'AUTO_RENEWALS_DGST'),
+ (9, 'sms', 1, 'circulation', 'AUTO_RENEWALS_DGST')
+ });
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES ('AutoRenewalNotices','cron','cron|preferences|never','How should Koha determine whether to end autorenewal notices','Choice')
+ });
+
+ NewVersion( $DBversion, 18532, 'Messaging preferences for auto renewals' );
+}
+
+$DBversion = '20.12.00.025';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
+ VALUES ('ChargeFinesOnClosedDays', '0', NULL, 'Charge fines on days the library is closed.', 'YesNo')
+ |);
+
+ NewVersion( $DBversion, 27835, "Add new system preference ChargeFinesOnClosedDays");
+}
+
+$DBversion = '20.12.00.026';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES ('DefaultHoldExpirationdate','0','','Automatically set default expiration date for holds','YesNo') });
+ $dbh->do(q{INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES ('DefaultHoldExpirationdatePeriod','0','','How long into the future default expiration date is set to be.','integer') });
+ $dbh->do(q{INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES ('DefaultHoldExpirationdateUnitOfTime','days','days|months|years','Which unit of time is used when setting the default expiration date. ','choice') });
+
+ NewVersion( $DBversion, 26498, "Bug 26498 - Add option to set a default expire date for holds at reservation time");
+}
+
+$DBversion = '20.12.00.027';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ UPDATE circulation_rules
+ SET
+ rule_value = CASE
+ WHEN rule_value='0' THEN 'not_allowed'
+ WHEN rule_value='1' THEN 'from_home_library'
+ WHEN rule_value='2' THEN 'from_any_library'
+ WHEN rule_value='3' THEN 'from_local_hold_group'
+ END
+ WHERE rule_name='holdallowed' AND rule_value >= 0 AND rule_value <= 3;
+ });
+
+ NewVersion( $DBversion, 27069, "Change holdallowed values from numbers to strings");
+}
+
+$DBversion = '20.12.00.028';
+if ( CheckVersion($DBversion) ) {
+
+ if ( !column_exists( 'letter', 'id' ) ) {
+ $dbh->do(q{
+ ALTER TABLE letter DROP PRIMARY KEY
+ });
+ $dbh->do(q{
+ ALTER TABLE letter ADD COLUMN `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
+ });
+ $dbh->do(q{
+ ALTER TABLE letter ADD UNIQUE KEY letter_uniq_1 (`module`,`code`,`branchcode`,`message_transport_type`,`lang`)
+ });
+ }
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, explanation, options, type)
+ VALUES ('NoticesLog','0',NULL,'If enabled, log changes to notice templates','YesNo')
+ });
+
+ NewVersion( $DBversion, 14233, "Add id field to letter table" );
+}
+
+$DBversion = '20.12.00.029';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do("ALTER TABLE problem_reports MODIFY content TEXT NOT NULL");
+
+ NewVersion( $DBversion, 27726, "Increase field size for problem_reports.content");
+}
+
+$DBversion = '20.12.00.030';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` )
+ VALUES ('LockExpiredDelay','','','Delay for locking expired patrons (empty means no locking)','Integer')
+ |);
+
+ NewVersion( $DBversion, 21549, "Add new system preference LockExpiredDelay");
+}
+
+$DBversion = '20.12.00.031';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, explanation, options, type)
+ VALUES ('Reference_NFL_Statuses','1|2',NULL,'Contains not for loan statuses considered as available for reference','Free')
+ });
+
+ NewVersion( $DBversion, 21260, "Add new system preference Reference_NFL_Statuses");
+}
+
+$DBversion = '20.12.00.032';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO letter
+ (module,code,branchcode,name,is_html,title,content,message_transport_type,lang)
+ VALUES ('reserves','HOLD_REMINDER','','Waiting hold reminder',0,'You have waiting holds.','Dear [% borrower.firstname %] [% borrower.surname %],\r\n\r\nThe following holds are waiting at [% branch.branchname %]:\r\n\\r\n[% FOREACH hold IN holds %]\r\n [% hold.biblio.title %] : waiting since [% hold.waitingdate | $KohaDates %]\r\n[% END %]','email','default')
+ });
+
+ NewVersion( $DBversion, 15986, "Add sample HOLD_REMINDER notice");
+}
+
+$DBversion = '20.12.00.033';
+if( CheckVersion( $DBversion ) ) {
+ my $debar = $dbh->selectall_arrayref(q|
+ SELECT d.borrowernumber, GROUP_CONCAT(comment SEPARATOR '\n') AS comment
+ FROM borrower_debarments d
+ LEFT JOIN borrowers b ON b.borrowernumber=d.borrowernumber
+ WHERE ( b.debarredcomment IS NULL OR b.debarredcomment = "" ) AND ( expiration > CURRENT_DATE() OR expiration IS NULL )
+ GROUP BY d.borrowernumber
+ |, { Slice => {} });
+
+
+ my $update_sth = $dbh->prepare(q|
+ UPDATE borrowers
+ SET debarredcomment=?
+ WHERE borrowernumber=?
+ |);
+ for my $d ( @$debar ) {
+ $update_sth->execute($d->{comment}, $d->{borrowernumber});
+ }
+
+ NewVersion( $DBversion, 26940, "Put in sync borrowers.debarredcomment with comments from borrower_debarments");
+}
+
+$DBversion = '20.12.00.034';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type)
+ VALUES ('casServerVersion', '2', '2|3', 'Version of the CAS server Koha will connect to.', 'Choice');
+ });
+
+ NewVersion( $DBversion, 20854, "Add new system preference casServerVersion");
+}
+
+$DBversion = '20.12.00.035';
+if( CheckVersion( $DBversion ) ) {
+ if( !column_exists( 'itemtypes', 'automatic_checkin' ) ) {
+ $dbh->do(q{
+ ALTER TABLE itemtypes
+ ADD COLUMN `automatic_checkin` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If automatic checkin is enabled for items of this type' AFTER `searchcategory`
+ });
+ }
+
+ NewVersion( $DBversion, 23207, "Add automatic_checkin to itemtypes table");
+}
+
+$DBversion = '20.12.00.036';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ ALTER TABLE club_holds_to_patron_holds
+ MODIFY COLUMN error_code
+ ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
+ 'tooManyHoldsForThisRecord', 'tooManyReservesToday',
+ 'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
+ 'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred',
+ 'noReservesAllowed'
+ )
+ });
+
+ NewVersion( $DBversion, 16787, "Add noReservesAllowed to club holds error codes");
+}
+
+$DBversion = '20.12.00.037';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do( q{
+ INSERT IGNORE INTO systempreferences (variable, value, explanation, options, type)
+ VALUES ('AcquisitionLog', '0', 'If enabled, log acquisition activity', '', 'YesNo');
+ });
+
+ NewVersion( $DBversion, 23971, "Add new system preference AcquisitionLog");
+}
+
+$DBversion = '20.12.00.038';
+if( CheckVersion( $DBversion ) ) {
+
+ # Add 'ItemLost' to reserves cancellation_reason enum
+ $dbh->do(
+ q{
+ ALTER TABLE
+ `branchtransfers`
+ MODIFY COLUMN
+ `cancellation_reason` enum(
+ 'Manual',
+ 'StockrotationAdvance',
+ 'StockrotationRepatriation',
+ 'ReturnToHome',
+ 'ReturnToHolding',
+ 'RotatingCollection',
+ 'Reserve',
+ 'LostReserve',
+ 'CancelReserve',
+ 'ItemLost'
+ )
+ AFTER `comments`
+ }
+ );
+
+ NewVersion( $DBversion, 27281, "Add 'ItemLost' to branchtransfers.cancellation_reason enum");
+}
+
+$DBversion = '20.12.00.039';
+if( CheckVersion( $DBversion ) ) {
+
+ $dbh->do(
+ q{
+ ALTER TABLE
+ `branchtransfers`
+ MODIFY COLUMN
+ `reason` enum(
+ 'Manual',
+ 'StockrotationAdvance',
+ 'StockrotationRepatriation',
+ 'ReturnToHome',
+ 'ReturnToHolding',
+ 'RotatingCollection',
+ 'Reserve',
+ 'LostReserve',
+ 'CancelReserve',
+ 'TransferCancellation'
+ )
+ AFTER `comments`
+ }
+ );
+
+ NewVersion( $DBversion, 12362, "Add 'TransferCancellation' to branchtransfers.reason enum");
+}
+
+$DBversion = '20.12.00.040';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(
+ q{
+ INSERT IGNORE INTO account_debit_types (
+ code,
+ description,
+ can_be_invoiced,
+ can_be_sold,
+ default_amount,
+ is_system
+ )
+ VALUES
+ ('VOID', 'Credit has been voided', 0, 0, NULL, 1)
+ }
+ );
+
+ $dbh->do(q{
+ INSERT IGNORE INTO account_offset_types ( type ) VALUES ('VOID');
+ });
+
+ NewVersion( $DBversion, 27971, "Add VOID debit type code");
+}
+
+$DBversion = '20.12.00.041';
+if ( CheckVersion($DBversion) ) {
+
+ # ACCOUNT_CREDIT UPDATES
+ # backup existing notice to action_logs
+ my $credit_arr = $dbh->selectall_arrayref(q{SELECT lang FROM letter WHERE code = 'ACCOUNT_CREDIT'}, { Slice => {} });
+ my $c_sth = $dbh->prepare(q{
+ INSERT INTO action_logs ( timestamp, module, action, object, info, interface )
+ SELECT NOW(), 'NOTICES', 'UPGRADE', id, content, 'cli'
+ FROM letter
+ WHERE lang = ? AND code = 'ACCOUNT_CREDIT'
+ });
+
+ for my $c ( @{$credit_arr} ) {
+ $c_sth->execute( $c->{lang} );
+ }
+
+ # replace notice with default
+ my $c_notice = q{
+[% USE Price %]
+[% PROCESS 'accounts.inc' %]
+<table>
+[% IF ( LibraryName ) %]
+ <tr>
+ <th colspan="4" class="centerednames">
+ <h3>[% LibraryName | html %]</h3>
+ </th>
+ </tr>
+[% END %]
+ <tr>
+ <th colspan="4" class="centerednames">
+ <h2><u>Fee receipt</u></h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="4" class="centerednames">
+ <h2>[% Branches.GetName( credit.patron.branchcode ) | html %]</h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="4">
+ Received with thanks from [% credit.patron.firstname | html %] [% credit.patron.surname | html %] <br />
+ Card number: [% credit.patron.cardnumber | html %]<br />
+ </th>
+ </tr>
+ <tr>
+ <th>Date</th>
+ <th>Description of charges</th>
+ <th>Note</th>
+ <th>Amount</th>
+ </tr>
+
+ <tr class="highlight">
+ <td>[% credit.date | $KohaDates %]</td>
+ <td>
+ [% PROCESS account_type_description account=credit %]
+ [%- IF credit.description %], [% credit.description | html %][% END %]
+ </td>
+ <td>[% credit.note | html %]</td>
+ <td class="credit">[% credit.amount | $Price %]</td>
+ </tr>
+
+<tfoot>
+ <tr>
+ <td colspan="3">Total outstanding dues as on date: </td>
+ [% IF ( credit.patron.account.balance >= 0 ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% credit.patron.account.balance | $Price %]</td>
+ </tr>
+</tfoot>
+</table>
+ };
+
+ $dbh->do(q{UPDATE letter SET content = ?, is_html = 1 WHERE code = 'ACCOUNT_CREDIT'}, undef, $c_notice);
+
+ # ACCOUNT_DEBIT UPDATES
+ # backup existing notice to action_logs
+ my $debit_arr = $dbh->selectall_arrayref(
+ "SELECT lang FROM letter WHERE code = 'ACCOUNT_DEBIT'", { Slice => {} });
+ my $d_sth = $dbh->prepare(q{
+ INSERT INTO action_logs ( timestamp, module, action, object, info, interface )
+ SELECT NOW(), 'NOTICES', 'UPGRADE', id, content, 'cli'
+ FROM letter
+ WHERE lang = ? AND code = 'ACCOUNT_DEBIT'
+ });
+
+ for my $d ( @{$debit_arr} ) {
+ $d_sth->execute( $d->{lang} );
+ }
+
+ # replace notice with default
+ my $d_notice = q{
+[% USE Price %]
+[% PROCESS 'accounts.inc' %]
+<table>
+ [% IF ( LibraryName ) %]
+ <tr>
+ <th colspan="5" class="centerednames">
+ <h3>[% LibraryName | html %]</h3>
+ </th>
+ </tr>
+ [% END %]
+
+ <tr>
+ <th colspan="5" class="centerednames">
+ <h2><u>INVOICE</u></h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="5" class="centerednames">
+ <h2>[% Branches.GetName( debit.patron.branchcode ) | html %]</h2>
+ </th>
+ </tr>
+ <tr>
+ <th colspan="5" >
+ Bill to: [% debit.patron.firstname | html %] [% debit.patron.surname | html %] <br />
+ Card number: [% debit.patron.cardnumber | html %]<br />
+ </th>
+ </tr>
+ <tr>
+ <th>Date</th>
+ <th>Description of charges</th>
+ <th>Note</th>
+ <th style="text-align:right;">Amount</th>
+ <th style="text-align:right;">Amount outstanding</th>
+ </tr>
+
+ <tr class="highlight">
+ <td>[% debit.date | $KohaDates%]</td>
+ <td>
+ [% PROCESS account_type_description account=debit %]
+ [%- IF debit.description %], [% debit.description | html %][% END %]
+ </td>
+ <td>[% debit.note | html %]</td>
+ <td class="debit">[% debit.amount | $Price %]</td>
+ <td class="debit">[% debit.amountoutstanding | $Price %]</td>
+ </tr>
+
+ [% IF ( tendered ) %]
+ <tr>
+ <td colspan="3">Amount tendered: </td>
+ <td>[% tendered | $Price %]</td>
+ </tr>
+ <tr>
+ <td colspan="3">Change given: </td>
+ <td>[% change | $Price %]</td>
+ </tr>
+ [% END %]
+
+ <tfoot>
+ <tr>
+ <td colspan="4">Total outstanding dues as on date: </td>
+ [% IF ( debit.patron.account.balance <= 0 ) %]<td class="credit">[% ELSE %]<td class="debit">[% END %][% debit.patron.account.balance | $Price %]</td>
+ </tr>
+ </tfoot>
+</table>
+ };
+ $dbh->do(q{UPDATE letter SET content = ?, is_html = 1 WHERE code = 'ACCOUNT_DEBIT'}, undef, $d_notice);
+
+ NewVersion( $DBversion, 26734, ["Update notices to use defaults", "WARNING - ACCOUNT_DEBIT and ACCOUNT_CREDIT slip templates have been replaced. Backups have been made to the action logs for your reference."] );
+}
+
+$DBversion = '20.12.00.042';
+if( CheckVersion( $DBversion ) ) {
+ unless( foreign_key_exists( 'collections_tracking', 'collectionst_ibfk_1' ) ) {
+ $dbh->do(q{
+ DELETE FROM collections_tracking WHERE colId NOT IN ( SELECT colId FROM collections )
+ });
+ $dbh->do(q{
+ ALTER TABLE collections_tracking
+ ADD CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE
+ });
+ }
+
+ NewVersion( $DBversion, 17202, "Add FK constraint for collection to collections_tracking");
+}
+
+$DBversion = '20.12.00.043';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ UPDATE letter SET
+ content = REPLACE(content, "The following item, [% biblio.title %], has correctly been renewed and is now due on [% checkout.date_due as_due_date => 1 %]" , "The following item, [% biblio.title %], has correctly been renewed and is now due on [% checkout.date_due | $KohaDates as_due_date => 1 %]")
+ WHERE code = 'AUTO_RENEWALS';
+ });
+
+ NewVersion( $DBversion, 28258, "Update AUTO_RENEWAL content");
+}
+
+$DBversion = '20.12.00.044';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ UPDATE language_subtag_registry SET description = 'Ukrainian' WHERE subtag='uk' and type='language' and description='Ukranian'
+ });
+ $dbh->do(q{
+ UPDATE language_descriptions SET description = 'Ukrainian' WHERE subtag='uk' and type='language' and lang='en' and description='Ukranian'
+ });
+
+ NewVersion( $DBversion, 28244, "Fix Ukrainian typo in English");
+}
+
+$DBversion = '20.12.00.045';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type) VALUES ('SearchLimitLibrary', 'both', 'homebranch|holdingbranch|both', "When limiting search results with a library or library group, use the item's home library, or holding library, or both.", 'Choice')
+ });
+
+ NewVersion( $DBversion, 21249, "Adding new system preference SearchLimitLibrary" );
+}
+
+$DBversion = '20.12.00.046';
+if( CheckVersion( $DBversion ) ) {
+ unless ( column_exists('message_queue', 'delivery_note') ) {
+ $dbh->do(q{
+ ALTER TABLE message_queue ADD delivery_note mediumtext AFTER content_type
+ });
+ }
+
+ NewVersion( $DBversion, 14723, "Additional delivery notes to messages" );
+}
+
# SEE bug 13068
# if there is anything in the atomicupdate, read and execute it.
my $update_dir = C4::Context->config('intranetdir') . '/installer/data/mysql/atomicupdate/';
}
}
+sub sanitize_zero_date {
+ my ( $table_name, $column_name ) = @_;
+
+ my (undef, $datatype) = $dbh->selectrow_array(qq|
+ SHOW COLUMNS FROM $table_name WHERE Field = ?|, undef, $column_name);
+
+ if ( $datatype eq 'date' ) {
+ $dbh->do(qq|
+ UPDATE $table_name
+ SET $column_name = NULL
+ WHERE CAST($column_name AS CHAR(10)) = '0000-00-00';
+ |);
+ } else {
+ $dbh->do(qq|
+ UPDATE $table_name
+ SET $column_name = NULL
+ WHERE CAST($column_name AS CHAR(19)) = '0000-00-00 00:00:00';
+ |);
+ }
+}
+
exit;
projects / srvgit / blobdiff