def gen_monthly_bill(s_id, t_id): #Student details cursor.execute( f"select name, course_id, room_id from stu_details, student where (stu_details.stu_id=student.stu_id) and student.stu_id={s_id}" ) result = cursor.fetchone() name = result[0] c_id = result[1] r_id = result[2] cursor.execute(f"select room_type from room where room_id='{r_id}'") r_type = cursor.fetchone()[0] #Fee details cursor.execute( f"select date_of_tran, month_paid, amount, remarks from transactions where transaction_id={t_id}" ) result = cursor.fetchone() t_date = result[0] month_paid_list = [result[1]] total_charge = result[2] remarks = json.loads(result[3]) r_charge = remarks["room_charge"] m_charge = remarks["mess_charges"] late_fee = remarks["late_fee"] add_fee = remarks["add_charges"] gen_pdf(name, s_id, c_id, r_id, r_type, t_id, t_date, month_paid_list, r_charge, m_charge, late_fee, add_fee, total_charge)
def get_fee_details(sid): cursor.execute( f"select room_charges, mess_charges, add_charges, total_charge from stu_fees where stu_id={sid}" ) result = cursor.fetchone() room_charges = result[0] mess_charges = result[1] add_charges = result[2] total_charge = result[3] return (room_charges, mess_charges, add_charges, total_charge)
def del_stu(s_id, btn_window): cursor.execute(f"select room_id from student where stu_id={s_id}") r_id = cursor.fetchone()[0] cursor.execute(f"select block_id from room where room_id='{r_id}'") b_id = cursor.fetchone()[0] #Update block status cursor.execute(f"select status from blocks where block_id='{b_id}'") status = cursor.fetchone()[0] if status == "occupied": cursor.execute( f"update blocks set status='vacant' where block_id='{b_id}'") cursor.execute( f"update blocks set vacant_beds=vacant_beds+1 where block_id='{b_id}'") #Update room_status cursor.execute(f"select status from room where room_id='{r_id}'") status = cursor.fetchone()[0] if status == "occupied": cursor.execute( f"update room set status='vacant' where room_id='{r_id}'") cursor.execute( f"update room set occupants=occupants-1 where room_id='{r_id}'") #Removing student details cursor.execute(f"delete from student where stu_id={s_id}") cursor.execute(f"delete from stu_fees where stu_id={s_id}") cursor.execute(f"delete from stu_details where stu_id={s_id}") db.commit() filename, heading, data = get_stu_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_room_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_block_for_show() gen_details_pdf(filename, heading, data) tkinter.messagebox.showinfo( f"RSGH - RMD", f"Student {s_id} has been successfully removed!", parent=btn_window)
def transaction(s_id): cursor.execute(f"select months_due from student where stu_id={s_id}") month_list = json.loads(f"{cursor.fetchone()[0]}") #Month to be paid month_paid = month_list.pop(0) month_paid_index = month_index[month_paid] if len(month_list) == 0: month_list = [ "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEPT", "OCT", "NOV", "DEC" ] months_due = json.dumps(month_list) #Calculating Charges & Date cursor.execute( f"select room_charges, mess_charges, add_charges, total_charge from stu_fees where stu_id={s_id}" ) result = cursor.fetchone() room_charges = result[0] mess_charges = result[1] add_charges = result[2] total_charge = result[3] date = datetime.now() month = int(date.strftime("%m")) late_fee = (float(100 * (abs(month - month_paid_index))) if month_paid_index < month else 0) fee_amount = total_charge + late_fee #Remarks to get data later during bill generation remarks = json.dumps({ "room_charge": room_charges, "mess_charges": mess_charges, "late_fee": late_fee, "add_charges": add_charges }) #Making transaction cursor.execute( f"insert into transactions (stu_id, amount, month_paid, remarks) values ({s_id}, {fee_amount}, '{month_paid}', '{remarks}')" ) cursor.execute( f"update student set months_due='{months_due}' where stu_id={s_id}") cursor.execute(f"update stu_fees set add_charges=0 where stu_id='{s_id}'") db.commit()
def extend_block(bid, type, size, num_of_rooms): cursor.execute(f"select room_id from room where block_id='{bid}'") n = int(cursor.fetchall()[-1][0][2:]) cursor.execute(f"select block_type from blocks where block_id='{bid}'") type = cursor.fetchone()[0] if type == "NAC": cost_single = 3000 cost_double = 2500 cost_tripple = 2000 elif type == "AC": cost_single = 4000 cost_double = 3500 cost_tripple = 3000 if size.lower() == 'single': beds = 1 cost = cost_single elif size.lower() == 'double': beds = 2 cost = cost_double elif size.lower() == 'triple': beds = 3 cost = cost_tripple #Adding rooms for num in range(n + 1, n + num_of_rooms + 1): cursor.execute( f"insert into room values ('{bid}{str(num).zfill(3)}', '{bid}', '{type}', {float(cost)}, {beds}, 0, 'vacant')" ) cursor.execute( f"update blocks set no_of_rooms=no_of_rooms+{num_of_rooms}, no_of_beds = no_of_beds+{num_of_rooms*beds}, vacant_beds=vacant_beds+{num_of_rooms*beds}, status='vacant' where block_id='{bid}'" ) db.commit() filename, heading, data = get_block_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_room_for_show() gen_details_pdf(filename, heading, data)
def del_block(id, btn_window): cursor.execute(f"select sum(occupants) from room where block_id='{id}'") occupants = cursor.fetchone()[0] if occupants != 0: tkinter.messagebox.showerror( 'RSGH - RMD', f'Block {id} is occupied. Please empty block before deletion!', parent=btn_window) else: cursor.execute(f"delete from room where block_id='{id}'") cursor.execute(f"delete from blocks where block_id='{id}'") db.commit() filename, heading, data = get_block_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_room_for_show() gen_details_pdf(filename, heading, data) tkinter.messagebox.showinfo( f"RSGH - RMD", f"Block {id} has been successfully removed!", parent=btn_window)
def insert_stu(name, dob, c_id, g_name, gender, add, r_id, c_no, g_c_no, blood_grp, is_veg): #Inserting into stu_details table cursor.execute( f"insert into stu_details (name, dob, guardian_name, gender, address, contact_no, guardian_cont_no, blood_grp) values ('{name}', '{dob}', '{g_name}', '{gender}', '{add}', {c_no}, {g_c_no}, '{blood_grp}')" ) db.commit() cursor.execute(f"select last_insert_id() from stu_details ") id = cursor.fetchone()[0] cursor.execute(f"select cost from room where room_id='{r_id}'") room_charges = cursor.fetchone()[0] cursor.execute(f"select block_id from room where room_id='{r_id}'") block_id = cursor.fetchone()[0] cursor.execute(f"select mess_id from blocks where block_id='{block_id}'") mess_id = cursor.fetchone()[0] cursor.execute(f"select mess_charges from mess where mess_id='{mess_id}'") mess_charges = cursor.fetchone()[0] #Inserting into stu_fees table cursor.execute( f"insert into stu_fees (stu_id, room_charges, mess_charges, add_charges) values ({id}, {room_charges}, {mess_charges}, 0)" ) db.commit() cursor.execute(f"select total_charge from stu_fees where stu_id='{id}'") total_charge = cursor.fetchone()[0] #Inserting into student table cursor.execute( f"insert into student (stu_id, course_id, room_id, is_veg) values ({id}, '{c_id}', '{r_id}', '{is_veg}')" ) db.commit() #Update room occupants and status cursor.execute(f"select occupants from room where room_id='{r_id}'") occupants = int(cursor.fetchone()[0]) cursor.execute(f"select no_of_beds from room where room_id='{r_id}'") num_beds = int(cursor.fetchone()[0]) cursor.execute( f"update room set occupants=occupants+1 where room_id='{r_id}'") if occupants + 1 == num_beds: cursor.execute( f"update room set status='occupied' where room_id='{r_id}'") #Update block status cursor.execute( f"update blocks set vacant_beds=vacant_beds-1 where block_id='{block_id}'" ) cursor.execute( f"select vacant_beds from blocks where block_id='{block_id}'") vacancy = int(cursor.fetchone()[0]) if vacancy == 0: cursor.execute( f"update blocks set status='occupied' where block_id='{block_id}'") db.commit() filename, heading, data = get_stu_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_room_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_block_for_show() gen_details_pdf(filename, heading, data)
def change_room(stu_id, new_b_id, new_r_id): cursor.execute(f"select cost from room where room_id='{new_r_id}'") room_charges = cursor.fetchone()[0] cursor.execute(f"select mess_id from blocks where block_id='{new_b_id}'") mess_id = cursor.fetchone()[0] cursor.execute(f"select mess_charges from mess where mess_id='{mess_id}'") mess_charges = cursor.fetchone()[0] cursor.execute(f"select room_id from student where stu_id={stu_id}") old_r_id = cursor.fetchone()[0] cursor.execute(f"select block_id from room where room_id='{old_r_id}'") old_b_id = cursor.fetchone()[0] #Change room cursor.execute( f"update student set room_id='{new_r_id}' where stu_id={stu_id}") cursor.execute( f"update stu_fees set room_charges={room_charges}, mess_charges={mess_charges} where stu_id={stu_id}" ) #Update room occupants and status cursor.execute(f"select occupants from room where room_id='{new_r_id}'") occupants = int(cursor.fetchone()[0]) cursor.execute(f"select no_of_beds from room where room_id='{new_r_id}'") num_beds = int(cursor.fetchone()[0]) cursor.execute( f"update room set occupants=occupants+1 where room_id='{new_r_id}'") if occupants + 1 == num_beds: cursor.execute( f"update room set status='occupied' where room_id='{new_r_id}'") #Update block status cursor.execute( f"update blocks set vacant_beds=vacant_beds-1 where block_id='{new_b_id}'" ) db.commit() cursor.execute( f"select vacant_beds from blocks where block_id='{new_b_id}'") vacancy = int(cursor.fetchone()[0]) if vacancy == 0: cursor.execute( f"update blocks set status='occupied' where block_id='{new_b_id}'") #Update old room/block details cursor.execute( f"update room set occupants=occupants-1, status='vacant' where room_id='{old_r_id}'" ) cursor.execute( f"update blocks set vacant_beds=vacant_beds+1, status='vacant' where block_id='{old_b_id}'" ) db.commit() filename, heading, data = get_stu_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_block_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_room_for_show() gen_details_pdf(filename, heading, data)
def get_block_gender(bid): cursor.execute(f"select gender from blocks where block_id='{bid}'") return cursor.fetchone()[0]
def get_stu_address(sid): cursor.execute(f"select address from stu_details where stu_id='{sid}'") return cursor.fetchone()[0]
def get_guar_name(sid): cursor.execute( f"select guardian_name from stu_details where stu_id='{sid}'") return cursor.fetchone()[0]
def get_staff_dob(stid): cursor.execute(f"select dob from staff where staff_id='{stid}'") return cursor.fetchone()[0]
def get_meal_pref(sid): cursor.execute(f"select is_veg from student where stu_id='{sid}'") return cursor.fetchone()[0]
def get_stu_bgrp(sid): cursor.execute(f"select blood_grp from stu_details where stu_id='{sid}'") return cursor.fetchone()[0]
def get_w_id(b_id): cursor.execute(f"select warden_id from blocks where block_id='{b_id}'") return cursor.fetchone()[0]
def get_tmonth(sid, tmonth): cursor.execute( f"select transaction_id from transactions where stu_id='{sid}' and month_paid='{tmonth}'" ) return cursor.fetchone()[0]
def get_block_mess(bid): cursor.execute(f"select mess_id from blocks where block_id='{bid}'") return cursor.fetchone()[0]
def get_trip_room(bid): cursor.execute( f"select count(*) from room where block_id='{bid}' and no_of_beds=3 group by no_of_beds;" ) return cursor.fetchone()[0]
def get_staff_pos(stid): cursor.execute(f"select is_warden from staff where staff_id='{stid}'") return cursor.fetchone()[0]
def get_staff_cont(stid): cursor.execute(f"select contact_no from staff where staff_id='{stid}'") return cursor.fetchone()[0]
def get_stu_room(sid): cursor.execute(f"select room_id from student where stu_id='{sid}'") return cursor.fetchone()[0]
def get_stu_cont(sid): cursor.execute(f"select contact_no from stu_details where stu_id='{sid}'") return cursor.fetchone()[0]
def get_last_stu_id(): cursor.execute(f"select last_insert_id() from stu_details") return cursor.fetchone()[0]
def get_staff_gender(stid): cursor.execute(f"select gender from staff where staff_id='{stid}'") return cursor.fetchone()[0]
def get_block_type(b_id): cursor.execute(f"select block_type from blocks where block_id='{b_id}'") return cursor.fetchone()[0]
def get_stu_gender(sid): cursor.execute(f"select gender from stu_details where stu_id='{sid}'") return cursor.fetchone()[0]