From 640e6826ca1751a4a938637ecb63e544c32a902c Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Wed, 19 Oct 2011 17:45:50 +0200 Subject: [PATCH] expire borrowers and set new dateexpiry for this year --- fer_upisani.sql | 45 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) create mode 100644 fer_upisani.sql diff --git a/fer_upisani.sql b/fer_upisani.sql new file mode 100644 index 0000000..1abf940 --- /dev/null +++ b/fer_upisani.sql @@ -0,0 +1,45 @@ +-- import students enrolled for this year + +drop table fer_upisani_2011 ; + +create table fer_upisani_2011 ( + jmbag varchar(10) primary key, + ime text not null, + prezime text not null, + nick text, + email text +); + +LOAD DATA INFILE '/srv/ferlib2koha/report_62836_1026_Aktivni_studenti.unl' INTO TABLE fer_upisani_2011 + FIELDS TERMINATED BY '#' + LINES TERMINATED BY '\r\n' +; + +select count(*) from fer_upisani_2011 + +exit +end + +-- students to expire +select borrowernumber,userid,email,dateexpiry +from borrowers +where categorycode='S' and length(userid) > 0 and dateexpiry > '2011-09-30' +; + +-- students to enroll +select borrowernumber,userid,dateexpiry,jmbag,nick +from borrowers +full join fer_upisani_2011 on nick = userid +where categorycode='S' and length(userid) > 0 and dateexpiry > '2011-09-30' ; + +begin; + +update borrowers +set dateexpiry='2011-09-30' +where categorycode='S' and length(userid) > 0 and dateexpiry > '2011-09-30' +; + +update borrowers,fer_upisani_2011 +set dateexpiry='2012-10-31' +where categorycode='S' and length(userid) > 0 and nick = userid +; -- 2.20.1