"""
    send_extracted_catalog_to_csv.py
    --------------------------------

    This module contains a set of functions that read the extracted
    catalog data from the database and export it to Excel format along
    with the row Ids so that the data can be refined and the database
    can be updated.

"""

__author__ = "Steven M. Satterfield"

import xlsxwriter
from firebase import firebase

from knowledgearea import KnowledgeArea
from learningobjective import LearningObjective
from course import Course
from session import Session
from coursedow import CourseDOW


firebase = firebase.FirebaseApplication('https://scholacity-org.firebaseio.com/')

knowledgeAreas = []
courses = []
learningObjectives = []
sessions = []
coursesDOW = []

workbook = xlsxwriter.Workbook('../outputs/Course_Catalog_Review_Spring_2021.xlsx')
coursesWsRow = 0
sessionsWsRow = 0
courseDOWWsRow = 0


def GetCoursesDOW(firebase: firebase) -> None:
    """
    

    Get DOW from database and insert them into global collection
    
    :param firebase: a firebase connection

    """
    
    global coursesDOW
    obj_key_list = []
    
    result = firebase.get('/courses_dow', None)
    
    if result is None:
        print("no courses DOW found")
        return
    
    for i in result.keys():
        obj_key_list.append(i)
        
    for i in obj_key_list:
        courseDOW = CourseDOW()
        courseDOW.setId(i)
        courseDOW.setCourseId(result[i]['courseid'])
        courseDOW.setDOW(result[i]['DOW'])
        coursesDOW.append(courseDOW)




def GetSessions(firebase: firebase) -> None:
    """

       Get Sessions from the database and insert them
       into the global collection.
       
       :param firebase: a firebase connection

    """

    global sessions
    obj_key_list = []
    
    result = firebase.get('/session', None)
    
    if result is None:
        print("no sessions found")
        return
    
    for i in result.keys():
        obj_key_list.append(i)
        
    for i in obj_key_list:
        session = Session()
        session.setId(i)
        session.setCourseId(result[i]['courseid'])
        session.setDOW(result[i]['DOW'])
        session.setSessionNumber(result[i]['session_number'])
        session.setSessionDate(result[i]['session_date'])
        session.setSessionTimeStart(result[i]['session_time_start'])
        session.setSessionTimeEnd(result[i]['session_time_end'])
        sessions.append(session)
        
        

def GetKnowledgeAreas(firebase: firebase) -> None:
    """
        Get  KnowledgeAreas from the database and
        insert them into the global collection.

        :param firebase: a firebase connection

    """

    global knowledgeAreas

    obj_key_list = []

    result = firebase.get('/knowledgearea', None)

    if result is None:
        return

    for i in result.keys():
        obj_key_list.append(i)

    for i in obj_key_list:
        knowledgeArea = KnowledgeArea()
        knowledgeArea.setText(result[i]['content'])
        knowledgeArea.setId(i)
        knowledgeAreas.append(knowledgeArea)


def GetCourses(firebase: firebase) -> None:
    """

        Get Courses from the database and insert them into the
        global collection.

        :param firebase: A firebase connection

    """

    global courses
    obj_key_list = []

    result = firebase.get('/course', None)

    if result is None:
        return

    for i in result.keys():
        obj_key_list.append(i)

    for i in obj_key_list:
        course = Course()
        course.setId(i)
        course.setKnowledgeAreaId(result[i]['knowledgeareaid'])
        course.setCatalogId(result[i]['catalogid'])
        course.setTitle(result[i]['name'])
        course.setDescription(result[i]['description'])
        course.setInstructor(result[i]['instructor'])
        course.setFee(result[i]['fee'])
        courses.append(course)


def GetLearningObjectives(firebase: firebase) -> None:
    """

        Get LearningObjectives from the database and insert them into the
        global collection.

        Parameters
        ----------
        :param firebase: A firebase connection

    """

    global learningObjectives
    obj_key_list = []

    result = firebase.get('/learningobjective', None)

    if result is None:
        return

    for i in result.keys():
        obj_key_list.append(i)

    for i in obj_key_list:
        lo = LearningObjective()
        lo.setId(i)
        lo.setCourseId(result[i]['courseid'])
        lo.setText(result[i]['content'])
        learningObjectives.append(lo)


def writeCourseesWorksheet(wb: xlsxwriter.Workbook) -> None:
    """

        Write a separate worksheet for all courses.

        :param wb: Excel Workbook Object

    """

    global knowledgeAreas
    global coursesWsRow


    ws = wb.add_worksheet()
    ws.name = "Courses"

    row = coursesWsRow
    col = 0

    ws.write(row, col, 'LearningObjective')
    col += 1

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1

    ws.write(row, col, 'CourseTitle - As Revised')
    col += 1

    ws.write(row, col, 'CourseDescription - As Extracted')
    col += 1

    ws.write(row, col, 'CourseDescription - As Revised')
    col += 1

    ws.write(row, col, 'Instructor - As Extracted')
    col += 1

    ws.write(row, col, 'Instructor - As Revised')
    col += 1

    ws.write(row, col, 'Fee - As Extracted')
    col += 1

    ws.write(row, col, 'Fee - As Revised')

    coursesWsRow += 1
    col = 0

    for knowledgeArea in knowledgeAreas:
        writeCourseRows(ws, knowledgeArea)


def writeDOWWorksheet(wb: xlsxwriter.Workbook) -> None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """

    global coursesDOW
    global courseDOWWsRow
    
    ws = wb.add_worksheet()
    ws.name = "DOW"
    
    row = courseDOWWsRow
    col = 0
    
    ws.write(row, col, 'SessionId')
    col += 1

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'DOW - As Extracted')
    col += 1

    ws.write(row, col, 'DOW - As Revised')
    col += 1

    courseDOWWsRow += 1
    col = 0

    writeDOWRows(ws)



def writeDOWRows(ws: xlsxwriter.worksheet) -> None:
    """

        Write the rows in the DOW worksheet for all courseDOW.

        :param ws: Excel Worksheet Object

    """

    global coursesDOW
    global courseDOWWsRow

    col = 0

    for dow in coursesDOW:
        courseDOWId = dow.getId()
        ws.write(courseDOWWsRow, col, courseDOWId)
        col += 1

        courseId = dow.getCourseId()
        ws.write(courseDOWWsRow, col, courseId)
        col += 1
        
        ws.write(courseDOWWsRow, col, dow.getDOW())
        
        courseDOWWsRow += 1
        col = 0


def writeSessionsWorksheet(wb: xlsxwriter.Workbook) -> None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """

    global sessions
    global sessionsWsRow
    
    ws = wb.add_worksheet()
    ws.name = "Sessions"
    
    row = sessionsWsRow
    col = 0
    
    ws.write(row, col, 'SessionId')
    col += 1

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'DOW - As Extracted')
    col += 1

    ws.write(row, col, 'DOW - As Revised')
    col += 1

    ws.write(row, col, 'Session_number')
    col += 1

    ws.write(row, col, 'Session_date - As Extracted')
    col += 1

    ws.write(row, col, 'Session_date - As Revised')
    col += 1

    ws.write(row, col, 'Session_start_time')
    col += 1

    ws.write(row, col, 'Session_start_time - As Revised')
    col += 1

    ws.write(row, col, 'Session_end_time')
    col += 1

    ws.write(row, col, 'Session_end_time - As Revised')
    
    sessionsWsRow += 1
    col = 0

    writeSessionRows(ws)
    

