Guida: Creare una Nuova API

Questa guida spiega come creare una nuova API REST per ERP seguendo i pattern stabiliti.

Checklist Rapida

[ ] 1. Model      → src/models/erp/{module}/{Resource}_api_model.php
[ ] 2. Library    → src/libraries/erp/{module}/{Resource}_api_lib.php
[ ] 3. Controller → src/controllers/erp/{module}/{Resource}.php
[ ] 4. Response   → src/response/contexts/erp/Erp{Resource}Responses.php
[ ] 5. Factory    → Aggiungere metodo in ErpApiResponseFactory.php
[ ] 6. Routes     → Aggiungere in src/routes/erp/{module}.php

Database Access Pattern

ADOdb - Non Query Builder

Questo progetto usa ADOdb con query SQL raw, NON il query builder di CodeIgniter.

Connessioni Database

Connessione	Uso	Metodi
$this->db_slave	Lettura (SELECT)	GetArray(), GetRow(), GetOne()
$this->db	Scrittura (INSERT/UPDATE/DELETE)	Execute(), Insert_ID()

Metodi ADOdb

// Lista di record
$rows = $this->db_slave->GetArray($query);  // array di array

// Singolo record
$row = $this->db_slave->GetRow($query);     // array o false

// Singolo valore
$value = $this->db_slave->GetOne($query);   // scalar o false

// Esecuzione query (INSERT/UPDATE/DELETE)
$result = $this->db->Execute($query);       // true/false

// ID dell'ultimo insert
$id = $this->db->Insert_ID();               // integer

Escaping Parametri

Usa sempre la funzione escape() per prevenire SQL injection:

escape($value, 'integer')  // Per numeri interi
escape($value, 'string')   // Per stringhe (aggiunge apici)

Esempio: API Categories

Creiamo l'API per gestire le categorie (/erp/admin/categories).

---

Step 1: Model

Il model gestisce l'accesso al database usando ADOdb con query SQL raw.

