import os
import time
import datetime
import mysql.connector 
import ftplib

import default_setting

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

def formatDate(date):
    if ((date == None) or (len(date) < 8)):
        date = "0001-01-01 00:00:01"
    else:
        yy = date[0:4]
        mm = date[4:6]
        dd = date[6:8]
        date = yy+"-"+mm+"-"+dd+" 00:00:01"
    return date
    
def clearreceipt(connection):
    cursor = connection.cursor()
    try:
        dataQuery = "TRUNCATE receipt_plan"
        cursor.execute(dataQuery)
        connection.commit()
    except Exception as e:
        dataQuery = "DELETE FROM receipt_plan WHERE id > 0"
        cursor.execute(dataQuery)
        connection.commit()
    return

def ftpGetreceipt(connection):

    def writeline(data):   
        fd.write(data+'\n')   
#        fd.write(os.linesep)
     
    try:
#    environment =  'WMS_TESTOUT'
        environment =  'KUK_TESTIN'  
#    $environment =  'KUK_LIVEIN'  

        FTPSQL = "SELECT * FROM kuk_ftp WHERE env_name = '"+environment+"' limit 1"
        cursor = connection.cursor(dictionary=True)
        cursor.execute(FTPSQL) 
        FTPRow = cursor.fetchone()
        if FTPRow:
            BaaNHandShake = FTPRow['handshake_dir'];
            BaaNDir = FTPRow['BaaN_dir'];
            VimsDir = FTPRow['vims_dir'];
            VimsHandShake = FTPRow['vims_handshake'];
            readlock = FTPRow['read_lock_file'];
            writelock = FTPRow['write_lock_file'];
            FTP_HOST = FTPRow['ip_address'];
            FTP_USER = FTPRow['user_id'];
            FTP_PASS = FTPRow['password'];
        cursor.close()
# set-up ftp connection to iSeries
        FTP_HOST = '172.92.5.3'
        BaaNDir = 'WM#DTA'
        FTP_USER = 'ca400'
        FTP_PASS = 'ca400'
        file_path = 'f:/kukfiles/adhoc'
        host_file = os.path.join(file_path, 'receiptplan.txt')
        ftp = ftplib.FTP(FTP_HOST)
        ftp.login(FTP_USER,FTP_PASS)
        ftp.cwd(BaaNDir)
        
        filename = 'VELRCDDIN'
        try:
            fd = open(host_file, 'wt')
            ftp.retrlines('RETR VELRCDIN', writeline)
            fd.close()
#            with open(host_file, 'wb') as local_file:
#                ftp.sendcmd('SITE NAMEFMT 1')
#                ftp.sendcmd('ASCII')
#                ftp.retrbinary('RETR ' + filename, local_file.write)
#                ftp.retrlines('RETR ' + filename, local_file.write)
#                ftp.delete(filename)
        except Exception as e:
            print('FAIL-ftp error',e)
            pass 
        ftp.quit()
    except Exception as e:
        print ("FAIL-",e)
    return host_file
        
def receiptPlan(connection, data):
    """
    data expected as sequence:
    data[0] = customer_reference (str)
    data[1] = line_no (int or str convertible)
    data[2] = part_number (str)
    data[3] = qty_required (numeric)
    data[4] = receipt_date (raw string for formatDate)
    data[5] = business_partner (str)
    data[6] = business_name (str)
    data[7] = promise_flag (str)
    """
    cursor = None
    try:
        cursor = connection.cursor()
        dueDate = formatDate(data[4]) # keep your existing formatting
        sql = """
        INSERT INTO receipt_plan
        (id, customer_reference, line_no, part_number, qty_required, receipt_date,
        business_partner, business_name, promise_flag, date_created, created_by,
        last_updated, last_updated_by)
        VALUES
        (DEFAULT, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), 'sys', NOW(), 'sys')
        ON DUPLICATE KEY UPDATE
        part_number = VALUES(part_number),
        qty_required = VALUES(qty_required),
        receipt_date = VALUES(receipt_date),
        business_partner = VALUES(business_partner),
        business_name = VALUES(business_name),
        promise_flag = VALUES(promise_flag),
        last_updated = NOW(),
        last_updated_by = 'sys'
        """
        params = (
        data[0],
        int(data[1]),
        data[2],
        float(data[3]) if data[3] not in (None, '') else None,
        dueDate,
        data[5],
        data[6],
        data[7],
        )
        cursor.execute(sql, params)
        connection.commit()
    except Exception as e:
    # log or raise as appropriate
        print("receiptPlan error:", e)
    try:
        connection.rollback()
    except Exception:
        pass
    finally:
        if cursor:
            cursor.close()
    
# timestamp value   
def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
    return ts

def main():
    try:
# Default Connection / System Settings
        defaults = default_setting.defaultSettings()
# mySql Connector
        connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
        
#        print('clearreceipt')
        clearreceipt(connection)
#        print('ftpGetreceipt')
#        file_path_map = ftpGetreceipt(connection)
        
        file_path_map = "C:/kcc6/633/interface/WMS/common/komoutput/RECEIPTPLAN"
        receiptInput = open(file_path_map,"r")
#        print('receiptPlan',file_path_map)
# for each line split out fields.
        print("Start: ",getts())
        i = 0
        for lines in receiptInput:
#            i=i+1
#            print("LINE",str(i),lines)
            lines = lines.replace('\n','')
            data = lines.split("|")            
#               print("TEST-",data)
            if ((data) and (len(data)>0) and (data[0] != '')):
                receiptPlan(connection,data)
        receiptInput.close()
        print("End: ",getts())       
    except Exception as e:
        print("FAIL-",str(e))
if __name__ == '__main__':
    main()