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) < 14)):
        date = "0001-01-01 00:00:01"
    else:
        dd = date[0:2]
        mm = date[3:5]
        yy = date[6:8]
        tm = date[9:14]
        date = '20'+yy+"-"+mm+"-"+dd+" "+tm+":00"
    return date
    
def clearPick(connection):
    cursor = connection.cursor()
    try:
        dataQuery = "TRUNCATE pick_plan"
        cursor.execute(dataQuery)
        connection.commit()
    except Exception as e:
        dataQuery = "DELETE FROM pick_plan WHERE id > 0"
        cursor.execute(dataQuery)
        connection.commit()
    return

def ftpGetPick(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, 'pickplan.txt')
        ftp = ftplib.FTP(FTP_HOST)
        ftp.login(FTP_USER,FTP_PASS)
        ftp.cwd(BaaNDir)
        
        filename = 'VELORDIN'
        try:
            fd = open(host_file, 'wt')
            ftp.retrlines('RETR VELORDIN', 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 pickPlan(connection,data):
    try:
#    if 1==1:
# Test for Existing Reference
        cursor = connection.cursor()
        print("TEST1",data)
        try:
            dataQuery = "DELETE FROM pick_plan WHERE customer_reference='"+data[0]+"' AND line_no="+str(data[3])+" LIMIT 1"
            cursor.execute(dataQuery) 
            connection.commit()
        except Exception as e:
            print ("FAIL-",e)
#        print("TEST2",dataQuery)
        try:
#                dataQuery = "INSERT INTO pick_plan (id,customer_reference,line_no,part_number,qty_expected,zone_destination,production_date,build_date,sale_date,status,transaction_type,operation,distributer,dist_name,parent_part,project,item,date_created,created_by,last_updated_date,last_updated_by)"
            dataQuery = "INSERT INTO pick_plan (id,customer_reference,line_no,part_number,qty_expected,zone_destination,production_date,build_date,sale_date,status,transaction_type,operation,distributer,dist_name,parent_part,project,item,date_created,created_by,last_updated,last_updated_by)"
            prodDate = formatDate(data[2])
            buildDate = formatDate(data[10])
            saleDate = formatDate(data[11])
            project = data[9][0:9]
            item = data[9][9:]
            if (data[8] != ''):
                dataQuery += " VALUES(DEFAULT,'"+data[0]+"',"+data[3]+",'"+data[5]+"',"+str(data[6])+",'"+data[8]+"','"+str(prodDate)+"','"+str(buildDate)+"','"+str(saleDate)+"','"+data[1]+"','issue','"+data[7]+"','"+data[12]+"','"+data[13]+"','"+item+"','"+project+"','"+item+"',now(),'sys',now(),'sys')"
            else:
                dataQuery += " VALUES(DEFAULT,'"+data[0]+"',"+data[3]+",'"+data[5]+"',"+str(data[6])+",'"+data[8]+"','"+str(prodDate)+"','"+str(buildDate)+"','"+str(saleDate)+"','"+data[1]+"','issue','"+data[7]+"','"+data[12]+"','"+data[13]+"','"+item+"','"+project+"','"+item+"',now(),'sys',now(),'sys')"
#            print("TEST3",dataQuery)
            cursor.execute(dataQuery)
            connection.commit()
        except Exception as e:
            print ('Update Error',str(e))
    except Exception as e:
        print("FAIL-",str(e))
    finally:
       cursor.close()
#    print("TEST4")
    return
	
# 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('clearPick')
        clearPick(connection)
#        print('ftpGetPick')
        file_path_map = ftpGetPick(connection)
        
#        file_path_map = os.path.join(dir_path, 'pickplan.txt')
        pickInput = open(file_path_map,"r")
#        print('pickPlan',file_path_map)
# for each line split out fields.
        i = 0
        for lines in pickInput:
#            i=i+1
#            print("LINE",str(i))
            lines = lines.replace('\n','')
            data = lines.split("|")            
#               print("TEST-",data)
            if ((data) and (len(data)>0) and (data[0] != '')):
                pickPlan(connection,data)
        pickInput.close()       
    except Exception as e:
        print("FAIL-",str(e))
if __name__ == '__main__':
    main()