من یه همچین ساختاری برای هر جدولم دارم..
شاید به دردت بخوره
namespace entities{
class ticket {
private $mysqli;
public $id;
public $suggested_time;
public $error;
public $flage = true;
private $tbl_name ;
public function __construct($commit = FALSE) {
$this->tbl_name = TBL_PREFIX.'tickets';
$this->mysqli = new \MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$this->mysqli->set_charset("utf8");
if ($commit)
$this->mysqli->query('start transaction;');
}
public function commit($var) {
if ($var)
$this->mysqli->query('commit;');
elseif (!$var)
$this->mysqli->query('rollback;');
}
public function save(
$id=NULL
,$title=NULL
,$body=NULL
,$register_user_id=NULL
,$register_date=NULL
,$ticket_tags=NULL
,$state_id=NULL
,$is_emergency=NULL
,$role_id=NULL
,$close_date=NULL
,$resource_id=NULL
,$deadline=NULL
,$suggested_time=NULL
,$parent_id=NULL
,$duration=NULL
,$worth=NULL
,$link=NULL
,$color=NULL
,$upload=NULL
,$concat=TRUE
,$old_ticket_id=NULL){
if($id==NULL):
$sql="INSERT INTO $this->tbl_name(
title ";
if($body != NULL) $sql .= " ,body";
if($register_user_id != NULL) $sql .= " ,register_user_id" ;
if($register_date != NULL) $sql .= " ,register_date" ;
if($ticket_tags != NULL) $sql .= " ,ticket_tags" ;
if($state_id != NULL) $sql .= " ,state_id" ;
if($is_emergency != NULL && $is_emergency != 'FALSE') $sql .= " ,is_emergency" ;
elseif($is_emergency == 'FALSE') $sql .= " ,is_emergency" ;
if($role_id != NULL) $sql .= " ,role_id" ;
if($close_date != NULL) $sql .= " ,close_date" ;
if($resource_id != NULL) $sql .= " ,resource_id" ;
if($deadline != NULL) $sql .= " ,deadline" ;
if($suggested_time != NULL) $sql .= " ,suggested_time" ;
if($parent_id != NULL ) $sql .= " ,parent_id" ;
if($duration != NULL) $sql .= " ,duration" ;
if($worth != NULL) $sql .= " ,worth" ;
if($link != NULL) $sql .= " ,link" ;
if($color != NULL) $sql .= " ,color" ;
if($old_ticket_id != NULL) $sql .= " ,old_ticket_id" ;
$sql .= " ) VALUES (
'$title' ";
if($body != NULL) $sql .= " ,'$body'" ;
if($register_user_id != NULL) $sql .= " , $register_user_id" ;
if($register_date != NULL) $sql .= " ,'$register_date'" ;
if($ticket_tags != NULL) $sql .= " ,'$ticket_tags'" ;
if($state_id != NULL) $sql .= " , $state_id" ;
if($is_emergency != NULL && $is_emergency != 'FALSE' ) $sql .= " , $is_emergency" ;
elseif($is_emergency == 'FALSE') $sql .= " , 0 " ;
if($role_id != NULL) $sql .= " , $role_id" ;
if($close_date != NULL) $sql .= " , '$close_date' " ;
if($resource_id != NULL) $sql .= " , $resource_id" ;
if($deadline != NULL) $sql .= " , '$deadline' " ;
if($suggested_time != NULL) $sql .= " ,'$suggested_time' " ;
if($parent_id != NULL ) $sql .= " , $parent_id" ;
if($duration != NULL) $sql .= " , $duration " ;
if($worth != NULL) $sql .= " , $worth" ;
if($link != NULL) $sql .= " , '$link'" ;
if($color != NULL ) $sql .= " , '$color'" ;
if($old_ticket_id != NULL ) $sql .= " , $old_ticket_id" ;
$sql .= " )";
elseif($id != NULL): //upduration
$sql="UPDATE $this->tbl_name SET id = $id ";
if($title != NULL) $sql .= " , title = '$title'";
if($body != NULL && $body != 'NULL')$sql .= " , body = '$body'";
if($body == 'NULL') $sql .= " , body = NULL ";
if($register_user_id != NULL) $sql .= " , register_user_id = $register_user_id";
if($register_date != NULL) $sql .= " , register_date = '$register_date'";
if($ticket_tags != NULL) $sql .= " , ticket_tags = '$ticket_tags'";
if($state_id != NULL) $sql .= " , state_id = $state_id";
if($is_emergency != NULL) $sql .= " , is_emergency = $is_emergency";
if($role_id != NULL) $sql .= " , role_id = $role_id";
if($close_date != NULL) $sql .= " , close_date = '$close_date'";
if($resource_id != NULL) $sql .= " , resource_id = $resource_id";
if($deadline != NULL && $deadline != 'FALSE') $sql .= " , deadline = '$deadline'";
elseif($deadline == 'FALSE') $sql .= " , deadline = NULL";
if($suggested_time != NULL) $sql .= " , suggested_time = $suggested_time";
if($parent_id != NULL ) $sql .= " , parent_id = $parent_id";
if($duration != NULL) $sql .= " , duration = $duration";
if($worth != NULL) $sql .= " , worth = $worth";
if($link != NULL && $link != 'FALSE') $sql .= " , link = '$link'";
elseif($link == 'FALSE') $sql .= " , link = NULL ";
if($color != NULL && $color != 'FALSE') $sql .= " , color = '$color'";
elseif($color == 'FALSE') $sql .= " , color = '0' ";
if($upload != NULL && $concat) $sql .= " , upload = CONCAT(upload ,',$upload') ";
elseif($upload != NULL && !$concat)$sql .= " , upload = '$upload' ";
if($old_ticket_id != NULL) $sql .= " , old_ticket_id = $old_ticket_id";
$sql .= " WHERE id = $id ";
endif;
$this->mysqli->query($sql);
$this->error = $this->mysqli->error;
if (!empty($this->mysqli->error)) {
$this->flage = FALSE;
}
if ($id == NULL)
return $this->mysqli->insert_id;
else
return $this->mysqli->error;
}
public function delete($ids=NULL,$role_id=NULL,$state_id=NULL)
{
$sql = "DELETE FROM $this->tbl_name WHERE 1=1 ";
if($ids != NULL) $sql .= " AND $this->tbl_name.id in($ids)";
if($role_id != NULL) $sql .= " AND $this->tbl_name.role_id = $role_id";
if($state_id != NULL) $sql .= " AND ($this->tbl_name.state_id = $state_id)";
$this->mysqli->query($sql);
}
public function get(
$id=NULL
,$title=NULL
,$body=NULL
,$register_user_id=NULL
,$register_date=NULL
,$ticket_tags=NULL
,$state_id=NULL
,$is_emergency=NULL
,$role_id=NULL
,$close_date=NULL
,$resource_id=NULL
,$deadline=NULL
,$suggested_time=NULL
,$parent_id=NULL
,$duration=NULL
,$worth=NULL
,$show = FALSE
,$plus = NULL
,$link = NULL
,$color=NULL
,$old_ticket_id=NULL)
{
$sql="SELECT * FROM $this->tbl_name WHERE 1=1";
$custom = '';
if($show==TRUE){
$custom .= " INNER JOIN ".TBL_PREFIX."roles ON ".TBL_PREFIX."tickets.role_id = ".TBL_PREFIX."roles.id ";
$sql="SELECT $this->tbl_name.id , $this->tbl_name.title , body , register_user_id ,
register_date , role_id , ".TBL_PREFIX."roles.title as role_title , suggested_time ,
close_date , resource_id ,is_emergency , ticket_tags , worth , state_id , color , deadline ,
duration
FROM $this->tbl_name $custom WHERE 1=1 ";
}
if($id != NULL) $sql .= " AND $this->tbl_name.id = $id";
if($title != NULL) $sql .= " AND $this->tbl_name.title = '$title'";
if($body != NULL) $sql .= " AND $this->tbl_name.body = '$body'";
if($register_user_id != NULL) $sql .= " AND $this->tbl_name.register_user_id = $register_user_id";
if($register_date != NULL) $sql .= " AND $this->tbl_name.register_date = '$register_date'";
if($role_id != NULL) $sql .= " AND $this->tbl_name.role_id = $role_id";
if($close_date != NULL) $sql .= " AND $this->tbl_name.close_date = $close_date";
if($resource_id != NULL) $sql .= " AND $this->tbl_name.resource_id = '$resource_id'";
if($ticket_tags != NULL) $sql .= " AND $this->tbl_name.ticket_tags = '$ticket_tags'";
if($worth != NULL) $sql .= " AND $this->tbl_name.worth = $worth";
if($state_id != NULL) $sql .= " AND ($this->tbl_name.state_id = $state_id)";
if($is_emergency != NULL) $sql .= " AND $this->tbl_name.is_emergency = $is_emergency";
if($deadline != NULL) $sql .= " AND $this->tbl_name.deadline = '$deadline'";
if($suggested_time != NULL) $sql .= " AND $this->tbl_name.suggested_time = $suggested_time";
if($parent_id != NULL && $parent_id != -1 ) $sql .= " AND $this->tbl_name.parent_id = $parent_id ";
elseif($parent_id == -1 ) $sql .= " AND ($this->tbl_name.parent_id IS NULL OR $this->tbl_name.parent_id = 0 )";
if($duration != NULL) $sql .= " AND $this->tbl_name.duration = $duration";
if($link != NULL) $sql .= " AND $this->tbl_name.link = '$link'";
if($color != NULL) $sql .= " AND $this->tbl_name.color = '$color'";
if($old_ticket_id != NULL) $sql .= " AND $this->tbl_name.old_ticket_id = '$old_ticket_id'";
return $this->mysqli->query($sql.$plus." order by is_emergency DESC ");
}
}
}