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)