// src/models/erp/admin/Categories_api_model.php
<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Categories_api_model extends CI_Model
{
    private const TABLE = 'COM_CATEGORIES';
    private const STATUS_ACTIVE = 1;
    private const STATUS_DELETED = 0;

    /**
     * Get all categories for a company
     */
    public function getAll(int $companyId, ?string $search, bool $includeDeleted): array
    {
        $query = "
            SELECT
                ID_CATEGORY AS id,
                CODE AS code,
                DESCRIPTION AS description,
                STATUS AS status,
                INS_USER AS createdBy,
                INS_DATE AS createdAt,
                CASE WHEN STATUS = 0 THEN 1 ELSE 0 END AS isDeleted
            FROM " . self::TABLE . "
            WHERE ID_COMPANY = " . escape($companyId, 'integer');

        if (!$includeDeleted) {
            $query .= " AND STATUS = " . self::STATUS_ACTIVE;
        }

        if (!empty($search)) {
            $searchEscaped = escape('%' . strtolower((string) $search) . '%', 'string');
            $query .= " AND (LOWER(CODE) LIKE " . $searchEscaped;
            $query .= " OR LOWER(DESCRIPTION) LIKE " . $searchEscaped . ")";
        }

        $query .= " ORDER BY DESCRIPTION ASC";

        return $this->db_slave->GetArray($query);
    }

    /**
     * Get single category by ID
     */
    public function getById(int $companyId, int $id): ?array
    {
        $query = "
            SELECT
                ID_CATEGORY AS id,
                CODE AS code,
                DESCRIPTION AS description,
                STATUS AS status,
                INS_USER AS createdBy,
                INS_DATE AS createdAt,
                CASE WHEN STATUS = 0 THEN 1 ELSE 0 END AS isDeleted
            FROM " . self::TABLE . "
            WHERE ID_COMPANY = " . escape($companyId, 'integer') . "
            AND ID_CATEGORY = " . escape($id, 'integer');

        $result = $this->db_slave->GetRow($query);

        return $result ?: null;
    }

    /**
     * Check if code exists
     */
    public function existsByCode(int $companyId, string $code, ?int $excludeId = null): bool
    {
        $query = "
            SELECT 1
            FROM " . self::TABLE . "
            WHERE ID_COMPANY = " . escape($companyId, 'integer') . "
            AND CODE = " . escape($code, 'string') . "
            AND STATUS = " . self::STATUS_ACTIVE;

        if ($excludeId !== null) {
            $query .= " AND ID_CATEGORY != " . escape($excludeId, 'integer');
        }

        return $this->db_slave->GetOne($query) !== false;
    }

    /**
     * Insert new category
     */
    public function insert(int $companyId, int $userId, string $code, string $description): ?array
    {
        $query = "
            INSERT INTO " . self::TABLE . " (
                ID_COMPANY,
                CODE,
                DESCRIPTION,
                INS_USER,
                INS_DATE,
                STATUS
            ) VALUES (
                " . escape($companyId, 'integer') . ",
                " . escape($code, 'string') . ",
                " . escape($description, 'string') . ",
                " . escape($userId, 'integer') . ",
                NOW(),
                " . self::STATUS_ACTIVE . "
            )
        ";

        $result = $this->db->Execute($query);

        if ($result === false) {
            return null;
        }

        $insertId = $this->db->Insert_ID();

        return $this->getById($companyId, $insertId);
    }

    /**
     * Update category
     */
    public function update(int $id, ?string $code, ?string $description): ?array
    {
        $updates = [];

        if ($code !== null) {
            $updates[] = "CODE = " . escape($code, 'string');
        }
        if ($description !== null) {
            $updates[] = "DESCRIPTION = " . escape($description, 'string');
        }

        if (empty($updates)) {
            $query = "SELECT ID_COMPANY FROM " . self::TABLE . " WHERE ID_CATEGORY = " . escape($id, 'integer');
            $companyId = $this->db->GetOne($query);
            return $companyId ? $this->getById((int) $companyId, $id) : null;
        }

        $updates[] = "LAST_MOD_DATE = NOW()";

        $query = "
            UPDATE " . self::TABLE . "
            SET " . implode(', ', $updates) . "
            WHERE ID_CATEGORY = " . escape($id, 'integer');

        $result = $this->db->Execute($query);

        if ($result === false) {
            return null;
        }

        $query = "SELECT ID_COMPANY FROM " . self::TABLE . " WHERE ID_CATEGORY = " . escape($id, 'integer');
        $companyId = $this->db->GetOne($query);

        return $companyId ? $this->getById((int) $companyId, $id) : null;
    }

    /**
     * Soft delete category
     */
    public function softDelete(int $id, int $userId): ?array
    {
        $query = "
            UPDATE " . self::TABLE . "
            SET
                STATUS = " . self::STATUS_DELETED . ",
                LAST_MOD_USER = " . escape($userId, 'integer') . ",
                LAST_MOD_DATE = NOW()
            WHERE ID_CATEGORY = " . escape($id, 'integer');

        $result = $this->db->Execute($query);

        if ($result === false) {
            return null;
        }

        $query = "SELECT ID_COMPANY FROM " . self::TABLE . " WHERE ID_CATEGORY = " . escape($id, 'integer');
        $companyId = $this->db->GetOne($query);

        return $companyId ? $this->getById((int) $companyId, $id) : null;
    }
}

---

Step 2: Library

La library contiene la business logic.

