import csv
import codecs
import urllib.request
from itertools import islice
from operator import itemgetter
from decimal import *
import time
from datetime import datetime
import re
import pyodbc

###################################################################
"""
Script écrit par Fahd FETTAHI pour ELECO CAPITAL

Janvier 2018

"""
###################################################################

site_code = [
'SolarGIS_min15_1_Alzonne_1_',
'SolarGIS_min15_2_Alzonne_2_',
'SolarGIS_min15_3_Bar_Alain_',
'SolarGIS_min15_4_Bar_Fabrice_',
'SolarGIS_min15_5_Bossu_1_',
'SolarGIS_min15_6_Bossu_2_',
'SolarGIS_min15_7_Bossu_3_',
'SolarGIS_min15_8_Bossu_4_',
'SolarGIS_min15_9_Bossu_5_',
'SolarGIS_min15_10_Bossu_6_',
'SolarGIS_min15_11_Bourret_',
'SolarGIS_min15_12_Bousquet_',
'SolarGIS_min15_13_Brosse_',
'SolarGIS_min15_14_Brunel_',
'SolarGIS_min15_15_Buathier_1_',
'SolarGIS_min15_16_Buathier_2_',
'SolarGIS_min15_17_Bulteau_1_',
'SolarGIS_min15_18_Bulteau_2_',
'SolarGIS_min15_19_Cave_Canet_1_',
'SolarGIS_min15_20_Cave_Canet_2_',
'SolarGIS_min15_21_Cave_Canet_3_',
'SolarGIS_min15_22_Cave_Canet_4_',
'SolarGIS_min15_23_Cave_Canet_5_',
'SolarGIS_min15_24_Cave_Canet_6_',
'SolarGIS_min15_25_Chabas_1_',
'SolarGIS_min15_26_Chabas_2_',
'SolarGIS_min15_27_Champion_',
'SolarGIS_min15_28_Chassagnoux_',
'SolarGIS_min15_29_Chenou_1_',
'SolarGIS_min15_30_Chenou_2_',
'SolarGIS_min15_31_Chocian_',
'SolarGIS_min15_32_CLAIR_Jean-Marie_and_FOURNEL_Laurence_',
'SolarGIS_min15_33_Croix_Rouge_',
'SolarGIS_min15_34_CROUZILLES_1_',
'SolarGIS_min15_35_CROUZILLES_2_',
'SolarGIS_min15_36_Cruscades_1_',
'SolarGIS_min15_37_Cruscades_2_',
'SolarGIS_min15_38_Dezillie_',
'SolarGIS_min15_39_DUC_Amandine_',
'SolarGIS_min15_40_Dumas_',
'SolarGIS_min15_41_EARL_du_pont_',
'SolarGIS_min15_42_EXAPAC_',
'SolarGIS_min15_43_Fenaison_',
'SolarGIS_min15_44_GAEC_DE_REDONDET_',
'SolarGIS_min15_45_GAEC_La_Tendrerie_',
'SolarGIS_min15_46_GAYET_',
'SolarGIS_min15_47_Gougne_Alain_',
'SolarGIS_min15_48_Gougne_Delphine_',
'SolarGIS_min15_49_GRAVIER_',
'SolarGIS_min15_50_Hedde_',
'SolarGIS_min15_51_Hemar_',
'SolarGIS_min15_52_KEISER_',
'SolarGIS_min15_53_La_florentaise_1_',
'SolarGIS_min15_54_La_florentaise_2_',
'SolarGIS_min15_55_LABEILLE_Rgine_',
'SolarGIS_min15_56_LAFON_',
'SolarGIS_min15_57_Lapalud_',
'SolarGIS_min15_58_Ma_Nouvelle_Mutuelle_',
'SolarGIS_min15_91_Ma_Nouvelle_Mutuelle_',
'SolarGIS_min15_92_Ma_Nouvelle_Mutuelle_',
'SolarGIS_min15_59_Meallares_',
'SolarGIS_min15_60_Mr_Arsac_1_',
'SolarGIS_min15_61_Mr_Arsac_2_',
'SolarGIS_min15_62_Navy_Service_',
'SolarGIS_min15_63_OTECH_',
'SolarGIS_min15_64_PEYRE_',
'SolarGIS_min15_65_Pinguet_1_',
'SolarGIS_min15_66_Pinguet_2_',
'SolarGIS_min15_67_Relais_Vert_1_',
'SolarGIS_min15_68_Relais_Vert_2_',
'SolarGIS_min15_69_Renard_',
'SolarGIS_min15_70_Reynal_',
'SolarGIS_min15_71_Roux_Claude_1_',
'SolarGIS_min15_72_Roux_Claude_2_',
'SolarGIS_min15_73_Roux_Jeremy_',
'SolarGIS_min15_74_SCI_Tournecamp_',
'SolarGIS_min15_75_Sud_Cereales_',
'SolarGIS_min15_76_Sunsteel_',
'SolarGIS_min15_77_T1271_',
'SolarGIS_min15_78_T1274_',
'SolarGIS_min15_79_T3203-Courties_1_',
'SolarGIS_min15_80_T3203-Courties_2_',
'SolarGIS_min15_81_T3227-Terraube_',
'SolarGIS_min15_82_T4602_1_',
'SolarGIS_min15_83_T4604_2_',
'SolarGIS_min15_84_T8107-Montredon_',
'SolarGIS_min15_85_T8208_',
'SolarGIS_min15_86_Vergne_',
'SolarGIS_min15_87_Vialle_',
'SolarGIS_min15_88_Vidal_',
'SolarGIS_min15_89_Vitrolles_1_',
'SolarGIS_min15_90_Vitrolles_2_']

