def doRemove(id_val, openfile):
db = AlohaDBF.DbfData(openfile)
rec = list(filter(lambda record: id_val == record[b'ID'], db))[0]
offset_start = rec.position
bytesDeleted = len(rec.to_bytes())
offset_next_rec = offset_start + bytesDeleted
#log("Offset["+str(offset_start)+"] ,delete "+str(bytesDeleted)+" bytes ,next offset["+str(offset_next_rec)+"]" )
#Keep remaining
db.seek(offset_next_rec)
bytesRemaining = db.read()
#Delete
db.seek(offset_start + 0)
db.truncate()
db.write(bytesRemaining)
#log("Write remaining "+str(len(bytearray(bytesRemaining))))
newRecordCount = db.record_count() - 1
#log("Update record size is "+str(newRecordCount))
db.flush()
if db.writable():
# write SUB (ASCII 26) after last record
db.seek(db.header_length() + newRecordCount * db.record_length())
db.write(b"\x1A")
db.truncate()
handleDBF = AlohaDBF.AlterHeadData()
handleDBF.loadByteArray(openfile)
handleDBF.resetRecordCount(newRecordCount)
handleDBF.writeToFile()
def main(argv):
#log("Original file record size is "+str(dbfRec.record_count()))
if menulink_ver <= 15 and aloha_ver >= 12:
#Check exist from query to file
conn = SQLConn.createConn()
cursor = SQLConn.getEmpData(conn,site_number)
for row in cursor:
empQuery = DBF.Emp(row)
loopOnRecord(empQuery, dbfRec)
log("hasNewRecordCreated is "+str(hasNewRecordCreated))
if hasNewRecordCreated:
doWriteCreated(dbfRec)
else:
dbfRec.close()
#log("File updated/created record size is " + str(dbfRec.record_count()))
#Check file to removed
openfile = filename
cursor = SQLConn.getEmpData(conn,site_number)
cursorID = [row[0] for row in cursor]
loopOnDBF(cursorID,openfile)
db = AlohaDBF.DbfData(openfile)
#log("File deleted record size is " + str(db.record_count()))
dbfRec.showWarning("Database file updated",
"The data "+str(DBF_FILE.get(sys.argv[1:][0]))+" is updated. Please restart Aloha Manager to load updated")
elif menulink_ver >= 15 and aloha_ver < 12:
print("to be future")
else:
pass
def doWriteCreated(db):
global filename
#print("Created row size update is "+str(db.record_count()))
newRecordCount = db.record_count()
db.close()
#log("Write updated file name "+ str(filename))
handleDBF = AlohaDBF.AlterHeadData()
handleDBF.loadByteArray(filename)
handleDBF.resetRecordCount(newRecordCount)
handleDBF.writeToFile()
def loopOnDBF(cursor,openfile):
db = AlohaDBF.DbfData(openfile)
found = list(filter(lambda record: record[b'MIDDLENAME'][0:3] == "SSN", db))
#log("Loop employee size=" + str(len(found)))
db.close()
for rec in found:
#log("Finding rec_ID " + str(rec[b'ID']))
foundID = list(filter(lambda ID: float(ID) == rec[b'ID'], cursor))
isExist = any(True for _ in foundID)
if not isExist:
log("Record deleted ,ID=" + str(int(rec[b'ID'])))
doRemove(rec[b'ID'], openfile)
def loopOnDBF(listOf_EmpID_Query, openfile):
db = AlohaDBF.DbfData(openfile)
found = list(
filter(
lambda record: record[b'MIDDLENAME'][0:3] == "SSN" or record[
b'MIDDLENAME'][0:4] == "กรอก", db))
#log("Loop employee size=" + str(len(found)))
db.close()
for rec in found:
#log("Finding rec_ID " + str(rec[b'ID']))
matchID = list(
filter(lambda ID: float(ID) == rec[b'ID'], listOf_EmpID_Query))
#log(len(matchID))
isExist = any(True for _ in matchID)
#log(isExist)
if not isExist:
log("Record deleted ,ID=" + str(int(rec[b'ID'])))
doRemove(rec[b'ID'], openfile)
def main_run(argv):
# log("Original file record size is "+str(dbfRec.record_count()))
if menulink_ver <= 15 and aloha_ver >= 12:
conn = SQLConn.createConn()
# Check exist from query to file
cursor = SQLConn.getEmpData(conn, site_number)
for row in cursor:
empQuery = DBF.Emp(row)
loopOnRecord(empQuery, dbfRec)
#log("hasNewRecordCreated is " + str(hasNewRecordCreated))
if hasNewRecordCreated:
doWriteCreated(dbfRec)
else:
dbfRec.close()
# log("File updated/created record size is " + str(dbfRec.record_count()))
# Check file to removed
openfile = filename
cursor = SQLConn.getEmpData(conn, site_number)
cursorID = [row[0] for row in cursor]
loopOnDBF(cursorID, openfile)
db = AlohaDBF.DbfData(openfile)
# log("File deleted record size is " + str(db.record_count()))
self.progress.stop()
self.progress.grid_forget()
self.label['text'] = 'Done.'
self.btn['state'] = 'normal'
dbfRec.showWarning(
"Database file updated",
"The data " + str(DBF_FILE.get(sys.argv[1:][0])) +
" is updated. Please refresh data in Aloha Configuration Center to load updated"
)
elif menulink_ver >= 15 and aloha_ver < 12:
log("to be future")
else:
pass
"""
log("############# Start Running")
log("Load configuration file config.ini")
config = Configure.ConfigureData("config.ini")
site_number = config.ConfigSectionMap("Minor")['site_number']
menulink_ver = float(config.ConfigSectionMap("MenuLink")['version'])
aloha_ver = float(config.ConfigSectionMap("Aloha")['version'])
log("MunuLink version " + str(menulink_ver))
log("Aloha version " + str(aloha_ver))
newdata_location = config.ConfigSectionMap("Aloha")['newdata_path']
filename = newdata_location + "\\" + str(DBF_FILE.get(sys.argv[1:][0]))
log("Exporting data to "+filename)
dbfRec = AlohaDBF.DbfData(filename)
# List field
byte_field_name = dbfRec.field_names()
byteKey_Map_StrKey = {bkey: bkey.decode(encoding='utf-8', errors='strict') for idx, bkey in
enumerate(byte_field_name)}
SQLConn = getSQLConn(config)
main(sys.argv[1:])
log("Finished")
except IndexError:
log("Error:DBF file is required ,please assign with options")
raise SystemExit("DBF file is required ,please assign with options.\n Example: toolExport.exe EMP")
except FileNotFoundError:
log("The DBF File is not found")
raise SystemExit("The DBF File is not found")