Untitled

drop table Fine;
drop table Borrower;

set @amt = 0;

Create Table Borrower(roll_no int, name varchar(60), date_of_issue date, name_of_book varchar(60), status varchar(20));

Create table Fine(roll_no int, return_date date, amt int);

Alter table Borrower add primary key(roll_no,name_of_book);

alter table Fine add foreign key(roll_no) references Borrower(roll_no);

Insert into Borrower values(1,'Rutu', '2021/08/13', 'How to', 'I');
Insert into Borrower values(2,'Tanish', '2022/08/01', 'I am good', 'I');
Insert into Borrower values(3,'Tanmya', '2018/08/09', 'Python is good', 'I');

select * from Borrower;

drop procedure calc;

delimiter @@

create  procedure calc(
	In b_name varchar(30)
)

Begin 
	
	declare amt int;
	declare roll int;
	
	select DATEDIFF(current_date, date_of_issue),roll_no
	into amt, roll
	from Borrower 
	where name_of_book = b_name;
	
	if amt>15 and amt<=30 then
		insert into Fine values(roll,current_date,(amt-15)*5);
	elseif amt>30 then
		insert into Fine values(roll,current_date,(amt-30)*50 + 15*5);
	end if;
	
	update Borrower set status='R' where roll_no=roll;

End@@
	
delimiter ;

call calc('How to');
call calc('I am good');

select * from Fine;
select * from Borrower;