<reponame>Devansh3712/python
"""
(1)

Write a program to w.r.t a “:” delimited file

Customer.txt which should have the following data:
Customerno : customername : monthlybuy : credit

i) Write a function to read customer no, customer name, monthly buying cost and assign credit as A in
case monthly buying capacity is more than 500000, B in case buying capacity is between 200000 and
500000 and C in case buying capacity is less than 200000

ii) Write a function to read all the records from the file and print all the A credit customers, B credit
Customers and C credit Customers

iii) To copy the records in a file in order of Customerno (sorted in ascending order)
"""

import csv

# (i)
def assign_credit() -> bool:

    try:
        file = open("customer.csv")
        reader_obj = csv.reader(file, delimiter = ":")
        contents = list(reader_obj)
        file.close()

        for record in contents:

            if int(record[2]) > 500000:
                record[3] = "A"

            elif int(record[2]) in range (200000, 500001):
                record[3] = "B"

            else:
                record[3] = "C"

        file = open("customer.csv", "w")
        writer_obj = csv.writer(file, delimiter = ":")
        writer_obj.writerows(contents)
        file.close()

        return True

    except:
        return False

# (ii)
def print_records():

    file = open("customer.csv")
    reader_obj = csv.reader(file, delimiter = ":")
    A_credit = []
    B_credit = []
    C_credit = []

    for record in reader_obj:

        if record[3] == "A":
            A_credit.append(record[1])

        elif record[3] == "B":
            B_credit.append(record[1])

        else:
            C_credit.append(record[1])

    print(f"A credit customers: {A_customer}")
    print(f"B credit customers: {B_customer}")
    print(f"C credit customers: {C_customer}")

    file.close()

# (iii)
def copy_file() -> bool:

    try:
        file = open("customer.csv")
        reader_obj = csv.reader(file, delimiter = ":")
        contents = list(reader_obj)
        file.close()

        contents.sort(key = lambda x: x[1])

        file = open("customer.csv", "w")
        writer_obj = csv.writer(file, delimiter = ":")
        writer_obj.writerows(contents)
        file.close()

        return True

    except:
        return False


"""
(2)

Write MySQL command / Python code for the queries given based on a relation CLUB from a
database “Sportsclub”

i) Create a function that prints number of employees in each type of membership

ii) Create a function that prints the name of the employee who is paying is highest fee and lowest fee
"""

import mysql.connector as mc

connection = mc.connect(
    host = "localhost",
    username = "root",
    password = "<PASSWORD>",
    database = "Sportsclub",
    autocommit = True
)

cursor = connection.cursor(buffered = True)

# (i)
def number_of_records():

    query = "select Membership, count(*) from CLUB group by Membership"
    cursor.execute(query)
    result = cursor.fetchall()

    for data in result:
        print(f"{data[0]}: {data[1]}")

# (ii)
def employee_fee():

    query = "select Mname from CLUB where Fees = (select min(Fees) from CLUB)"
    cursor.execute(query)
    result = cursor.fetchall()
    print(f"Lowest fee paying employee: {result[0]}")

    query = "select Mname from CLUB where Fees = (select max(Fees) from CLUB)"
    cursor.execute(query)
    result = cursor.fetchall()
    print(f"Highest fee paying employee: {result[0]}")
