307 lines
11 KiB
PHP
307 lines
11 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
namespace App\Models;
|
||
|
|
|
||
|
|
use CodeIgniter\Database\ConnectionInterface;
|
||
|
|
|
||
|
|
class AutoCRUD_Model {
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
protected $db;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
public function __construct(ConnectionInterface &$db) { $this->db = &$db; }
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
public function schema($table) {
|
||
|
|
$query = "SHOW COLUMNS FROM $table";
|
||
|
|
$result = $this->db->query($query)->getResult();
|
||
|
|
return $result;
|
||
|
|
}//function schema
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Ricava il nome del campo che fa da chiave primaria della tabella
|
||
|
|
* @param string $table nome della tabella nel DB
|
||
|
|
* @return string nome del campo che fa da chiave primaria della tabella
|
||
|
|
*/
|
||
|
|
function get_primary_key_field_name($table) {
|
||
|
|
$query = "SHOW KEYS FROM $table WHERE Key_name = 'PRIMARY'";
|
||
|
|
return $this->db->query($query)->getRow()->Column_name;
|
||
|
|
}//function get_primary_key_field_name
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Calcola il numero di record in base al filtro
|
||
|
|
*/
|
||
|
|
public function countTotalRows($table, $where = null, $request, $schema, $fields) {
|
||
|
|
$count_query = "SELECT COUNT(*) as total FROM " . $table;
|
||
|
|
//get primary_key field name
|
||
|
|
$pk = $this->get_primary_key_field_name($table);
|
||
|
|
|
||
|
|
// Se ho dei filtri where, li decodifico e li inserisco nella query:
|
||
|
|
if ($where) {
|
||
|
|
$count_query .= " WHERE (";
|
||
|
|
$i = 0;
|
||
|
|
foreach ($where as $key => $value) {
|
||
|
|
if ($i > 0)
|
||
|
|
$count_query .= " AND ";
|
||
|
|
|
||
|
|
//Check if operator is different from = (equal sign)
|
||
|
|
$operator_arr = explode(" ", $key);
|
||
|
|
if (isset($operator_arr[1]))
|
||
|
|
$operator = $operator_arr[1];
|
||
|
|
else
|
||
|
|
$operator = "=";
|
||
|
|
|
||
|
|
$count_query .= " `$operator_arr[0]`$operator" . $this->db->escape($value) . " ";
|
||
|
|
$i++;
|
||
|
|
}
|
||
|
|
$count_query .= ")";
|
||
|
|
}
|
||
|
|
|
||
|
|
// Se ho indicato delle stringhe di ricerca nella tabella, le inserisco nella query come LIKE
|
||
|
|
if ($table_search = $request->getPost('table_search')) {
|
||
|
|
$allEmpty = true;
|
||
|
|
$tempQuery = '';
|
||
|
|
|
||
|
|
if ($where)
|
||
|
|
$tempQuery .= " AND ";
|
||
|
|
else
|
||
|
|
$tempQuery .= " WHERE ";
|
||
|
|
$tempQuery .= " ( ";
|
||
|
|
$i = 0;
|
||
|
|
// ciclo su tutti i campi della tabella per verificare se hanno delle stringhe di ricerca
|
||
|
|
foreach ($schema as $column) {
|
||
|
|
if (trim($request->getPost($column->Field)) == '')
|
||
|
|
continue;
|
||
|
|
if ($column->Extra == 'other_table')
|
||
|
|
continue;
|
||
|
|
|
||
|
|
$allEmpty = false;
|
||
|
|
if ($i > 0)
|
||
|
|
$tempQuery .= " OR ";
|
||
|
|
|
||
|
|
|
||
|
|
$tempQuery .= " " . $table_search
|
||
|
|
. "." . $column->Field
|
||
|
|
. " LIKE '%"
|
||
|
|
. trim($this->db->escapeLikeString($request->getPost($column->Field)))
|
||
|
|
. "%' ESCAPE '!'";
|
||
|
|
|
||
|
|
$i++;
|
||
|
|
}
|
||
|
|
$tempQuery .= ")";
|
||
|
|
if (!$allEmpty)
|
||
|
|
$count_query .= $tempQuery;
|
||
|
|
}
|
||
|
|
|
||
|
|
// MRK debug: echo $count_query;
|
||
|
|
|
||
|
|
return $this->db->query($count_query)->getRow()->total;
|
||
|
|
}//function countTotalRows
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Select dei record in base alla query (clausole where principali e quelle di ricerca)
|
||
|
|
*/
|
||
|
|
public function getItems($table, $where = null, $request, $schema, $fields, $order, $offset, $per_page) {
|
||
|
|
$result_query = "SELECT * FROM " . $table;
|
||
|
|
//get primary_key field name
|
||
|
|
$pk = $this->get_primary_key_field_name($table);
|
||
|
|
|
||
|
|
// Ciclo fra le opzioni passate, per unire le tabelle che sono relazionate con questa tramite JOIN:
|
||
|
|
foreach ($fields as $key => $rel_field) {
|
||
|
|
if (isset($rel_field['relation']) && !isset($rel_field['relation']['save_table'])) {
|
||
|
|
$rfield = $rel_field['relation'];
|
||
|
|
$result_query .= " LEFT JOIN " . $rfield['table'] . " ON " . $table . '.' . $key . "=" . $rfield['table'] . "." . $rfield['primary_key'] . " ";
|
||
|
|
//$this->db->join($rfield['table'], $table.'.'.$key.'='.$rfield['table'].'.'.$rfield['primary_key'], 'left');
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Se ho dei filtri where, li decodifico e li inserisco nella query:
|
||
|
|
if ($where) {
|
||
|
|
$result_query .= " WHERE (";
|
||
|
|
$i = 0;
|
||
|
|
foreach ($where as $key => $value) {
|
||
|
|
if ($i > 0)
|
||
|
|
$result_query .= " AND ";
|
||
|
|
|
||
|
|
//Check if operator is different from = (equal sign)
|
||
|
|
$operator_arr = explode(" ", $key);
|
||
|
|
if (isset($operator_arr[1]))
|
||
|
|
$operator = $operator_arr[1];
|
||
|
|
else
|
||
|
|
$operator = "=";
|
||
|
|
|
||
|
|
$count_query .= " `$operator_arr[0]`$operator" . $this->db->escape($value) . " ";
|
||
|
|
$i++;
|
||
|
|
}
|
||
|
|
$result_query .= ")";
|
||
|
|
}
|
||
|
|
|
||
|
|
// Se ho indicato delle stringhe di ricerca nella tabella, le inserisco nella query come LIKE
|
||
|
|
if ($request->getPost('table_search')) {
|
||
|
|
|
||
|
|
$allEmpty = true;
|
||
|
|
$tempQuery = '';
|
||
|
|
$i = 0;
|
||
|
|
if ($where)
|
||
|
|
$tempQuery .= " AND ";
|
||
|
|
else
|
||
|
|
$tempQuery .= " WHERE ";
|
||
|
|
|
||
|
|
$tempQuery .= " ( ";
|
||
|
|
// ciclo su tutti i campi della tabella per verificare se hanno delle stringhe di ricerca
|
||
|
|
foreach ($schema as $column) {
|
||
|
|
|
||
|
|
if ($request->getPost($column->Field) == '')
|
||
|
|
continue;
|
||
|
|
|
||
|
|
$allEmpty = false;
|
||
|
|
$col_search = [];
|
||
|
|
$col_search[] = $column->Field;
|
||
|
|
if (isset($fields[$column->Field]['relation']) && isset($fields[$column->Field]['relation']['save_table'])) {
|
||
|
|
//Search relational table to get the ids of related ids
|
||
|
|
$relField = $fields[$column->Field]['relation'];
|
||
|
|
|
||
|
|
$parent_table = $relField['table'];
|
||
|
|
$relation_table = $relField['save_table'];
|
||
|
|
$joinString = $relation_table . '.' . $relField['child_field'] . '=' . $parent_table . '.' . $relField['primary_key'];
|
||
|
|
$likeColumns = $relField['display'];
|
||
|
|
$likeTerm = $request->getPost($column->Field);
|
||
|
|
//$relselect is optional. when used it will add DISTINCT to prevent dublicates
|
||
|
|
$relSelect = $relation_table . '.' . $relField['parent_field'];
|
||
|
|
$relatedItems = $this->searchRelatedItems($parent_table, $relation_table, $joinString, $likeColumns, $likeTerm, $relSelect);
|
||
|
|
$relatedItemsIdArr = [];
|
||
|
|
if (!$relatedItems)
|
||
|
|
$relatedItemsIdArr = '-1';
|
||
|
|
else {
|
||
|
|
//Create an array of ids for whereIn statement
|
||
|
|
foreach ($relatedItems as $relatedItem) {
|
||
|
|
$relatedItemsIdArr[] = $relatedItem->{$relField['parent_field']};
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($i > 0)
|
||
|
|
$tempQuery .= " AND ";
|
||
|
|
|
||
|
|
if (is_array($relatedItemsIdArr))
|
||
|
|
$relTempQuery = '' . $table . '.' . $pk . ' IN (' . implode(',', $relatedItemsIdArr) . ')';
|
||
|
|
else
|
||
|
|
$relTempQuery = $table . '.' . $pk . ' = ' . $relatedItemsIdArr;
|
||
|
|
|
||
|
|
// echo $tempQuery.'<br>';
|
||
|
|
$tempQuery .= $relTempQuery;
|
||
|
|
// echo $tempQuery;
|
||
|
|
|
||
|
|
$i++;
|
||
|
|
//$allEmpty = false;
|
||
|
|
continue;
|
||
|
|
} else if (isset($fields[$column->Field]['relation'])) {
|
||
|
|
|
||
|
|
$col_search = $fields[$column->Field]['relation']['display'];
|
||
|
|
//check if display is an array of columns
|
||
|
|
if (!is_array($col_search))
|
||
|
|
$col_search[] = $col_search;
|
||
|
|
|
||
|
|
$table_search = $fields[$column->Field]['relation']['table'];
|
||
|
|
} else {
|
||
|
|
$table_search = $table;
|
||
|
|
|
||
|
|
//$col_search[] = $column->Field;
|
||
|
|
}
|
||
|
|
if ($i > 0)
|
||
|
|
$tempQuery .= " AND ";
|
||
|
|
|
||
|
|
|
||
|
|
//For loop is required when search must be performed in multiple relational columns from another table
|
||
|
|
$searchLikeTempQuery = '';
|
||
|
|
$searchLikeTempQueryArr = [];
|
||
|
|
foreach ($col_search as $colToSearch) {
|
||
|
|
$searchLikeTempQueryArr[] = $this->generateLikeClause($table_search, $colToSearch, $request->getPost($column->Field));
|
||
|
|
}
|
||
|
|
|
||
|
|
if (count($col_search) > 1) {
|
||
|
|
$searchLikeTempQuery = implode(' OR ', $searchLikeTempQueryArr);
|
||
|
|
$searchLikeTempQuery = "($searchLikeTempQuery)";
|
||
|
|
} else
|
||
|
|
$searchLikeTempQuery = $searchLikeTempQueryArr[0];
|
||
|
|
|
||
|
|
|
||
|
|
$tempQuery .= $searchLikeTempQuery;
|
||
|
|
|
||
|
|
//$this->db->like($table_search.'.'.$col_search, $request->getPost($column->Field), 'both');
|
||
|
|
$i++;
|
||
|
|
}
|
||
|
|
|
||
|
|
$tempQuery .= isset($searchLikeTempQuery) ? " " : "";
|
||
|
|
if (!$allEmpty)
|
||
|
|
$result_query .= $tempQuery;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($order) {
|
||
|
|
$result_query .= " ORDER BY ";
|
||
|
|
$i = 0;
|
||
|
|
foreach ($order as $ord) {
|
||
|
|
if ($i > 0)
|
||
|
|
$result_query .= ", ";
|
||
|
|
$result_query .= $ord[0] . " " . $ord[1];
|
||
|
|
//$this->db->order_by($ord[0], $ord[1]);
|
||
|
|
$i++;
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
|
||
|
|
$result_query .= " ORDER BY " . $pk . " DESC";
|
||
|
|
//$this->db->order_by($pk, 'DESC');
|
||
|
|
}
|
||
|
|
|
||
|
|
$result_query = rtrim($result_query, ',');
|
||
|
|
$result_query .= " LIMIT $offset, $per_page ";
|
||
|
|
//$this->db->limit($per_page, $offset);
|
||
|
|
|
||
|
|
|
||
|
|
$page_items = $this->db->query($result_query)->getResult();
|
||
|
|
|
||
|
|
|
||
|
|
return $page_items;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Estrae il singolo record in base all'id
|
||
|
|
*/
|
||
|
|
public function getItem($table, $field, $id) {
|
||
|
|
return $this->db->table($table)
|
||
|
|
->where([$field => $id])
|
||
|
|
->get()
|
||
|
|
->getRow();
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
public function generateLikeClause($table_search, $colToSearch, $searchTerm) {
|
||
|
|
return $table_search . "." . $colToSearch
|
||
|
|
. " LIKE '%"
|
||
|
|
. trim($this->db->escapeLikeString($searchTerm))
|
||
|
|
. "%' ESCAPE '!'";
|
||
|
|
}//function generateLikeClause
|
||
|
|
|
||
|
|
|
||
|
|
}//class AutoCRUD_Model
|
||
|
|
|