course_schedule

# -*- coding: utf-8 -*-
"""
Created on Thu Jun 22 17:42:29 2017

@author: Menna
"""

import pymysql
import pandas as pd


db = pymysql.connect(host="127.0.0.1",user="root",passwd="",db="registration_system")
cur = db.cursor()
#cur1 = db.cursor()
cur2 = db.cursor()
cur3 = db.cursor()
cur4 = db.cursor()
cur5 = db.cursor()
cur6 = db.cursor()
cur7 = db.cursor()
cur8 = db.cursor()
cur9 = db.cursor()
cur.execute("SELECT *FROM schedule INNER JOIN course ON schedule.course_code=course.course_code WHERE course_semester LIKE 'Fall%'")
query_result = cur.fetchall()
#cur1.execute("SELECT *FROM schedule INNER JOIN course ON schedule.course_code=course.course_code WHERE course_semester LIKE 'Summer%'")
#query_result1 = cur1.fetchall()
cur2.execute("SELECT *FROM schedule INNER JOIN course ON schedule.course_code=course.course_code WHERE course_semester LIKE 'Spring%'")
query_result2 = cur2.fetchall()
cur3.execute("SELECT course_code , grade , student_semester FROM student_course WHERE grade LIKE 'F%'")
query_result3 = cur3.fetchall()
cur4.execute("SELECT course_code , grade , student_semester FROM student_course ")
query_result4 = cur4.fetchall()
cur5.execute("SELECT * FROM course INNER JOIN fixed_studyplan on course.course_code=fixed_studyplan.course_code WHERE course_creditHours>2 AND EC=1 AND course_semester LIKE 'F%'")
query_result5 = cur5.fetchall()
cur6.execute("SELECT * FROM course INNER JOIN fixed_studyplan on course.course_code=fixed_studyplan.course_code WHERE course_creditHours>2 AND EC=1 AND course_semester LIKE 'Sp%'")
query_result6 = cur6.fetchall()
cur7.execute("SELECT * FROM fixed_studyplan WHERE course_code LIKE 'HS%' and course_semester LIKE 'F%'")
query_result7 = cur7.fetchall()
cur8.execute("SELECT * FROM fixed_studyplan WHERE course_code LIKE 'HS%' and course_semester LIKE 'Sp%'")
query_result8 = cur8.fetchall()
cur9.execute("SELECT course_code , course_name FROM course")
query_result9 = cur9.fetchall()
db.close()

FreqFall = pd.DataFrame()
#FreqSummer = pd.DataFrame()
FreqSpring = pd.DataFrame()
Failed_Courses = pd.DataFrame()
All_Courses = pd.DataFrame()
Core_coursesofFall = pd.DataFrame()
Core_coursesofSpring = pd.DataFrame()
humanFall = pd.DataFrame()
humanSpring = pd.DataFrame()
Name_Courses = pd.DataFrame()




for (i,row) in enumerate(query_result):
    Fall_courses = pd.DataFrame({'CourseCode':[(row[0])],'Count':[row[1]]},index=[i])
    FreqFall = FreqFall.append(Fall_courses)
FreqFall = FreqFall.groupby('CourseCode').count()




#for (i,row) in enumerate(query_result1):
#    Summer_courses = pd.DataFrame({'CourseCode':[(row[0])],'Count':[row[1]]},index=[i])
#    FreqSummer = FreqSummer.append(Summer_courses)
#FreqSummer = FreqSummer.groupby('CourseCode').count()
#


for (i,row) in enumerate(query_result2):
    Spring_courses = pd.DataFrame({'CourseCode':[(row[0])],'Count':[row[1]]},index=[i])
    FreqSpring = FreqSpring.append(Spring_courses)
FreqSpring = FreqSpring.groupby('CourseCode').count()


for (i,row) in enumerate(query_result3):
    Failed = pd.DataFrame({'CourseCode':[(row[0])],'Grade':[row[1]],'Semester':[row[2]]},index=[i])
    Failed_Courses = Failed_Courses.append(Failed)
Failed_Courses = Failed_Courses.groupby(["CourseCode", "Semester"]).size().reset_index(name="SumFailed")



for (i,row) in enumerate(query_result4):
    Courses = pd.DataFrame({'CourseCode':[(row[0])],'Grade':[row[1]],'Semester':[row[2]]},index=[i])
    All_Courses = All_Courses.append(Courses)
All_Courses = All_Courses.groupby(["CourseCode", "Semester"]).size().reset_index(name="Total")


# ALL core courses in the fall
for (i,row) in enumerate(query_result5):
    CoreFall = pd.DataFrame({'CourseCode':[(row[0])],'Course Name':[row[1]],'Semester':[row[6]],'term':[row[7]]},index=[i])
    Core_coursesofFall = Core_coursesofFall.append(CoreFall)

CoursesOfFall = [] # courses ely tl3a mel teqrar bt3 el fall in array version
Fall_CounterMax = FreqFall['Count'].max()
for (i,row) in enumerate(FreqFall['Count']):
    if FreqFall['Count'].iloc[i] == Fall_CounterMax or FreqFall['Count'].iloc[i] == Fall_CounterMax-1:
        CoursesOfFall.append(FreqFall.index[i])

