import pymysql.cursors
import csv
from Variabelpro import *




cnx = pymysql.connect(user='root',
                      password='Elisabeth95',
                      host='127.0.0.1',
                      database='productdb')
cursor = cnx.cursor()




def sql_stuff(object):
    ## Opretter Company med navn og antal timer
    query = ('INSERT INTO companytime (Company_Name, time_capacity) VALUES (%s, %s);')
    cursor.execute(query, ("DogS Inc.", kaptimer))

    ##Opdatere productdb dataen fra begge produkter
    for i in VO[current_product1]:
        query1 = ('INSERT INTO productdb (name,unit_cost,time_produce_minutes) VALUES (%s, %s, %s);')
        time_produce_minutes1 = Timer[current_product1]
        cursor.execute(query1, (current_product1,i,time_produce_minutes1))

    for i in VO[current_product2]:
        query2 = ('INSERT INTO productdb (name,unit_cost,time_produce_minutes) VALUES (%s, %s, %s);')
        time_produce_minutes2 = Timer[current_product2]
        cursor.execute(query2, (current_product2,i,time_produce_minutes2))

    for i in Salgspriser[current_product1]:
        query3 = ('INSERT INTO productinfo (product_id,unit_value) VALUES (%s, %s);')
        product_id = 1
        cursor.execute(query3, (product_id, i))

    query4 = ('UPDATE productinfo SET amount=%s WHERE unit_value = %s;')
    cursor.execute(query4, (Afsætning[current_product1][0], Salgspriser[current_product1][0]))
    cursor.execute(query4, (Afsætning[current_product1][1], Salgspriser[current_product1][1]))
    cursor.execute(query4, (Afsætning[current_product1][2], Salgspriser[current_product1][2]))
    cursor.execute(query4, (Afsætning[current_product1][3], Salgspriser[current_product1][3]))
    cursor.execute(query4, (Afsætning[current_product1][4], Salgspriser[current_product1][4]))


    for i in Salgspriser[current_product2]:
        query5 = ('INSERT INTO productinfo (product_id,unit_value) VALUES (%s, %s);')
        product_id = 2
        cursor.execute(query5, (product_id, i))

    query6 = ('UPDATE productinfo SET amount=%s WHERE unit_value = %s;')
    cursor.execute(query6, (Afsætning[current_product2][0], Salgspriser[current_product2][0]))
    cursor.execute(query6, (Afsætning[current_product2][1], Salgspriser[current_product2][1]))
    cursor.execute(query6, (Afsætning[current_product2][2], Salgspriser[current_product2][2]))
    cursor.execute(query6, (Afsætning[current_product2][3], Salgspriser[current_product2][3]))




def reset_database(object):

    reset = ('SET FOREIGN_KEY_CHECKS = 0; truncate companytime; truncate product_data; truncate productdb; truncate productinfo; SET FOREIGN_KEY_CHECKS = 1;  commit')
    cursor.execute(reset)


reset_database(object)
sql_stuff(object)


cnx.commit()
cnx.close()