// src/libraries/erp/admin/Categories_api_lib.php
<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Categories_api_lib
{
    private $CI;
    private const CONTEXT = 'categories';

    public function __construct()
    {
        $this->CI = &get_instance();
        $this->CI->load->model('erp/admin/Categories_api_model');
    }

    public function list(int $companyId, ?string $search, bool $includeDeleted): ServiceResponse
    {
        $categories = $this->CI->categories_api_model->getAll($companyId, $search, $includeDeleted);

        return ServiceResponseFactory::ok($categories, self::CONTEXT, 'list');
    }

    public function get(int $companyId, int $id): ServiceResponse
    {
        $category = $this->CI->categories_api_model->getById($companyId, $id);

        if (!$category) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'notFound',
                ['id' => $id],
                ServiceErrorType::NotFound,
                'Categoria non trovata'
            );
        }

        return ServiceResponseFactory::ok($category, self::CONTEXT, 'get');
    }

    public function create(int $companyId, int $userId, string $code, string $description): ServiceResponse
    {
        // Validate uniqueness
        if ($this->CI->categories_api_model->existsByCode($companyId, $code)) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'duplicateCode',
                ['field' => 'code', 'value' => $code],
                ServiceErrorType::Validation,
                'Codice categoria gia esistente'
            );
        }

        $category = $this->CI->categories_api_model->insert($companyId, $userId, $code, $description);

        if (!$category) {
            return ServiceResponseFactory::failDbInsert(self::CONTEXT);
        }

        return ServiceResponseFactory::ok($category, self::CONTEXT, 'create');
    }

    public function update(int $companyId, int $id, ?string $code, ?string $description): ServiceResponse
    {
        $category = $this->CI->categories_api_model->getById($companyId, $id);

        if (!$category) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'notFound',
                ['id' => $id],
                ServiceErrorType::NotFound,
                'Categoria non trovata'
            );
        }

        if ($category['isDeleted']) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'alreadyDeleted',
                ['id' => $id],
                ServiceErrorType::Conflict,
                'Impossibile modificare una categoria eliminata'
            );
        }

        // Validate uniqueness (excluding current)
        if ($code && $this->CI->categories_api_model->existsByCode($companyId, $code, $id)) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'duplicateCode',
                ['field' => 'code', 'value' => $code],
                ServiceErrorType::Validation,
                'Codice categoria gia esistente'
            );
        }

        $updated = $this->CI->categories_api_model->update($id, $code, $description);

        return ServiceResponseFactory::ok($updated, self::CONTEXT, 'update');
    }

    public function delete(int $companyId, int $userId, int $id): ServiceResponse
    {
        $category = $this->CI->categories_api_model->getById($companyId, $id);

        if (!$category) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'notFound',
                ['id' => $id],
                ServiceErrorType::NotFound,
                'Categoria non trovata'
            );
        }

        if ($category['isDeleted']) {
            return ServiceResponseFactory::fail(
                self::CONTEXT,
                'alreadyDeleted',
                ['id' => $id],
                ServiceErrorType::Conflict,
                'Categoria gia eliminata'
            );
        }

        $this->CI->categories_api_model->softDelete($id, $userId);

        return ServiceResponseFactory::ok(['id' => $id], self::CONTEXT, 'delete');
    }
}

---

Step 3: Controller

Il controller gestisce HTTP request/response.

// src/controllers/erp/admin/Categories.php
<?php

defined('BASEPATH') or exit('No direct script access allowed');

require APPPATH . 'libraries/erp/Erp_controller.php';

class Categories extends Erp_controller
{
    private $lib;
    protected $ctx;

    public function __construct()
    {
        parent::__construct();

        $this->CI->load->library('Session_context');
        $this->ctx = $this->CI->session_context;

        $this->CI->load->library('erp/admin/Categories_api_lib');
        $this->lib = &$this->CI->categories_api_lib;
    }

    /**
     * GET /erp/admin/categories
     */
    public function index_get()
    {
        $params = $this->get();

        $response = $this->lib->list(
            $this->ctx->getCompanyId(),
            $params['search'] ?? null,
            filter_var($params['includeDeleted'] ?? false, FILTER_VALIDATE_BOOLEAN)
        );

        ErpApiResponseFactory::categories()->auto($response)->toJson();
    }

    /**
     * POST /erp/admin/categories
     */
    public function index_post()
    {
        $params = $this->post();

        $schema = QueryStringValidator::merge(
            QueryStringValidator::requiredString('code'),
            QueryStringValidator::requiredString('description')
        );

        QueryStringValidator::validate($params, $schema, ErpApiResponseFactory::categories());

        $response = $this->lib->create(
            $this->ctx->getCompanyId(),
            $this->ctx->getUserId(),
            trim($params['code']),
            trim($params['description'])
        );

        ErpApiResponseFactory::categories()->auto($response)->toJson();
    }

    /**
     * GET /erp/admin/categories/{id}
     */
    public function id_get($id)
    {
        $response = $this->lib->get(
            $this->ctx->getCompanyId(),
            (int) $id
        );

        ErpApiResponseFactory::categories()->auto($response)->toJson();
    }

