import os
import MySQLdb

db = MySQLdb.connect(host='localhost',user='root',passwd='pass',db='papers_read')
cur = db.cursor()


folder_to_add = 'C:\\Users\\ermwebb\\Dropbox\\Reading\\Notes\\Need to Add\\'

for f in os.listdir(folder_to_add):
    if f == 'template.txt':
        pass    
    else:
        title = ''
        journal = ''
        year = -1
        citations = -1
        keywords = []
        authors = []
        year_read = -1
        month_read = -1
        day_read = -1
        hours_to_read = -1
        not_read = ''
        skimmed = ''
        reason_read = ''
        follow_up = ''
        notes = []
        
        for line in open(folder_to_add+f,'r'):
            if len(line)>4:
                if line[0:5]=='Title':
                    title = line.split(':')[1].strip()
                if line[0:5]=='Journ':
                    journal = line.split(':')[1].strip()
                if line[0:5]=='Year:':
                    year = int(line.split(':')[1].strip())
                if line[0:5]=='Citat':
                    citations = int(line.split(':')[1].strip())
                if line[0:5]=='Comma':
                    k = line.split(':')[1].strip()
                    k_s = k.split(',')
                    for key in k_s:
                        if len(key)>2:
                            keywords.append([key.strip(),'Paper'])
                if line[0:5]=='Last ':
                    author_last = line.split(':')[1].strip()
                if line[0:5]=='Given':
                    author_init = line.split(':')[1].strip()
                if line[0:5]=='Affil':
                    author_aff = line.split(':')[1].strip()
                    authors.append([author_last,author_init,author_aff])
                if line[0:5]=='Year ':
                    year_read = int(line.split(':')[1].strip())
                if line[0:5]=='Month':
                    month_read = int(line.split(':')[1].strip())
                if line[0:5]=='Day (':
                    day_read = int(line.split(':')[1].strip())
                if line[0:5]=='Hours':
                    hours_to_read = float(line.split(':')[1].strip())
                if line[0:5]=='Not R':
                    not_read = line.split(':')[1].strip()
                if line[0:5]=='Skimm':
                    skimmed = line.split(':')[1].strip()
                if line[0:5]=='Reaso':
                    reason_read = line.split(':')[1].strip()
                if line[0:5]=='Keywo':
                    k = line.split(':')[1].strip()
                    k_s = k.split(',')
                    for key in k_s:
                        keywords.append([key.strip(),'User'])
                if line[0:5]=='Follo':
                    follow_up = line.split(':')[1].strip()
                if line[0:5]=='Note1':
                    n = line.split(':')[1].strip()
                    if len(n)>2:
                        notes.append(n)
                if line[0:5]=='Note2':
                    n = line.split(':')[1].strip()
                    if len(n)>2:
                        notes.append(n)
                if line[0:5]=='Note3':
                    n = line.split(':')[1].strip()
                    if len(n)>2:
                        notes.append(n)
                if line[0:5]=='Note4':
                    n = line.split(':')[1].strip()
                    if len(n)>2:
                        notes.append(n)
                if line[0:5]=='Note5':
                    n = line.split(':')[1].strip()
                    if len(n)>2:
                        notes.append(n)
        
        sql = 'select count(*) from papers'
        cur.execute(sql)
        inc_papers=int(cur.fetchone()[0])+1
        
        cur.execute('''INSERT INTO papers(idPapers,Title,Journal,YearPublished,YearRead,MonthRead,DayRead,Citations,NotRead,Skimmed,FollowUp,ReasonRead,HoursToRead) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''',(inc_papers,title,journal,year,year_read,month_read,day_read,citations,not_read,skimmed,follow_up,reason_read,hours_to_read))
    
        sql = 'select count(*) from authors'
        cur.execute(sql)
        inc_auth=int(cur.fetchone()[0])
    
        for a in authors:
            inc_auth += 1
            cur.execute('''INSERT INTO authors(idauthors,idpaper,LastName,Given Name,InitialCollege) VALUES (%s,%s,%s,%s,%s)''',(inc_auth,inc_papers,a[0],a[1],a[2]))
        
        sql = 'select count(*) from keywords'
        cur.execute(sql)
        inc_key=int(cur.fetchone()[0])
        
        for k in keywords:
            inc_key += 1
            cur.execute('''INSERT INTO keywords(idKeywords,idPaper,Keyword,Source) VALUES (%s,%s,%s,%s)''',(inc_key,inc_papers,k[0],k[1]))
    
        sql = 'select count(*) from notes'
        cur.execute(sql)
        inc_notes=int(cur.fetchone()[0])
    
        for n in notes:
            inc_notes += 1
            cur.execute('''INSERT INTO notes(idNotes,idPaper,Note) VALUES (%s,%s,%s)''',(inc_notes,inc_papers,n))
            
            
        db.commit()
        print f