#!/c:/Python39/python.exe

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

def table(tablename, filterStr, reportpath, dbhost, dbuser, dbpwd, dbase):
	
    cnx = mysql.connector.connect(user=dbuser, password=dbpwd,host=dbhost,database=dbase)

    book = xlsxwriter.Workbook(reportpath+"/"+tablename+".xlsx")
    bold = book.add_format({'bold': True})
    cell_formatdate = book.add_format()
    cell_formatdate.set_num_format("yyyy/mm/dd hh:mm")
    #	cell_formatqty = book.add_format({'num_format'})
    cell_formatqty = book.add_format()
    cell_formatqty.set_num_format('0')
    cell_formatgeneral = book.add_format()
    cell_formatgeneral.set_num_format('general')
    sheet1 = book.add_worksheet("Sheet 1")

    testStr = str(filterStr)
    #	print("TEST1:"+testStr)
    if (testStr[0:6] == "SELECT"):
        cursor = cnx.cursor()
        dataQuery = "DROP VIEW IF EXISTS "+dbase+".view_"+tablename
        try:
            cursor.execute(dataQuery)
    #			connection.commit()
        except UnboundLocalError:
            return 'Drop Error'
    #		finally:
    #			cursor.close()
        dataQuery = "CREATE VIEW "+dbase+".view_"+tablename+" AS("+filterStr+")"		
        print("TEST2:"+dataQuery)
        try:
            cursor.execute(dataQuery)
        except UnboundLocalError:
            return "FAIL-View Error"
        finally:
            cursor.close()
        
    if (tablename == "transaction_history"):
    #		sheet1.set_row(0,0,None,bold)
        sheet1.set_column(0,0, None, cell_formatdate)
        sheet1.write(0,0,"date_created")
        sheet1.write(0,1,"part_number")
        sheet1.write(0,2,"serial_reference")
        sheet1.write(0,3,"short_code")
        sheet1.set_column(4,4, None, cell_formatqty)
        sheet1.write(0,4,"txn_qty")
        sheet1.write(0,5,"customer_reference")
        sheet1.write(0,6,"ran_or_order")
        sheet1.write(0,7,"document_reference")
        sheet1.write(0,8,"to_location")
        sheet1.write(0,9,"from_location")
        sheet1.write(0,10,"comment")
        sheet1.write(0,11,"to_pallet")
        sheet1.write(0,12,"created_by")
        sheet1.write(0,13,"conversion_factor")
    else:
        try:
            dataQuery = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='"+dbase+"' AND `TABLE_NAME`='"+tablename+"' ORDER BY ORDINAL_POSITION";
            cursor = cnx.cursor()
            cursor.execute(dataQuery)
            data = cursor.fetchall()
            r = 0
            c = 0
            for hrow in data:
                if (hrow[0] == "date_created"):
                    sheet1.set_column(0, c, None, cell_formatdate)
                elif (hrow[0] == "last_updated"):
                    sheet1.set_column(0, c, None, cell_formatdate)
                elif (hrow[0] == "date_time"):
                    sheet1.set_column(0, c, None, cell_formatdate)
                elif ("date" in hrow[0]):
                    sheet1.set_column(0, c, None, cell_formatdate)
                elif (hrow[0] == "txn_qty"):
                    sheet1.set_column(0, c, None, cell_formatqty)
                else:
                    sheet1.set_column(0, c, None, cell_formatgeneral)
    #				sheet1.set_column(0, c, None, bold)
                sheet1.write(r,c,hrow[0])
                c = c+1;
        except UnboundLocalError:
            raise 'MySql Error'		
        finally:
            try:
                cursor.close()
            except:
                pass

    try:
        if (tablename == "transaction_history"):		
            dataQuery = "SELECT date_created, part_number, serial_reference, short_code, txn_qty AS txn_qty, customer_reference, ran_or_order, associated_document_reference, to_location_code, from_location_code, comment, to_plt, created_by, conversion_factor FROM transaction_history"
        else:
            dataQuery = "SELECT * FROM "+tablename
        if (filterStr != None and filterStr != ""):
            if (filterStr[:5] == "WHERE"):
                dataQuery += " "+filterStr
            else:
                dataQuery = dataQuery +" WHERE "+filterStr
        cursor = cnx.cursor(dictionary=True)
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            try:
                c = 0
                for col in row:
                    try:
                        if ((col=='txn_qty') or (col=='inventory_qty') or (col=='allocated_qty')):
                            try:
                                sheet1.write(r,c,row[col]/row['conversion_factor'])
                            except:
                                sheet1.write(r,c,row[col])
                        else:
                            sheet1.write(r,c,row[col])
                    except Exception as err:
                        print("TEST1",row[col])
#                        pass
                        sheet1.write(r,c,'ERROR')
                    c=c+1
            except:
                print("TEST2")
                pass
            r = r+1
    #			if (r>65000):
    #				break
    except UnboundLocalError:
        return ('MySql Error',dataQuery)
        
    finally:
        try:
            cursor.close()
            cnx.close()
            book.close()
        except:
            pass
    
#    response = "OK  -"+dataQuery
    response = "/vimsreports/"+tablename+".xlsx"
    return (response)

def main(reportpath):
    dbhost = '172.18.25.11'
    dbuser = 'root'
    dbpwd = 'root'
    dbase = 'kuk'
#    tablename = 'company'
    tablename = 'view_rdsummary'
    filterstr = ''
    
    if (len(sys.argv) > 1):
        dbhost = sys.argv[1]
    if (len(sys.argv) > 2):
        dbuser = sys.argv[2]
    if (len(sys.argv) > 3):
        dbpwd = sys.argv[3]
    if (len(sys.argv) > 4):
        dbase = sys.argv[4]
    if (len(sys.argv) > 5):
        tablename = sys.argv[5]
    if (len(sys.argv) > 6):
        filterstr = sys.argv[6]
        
#    return ("TEST1 "+dbhost+" "+dbuser+" "+dbpwd+" "+dbase+" "+tablename+" "+filterstr)
#    reportpath = "c:/web/veldb/vimsreports"
    if not os.path.isdir(reportpath):
        os.makedirs(reportpath)
    sender = "vims@vanteceurope.com"

#    tablename='company'
#    filterstr=''
#    dbhost='172.18.25.4'
#    dbuser='root'
#    dbpwd='root'
#    dbase='cif'
    
#    return ("TEST1 "+dbhost+" "+dbuser+" "+dbpwd+" "+dbase+" "+tablename+" "+filterstr+" "+reportpath)
    
    response = table(tablename,filterstr,reportpath,dbhost, dbuser, dbpwd, dbase)
    return (response)
#    print ("Content-Type: text/plain;charset=utf-8")
#    print ()
#    print ("/vimsreports/"+tablename+".xlsx")
#    return ("/vimsreports/"+tablename+".xlsx")

if __name__ == '__main__':
    dirPath = os.path.dirname(os.path.realpath(__file__))
#    reportpath = "c:/web/veldb/vimsreports"
    reportpath = "c:/xampp/htdocs/vimsreports"
    response = main(reportpath)
    print (response)