예제 #1
0
 public function findByAlamat()
 {
     if ($this->before != null and $this->after == null) {
         $sql = "SELECT id_lamp as id, tanggal as tgl, nomor as nomor, hal as hal, asal as alamat, file as file, 'lampiran' as tipe FROM lampiran WHERE asal LIKE '%{$this->keyword}%' AND tanggal>='" . $this->before . "'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratmasuk as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_masuk' as tipe FROM suratmasuk a LEFT JOIN alamat b ON a.asal_surat = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%' AND a.tgl_surat>='" . $this->before . "'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratkeluar as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_keluar' as tipe FROM suratkeluar a LEFT JOIN alamat b ON a.tujuan = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%' AND a.tgl_surat>='" . $this->before . "'";
     } elseif ($this->before == null and $this->after != null) {
         $sql = "SELECT id_lamp as id, tanggal as tgl, nomor as nomor, hal as hal, asal as alamat, file as file, 'lampiran' as tipe FROM lampiran WHERE asal LIKE '%{$this->keyword}%' AND tanggal<='" . $this->after . "'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratmasuk as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_masuk' as tipe FROM suratmasuk a LEFT JOIN alamat b ON a.asal_surat = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%' AND a.tgl_surat<='" . $this->after . "'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratkeluar as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_keluar' as tipe FROM suratkeluar a LEFT JOIN alamat b ON a.tujuan = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%' AND a.tgl_surat<='" . $this->after . "'";
     } elseif ($this->before != null and $this->after != null) {
         $sql = "SELECT id_lamp as id, tanggal as tgl, nomor as nomor, hal as hal, asal as alamat, file as file, 'lampiran' as tipe FROM lampiran WHERE asal LIKE '%{$this->keyword}%'  AND tanggal>='" . $this->before . "' AND tanggal<='" . $this->after . "'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratmasuk as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_masuk' as tipe FROM suratmasuk a LEFT JOIN alamat b ON a.asal_surat = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%' AND a.tgl_surat>='" . $this->before . "' AND a.tgl_surat<='" . $this->after . "'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratkeluar as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_keluar' as tipe FROM suratkeluar a LEFT JOIN alamat b ON a.tujuan = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%' AND a.tgl_surat>='" . $this->before . "' AND a.tgl_surat<='" . $this->after . "'";
     } else {
         $sql = "SELECT id_lamp as id, tanggal as tgl, nomor as nomor, hal as hal, asal as alamat, file as file, 'lampiran' as tipe FROM lampiran WHERE asal LIKE '%{$this->keyword}%'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratmasuk as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_masuk' as tipe FROM suratmasuk a LEFT JOIN alamat b ON a.asal_surat = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%'";
         $sql .= " UNION ";
         $sql .= "SELECT a.id_suratkeluar as id, a.tgl_surat as tgl, a.no_surat as nomor, a.perihal as hal, b.nama_satker as alamat, a.file as file, 'surat_keluar' as tipe FROM suratkeluar a LEFT JOIN alamat b ON a.tujuan = b.kode_satker WHERE b.nama_satker LIKE '%{$this->keyword}%'";
     }
     $sql .= " ORDER BY tgl DESC";
     //        var_dump($sql);
     $data = $this->select($sql);
     $result = array();
     foreach ($data as $val) {
         $cari = new SearchResult();
         $cari->setId($val['id']);
         $cari->setTanggal($val['tgl']);
         $cari->setNomor($val['nomor']);
         $cari->setPerihal($val['hal']);
         $cari->setTipe($val['tipe']);
         $cari->setFile($val['file']);
         $result[] = $cari;
     }
     return $result;
 }