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()