Python Bits and Pieces with Cyber Security

Tuesday, September 10, 2024

Creating Forms and Print Preview with Jinja2 Templates in PyQt6

In this blog post, we will walk through creating a PyQt6 application that uses Jinja2 templates for rendering printable content. Our example application is a manufacturing order entry system with forms for data entry and functionality for print preview and printing.

We expect to create the following screen:

And here is its print preview:

Step-by-Step Breakdown

Setup PyQt6 and Jinja2
Ensure you have PyQt6 and Jinja2 installed. You can install these via pip if you haven’t already:

pip install PyQt6 jinja2

2. Create the Main Application Class
We'll start by creating a class ManufacturingOrderEntry that inherits from QMainWindow. This class will contain all the UI elements and functionality for our form.

import sys
from PyQt6.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QFormLayout, QLineEdit, QDateEdit, QLabel, QHBoxLayout, QTableWidget, QTableWidgetItem, QGridLayout, QPushButton, QTextEdit
from PyQt6.QtGui import QFont, QColor, QPalette
from PyQt6.QtCore import Qt
from PyQt6 import QtPrintSupport
from jinja2 import Template

3. Define the Constructor and Layouts
In the __init__ method, set up the main window layout and UI components

class ManufacturingOrderEntry(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Manufacturing Order Entry")
        self.setGeometry(100, 100, 800, 600)

        # Main Layout
        main_widget = QWidget(self)
        self.setCentralWidget(main_widget)
        main_layout = QVBoxLayout()
        main_widget.setLayout(main_layout)

        # Header Sections
        header_layout = self.create_header_layout()
        main_layout.addLayout(header_layout)

        # Process and Inspection Sections
        process_layout = self.create_process_layout()
        main_layout.addLayout(process_layout)

        # Prepared and Approved By Section
        prepared_approved_layout = self.create_prepared_approved_layout()
        main_layout.addLayout(prepared_approved_layout)

        # Adding TextEdit for rendering printable content
        self.etext = QTextEdit(self)
        self.etext.setReadOnly(True)
        main_layout.addWidget(self.etext)

        # Buttons for Print and Preview
        button_layout = QHBoxLayout()
        self.print_button = QPushButton('Print', self)
        self.preview_button = QPushButton('Preview', self)
        button_layout.addWidget(self.preview_button)
        button_layout.addWidget(self.print_button)
        main_layout.addLayout(button_layout)

        # Connecting buttons to handlers
        self.preview_button.clicked.connect(self.handle_print_preview)
        self.print_button.clicked.connect(self.handle_print)

        # Generate the content for the QTextEdit
        self.generate_content()

4. Define Layout Creation Methods
Methods like create_header_layout, create_process_layout, and create_prepared_approved_layout are responsible for setting up different sections of the form.

def create_header_layout(self):
    layout = QGridLayout()
    layout.setContentsMargins(5, 5, 5, 5)
    # Add widgets and layout configurations here
    return layout

def create_process_layout(self):
    layout = QVBoxLayout()
    # Add widgets and layout configurations here
    return layout

def create_prepared_approved_layout(self):
    layout = QHBoxLayout()
    # Add widgets and layout configurations here
    return layout

5. Generate Content with Jinja2
Use Jinja2 templates to render HTML content for printing. This content is set in a QTextEdit widget, which will be used for both print preview and printing.

def generate_content(self):
    # Collecting data
    # Define the data to be used in the template
    template = """
    <style>
        /* Define your CSS styles here */
    </style>
    <h1>Production Order</h1>
    <!-- Manufacturing Order Details -->
    <table>
        <!-- Table content here -->
    </table>
    <!-- Process I, II, Inspection, Packing tables -->
    <!-- Signature section -->
    """
    # Render the template with data
    rendered_html = Template(template).render(
        # Pass variables here
    )
    self.etext.setHtml(rendered_html)

6. Implement Print Preview and Printing
Implement methods to handle print preview and printing using QPrintPreviewDialog and QPrinter.

def handle_print_preview(self):
    dialog = QtPrintSupport.QPrintPreviewDialog()
    dialog.paintRequested.connect(self.etext.print)
    dialog.exec()

def handle_print(self):
    printer = QtPrintSupport.QPrinter(QtPrintSupport.QPrinter.PrinterMode.HighResolution)
    printer.setPageSize(QtPrintSupport.QPageSize(QtPrintSupport.QPageSize.PageSizeId.A4))
    self.etext.print(printer)

7. Run the Application
Finally, set up the main function to run the application

def main():
    app = QApplication(sys.argv)
    window = ManufacturingOrderEntry()
    window.show()
    sys.exit(app.exec())

if __name__ == "__main__":
    main()

Conclusion

This example demonstrates how to integrate Jinja2 templating with PyQt6 to create dynamic, printable forms. The approach allows for the separation of form data and presentation logic, making it easier to manage and update form layouts and styles. You can further customize the template and styles as needed for your specific use case.

Here is the rest complete program listing:

import sys
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QFormLayout, QLineEdit, 
    QDateEdit, QLabel, QHBoxLayout, QTableWidget, QTableWidgetItem, QGridLayout, QPushButton, QTextEdit
)
from PyQt6.QtGui import QFont, QColor, QPalette
from PyQt6.QtCore import Qt
from PyQt6 import QtPrintSupport
from jinja2 import Template

