Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
2.5 kB
2
Indexable
Never
def load_data(self):
    print(self.date)
    # Connect to the PostgreSQL database
    conn = psycopg2.connect(host='localhost', dbname='insurgent_db', user='postgres', password='admin', port='5432')
    cursor = conn.cursor()

    # Retrieve data from the employees table including the staff ID
    cursor.execute("""
        SELECT 
            employees.employee_id, 
            first_name, 
            last_name,
            start_time,
            end_time,
            status,
            schedule_id
        FROM 
            employees 
        LEFT JOIN 
            schedules 
        ON 
            employees.employee_id = schedules.employee_id 
        WHERE 
            (schedules.shift_date = %s OR schedules.shift_date IS NULL)
        ORDER BY 
            CASE status 
                WHEN 'REGULAR' THEN 1
                WHEN 'RESERVE' THEN 2
                WHEN 'DAY OFF' THEN 3
                ELSE 4
            END,
            start_time
    """, (self.date,))
    rows = cursor.fetchall()

    self.tableWidget.setColumnCount(7)  # Set the number of columns including the hidden ID column
    self.tableWidget.setHorizontalHeaderLabels(['ID', 'First Name', 'Last Name', 'Start Time', 'End Time', 'Status', 'Schedule ID'])
    self.tableWidget.setRowCount(len(rows))  # Set the number of rows

    for row_idx, row_data in enumerate(rows):
        for col_idx, col_data in enumerate(row_data):
            if col_idx == 3 or col_idx == 4:  # Start Time or End Time
                if row_data[5] == 'DAY OFF':  # Check if status is 'DAY OFF'
                    col_data = ''  # Set to empty string
                elif isinstance(col_data, datetime.datetime):
                    col_data = col_data.strftime('%H:%M')  # Convert datetime to time string
                elif isinstance(col_data, datetime.date):
                    col_data = col_data.strftime('%Y-%m-%d')  # Convert date to string
            item = QTableWidgetItem(str(col_data))
            item.setTextAlignment(Qt.AlignCenter)  # Align text to center
            self.tableWidget.setItem(row_idx, col_idx, item)

    header = self.tableWidget.horizontalHeader()
    header.setSectionResizeMode(QHeaderView.Stretch)
    # Hide the ID column
    self.tableWidget.setColumnHidden(0, True)
    self.tableWidget.setColumnHidden(6, True)

    # Close the connection
    cursor.close()
    conn.close()
Leave a Comment