def writeSessionRows(ws: xlsxwriter.worksheet) -> None:
    """

        Write the rows in the sessions worksheet for all sessions.

        :param ws: Excel Worksheet Object
        :param session: Session

    """

    global sessions
    global sessionsWsRow

    col = 0

    for session in sessions:
        sessionId = session.getId()
        ws.write(sessionsWsRow, col, sessionId)
        col += 1

        courseId = session.getCourseId()
        ws.write(sessionsWsRow, col, courseId)
        col += 1
        
        ws.write(sessionsWsRow, col, session.getDOW())
        col += 2
        
        ws.write(sessionsWsRow, col, session.getSessionNumber())
        col += 1
        ws.write(sessionsWsRow, col, session.getSessionDate())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeStart())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
        
        
        sessionsWsRow += 1
        col = 0


def writeCourseRows(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea) -> None:
    """

        Write the rows in the courses worksheet for all courses under
        the knowledgearea.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea

    """

    global courses
    global coursesWsRow

    col = 0
    knowledgeAreaId = knowledgeArea.getId()

    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            ws.write(coursesWsRow, col, knowledgeArea.getText())
            col += 1

            courseId = course.getId()
            ws.write(coursesWsRow, col, courseId)
            col += 1
            ws.write(coursesWsRow, col, course.getTitle())
            col += 2
            ws.write(coursesWsRow, col, course.getDescription())
            col += 2
            ws.write(coursesWsRow, col, course.getInstructor())
            col += 2
            ws.write(coursesWsRow, col, course.getFee())
            
            coursesWsRow += 1
            col = 0


def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) -> None:
    """

        Iterate through the global collection of KnowledgeAreas,
        create a worksheet for each, and call method to populate
        the worksheet with courses and learning objectives.

        :param wb: Excel Workbook Object

    """

    global knowledgeAreas

    for knowledgeArea in knowledgeAreas:
        ws = wb.add_worksheet()
        ws.name = knowledgeArea.getText()[0:31]
        writeWorksheet(ws, knowledgeArea)


def writeWorksheet(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea) -> None:
    """

        Iterate through the global collection of KnowledgeAreas
        and write them to a worksheet in an Excel workbook.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea Object

    """

    global courses
    global learningObjectives

    row = 0
    col = 0

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1

    ws.write(row, col, 'learningObjectiveId')
    col += 1

    ws.write(row, col, 'learningObjective - As Extracted')
    col += 1

    ws.write(row, col, 'learningObjective - As Revised')

    row += 1
    col = 0

    knowledgeAreaId = knowledgeArea.getId()

    for course in courses:
        #print("Course {}, {} ".format(course.getTitle(), course.getKnowledgeAreaId()))
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            courseId = course.getId()
            if course.getDescription() == "":
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                # write another row for the course to allow for 2 learning outcomes
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                continue
            else:
                for learningObjective in learningObjectives:
                    #print("LearningObjective: {}, {} ".format(learningObjective.getText(), learningObjective.getCourseId()))

                    if str(learningObjective.getCourseId()) == str(courseId):
                        ws.write(row, col, courseId)
                        col += 1
                        ws.write(row, col, course.getTitle())
                        col += 1
                        ws.write(row, col, learningObjective.getId())
                        col += 1
                        ws.write(row, col, learningObjective.getText())
                        row += 1
                        col = 0
                continue


if __name__ == "__main__":
    GetKnowledgeAreas(firebase)
    GetCourses(firebase)
    GetLearningObjectives(firebase)
    GetSessions(firebase)
    GetCoursesDOW(firebase)

    writeCourseesWorksheet(workbook)
    writeKnowledgeAreaWorksheets(workbook)
    writeSessionsWorksheet(workbook)
    writeDOWWorksheet(workbook)
    workbook.close()

08c4a1fc9d13553f637e66fc368b0a24f7d66a97
<mask token>


