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] flag = 0 if IFACE_FUNCTION_KEYS == True: if int(list[4]) == 3: flag = 3 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," + 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 if FUNCTION_KEYS == True: if int(list[2]) > 0: tx = "INSERT INTO d_iface_att_event (employee_id,date_and_time,event,handled,terminal_id)"\ " VALUES (" + str(emp_id) + "," + booking + "," + str(list[2]) + ",0," + str(terminal_id) + ")" ret = sqlconns.sql_command(tx) if ret == -1: return -1 if IFACE_FUNCTION_KEYS == True: if int(list[4]) > 0: tx = "INSERT INTO d_iface_att_event (employee_id,date_and_time,event,handled,terminal_id)"\ " VALUES (" + str(emp_id) + "," + booking + "," + str(list[4]) + ",0," + str(terminal_id) + ")" 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 refreshTable(self): global DATA_LIST self.tableWidget.verticalHeader().setVisible(False) self.tableWidget.setHorizontalHeaderLabels([ ' id ', ' Description ', ' Number ', ' SN ', ' Poll Success ', ' Unsent ', ' In Transit ', ' Complete ', ' Att Stamp ', ' Op Stamp ' ]) for n in range(10): self.tableWidget.horizontalHeaderItem(n).setTextAlignment( Qt.AlignLeft) self.tableWidget.setRowCount(len(DATA_LIST)) for n in range(len(DATA_LIST)): for y in range(5): if DATA_LIST[n][y] == None: field = "None" else: if y == 4: field = f.get_sql_date(DATA_LIST[n][y], "dd/mm/yyyy hh:mm:ss") else: field = str(DATA_LIST[n][y]) self.tableWidget.setItem(n, y, QTableWidgetItem(field)) stylesheet = "QHeaderView::section{Background-color:rgb(117,146,156);\ border-radius:40px;}" self.tableWidget.setStyleSheet(stylesheet) self.tableWidget.resizeColumnsToContents() self.show_ids()
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 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 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 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 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 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") 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 = ?" reta = sqlconns.sql_command_args(tx, content, user_id) return ret
def save_op_stamp(stamp,terminal_id,sn): #if stamp is too big then bomb out for safety...do not record stamps than can be from 1970 eg 3300000000 # typical stamp is 601659677, that is 9 digits long #get MAX and MIN stamps from general.ini 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 = '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 + "')" #just replace op_stamp with bad_op_stamp if int(stamp) >= int(MAX_STAMP) or int(stamp) <= int(MIN_STAMP): tx =str.replace(tx, 'op_stamp', 'bad_op_stamp') return ret = sqlconns.sql_command(tx) if ret==-1: return -1 return
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 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 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 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()
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] flag = 0 #if stamp is bad do not save it if int(stamp) >= int(MAX_STAMP) or int(stamp) <= int(MIN_STAMP): 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 = 'bad_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 ('bad_att_stamp','" + stamp + "'," + str( terminal_id) + "," + \ date_now + ",'" + str(sn) + "')" ret = sqlconns.sql_command(tx) if ret == -1: return -1 return 1 #go no further if bad stamp, change on 18012019, this line was not here. #check if in att log table and bail out if need be test_dte = f.iface_string_to_date_format(booking) test_booking = f.get_sql_date(test_dte, "yyyy-mm-dd hh:mm:ss") if int(list[2]) != 100 :#100 is cost centre if bAttFound (sn,emp_id,test_booking): return 1 #if a cost centre clocking then check att_log_table and bail out if need be else: if bAttEventFound(terminal_id, emp_id, test_booking): return 1 if IFACE_FUNCTION_KEYS == True: if int(list[4]) == 3: flag = 3 #backup attendance clocking, may add as an option in future, may add a purge to the build commands script anything older than a year? #this is handled in the application script 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) #DEVICE = ACCESS CONTROL TERMINAL if configuration == ACCESS_TERMINAL: booking = f.get_sql_date(dte,"yyyy-mm-dd hh:mm:ss") #if booking not found then write it 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 #insert rollcall roll_call_enabled,reader_direction,reader_description, zone_id = get_terminal_roll_call_info (terminal_id) if roll_call_enabled == -1: return -1 reader = 1 if roll_call_enabled ==True: ret = update_roll_call_table (emp_id,reader,reader_direction,reader_description,terminal_id,zone_id,dte) if ret ==-1 : return -1 elif configuration == ATTENDANCE_TERMINAL: booking = f.get_sql_date(dte, "yyyy-mm-dd hh:mm") #cost centre clocking = 100 #if code is differentt to 100 then it also needs an attendance entry if int(list[2]) < 100 or int(list[2]) == 255: #255 is standard clocking from untweaked UFACE #this is the ACTUAL ATTENDANCE swipe 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," + str(flag) + ",0,'',3,1,0)" ret = sqlconns.sql_command(tx) if ret==-1: return -1 if ORIGINAL_BOOKINGS: tx = tx.replace("twork_unprocessed", "twork_unprocessed_archive") ret = sqlconns.sql_command(tx) if ret==-1: return -1 if FUNCTION_KEYS == True: #shouldnt be over 100 as this will conflict with other functions #business leave booking on terminal if int(list[2]) > 0 and int(list[2])<100: tx = "INSERT INTO d_iface_att_event (employee_id,date_and_time,event,handled,terminal_id)"\ " VALUES (" + str(emp_id) + "," + booking + "," + str(list[2]) + ",0," + str(terminal_id) + ")" ret = sqlconns.sql_command(tx) if ret==-1: return -1 if IFACE_FUNCTION_KEYS == True: #business leave booking on iface terminal if int(list[4]) > 0: tx = "INSERT INTO d_iface_att_event (employee_id,date_and_time,event,handled,terminal_id)"\ " VALUES (" + str(emp_id) + "," + booking + "," + str(list[4]) + ",0," + str(terminal_id) + ")" ret = sqlconns.sql_command(tx) if ret==-1: return -1 if CC_FUNCTION_KEYS == True: ########## Work codes have been removed, list[4] is now nothing pending ticket from ZK ########## Now use punch key over 100 ########## 100 can be use for Dummy default button # cc function keys if code is greater than 100 then make the booking but other than that ignore it. if int(list[2]) >= 100: tx = "INSERT INTO d_iface_att_event (employee_id,date_and_time,event,handled,terminal_id)" \ " VALUES (" + str(emp_id) + "," + booking + "," + str(list[2]) + ",0," + str( terminal_id) + ")" 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 = '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 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 comm_failure_date_time(terminal_id): sqltxt = """UPDATE tterminal SET poll_failed = """ + f.get_sql_date( datetime.datetime.now(), "yyyy-mm-dd hh:mm:ss") + """ WHERE terminal_id = """ + str(terminal_id) ret = sqlconns.sql_command(sqltxt) return ret
def update_commands_to_sent_status(id): date_now = f.get_sql_date(datetime.now(),"yyyy-mm-dd hh:mm:ss") tx = "UPDATE d_iface_commands SET sent=1,sent_date="+date_now+"WHERE iface_command_id="+str(id) sqlconns.sql_command(tx)