import configparser
import mysql.connector
import os
import sys
import time
import datetime
# import xlwt
import xlsxwriter    

import default_setting
import ../vimsmail

def strip_quotes(v):
    if not v:
        return v
    v = v.strip()
    if (v.startswith('"') and v.endswith('"')) or (v.startswith("'") and v.endswith("'")):
        return v[1:-1]
    return v

def getData(cnx,reportpath,filter):
    tablename = "VIMS_daytarget"
    book = xlsxwriter.Workbook(reportpath+"/"+tablename+".xlsx")
    cell_formatdate = book.add_format()
    cell_formatdate.set_num_format("yyyy/mm/dd hh:mm")
    sheet1 = book.add_worksheet("Sheet 1")

    for col in range(20):
        sheet1.set_column(col, col, 20)
    sheet1.write(0,0,"Current Date")
    sheet1.write(0,1,"Model")
    sheet1.write(0,2,"Serial")
    sheet1.write(0,3,"Prod.Start Date")
    sheet1.write(0,4,"Prod.Start Time")
    sheet1.write(0,5,"Hard Short")
    sheet1.write(0,6,"Soft Short")
    sheet1.write(0,7,"AAA Outs.")
    try:
        dataQuery = """
                    SELECT DATE(now()) AS curr_date,MODL, SERL, 
                    PROD_START AS prod_date, PROD_STARTT AS prod_time,
                    HARD_SHORT, SOFT_SHORT,AAA_OUTS 
                    FROM daytgt_daytarget 
                    WHERE hard_short>0 OR soft_short>0 OR AAA_OUTS>0 
                    AND prod_start > current_date;            
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 0
            for col in row:
                sheet1.write(r,c,col)
                c=c+1
            r = r+1
#           if (r>65000):
#               break
    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
            cnx.close()
            book.close()
#           if not os.path.isdir(reportpath):
#               os.makedirs(reportpath)
#           book.save(reportpath+"\\"+tablename+".xlsx")
        except:
            pass
            
#   return defaults

if __name__ == '__main__':
# check for filter
    if (len(sys.argv)>1):
        filter = sys.argv[1]
    else:
        filter = ""
    reportpath = "e:/kukfiles/daily_target/"
    sender = "vimsreports@vanteceurope.com"

    CONFIG_PATH = ".env"
    config = configparser.ConfigParser()
# preserve case of keys if you want (by default lowercased)
    config.optionxform = str
    config.read(CONFIG_PATH)

# Access database section
    if "database" not in config:
        raise RuntimeError("Missing [database] section in config")

    db_cfg = config["database"]
    db_host = db_cfg.get("DB_HOST") or db_cfg.get("db_host")
    db_name = db_cfg.get("DB_DATABASE") or db_cfg.get("db_database")
    db_user = db_cfg.get("DB_USERNAME") or db_cfg.get("db_username")
    db_pass = db_cfg.get("DB_PASSWORD") or db_cfg.get("db_password")
    db_port = db_cfg.getint("DB_PORT", fallback=3306)
    db_host = strip_quotes(db_host)
    db_name = strip_quotes(db_name)
    db_user = strip_quotes(db_user)
    db_pass = strip_quotes(db_pass)

    cnx = mysql.connector.connect(
        host=db_host,
        database=db_name,
        user=db_user,
        password=db_pass,
        port=db_port
    )

    getData(defaults,reportpath,filter)
#    recipients = vimsmail.getRecipients(defaults,"dailytarget")
    recipients = "geoff.thorpe@vanteceurope.com"
    vimsmail.createMail(reportpath, sender, recipients,"")