coeff_pond = [
(1,0.479289940828402),
(2,0.497041420118343),
(3,0.290526315789474),
(4,0.697263157894737),
(5,0.998792270531401),
(6,0.998652173913044),
(7,0.0596296296296296),
(8,0.0340740740740741),
(9,0.1725),
(10,0.1725),
(11,0.274722222222222),
(12,0.284305555555556),
(13,1),
(14,0.997944444444444),
(15,1.0009756097561),
(16,0.780165289256198),
(17,0.219834710743802),
(18,0.95),
(19,0.0496252927400468),
(20,0.171296296296296),
(21,0.134259259259259),
(22,0.101851851851852),
(23,0.240740740740741),
(24,0.203703703703704),
(25,0.148148148148148),
(26,0.443916083916084),
(27,0.556503496503497),
(28,0.997846153846154),
(29,1),
(30,0.5),
(31,0.508474576271186),
(32,0.997933884297521),
(33,1),
(34,0.47289156626506),
(35,0.530963855421687),
(36,0.71875),
(37,0.277916666666667),
(38,0.397849462365591),
(39,0.602150537634409),
(40,0.999881305637982),
(41,1.00363636363636),
(42,0.999279869067103),
(43,1.00031746031746),
(44,1),
(45,1.00216216216216),
(46,1.0013023255814),
(47,0.997846153846154),
(48,0.999493670886076),
(49,1.00106557377049),
(50,1.00222222222222),
(51,0.997974683544304),
(52,0.779710144927536),
(53,0.220289855072464),
(54,0.99912),
(55,0.998684210526316),
(56,0.996),
(57,1),
(58,0.740677966101695),
(91,0.144237288135593),
(92,0.0935593220338983),
(59,0.521348314606742),
(60,0.478651685393258),
(61,0.997846153846154),
(62,0.998857142857143),
(63,1),
(64,0.9994),
(65,0.571428571428571),
(66,0.428571428571429),
(67,0.99774647887324),
(68,1.00135135135135),
(69,1),
(70,0.997795275590551),
(71,0.999493670886076),
(72,0.5544),
(73,0.4455),
(74,0.999894736842105),
(75,1.00279069767442),
(76,1),
(77,0.5),
(78,1),
(79,1.00150485436893),
(80,0.509090909090909),
(81,0.496969696969697),
(82,0.998780487804878),
(83,1.0032),
(84,1.00304347826087),
(85,1.00236486486486),
(86,0.998957264957265),
(87,1.00040816326531),
(88,1.00139737991266),
(89,1.00363636363636),
(90,1),
]

