def work_out_pairs(time1, time2):
if time1 == None or time1 == ' ' or time1 == '': return 0
time1 = str(f.convert_sql_date(time1, "hh:mm"))
if time2 == None or time2 == ' ' or time2 == '':
time2 = str(f.convert_sql_date(datetime.now(), "hh:mm"))
else:
time2 = str(f.convert_sql_date(time2, "hh:mm"))
try:
h1 = str(time1).split(':')
h2 = str(time2).split(':')
if int(h1[0]) > 23: return 0
if int(h2[0]) > 23: return 0
if int(h1[1]) > 59: return 0
if int(h2[1]) > 59: return 0
mins1 = (int(h1[0]) * 60) + int(h1[1])
mins2 = (int(h2[0]) * 60) + int(h2[1])
if mins2 < mins1: mins2 += (24 * 60)
mins1 = mins2 - mins1
return mins1
except:
return 0
#def get_employee_details():
# #global DATA_LIST
# tx = "SELECT "
# if SQL_TOP != '': tx += SQL_TOP + ' '
# tx += "last_name, first_name, employee_id from temployee "
# tx += EMPLOYEE_SQL
ret = sqlconns.sql_select_into_list(tx)
def get_start_time(dte_started, dte_activated):
if dte_activated == None:
return f.convert_sql_date(dte_started, "yyyymmdd")
x = f.convert_sql_date(dte_activated, "yyyymmdd")
y = f.convert_sql_date(dte_started, "yyyymmdd")
if x >= y:
return x
else:
return y
def get_start_time(dte_started,dte_activated):
return "20000101"
base_date = datetime.datetime(2015,1,1)
if dte_activated == None:
dte_activated = base_date
if dte_started == None:
dte_started = base_date
if dte_activated < base_date: dte_activated = base_date
if dte_started < base_date: dte_started = base_date
if dte_activated >= dte_started:
return f.convert_sql_date(dte_activated,'yyyymmdd')
else:
return f.convert_sql_date(dte_started,'yyyymmdd')
def get_terminal_status_list():
terminal_list = sqlconns.sql_select_into_list("SELECT description, ip_address, configuration,poll_success," \
" (SELECT TOP 1 last_ip as lastIp from d_iface_stamps WHERE terminal_id = tterminal.terminal_id)" \
" FROM tterminal LEFT OUTER JOIN" \
" d_iface_stamps ON tterminal.terminal_id = d_iface_stamps.terminal_id" \
" WHERE" \
" configuration in (" + str(ACCESS_TERMINAL) + "," + str(ATTENDANCE_TERMINAL) + ")" \
" ORDER BY configuration, description")
if terminal_list==-1: return ""
tx = ""
for index in range(len(terminal_list)):
tx += '<tr>'
terminal_type = ""
#have included other terminal types but they will not show unless they are in the general.ini as access or attendance terminal config type
if terminal_list[index][2] == 4: terminal_type = 'Access Control'
if terminal_list[index][2] == 10: terminal_type = 'ZK Terminal'
if terminal_list[index][2] == 8: terminal_type = 'Attendance NMD'
if terminal_list[index][2] == 5: terminal_type = 'Hand Punch'
if terminal_list[index][2] == 14: terminal_type = 'Fire Panel'
if str(terminal_list[index][3]) == "None":
tx += '<td>' + terminal_list[index][1] + '</td><td>' + 'No polling received.' + '</td><td>' + str(terminal_list[index][0]) + '</td><td>' + terminal_type + '</td>'
else:
tx += '<td>' + terminal_list[index][1] + '</td><td>' + str(f.convert_sql_date(terminal_list[index][3],'dd/mm/yyyy hh:mm')) + '</td><td>' + str(terminal_list[index][0]) + '</td><td>' + terminal_type + '</td>'
#dummy space for last column
if terminal_list[index][4] != None:
tx += '<td>' + terminal_list[index][4] + '</td>'
else:
tx += '<td>' + '0.0.0.0' + '</td>'
tx += '</tr>'
return tx
def get_terminal_status_list():
terminal_list = sqlconns.sql_select_into_list('SELECT description, ip_address, configuration,poll_success FROM tterminal WHERE \
configuration in (' + str(ACCESS_TERMINAL) + ',' + str(ATTENDANCE_TERMINAL) + ')'\
'ORDER BY configuration, description')
if terminal_list == -1: return ""
tx = ""
for index in range(len(terminal_list)):
terminal_type = ""
if terminal_list[index][2] == 4: terminal_type = 'Access Terminal'
if terminal_list[index][2] == 10: terminal_type = 'ZK Terminal'
if terminal_list[index][2] == 8: terminal_type = 'Attendance Terminal'
if terminal_list[index][2] == 5: terminal_type = 'Hand Punch'
if terminal_list[index][2] == 14: terminal_type = 'Fire Panel'
if str(terminal_list[index][3]) == "None":
tx += terminal_list[index][
1] + ' --- ' + 'No polling received.' + ' --- ' + str(
terminal_list[index][0]) + ' --- ' + terminal_type
else:
tx += terminal_list[index][1] + ' --- ' + str(
f.convert_sql_date(
terminal_list[index][3],
'dd/mm/yyyy hh:mm')) + ' --- ' + str(
terminal_list[index][0]) + ' --- ' + terminal_type
tx += '<br>'
return tx
def work_out_pairs(time1, time2):
if time1 == None or time1 == ' ' or time1 == '': return 0
time1 = str(f.convert_sql_date(time1, "hh:mm"))
if time2 == None or time2 == ' ' or time2 == '':
time2 = str(f.convert_sql_date(datetime.now(), "hh:mm"))
else:
time2 = str(f.convert_sql_date(time2, "hh:mm"))
try:
h1 = str(time1).split(':')
h2 = str(time2).split(':')
if int(h1[0]) > 23: return 0
if int(h2[0]) > 23: return 0
if int(h1[1]) > 59: return 0
if int(h2[1]) > 59: return 0
mins1 = (int(h1[0]) * 60) + int(h1[1])
mins2 = (int(h2[0]) * 60) + int(h2[1])
if mins2 < mins1: mins2 += (24 * 60)
mins1 = mins2 - mins1
return mins1
except:
return 0
def get_end_time(dte):
return "23000101"
if dte == None: return "23000101"
return f.convert_sql_date(dte, "yyyymmdd")
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 build_access_string(access_patterns):
timezone_list = []
for index in range(len(access_patterns)):
a = [0,0,0,0,0,0,0]
timezone_id = access_patterns[index][0]
timezone_string = "TimezoneId=" + str(timezone_id)
for x in range(10):
if access_patterns[index][x+4] is not None:
time_from = f.convert_sql_date(access_patterns[index][x+4],"hh:mm")
time_to = f.convert_sql_date(access_patterns[index][x+14],"hh:mm")
type = access_patterns[index][x+24]
value = str(timezone_dec(time_from,time_to))
if type.lower() == "all days":
if a[0] < 3:
y = int(a[0])+1
timezone_string += "\t" + "SunTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[0] +=1
if a[1] < 3:
y = int(a[1])+1
timezone_string += "\t" + "MonTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[1] +=1
if a[2] < 3:
y = int(a[2])+1
timezone_string += "\t" + "TueTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[2] +=1
if a[3] < 3:
y = int(a[3])+1
timezone_string += "\t" + "WedTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[3] +=1
if a[4] < 3:
y = int(a[4])+1
timezone_string += "\t" + "ThuTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[4] +=1
if a[5] < 3:
y = int(a[5])+1
timezone_string += "\t" + "FriTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[5] +=1
if a[6] < 3:
y = int(a[6])+1
timezone_string += "\t" + "SatTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[6] +=1
if type.lower() == "sunday":
if a[0] < 3:
y = int(a[0])+1
timezone_string += "\t" + "SunTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[0] +=1
if type.lower() == "monday":
if a[1] < 3:
y = int(a[1])+1
timezone_string += "\t" + "MonTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[1] +=1
if type.lower() == "tuesday":
if a[2] < 3:
y = int(a[2])+1
timezone_string += "\t" + "TueTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[2] +=1
if type.lower() == "wednesday":
if a[3] < 3:
y = int(a[3])+1
timezone_string += "\t" + "WedTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[3] +=1
if type.lower() == "thursday":
if a[4] < 3:
y = int(a[4])+1
timezone_string += "\t" + "ThuTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[4] +=1
if type.lower() == "friday":
if a[5] < 3:
y = int(a[5])+1
timezone_string += "\t" + "FriTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[5] +=1
if type.lower() == "saturday":
if a[6] < 3:
y = int(a[6])+1
timezone_string += "\t" + "SatTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[6] +=1
if type.lower() == "mon-thu":
if a[1] < 3:
y = int(a[1])+1
timezone_string += "\t" + "MonTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[1] +=1
if a[2] < 3:
y = int(a[2])+1
timezone_string += "\t" + "TueTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[2] +=1
if a[3] < 3:
y = int(a[3])+1
timezone_string += "\t" + "WedTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[3] +=1
if a[4] < 3:
y = int(a[4])+1
timezone_string += "\t" + "ThuTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[4] +=1
if type.lower() == "sat-sun":
if a[0] < 3:
y = int(a[0])+1
timezone_string += "\t" + "SunTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[0] +=1
if a[6] < 3:
y = int(a[6])+1
timezone_string += "\t" + "SatTime%d"%(y) + "=" + str(timezone_dec(time_from,time_to))
a[6] +=1
timezone_list[len(timezone_list):] = [timezone_string]
return timezone_list
def refreshTable(self):
global DATA_LIST
self.tableWidget.verticalHeader().setVisible(False)
self.tableWidget.setHorizontalHeaderLabels([
' LastName ', ' FirstName ',
' IN ', ' OUT ', ' IN ', ' OUT ', ' IN ',
' OUT ', ' IN ', ' OUT ', ' Total ', ' ',
' LastName ', ' FirstName ',
' IN ', ' OUT ', ' IN ', ' OUT ', ' IN ',
' OUT ', ' IN ', ' OUT ', ' Total '
])
for n in range(22):
self.tableWidget.horizontalHeaderItem(n).setTextAlignment(
Qt.AlignLeft)
rowcount = 0
if len(DATA_LIST) == 1:
rowcount = 1
else:
rowcount = int((len(DATA_LIST) + 1) / 2)
self.tableWidget.setRowCount(rowcount)
for n in range(len(DATA_LIST)):
row_number = int((n) / 2)
if n % 2 == 0:
column = 'left'
else:
column = 'right'
self.tableWidget.setItem(n, 10, QTableWidgetItem('0:00'))
self.tableWidget.setItem(n, 12, QTableWidgetItem(' '))
self.tableWidget.setItem(n, 22, QTableWidgetItem('0:00'))
swipes_count = 0
for y in range(11):
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])
if column == 'left':
self.tableWidget.setItem(row_number, yy,
QTableWidgetItem(field))
else:
self.tableWidget.setItem(row_number, yy + 12,
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 = get_total_time(n)
if column == 'left':
self.tableWidget.setItem(
row_number, 10, QTableWidgetItem(display_emp_total))
else:
self.tableWidget.setItem(
row_number, 22, QTableWidgetItem(display_emp_total))
for col in range(11):
actual_col = col
if column == 'right': actual_col += 12
if self.tableWidget.item(row_number, actual_col) != None:
self.tableWidget.item(row_number,
actual_col).setBackground(
QtGui.QColor(r, g, b))
stylesheet = "QHeaderView::section{Background-color:rgb(117,146,156);\
border-radius:40px;}"
self.tableWidget.setStyleSheet(stylesheet)
self.tableWidget.resizeColumnsToContents()
