def change_staff_details(id, name, c_no): #Changing staff details cursor.execute( f"update staff set name='{name}', contact_no={c_no} where staff_id='{id}'" ) db.commit() filename, heading, data = get_staff_for_show() gen_details_pdf(filename, heading, data)
def get_block_for_show(): heading_list = [ 'Block ID', 'Type', 'Gender', 'Total Rooms', 'Number of beds', 'Vacant Beds' ] cursor.execute( f"select block_id, block_type, gender, no_of_rooms, no_of_beds, vacant_beds from blocks" ) return ('block_details', heading_list, cursor.fetchall())
def change_stu_details(id, name, g_name, add, c_no, g_c_no, is_veg): #Updating student details cursor.execute( f"update stu_details set name='{name}', guardian_name='{g_name}', address='{add}', contact_no={c_no}, guardian_cont_no={g_c_no} where stu_id={id}" ) cursor.execute(f"update student set is_veg='{is_veg}'") db.commit() filename, heading, data = get_stu_for_show() gen_details_pdf(filename, heading, data)
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 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 get_room_by_cat(cat): cursor.execute( f"select block_id from blocks where gender='{cat}' and status='vacant'" ) b_ids = cursor.fetchall() room_list = [] for bid in b_ids: cursor.execute( f"select room_id from room where block_id='{bid[0]}' and status='vacant'" ) rooms = cursor.fetchall() for room in rooms: room_list.append(room[0]) return room_list
def insert_course(c_id, c_name, sems, btn_window, func): #Adding a course try: cursor.execute( f"insert into courses values ('{c_id}', '{c_name}', {sems})") db.commit() filename, heading, data = get_courses_for_show() gen_details_pdf(filename, heading, data) tkinter.messagebox.showinfo( "RSGH - RMD", f"New Course added successfully!\nCourse ID: {c_id}", parent=btn_window) btn_window.destroy() func() except IntegrityError: tkinter.messagebox.showerror("RSGH - RMD", f'Course {c_id} already exists!', parent=btn_window)
def insert_block(id, type, w_id, gender, mess_id, num_beds, num_single, num_double, num_tripple, btn_window, func): #Creating blocks try: num_rooms = num_single + num_double + num_tripple num_beds = num_single + (num_double * 2) + (num_tripple * 3) cursor.execute( f"insert into blocks values ('{id}', '{type}', '{w_id}', '{gender}', {num_rooms}, {num_beds},'{mess_id}', {num_beds}, 'vacant')" ) db.commit() tkinter.messagebox.showinfo( "RSGH - RMD", f"Block added successfully!\nBlock ID: {id}", parent=btn_window) btn_window.destroy() func() except IntegrityError: tkinter.messagebox.showerror("RSGH - RMD", f"Block {id} already exists!", parent=btn_window) return if type == "NAC": cost_single = 3000 cost_double = 2500 cost_tripple = 2000 elif type == "AC": cost_single = 4000 cost_double = 3500 cost_tripple = 3000 #Creating rooms for num in range(1, num_single + 1): cursor.execute( f"insert into room values ('{id}{str(num).zfill(3)}', '{id}', '{type}', {float(cost_single)}, 1, 0, 'vacant')" ) for num in range(num_single + 1, num_single + num_double + 1): cursor.execute( f"insert into room values ('{id}{str(num).zfill(3)}', '{id}', '{type}', {float(cost_double)}, 2, 0, 'vacant')" ) for num in range(num_single + num_double + 1, num_single + num_double + num_tripple + 1): cursor.execute( f"insert into room values ('{id}{str(num).zfill(3)}', '{id}', '{type}', {float(cost_tripple)}, 3, 0, 'vacant')" ) 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 insert_staff(id, name, c_no, dob, gender, is_warden, btn_window, func): #Adding staff try: cursor.execute( f"insert into staff values ('{id}', '{name}', {c_no}, '{gender}', '{dob}', '{is_warden}')" ) db.commit() filename, heading, data = get_staff_for_show() gen_details_pdf(filename, heading, data) tkinter.messagebox.showinfo( "RSGH - RMD", f"Staff added successfully!\nStaff ID: {id}", parent=btn_window) btn_window.destroy() func() except IntegrityError: tkinter.messagebox.showerror("RSGH - RMD", f'Staff {id} already exists!', parent=btn_window)
def insert_mess(id, btn_window, first_input, charges=2100): #Inserting into mess table try: cursor.execute(f"insert into mess values ('{id}', {charges})") db.commit() filename, heading, data = get_mess_for_show() gen_details_pdf(filename, heading, data) tkinter.messagebox.showinfo("RSGH - RMD", f"Mess added successfully!\nMess ID: {id}", parent=btn_window) for widget in btn_window.winfo_children(): if isinstance(widget, tkinter.Entry): widget.delete(0, 'end') if isinstance(widget, tkinter.Text): widget.delete('1.0', 'end') first_input.focus_set() except IntegrityError: tkinter.messagebox.showerror("RSGH - RMD", f'Mess {id} already exists!', parent=btn_window)
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 del_staff(id, btn_window): cursor.execute(f"select warden_id from blocks") warden_list = [] warden_res = cursor.fetchall() for i in range(len(warden_res)): warden_list.append(warden_res[i][0]) if id in warden_list: cursor.execute(f"select block_id from blocks where warden_id='{id}'") res = cursor.fetchall() if len(res) == 1: tkinter.messagebox.showerror( "RSGH - RMD", f"Staff {id} has been assigned as warden of block {res[0][0]}.\nPlease change the warden of the mentioned block before attempting deletion!", parent=btn_window) #Assigned as warden else: block_list = [] for i in range(len(res)): block_list.append(res[i][0]) tkinter.messagebox.showerror( "RSGH - RMD", f"Staff {id} has been assigned as warden of blocks {block_list}.\nPlease change the warden of the mentioned blocks before attempting deletion!", parent=btn_window) else: cursor.execute(f"delete from staff where staff_id='{id}'") db.commit() filename, heading, data = get_staff_for_show() gen_details_pdf(filename, heading, data) filename, heading, data = get_staff_for_show() gen_details_pdf(filename, heading, data) tkinter.messagebox.showinfo( f"RSGH - RMD", f"Staff {id} has been successfully removed!", parent=btn_window)
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 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 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 get_w_id(b_id): cursor.execute(f"select warden_id from blocks where block_id='{b_id}'") return cursor.fetchone()[0]
def give_add_charges(s_id, amount): cursor.execute( f"update stu_fees set add_charges=add_charges+{amount} where stu_id='{s_id}'" ) db.commit()
def get_updated_w_id(wid): cursor.execute( f"select staff_id from staff where is_warden='Yes' and staff_id!='{wid}'" ) return [res[0] for res in cursor.fetchall()]
def show_courses(): cursor.execute("select course_id from courses") return [res[0] for res in cursor.fetchall()]
def get_all_wardens(): cursor.execute(f"select staff_id from staff where is_warden='Yes'") return [res[0] for res in cursor.fetchall()]
def show_all_stu(): cursor.execute("select stu_id from student") return [res[0] for res in cursor.fetchall()]
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 show_vacant_rooms(): cursor.execute("select room_id from room where status='vacant'") return [res[0] for res in cursor.fetchall()]
def list_stu_paid(): cursor.execute("select distinct stu_id from transactions") return [res[0] for res in cursor.fetchall()]
def change_warden(b_id, w_id): #Change warden cursor.execute( f"update blocks set warden_id='{w_id}' where block_id='{b_id}'") db.commit()
def get_stu_cont(sid): cursor.execute(f"select contact_no from stu_details where stu_id='{sid}'") 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_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]