def GetCoursesDOW(firebase: firebase) ->None:
    """
    

    Get DOW from database and insert them into global collection
    
    :param firebase: a firebase connection

    """
    global coursesDOW
    obj_key_list = []
    result = firebase.get('/courses_dow', None)
    if result is None:
        print('no courses DOW found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        courseDOW = CourseDOW()
        courseDOW.setId(i)
        courseDOW.setCourseId(result[i]['courseid'])
        courseDOW.setDOW(result[i]['DOW'])
        coursesDOW.append(courseDOW)


def GetSessions(firebase: firebase) ->None:
    """

       Get Sessions from the database and insert them
       into the global collection.
       
       :param firebase: a firebase connection

    """
    global sessions
    obj_key_list = []
    result = firebase.get('/session', None)
    if result is None:
        print('no sessions found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        session = Session()
        session.setId(i)
        session.setCourseId(result[i]['courseid'])
        session.setDOW(result[i]['DOW'])
        session.setSessionNumber(result[i]['session_number'])
        session.setSessionDate(result[i]['session_date'])
        session.setSessionTimeStart(result[i]['session_time_start'])
        session.setSessionTimeEnd(result[i]['session_time_end'])
        sessions.append(session)


def GetKnowledgeAreas(firebase: firebase) ->None:
    """
        Get  KnowledgeAreas from the database and
        insert them into the global collection.

        :param firebase: a firebase connection

    """
    global knowledgeAreas
    obj_key_list = []
    result = firebase.get('/knowledgearea', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        knowledgeArea = KnowledgeArea()
        knowledgeArea.setText(result[i]['content'])
        knowledgeArea.setId(i)
        knowledgeAreas.append(knowledgeArea)


def GetCourses(firebase: firebase) ->None:
    """

        Get Courses from the database and insert them into the
        global collection.

        :param firebase: A firebase connection

    """
    global courses
    obj_key_list = []
    result = firebase.get('/course', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        course = Course()
        course.setId(i)
        course.setKnowledgeAreaId(result[i]['knowledgeareaid'])
        course.setCatalogId(result[i]['catalogid'])
        course.setTitle(result[i]['name'])
        course.setDescription(result[i]['description'])
        course.setInstructor(result[i]['instructor'])
        course.setFee(result[i]['fee'])
        courses.append(course)


def GetLearningObjectives(firebase: firebase) ->None:
    """

        Get LearningObjectives from the database and insert them into the
        global collection.

        Parameters
        ----------
        :param firebase: A firebase connection

    """
    global learningObjectives
    obj_key_list = []
    result = firebase.get('/learningobjective', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        lo = LearningObjective()
        lo.setId(i)
        lo.setCourseId(result[i]['courseid'])
        lo.setText(result[i]['content'])
        learningObjectives.append(lo)


def writeCourseesWorksheet(wb: xlsxwriter.Workbook) ->None:
    """

        Write a separate worksheet for all courses.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    global coursesWsRow
    ws = wb.add_worksheet()
    ws.name = 'Courses'
    row = coursesWsRow
    col = 0
    ws.write(row, col, 'LearningObjective')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'CourseTitle - As Revised')
    col += 1
    ws.write(row, col, 'CourseDescription - As Extracted')
    col += 1
    ws.write(row, col, 'CourseDescription - As Revised')
    col += 1
    ws.write(row, col, 'Instructor - As Extracted')
    col += 1
    ws.write(row, col, 'Instructor - As Revised')
    col += 1
    ws.write(row, col, 'Fee - As Extracted')
    col += 1
    ws.write(row, col, 'Fee - As Revised')
    coursesWsRow += 1
    col = 0
    for knowledgeArea in knowledgeAreas:
        writeCourseRows(ws, knowledgeArea)


def writeDOWWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global coursesDOW
    global courseDOWWsRow
    ws = wb.add_worksheet()
    ws.name = 'DOW'
    row = courseDOWWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    courseDOWWsRow += 1
    col = 0
    writeDOWRows(ws)


<mask token>


def writeSessionsWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global sessions
    global sessionsWsRow
    ws = wb.add_worksheet()
    ws.name = 'Sessions'
    row = sessionsWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    ws.write(row, col, 'Session_number')
    col += 1
    ws.write(row, col, 'Session_date - As Extracted')
    col += 1
    ws.write(row, col, 'Session_date - As Revised')
    col += 1
    ws.write(row, col, 'Session_start_time')
    col += 1
    ws.write(row, col, 'Session_start_time - As Revised')
    col += 1
    ws.write(row, col, 'Session_end_time')
    col += 1
    ws.write(row, col, 'Session_end_time - As Revised')
    sessionsWsRow += 1
    col = 0
    writeSessionRows(ws)


def writeSessionRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the sessions worksheet for all sessions.

        :param ws: Excel Worksheet Object
        :param session: Session

    """
    global sessions
    global sessionsWsRow
    col = 0
    for session in sessions:
        sessionId = session.getId()
        ws.write(sessionsWsRow, col, sessionId)
        col += 1
        courseId = session.getCourseId()
        ws.write(sessionsWsRow, col, courseId)
        col += 1
        ws.write(sessionsWsRow, col, session.getDOW())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionNumber())
        col += 1
        ws.write(sessionsWsRow, col, session.getSessionDate())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeStart())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
        sessionsWsRow += 1
        col = 0


<mask token>


def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) ->None:
    """

        Iterate through the global collection of KnowledgeAreas,
        create a worksheet for each, and call method to populate
        the worksheet with courses and learning objectives.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    for knowledgeArea in knowledgeAreas:
        ws = wb.add_worksheet()
        ws.name = knowledgeArea.getText()[0:31]
        writeWorksheet(ws, knowledgeArea)


def writeWorksheet(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea
    ) ->None:
    """

        Iterate through the global collection of KnowledgeAreas
        and write them to a worksheet in an Excel workbook.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea Object

    """
    global courses
    global learningObjectives
    row = 0
    col = 0
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjectiveId')
    col += 1
    ws.write(row, col, 'learningObjective - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjective - As Revised')
    row += 1
    col = 0
    knowledgeAreaId = knowledgeArea.getId()
    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            courseId = course.getId()
            if course.getDescription() == '':
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                continue
            else:
                for learningObjective in learningObjectives:
                    if str(learningObjective.getCourseId()) == str(courseId):
                        ws.write(row, col, courseId)
                        col += 1
                        ws.write(row, col, course.getTitle())
                        col += 1
                        ws.write(row, col, learningObjective.getId())
                        col += 1
                        ws.write(row, col, learningObjective.getText())
                        row += 1
                        col = 0
                continue


<mask token>

<mask token>


def GetCoursesDOW(firebase: firebase) ->None:
    """
    

    Get DOW from database and insert them into global collection
    
    :param firebase: a firebase connection

    """
    global coursesDOW
    obj_key_list = []
    result = firebase.get('/courses_dow', None)
    if result is None:
        print('no courses DOW found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        courseDOW = CourseDOW()
        courseDOW.setId(i)
        courseDOW.setCourseId(result[i]['courseid'])
        courseDOW.setDOW(result[i]['DOW'])
        coursesDOW.append(courseDOW)


def GetSessions(firebase: firebase) ->None:
    """

       Get Sessions from the database and insert them
       into the global collection.
       
       :param firebase: a firebase connection

    """
    global sessions
    obj_key_list = []
    result = firebase.get('/session', None)
    if result is None:
        print('no sessions found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        session = Session()
        session.setId(i)
        session.setCourseId(result[i]['courseid'])
        session.setDOW(result[i]['DOW'])
        session.setSessionNumber(result[i]['session_number'])
        session.setSessionDate(result[i]['session_date'])
        session.setSessionTimeStart(result[i]['session_time_start'])
        session.setSessionTimeEnd(result[i]['session_time_end'])
        sessions.append(session)


def GetKnowledgeAreas(firebase: firebase) ->None:
    """
        Get  KnowledgeAreas from the database and
        insert them into the global collection.

        :param firebase: a firebase connection

    """
    global knowledgeAreas
    obj_key_list = []
    result = firebase.get('/knowledgearea', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        knowledgeArea = KnowledgeArea()
        knowledgeArea.setText(result[i]['content'])
        knowledgeArea.setId(i)
        knowledgeAreas.append(knowledgeArea)


def GetCourses(firebase: firebase) ->None:
    """

        Get Courses from the database and insert them into the
        global collection.

        :param firebase: A firebase connection

    """
    global courses
    obj_key_list = []
    result = firebase.get('/course', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        course = Course()
        course.setId(i)
        course.setKnowledgeAreaId(result[i]['knowledgeareaid'])
        course.setCatalogId(result[i]['catalogid'])
        course.setTitle(result[i]['name'])
        course.setDescription(result[i]['description'])
        course.setInstructor(result[i]['instructor'])
        course.setFee(result[i]['fee'])
        courses.append(course)


def GetLearningObjectives(firebase: firebase) ->None:
    """

        Get LearningObjectives from the database and insert them into the
        global collection.

        Parameters
        ----------
        :param firebase: A firebase connection

    """
    global learningObjectives
    obj_key_list = []
    result = firebase.get('/learningobjective', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        lo = LearningObjective()
        lo.setId(i)
        lo.setCourseId(result[i]['courseid'])
        lo.setText(result[i]['content'])
        learningObjectives.append(lo)


def writeCourseesWorksheet(wb: xlsxwriter.Workbook) ->None:
    """

        Write a separate worksheet for all courses.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    global coursesWsRow
    ws = wb.add_worksheet()
    ws.name = 'Courses'
    row = coursesWsRow
    col = 0
    ws.write(row, col, 'LearningObjective')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'CourseTitle - As Revised')
    col += 1
    ws.write(row, col, 'CourseDescription - As Extracted')
    col += 1
    ws.write(row, col, 'CourseDescription - As Revised')
    col += 1
    ws.write(row, col, 'Instructor - As Extracted')
    col += 1
    ws.write(row, col, 'Instructor - As Revised')
    col += 1
    ws.write(row, col, 'Fee - As Extracted')
    col += 1
    ws.write(row, col, 'Fee - As Revised')
    coursesWsRow += 1
    col = 0
    for knowledgeArea in knowledgeAreas:
        writeCourseRows(ws, knowledgeArea)


def writeDOWWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global coursesDOW
    global courseDOWWsRow
    ws = wb.add_worksheet()
    ws.name = 'DOW'
    row = courseDOWWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    courseDOWWsRow += 1
    col = 0
    writeDOWRows(ws)


def writeDOWRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the DOW worksheet for all courseDOW.

        :param ws: Excel Worksheet Object

    """
    global coursesDOW
    global courseDOWWsRow
    col = 0
    for dow in coursesDOW:
        courseDOWId = dow.getId()
        ws.write(courseDOWWsRow, col, courseDOWId)
        col += 1
        courseId = dow.getCourseId()
        ws.write(courseDOWWsRow, col, courseId)
        col += 1
        ws.write(courseDOWWsRow, col, dow.getDOW())
        courseDOWWsRow += 1
        col = 0


def writeSessionsWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global sessions
    global sessionsWsRow
    ws = wb.add_worksheet()
    ws.name = 'Sessions'
    row = sessionsWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    ws.write(row, col, 'Session_number')
    col += 1
    ws.write(row, col, 'Session_date - As Extracted')
    col += 1
    ws.write(row, col, 'Session_date - As Revised')
    col += 1
    ws.write(row, col, 'Session_start_time')
    col += 1
    ws.write(row, col, 'Session_start_time - As Revised')
    col += 1
    ws.write(row, col, 'Session_end_time')
    col += 1
    ws.write(row, col, 'Session_end_time - As Revised')
    sessionsWsRow += 1
    col = 0
    writeSessionRows(ws)


def writeSessionRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the sessions worksheet for all sessions.

        :param ws: Excel Worksheet Object
        :param session: Session

    """
    global sessions
    global sessionsWsRow
    col = 0
    for session in sessions:
        sessionId = session.getId()
        ws.write(sessionsWsRow, col, sessionId)
        col += 1
        courseId = session.getCourseId()
        ws.write(sessionsWsRow, col, courseId)
        col += 1
        ws.write(sessionsWsRow, col, session.getDOW())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionNumber())
        col += 1
        ws.write(sessionsWsRow, col, session.getSessionDate())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeStart())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
        sessionsWsRow += 1
        col = 0


