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 = "VIMS_reconc"
    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,"wms_kuk_qty")
    sheet1.write(0,10,"wms_pot_qty")
    sheet1.write(0,11,"wms_ktx_qty")
    sheet1.write(0,12,"vims_kuk_qty")
    sheet1.write(0,13,"vims_pot_qty")
    sheet1.set_column(14,14, None, cell_formatdate)
    sheet1.write(0,14,"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, wms_kuk_qty, wms_pot_qty, wms_ktx_qty, vims_kuk_qty, vims_pot_qty, date_created"
        dataQuery +=" FROM e2reconc"
        if (filter != None and filter != ""):
            dataQuery = dataQuery +" WHERE "+filter
        if (filter[:6] == "SELECT"):
            dataQuery = filter
#        dataQuery = dataQuery + " ORDER BY transaction_history.part_number, transaction_history.date_created"
        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"
    defaults = default_setting.defaultSettings()
    getData(defaults,reportpath,filter)
    recipients = vimsmail.getRecipients(defaults,"reconc") 
    vimsmail.createMail(reportpath, sender, recipients,"")
    
#   print(defaults)