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

import default_setting
import vimsmail

def getData(defaults,reportpath,filter):
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
    tablename = "reconc_VIMS"
    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")
#   style1 = xlwt.XFStyle()
#   style1.num_format_str = 'DD-MM-YYYY HH:MM:SS'

    for col in range(20):
        sheet1.set_column(col, col, 20)
    sheet1.write(0,0,"part_number")
    sheet1.write(0,1,"part_description")
    sheet1.write(0,2,"vendor_reference_code")
    sheet1.write(0,3,"vendor_name")
    sheet1.write(0,4,"location_code")
    sheet1.write(0,5,"std_cost")
    sheet1.write(0,6,"baan_qty_available")
    sheet1.write(0,7,"baan_qty_unavailable")
    sheet1.write(0,8,"baan_hold_qty")
    sheet1.write(0,9,"vims_kuk_qty")
    sheet1.write(0,10,"vims_pot_qty")
    sheet1.set_column(11,11, None, cell_formatdate)
    sheet1.write(0,11,"date_created")   
    try:
        dataQuery = "SELECT part_number, part_description, vendor_reference_code, vendor_name, location_code, std_cost, baan_qty_available, baan_qty_unavailable, baan_hold_qty, vims_kuk_qty, vims_pot_qty, date_created"
        dataQuery +=" FROM reconc"
        if (filter != None and filter != ""):
            dataQuery = dataQuery +" WHERE "+filter
        if (filter[:6] == "SELECT"):
            dataQuery = filter
        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 = "f:/kukfiles/reconc/"
#    sender = "vimsreports@vanteceurope.com"
    sender = "vimsreport.gb@logisteed.com"
#    sender = "vimsreport.gb@logisteed.com"
    defaults = default_setting.defaultSettings()
    getData(defaults,reportpath,filter)
    recipients = vimsmail.getRecipients(defaults,"reconcvims") 
    vimsmail.createMail(reportpath, sender, recipients,"")

#    try:
#        os.remove(reportpath+"/reconc_VIMS.xlsx")
#        print(f"reconc_VIMS has been deleted.")
#    except OSError as e:
#        print(f"Error: {e.strerror}")
    
#   print(defaults)