<mask token>


def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) ->None:
    """

        Iterate through the global collection of KnowledgeAreas,
        create a worksheet for each, and call method to populate
        the worksheet with courses and learning objectives.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    for knowledgeArea in knowledgeAreas:
        ws = wb.add_worksheet()
        ws.name = knowledgeArea.getText()[0:31]
        writeWorksheet(ws, knowledgeArea)


def writeWorksheet(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea
    ) ->None:
    """

        Iterate through the global collection of KnowledgeAreas
        and write them to a worksheet in an Excel workbook.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea Object

    """
    global courses
    global learningObjectives
    row = 0
    col = 0
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjectiveId')
    col += 1
    ws.write(row, col, 'learningObjective - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjective - As Revised')
    row += 1
    col = 0
    knowledgeAreaId = knowledgeArea.getId()
    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            courseId = course.getId()
            if course.getDescription() == '':
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                continue
            else:
                for learningObjective in learningObjectives:
                    if str(learningObjective.getCourseId()) == str(courseId):
                        ws.write(row, col, courseId)
                        col += 1
                        ws.write(row, col, course.getTitle())
                        col += 1
                        ws.write(row, col, learningObjective.getId())
                        col += 1
                        ws.write(row, col, learningObjective.getText())
                        row += 1
                        col = 0
                continue


<mask token>

<mask token>


def GetCoursesDOW(firebase: firebase) ->None:
    """
    

    Get DOW from database and insert them into global collection
    
    :param firebase: a firebase connection

    """
    global coursesDOW
    obj_key_list = []
    result = firebase.get('/courses_dow', None)
    if result is None:
        print('no courses DOW found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        courseDOW = CourseDOW()
        courseDOW.setId(i)
        courseDOW.setCourseId(result[i]['courseid'])
        courseDOW.setDOW(result[i]['DOW'])
        coursesDOW.append(courseDOW)


def GetSessions(firebase: firebase) ->None:
    """

       Get Sessions from the database and insert them
       into the global collection.
       
       :param firebase: a firebase connection

    """
    global sessions
    obj_key_list = []
    result = firebase.get('/session', None)
    if result is None:
        print('no sessions found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        session = Session()
        session.setId(i)
        session.setCourseId(result[i]['courseid'])
        session.setDOW(result[i]['DOW'])
        session.setSessionNumber(result[i]['session_number'])
        session.setSessionDate(result[i]['session_date'])
        session.setSessionTimeStart(result[i]['session_time_start'])
        session.setSessionTimeEnd(result[i]['session_time_end'])
        sessions.append(session)


def GetKnowledgeAreas(firebase: firebase) ->None:
    """
        Get  KnowledgeAreas from the database and
        insert them into the global collection.

        :param firebase: a firebase connection

    """
    global knowledgeAreas
    obj_key_list = []
    result = firebase.get('/knowledgearea', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        knowledgeArea = KnowledgeArea()
        knowledgeArea.setText(result[i]['content'])
        knowledgeArea.setId(i)
        knowledgeAreas.append(knowledgeArea)


def GetCourses(firebase: firebase) ->None:
    """

        Get Courses from the database and insert them into the
        global collection.

        :param firebase: A firebase connection

    """
    global courses
    obj_key_list = []
    result = firebase.get('/course', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        course = Course()
        course.setId(i)
        course.setKnowledgeAreaId(result[i]['knowledgeareaid'])
        course.setCatalogId(result[i]['catalogid'])
        course.setTitle(result[i]['name'])
        course.setDescription(result[i]['description'])
        course.setInstructor(result[i]['instructor'])
        course.setFee(result[i]['fee'])
        courses.append(course)


def GetLearningObjectives(firebase: firebase) ->None:
    """

        Get LearningObjectives from the database and insert them into the
        global collection.

        Parameters
        ----------
        :param firebase: A firebase connection

    """
    global learningObjectives
    obj_key_list = []
    result = firebase.get('/learningobjective', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        lo = LearningObjective()
        lo.setId(i)
        lo.setCourseId(result[i]['courseid'])
        lo.setText(result[i]['content'])
        learningObjectives.append(lo)


def writeCourseesWorksheet(wb: xlsxwriter.Workbook) ->None:
    """

        Write a separate worksheet for all courses.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    global coursesWsRow
    ws = wb.add_worksheet()
    ws.name = 'Courses'
    row = coursesWsRow
    col = 0
    ws.write(row, col, 'LearningObjective')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'CourseTitle - As Revised')
    col += 1
    ws.write(row, col, 'CourseDescription - As Extracted')
    col += 1
    ws.write(row, col, 'CourseDescription - As Revised')
    col += 1
    ws.write(row, col, 'Instructor - As Extracted')
    col += 1
    ws.write(row, col, 'Instructor - As Revised')
    col += 1
    ws.write(row, col, 'Fee - As Extracted')
    col += 1
    ws.write(row, col, 'Fee - As Revised')
    coursesWsRow += 1
    col = 0
    for knowledgeArea in knowledgeAreas:
        writeCourseRows(ws, knowledgeArea)


def writeDOWWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global coursesDOW
    global courseDOWWsRow
    ws = wb.add_worksheet()
    ws.name = 'DOW'
    row = courseDOWWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    courseDOWWsRow += 1
    col = 0
    writeDOWRows(ws)


def writeDOWRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the DOW worksheet for all courseDOW.

        :param ws: Excel Worksheet Object

    """
    global coursesDOW
    global courseDOWWsRow
    col = 0
    for dow in coursesDOW:
        courseDOWId = dow.getId()
        ws.write(courseDOWWsRow, col, courseDOWId)
        col += 1
        courseId = dow.getCourseId()
        ws.write(courseDOWWsRow, col, courseId)
        col += 1
        ws.write(courseDOWWsRow, col, dow.getDOW())
        courseDOWWsRow += 1
        col = 0


def writeSessionsWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global sessions
    global sessionsWsRow
    ws = wb.add_worksheet()
    ws.name = 'Sessions'
    row = sessionsWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    ws.write(row, col, 'Session_number')
    col += 1
    ws.write(row, col, 'Session_date - As Extracted')
    col += 1
    ws.write(row, col, 'Session_date - As Revised')
    col += 1
    ws.write(row, col, 'Session_start_time')
    col += 1
    ws.write(row, col, 'Session_start_time - As Revised')
    col += 1
    ws.write(row, col, 'Session_end_time')
    col += 1
    ws.write(row, col, 'Session_end_time - As Revised')
    sessionsWsRow += 1
    col = 0
    writeSessionRows(ws)


def writeSessionRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the sessions worksheet for all sessions.

        :param ws: Excel Worksheet Object
        :param session: Session

    """
    global sessions
    global sessionsWsRow
    col = 0
    for session in sessions:
        sessionId = session.getId()
        ws.write(sessionsWsRow, col, sessionId)
        col += 1
        courseId = session.getCourseId()
        ws.write(sessionsWsRow, col, courseId)
        col += 1
        ws.write(sessionsWsRow, col, session.getDOW())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionNumber())
        col += 1
        ws.write(sessionsWsRow, col, session.getSessionDate())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeStart())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
        sessionsWsRow += 1
        col = 0


def writeCourseRows(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea
    ) ->None:
    """

        Write the rows in the courses worksheet for all courses under
        the knowledgearea.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea

    """
    global courses
    global coursesWsRow
    col = 0
    knowledgeAreaId = knowledgeArea.getId()
    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            ws.write(coursesWsRow, col, knowledgeArea.getText())
            col += 1
            courseId = course.getId()
            ws.write(coursesWsRow, col, courseId)
            col += 1
            ws.write(coursesWsRow, col, course.getTitle())
            col += 2
            ws.write(coursesWsRow, col, course.getDescription())
            col += 2
            ws.write(coursesWsRow, col, course.getInstructor())
            col += 2
            ws.write(coursesWsRow, col, course.getFee())
            coursesWsRow += 1
            col = 0