correction_name = [
("Alzonne","ALZONNE"),
("Mr_Arsac","ARSAC"),
("Bar_Alain","BARGERIE ALAIN"),
("Bar_Fabrice","BARGERIE FABRICE"),
("Bossu","BOSSU"),
("Bourret","BOURRET"),
("Brosse","BROSSE"),
("Brunel","BRUNEL"),
("Buathier","BUATHIER"),
("Bulteau","BULTEAU"),
("Cave_Canet","CAVE CANET D'AUDE"),
("Chabas","CHABAS"),
("Champion","CHAMPION"),
("Chassagnoux","CHASSAGNOUX"),
("Chenou","CHENOU"),
("Chocian","CHOCIAN"),
("CLAIR_Jean-Marie_and_FOURNEL_Laurence","CLAIR"),
("Croix_Rouge","CROIX ROUGE"),
("CROUZILLES","CROUZILLES"),
("Cruscades","CRUSCADES"),
("Dezillie","DEZILLIE"),
("DUC_Amandine","DUC"),
("Dumas","DUMAS"),
("EXAPAC","EXAPAQ"),
("Fenaison","FENAISONS"),
("GAYET","GAYET"),
("Gougne_Alain","GOUGNE ALAIN"),
("Gougne_Delphine","GOUGNE DELPHINE"),
("GRAVIER","GRAVIER"),
("Hedde","HEDDE"),
("Hemar","HEMAR"),
("KEISER","KEISER"),
("La_florentaise","LA FLORENTAISE"),
("GAEC_La_Tendrerie","LA TENDRERIE"),
("LABEILLE_Rgine","LABEILLE"),
("LAFON","LAFON"),
("Lapalud","LAPALUD"),
("Ma_Nouvelle_Mutuelle","MA NOUVELLE MUTUELLE"),
("Vitrolles","MABEMA"),
("Meallares","MEALLARES"),
("Navy_Service","NAVY SERVICE"),
("OTECH","OTECH"),
("PEYRE","PEYRE"),
("Pinguet","PINGUET"),
("EARL_du_pont","PONT DE RHODES BOISSIERE"),
("GAEC_DE_REDONDET","REDONDET ISSALY (CROIX DE BOURLAC)"),
("Relais_Vert","RELAIS VERT"),
("Renard","RENARD"),
("Reynal","REYNAL"),
("Roux_Claude","ROUX CLAUDE"),
("Roux_Jeremy","ROUX JEREMY"),
("Bousquet","SERRE MONTOLIEU BOUSQUET (LES BRETOUS)"),
("Sud_Cereales","SUD CEREALES"),
("Sunsteel","SUNSTEEL"),
("T1271","T1271 (SAINT MARTIN DE LENNE)"),
("T1274","T1274 (REQUISTA)"),
("T3203-Courties","T3203 (COURTIES)"),
("T3227-Terraube","T3227 (TERRAUBE)"),
("T4602","T4602 (MAYRINHAC)"),
("T4604","T4604 (LAVERGNE)"),
("T8107-Montredon","T8107 (MONTREDON)"),
("T8208","T8208 (BEAUMONT DE LOMAGNE)"),
("SCI_Tournecamp","TOUR DE CAMP"),
("Vergne","VERGNE"),
("Vialle","VIALLE"),
("Vidal","VIDAL")
]

coeff_auto = True

data_line = 50

url_elios ='ftp://ELIOS:7K96Egg@ftp.geomodel.eu/CLIMDATA/'

## url_eleco ='ftp://ELECO:msZ6QyH@ftp.geomodel.eu/CLIMDATA/'

GTI = 4
TEMP = 8
site_line = 4

