#!/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")
    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.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;
            """
        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
    #			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 = '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]
        
#    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"
    reportpath = "e:/webRoot/vimsreports"
    response = main(reportpath)
    print (response)