I’m looking to incorporate page numbers into my document for better navigation. At the moment, I’ve managed to display ‘Page 1’, but as the document flows onto subsequent pages, I want the numbering to continue incrementing. Additionally, I’m facing challenges in positioning the page numbers neatly in the bottom right corner.

# Function to fetch employee data from SQLite database
def fetch_employee_data(database_file, fromdate, todate):
    conn = sqlite3.connect("brgydb.db")
    cursor = conn.cursor()
    # Execute a query to retrieve records within the date range
    query = "SELECT lastname || ', ' || firstname, address, mobileno FROM employee WHERE datehired BETWEEN ? AND ?"
    cursor.execute(query, (fromdate, todate))

    employee_data = cursor.fetchall()
    conn.close()
    return employee_data
def generate_pdf(employee_data, output_file):
    doc = SimpleDocTemplate(output_file, pagesize=letter, topMargin=50)

    def footer(canvas, doc):
        pageNumber = canvas.getPageNumber()
        canvas.saveState()
        canvas.setFont('Helvetica', 9)
        if pageNumber == 1:  # First page
            text = "Page 1"
        else:  # Second page onwards
            text = f"Page {pageNumber - 1}" 
        text_width = stringWidth(text, 'Helvetica', 9)  
        canvas.drawString(doc.width - inch * 0.5 - text_width, 0.15 * inch, text)  
        canvas.restoreState()

    # Create a frame for the footer
    frame = Frame(doc.leftMargin, doc.bottomMargin, doc.width, doc.height, id='normal')
    template = PageTemplate(id='test', frames=[frame], onPage=footer)
    doc.addPageTemplates([template])
    
    elements = []

    chart_style = TableStyle([('ALIGN', (0, 0), (-1, -1), 'CENTER')])
    image1 = Image("provineofrizal.png", width=80, height=80) 
    image2 = Image("brgylogo.jpg", width=80, height=80) 
    bold_style = ParagraphStyle('BoldText', fontName='Helvetica-Bold', fontSize=15, alignment=1, spaceBefore=10)

    # Write text to the canvas
    text = "<b>COMPANY NAME</b><br/><br/>EMPLOYEE REPORT LIST<br/><br/>Whatever text you put<br/>"
    p = Paragraph(text, bold_style)
    
    elements.append(Table([[image1, p, image2]],
                          colWidths=[3.3 * inch, 3.3 * inch, 3.3 * inch],
                          rowHeights=[2.5 * inch], style=chart_style))
    elements.append(Spacer(1, 15))
    
    # Define table data
    data = [['Employee Name', 'Mobile No', 'Address']]
    data.append(['--------------------', '--------------------', '----------------------------------------------------------------------------------------------'])  # Separator line
    for employee in employee_data:
 
        data.append([employee[0], employee[2][:200], employee[1]])

    # Define table style 
    style = TableStyle([('FONT', (0, 0), (-1, 0), 'Helvetica-Bold'),
                        ('BACKGROUND', (0, 0), (-1, 0), colors.white)])

    # Create table object
    table = Table(data)

    # Apply style to table
    table.setStyle(style)
    elements.append(table)

    # Build PDF
    doc.build(elements)