#!/c:/Python39/python.exe import mysql.connector import os import time import datetime import xlsxwriter import sys from pathlib import Path 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() 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) if (testStr[0:6] == "SELECT"): cursor = cnx.cursor() dataQuery = "DROP VIEW IF EXISTS " + dbase + ".view_" + tablename try: cursor.execute(dataQuery) except UnboundLocalError: return 'Drop Error' 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_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") elif (tablename == "inventory_master"): sheet1.write(0, 0, "part_number") sheet1.write(0, 1, "serial_reference") sheet1.write(0, 2, "ran_or_order") sheet1.write(0, 3, "conversion_factor") sheet1.set_column(4, 4, None, cell_formatqty) sheet1.write(0, 4, "inventory_qty") sheet1.set_column(5, 5, None, cell_formatqty) sheet1.write(0, 5, "allocated_qty") sheet1.write(0, 6, "status_code") sheet1.write(0, 7, "location_code") sheet1.write(0, 8, "pallet") sheet1.set_column(9, 9, None, cell_formatdate) sheet1.write(0, 9, "last_updated") sheet1.write(0, 10, "last_updated_by") sheet1.set_column(11, 11, None, cell_formatdate) sheet1.write(0, 11, "date_created") sheet1.write(0, 12, "created_by") elif (tablename == "user"): sheet1.write(0, 0, "Username") sheet1.write(0, 1, "First Name") sheet1.write(0, 2, "Last Name") sheet1.write(0, 3, "User Level") sheet1.write(0, 4, "Shift") sheet1.write(0, 5, "Created By") sheet1.set_column(6, 6, None, cell_formatdate) sheet1.write(0, 6, "Date Created") sheet1.write(0, 7, "Last Updated By") sheet1.set_column(8, 8, None, cell_formatdate) sheet1.write(0, 8, "Last Updated") 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.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" elif (tablename == "inventory_master"): dataQuery = "SELECT inventory_master.part_number, inventory_master.serial_reference, inventory_master.ran_or_order, inventory_master.conversion_factor, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, inventory_status.inventory_status_code, location.location_code,inventory_master.tag_reference AS pallet, inventory_master.last_updated,inventory_master.last_updated_by,inventory_master.date_created, inventory_master.created_by FROM inventory_master LEFT JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id" elif (tablename == "user"): dataQuery = "SELECT UPPER(user.username) AS `Username`, user.first_name AS `First Name`, user.last_name AS `Last Name`, UPPER(user_level.user_level) AS `User Level`, UPPER(user.shift) AS `Shift`, user.created_by AS `Created By`, user.date_created AS `Date Created`, user.last_updated_by AS `Last Updated`, user.last_updated_date AS `Last Updated At` FROM user LEFT JOIN user_level ON user_level.id = user.user_level_id WHERE user.first_name IS NOT NULL ORDER BY user.username;" 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') or (col == 'available_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 except UnboundLocalError: return ('MySql Error', dataQuery) finally: try: cursor.close() cnx.close() book.close() except: pass response = "/vimsreports/" + tablename + ".xlsx" return (response) def main(reportpath): dbhost = '172.18.25.11' dbuser = 'root' dbpwd = 'root' dbase = 'kuk' tablename = 'company' 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] if not os.path.isdir(reportpath): os.makedirs(reportpath) sender = "vims@vanteceurope.com" response = table(tablename, filterstr, reportpath, dbhost, dbuser, dbpwd, dbase) return (response) if __name__ == '__main__': drive_letter = Path.cwd().drive report_path = drive_letter + "/webroot/vimsreports" response = main(report_path) print(response)