'''
Created on 30 avr. 2014

@author: thibaut.feret
'''
import xlrd
import csv
import os
import fonc_banc
import pymysql


path="B:\\eclipse\Script_R_macrophyteCoursdEau\Bancarisation\Bancarisation2014\EtatDeLaBase\Bancarisation"
pathin="B:\\eclipse\\Script_R_macrophyteCoursdEau\\Bancarisation\\Bancarisation2014\\EtatDeLaBase\\Bancarisation\\abancariser"
pathout="B:\\eclipse\Script_R_macrophyteCoursdEau\Bancarisation\Bancarisation2014\EtatDeLaBase\Bancarisation\sortie"

os.chdir(path)

fileT=fonc_banc.listdirectory(pathin)
        
NbFile=len(fileT)
compil_name = ['cd_site','riviere','Station','nom_BE','nom_prel','Date','pc_facies_f1','pc_facies_f2','pc_facies_tot','fichier','Feuille']
liste_naiade_name=['cd_site','date_opecont','cd_taxon','nom_taxon','cd_sandre','pc_facies_f1','pc_facies_f2','cf']
csv_compil=open('sortie\compil.csv', 'w', newline='')
compil = csv.writer(csv_compil, delimiter=';', quoting=csv.QUOTE_MINIMAL)
compil.writerow(compil_name)

csv_liste_naiade=open('sortie\liste_naiade.csv', 'w', newline='')
liste_naiade = csv.writer(csv_liste_naiade, delimiter=';', quoting=csv.QUOTE_MINIMAL)
liste_naiade.writerow(liste_naiade_name)


N=0
RemovSheet=['station XX','xxx','notice','notice � lire','accueil','liste reference','R\xe9cap.','modele','liste codes r\xe9f','Graphes','liste codes r�f','R�cap.','Synth�se','station','Feuil1']
#Boucle sur les fichiers xls
for file in fileT:
    if file.endswith(".xls")==True:
        N=N+1
        print(N,"-",file)
        book = xlrd.open_workbook(file, encoding_override="utf-8")
        SheetName=book.sheet_names()
        lenRemo=len(RemovSheet)
        for i in range(0,lenRemo):
            if RemovSheet[i] in SheetName:
                SheetName.remove(RemovSheet[i])
            if i==lenRemo:
                break
        
        lenSheet=len(SheetName)
        for worksheet_name in SheetName:
            worksheet_name2=worksheet_name
            if worksheet_name2 not in RemovSheet:
                worksheet = book.sheet_by_name(worksheet_name)
#Lecture des cellules pour compil
# Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank               
                CellCE=fonc_banc.ReadAndTestXLS(book,worksheet,2,0,1,'No Data')
                CellSt=fonc_banc.ReadAndTestXLS(book,worksheet,2,2,1,'No Data')
                CellcdSta=fonc_banc.ReadAndTestXLS(book,worksheet,2,10,1,'No Data')
                CellDate=fonc_banc.ReadAndTestXLS(book,worksheet,3,0,3,'No Data')
                CellBE=fonc_banc.ReadAndTestXLS(book,worksheet,1,0,1,'No Data')
                Cellnomprel=fonc_banc.ReadAndTestXLS(book,worksheet,1,2,1,'No Data')
                CellFaciesF1=fonc_banc.ReadAndTestXLS(book,worksheet,6,1,2,0)
                CellFaciesF2=fonc_banc.ReadAndTestXLS(book,worksheet,6,2,2,0)
                CellFaciesFtot=CellFaciesF1+CellFaciesF2
                compil_stock= [CellcdSta,CellCE,CellSt,CellBE,Cellnomprel,CellDate,CellFaciesF1,CellFaciesF2,CellFaciesFtot,file,worksheet_name]
                compil.writerow(compil_stock)
#Lecture des cellules pour liste_naiade
                for k in range(0,59):
                    Cellcdtaxon=fonc_banc.ReadAndTestXLS(book,worksheet,22+k,0,1,'No Data')
                    Cellcdtaxon=Cellcdtaxon.replace('.','')
                    CellPcF1=fonc_banc.ReadAndTestXLS(book,worksheet,22+k,1,2,0)
                    CellPcF2=fonc_banc.ReadAndTestXLS(book,worksheet,22+k,2,2,0)
                    CellPcnomtaxon=fonc_banc.ReadAndTestXLS(book,worksheet,22+k,10,1,'No Data')
                    CellConfer=fonc_banc.ReadAndTestXLS(book,worksheet,22+k,25,1,"")
                    if Cellcdtaxon!="":
                        liste_naiade_stock= [CellcdSta,CellDate,Cellcdtaxon,CellPcnomtaxon,"",CellPcF1,CellPcF2,CellConfer]
                        liste_naiade.writerow(liste_naiade_stock)
            if worksheet_name==SheetName[lenSheet-1]:
                break


csv_compil.close()
csv_liste_naiade.close()

conn = pymysql.connect(host='195.221.113.106', user='feret', passwd='thibaut', db='pandore')
cur = conn.cursor()
test=cur.execute("SELECT * FROM compil WHERE support='naiade'")
cur.close()
conn.close()



print("fin du script")