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;
}
