def insert_booking(data, terminal_id, sn, configuration, stamp):
list = data.split("\t")
if list[0] == '': return 1
emp_id = int(list[0])
booking = list[1]
tx = "INSERT INTO d_iface_att (stamp,emp_id,date_and_time,sn)"\
" VALUES ('" + stamp + "'," + str(emp_id) + ",'" + booking + "','" + sn + "')"
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
dte = f.iface_string_to_date_format(booking)
if configuration == ACCESS_TERMINAL:
booking = f.get_sql_date(dte, "yyyy-mm-dd hh:mm:ss")
tx = "INSERT INTO taccess_archive (user_id,employee_id,terminal_id,date_and_time,flag,badge)"\
" VALUES (0," + str(emp_id) + "," + str(terminal_id) + "," + booking + ",1,0)"
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
elif configuration == ATTENDANCE_TERMINAL:
booking = f.get_sql_date(dte, "yyyy-mm-dd hh:mm")
tx = "INSERT INTO twork_unprocessed (employee_id,terminal_id,date_and_time,[type],flag,[key],memo,authorisation,authorisation_finalised,source)"\
" VALUES (" + str(emp_id) + "," + str(terminal_id) + "," + booking + ",1000,0,0,'',3,1,0)"
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
tx = tx.replace("twork_unprocessed", "twork_unprocessed_archive")
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
else:
return -1
date_now = f.get_sql_date(datetime.now(), "yyyy-mm-dd hh:mm:ss")
tx = "UPDATE d_iface_stamps SET stamp = " + stamp + ",date_added = " + date_now + ",sn = '" + str(sn) + "' WHERE table_name like 'att_stamp' AND terminal_id = " + str(terminal_id) + ""\
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_stamps(table_name,stamp,terminal_id,date_added,sn) VALUES ('att_stamp','" + stamp + "'," + str(terminal_id) + "," + \
date_now + ",'" + str(sn) + "')"
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
return 1
def insert_booking(empid, clock_num, dte):
#if person not found flag for update and dont save
ret = sqlconns.sql_select_single_field(
'SELECT COUNT(*) as FOO from temployee WHERE employee_id = ' +
str(empid))
if sqlconns.sql_select_single_field(
'SELECT COUNT(*) as FOO from temployee WHERE employee_id = ' +
str(empid)) == "0":
ret = sqlconns.sql_command(
'INSERT into d_zpad_events (employee_id) VALUES (?)', empid)
terminal_id = sqlconns.sql_select_single_field(
"SELECT TOP 1 terminal_id FROM tterminal WHERE number = " + clock_num)
if terminal_id == "": terminal_id = "0"
if terminal_id == -1: return -1
if terminal_id != "0":
ret = sqlconns.sql_command(
"UPDATE tterminal SET poll_success = ? WHERE number = ?",
datetime.datetime.now(), clock_num)
if ret == -1: return
booking = "'" + dte + "'"
flag = 0
tx = "INSERT INTO twork_unprocessed (employee_id,terminal_id,date_and_time,[type],flag,[key],memo,authorisation,authorisation_finalised,source)" \
" VALUES (" + str(empid) + "," + str(terminal_id) + "," + booking + ",1000," + str(flag) + ",0,'',3,1,0)"
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
tx = tx.replace("twork_unprocessed", "twork_unprocessed_archive")
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
return 0
def update_tevent_update(empid):
tx = "IF (SELECT COUNT(*) FROM d_inbio_events WHERE [key] = '" + str(empid)+ "')= 0" \
" INSERT INTO d_inbio_events" \
" ([key]) VALUES" \
" ('"+str(empid)+"')"
if INBIO_USED:
ret = sqlconns.sql_command(tx)
tx = "IF (SELECT COUNT(*) FROM d_iface_events WHERE employee_id = " + str(empid)+ ")= 0" \
" INSERT INTO d_iface_events" \
" (employee_id) VALUES" \
" ("+str(empid)+")"
ret = sqlconns.sql_command(tx)
return ret
def onbutton_start_service(self,event):
sqlconns.sql_command("UPDATE d_inbio_misc SET value = 0 WHERE property like 'paused'")
progressMax = 7
dialog = wx.ProgressDialog("Progress Information", ("Starting Inbio Comunications Service."), progressMax)
keepGoing = True
count = 0
while keepGoing and count < progressMax:
count = count + 1
wx.Sleep(1)
keepGoing = dialog.Update(count)
dialog.Destroy()
self.button_start_service.Enable(False)
self.button_stop_service.Enable(True)
def create_inbio_sql_tables(self, event):
ret = wx.MessageDialog(None,'Do you wish to create Inbio Sql Tables? (Ensure not already created)', 'User Option', wx.YES_NO)
ret1 = ret.ShowModal()
if ret1 == wx.ID_NO:
ret.Destroy()
return -1
if ret1 == wx.ID_YES:
sql = """CREATE TABLE [dbo].[d_inbio_misc](
[index] [int] IDENTITY(1,1) NOT NULL,
[property] [nvarchar](max) NULL,
[value] [nvarchar](max) NULL
) ON [PRIMARY]"""
sqlret = sqlconns.sql_command(sql)
if sqlret == -1:
wx.MessageBox('There was an issue creating Inbio Tables, please investigate.','User Alert')
return -1
else:
sql = '''CREATE TABLE [dbo].[d_inbio_events](
[index] [int] IDENTITY(1,1) NOT NULL,
[key] [nvarchar](max) NULL,
[unhandled_terminals] [nvarchar](max) NULL,
[last_attempt] [datetime] NULL,
[completion_date] [datetime] NULL,
[completed] [int] NULL
) ON [PRIMARY]'''
sqlret = sqlconns.sql_command(sql)
if sqlret == -1:
wx.MessageBox('There was an issue creating Inbio Tables, please investigate.','User Alert')
return -1
sql = '''CREATE TRIGGER tevent_to_inbio_event
ON tevent_update
for insert,update
AS
if (SELECT COUNT (*) FROM inserted WHERE [event_update_command_ref_id] =1) > 0
INSERT INTO d_inbio_events([key],[completed])
values ((SELECT [find_key] from inserted),0)'''
sqlret = sqlconns.sql_command(sql)
wx.MessageBox('Inbio Tables Created successfully','User Information')
#disable create menu option
menubar = self.GetMenuBar()
menubar.Enable(ID_SIMPLE, False)
#remove warning information from warning panel
self.warning_label.SetLabel("")
self.warning_label1.SetLabel("")
self.button_stop_service.Enable(True)
return 0
def bAttFound(sn, emp_id, booking):
tx = "select TOP 1 [d_iface_att_id] from d_iface_att WHERE sn = '" + sn + "' AND emp_id = " + str(
emp_id) + " AND date_and_time = " + booking
ret = sqlconns.sql_select_single_field(tx)
if ret == "":
return False
if int(ret) > 0:
tx = "If ("\
"SELECT count(*) from d_iface_att" \
" where d_iface_att_id = "+str(int(ret))+ " and repoll_count is null) > 0"\
" UPDATE d_iface_att"\
" SET repoll_count = 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_att_id ="+ str(int(ret))+ ""\
" ELSE"\
" UPDATE d_iface_att"\
" SET repoll_count = repoll_count + 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_att_id = "+ str(int(ret))
ret = sqlconns.sql_command(tx)
return True
else:
return False
def update_tevent_update(empid):
date_now = f.get_sql_date(datetime.now(),"yyyy-mm-dd hh:mm:ss")
tx = "IF (SELECT COUNT(*) FROM d_iface_events WHERE employee_id = " + str(empid)+ ")= 0" \
" INSERT INTO d_iface_events" \
" (employee_id) VALUES" \
" ("+str(empid)+")"
return sqlconns.sql_command(tx)
def completed_failure(empid):
ret = 0
sqltxt = "UPDATE d_inbio_events SET completed = 2"
if str(empid) != "":
sqltxt += """ WHERE [key] = '""" + str(empid) + """'"""
ret = sqlconns.sql_command(sqltxt)
return ret
def update_roll_call_table(empid,reader,reader_direction,reader_description,terminal_id,zone_id,dte):
ret = sqlconns.sql_command("UPDATE temployee_roll_call SET reader = ?,reader_direction = ?,reader_description = ?,terminal_id = ?,zone_id = ?,date_and_time = ? WHERE employee_id = ?"\
" IF @@ROWCOUNT=0" \
" INSERT INTO temployee_roll_call(employee_id,reader,reader_direction,reader_description,terminal_id,zone_id,date_and_time) VALUES (?,?,?,?,?,?,?)",
reader,reader_direction,reader_description,terminal_id,zone_id,dte,
empid,
empid,reader,reader_direction,reader_description,terminal_id,zone_id,dte)
return ret
def save_op_stamp(stamp, terminal_id, sn):
date_now = f.get_sql_date(datetime.now(), "yyyy-mm-dd hh:mm:ss")
tx = "UPDATE d_iface_stamps SET stamp = " + stamp + ",date_added = " + date_now + ",sn = '" + sn + "' WHERE table_name like 'op_stamp' AND terminal_id = " + str(terminal_id) + ""\
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_stamps(table_name,stamp,terminal_id,date_added,sn) VALUES ('op_stamp','" + stamp + "','" + str(terminal_id) + "'," + date_now + ",'" + sn + "')"
ret = sqlconns.sql_command(tx)
if ret == -1: return -1
return
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 insert_sql_transaction(empid, dte, event, door_id, terminal_id):
terminal_id_2 = get_terminal_id(door_id, terminal_id)
#return 0 if no door found in software, preventative measure
if terminal_id_2 == 0:
return 0
sqltxt = """IF (SELECT COUNT (*) FROM taccess_archive WHERE employee_id = """ + empid + """ AND date_and_time = """ + dte + """ AND flag = """ + event + """) =0
INSERT INTO taccess_archive (employee_id,terminal_id,date_and_time,flag) VALUES (""" + empid + """,""" + terminal_id_2 + """,""" + dte + """,""" + event + ")" ""
ret = sqlconns.sql_command(sqltxt)
return ret
def update_tevent_update(empid):
tx = "IF (SELECT COUNT(*) FROM d_inbio_events WHERE [key] = '" + str(empid)+ "')= 0" \
" INSERT INTO d_inbio_events" \
" ([key]) VALUES" \
" ('"+str(empid)+"')"
print(tx)
print("INBIO BOOLEAN = ", INBIO_USED)
if INBIO_USED:
print("doing inbio event")
ret = sqlconns.sql_command(tx)
tx = "IF (SELECT COUNT(*) FROM d_iface_events WHERE employee_id = " + str(empid)+ ")= 0" \
" INSERT INTO d_iface_events" \
" (employee_id) VALUES" \
" ("+str(empid)+")"
print(tx)
ret = sqlconns.sql_command(tx)
print(ret)
return ret
def post(self):
data = self.request.body
data = data.decode("utf-8")
print(data)
tx = """Insert into d_xml_receiver (xml_data, date_added) values (?, ?)"""
print(tx)
ret = sqlconns.sql_command(tx, data,datetime.now())
print(ret)
self.write("OK")
def save_op_stamp(stamp,terminal_id):
tx = "UPDATE d_iface_stamps SET stamp = " + stamp + " WHERE table_name like 'op_stamp' AND terminal_id = " + str(terminal_id) + ""\
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_stamps(table_name,stamp,terminal_id) VALUES ('op_stamp','" + stamp + "'," + str(terminal_id) + ")"
#turn these on when ready
ret = sqlconns.sql_command(tx)
if ret==-1: return -1
return
def save_op_log(xx, terminal_id, sn):
list = xx.split("\t")
if list[0] == "OPLOG 3":
dte = list[2]
#push button
if list[3] == "53":
tx = "INSERT INTO taccess_archive (user_id,employee_id,terminal_id,date_and_time,flag,badge) VALUES (0,0,?,?,?,0)"
ret = sqlconns.sql_command(tx, terminal_id, dte, 8)
if ret == -1: return -1
return
def get(self):
list = self.request.uri.split("?SN=")
list2 = list[1].split("&")
sn = list2[0]
uface = False #TODO this line is legacy. can it be removed
tx = "SELECT TOP 1 notepad from tterminal WHERE ip_address = '" + sn + "'"
notepad_options = str.lower(sqlconns.sql_select_single_field(tx))
if "uface" in str.lower(notepad_options):
uface = True #TODO this line is legacy, can it be removed
data_list = get_commands_list(sn)
if data_list == -1:
return
ret = sqlconns.sql_command(
"UPDATE tterminal SET poll_success = ? WHERE ip_address = ?",
datetime.now(), sn)
if ret == -1: return
counter = 0
reboot = 0
clear_data = 0
data = ""
#used to do this for uface terminals now do for all
if len(data_list) == 0:
ok = "OK"
self.write(ok)
for index in range(len(data_list)):
if data_list[index][1] == "REBOOT":
reboot = 1
elif data_list[index][1] == "CLEAR DATA":
clear_data = 1
else:
counter += 1
if sys.getsizeof(data +
("C:ID" + str(data_list[index][0]) + ":" +
data_list[index][1] + "\r\n")) > 39000:
break
data = data + "C:ID" + str(
data_list[index][0]) + ":" + data_list[index][1] + "\r\n"
ret = update_commands_to_sent_status(data_list[index][0])
if ret == -1: return
if data != "":
self.write(data)
dte = date_time_string(sn)
bDateHeader = False # are we sending the date header?
if dte != None: bDateHeader = True
self.device_headers(dte, bDateHeader)
return
if clear_data == 1:
self.write("C:ID" + str(data_list[index][0]) + ":CLEAR DATA\r\n")
self.write(data)
if reboot == 1:
self.write("C:ID" + str(data_list[index][0]) + ":REBOOT\r\n")
dte = date_time_string(sn)
bDateHeader = False # are we sending the date header?
if dte != None: bDateHeader = True
self.device_headers(dte, bDateHeader)
def insert_booking(data,terminal_id,sn,configuration,stamp):
list = data.split("\t")
if list[0]=='': return 1
emp_id = int(list[0])
booking = list[1]
#tx = "SELECT COUNT(*) as FOO from d_iface_att WHERE emp_id = " + str(emp_id) + " AND date_and_time='" + booking +"'"
#ret = int(sqlconns.sql_select_single_field(tx))
#if ret == -1: return -1
#no longer quitting if the clocking already exists
#if ret>0:return 1
#dont need this as doing at the bottom
#tx = "INSERT INTO d_iface_att (stamp,emp_id,date_and_time,sn)"\
# " VALUES ('" + stamp + "'," + str(emp_id) + ",'" + booking + "','" + sn + "')"
#ret = sqlconns.sql_command(tx)
#if ret==-1: return -1
dte = f.iface_string_to_date_format(booking)
if configuration == ACCESS_TERMINAL:
booking = f.get_sql_date(dte,"yyyy-mm-dd hh:mm:ss")
tx = "INSERT INTO taccess_archive (user_id,employee_id,terminal_id,date_and_time,flag,badge)"\
" VALUES (0," + str(emp_id) + "," + str(terminal_id) + "," + booking + ",1,0)"
ret = sqlconns.sql_command(tx)
if ret==-1: return -1
elif configuration == ATTENDANCE_TERMINAL:
booking = f.get_sql_date(dte,"yyyy-mm-dd hh:mm")
tx = "INSERT INTO twork_unprocessed (employee_id,terminal_id,date_and_time,[type],flag,[key],memo,authorisation,authorisation_finalised,source)"\
" VALUES (" + str(emp_id) + "," + str(terminal_id) + "," + booking + ",1000,0,0,'',3,1,0)"
ret = sqlconns.sql_command(tx)
if ret==-1: return -1
tx = tx.replace("twork_unprocessed", "twork_unprocessed_archive")
ret = sqlconns.sql_command(tx)
if ret==-1: return -1
else:
return -1
tx = "UPDATE d_iface_stamps SET stamp = " + stamp + " WHERE table_name like 'att_stamp' AND terminal_id = " + str(terminal_id) + ""\
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_stamps(table_name,stamp,terminal_id) VALUES ('att_stamp','" + stamp + "'," + str(terminal_id) + ")"
ret = sqlconns.sql_command(tx)
if ret==-1: return -1
return 1
def save_user_finger(xx, terminal_id):
list = xx.split("\t")
user_id = list[0].replace("FP PIN=", "")
if user_id == "": return 0
fid = list[1].replace("FID=", "")
size = list[2].replace("Size=", "")
valid = list[3].replace("Valid=", "")
tmp = list[4].replace("TMP=", "")
#clear old templates
tx = "DELETE from d_iface_finger WHERE employee_id = " + str(
user_id) + " AND date_added < dateadd(minute,-" + str(
FINGER_DELETION_MINS) + ",getdate())"
sqlconns.sql_command(tx)
date_now = f.get_sql_date(datetime.now(), "yyyy-mm-dd hh:mm:ss")
tx = "UPDATE d_iface_finger SET size = '" + size + "',[valid]="+valid+", [tmp]='" + tmp + "',date_added="+date_now+",terminal_id="+str(terminal_id)+" WHERE employee_id =" + user_id + " AND fid="+fid+"" \
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_finger(employee_id,size,tmp,date_added,[valid],fid,terminal_id) VALUES ('"+user_id+"','"+size+"','"+tmp+"',"+date_now+","+valid+","+fid+","+str(terminal_id)+")"
ret = sqlconns.sql_command(tx)
if ret == -1: return ret
ret = update_tevent_update(user_id)
return ret
def save_user_finger(xx, terminal_id):
list = xx.split("\t")
user_id = list[0].replace("FP PIN=", "")
if user_id == "": return 0
fid = list[1].replace("FID=", "")
size = list[2].replace("Size=", "")
valid = list[3].replace("Valid=", "")
tmp = list[4].replace("TMP=", "")
#check if exists
tx = "Select top 1 [d_iface_finger_id] from d_iface_finger WHERE employee_id =" + user_id + " AND fid=" + fid + " AND tmp = '" + tmp + "'"
ret = sqlconns.sql_select_single_field(tx)
if ret != "" and int(ret) > 0:
tx = "If ("\
"SELECT count(*) from d_iface_finger"\
" where d_iface_finger_id = "+str(int(ret))+ " and repoll_count is null) > 0"\
" UPDATE d_iface_finger"\
" SET repoll_count = 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_finger_id ="+ str(int(ret))+ ""\
" ELSE"\
" UPDATE d_iface_finger"\
" SET repoll_count = repoll_count + 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_finger_id = "+ str(int(ret))
ret = sqlconns.sql_command(tx)
return 1
#clear old templates
tx = "DELETE from d_iface_finger WHERE employee_id = " + str(
user_id) + " AND date_added < dateadd(minute,-" + str(
FINGER_DELETION_MINS) + ",getdate())"
sqlconns.sql_command(tx)
date_now = f.get_sql_date(datetime.now(), "yyyy-mm-dd hh:mm:ss")
tx = "UPDATE d_iface_finger SET size = '" + size + "',[valid]="+valid+", [tmp]='" + tmp + "',date_added="+date_now+",terminal_id="+str(terminal_id)+" WHERE employee_id =" + user_id + " AND fid="+fid+"" \
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_finger(employee_id,size,tmp,date_added,[valid],fid,terminal_id) VALUES ('"+user_id+"','"+size+"','"+tmp+"',"+date_now+","+valid+","+fid+","+str(terminal_id)+")"
ret = sqlconns.sql_command(tx)
if ret == -1: return ret
ret = update_tevent_update(user_id)
return ret
def post(self):
data = self.request.body
data = data.decode("utf-8")
#print(data)
data = str.replace(data, ' encoding="UTF-8"', '')
tx = """Insert into d_xml_receiver (xml_data, date_added, date_and_time_added) values (?, ?, ?)"""
ret = sqlconns.sql_command(tx, data, datetime.now(), datetime.now())
#self.write("OK")
self.set_status(200)
def do_GET(self):
list = self.path.split("?SN=")
list2 = list[1].split("&")
sn = list2[0]
ret = sqlconns.sql_command("UPDATE tterminal SET poll_success = ? WHERE ip_address = ?",datetime.now(),sn)
if ret==-1: return
if "cdata" in self.path:
#power on send stamps and options
power_on_getrequest = create_string_buffer(str.encode(build_power_on_get_request(sn)))
if power_on_getrequest == "": return
self.send_response(200)
self.do_headers()
self.wfile.write(power_on_getrequest)
return
elif "getrequest" in self.path:
#send commands from command list
self.send_response(200)
self.do_headers()
#remember to change to only send unsent stuff
data_list = get_commands_list(sn)
if data_list==-1: return
counter = 0
reboot=0
clear_data=0
data=""
for index in range(len(data_list)):
if data_list[index][1] == "REBOOT":
reboot = 1
elif data_list[index][1] == "CLEAR DATA":
clear_data = 1
else:
counter+=1
if sys.getsizeof(data+("C:ID"+str(data_list[index][0])+":"+data_list[index][1]+"\r\n"))>39000:
break
data = data + "C:ID"+str(data_list[index][0])+":"+data_list[index][1]+"\r\n"
ret = update_commands_to_sent_status(data_list[index][0])
if ret==-1:return
if data!="":
data = create_string_buffer(str.encode(data))
self.wfile.write(data)
return
if clear_data==1:
data = create_string_buffer(str.encode("C:ID"+str(data_list[index][0])+":CLEAR DATA\r\n"))
self.wfile.write(data)
if reboot==1:
data = create_string_buffer(str.encode("C:ID"+str(data_list[index][0])+":REBOOT\r\n"))
self.wfile.write(data)
return
return
def save_user_face(xx,terminal_id):
list = xx.split("\t")
user_id = list[0].replace("FACE PIN=","")
if user_id=="":return 0
fid = list[1].replace("FID=","")
size = list[2].replace("SIZE=","")
valid = list[3].replace("VALID=","")
tmp = list[4].replace("TMP=","")
date_now = f.get_sql_date(datetime.now(),"yyyy-mm-dd hh:mm:ss")
#check if already there
tx = "SELECT TOP 1 d_iface_face_id from d_iface_tmp WHERE employee_id ="+ user_id+ " AND fid="+fid+" AND [tmp] ='"+ tmp+ "'"
ret = sqlconns.sql_select_single_field(tx)
if ret!= "" and int(ret) > 0:
tx = "If ("\
"SELECT count(*) from d_iface_tmp"\
" where d_iface_face_id = " + str(int(ret)) + " and repoll_count is null) > 0"\
" UPDATE d_iface_tmp"\
" SET repoll_count = 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_face_id =" + str(int(ret)) + ""\
" ELSE"\
" UPDATE d_iface_tmp"\
" SET repoll_count = repoll_count + 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_Face_id = " + str(int(ret))
ret = sqlconns.sql_command(tx)
return 1
tx = "UPDATE d_iface_tmp SET size = '" + size + "',[valid]="+valid+", [tmp]='" + tmp + "',date_added="+date_now+",terminal_id="+str(terminal_id)+" WHERE employee_id =" + user_id + " AND fid="+fid+"" \
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_tmp(employee_id,size,tmp,date_added,[valid],fid,terminal_id) VALUES ('"+user_id+"','"+size+"','"+tmp+"',"+date_now+","+valid+","+fid+","+str(terminal_id)+")"
ret = sqlconns.sql_command(tx)
if ret==-1: return ret
#if fid = 11 then check recently got 12 from this terminal that are new and put entry into tevent_update AND clear new flag
if fid=="11":
ret = update_tevent_update(user_id)
return ret
def get(self):
list = self.request.uri.split("?SN=")
list2 = list[1].split("&")
sn = list2[0]
ret = sqlconns.sql_command(
"UPDATE tterminal SET poll_success = ? WHERE ip_address = ?",
datetime.now(), sn)
if ret == -1: return
self.set_status(200)
self.set_header("content-type", "text/plain")
self.set_header("date", date_time_string(sn))
#power on send stamps and options
power_on_getrequest = build_power_on_get_request(sn)
if power_on_getrequest == "": return
self.write(power_on_getrequest)
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 save_user_photo(xx,terminal_id):
list = xx.split("\t")
user_id = list[0].replace("USERPIC PIN=","")
file_name = list[1].replace("FileName=","")
size = list[2].replace("Size=","")
content = list[3].replace("Content=","")
date_now = f.get_sql_date(datetime.now(),"yyyy-mm-dd hh:mm:ss")
#check exixts and dont write
tx = "Select top 1 [d_iface_photo_id] from d_iface_photo WHERE employee_id =" + user_id + " AND content = '"+ content + "'"
ret = sqlconns.sql_select_single_field(tx)
if ret != "" and int(ret) > 0:
tx = "If ("\
"SELECT count(*) from d_iface_photo"\
" where d_iface_photo_id = "+str(int(ret))+ " and repoll_count is null) > 0"\
" UPDATE d_iface_photo"\
" SET repoll_count = 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_photo_id ="+ str(int(ret))+ ""\
" ELSE"\
" UPDATE d_iface_photo"\
" SET repoll_count = repoll_count + 1,"\
" repoll_date = getdate()"\
" WHERE d_iface_photo_id = "+ str(int(ret))
ret = sqlconns.sql_command(tx)
return 1
#photo does not exist, carry on
tx = "UPDATE d_iface_photo SET size = '" + size + "', content='" + content + "',date_added="+date_now+",terminal_id="+str(terminal_id)+",new=1 WHERE employee_id =" + user_id + "" \
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_photo(employee_id,file_name,size,content,date_added,terminal_id,new) VALUES ('"+user_id+"','"+file_name+"','"+size+"','"+content+"',"+date_now+","+str(terminal_id)+",1)"
ret = sqlconns.sql_command(tx)
if ret==0:
if gl.face_to_personnel==True:
content = base64.b64decode(content)
tx = "UPDATE temployee SET photo = ? WHERE employee_id = ?"
ret = sqlconns.sql_command_args(tx,content,user_id)
return ret
def onbutton_stop_service(self,event):
sqltext = """UPDATE d_inbio_misc SET value = 1 WHERE property like 'paused'
IF @@ROWCOUNT=0
INSERT INTO d_inbio_misc(property,value) VALUES ('paused',1)"""
ret = sqlconns.sql_command(sqltext)
progressMax = 30
dialog = wx.ProgressDialog("Progress Information", ("Pausing Inbio Comunications Service."), progressMax)
keepGoing = True
count = 0
while keepGoing and count < progressMax:
count = count + 1
wx.Sleep(1)
keepGoing = dialog.Update(count)
dialog.Destroy()
self.button_start_service.Enable(True)
self.button_stop_service.Enable(False)
def insert_sql_transaction(empid, dte, event, door_id, terminal_id,
inoutstate):
function = inspect.stack()[0][3]
try:
if inoutstate == "1" and event == 1: event = 2
if inoutstate == "1" and event == 3: event = 4
except Exception as e:
ret_error(str(terminal_id), -1,
function + "," + str(inoutstate) + " " + str(e))
terminal_id_2 = get_terminal_id(door_id, terminal_id)
#return 0 if no door found in software, preventative measure
if terminal_id_2 == 0 or terminal_id_2 == '':
return 0
sqltxt = """IF (SELECT COUNT (*) FROM taccess_archive WHERE employee_id = """ + empid + """ AND date_and_time = """ + dte + """ AND flag = """ + event + """) =0 \
INSERT INTO taccess_archive (employee_id,terminal_id,date_and_time,flag) VALUES (""" + empid + """,""" + terminal_id_2 + """,""" + dte + """,""" + event + ")" ""
ret = sqlconns.sql_command(sqltxt)
return ret
def save_user_face(xx,terminal_id):
list = xx.split("\t")
user_id = list[0].replace("FACE PIN=","")
fid = list[1].replace("FID=","")
size = list[2].replace("SIZE=","")
valid = list[3].replace("VALID=","")
tmp = list[4].replace("TMP=","")
date_now = f.get_sql_date(datetime.now(),"yyyy-mm-dd hh:mm:ss")
tx = "UPDATE d_iface_tmp SET size = '" + size + "',[valid]="+valid+", [tmp]='" + tmp + "',date_added="+date_now+",terminal_id="+str(terminal_id)+" WHERE employee_id =" + user_id + " AND fid="+fid+"" \
" IF @@ROWCOUNT=0" \
" INSERT INTO d_iface_tmp(employee_id,size,tmp,date_added,[valid],fid,terminal_id) VALUES ('"+user_id+"','"+size+"','"+tmp+"',"+date_now+","+valid+","+fid+","+str(terminal_id)+")"
ret = sqlconns.sql_command(tx)
if ret==-1: return ret
#if fid = 11 then check recently got 12 from this terminal that are new and put entry into tevent_update AND clear new flag
if fid=="11":
ret = update_tevent_update(user_id)
return ret
def get(self):
list = self.request.uri.split("?SN=")
list2 = list[1].split("&")
sn = list2[0]
ret = sqlconns.sql_command("UPDATE tterminal SET poll_success = ? WHERE ip_address = ?",datetime.now(),sn)
if ret==-1: return
#record ip address, you may have bother with this if https is ever used
x_real_ip = self.request.headers.get("X-Real-IP")
remote_ip = x_real_ip or self.request.remote_ip
if remote_ip != None: log_ip_address(sn,remote_ip)
#power on send stamps and options
power_on_getrequest = build_power_on_get_request(sn)
if power_on_getrequest != "": self.write(power_on_getrequest)
dte = date_time_string(sn)
bDateHeader = False # are we sending the date header?
#if dte != None: bDateHeader = True do not send dte heaaer in power on
self.device_headers(dte,bDateHeader)
