expire borrowers and set new dateexpiry for this year

diff --git a/fer_upisani.sql b/fer_upisani.sql
new file mode 100644 (file)
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
+;