# Create / Update Receipt Data from ASN Interface Files

import os
import sys
import shutil
import glob
import time
import datetime
import mysql.connector

import default_setting

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

def partUpdate(cnx,partnumber,insp,crit):
    try:
# pad / format date/time
#		dateTime = date[6:9]+"-"+date[3:5]+'-'+date[0:3]+' 00:00:01'
# Test for Existing Reference
        cursor = cnx.cursor()
#		dataQuery = "UPDATE part SET requires_inspection = "+insp+",last_updated=now(),last_updated_by='sys' WHERE part_number LIKE('%"+partNumber+"') LIMIT 1"
        dataQuery = "UPDATE part SET "
        if (insp != ""):
            dataQuery +=" requires_inspection="+insp+","
        if (crit != ""):
            dataQuery +=" high_value="+crit+","
        dataQuery +=" last_updated=now(),last_updated_by='sys' WHERE LOCATE(part.part_number,'"+partnumber+"') >0 LIMIT 10"
        print("mysql:",dataQuery)
        try:
            cursor.execute(dataQuery)
            cnx.commit()
        finally:
            cursor.close()
    except UnboundLocalError as e:
        print(e,dataQuery)

    return

def ssUpdate(cnx,partnumber,insp,crit):
    try:
        cursor = cnx.cursor()
        
# Get location_type id
        locTypeId = 0
        dataQuery = "SELECT id FROM location_type WHERE lower(location_type_code)='stock' LIMIT 1"
        try:
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            locTypeId = row[0]
        except Exception as Err:
            locTypeId = 0
        locSubTypeId = 0
        dataQuery = "SELECT id FROM location_sub_type WHERE lower(location_sub_type_code)='ss' LIMIT 1"
        try:
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            locSubTypeId = row[0]
        except Exception as Err:
            locSubTypeId = 0
        
        dataQuery = "UPDATE part SET "
        if (locTypeId != 0):
            dataQuery +=" fixed_location_type_id="+str(locTypeId)+","
        if (locSubTypeId != 0):
            dataQuery +=" fixed_sub_type_id="+str(locSubTypeId)+","
        dataQuery +=" last_updated=now(),last_updated_by='sys_crit' WHERE LOCATE(part.part_number,'"+partnumber+"') >0 LIMIT 2"
        print("mysql:",dataQuery)
        try:
            cursor.execute(dataQuery)
            cnx.commit()
        finally:
            cursor.close()
    except UnboundLocalError as e:
        print(e,dataQuery)
    return

def newUpdate(cnx,partnumber,insp,crit):
    try:
        cursor = cnx.cursor()
# check for change of critical status
        dataQuery = "SELECT part.high_value AS high_value FROM part WHERE LOCATE(part.part_number,'"+partnumber+"') >0 LIMIT 2"
        try:
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            highVal = row[0]
        except Exception as Err:
            highVal = 0
        if (highVal == 1):
# Get location_type id
            locTypeId = 0
            dataQuery = "SELECT id FROM location_type WHERE lower(location_type_code)='new' LIMIT 1"
            try:
                cursor.execute(dataQuery) 
                row = cursor.fetchone()
                locTypeId = row[0]
            except Exception as Err:
                locTypeId = 0
            locSubTypeId = 0
            dataQuery = "SELECT id FROM location_sub_type WHERE lower(location_sub_type_code)='new' LIMIT 1"
            try:
                cursor.execute(dataQuery) 
                row = cursor.fetchone()
                locSubTypeId = row[0]
            except Exception as Err:
                locSubTypeId = 0
            
            dataQuery = "UPDATE part SET "
            if (locTypeId != 0):
                dataQuery +=" fixed_location_type_id="+str(locTypeId)+","
            if (locSubTypeId != 0):
                dataQuery +=" fixed_sub_type_id="+str(locSubTypeId)+","
            dataQuery +=" last_updated=now(),last_updated_by='sys_crit' WHERE LOCATE(part.part_number,'"+partnumber+"') >0 LIMIT 2"
            print("mysql:",dataQuery)
            try:
                cursor.execute(dataQuery)
                cnx.commit()
            finally:
                cursor.close()
    except UnboundLocalError as e:
        print(e,dataQuery)
    return

def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
    return ts

def getTxt(filename,cnx):
    partnumber = ""
    insp = ""
    crit = ""
    idoc = ""
    f = open(filename,'r')
    x = f.readline()
    while x:
        if (x[:14] == "SEGNAM:E2MARAM"):
            s = x.find("MATNR:")
            if (s > -1):
                try:	
                    e = x.find(",",s)
                    if (e == -1):
                        e = s+21
                    partnumber = x[s+6:e]
                except:
                    pass
    #			print("partnumber",partnumber)
        if (x[:14] == "SEGNAM:ZMARCEX"):
            s = x.find("IDOC:")
            if (s > -1):
                try:	
                    e = x.find(",",s)
                    if (e == -1):
                        e = s+21
                    idoc = x[s+5:e]
                except:
                    pass
            s = x.find("INSP:")
            if (s > -1):
                try:
                    insp = x[s+5:s+6]
                except:
                    pass
            s = x.find("CRIT:")
            if (s > -1):
                try:
                    crit = x[s+5:s+6]
                except:
                    pass		
        if (partnumber != "") and ((insp != "") or (crit != "")):
            print("part:",partnumber," insp:",insp," crit:",crit)
            if (crit == "0"):
                newUpdate(cnx,partnumber,insp,crit)
            partUpdate(cnx,partnumber,insp,crit)
            if (crit == "1"):
                ssUpdate(cnx,partnumber,insp,crit)
            partnumber = ""
            insp = ""
            crit = ""
            idoc = ""
            
        ok = ""
        while (ok == ""):
            try:
                x = f.readline()
                ok = "ok"
            except:
                ok = ""
    f.close()
			
def main():
    ts = getts()
    print('Time:'+str(ts))
    dir_path = os.path.dirname(os.path.realpath(__file__))
# Default Connection / System Settings
    defaults = default_setting.defaultSettings()
    uploadDirs = default_setting.companySettings(defaults)
    print(uploadDirs['interfacein'])
		
# mySql Connector
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
		
# read "INSP" files
    for insp in glob.glob("c:/rrfiles/input/INSP*.txt"):

# Process IDOC message
        getTxt(insp,cnx)
		
# remove processed file			
        if os.path.exists(insp):
            os.remove(insp)
		
if __name__ == '__main__':
    main()