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)