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 pickPlan(connection,data):
    try:
#    if 1==1:
# Test for Existing Reference
        cursor = connection.cursor()
        print("PICK_PLAN-",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)
        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,last_updated_by)"
            prodDate = formatDate(data[2])
            buildDate = formatDate(data[10])
            saleDate = formatDate(data[11])
            project = data[9][0:9]
            item = data[9][9:]
            pos = data[4].find('Receipt')
            if pos > 0:
                type = 'receipt'
            else:
                type = 'issue'

            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]+"','"+type+"','"+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]+"','"+type+"','"+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'])
        
        clearPick(connection)
        
        file_path_map = "C:/kcc6/633/interface/WMS/common/komoutput/PICKPLAN"
        pickInput = open(file_path_map,"r")
# for each line split out fields.
        i = 0
        for lines in pickInput:
            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()