def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) ->None:
    """

        Iterate through the global collection of KnowledgeAreas,
        create a worksheet for each, and call method to populate
        the worksheet with courses and learning objectives.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    for knowledgeArea in knowledgeAreas:
        ws = wb.add_worksheet()
        ws.name = knowledgeArea.getText()[0:31]
        writeWorksheet(ws, knowledgeArea)


def writeWorksheet(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea
    ) ->None:
    """

        Iterate through the global collection of KnowledgeAreas
        and write them to a worksheet in an Excel workbook.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea Object

    """
    global courses
    global learningObjectives
    row = 0
    col = 0
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjectiveId')
    col += 1
    ws.write(row, col, 'learningObjective - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjective - As Revised')
    row += 1
    col = 0
    knowledgeAreaId = knowledgeArea.getId()
    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            courseId = course.getId()
            if course.getDescription() == '':
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                continue
            else:
                for learningObjective in learningObjectives:
                    if str(learningObjective.getCourseId()) == str(courseId):
                        ws.write(row, col, courseId)
                        col += 1
                        ws.write(row, col, course.getTitle())
                        col += 1
                        ws.write(row, col, learningObjective.getId())
                        col += 1
                        ws.write(row, col, learningObjective.getText())
                        row += 1
                        col = 0
                continue


<mask token>

<mask token>
__author__ = 'Steven M. Satterfield'
import xlsxwriter
from firebase import firebase
from knowledgearea import KnowledgeArea
from learningobjective import LearningObjective
from course import Course
from session import Session
from coursedow import CourseDOW
firebase = firebase.FirebaseApplication(
    'https://scholacity-org.firebaseio.com/')
knowledgeAreas = []
courses = []
learningObjectives = []
sessions = []
coursesDOW = []
workbook = xlsxwriter.Workbook(
    '../outputs/Course_Catalog_Review_Spring_2021.xlsx')
coursesWsRow = 0
sessionsWsRow = 0
courseDOWWsRow = 0


def GetCoursesDOW(firebase: firebase) ->None:
    """
    

    Get DOW from database and insert them into global collection
    
    :param firebase: a firebase connection

    """
    global coursesDOW
    obj_key_list = []
    result = firebase.get('/courses_dow', None)
    if result is None:
        print('no courses DOW found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        courseDOW = CourseDOW()
        courseDOW.setId(i)
        courseDOW.setCourseId(result[i]['courseid'])
        courseDOW.setDOW(result[i]['DOW'])
        coursesDOW.append(courseDOW)


def GetSessions(firebase: firebase) ->None:
    """

       Get Sessions from the database and insert them
       into the global collection.
       
       :param firebase: a firebase connection

    """
    global sessions
    obj_key_list = []
    result = firebase.get('/session', None)
    if result is None:
        print('no sessions found')
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        session = Session()
        session.setId(i)
        session.setCourseId(result[i]['courseid'])
        session.setDOW(result[i]['DOW'])
        session.setSessionNumber(result[i]['session_number'])
        session.setSessionDate(result[i]['session_date'])
        session.setSessionTimeStart(result[i]['session_time_start'])
        session.setSessionTimeEnd(result[i]['session_time_end'])
        sessions.append(session)


def GetKnowledgeAreas(firebase: firebase) ->None:
    """
        Get  KnowledgeAreas from the database and
        insert them into the global collection.

        :param firebase: a firebase connection

    """
    global knowledgeAreas
    obj_key_list = []
    result = firebase.get('/knowledgearea', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        knowledgeArea = KnowledgeArea()
        knowledgeArea.setText(result[i]['content'])
        knowledgeArea.setId(i)
        knowledgeAreas.append(knowledgeArea)


def GetCourses(firebase: firebase) ->None:
    """

        Get Courses from the database and insert them into the
        global collection.

        :param firebase: A firebase connection

    """
    global courses
    obj_key_list = []
    result = firebase.get('/course', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        course = Course()
        course.setId(i)
        course.setKnowledgeAreaId(result[i]['knowledgeareaid'])
        course.setCatalogId(result[i]['catalogid'])
        course.setTitle(result[i]['name'])
        course.setDescription(result[i]['description'])
        course.setInstructor(result[i]['instructor'])
        course.setFee(result[i]['fee'])
        courses.append(course)


def GetLearningObjectives(firebase: firebase) ->None:
    """

        Get LearningObjectives from the database and insert them into the
        global collection.

        Parameters
        ----------
        :param firebase: A firebase connection

    """
    global learningObjectives
    obj_key_list = []
    result = firebase.get('/learningobjective', None)
    if result is None:
        return
    for i in result.keys():
        obj_key_list.append(i)
    for i in obj_key_list:
        lo = LearningObjective()
        lo.setId(i)
        lo.setCourseId(result[i]['courseid'])
        lo.setText(result[i]['content'])
        learningObjectives.append(lo)


def writeCourseesWorksheet(wb: xlsxwriter.Workbook) ->None:
    """

        Write a separate worksheet for all courses.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    global coursesWsRow
    ws = wb.add_worksheet()
    ws.name = 'Courses'
    row = coursesWsRow
    col = 0
    ws.write(row, col, 'LearningObjective')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'CourseTitle - As Revised')
    col += 1
    ws.write(row, col, 'CourseDescription - As Extracted')
    col += 1
    ws.write(row, col, 'CourseDescription - As Revised')
    col += 1
    ws.write(row, col, 'Instructor - As Extracted')
    col += 1
    ws.write(row, col, 'Instructor - As Revised')
    col += 1
    ws.write(row, col, 'Fee - As Extracted')
    col += 1
    ws.write(row, col, 'Fee - As Revised')
    coursesWsRow += 1
    col = 0
    for knowledgeArea in knowledgeAreas:
        writeCourseRows(ws, knowledgeArea)


def writeDOWWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global coursesDOW
    global courseDOWWsRow
    ws = wb.add_worksheet()
    ws.name = 'DOW'
    row = courseDOWWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    courseDOWWsRow += 1
    col = 0
    writeDOWRows(ws)


def writeDOWRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the DOW worksheet for all courseDOW.

        :param ws: Excel Worksheet Object

    """
    global coursesDOW
    global courseDOWWsRow
    col = 0
    for dow in coursesDOW:
        courseDOWId = dow.getId()
        ws.write(courseDOWWsRow, col, courseDOWId)
        col += 1
        courseId = dow.getCourseId()
        ws.write(courseDOWWsRow, col, courseId)
        col += 1
        ws.write(courseDOWWsRow, col, dow.getDOW())
        courseDOWWsRow += 1
        col = 0


def writeSessionsWorksheet(wb: xlsxwriter.Workbook) ->None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """
    global sessions
    global sessionsWsRow
    ws = wb.add_worksheet()
    ws.name = 'Sessions'
    row = sessionsWsRow
    col = 0
    ws.write(row, col, 'SessionId')
    col += 1
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'DOW - As Extracted')
    col += 1
    ws.write(row, col, 'DOW - As Revised')
    col += 1
    ws.write(row, col, 'Session_number')
    col += 1
    ws.write(row, col, 'Session_date - As Extracted')
    col += 1
    ws.write(row, col, 'Session_date - As Revised')
    col += 1
    ws.write(row, col, 'Session_start_time')
    col += 1
    ws.write(row, col, 'Session_start_time - As Revised')
    col += 1
    ws.write(row, col, 'Session_end_time')
    col += 1
    ws.write(row, col, 'Session_end_time - As Revised')
    sessionsWsRow += 1
    col = 0
    writeSessionRows(ws)


def writeSessionRows(ws: xlsxwriter.worksheet) ->None:
    """

        Write the rows in the sessions worksheet for all sessions.

        :param ws: Excel Worksheet Object
        :param session: Session

    """
    global sessions
    global sessionsWsRow
    col = 0
    for session in sessions:
        sessionId = session.getId()
        ws.write(sessionsWsRow, col, sessionId)
        col += 1
        courseId = session.getCourseId()
        ws.write(sessionsWsRow, col, courseId)
        col += 1
        ws.write(sessionsWsRow, col, session.getDOW())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionNumber())
        col += 1
        ws.write(sessionsWsRow, col, session.getSessionDate())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeStart())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
        sessionsWsRow += 1
        col = 0


def writeCourseRows(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea
    ) ->None:
    """

        Write the rows in the courses worksheet for all courses under
        the knowledgearea.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea

    """
    global courses
    global coursesWsRow
    col = 0
    knowledgeAreaId = knowledgeArea.getId()
    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            ws.write(coursesWsRow, col, knowledgeArea.getText())
            col += 1
            courseId = course.getId()
            ws.write(coursesWsRow, col, courseId)
            col += 1
            ws.write(coursesWsRow, col, course.getTitle())
            col += 2
            ws.write(coursesWsRow, col, course.getDescription())
            col += 2
            ws.write(coursesWsRow, col, course.getInstructor())
            col += 2
            ws.write(coursesWsRow, col, course.getFee())
            coursesWsRow += 1
            col = 0