class ManufacturingOrderEntry(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Manufacturing Order Entry")
        self.setGeometry(100, 100, 800, 600)

        # Main Layout
        main_widget = QWidget(self)
        self.setCentralWidget(main_widget)
        main_layout = QVBoxLayout()
        main_widget.setLayout(main_layout)

        # Header Sections
        header_layout = self.create_header_layout()
        main_layout.addLayout(header_layout)

        # Process and Inspection Sections
        process_layout = self.create_process_layout()
        main_layout.addLayout(process_layout)

        # Prepared and Approved By Section
        prepared_approved_layout = self.create_prepared_approved_layout()
        main_layout.addLayout(prepared_approved_layout)

        # Adding TextEdit for rendering printable content
        self.etext = QTextEdit(self)
        self.etext.setReadOnly(True)
        main_layout.addWidget(self.etext)

        # Buttons for Print and Preview
        button_layout = QHBoxLayout()
        self.print_button = QPushButton('Print', self)
        self.preview_button = QPushButton('Preview', self)
        button_layout.addWidget(self.preview_button)
        button_layout.addWidget(self.print_button)
        main_layout.addLayout(button_layout)

        # Connecting buttons to handlers
        self.preview_button.clicked.connect(self.handle_print_preview)
        self.print_button.clicked.connect(self.handle_print)

        # Generate the content for the QTextEdit
        self.generate_content()

    def generate_content(self):
        # Collecting data from the form fields
        order_no = "12345"
        product = "Widget A"
        job_quantity = "500"
        customer_delivery_date = "2024-09-25"
        job_started_date = "2024-09-20"
        rm_transfer_date = "2024-09-21"
        packing_target_date = "2024-09-22"
        dispatch_target_date = "2024-09-23"
        finish_product_transfer_date = "2024-09-24"
        job_end_date = "2024-09-25"

        process_i = [
            ["1", "100", "pcs", "Stage 1", "0", "$100", "100"],
            ["2", "200", "pcs", "Stage 2", "0", "$200", "200"],
            ["3", "300", "pcs", "Stage 3", "0", "$300", "300"],
        ]
        process_ii = [
            ["1", "100", "pcs", "Stage 1", "0", "$100", "100"],
            ["2", "200", "pcs", "Stage 2", "0", "$200", "200"],
        ]
        inspection = [
            ["1", "100", "pcs", "Inspection 1", "0", "$50", "100"],
        ]
        packing = [
            ["1", "100", "pcs", "Packing 1", "0", "$70", "100"],
        ]
        prepared_by = "John Doe"
        approved_by = "Jane Smith"

        template = """
<style>
    body {
        font-family: Arial, sans-serif;
    }
    table {
        width: 100%;
        border-collapse: collapse;
    }
    th, td {
        border: 1px solid #000;
        padding: 5px;
        text-align: center;
    }
    th {
        background-color: #000;
        color: #fff;
    }
    .section-header {
        background-color: #000;
        color: #fff;
        text-align: center;
        font-weight: bold;
        padding: 5px;
        border: 1px solid #000;
        margin-top: 10px;
    }
    .process-table {
        margin-top: 10px;
    }
    .process-header {
        background-color: #FFD700;
        text-align: center;
        font-weight: bold;
    }
    .signature-section {
        width: 50%;
        margin: 30px auto;
        text-align: center;
    }
    .signature-line {
        border-top: 1px solid #000;
        width: 60%;
        margin: 0 auto;
    }
    .signature-label {
        margin-top: 5px;
        font-weight: bold;
    }
    .signature-container {
        display: flex;
        justify-content: space-between;
    }
    .signature-item {
        flex: 1;
        text-align: center;
    }
</style>

<h1>Production Order</h1>

<!-- Manufacturing Order Details -->
<table>
    <tr>
        <td>Manufacturing Order No. & Date:</td>
        <td>{{ order_no }}</td>
        <td>Job Started Date:</td>
        <td>{{ job_started_date }}</td>
    </tr>
    <tr>
        <td>Product:</td>
        <td>{{ product }}</td>
        <td>R.M. Location - Mat. Transfer Date:</td>
        <td>{{ rm_transfer_date }}</td>
    </tr>
    <tr>
        <td>Job Quantity:</td>
        <td>{{ job_quantity }}</td>
        <td>Target of Packing:</td>
        <td>{{ packing_target_date }}</td>
    </tr>
    <tr>
        <td>Customer Delivery Date:</td>
        <td>{{ customer_delivery_date }}</td>
        <td>Target of Dispatch:</td>
        <td>{{ dispatch_target_date }}</td>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td>Finish Product Location - Mat. Transfer Date:</td>
        <td>{{ finish_product_transfer_date }}</td>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td>Job End Date:</td>
        <td>{{ job_end_date }}</td>
    </tr>
</table>

<!-- Process I -->
<div class="section-header">Process I</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in process_i %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Process II -->
<div class="section-header">Process II</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in process_ii %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Inspection -->
<div class="section-header">Inspection</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in inspection %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Packing -->
<div class="section-header">Packing</div>
<table class="process-table">
    <thead class="process-header">
        <tr>
            <th>Serial No.</th>
            <th>QTY</th>
            <th>U.M.O</th>
            <th>Internal Process Stage</th>
            <th>Rej. Qty</th>
            <th>Cost</th>
            <th>Passed Quantity</th>
        </tr>
    </thead>
    <tbody>
        {% for row in packing %}
        <tr>
            {% for element in row %}
            <td>{{ element }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </tbody>
</table>

<!-- Prepared and Approved By -->
<div class="signature-section" style="margin-top: 50px;">
    <div class="signature-container">
        <div class="signature-item">
            <div class="signature-label">Prepared By</div>
            <div class="signature-line"></div>
            <div style="height: 50px;"></div> <!-- Space for signature -->
            <div>{{ prepared_by }}</div>
        </div>
        <p /> <br />
        <div class="signature-item">
            <div class="signature-label">Approved By</div>
            <div class="signature-line"></div>
            <div style="height: 50px;"></div> <!-- Space for signature -->
            <div>{{ approved_by }}</div>
        </div>
    </div>
</div>
"""

        # Render the table with Jinja2 template
        rendered_html = Template(template).render(
            order_no=order_no,
            product=product,
            job_quantity=job_quantity,
            customer_delivery_date=customer_delivery_date,
            job_started_date=job_started_date,
            rm_transfer_date=rm_transfer_date,
            packing_target_date=packing_target_date,
            dispatch_target_date=dispatch_target_date,
            finish_product_transfer_date=finish_product_transfer_date,
            job_end_date=job_end_date,
            process_i=process_i,
            process_ii=process_ii,
            inspection=inspection,
            packing=packing,
            prepared_by=prepared_by,
            approved_by=approved_by
        )
        self.etext.setHtml(rendered_html)


    def handle_print_preview(self):
        dialog = QtPrintSupport.QPrintPreviewDialog()
        dialog.paintRequested.connect(self.etext.print)
        dialog.exec()

    def handle_print(self):
        printer = QtPrintSupport.QPrinter(QtPrintSupport.QPrinter.PrinterMode.HighResolution)
        printer.setPageSize(QtPrintSupport.QPageSize(QtPrintSupport.QPageSize.PageSizeId.A4))
        self.etext.print(printer)

    def create_header_layout(self):
        layout = QGridLayout()
        layout.setContentsMargins(5, 5, 5, 5)

        # Manufacturing Order Details
        layout.addWidget(self.create_colored_label("MANUFACTURING ORDER NO. & DATE:", "lightblue"), 0, 0)
        layout.addWidget(QLineEdit(), 0, 1)
        layout.addWidget(self.create_colored_label("PRODUCT:", "lightblue"), 1, 0)
        layout.addWidget(QLineEdit(), 1, 1)
        layout.addWidget(self.create_colored_label("JOB QUANTITY:", "lightblue"), 2, 0)
        layout.addWidget(QLineEdit(), 2, 1)
        layout.addWidget(self.create_colored_label("CUSTOMER DELIVERY DATE:", "lightblue"), 3, 0)
        layout.addWidget(QDateEdit(calendarPopup=True), 3, 1)

        # Job Details
        layout.addWidget(self.create_colored_label("JOB STARTED DATE:", "lightgreen"), 0, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 0, 3)
        layout.addWidget(self.create_colored_label("R.M. LOCATION - MAT. TRANSFER DATE:", "lightgreen"), 1, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 1, 3)
        layout.addWidget(self.create_colored_label("TARGET OF PACKING:", "lightgreen"), 2, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 2, 3)
        layout.addWidget(self.create_colored_label("TARGET OF DISPATCH:", "lightgreen"), 3, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 3, 3)
        layout.addWidget(self.create_colored_label("FINISH PRODUCT LOC. - MAT. TRANSFER DATE:", "lightgreen"), 4, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 4, 3)
        layout.addWidget(self.create_colored_label("JOB END DATE:", "lightgreen"), 5, 2)
        layout.addWidget(QDateEdit(calendarPopup=True), 5, 3)

        return layout

    def create_process_layout(self):
        layout = QVBoxLayout()
        
        # Process I Section
        layout.addWidget(self.create_colored_label("Process I", "lightpurple", True, font_color="white"))
        layout.addWidget(self.create_process_table("Process I", QColor(245, 222, 255)))  # Light purple

        # Process II Section
        layout.addWidget(self.create_colored_label("Process II", "lightpurple", True, font_color="white"))
        layout.addWidget(self.create_process_table("Process II", QColor(245, 222, 255)))  # Light purple

        # Inspection Section
        layout.addWidget(self.create_colored_label("Inspection", "yellow", True))
        layout.addWidget(self.create_process_table("Inspection", QColor(255, 255, 204)))  # Light yellow

        # Packing Section
        layout.addWidget(self.create_colored_label("Packing", "lightgreen", True))
        layout.addWidget(self.create_process_table("Packing", QColor(204, 255, 204)))  # Light green

        return layout

    def create_process_table(self, title, color):
        table = QTableWidget(5, 7)
        table.setHorizontalHeaderLabels([
            "Serial No.", "QTY", "U.M.O", "Internal Process Stage", 
            "Rej. Qty", "Cost", "Passed Quantity"
        ])
        for i in range(5):
            for j in range(7):
                table.setItem(i, j, QTableWidgetItem(""))

        # Set background color for each cell
        palette = table.palette()
        palette.setColor(QPalette.ColorRole.Base, color)
        table.setPalette(palette)
        table.setAlternatingRowColors(True)
        return table

    def create_colored_label(self, text, color, is_section=False, font_color="black"):
        label = QLabel(text)
        if is_section:
            label.setAlignment(Qt.AlignmentFlag.AlignCenter)
            label.setFont(QFont('Arial', 10, QFont.Weight.Bold))
            label.setStyleSheet(f"background-color: {color}; color: {font_color}; padding: 5px; border: 1px solid black;")
        else:
            label.setStyleSheet(f"background-color: {color}; padding: 5px;")
        return label

    def create_prepared_approved_layout(self):
        layout = QHBoxLayout()
        layout.addWidget(self.create_colored_label("PREPARED BY", "white"))
        layout.addWidget(QLineEdit())
        layout.addWidget(self.create_colored_label("APPROVED BY", "white"))
        layout.addWidget(QLineEdit())
        return layout

def main():
    app = QApplication(sys.argv)
    window = ManufacturingOrderEntry()
    window.show()
    sys.exit(app.exec())

if __name__ == "__main__":
    main()

Sunday, September 8, 2024

Creating Hierarchical Queries in SQLite Using Python: A Step-by-Step Guide

When working with databases, organizing data hierarchically can significantly improve data understanding and accessibility, especially in software development, customization, and enhancement projects. In this blog post, we will explore how to create hierarchical queries in SQLite using Python, focusing on a real-world scenario involving companies, plants, warehouses, sales organizations, and sales divisions. We will use subqueries to achieve this and save the result as a view for easy reuse.

Why Hierarchical Queries Matter in Database Management

Hierarchical queries are essential in applications where data is naturally structured in parent-child relationships. For instance, in a manufacturing setup:

A company might own multiple plants and sales organizations.
Each plant could have several warehouses.
Each sales organization might have multiple sales divisions.

Such hierarchical structures help in:

Visualizing complex relationships between different data entities.
Simplifying data retrieval by providing a structured view.
Enhancing performance by reducing the need for multiple joins in frequently executed queries.

By creating a hierarchical view in the database, developers and analysts can query this view directly, significantly simplifying application logic and report generation.

Setting Up the Scene: The Database Schema

Imagine the following database tables:

CompanyCodes: Contains company code details.
Plants: Contains plant details, related to a company code.
SalesOrganizations: Contains sales organization details, related to a company code.
Warehouses: Contains warehouse details, related to a plant.
SalesDivisions: Contains sales division details, related to a sales organization.

The goal is to create a hierarchical view where each company code lists its associated plants, sales organizations, warehouses, and sales divisions.

Step-by-Step Guide to Creating the Hierarchical Query

Step 1: Connect to Your SQLite Database

First, connect to your SQLite database using Python’s sqlite3 module. This module provides a lightweight and easy-to-use interface for working with SQLite databases.

import sqlite3

# Path to your SQLite database
db_path = 'your_database.db'

# Connect to the database
connection = sqlite3.connect(db_path)
cursor = connection.cursor()

Step 2: Write the Hierarchical Query

We will use subqueries within our main query to nest the results. Each subquery will gather related data, such as plants under each company, warehouses under each plant, etc.

CREATE VIEW CompanyHierarchy AS
SELECT 
    cc.company_code, 
    -- Subquery to get all plants related to the company code
    (
        SELECT GROUP_CONCAT(plant_name)
        FROM (
            SELECT p.plant_name 
            FROM Plants p 
            WHERE p.company_code = cc.company_code
        ) AS PlantList
    ) AS plants,
    -- Subquery to get all sales organizations related to the company code
    (
        SELECT GROUP_CONCAT(sales_org_name)
        FROM (
            SELECT so.sales_org_name 
            FROM SalesOrganizations so 
            WHERE so.company_code = cc.company_code
        ) AS SalesOrgList
    ) AS sales_organizations,
    -- Subquery to get warehouses for each plant of the company
    (
        SELECT GROUP_CONCAT(wh.warehouse_name)
        FROM Warehouses wh
        JOIN Plants p ON wh.plant_id = p.plant_id
        WHERE p.company_code = cc.company_code
    ) AS warehouses,
    -- Subquery to get sales divisions for each sales organization of the company
    (
        SELECT GROUP_CONCAT(sd.sales_division_name)
        FROM SalesDivisions sd
        JOIN SalesOrganizations so ON sd.sales_org_id = so.sales_org_id
        WHERE so.company_code = cc.company_code
    ) AS sales_divisions
FROM 
    CompanyCodes cc;

Step 3: Execute the Query and Save as a View

The CREATE VIEW statement saves the hierarchical query as a view named CompanyHierarchy. This allows you to query this structure directly in future database operations.

# Define the query to create the hierarchical view
create_view_query = """
CREATE VIEW CompanyHierarchy AS
SELECT 
    cc.company_code, 
    (
        SELECT GROUP_CONCAT(plant_name)
        FROM (
            SELECT p.plant_name 
            FROM Plants p 
            WHERE p.company_code = cc.company_code
        ) AS PlantList
    ) AS plants,
    (
        SELECT GROUP_CONCAT(sales_org_name)
        FROM (
            SELECT so.sales_org_name 
            FROM SalesOrganizations so 
            WHERE so.company_code = cc.company_code
        ) AS SalesOrgList
    ) AS sales_organizations,
    (
        SELECT GROUP_CONCAT(wh.warehouse_name)
        FROM Warehouses wh
        JOIN Plants p ON wh.plant_id = p.plant_id
        WHERE p.company_code = cc.company_code
    ) AS warehouses,
    (
        SELECT GROUP_CONCAT(sd.sales_division_name)
        FROM SalesDivisions sd
        JOIN SalesOrganizations so ON sd.sales_org_id = so.sales_org_id
        WHERE so.company_code = cc.company_code
    ) AS sales_divisions
FROM 
    CompanyCodes cc;
"""

# Execute the query to create the view
cursor.execute(create_view_query)
connection.commit()

# Close the connection
connection.close()

print("View 'CompanyHierarchy' created successfully.")

Step 4: Query the Hierarchical View

Now that the view is created, you can easily query the hierarchical data structure:

# Reconnect to the database
connection = sqlite3.connect(db_path)
cursor = connection.cursor()

# Query the hierarchical view
cursor.execute("SELECT * FROM CompanyHierarchy")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the connection
connection.close()

Benefits of Using Hierarchical Views in Development

Simplified Queries: By encapsulating the complex joins and relationships within a view, your application code becomes cleaner and easier to maintain.
Performance Optimization: Views can sometimes help in performance optimization by allowing the database engine to optimize the query execution plan.
Enhanced Readability: It improves the readability of data relationships, making it easier for developers and analysts to understand and work with the data.
Reusability: Once the view is created, it can be reused across different parts of the application, ensuring consistency in data representation.

Conclusion

Creating hierarchical queries in SQLite using Python is a powerful technique for managing and visualizing complex data relationships. By saving these queries as views, you not only simplify the development process but also enhance the performance and maintainability of your applications. Whether you are developing new features, customizing existing systems, or enhancing current workflows, understanding and leveraging hierarchical data structures will provide significant advantages.

Storing Images in Databases: A Practical Approach for ERP Systems

In enterprise resource planning (ERP) systems, data management is crucial for operational efficiency and accuracy. While traditional ERP systems primarily handle structured data like numbers, text, and dates, there's a growing need to store unstructured data such as images, scanned documents, and other media files. This is especially important for maintaining records of scanned copies of invoices, purchase orders, delivery receipts, and other critical documents. In this blog post, we will explore a Python program that demonstrates how to store and retrieve images (like JPG files) in an SQLite3 database, and discuss the advantages of this approach in ERP systems.

Why Store Images in Databases?

Centralized Data Management: By storing images in a database, all related data is centralized, making it easier to manage, backup, and restore. This is particularly useful in ERP systems where scanned documents like invoices and receipts need to be securely stored alongside other transactional data.
Enhanced Security: Databases offer robust security features like access control, encryption, and audit trails, which help protect sensitive data, including scanned documents. Storing images within the database ensures they are subject to the same security measures as other data.
Improved Data Integrity and Consistency: When images are stored in the same database as the transactional records they relate to, there is a higher level of data integrity. For example, linking a scanned purchase order directly to its corresponding entry in the system ensures consistency and traceability.
Streamlined Access and Retrieval: Storing images directly in the database allows for easy retrieval using standard SQL queries. This simplifies the process of accessing documents related to transactions, such as pulling up an invoice when reviewing a financial transaction.
Scalability: As ERP systems scale, so does the need for storage. Modern databases can handle large amounts of BLOB (Binary Large Object) data efficiently, making it feasible to store even large scanned documents without significant performance degradation.

The Python Program: Storing and Displaying Images in SQLite3

Below is a sample Python program that demonstrates how to store and display images using SQLite3. This approach can be extended to manage scanned copies of invoices, purchase orders, and delivery receipts within an ERP system.

import sqlite3
from PIL import Image
import io

# Step 1: Create the database connection and cursor
conn = sqlite3.connect('images.db')
cursor = conn.cursor()

# Step 2: Create the table with a BLOB field for storing images
cursor.execute('''
CREATE TABLE IF NOT EXISTS photos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    image BLOB
)
''')
conn.commit()

# Function to convert image file to binary data
def convert_to_binary_data(filename):
    with open(filename, 'rb') as file:
        return file.read()

# Step 3: Insert an image into the database
def insert_image(name, filename):
    image_data = convert_to_binary_data(filename)
    cursor.execute('''
    INSERT INTO photos (name, image) VALUES (?, ?)
    ''', (name, image_data))
    conn.commit()
    print(f"Image {name} inserted successfully")

# Step 4: Retrieve and display the image from the database
def display_image(image_id):
    cursor.execute('SELECT name, image FROM photos WHERE id = ?', (image_id,))
    record = cursor.fetchone()
    
    if record:
        name, image_data = record
        print(f"Displaying Image: {name}")
        
        # Convert binary data to image and display
        image = Image.open(io.BytesIO(image_data))
        image.show()
    else:
        print("No image found with the given ID.")

# Example usage
# Insert an image into the database (ensure you have a 'sample.jpg' in the same directory)
insert_image('Sample Image', 'sample.jpg')

# Retrieve and display the inserted image
display_image(1)

# Close the database connection
conn.close()

How the Program Works

Database Setup: The program first establishes a connection to an SQLite3 database and creates a table called photos with fields for an ID, name, and image data (stored as BLOB).
Inserting Images: The insert_image function reads an image file in binary mode and inserts it into the database. This is particularly useful for storing scanned documents directly within the ERP database.
Displaying Images: The display_image function retrieves image data from the database using a given ID and displays it using the Python Imaging Library (PIL). This feature can be used to view scanned documents linked to transactions in real-time.

Advantages of Storing Images in a Database for ERP Systems

Direct Association with Transactions: Images can be directly linked to their corresponding records (e.g., invoices, purchase orders), simplifying access and enhancing the accuracy of record-keeping.
Reduced Risk of File Loss: Unlike files stored on separate servers or in file systems, database-managed images benefit from database backup and recovery protocols, reducing the risk of data loss.
Consistent Data Access: Users access all data, including images, through the same interface (the ERP system), ensuring consistent access controls and data handling procedures.
Simplified Auditing and Compliance: Storing images in the database aids in meeting compliance requirements, such as keeping copies of financial documents, by maintaining all records in one location with appropriate audit trails.
Enhanced Reporting and Analytics: With images stored in the database, it's easier to include them in reports or analytics processes, providing a complete view of transactions, including the supporting documents.

Considerations

Performance: While storing images in a database offers many benefits, it's essential to monitor performance, especially as the volume of images grows. Proper indexing and database tuning are required to maintain performance.
Database Size Management: Storing large BLOBs can increase the size of the database significantly. Strategies like archiving older records or using database storage optimization techniques can help manage this.

Conclusion

Storing images such as scanned copies of invoices, purchase orders, and delivery receipts in a database provides significant advantages in an ERP environment, including improved data integrity, security, and ease of access. The Python program presented above is a practical example of how this can be implemented using SQLite3, demonstrating the feasibility and benefits of integrating image storage into your ERP system's database.

Friday, September 6, 2024

Introduction to Solidity: Building Smart Contracts for Ethereum and Practical Crowdfunding Example

Solidity is a high-level, statically-typed programming language used for writing smart contracts that run on the Ethereum blockchain. Smart contracts are self-executing programs that enforce and automate agreements between parties without the need for intermediaries. Solidity is specifically designed for creating these contracts on the Ethereum Virtual Machine (EVM).

Here's a brief introduction to Solidity programming, including its key concepts, a simple tutorial, and an example of writing a smart contract.

Key Concepts of Solidity

Smart Contracts: These are autonomous programs on the blockchain that execute actions when predefined conditions are met. They are the building blocks of decentralized applications (dApps).
Ethereum Virtual Machine (EVM): This is the runtime environment for smart contracts on Ethereum. It ensures that all nodes in the network can execute the contract code in the same way.
Gas: Executing operations in Solidity consumes computational resources, which are paid for with "gas" (in ETH). Gas costs ensure that contracts are optimized and prevent abuse of network resources.
ABI (Application Binary Interface): The ABI defines how data structures and functions in smart contracts can be accessed from outside the blockchain.

Getting Started with Solidity

1. Setting Up Your Environment

You can write and deploy Solidity contracts using various tools, but the easiest way to get started is with Remix, an online IDE specifically for Solidity development:

Visit Remix IDE
Create a new file with a .sol extension (e.g., SimpleContract.sol).

2. Basic Structure of a Solidity Contract

Here's a basic template of a Solidity smart contract:

// SPDX-License-Identifier: MIT
pragma solidity ^0.8.0; // Specify the Solidity compiler version

contract SimpleContract {
    // State variables are stored on the blockchain
    uint public count; // Unsigned integer

    // A constructor that initializes the contract's state
    constructor() {
        count = 0; // Initialize the count to 0
    }

    // Function to increment the count
    function increment() public {
        count += 1;
    }

    // Function to get the current count
    function getCount() public view returns (uint) {
        return count;
    }
}

Breakdown of the Code

License Identifier: The // SPDX-License-Identifier: MIT line specifies the license for the contract, helping with compliance.
Pragma Directive: pragma solidity ^0.8.0; specifies the Solidity compiler version. This ensures compatibility and prevents code from running on unintended versions.
Contract Definition: contract SimpleContract defines a new contract. It's similar to a class in object-oriented programming.
State Variables: uint public count; declares a state variable. State variables are stored on the blockchain and maintain their values between function calls.
Constructor: The constructor() function initializes the state when the contract is deployed.
Functions:
- increment(): A public function that increments the count variable. Public functions can be called internally and externally.
- getCount(): A view function that returns the current value of the count. View functions do not alter the state and are free in terms of gas cost.

A More Practical Example: Simple Crowdfunding Contract

Here's a more practical example of a crowdfunding contract, illustrating the basics of contributions and simple refund logic:

// SPDX-License-Identifier: MIT
pragma solidity ^0.8.0;

contract Crowdfunding {
    address public owner; // Address of the project owner
    uint public goal; // Funding goal in Wei
    uint public deadline; // Deadline timestamp
    uint public totalFunds; // Total funds raised

    mapping(address => uint) public contributions; // Tracks contributions per address

    constructor(uint _goal, uint _duration) {
        owner = msg.sender; // Set the owner as the contract deployer
        goal = _goal; // Set the funding goal
        deadline = block.timestamp + _duration; // Set the funding deadline
    }

    // Function to contribute to the crowdfunding campaign
    function contribute() external payable {
        require(block.timestamp < deadline, "Campaign has ended");
        require(msg.value > 0, "Contribution must be greater than 0");

        contributions[msg.sender] += msg.value;
        totalFunds += msg.value;
    }

    // Function to withdraw funds if the goal is reached
    function withdraw() external {
        require(msg.sender == owner, "Only owner can withdraw");
        require(totalFunds >= goal, "Funding goal not reached");
        require(block.timestamp >= deadline, "Campaign is still active");

        payable(owner).transfer(totalFunds); // Transfer funds to the owner
    }

    // Function for contributors to claim a refund if the goal is not met
    function refund() external {
        require(block.timestamp >= deadline, "Campaign is still active");
        require(totalFunds < goal, "Funding goal reached, no refunds");

        uint amount = contributions[msg.sender];
        require(amount > 0, "No contributions to refund");

        contributions[msg.sender] = 0;
        payable(msg.sender).transfer(amount); // Refund the contributor
    }
}

Features of the Crowdfunding Contract

Owner Address: Set to the account that deploys the contract. This is typically the project owner.
Funding Goal and Deadline: Set during contract deployment. The contract tracks whether the goal is met within the deadline.
Contribution Management: Users can contribute Ether to the campaign, which is tracked by their addresses.
Withdrawal and Refunds:
- If the goal is met, the project owner can withdraw the funds after the campaign ends.
- If the goal is not met, contributors can request a refund of their contributions.

Deploying the Contract

To deploy the contract using Remix:

Paste the code into Remix and compile it.
Go to the "Deploy & Run Transactions" tab.
Select the environment (e.g., JavaScript VM for testing).
Deploy the contract by specifying the goal and duration.
Interact with the deployed contract functions (contribute, withdraw, refund) via the Remix UI.

Best Practices for Solidity Development

Security Audits: Smart contracts are immutable once deployed, so always conduct thorough security audits.
Testing: Use testing frameworks like Truffle or Hardhat to write and run tests for your contracts.
Gas Optimization: Optimize your code to reduce gas costs, as users pay for each operation.
Error Handling: Use require, assert, and revert to manage errors and ensure your contract operates safely.

Interacting with the Ethereum smart Contract with Python

To interact with a smart contract on Ethereum using Python, you can use the web3.py library. Below is a step-by-step guide and a sample Python program that demonstrates how to connect to an Ethereum network, interact with a deployed smart contract, and call its functions.

Step-by-Step Guide

Install web3.py: First, make sure you have web3.py installed. You can install it via pip:

pip install web3

Set Up the Ethereum Connection: To connect to the Ethereum network, you'll need access to an Ethereum node. You can use a service like Infura or Alchemy, or run your own node.
Get Contract ABI and Address:
ABI: The Application Binary Interface (ABI) is a JSON file that defines how to interact with the contract's functions and data structures.
Contract Address: This is the address of the deployed smart contract on the Ethereum blockchain.
Python Script: Below is a Python script that demonstrates how to connect to a smart contract, read data, and send transactions.

Example Python Program

Here’s an example program to interact with the crowdfunding contract provided earlier:

from web3 import Web3

# Connect to an Ethereum node (e.g., Infura)
infura_url = 'https://mainnet.infura.io/v3/YOUR_INFURA_PROJECT_ID'
web3 = Web3(Web3.HTTPProvider(infura_url))

# Check if the connection is successful
if web3.isConnected():
    print("Connected to Ethereum network")
else:
    print("Failed to connect to Ethereum network")

# Contract ABI and Address
contract_address = '0xYourContractAddress'  # Replace with your deployed contract address
contract_abi = [
    {
        "inputs": [
            {"internalType": "uint256", "name": "_goal", "type": "uint256"},
            {"internalType": "uint256", "name": "_duration", "type": "uint256"}
        ],
        "stateMutability": "nonpayable",
        "type": "constructor"
    },
    {
        "inputs": [],
        "name": "contribute",
        "outputs": [],
        "stateMutability": "payable",
        "type": "function"
    },
    {
        "inputs": [],
        "name": "withdraw",
        "outputs": [],
        "stateMutability": "nonpayable",
        "type": "function"
    },
    {
        "inputs": [],
        "name": "refund",
        "outputs": [],
        "stateMutability": "nonpayable",
        "type": "function"
    },
    {
        "inputs": [],
        "name": "goal",
        "outputs": [{"internalType": "uint256", "name": "", "type": "uint256"}],
        "stateMutability": "view",
        "type": "function"
    },
    {
        "inputs": [],
        "name": "totalFunds",
        "outputs": [{"internalType": "uint256", "name": "", "type": "uint256"}],
        "stateMutability": "view",
        "type": "function"
    }
]

# Create contract instance
contract = web3.eth.contract(address=contract_address, abi=contract_abi)

# Example: Read the current funding goal
goal = contract.functions.goal().call()
print(f"Funding Goal: {goal} wei")

# Example: Read the total funds raised
total_funds = contract.functions.totalFunds().call()
print(f"Total Funds Raised: {total_funds} wei")

# Example: Contribute to the crowdfunding campaign
def contribute(private_key, amount):
    # Address from private key
    account = web3.eth.account.from_key(private_key).address

    # Create a transaction
    txn = contract.functions.contribute().buildTransaction({
        'from': account,
        'value': web3.toWei(amount, 'ether'),  # Amount to send in Ether
        'gas': 2000000,
        'gasPrice': web3.toWei('50', 'gwei'),
        'nonce': web3.eth.getTransactionCount(account),
    })

    # Sign the transaction
    signed_txn = web3.eth.account.sign_transaction(txn, private_key)

    # Send the transaction
    txn_hash = web3.eth.sendRawTransaction(signed_txn.rawTransaction)

    print(f"Transaction sent: {txn_hash.hex()}")

    # Wait for the transaction to be mined
    receipt = web3.eth.waitForTransactionReceipt(txn_hash)
    print(f"Transaction receipt: {receipt}")

# Example usage (Replace with your private key and amount to contribute)
# contribute('0xYourPrivateKey', 0.1)

Explanation

Connecting to the Ethereum Network:
The script connects to the Ethereum network using an Infura endpoint. Replace `'YOUR_INFURA_PROJECT_ID'` with your actual Infura project ID.
Contract Interaction:
The ABI and contract address are used to create a contract instance, which allows you to call its functions.
The example reads the current funding goal and total funds raised using `contract.functions.goal().call()` and `contract.functions.totalFunds().call()`.
Contributing to the Contract:
The `contribute` function demonstrates how to send Ether to the contract using the `contribute` function defined in the Solidity contract.
Replace `'0xYourPrivateKey'` with your Ethereum account's private key to sign the transaction. Never share or expose your private key publicly.
Gas and Gas Price:
`gas` and `gasPrice` parameters define the gas limit and price per unit of gas for the transaction. Adjust these values based on the current network conditions.

Security Considerations

Private Key Security: Never expose private keys in your source code. Use environment variables or secure vaults to manage sensitive information.
Error Handling: Add error handling around blockchain interactions to gracefully manage issues like network failures or insufficient gas.
This script provides a basic template to interact with a smart contract on Ethereum using Python. You can expand this with additional functionality as needed for your application!

Conclusion

Solidity is a powerful tool for creating decentralized applications on Ethereum. Understanding its syntax, key concepts, and best practices can help you write secure and efficient smart contracts. As you advance, you can explore more complex features such as inheritance, libraries, and advanced contract patterns to build sophisticated dApps.