    /**
     * PUT /erp/admin/categories/{id}
     */
    public function id_put($id)
    {
        $params = $this->put();

        $response = $this->lib->update(
            $this->ctx->getCompanyId(),
            (int) $id,
            isset($params['code']) ? trim($params['code']) : null,
            isset($params['description']) ? trim($params['description']) : null
        );

        ErpApiResponseFactory::categories()->auto($response)->toJson();
    }

    /**
     * DELETE /erp/admin/categories/{id}
     */
    public function id_delete($id)
    {
        $response = $this->lib->delete(
            $this->ctx->getCompanyId(),
            $this->ctx->getUserId(),
            (int) $id
        );

        ErpApiResponseFactory::categories()->auto($response)->toJson();
    }
}

---

Step 4: Response Context

// src/response/contexts/erp/ErpCategoriesResponses.php
<?php

class ErpCategoriesResponses extends ErpCommonResponses
{
    protected string $responseContext = 'categories';
}

---

Step 5: Response Factory

Aggiungere il metodo in ErpApiResponseFactory.php:

// src/response/ErpApiResponseFactory.php

public static function categories()
{
    require_once 'contexts/erp/ErpCategoriesResponses.php';
    return new ErpCategoriesResponses();
}

---

Step 6: Routes

Aggiungere le route in src/routes/erp/admin.php:

// Categories API
$route['erp/admin/categories/(:num)'] = 'erp/admin/categories/id/$1';

Nota sulle Routes

Solo le route con parametri URL richiedono definizione esplicita. index_get e index_post funzionano automaticamente su /erp/admin/categories.

---

Pattern Comuni

Validazione Input

$schema = QueryStringValidator::merge(
    QueryStringValidator::requiredString('code'),
    QueryStringValidator::requiredString('description'),
    QueryStringValidator::requiredInt('parentId')
);

QueryStringValidator::validate($params, $schema, ErpApiResponseFactory::categories());

Check Dipendenze prima di Delete

public function checkDependencies(int $id): array
{
    // Count articles using this category
    $articlesQuery = "
        SELECT COUNT(*) AS cnt
        FROM COM_ARTICLES
        WHERE ID_CATEGORY = " . escape($id, 'integer');

    $articlesCount = (int) $this->db_slave->GetOne($articlesQuery);

    return [
        'total' => $articlesCount,
        'articlesCount' => $articlesCount,
    ];
}

Soft Delete

// Nel model
public function softDelete(int $id, int $userId): ?array
{
    $query = "
        UPDATE " . self::TABLE . "
        SET
            STATUS = " . self::STATUS_DELETED . ",
            LAST_MOD_USER = " . escape($userId, 'integer') . ",
            LAST_MOD_DATE = NOW()
        WHERE ID_CATEGORY = " . escape($id, 'integer');

    $result = $this->db->Execute($query);

    if ($result === false) {
        return null;
    }

    // Fetch and return updated record
    $query = "SELECT ID_COMPANY FROM " . self::TABLE . " WHERE ID_CATEGORY = " . escape($id, 'integer');
    $companyId = $this->db->GetOne($query);

    return $companyId ? $this->getById((int) $companyId, $id) : null;
}

Restore

public function restore(int $id, int $userId): ?array
{
    $query = "
        UPDATE " . self::TABLE . "
        SET
            STATUS = " . self::STATUS_ACTIVE . ",
            RESTORED_BY = " . escape($userId, 'integer') . ",
            RESTORED_AT = NOW()
        WHERE ID_CATEGORY = " . escape($id, 'integer');

    $result = $this->db->Execute($query);

    if ($result === false) {
        return null;
    }

    $query = "SELECT ID_COMPANY FROM " . self::TABLE . " WHERE ID_CATEGORY = " . escape($id, 'integer');
    $companyId = $this->db->GetOne($query);

    return $companyId ? $this->getById((int) $companyId, $id) : null;
}

---

Best Practices

1. Sempre filtrare per Company

// SEMPRE includere ID_COMPANY nelle query
$query = "SELECT * FROM " . self::TABLE . " WHERE ID_COMPANY = " . escape($companyId, 'integer');

2. Validare unicita escludendo il record corrente

