def get_employee_details(self):
# global DATA_LIST
tx = "SELECT "
if SQL_TOP != '': tx += SQL_TOP + ' '
tx += "last_name, first_name, employee_id from temployee "
tx += EMPLOYEE_SQL
ret = sqlconns.sql_select_into_list(tx)
if ret == -1:
DATA_LIST = [['None', 'None', '0', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None']]
else:
DATA_LIST = [x for x in ret]
for n in range(len(DATA_LIST)):
DATA_LIST[n] = DATA_LIST[n] = [x for x in DATA_LIST[n]]
for y in range(12):
DATA_LIST[n].append('')
for n in range(len(DATA_LIST)):
for y in range(12):
DATA_LIST[n][y + 3] = ''
sql_get_swipes_tx = 'SELECT TOP (12) twork_swipe.date_and_time from twork INNER JOIN twork_swipe ON twork.work_id = twork_swipe.work_id ' \
' WHERE twork.employee_id = ' + str(
DATA_LIST[n][2]) + ' AND twork.[type] = 1000 and twork.date_and_time = ' + f.get_sql_date(
datetime.now(), "yyyy-mm-dd") + ' ORDER BY twork_swipe.date_and_time ASC'
swipes = sqlconns.sql_select_into_list(sql_get_swipes_tx)
if swipes != -1 and len(swipes) > 0:
for y in range(len(swipes)):
DATA_LIST[n][y + 3] = swipes[y][0]
#return [['None', 'None', '0', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None']]
return DATA_LIST
def work_out_pairs(time1, time2):
if time1 == None or time1 == ' ' or time1 == '': return 0
time1 = str(f.convert_sql_date(time1, "hh:mm"))
if time2 == None or time2 == ' ' or time2 == '':
time2 = str(f.convert_sql_date(datetime.now(), "hh:mm"))
else:
time2 = str(f.convert_sql_date(time2, "hh:mm"))
try:
h1 = str(time1).split(':')
h2 = str(time2).split(':')
if int(h1[0]) > 23: return 0
if int(h2[0]) > 23: return 0
if int(h1[1]) > 59: return 0
if int(h2[1]) > 59: return 0
mins1 = (int(h1[0]) * 60) + int(h1[1])
mins2 = (int(h2[0]) * 60) + int(h2[1])
if mins2 < mins1: mins2 += (24 * 60)
mins1 = mins2 - mins1
return mins1
except:
return 0
#def get_employee_details():
# #global DATA_LIST
# tx = "SELECT "
# if SQL_TOP != '': tx += SQL_TOP + ' '
# tx += "last_name, first_name, employee_id from temployee "
# tx += EMPLOYEE_SQL
ret = sqlconns.sql_select_into_list(tx)
def get_terminal_status_list():
terminal_list = sqlconns.sql_select_into_list("SELECT description, ip_address, configuration,poll_success," \
" (SELECT TOP 1 last_ip as lastIp from d_iface_stamps WHERE terminal_id = tterminal.terminal_id)" \
" FROM tterminal LEFT OUTER JOIN" \
" d_iface_stamps ON tterminal.terminal_id = d_iface_stamps.terminal_id" \
" WHERE" \
" configuration in (" + str(ACCESS_TERMINAL) + "," + str(ATTENDANCE_TERMINAL) + ")" \
" ORDER BY configuration, description")
if terminal_list==-1: return ""
tx = ""
for index in range(len(terminal_list)):
tx += '<tr>'
terminal_type = ""
#have included other terminal types but they will not show unless they are in the general.ini as access or attendance terminal config type
if terminal_list[index][2] == 4: terminal_type = 'Access Control'
if terminal_list[index][2] == 10: terminal_type = 'ZK Terminal'
if terminal_list[index][2] == 8: terminal_type = 'Attendance NMD'
if terminal_list[index][2] == 5: terminal_type = 'Hand Punch'
if terminal_list[index][2] == 14: terminal_type = 'Fire Panel'
if str(terminal_list[index][3]) == "None":
tx += '<td>' + terminal_list[index][1] + '</td><td>' + 'No polling received.' + '</td><td>' + str(terminal_list[index][0]) + '</td><td>' + terminal_type + '</td>'
else:
tx += '<td>' + terminal_list[index][1] + '</td><td>' + str(f.convert_sql_date(terminal_list[index][3],'dd/mm/yyyy hh:mm')) + '</td><td>' + str(terminal_list[index][0]) + '</td><td>' + terminal_type + '</td>'
#dummy space for last column
if terminal_list[index][4] != None:
tx += '<td>' + terminal_list[index][4] + '</td>'
else:
tx += '<td>' + '0.0.0.0' + '</td>'
tx += '</tr>'
return tx
def build_power_on_get_request(sn):
terminal_id = get_terminal_id_from_sn(sn)
if terminal_id=="": return ""
att_stamp = 1
op_stamp = 1
tx = "SELECT table_name,stamp FROM d_iface_stamps WHERE terminal_id = " + str(terminal_id)
ret = sqlconns.sql_select_into_list(tx)
if ret!=-1:
for index in range(len(ret)):
if ret[index][0]== "att_stamp": att_stamp = ret[index][1]
if ret[index][0]== "op_stamp": op_stamp = ret[index][1]
else:
return ret
tx = "SELECT TOP 1 notepad from tterminal WHERE ip_address = '" + sn + "'"
notepad_options = str.lower(sqlconns.sql_select_single_field(tx))
#tidy up on stamps based on latest push firmware, refer to older backups if you need to revert this.
trans_flag_string = "1"
if 'uface' in notepad_options:
trans_flag_string = 'TransData AttLog\tOpLog\tAttPhoto\tEnrollUser\tChgUser\tEnrollFP\tChgFP\tFACE\tUserPic'
xx = "GET OPTION FROM:" + sn + \
"\r\nStamp=" + str(att_stamp) +\
"\r\nOpStamp=" + str(op_stamp) + \
"\r\nPhotoStamp=" + str(op_stamp) + \
"\r\nErrorDelay=3" +\
"\r\nDelay=5" + \
"\r\nTransTimes=" + "00:00;14:05" + \
"\r\nTransInterval=" + "1" + \
"\r\nTransFlag=" + trans_flag_string + \
"\r\nRealtime=1" + \
"\r\nTimeZone=1" + \
"\r\nATTLOGStamp=" + str(att_stamp) + \
"\r\nOPERLOGStamp=" + str(op_stamp) + \
"\r\nATTPHOTOStamp=" + str(op_stamp) + \
"\r\n"
return xx
def try_sql():
xx = sqlconns.sql_select_into_list(
"SELECT photo,paylink,last_name,first_name from temployee where employee_status_id <> 8"
)
cnt = 0
for index in range(len(xx)):
if xx[index][1] != None:
if xx[index][0] != None:
cnt += 1
paylink = xx[index][1]
lastname = xx[index][2]
firstname = xx[index][3]
filename = ''
if FILENAME_FORMAT == 'lastnamefirstnamepaylink':
filename = EXPORT_PATH + lastname + '_' + firstname + '_' + paylink
elif FILENAME_FORMAT == 'lastnamefirstname':
filename = EXPORT_PATH + lastname + '_' + paylink
elif FILENAME_FORMAT == 'paylink':
filename = EXPORT_PATH + paylink
else:
print('Error in filename_format...check ini file.')
return -1
filename += IMAGE_TYPE
print('Writing...', filename)
f = open(filename, 'wb')
f.write(xx[index][0])
f.close()
return cnt
def inbio_started():
list = sqlconns.sql_select_into_list(
"SELECT TOP 1 value FROM d_inbio_misc WHERE property like 'paused'")
if list == -1: return -1
if len((list)) == 0: return True
if list[0][0] == "0": return True
return False
def get_doors_from_ip(terminal_ip):
door_list=[]
sql_text = "SELECT terminal_id,number from tterminal where number >= 1000 AND ip_address like '%" + terminal_ip + "%'" + " AND configuration =" + gl.access_terminal_configuration + " order by number"
door_list_list = sqlconns.sql_select_into_list(sql_text)
if door_list_list == -1: return -1
#turn list of lists into a list as there is only one field
return door_list_list
def get_terminal_status_list():
terminal_list = sqlconns.sql_select_into_list('SELECT description, ip_address, configuration,poll_success FROM tterminal WHERE \
configuration in (' + str(ACCESS_TERMINAL) + ',' + str(ATTENDANCE_TERMINAL) + ')'\
'ORDER BY configuration, description')
if terminal_list == -1: return ""
tx = ""
for index in range(len(terminal_list)):
terminal_type = ""
if terminal_list[index][2] == 4: terminal_type = 'Access Terminal'
if terminal_list[index][2] == 10: terminal_type = 'ZK Terminal'
if terminal_list[index][2] == 8: terminal_type = 'Attendance Terminal'
if terminal_list[index][2] == 5: terminal_type = 'Hand Punch'
if terminal_list[index][2] == 14: terminal_type = 'Fire Panel'
if str(terminal_list[index][3]) == "None":
tx += terminal_list[index][
1] + ' --- ' + 'No polling received.' + ' --- ' + str(
terminal_list[index][0]) + ' --- ' + terminal_type
else:
tx += terminal_list[index][1] + ' --- ' + str(
f.convert_sql_date(
terminal_list[index][3],
'dd/mm/yyyy hh:mm')) + ' --- ' + str(
terminal_list[index][0]) + ' --- ' + terminal_type
tx += '<br>'
return tx
def build_zpad_command_list(client):
try:
tx = "SELECT employee_id,[index] from d_zpad_events"
list_to_update = sqlconns.sql_select_into_list(tx)
if list_to_update == -1: return -1
for n in range(len(list_to_update)):
tx = "SELECT TOP 1 temployee.employee_id,security_pin,first_name,last_name,badge, exclude_from_calculation FROM temployee LEFT OUTER JOIN" \
" temployee_status ON temployee.employee_status_id = temployee_status.employee_status_id" \
" WHERE employee_id = " + str(list_to_update[n][0])
emp_details = sqlconns.sql_select_into_list(tx)
if emp_details == -1: return -1
if len(emp_details) == 0:
ret = del_empid(client, str(list_to_update[n][0]))
ret = sqlconns.sql_command(
"DELETE FROM d_zpad_events WHERE employee_id = ?",
list_to_update[n][0])
if ret == 1: return -1
for y in range(len(emp_details)):
empid = int(emp_details[y][0])
if emp_details[y][1] != None:
security_pin = str(emp_details[y][1])
else:
security_pin = ''
first_name = str(emp_details[y][2])
last_name = str(emp_details[y][3])
if emp_details[y][4] != None:
badge = int(emp_details[y][4])
else:
badge = 0
exclude_from_calculation = str(emp_details[y][5])
ret = del_empid(client, empid)
if exclude_from_calculation == "0":
ret = insert_empid(client, empid, security_pin, first_name,
last_name, badge)
if ret == -1:
return -1
else:
ret = sqlconns.sql_command(
"DELETE FROM d_zpad_events WHERE employee_id = ?",
empid)
if ret == 1: return -1
except ConnectionResetError as e:
f.error_logging(APPNAME, str(e), 'error_log',
str(inspect.stack()[0][3]))
return -1
def get_commands_list(sn):
terminal_id = sqlconns.sql_select_single_field("SELECT TOP 1 terminal_id FROM tterminal WHERE ip_address = '" + sn+"'")
if terminal_id == "": return -1
if int(terminal_id) > 0:
data_list = sqlconns.sql_select_into_list("SELECT top 50 iface_command_id,command FROM d_iface_commands where sent <> 1 and terminal_id ="+terminal_id+"ORDER BY iface_command_id")
if data_list==-1: return -1
return data_list
else:
return -1
def try_sql():
xx = sqlconns.sql_select_into_list("SELECT photo,paylink from temployee where employee_status_id <> 8")
for index in range(len(xx)):
if xx[index][1] != None:
if xx[index][0] != None:
yy = xx[index][0]
#print(str(len(yy)) + " " + str(xx[index][1]))
f=open("d:/ntd/images/"+ str(xx[index][1])+".png",'wb')
f.write(yy)
f.close()
def get_terminal_roll_call_info(terminal_id):
tx = "SELECT TOP 1 roll_call_enabled,r1_direction,r1_description,r1_zone_id FROM tterminal WHERE terminal_id = " + str(terminal_id)
roll_call_info_list = sqlconns.sql_select_into_list(tx)
if roll_call_info_list == -1 : return -1,-1,-1,-1
for n in range(len(roll_call_info_list)):
roll_call_enabled = roll_call_info_list[n][0]
reader_direction = roll_call_info_list[n][1]
reader_description = roll_call_info_list[n][2]
zone_id = roll_call_info_list[n][3]
return roll_call_enabled,reader_direction,reader_description,zone_id
def no_connection_error(ip_address):
list = sqlconns.sql_select_into_list(
"SELECT TOP 1 value FROM d_inbio_misc WHERE property like 'LOG_COMMS_FAILURES'"
)
if list == -1: return
if len((list)) == 0: return
if list[0][0] == "0": return
f.error_logging(APPNAME, "Connect failure to " + ip_address + ".",
COMM_ERROR, "")
return
def get_employee_details(self):
# global DATA_LIST
tx = "SELECT "
if SQL_TOP != '': tx += SQL_TOP + ' '
tx += "last_name, first_name, employee_id from temployee "
tx += EMPLOYEE_SQL
ret = sqlconns.sql_select_into_list(tx)
if ret == -1:
DATA_LIST = [[
'None', 'None', '0', 'None', 'None', 'None', 'None', 'None'
]]
else:
DATA_LIST = [x for x in ret]
for n in range(len(DATA_LIST)):
DATA_LIST[n] = DATA_LIST[n] = [x for x in DATA_LIST[n]]
for y in range(8):
DATA_LIST[n].append('')
for n in range(len(DATA_LIST)):
for y in range(8):
DATA_LIST[n][y + 3] = ''
sql_get_swipes_tx = "SELECT top 1 format(twork_job.start_time,'dd/MM/yyyy')as start_date, format(twork_job.start_time,'HH:mm')as start_date, twork_job.stop_time,tjob.code, tjob.description, toperation.description AS Expr1"\
"FROM"\
' twork INNER JOIN'\
' twork_job ON twork.work_id = twork_job.work_id INNER JOIN'\
' tjob ON twork_job.job_id = tjob.job_id INNER JOIN'\
' toperation ON twork_job.operation_id = toperation.operation_id'\
' WHERE twork.employee_id = ' + str(DATA_LIST[n][2]) + \
' order by date_and_time desc'
ret = sqlconns.sql_select_into_list(sql_get_swipes_tx)
if ret != -1 and len(ret) > 0:
DATA_LIST[n][SQL_COL_START_DATE] = ret[0]
DATA_LIST[n][SQL_COL_START_TIME] = ret[1]
DATA_LIST[n][SQL_COL_STOP_TIME] = ret[2]
DATA_LIST[n][SQL_COL_CODE] = ret[3]
DATA_LIST[n][SQL_COL_DESC] = ret[4]
DATA_LIST[n][SQL_COL_OP] = ret[5]
return DATA_LIST
def get_finger_template(emp_id):
finger_list=[]
employee_fingers = sqlconns.sql_select_into_list("SELECT size,fid,tmp from d_iface_finger WHERE employee_id = " + str(emp_id))
if employee_fingers ==-1:
return finger_list
p_fingers = create_string_buffer(str.encode("templatev10"))
for z in range(len(employee_fingers)):
tx = 'Size=0\tUID=#\tPin='+str(emp_id)+'\tFingerID='+str(employee_fingers[z][1])+\
'\tValid=1\tTemplate='+str(employee_fingers[z][2])+'\tResverd=\tEndTag='
finger_list.append(tx)
return finger_list
def get_command_info():
global TERMINAL_LIST
for n in range(len(TERMINAL_LIST)):
tx = "SELECT COUNT(*) AS FOO FROM d_iface_commands WHERE sent <> 1 AND terminal_id=" + str(
TERMINAL_LIST[n][0])
the_sum = sqlconns.sql_select_single_field(tx)
if the_sum == -1: continue
TERMINAL_LIST[n][1] = str(the_sum)
tx = "SELECT COUNT(*) AS FOO FROM d_iface_commands WHERE sent = 1 AND completed_flag <> 1 and terminal_id=" + str(
TERMINAL_LIST[n][0])
the_sum = sqlconns.sql_select_single_field(tx)
if the_sum == -1: continue
TERMINAL_LIST[n][2] = str(the_sum)
tx = "SELECT COUNT(*) AS FOO FROM d_iface_commands WHERE completed_flag = 1 AND terminal_id=" + str(
TERMINAL_LIST[n][0])
the_sum = sqlconns.sql_select_single_field(tx)
if the_sum == -1: continue
TERMINAL_LIST[n][3] = str(the_sum)
tx = "SELECT poll_success FROM tterminal WHERE terminal_id=" + str(
TERMINAL_LIST[n][0])
the_sum = sqlconns.sql_select_into_list(tx)
if the_sum == -1: continue
if the_sum[0][0] == None:
the_sum = "None"
elif the_sum[0][0] != None:
the_sum = str.replace(
f.get_sql_date(the_sum[0][0], "dd/mm/yyyy hh:mm:ss"), "'", "")
TERMINAL_LIST[n][4] = str(the_sum)
tx = "SELECT TOP 1 stamp FROM d_iface_stamps WHERE table_name like 'att_stamp' and terminal_id = " + str(
TERMINAL_LIST[n][0])
listy = sqlconns.sql_select(tx)
if listy == -1: continue
if len(listy) == 0:
att = 'None'
elif listy[0][0] == None:
att = "None"
else:
att = str(listy[0][0])
tx = "SELECT TOP 1 stamp FROM d_iface_stamps WHERE table_name like 'op_stamp' and terminal_id = " + str(
TERMINAL_LIST[n][0])
listy = sqlconns.sql_select(tx)
if listy == -1: continue
if len(listy) == 0:
op = 'None'
elif listy[0][0] == None:
op = "None"
else:
op = str(listy[0][0])
TERMINAL_LIST[n][5] = att
TERMINAL_LIST[n][6] = op
def build_personnel_list(terminal_ip):
#user table list
#authorised table list
personnel_auth_list =[]
personnel_list=[]
finger_template_list=[]
term_list = get_doors_from_ip(terminal_ip)
if len(term_list) == 0: return -1,-1
for index in range(len(term_list)):
sql_text = """SELECT temployee.employee_id, temployee.badge, temployee.date_started_with_company, temployee.badge_activation, temployee.badge_expiry, tterminal_policy.data
FROM temployee INNER JOIN
temployee_status ON temployee.employee_status_id = temployee_status.employee_status_id INNER JOIN
tterminal_policy ON temployee.terminal_policy_id = tterminal_policy.terminal_policy_id
WHERE exclude_from_calculation = 0 AND (
data like '%TerminalAssignment\AccessControl\optAllYes,True%'"""
sql_text += """ OR data like """ + "'%TerminalAssignment\\AccessControl\\Terminal\\" + str(term_list[index][0]) + ",True%'"
sql_text +=")"
employee_list = sqlconns.sql_select_into_list(sql_text)
#personnel_list=[[0 for cols in range(5)] for rows in range(len(employee_list))]
#now need to convert_personnel_list for sending to clock
for index in range(len(employee_list)):
x_string = "Pin=" + str(employee_list[index][0])
x_string +="""\tCardNo="""
if employee_list[index][1] != None: x_string += str(int(employee_list[index][1]))
x_string += "\tStartTime=" + get_start_time(employee_list[index][2],employee_list[index][3]) + "\tEndTime=" + get_end_time(employee_list[index][4])
personnel_list.append(x_string)
#now do authorised
#big question on whether you can have different timezone for each door
#if not this will need recoded for now assuming you can do that
for index in range(len(employee_list)):
#code finger stuff here
emp_finger=[]
emp_finger = get_finger_template(employee_list[index][0])
if len(emp_finger) > 0: finger_template_list.extend(emp_finger)
data = employee_list[index][5]
datalist = data.split("""\r""")
#got term list for employee for this inbio, now build authorised list
for z in range(len(term_list)):
#check you allowed for this clock
for y in range(len(datalist)):
term_policy_string = """AccessControl\\Terminal\\"""
term_policy_string += str(term_list[z][0]) + """,True"""
if term_policy_string in datalist[y]:
door_id = get_door_id(term_list[z][1])
auth_timezone_id = datalist[y+1]
auth_timezone_id = auth_timezone_id[-1]
if int(auth_timezone_id) > 0: personnel_auth_list.append("Pin=" + str(employee_list[index][0]) + "\tAuthorizeTimezoneId=" + auth_timezone_id + "\tAuthorizeDoorId=" + str(door_id))
print(personnel_list)
print(personnel_auth_list)
return personnel_list,personnel_auth_list,finger_template_list
def get_terminal_id(path):
path = path.replace("/iclock/cdata?SN=","")
list = str.split(path,"&")
sn = list[0]
table_type = list[1].replace("table=","")
stamp = list[2].split("=",1)[1]
tx = "SELECT TOP 1 terminal_id, configuration FROM tterminal WHERE ip_address = '" + sn+"'"
ret = sqlconns.sql_select_into_list(tx)
if ret == -1 or len(ret) == 0:
terminal_id = -1
configuration = -1
else:
terminal_id = ret[0][0]
configuration = ret[0][1]
return terminal_id, configuration, sn, table_type, stamp
def inbio_communicate():
#reset inbio_events
ret = sqlconns.sql_command("update d_inbio_events SET completed = 0")
if ret == -1: return -1
sql_text = "SELECT terminal_id,number,description,ip_address FROM tterminal WHERE number >= 1000 AND configuration = " + gl.access_terminal_configuration + \
" ORDER BY number"
terminal_list = sqlconns.sql_select_into_list(sql_text)
if terminal_list == -1: return -1
for index in range(len(terminal_list)):
if terminal_list[index][1] % 100 == 1:
ret = poll_inbio(terminal_list[index][3], terminal_list[index][0])
if ret == -1: return -1
ret = do_inbio_events(terminal_list)
if ret == -1: return -1
return
def populate_terminal_grid(self,inbio_controller_grid):
global INBIO_GRID_ROWS
INBIO_GRID_ROWS = 0
colLabels = ["Id", "Number", "Description", "IP Address", "Gateway","Net Mask","Receive Time","Information"]
colSize = [20,50,150,100,100,100,110,150]
sql_text = "SELECT terminal_id,number,description,ip_address FROM tterminal WHERE number >= 1000 AND configuration = " + gl.access_terminal_configuration + \
" ORDER BY number"
terminal_list = sqlconns.sql_select_into_list(sql_text)
if terminal_list ==-1:return -1
inbio_controller_grid.AppendRows(len(terminal_list), updateLabels = 1)
for index in range(len(terminal_list)):
INBIO_GRID_ROWS += 1
for col in range(4):
inbio_controller_grid.SetCellValue(index,col,str(terminal_list[index][col]))
#sort out columns
for index in range(8):
inbio_controller_grid.SetColLabelValue(index,colLabels[index])
inbio_controller_grid.SetColSize(index,colSize[index])
self.refresh_terminal_grid(inbio_controller_grid)
def build_power_on_get_request(sn):
terminal_id = get_terminal_id_from_sn(sn)
if terminal_id=="": return ""
#photo_stamp = 1
att_stamp = 1
op_stamp = 1
tx = "SELECT table_name,stamp FROM d_iface_stamps WHERE terminal_id = " + str(terminal_id)
ret = sqlconns.sql_select_into_list(tx)
if ret!=-1:
for index in range(len(ret)):
if "att_stamp" in ret[index][0]:att_stamp = ret[index][1]
if "op_stamp" in ret[index][0]:op_stamp = ret[index][1]
#if "photo_stamp" in ret[index][0]:photo_stamp = ret[index][1]
xx = "GET OPTION FROM:" + sn + "\r\nAttStamp=" + str(att_stamp) + "\r\nOpStamp=" + str(op_stamp) + \
"\r\nErrorDelay=" + "3" + "\r\nDelay=" + "15" + "\r\nTransTimes=" + "00:00;14:05" + \
"\r\nTransInterval=" + "1" + "\r\nTransFlag=" + "1" + "\r\nRealtime=" + \
"1" + "\r\nEncrypt=" + "0" + "\r\nTimeZone=" + "1" + "\r\n"
return xx
def get_terminal_details():
global DATA_LIST
global TERMINAL_LIST
tx = "SELECT terminal_id, description, number, ip_address,poll_success FROM tterminal WHERE configuration in (" + str(
ATTENDANCE_TERMINALS) + "," + str(
ACCESS_TERMINALS
) + ") and ip_address not like '%.%' ORDER BY number"
ret = sqlconns.sql_select_into_list(tx)
if ret == -1:
DATA_LIST = [[
'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None',
'None', 'None'
]]
else:
DATA_LIST = ret
TERMINAL_LIST = []
for n in range(len(DATA_LIST)):
TERMINAL_LIST.append([(str(DATA_LIST[n][0])), "0", "0", "0",
"None", "None", "None"])
def big_sql_query():
xx = sqlconns.sql_select_into_list("SELECT TOP 400 command from d_iface_commands WHERE sent <> 1 ORDER BY iface_command_id DESC")
print(xx)
def fetch_timezone_string():
sql_text = "Select * From taccess_pattern"
access_patterns = sqlconns.sql_select_into_list(sql_text)
if access_patterns == -1: return -1
timezone_string = build_timezone_string(access_patterns)
return timezone_string
def get_doors_from_ip(terminal_ip):
sql_text = "SELECT terminal_id,number from tterminal where number >= 1000 and ip_address like '%" + terminal_ip + "%'" + " AND configuration =" + gl.access_terminal_configuration + " ORDER by number"
door_list_list = sqlconns.sql_select_into_list(sql_text)
if door_list_list == -1: return -1
return door_list_list
def refreshTable(self):
#global DATA_LIST
# lastname, firstname , employee_id, LAst Swipe Time, Last Swipe Date,stop_time Job No, Job Description x 2
DATA_LIST = [[
'None', 'None', '0', 'None', 'None', 'None', 'None', 'None', 'None'
],
[
'None',
'None',
'0',
'None',
'None',
'None',
'None',
'None',
'None',
]]
#**********************************************************
tx = "SELECT "
if SQL_TOP != '': tx += SQL_TOP + ' '
tx += "last_name, first_name, employee_id from temployee "
tx += EMPLOYEE_SQL
ret = sqlconns.sql_select_into_list(tx)
# lastname, firstname , employee_id, LAst Swipe Time, Last Swipe Date, Job No, Job Description
if ret == -1:
DATA_LIST = [[
'None', 'None', '0', 'None', 'None', 'None', 'None', 'None',
'None'
]]
else:
DATA_LIST = [x for x in ret]
for n in range(len(DATA_LIST)):
DATA_LIST[n] = DATA_LIST[n] = [x for x in DATA_LIST[n]]
for y in range(
6
): # was 8 for 8 swipes now 6 for 6 fields in the select query
DATA_LIST[n].append('')
for n in range(len(DATA_LIST)):
for y in range(6):
DATA_LIST[n][y + 3] = ''
ret = "SELECT top 1 format(twork_job.start_time,'dd/MM/yyyy')as start_date, format(twork_job.start_time,'HH:mm')as start_date, twork_job.stop_time,tjob.code, tjob.description, toperation.description AS Expr1"\
" FROM"\
" twork INNER JOIN"\
' twork_job ON twork.work_id = twork_job.work_id INNER JOIN'\
' tjob ON twork_job.job_id = tjob.job_id INNER JOIN'\
' toperation ON twork_job.operation_id = toperation.operation_id'\
' WHERE twork.employee_id = ' + str(DATA_LIST[n][2]) + \
' order by date_and_time desc'
ret = sqlconns.sql_select_into_list(ret)
if ret != -1 and len(ret) > 0:
DATA_LIST[n][SQL_COL_START_DATE] = ret[0][0]
DATA_LIST[n][SQL_COL_START_TIME] = str(ret[0][1])
if ret[0][2] != None:
DATA_LIST[n][SQL_COL_STOP_TIME] = ret[0][2]
else:
DATA_LIST[n][SQL_COL_STOP_TIME] = ''
DATA_LIST[n][SQL_COL_CODE] = ret[0][3]
DATA_LIST[n][SQL_COL_DESC] = ret[0][4]
DATA_LIST[n][SQL_COL_OP] = ret[0][5]
for n in range(15): #was 22
self.tableWidget.horizontalHeaderItem(n).setTextAlignment(
Qt.AlignLeft)
#rowcount = 0
if len(DATA_LIST) == 1:
rowcount = 1
else:
rowcount = int((len(DATA_LIST) + 1) / 2)
self.tableWidget.setRowCount(rowcount)
for n in range(len(DATA_LIST)):
row_number = int((n) / 2)
if n % 2 == 0:
column = 'left'
else:
column = 'right'
if column == 'left':
col_mod = 0
else:
col_mod = 8
#list all the column mapping here
field = DATA_LIST[n][SQL_COL_LN]
self.tableWidget.setItem(row_number, GRID_COL_LN + col_mod,
QTableWidgetItem(field))
field = DATA_LIST[n][SQL_COL_FN]
self.tableWidget.setItem(row_number, GRID_COL_FN + col_mod,
QTableWidgetItem(field))
field = DATA_LIST[n][SQL_COL_START_DATE]
self.tableWidget.setItem(row_number, GRID_COL_START_DATE + col_mod,
QTableWidgetItem(field))
field = DATA_LIST[n][SQL_COL_START_TIME]
self.tableWidget.setItem(row_number, GRID_COL_START_TIME + col_mod,
QTableWidgetItem(field))
field = DATA_LIST[n][SQL_COL_CODE]
self.tableWidget.setItem(row_number, GRID_COL_CODE + col_mod,
QTableWidgetItem(field))
field = DATA_LIST[n][SQL_COL_DESC]
self.tableWidget.setItem(row_number, GRID_COL_DESC + col_mod,
QTableWidgetItem(field))
field = DATA_LIST[n][SQL_COL_OP]
self.tableWidget.setItem(row_number, GRID_COL_OP + col_mod,
QTableWidgetItem(field))
#go green and then if no start time go red
#if there is a stop_time go red
r = OUT_RED
g = OUT_GREEN
b = OUT_BLUE
if DATA_LIST[n][SQL_COL_START_DATE] == None or DATA_LIST[n][
SQL_COL_START_DATE] != '':
# orange
r = IN_RED
g = IN_GREEN
b = IN_BLUE
for col in range(7): #was 11
actual_col = col
if column == 'right': actual_col += 8 #was 12
if self.tableWidget.item(row_number, actual_col) != None:
self.tableWidget.item(row_number,
actual_col).setBackground(
QtGui.QColor(r, g, b))
stylesheet = "QHeaderView::section{Background-color:rgb(117,146,156);\
border-radius:40px;}"
self.tableWidget.setStyleSheet(stylesheet)
self.tableWidget.resizeColumnsToContents()
del DATA_LIST
self.restart()
def update_inbio(self,event):
ret = wx.MessageDialog(None,'Do you wish to update all terminal tables in Inbio ' + self.terminal_ip + '?', 'User Option', wx.YES_NO)
ret1 = ret.ShowModal()
if ret1 == wx.ID_NO:
ret.Destroy()
return -1
#keep track of how many items to send for the progress bar later
progressMax = 0
sql_text = "Select * From taccess_pattern"
access_patterns = sqlconns.sql_select_into_list(sql_text)
access_list = build_access_string(access_patterns)
personnel_list, personnel_auth_list,finger_template_list = build_personnel_list(self.terminal_ip)
progressMax +=len(personnel_list) + len(access_list) + len(personnel_auth_list) +len(finger_template_list)
if len(personnel_list) == 0:
wx.MessageBox('No data to send, please check personnel.','User Alert')
return
progressMax+=1
constr = create_string_buffer(str.encode('protocol=TCP,ipaddress='+ self.terminal_ip + ',port=4370,timeout=' + str(gl.COMM_TIMEOUT) +',passwd='))
commpro = windll.LoadLibrary("plcommpro.dll")
hcommpro = commpro.Connect(constr)
if hcommpro == 0:
wx.MessageBox('No communications, please check connections.','User Alert')
return
#relay times
term_list = get_doors_from_ip(self.terminal_ip)
for index in range(len(term_list)):
tx = 'select top 1 relay_trip_seconds from tterminal where terminal_id = ' + str(term_list[index][0])
relay_time= sqlconns.sql_select_single_field(tx)
if relay_time == - 1:
wx.MessageBox('Error during communications (relay list)...' + '(' + str(relay_time) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
if term_list[index][1]%100 == 1:
door_relay = 'Door1Drivertime=' + str(relay_time)
p_items = create_string_buffer(str.encode(door_relay))
ret = commpro.SetDeviceParam(hcommpro, p_items)
if ret < 0:
wx.MessageBox('Error during communications (door relay)...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
if term_list[index][1]%100 == 2:
door_relay = 'Door2Drivertime=' + str(relay_time)
p_items = create_string_buffer(str.encode(door_relay))
ret = commpro.SetDeviceParam(hcommpro, p_items)
if ret < 0:
wx.MessageBox('Error during communications (door relay)...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
if term_list[index][1]%100 == 3:
door_relay = 'Door3Drivertime=' + str(relay_time)
p_items = create_string_buffer(str.encode(door_relay))
ret = commpro.SetDeviceParam(hcommpro, p_items)
if ret < 0:
wx.MessageBox('Error during communications (door relay)...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
if term_list[index][1]%100 == 4:
door_relay = 'Door4Drivertime=' + str(relay_time)
p_items = create_string_buffer(str.encode(door_relay))
ret = commpro.SetDeviceParam(hcommpro, p_items)
if ret < 0:
wx.MessageBox('Error during communications (door relay)...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
p_data = create_string_buffer(str.encode(""))
p_table = create_string_buffer(str.encode("userauthorize"))
ret = commpro.DeleteDeviceData(hcommpro,p_table,p_data,"")
p_table = create_string_buffer(str.encode("user"))
ret = commpro.DeleteDeviceData(hcommpro,p_table,p_data,"")
p_table = create_string_buffer(str.encode("timezone"))
dialog = wx.ProgressDialog("Progress Information", ("Sending Settings to Inbio = " + self.terminal_ip+"."), progressMax)
counter = 0
for index in range(len(access_list)):
ret = commpro.SetDeviceData(hcommpro,p_table,create_string_buffer(str.encode(access_list[index])),"")
if ret < 0:
wx.MessageBox('Error during communications...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
counter +=1
dialog.Update(counter)
p_table = create_string_buffer(str.encode("user"))
for index in range(len(personnel_list)):
ret = commpro.SetDeviceData(hcommpro,p_table,create_string_buffer(str.encode(personnel_list[index])),"")
if ret < 0:
dialog.Destroy()
wx.MessageBox('Error during communications...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
counter +=1
dialog.Update(counter)
p_table = create_string_buffer(str.encode("userauthorize"))
for index in range(len(personnel_auth_list)):
ret = commpro.SetDeviceData(hcommpro,p_table,create_string_buffer(str.encode(personnel_auth_list[index])),"")
if ret < 0:
dialog.Destroy()
wx.MessageBox('Error during communications...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
counter +=1
dialog.Update(counter)
#finger part
p_table = create_string_buffer(str.encode("templatev10"))
for index in range(len(finger_template_list)):
ret = commpro.SetDeviceData(hcommpro,p_table,create_string_buffer(str.encode(finger_template_list[index])),"")
if ret < 0:
dialog.Destroy()
wx.MessageBox('Error during communications...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
counter +=1
dialog.Update(counter)
#time, do other params here if you need, make sure set as two way up above if need be
ret = commpro.SetDeviceParam(hcommpro,create_string_buffer(str.encode("DateTime="+str(convert_now_to_int()))))
if ret < 0:
dialog.Destroy()
wx.MessageBox('Error during communications...' + '(' + str(ret) + ')','User Alert')
commpro.Disconnect(hcommpro)
return
counter +=1
dialog.Update(counter)
wx.MessageBox('Send all Settings to Inbio ' + self.terminal_ip + ' was successful.','User Alert')
return
def get_inbio_events():
sqltxt = "SELECT * FROM d_inbio_events"
ret = sqlconns.sql_select_into_list(sqltxt)
if ret == -1: return -1
if len(ret) == 0: return 0
return ret
def send_events(terminal_id, ip_address, event_list):
function = inspect.stack()[0][3]
#send each command seperately in case we have hundreds, you wil be connecting to this terminal, if comms error set completed to 2 on all events
constr = create_string_buffer(
str.encode('protocol=TCP,ipaddress=' + ip_address +
',port=4370,timeout=' + str(gl.COMM_TIMEOUT) + ',passwd='))
commpro = windll.LoadLibrary("plcommpro.dll")
hcommpro = commpro.Connect(constr)
if hcommpro == 0:
commpro.Disconnect(hcommpro)
no_connection_error(ip_address)
ret = completed_failure("")
if ret == -1: return -1
return 0
#do timezone build
timezone_string = f.fetch_timezone_string()
if timezone_string == -1:
commpro.Disconnect(hcommpro)
return -1
p_table = create_string_buffer(str.encode("timezone"))
for index in range(len(timezone_string)):
ret = commpro.SetDeviceData(
hcommpro, p_table,
create_string_buffer(str.encode(timezone_string[index])), "")
if ret < 0:
ret_error(ip_address, ret, function)
commpro.Disconnect(hcommpro)
return 0
term_list = get_doors_from_ip(ip_address)
if term_list == -1:
commpro.Disconnect(hcommpro)
return -1
for index in range(len(event_list)):
p_authorize = create_string_buffer(str.encode("userauthorize"))
p_data = create_string_buffer(str.encode("Pin=" +
event_list[index][1]))
ret = commpro.DeleteDeviceData(hcommpro, p_authorize, p_data, "")
if ret < 0:
commpro.Disconnect(hcommpro)
ret_error(ip_address, ret, function)
ret = completed_failure(str(event_list[index][1]))
if ret == -1: return 0
p_user = create_string_buffer(str.encode("user"))
p_data = create_string_buffer(str.encode("Pin=" +
event_list[index][1]))
ret = commpro.DeleteDeviceData(hcommpro, p_user, p_data, "")
if ret < 0:
commpro.Disconnect(hcommpro)
ret_error(ip_address, ret, function)
ret = completed_failure(str(event_list[index][1]))
if ret == -1:
return 0
p_fingers = create_string_buffer(str.encode("templatev10"))
ret = commpro.DeleteDeviceData(hcommpro, p_fingers, p_data, "")
if ret < 0:
commpro.Disconnect(hcommpro)
ret_error(ip_address, ret, function)
ret = completed_failure(str(event_list[index][1]))
if ret == -1:
return 0
for xx in range(len(term_list)):
sql_text = """SELECT temployee.employee_id, temployee.badge, temployee.date_started_with_company, temployee.badge_activation, temployee.badge_expiry, tterminal_policy.data
FROM temployee INNER JOIN
temployee_status ON temployee.employee_status_id = temployee_status.employee_status_id INNER JOIN
tterminal_policy ON temployee.terminal_policy_id = tterminal_policy.terminal_policy_id
WHERE temployee_status.exclude_from_calculation <> 1 AND
temployee.employee_id = """ + event_list[index][1] + """ AND (
data like '%TerminalAssignment\AccessControl\optAllYes,True%'"""
sql_text += """ OR data like """ + "'%TerminalAssignment\\AccessControl\\Terminal\\" + str(
term_list[xx][0]) + ",True%'"
sql_text += ")"
employee_list = sqlconns.sql_select_into_list(sql_text)
if employee_list == -1:
commpro.Disconnect(hcommpro)
return -1
if len(employee_list) > 0:
p_data = ""
p_data = "Pin=" + str(employee_list[0][0])
if employee_list[0][1] != None:
p_data += """\tCardNo=""" + str(int(employee_list[0][1]))
p_data += "\tStartTime=" + get_start_time(
employee_list[0][2],
employee_list[0][3]) + "\tEndTime=" + get_end_time(
employee_list[0][4])
p_data = create_string_buffer(str.encode(p_data))
ret = commpro.SetDeviceData(hcommpro, p_user, p_data, "")
if ret < 0:
ret_error(ip_address, ret, function)
commpro.Disconnect(hcommpro)
ret = completed_failure(str(event_list[index][1]))
if ret == -1: return -1
return 0
data = employee_list[0][5]
datalist = data.split("""\r""")
for zz in range(len(term_list)):
#check you allowed for this clock
for y in range(len(datalist)):
term_policy_string = """AccessControl\\Terminal\\"""
term_policy_string += str(
term_list[zz][0]) + """,True"""
if term_policy_string in datalist[y]:
door_id = get_door_id(term_list[zz][1])
auth_timezone_id = datalist[y + 1]
auth_timezone_id = auth_timezone_id[-1]
p_authorize = create_string_buffer(
str.encode("userauthorize"))
if int(auth_timezone_id) > 0:
p_data = ("Pin=" + str(employee_list[0][0]) +
"\tAuthorizeTimezoneId=" +
auth_timezone_id +
"\tAuthorizeDoorId=" + str(door_id))
print(p_data)
p_data = create_string_buffer(
str.encode(p_data))
ret = commpro.SetDeviceData(
hcommpro, p_authorize, p_data, "")
if ret < 0:
ret_error(ip_address, ret, function)
commpro.Disconnect(hcommpro)
ret = completed_failure(
str(event_list[index][1]))
if ret == -1: return -1
return 0
employee_fingers = sqlconns.sql_select_into_list(
"SELECT size,fid,tmp from d_iface_finger WHERE employee_id = "
+ str(employee_list[0][0]))
if employee_fingers == -1:
ret_error(ip_address, ret, function)
commpro.Disconnect(hcommpro)
ret = completed_failure(
str(event_list[index][1]))
if ret == -1: return -1
return 0
p_fingers = create_string_buffer(
str.encode("templatev10"))
emp_pin = create_string_buffer(
str.encode('Pin=' +
str(employee_list[0][0])))
for z in range(len(employee_fingers)):
p_data = 'Size=0\tUID=#\tPin='+str(employee_list[0][0])+'\tFingerID='+str(employee_fingers[z][1])+\
'\tValid=1\tTemplate='+str(employee_fingers[z][2])+'\tResverd=\tEndTag='
p_data = create_string_buffer(
str.encode(p_data))
ret = commpro.SetDeviceData(
hcommpro, p_fingers, p_data, "")
if ret < 0:
ret_error(ip_address, ret, function)
commpro.Disconnect(hcommpro)
ret = completed_failure(
str(event_list[index][1]))
if ret == -1: return -1
return 0
commpro.Disconnect(hcommpro)
def refreshTable(self):
#global DATA_LIST
DATA_LIST = ['None', 'None', '0', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None']
# DATA_LIST = self.get_employee_details()
#**********************************************************
tx = "SELECT "
if SQL_TOP != '': tx += SQL_TOP + ' '
tx += "last_name, first_name, employee_id from temployee "
tx += EMPLOYEE_SQL
ret = sqlconns.sql_select_into_list(tx)
if ret == -1:
DATA_LIST = ['None', 'None', '0', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None', 'None']
else:
DATA_LIST = [x for x in ret]
for n in range(len(DATA_LIST)):
DATA_LIST[n] = DATA_LIST[n] = [x for x in DATA_LIST[n]]
for y in range(12):
DATA_LIST[n].append('')
for n in range(len(DATA_LIST)):
for y in range(12):
DATA_LIST[n][y + 3] = ''
sql_get_swipes_tx = 'SELECT TOP (12) twork_swipe.date_and_time from twork INNER JOIN twork_swipe ON twork.work_id = twork_swipe.work_id ' \
' WHERE twork.employee_id = ' + str(
DATA_LIST[n][2]) + ' AND twork.[type] = 1000 and twork.date_and_time = ' + f.get_sql_date(
datetime.now(), "yyyy-mm-dd") + ' ORDER BY twork_swipe.date_and_time ASC'
swipes = sqlconns.sql_select_into_list(sql_get_swipes_tx)
if swipes != -1 and len(swipes) > 0:
for y in range(len(swipes)):
DATA_LIST[n][y + 3] = swipes[y][0]
#************************************************************
self.tableWidget.verticalHeader().setVisible(False)
self.tableWidget.setHorizontalHeaderLabels([' LastName ',' FirstName ',' IN ',' OUT ',' IN ', ' OUT ',' IN ',' OUT ',' IN ',' OUT ',
' IN ', ' OUT ', ' IN ', ' OUT ',
' Total '
])
for n in range(14):
self.tableWidget.horizontalHeaderItem(n).setTextAlignment(Qt.AlignLeft)
rowcount = 0
if len(DATA_LIST) == 1:
rowcount = 1
else:
rowcount = int((len(DATA_LIST)+1))
self.tableWidget.setRowCount(rowcount)
for n in range(len(DATA_LIST)):
row_number = int((n))
if n %2 ==0:
column ='left'
else:
column = 'left'
swipes_count = 0
for y in range(15):
yy=y
if yy>2: yy -= 1
if y != 2 :
if y >2 and DATA_LIST[n][y] != 'None' and DATA_LIST[n][y] != '':
field = f.convert_sql_date(DATA_LIST[n][y],"hh:mm")
swipes_count+=1
else:
field = str(DATA_LIST[n][y])
self.tableWidget.setItem(row_number, yy, QTableWidgetItem(field))
#green
r=OUT_RED
g=OUT_GREEN
b=OUT_BLUE
if swipes_count % 2 !=0:
# orange
r = IN_RED
g = IN_GREEN
b = IN_BLUE
#get total hours for display if there are clockings
if swipes_count > 0:
display_emp_total = self.get_total_time(n,DATA_LIST)
#self.tableWidget.setItem(row_number, 14, QTableWidgetItem(display_emp_total))
else:
null_time = '0:00'
# self.tableWidget.setItem(row_number, 14, QTableWidgetItem(null_time))
for col in range(14):
actual_col = col
if self.tableWidget.item(row_number, actual_col)!=None: self.tableWidget.item(row_number, actual_col).setBackground(QtGui.QColor(r,g,b))
stylesheet = "QHeaderView::section{Background-color:rgb(117,146,156);\
border-radius:40px;}"
self.tableWidget.setStyleSheet(stylesheet)
self.tableWidget.resizeColumnsToContents()
del DATA_LIST
self.restart()