-- 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
;
