146 lines
3.9 KiB
PHP
146 lines
3.9 KiB
PHP
<?php
|
|
require_once "Database.php";
|
|
|
|
class Record {
|
|
|
|
protected $conn;
|
|
|
|
public function __construct(){
|
|
$db = new Database();
|
|
$this->conn = $db->connect();
|
|
}
|
|
|
|
/* FETCH RECORDS WITH FILTER + SEARCH */
|
|
public function getRecords($status="",$search="",$page=1,$perPage=10,$sort="id",$order="desc")
|
|
{
|
|
|
|
$conditions = [];
|
|
|
|
// status filter
|
|
if($status=="trash"){
|
|
$conditions[]="is_deleted=1";
|
|
}else{
|
|
$conditions[]="is_deleted=0";
|
|
if($status!=""){
|
|
$status = $this->conn->real_escape_string($status);
|
|
$conditions[]="status='$status'";
|
|
}
|
|
}
|
|
|
|
// search filter
|
|
if($search!=""){
|
|
$search = $this->conn->real_escape_string($search);
|
|
$conditions[]="(
|
|
CAST(id AS CHAR) LIKE '%$search%' OR
|
|
LOWER(name) LIKE LOWER('%$search%') OR
|
|
LOWER(email) LIKE LOWER('%$search%') OR
|
|
CAST(phone AS CHAR) LIKE '%$search%'
|
|
)";
|
|
}
|
|
|
|
$where = count($conditions)>0 ? "WHERE ".implode(" AND ",$conditions):"";
|
|
|
|
$start = ($page - 1) * $perPage;
|
|
|
|
$allowedSort = ["id","name","email","status"];
|
|
|
|
if(!in_array($sort,$allowedSort))
|
|
{
|
|
$sort="id";
|
|
}
|
|
|
|
$order = strtolower($order)=="asc" ? "ASC":"DESC";
|
|
|
|
if($sort == "status"){
|
|
if($order == "ASC"){
|
|
// Inactive first, then Active
|
|
$orderBy = "FIELD(status,'inactive','active'), id DESC";
|
|
}else{
|
|
// Active first, then Inactive
|
|
$orderBy = "FIELD(status,'active','inactive'), id DESC";
|
|
}
|
|
}else{
|
|
$orderBy = "$sort $order";
|
|
}
|
|
|
|
|
|
$sql="SELECT * FROM records $where ORDER BY $orderBy LIMIT $perPage OFFSET $start";
|
|
|
|
|
|
return $this->conn->query($sql);
|
|
|
|
}
|
|
|
|
/* TOTAL PAGES */
|
|
public function countFiltered($status="",$search=""){
|
|
|
|
$conditions = [];
|
|
|
|
if($status=="trash"){
|
|
$conditions[]="is_deleted=1";
|
|
}else{
|
|
$conditions[]="is_deleted=0";
|
|
if($status!=""){
|
|
$status = $this->conn->real_escape_string($status);
|
|
$conditions[]="status='$status'";
|
|
}
|
|
}
|
|
|
|
if($search!=""){
|
|
$search = $this->conn->real_escape_string($search);
|
|
$conditions[]="(
|
|
CAST(id AS CHAR) LIKE '%$search%' OR
|
|
LOWER(name) LIKE LOWER('%$search%') OR
|
|
LOWER(email) LIKE LOWER('%$search%') OR
|
|
CAST(phone AS CHAR) LIKE '%$search%'
|
|
)";
|
|
}
|
|
|
|
$where = count($conditions)>0 ? "WHERE ".implode(" AND ",$conditions):"";
|
|
|
|
$result = $this->conn->query("SELECT COUNT(*) as total FROM records $where");
|
|
return $result->fetch_assoc()['total'];
|
|
}
|
|
|
|
|
|
/* SOFT DELETE */
|
|
public function deleteRecord($id){
|
|
$id = $this->conn->real_escape_string($id);
|
|
return $this->conn->query("UPDATE records SET is_deleted=1 WHERE id='$id'");
|
|
}
|
|
|
|
/* RESTORE RECORD */
|
|
public function restoreRecord($id){
|
|
$id = $this->conn->real_escape_string($id);
|
|
return $this->conn->query("UPDATE records SET is_deleted=0 WHERE id='$id'");
|
|
}
|
|
|
|
/* BULK DELETE */
|
|
public function bulkDelete($ids){
|
|
foreach($ids as $id){
|
|
$this->deleteRecord($id);
|
|
}
|
|
}
|
|
|
|
/* TOTAL RECORDS */
|
|
public function countTotal(){
|
|
$result = $this->conn->query("SELECT COUNT(*) as total FROM records WHERE is_deleted=0");
|
|
return $result->fetch_assoc()['total'];
|
|
}
|
|
|
|
/* ACTIVE RECORDS */
|
|
public function countActive(){
|
|
$result = $this->conn->query("SELECT COUNT(*) as active FROM records WHERE status='active' AND is_deleted=0");
|
|
return $result->fetch_assoc()['active'];
|
|
}
|
|
|
|
/* INACTIVE / PENDING RECORDS */
|
|
public function countInactive(){
|
|
$result = $this->conn->query("SELECT COUNT(*) as inactive FROM records WHERE status='inactive' AND is_deleted=0");
|
|
return $result->fetch_assoc()['inactive'];
|
|
}
|
|
|
|
|
|
}
|
|
?>
|