recuperation_mensuelle = False
recuperation_jour = True

correction_jour = False

datemensuelle = 201708
datejournaliere = 20171204

## CONNECTION

SERVERNAME = 'elecosql.database.windows.net'
DATABASE_NAME = 'REC_ActiviteEleco'
USER = 'eleco_sql_user'
PASSWORD = 'dh6çù&az'

REQUEST_COEFFS = 'SELECT [ID_Solargis],[Coeff] FROM [dbo].[Coeffs] ORDER BY Site'

###################################################################

url1 = url_elios  # à implémenter en radio

start = time.time()


conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+SERVERNAME+';DATABASE='+DATABASE_NAME+';UID='+USER+';PWD='+PASSWORD)
cursor = conn.cursor()

if (coeff_auto == True):
	coeff_pond = []
	cursor.execute(REQUEST_COEFFS)
	for row in cursor.fetchall():
		coeff_pond.append(row)



if (recuperation_mensuelle == True): # recuperation mensuelle
	recuperation_jour = False
	url2 = str(datemensuelle)+'01_'+ str(datemensuelle)+'31.csv'
	print("Début de l'importation mensuelle :")

elif(recuperation_jour == True):
	recuperation_mensuelle = False
	url2 = str(datejournaliere)+'.csv'
	print("Début de l'importation journalière :")

tableau_data = []
irr_ponderee = 0
coeff = 0
new_name =''

for x,n in enumerate(site_code):

	url = url1 + n + url2

	irr_site = 0
	rows = 0
	temp = 0

	id_pan_solargis = n.replace('SolarGIS_min15_','').split('_',1)[0]


	name_site = n.replace('SolarGIS_min15_','').split('_',1)[1]
	name_site = re.sub('_[0-9]+', '', name_site)
	name_site = re.sub('_$', '', name_site)

	if(x+1 < len(site_code)):
		name_site_next = site_code[x+1].replace('SolarGIS_min15_','').split('_',1)[1]
		name_site_next = re.sub('_[0-9]+_', '', name_site_next)
		name_site_next = re.sub('_$', '', name_site_next)

	for j in coeff_pond:
		if(str(id_pan_solargis) == str(j[0])):
			coeff = j[1]

	# Sécurité dans le cas ou les coefficients ne sont pas bons
	if (coeff == 0):
		print('Erreur : Les coefficients sont erronés')

	ftpstream = urllib.request.urlopen(url)
	csvfile = csv.reader(codecs.iterdecode(ftpstream, 'utf-8'), delimiter='\t', dialect='excel')

	for i,line in enumerate(csvfile):

		if(i >=data_line):
			rows=rows+1
			irr_str = line[0].split(';')[GTI]
			irr_site = float(irr_site + int(irr_str) / 4000)

			temp_str = line[0].split(';')[TEMP]
			temp = float(temp + float(temp_str))

	if(name_site == name_site_next):
		irr_ponderee = irr_ponderee + irr_site*float(coeff)

	else:
		irr_ponderee =  irr_site


	temp = temp / rows

	if((name_site != name_site_next) | (x+1 == len(site_code))):

		for k in correction_name:
			if(str(name_site) == str(k[0])):
				new_name = k[1]

		if(recuperation_jour == True):
			date_sql = datetime.strptime(str(datejournaliere), '%Y%m%d').strftime('%Y-%m-%d')


		elif(recuperation_mensuelle == True):
			date_sql = datetime.strptime(str(datemensuelle)+'01', '%Y%m%d').strftime('%Y-%m-%d')


		tableau_data.append([date_sql,new_name,irr_ponderee])
		print(date_sql,new_name,irr_ponderee,temp,sep='                ')

		irr_ponderee= 0


cursor.executemany("INSERT INTO [dbo].[IrradiationExecutedTEST] ([DateMensuelle],[Site], [IR]) VALUES (?,?,?)",tableau_data)
cursor.commit()


end = time.time()

print ('Importation effectuée en :')
print (str(end - start)+ ' secondes')