def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) ->None:
    """

        Iterate through the global collection of KnowledgeAreas,
        create a worksheet for each, and call method to populate
        the worksheet with courses and learning objectives.

        :param wb: Excel Workbook Object

    """
    global knowledgeAreas
    for knowledgeArea in knowledgeAreas:
        ws = wb.add_worksheet()
        ws.name = knowledgeArea.getText()[0:31]
        writeWorksheet(ws, knowledgeArea)


def writeWorksheet(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea
    ) ->None:
    """

        Iterate through the global collection of KnowledgeAreas
        and write them to a worksheet in an Excel workbook.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea Object

    """
    global courses
    global learningObjectives
    row = 0
    col = 0
    ws.write(row, col, 'CourseId')
    col += 1
    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjectiveId')
    col += 1
    ws.write(row, col, 'learningObjective - As Extracted')
    col += 1
    ws.write(row, col, 'learningObjective - As Revised')
    row += 1
    col = 0
    knowledgeAreaId = knowledgeArea.getId()
    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            courseId = course.getId()
            if course.getDescription() == '':
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                continue
            else:
                for learningObjective in learningObjectives:
                    if str(learningObjective.getCourseId()) == str(courseId):
                        ws.write(row, col, courseId)
                        col += 1
                        ws.write(row, col, course.getTitle())
                        col += 1
                        ws.write(row, col, learningObjective.getId())
                        col += 1
                        ws.write(row, col, learningObjective.getText())
                        row += 1
                        col = 0
                continue


if __name__ == '__main__':
    GetKnowledgeAreas(firebase)
    GetCourses(firebase)
    GetLearningObjectives(firebase)
    GetSessions(firebase)
    GetCoursesDOW(firebase)
    writeCourseesWorksheet(workbook)
    writeKnowledgeAreaWorksheets(workbook)
    writeSessionsWorksheet(workbook)
    writeDOWWorksheet(workbook)
    workbook.close()

"""
    send_extracted_catalog_to_csv.py
    --------------------------------

    This module contains a set of functions that read the extracted
    catalog data from the database and export it to Excel format along
    with the row Ids so that the data can be refined and the database
    can be updated.

"""

__author__ = "Steven M. Satterfield"

import xlsxwriter
from firebase import firebase

from knowledgearea import KnowledgeArea
from learningobjective import LearningObjective
from course import Course
from session import Session
from coursedow import CourseDOW


firebase = firebase.FirebaseApplication('https://scholacity-org.firebaseio.com/')

knowledgeAreas = []
courses = []
learningObjectives = []
sessions = []
coursesDOW = []

workbook = xlsxwriter.Workbook('../outputs/Course_Catalog_Review_Spring_2021.xlsx')
coursesWsRow = 0
sessionsWsRow = 0
courseDOWWsRow = 0


def GetCoursesDOW(firebase: firebase) -> None:
    """
    

    Get DOW from database and insert them into global collection
    
    :param firebase: a firebase connection

    """
    
    global coursesDOW
    obj_key_list = []
    
    result = firebase.get('/courses_dow', None)
    
    if result is None:
        print("no courses DOW found")
        return
    
    for i in result.keys():
        obj_key_list.append(i)
        
    for i in obj_key_list:
        courseDOW = CourseDOW()
        courseDOW.setId(i)
        courseDOW.setCourseId(result[i]['courseid'])
        courseDOW.setDOW(result[i]['DOW'])
        coursesDOW.append(courseDOW)




def GetSessions(firebase: firebase) -> None:
    """

       Get Sessions from the database and insert them
       into the global collection.
       
       :param firebase: a firebase connection

    """

    global sessions
    obj_key_list = []
    
    result = firebase.get('/session', None)
    
    if result is None:
        print("no sessions found")
        return
    
    for i in result.keys():
        obj_key_list.append(i)
        
    for i in obj_key_list:
        session = Session()
        session.setId(i)
        session.setCourseId(result[i]['courseid'])
        session.setDOW(result[i]['DOW'])
        session.setSessionNumber(result[i]['session_number'])
        session.setSessionDate(result[i]['session_date'])
        session.setSessionTimeStart(result[i]['session_time_start'])
        session.setSessionTimeEnd(result[i]['session_time_end'])
        sessions.append(session)
        
        

def GetKnowledgeAreas(firebase: firebase) -> None:
    """
        Get  KnowledgeAreas from the database and
        insert them into the global collection.

        :param firebase: a firebase connection

    """

    global knowledgeAreas

    obj_key_list = []

    result = firebase.get('/knowledgearea', None)

    if result is None:
        return

    for i in result.keys():
        obj_key_list.append(i)

    for i in obj_key_list:
        knowledgeArea = KnowledgeArea()
        knowledgeArea.setText(result[i]['content'])
        knowledgeArea.setId(i)
        knowledgeAreas.append(knowledgeArea)


def GetCourses(firebase: firebase) -> None:
    """

        Get Courses from the database and insert them into the
        global collection.

        :param firebase: A firebase connection

    """

    global courses
    obj_key_list = []

    result = firebase.get('/course', None)

    if result is None:
        return

    for i in result.keys():
        obj_key_list.append(i)

    for i in obj_key_list:
        course = Course()
        course.setId(i)
        course.setKnowledgeAreaId(result[i]['knowledgeareaid'])
        course.setCatalogId(result[i]['catalogid'])
        course.setTitle(result[i]['name'])
        course.setDescription(result[i]['description'])
        course.setInstructor(result[i]['instructor'])
        course.setFee(result[i]['fee'])
        courses.append(course)


def GetLearningObjectives(firebase: firebase) -> None:
    """

        Get LearningObjectives from the database and insert them into the
        global collection.

        Parameters
        ----------
        :param firebase: A firebase connection

    """

    global learningObjectives
    obj_key_list = []

    result = firebase.get('/learningobjective', None)

    if result is None:
        return

    for i in result.keys():
        obj_key_list.append(i)

    for i in obj_key_list:
        lo = LearningObjective()
        lo.setId(i)
        lo.setCourseId(result[i]['courseid'])
        lo.setText(result[i]['content'])
        learningObjectives.append(lo)


def writeCourseesWorksheet(wb: xlsxwriter.Workbook) -> None:
    """

        Write a separate worksheet for all courses.

        :param wb: Excel Workbook Object

    """

    global knowledgeAreas
    global coursesWsRow


    ws = wb.add_worksheet()
    ws.name = "Courses"

    row = coursesWsRow
    col = 0

    ws.write(row, col, 'LearningObjective')
    col += 1

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1

    ws.write(row, col, 'CourseTitle - As Revised')
    col += 1

    ws.write(row, col, 'CourseDescription - As Extracted')
    col += 1

    ws.write(row, col, 'CourseDescription - As Revised')
    col += 1

    ws.write(row, col, 'Instructor - As Extracted')
    col += 1

    ws.write(row, col, 'Instructor - As Revised')
    col += 1

    ws.write(row, col, 'Fee - As Extracted')
    col += 1

    ws.write(row, col, 'Fee - As Revised')

    coursesWsRow += 1
    col = 0

    for knowledgeArea in knowledgeAreas:
        writeCourseRows(ws, knowledgeArea)


