#------------------------------------------------------------------------------------------------------#
#добавляет запиписи в выполненную работу
def add(name,work):
    import psycopg2
    from datetime import datetime

    #подключение к базе данных
    con = psycopg2.connect(
            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()


    cur.execute("SELECT executor_id FROM executor WHERE surname ='" + str(name) + "';")
    surname_id = cur.fetchall()

    cur.execute("SELECT work_id FROM work WHERE work_type = '" + str(work) + "';")
    work_id = cur.fetchall()

    now = datetime.now()

    cur.execute("INSERT INTO executor_work VALUES ("+ str(surname_id[0][0]) +","+ str(work_id[0][0]) + ",'" + now.strftime('%d-%m-%Y %H:%M') + "');")

    cur.close()
    con.close()
#------------------------------------------------------------------------------------------------------#

#------------------------------------------------------------------------------------------------------#
#Установка процента для каждого исполнителя
def set_precent(name, precent):
    import psycopg2

    con = psycopg2.connect(

            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()

    #установка процента для каждого исполнителя
    for i in range(1,8):
        cur.execute("UPDATE executor SET precent_id =" + str(precent) + "WHERE surname = '" + str(name) + "';")

    cur.close()
    con.close()

#------------------------------------------------------------------------------------------------------#

#------------------------------------------------------------------------------------------------------#
#возвращает массив с историей выполненных работ
def history():
    import psycopg2
    from datetime import datetime
    history_out = []

    con = psycopg2.connect(

            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()

    cur.execute('select executor_id,work_id,date_work FROM executor_work;')
    executor_id = cur.fetchall()

    for i in executor_id:
            cur.execute('SELECT surname,precent_id FROM executor WHERE executor_id = ' + str(i[0]) + ';')
            rec = cur.fetchall()
            cur.execute('SELECT work_type,price FROM work WHERE work_id = ' + str(i[1]) + ';')
            rec1 = cur.fetchall()
            cur.execute( 'SELECT precent FROM precent WHERE precent_id = ' + str(rec[0][1]) + ';' )
            rec2 = cur.fetchall()


            history_out.append(str(rec[0][0]) + ' ' + str(rec2[0][0]) + ' ' +  str(rec1[0][0]) + ' ' + str(rec1[0][1]) + ' ' +str(i[2])+ ' ' + str(rec1[0][1] + rec1[0][1]* rec2[0][0]/100))


    cur.close()
    con.close()
    return history_out

#------------------------------------------------------------------------------------------------------#

#------------------------------------------------------------------------------------------------------#
#Возвращает список исполнителей
def executors():
    import psycopg2
    surname = []
    con = psycopg2.connect(

            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()
    cur.execute("SELECT surname FROM executor;")
    rec = cur.fetchall()
    for i in rec:
        surname.append(str(i[0]))
    return surname

#------------------------------------------------------------------------------------------------------#

#------------------------------------------------------------------------------------------------------#
#Возвращает список работы
def works():
    import psycopg2
    surname = []
    con = psycopg2.connect(

            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()
    cur.execute("SELECT work_type FROM work;")
    rec = cur.fetchall()
    for i in rec:
        surname.append(str(i[0]))
    return surname

#------------------------------------------------------------------------------------------------------#

#------------------------------------------------------------------------------------------------------#
#Возвращает процент
def get_precent(name):
    import psycopg2
    con = psycopg2.connect(

            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()
    cur.execute("SELECT precent_id FROM executor WHERE surname = '" + str(name) + "';")
    rec = cur.fetchall()
    return rec[0][0]

#------------------------------------------------------------------------------------------------------#

#------------------------------------------------------------------------------------------------------#
#Возвращает стоимость работы
def work_cost(name):
    import psycopg2
    con = psycopg2.connect(

            database="remont",
            user="postgres",
            password="2611815")
    con.autocommit = True

    cur = con.cursor()
    cur.execute("SELECT price FROM work WHERE work_type = '" + str(name) + "';")
    rec = cur.fetchall()
    return rec[0][0]

#------------------------------------------------------------------------------------------------------#