#https://github.com/mkleehammer/pyodbc/wiki/

import os
import re
import logging
import pyodbc
from openpyxl import Workbook
from openpyxl.styles import Font

class SQLserver:
	def __init__(self, params, autocommit=False, searchescape=None, timeout=None):
		params_str = r'DRIVER={ODBC Driver 11 for SQL Server};'
		params_str += r"SERVER=%s;" % (params["server"])
		params_str += r"DATABASE=%s;" % (params["database"])
		params_str += r"UID=%s;" % (params["uid"])
		params_str += r"PWD=%s;" % (params["pwd"])
		params_str += r"APP=%s;" % (params.get("app", None) or "TelegramBot")

		self._conn = pypyodbc.connect(params_str, autocommit=autocommit, searchescape=searchescape, timeout=timeout) #mantieni la connessione al db viva

	def commit(self):
		if not self._conn.autocommit: #ignora se autocommit attivo
			self._conn.commit()
			return True
		else:
			return False

	def execSQL(self, SQLcommand):
		cursor = self._connection.cursor()
		try:
			cursor.execute(SQLCommand)
		except pypyodbc.ProgrammingError:
			return 0

		return cursor

	def _toExcel(self, rows, file_path, sheet2=None):
		"""
		sheet2 = {
			"title": "nome folgio 2",
			"A1": "contenuto cella A1"
		}
		"""

		wb = Workbook()

		bold_font = Font(color='00FF0000', bold=True)
		
		ws = wb.active #usa il foglio corrente
		
		ws.title("Risultati") #cambia nome foglio
		ws.sheet_properties.tabColor = "5BF77D" #colore background foglio (verde)
		
		for row in rows:
			ws.append(row)

		for cell in ws["1:1"]:
    		cell.font = bold_font

    	if sheet2:
    		ws = wb.create_sheet(sheet2['title']) #foglio con la query
    		ws.sheet_properties.tabColor = "7BC1ED" #cambia colore background foglio (blu)
    		ws['A1'] = sheet2['A1'] #scrivi stringa col comando nella prima cella del foglio
		

		wb.save(file_path)

		return file_path

	def to_excel(self, SQLcommand, file_path):
		cursor = execSQL(SQLcommand)
		if cursor == 0:
			return cursor

		columns = [column[0] for column in cursor.description]
		
		rows = []
		rows.append(columns)
		for row in cursor.fetchall():
			rows.append(row)

		return self._toExcel(rows, file_path, {"title": "query", "A1": SQLcommand})
