public static function all($pelajar = null, $filter2 = array()) { $defaultFilter = array('order' => 'permohonan.id', 'by' => 'DESC', 'limit' => 10, 'page' => 1); $filter = array_merge($defaultFilter, $filter2); $param = array(); $strSQL = "SELECT permohonan.*, status_permohonan.nama as status_mohon, sekolah.nama as nama_sekolah, subjek.nama as nama_subjek, pelajar.nama_penuh, pelajar.no_matrik\n\t\tFROM permohonan \n\t\tINNER JOIN status_permohonan ON status_permohonan.id = permohonan.status \n\t\tINNER JOIN sekolah ON sekolah.id = permohonan.id_sekolah \n\t\tINNER JOIN subjek ON subjek.id = permohonan.id_subjek \n\t\tINNER JOIN pelajar ON pelajar.id = permohonan.id_pelajar \n\t\tWHERE 1=1"; $countSQL = "SELECT COUNT(*) as totalItem FROM permohonan \n\t\tINNER JOIN pelajar ON pelajar.id = permohonan.id_pelajar \n\t\tINNER JOIN sekolah ON sekolah.id = permohonan.id_sekolah \n\t\tWHERE 1=1"; $filterSQL = ""; if (!empty($pelajar)) { $filterSQL .= " AND id_pelajar=:id_pelajar"; $param[':id_pelajar'] = $pelajar; } else { } // where clause if (!empty($filter['nama_pelajar'])) { $filterSQL .= " AND pelajar.nama_penuh LIKE :namapelajar"; $param[':namapelajar'] = "%{$filter['nama_pelajar']}%"; } if (!empty($filter['no_matrik'])) { $filterSQL .= " AND pelajar.no_matrik LIKE :nomatrik"; $param[':nomatrik'] = "%{$filter['no_matrik']}%"; } if (!empty($filter['sekolah'])) { $filterSQL .= " AND sekolah.id=:sekolah"; $param[':sekolah'] = $filter['sekolah']; } if (!empty($filter['status'])) { $filterSQL .= " AND permohonan.status=:status"; $param[':status'] = $filter['status']; } // $countSQL .= $filterSQL; $statement2 = DbConn::$dbConn->prepare($countSQL); $statement2->execute($param); $rowTotalITem = $statement2->fetch(PDO::FETCH_ASSOC); self::$totalItem = $rowTotalITem['totalItem']; // // :order => ID, name etc, :by => ASC, DESC if (!empty($filter['order']) && !empty($filter['by'])) { $filterSQL .= " ORDER BY :order :by"; $param[':order'] = $filter['order']; $param[':by'] = $filter['by']; } $offset = ($filter['page'] - 1) * $filter['limit']; $filterSQL .= " LIMIT {$filter['limit']} OFFSET {$offset}"; $strSQL .= $filterSQL; // echo $strSQL; // var_dump( $param ); $statement = DbConn::$dbConn->prepare($strSQL); $statement->execute($param); $applications = array(); while ($row = $statement->fetch(PDO::FETCH_ASSOC)) { $applications[] = $row; } return $applications; }