def writeDOWWorksheet(wb: xlsxwriter.Workbook) -> None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """

    global coursesDOW
    global courseDOWWsRow
    
    ws = wb.add_worksheet()
    ws.name = "DOW"
    
    row = courseDOWWsRow
    col = 0
    
    ws.write(row, col, 'SessionId')
    col += 1

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'DOW - As Extracted')
    col += 1

    ws.write(row, col, 'DOW - As Revised')
    col += 1

    courseDOWWsRow += 1
    col = 0

    writeDOWRows(ws)



def writeDOWRows(ws: xlsxwriter.worksheet) -> None:
    """

        Write the rows in the DOW worksheet for all courseDOW.

        :param ws: Excel Worksheet Object

    """

    global coursesDOW
    global courseDOWWsRow

    col = 0

    for dow in coursesDOW:
        courseDOWId = dow.getId()
        ws.write(courseDOWWsRow, col, courseDOWId)
        col += 1

        courseId = dow.getCourseId()
        ws.write(courseDOWWsRow, col, courseId)
        col += 1
        
        ws.write(courseDOWWsRow, col, dow.getDOW())
        
        courseDOWWsRow += 1
        col = 0


def writeSessionsWorksheet(wb: xlsxwriter.Workbook) -> None:
    """
    

        Write a separate worksheet for all sessions.

        :param wb: Excel Workbook Object

    """

    global sessions
    global sessionsWsRow
    
    ws = wb.add_worksheet()
    ws.name = "Sessions"
    
    row = sessionsWsRow
    col = 0
    
    ws.write(row, col, 'SessionId')
    col += 1

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'DOW - As Extracted')
    col += 1

    ws.write(row, col, 'DOW - As Revised')
    col += 1

    ws.write(row, col, 'Session_number')
    col += 1

    ws.write(row, col, 'Session_date - As Extracted')
    col += 1

    ws.write(row, col, 'Session_date - As Revised')
    col += 1

    ws.write(row, col, 'Session_start_time')
    col += 1

    ws.write(row, col, 'Session_start_time - As Revised')
    col += 1

    ws.write(row, col, 'Session_end_time')
    col += 1

    ws.write(row, col, 'Session_end_time - As Revised')
    
    sessionsWsRow += 1
    col = 0

    writeSessionRows(ws)
    

def writeSessionRows(ws: xlsxwriter.worksheet) -> None:
    """

        Write the rows in the sessions worksheet for all sessions.

        :param ws: Excel Worksheet Object
        :param session: Session

    """

    global sessions
    global sessionsWsRow

    col = 0

    for session in sessions:
        sessionId = session.getId()
        ws.write(sessionsWsRow, col, sessionId)
        col += 1

        courseId = session.getCourseId()
        ws.write(sessionsWsRow, col, courseId)
        col += 1
        
        ws.write(sessionsWsRow, col, session.getDOW())
        col += 2
        
        ws.write(sessionsWsRow, col, session.getSessionNumber())
        col += 1
        ws.write(sessionsWsRow, col, session.getSessionDate())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeStart())
        col += 2
        ws.write(sessionsWsRow, col, session.getSessionTimeEnd())
        
        
        sessionsWsRow += 1
        col = 0


def writeCourseRows(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea) -> None:
    """

        Write the rows in the courses worksheet for all courses under
        the knowledgearea.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea

    """

    global courses
    global coursesWsRow

    col = 0
    knowledgeAreaId = knowledgeArea.getId()

    for course in courses:
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            ws.write(coursesWsRow, col, knowledgeArea.getText())
            col += 1

            courseId = course.getId()
            ws.write(coursesWsRow, col, courseId)
            col += 1
            ws.write(coursesWsRow, col, course.getTitle())
            col += 2
            ws.write(coursesWsRow, col, course.getDescription())
            col += 2
            ws.write(coursesWsRow, col, course.getInstructor())
            col += 2
            ws.write(coursesWsRow, col, course.getFee())
            
            coursesWsRow += 1
            col = 0


def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) -> None:
    """

        Iterate through the global collection of KnowledgeAreas,
        create a worksheet for each, and call method to populate
        the worksheet with courses and learning objectives.

        :param wb: Excel Workbook Object

    """

    global knowledgeAreas

    for knowledgeArea in knowledgeAreas:
        ws = wb.add_worksheet()
        ws.name = knowledgeArea.getText()[0:31]
        writeWorksheet(ws, knowledgeArea)


def writeWorksheet(ws: xlsxwriter.worksheet, knowledgeArea: KnowledgeArea) -> None:
    """

        Iterate through the global collection of KnowledgeAreas
        and write them to a worksheet in an Excel workbook.

        :param ws: Excel Worksheet Object
        :param knowledgeArea: KnowledgeArea Object

    """

    global courses
    global learningObjectives

    row = 0
    col = 0

    ws.write(row, col, 'CourseId')
    col += 1

    ws.write(row, col, 'CourseTitle - As Extracted')
    col += 1

    ws.write(row, col, 'learningObjectiveId')
    col += 1

    ws.write(row, col, 'learningObjective - As Extracted')
    col += 1

    ws.write(row, col, 'learningObjective - As Revised')

    row += 1
    col = 0

    knowledgeAreaId = knowledgeArea.getId()

    for course in courses:
        #print("Course {}, {} ".format(course.getTitle(), course.getKnowledgeAreaId()))
        if str(course.getKnowledgeAreaId()) == str(knowledgeAreaId):
            courseId = course.getId()
            if course.getDescription() == "":
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                # write another row for the course to allow for 2 learning outcomes
                ws.write(row, col, courseId)
                col += 1
                ws.write(row, col, course.getTitle())
                row += 1
                col = 0
                continue
            else:
                for learningObjective in learningObjectives:
                    #print("LearningObjective: {}, {} ".format(learningObjective.getText(), learningObjective.getCourseId()))

                    if str(learningObjective.getCourseId()) == str(courseId):
                        ws.write(row, col, courseId)
                        col += 1
                        ws.write(row, col, course.getTitle())
                        col += 1
                        ws.write(row, col, learningObjective.getId())
                        col += 1
                        ws.write(row, col, learningObjective.getText())
                        row += 1
                        col = 0
                continue


if __name__ == "__main__":
    GetKnowledgeAreas(firebase)
    GetCourses(firebase)
    GetLearningObjectives(firebase)
    GetSessions(firebase)
    GetCoursesDOW(firebase)

    writeCourseesWorksheet(workbook)
    writeKnowledgeAreaWorksheets(workbook)
    writeSessionsWorksheet(workbook)
    writeDOWWorksheet(workbook)
    workbook.close()