// Nel model
public function existsByCode(int $companyId, string $code, ?int $excludeId = null): bool
{
    $query = "
        SELECT 1
        FROM " . self::TABLE . "
        WHERE ID_COMPANY = " . escape($companyId, 'integer') . "
        AND CODE = " . escape($code, 'string') . "
        AND STATUS = " . self::STATUS_ACTIVE;

    if ($excludeId !== null) {
        $query .= " AND ID_CATEGORY != " . escape($excludeId, 'integer');
    }

    return $this->db_slave->GetOne($query) !== false;
}

// Nella library
if ($this->CI->categories_api_model->existsByCode($companyId, $code, $excludeId)) {
    return ServiceResponseFactory::fail('categories', 'duplicateCode', ...);
}

3. Usare ServiceResponseFactory per le risposte

// Success
return ServiceResponseFactory::ok($data, 'context', 'action');

// Error
return ServiceResponseFactory::fail('context', 'errorCode', $data, ServiceErrorType::NotFound);

4. Trim degli input stringa

trim($params['code'])

5. Cast esplicito degli ID

(int) $id
(int) $row['ID_CATEGORY']

6. Tracciare utente nelle modifiche

// Nelle query INSERT/UPDATE
$query = "
    INSERT INTO " . self::TABLE . " (
        ...,
        INS_USER,
        INS_DATE
    ) VALUES (
        ...,
        " . escape($userId, 'integer') . ",
        NOW()
    )
";

// Per UPDATE
$query = "
    UPDATE " . self::TABLE . "
    SET
        ...,
        LAST_MOD_USER = " . escape($userId, 'integer') . ",
        LAST_MOD_DATE = NOW()
    WHERE ...
";

7. Usare db_slave per le letture

// Lettura - usa sempre db_slave
$data = $this->db_slave->GetArray($query);

// Scrittura - usa db
$result = $this->db->Execute($query);

---

Accesso Cross-Library (Pattern Filtri)

Quando una libreria necessita di dati da un'altra anagrafica (es. brands per filtri articoli), utilizzare il pattern centralizzato invece di duplicare le query SQL.

Pattern Corretto

1. Ogni libreria anagrafica DEVE esporre un metodo getForFilters()
2. Le altre librerie caricano la libreria e usano il metodo

Metodo Standard getForFilters()

Ogni libreria anagrafica deve implementare:

// Nel model (es. Brands_api_model.php)
public function getActive(int $companyId): array
{
    $query = "
        SELECT
            ID_BRAND AS id,
            C_BRAND AS code,
            D_BRAND AS description
        FROM COM_BRANDS
        WHERE ID_COMPANY = " . escape($companyId, 'integer') . "
        AND STATUS = " . self::STATUS_ACTIVE . "
        ORDER BY D_BRAND ASC";

    return $this->db_slave->GetArray($query);
}

// Nella library (es. Brands_api_lib.php)
public function getForFilters(int $companyId): array
{
    return $this->CI->brands_api_model->getActive($companyId);
}

Utilizzo da Altra Libreria

// In un'altra libreria (es. Articles_api_lib.php)
class Articles_api_lib
{
    public function __construct()
    {
        $this->CI = &get_instance();

        // Carica librerie per accesso centralizzato ai filtri
        $this->CI->load->library('erp/admin/Brands_api_lib', [], 'brands_lib');
        // $this->CI->load->library('erp/admin/Categories_api_lib', [], 'categories_lib');
    }

    public function getFilters(int $companyId): ServiceResponse
    {
        $filters = [
            'brands' => $this->CI->brands_lib->getForFilters($companyId),
            // 'categories' => $this->CI->categories_lib->getForFilters($companyId),
        ];

        return ServiceResponseFactory::ok($filters, 'articles', 'filters');
    }
}

Vantaggi

Aspetto	Pattern Corretto	Pattern Errato
Query	Centralizzata in un punto	Duplicata in ogni libreria
Manutenzione	Modifica in un solo file	Modifica in N file
Logica	Soft-delete gestito automaticamente	Rischio di dimenticare STATUS = 1
Performance	Query ottimizzata (solo campi necessari)	Query pesante con tutti i campi

File Esempio

Vedi src/libraries/erp/examples/Filters_example_lib.php per un esempio completo commentato.