FallCourses=pd.DataFrame(CoursesOfFall) # courses ely tl3a mel teqrar bt3 el fall in dataframe version
resultFall = pd.concat([FallCourses,Core_coursesofFall['CourseCode']],ignore_index=True)
resultFall = resultFall.drop_duplicates().reset_index(drop=True)


for (i,row) in enumerate(query_result7):
    HFall = pd.DataFrame({'CourseCode':[(row[0])]},index=[i])
    humanFall = humanFall.append(HFall)
resultFall = pd.concat([resultFall,humanFall['CourseCode']],ignore_index=True)
resultFall = resultFall.drop_duplicates().reset_index(drop=True)
resultFall.columns = ['CourseCode']
resultFall = (list(resultFall['CourseCode']))

#CoursesOfSummer = []
#Summer_CounterMax = FreqSummer['Count'].max()
#for (i,row) in enumerate(FreqSummer['Count']):
#    if FreqSummer['Count'].iloc[i] == Summer_CounterMax or FreqSummer['Count'].iloc[i] == Summer_CounterMax-1:
#        CoursesOfSummer.append(FreqSummer.index[i])


# ALL core courses in the Spring
for (i,row) in enumerate(query_result6):
    CoreSpring = pd.DataFrame({'CourseCode':[(row[0])],'Course Name':[row[1]],'Semester':[row[6]],'term':[row[7]]},index=[i])
    Core_coursesofSpring = Core_coursesofSpring.append(CoreSpring)

CoursesOfSpring = [] # courses ely tl3a mel teqrar bt3 el spring in array version
Spring_CounterMax = FreqSpring['Count'].max()
for (i,row) in enumerate(FreqSpring['Count']):
    if FreqSpring['Count'].iloc[i] == Spring_CounterMax or FreqSpring['Count'].iloc[i] == Spring_CounterMax-1:
        CoursesOfSpring.append(FreqSpring.index[i])

SpringCourses=pd.DataFrame(CoursesOfSpring) # courses ely tl3a mel teqrar bt3 el spring in dataframe version
resultSpring = pd.concat([SpringCourses,Core_coursesofSpring['CourseCode']],ignore_index=True)
resultSpring = resultSpring.drop_duplicates().reset_index(drop=True)


for (i,row) in enumerate(query_result8):
    HSpring = pd.DataFrame({'CourseCode':[(row[0])]},index=[i])
    humanSpring = humanSpring.append(HSpring)
resultSpring = pd.concat([resultSpring,humanSpring['CourseCode']],ignore_index=True)
resultSpring = resultSpring.drop_duplicates().reset_index(drop=True)
resultSpring.columns = ['CourseCode']
resultSpring = (list(resultSpring['CourseCode']))


Merged_Courses = pd.merge(All_Courses, Failed_Courses, on=['CourseCode', 'Semester'])

input_last_term = input('Input Last Term:')
print("")

PercentageOfFailure = Merged_Courses['SumFailed']/Merged_Courses['Total']
PercentageOfFailure = PercentageOfFailure.to_frame('Percentage')
Merged_Courses = pd.concat([Merged_Courses, PercentageOfFailure], axis=1)
LastTermFailures = Merged_Courses[ Merged_Courses['Semester'] == input_last_term]
LastTermFailures = LastTermFailures[LastTermFailures['SumFailed'] >= 10]
LastTermFailures = list(LastTermFailures['CourseCode'])



for (i,row) in enumerate(query_result9):
    Namecourses = pd.DataFrame({'CourseCode':[(row[0])],'CourseName':[row[1]]},index=[i])
    Name_Courses = Name_Courses.append(Namecourses)


if('Fall' in input_last_term):
    resultSpring = resultSpring + LastTermFailures
    resultSpring = set(resultSpring)
    resultSpring = list(resultSpring)
    print("Spring Semester")
    Output = pd.DataFrame(resultSpring, columns=['CourseCode'])
elif('Spring' in input_last_term):
    resultSummer = []
    resultSummer = LastTermFailures
    print("Summer Semester")
    Output = pd.DataFrame(resultSummer, columns=['CourseCode'])
elif('Summer' in input_last_term):
    resultFall = resultFall + LastTermFailures
    resultFall = set(resultFall)
    resultFall = list(resultFall)
    print("Fall Semester")
    Output = pd.DataFrame(resultFall, columns=['CourseCode'])

FinalOutput = pd.merge(Name_Courses, Output, on='CourseCode')
print("")
print (FinalOutput)

db1 = pymysql.connect(host="127.0.0.1",user="root",passwd="",db="recommender_system")
cur10 = db1.cursor()
for(i,row) in enumerate(FinalOutput.iterrows()):
    v=(FinalOutput['CourseCode'][i], FinalOutput['CourseName'][i])
    cur10.execute("INSERT INTO output_schedule (course_code, course_name) VALUES (%s, %s)" , v)
    db1.commit()
db1.close()