import psycopg2
def insertPostgres(cur,values,tabel):
    sql = 'INSERT INTO ' + tabel + ' VALUES (' + values + ')'
    cur.execute(sql)
def getMostPurchasedProducts(cur,buids):
    product_list = []
    counter_list = []
    buids= str(buids).strip('[]')
    buids = buids.replace('\"', '')
    sql='SELECT orders FROM orders WHERE buid IN ('+buids+')'
    cur.execute(sql)
    row = cur.fetchone()
    a=0
    while row is not None:
        a+=1
        if row[0] not in product_list:
            product_list.append(row[0])
            counter_list.append(1)
        else:
            counter_list[product_list.index(row[0])] += 1
        row = cur.fetchone()
    print('im here')
    x = 0
    topProd=[]
    while x < 5:

        most_sold_id = product_list[counter_list.index(max(counter_list))]
        most_sold_count = max(counter_list)
        topProd.append(most_sold_id)
        counter_list.remove(most_sold_count)
        product_list.remove(most_sold_id)
        x += 1
    return topProd

def getBuidInThisTimeInterval(cur):
    start_datum = '2018-01-01'
    end_datum = '2018-01-09'
    datum = start_datum
    dagenineenmaand = 32  # In code wordt 31 gebruikt
    maandenineenjaar = 13  # In code wordt 12 gebruikt
    dag = 1
    poproducten = []
    buids = []
    a=1
    while dag != 0:
        print(datum)

        sql = 'SELECT buids FROM sessions WHERE datum LIKE \'' + datum + '\''
        cur.execute(sql)

        row = cur.fetchone()
        datum = datum.split('-')
        if (row is None and (datum[2] == '29' or datum[2] == '30' or datum[2] == '31')):
            skip = 1
        else:
            skip = 0

        while row is not None:
            buids.append('\'' + row[0] + '\'')
            row = cur.fetchone()

        datum[2] = int(datum[2]) + 1
        if datum[2] == dagenineenmaand:
            datum[1] = int(datum[1]) + 1
            datum[2] = 1
        if datum[1] == maandenineenjaar:
            datum[0] = int(datum[0]) + 1
            datum[1] = 1
        if datum[2] in range(0, 10):
            datum[2] = str(0) + str(datum[2])
        if datum[1] in range(0, 9):
            datum[1] = str(0) + str(datum[1])
        datum = str(datum[0]) + '-' + str(datum[1]) + '-' + str(datum[2])
        if datum == end_datum:
            break
        if dag % 7 == 0 and skip == 0:
            poproducten.append(getMostPurchasedProducts(cur, buids))
            print(a)
            a=a+1
        elif skip == 1:
            continue

        dag = dag + 1
    print(poproducten)
    for nummer_van_een_week in range(len(poproducten)):
        for product in range(len(poproducten[nummer_van_een_week])):
            values = []
            values.append(nummer_van_een_week+1)
            values.append(poproducten[nummer_van_een_week][product])
            values = str(values).strip('[]')
            values = values.replace('\"', '\'')
            insertPostgres(cur,values,'topperweek')
    conn.commit()

def getTopPerWeek(cur,week):
    sql='SELECT product FROM topperweek WHERE week IN ('+str(week)+')'
    cur.execute(sql)
    row = cur.fetchone()
    topProd=[]
    while row is not None:
        topProd.append(row[0])
        row = cur.fetchone()
    return(topProd)

conn = psycopg2.connect("dbname=AAIproject user=postgres password=123456")
cur = conn.cursor()
#getMostPurchasedProducts(cur)
getBuidInThisTimeInterval(cur)
cur.close()
conn.close()