Obinote: 2012

Kamis, Februari 16

Membuat Laporan Excel dengan PHP

sumber : blog.rosihanari.net/

###Database### 

CREATE TABLE nilaimhs(

nim varchar(10),

namaMhs varchar(30),

nilai int(11),

PRIMARY KEY(nim)

)

INSERT INTO nilaimhs VALUES

('M0197001', 'Faza Fauzan Kh.', 80),

('M0197002', 'Dwi Amalia Fitriani', 75),

('M0197003', 'Rosihan Ari Yuana', 45),

('M0197004', 'Nada Hasanah', 83),

('M0197005', 'Muh. Ahsani Taqwim', 90);

####Script PHP ####
<?php
// nama file

$namaFile = "report.xls";

// Function penanda awal file (Begin Of File) Excel

function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}

// Function penanda akhir file (End Of File) Excel

function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}

// Function untuk menulis data (angka) ke cell excel

function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}

// Function untuk menulis data (text) ke cell excel

function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}

// header file excel

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");

// header untuk nama file
header("Content-Disposition: attachment; filename=".$namaFile."");

header("Content-Transfer-Encoding: binary ");

// memanggil function penanda awal file excel
xlsBOF();

// ------ membuat kolom pada excel --- //

// mengisi pada cell A1 (baris ke-0, kolom ke-0)
xlsWriteLabel(0,0,"NO");

// mengisi pada cell A2 (baris ke-0, kolom ke-1)
xlsWriteLabel(0,1,"NIM");

// mengisi pada cell A3 (baris ke-0, kolom ke-2)
xlsWriteLabel(0,2,"NAMA MAHASISWA");

// mengisi pada cell A4 (baris ke-0, kolom ke-3)
xlsWriteLabel(0,3,"NILAI");

// mengisi pada cell A5 (baris ke-0, kolom ke-4)
xlsWriteLabel(0,4,"STATUS KELULUSAN");

// -------- menampilkan data --------- //

// koneksi ke mysql

mysql_connect("localhost", "username", "password");
mysql_select_db("database");

// query menampilkan semua data

$query = "SELECT * FROM nilaimhs";
$hasil = mysql_query($query);

// nilai awal untuk baris cell
$noBarisCell = 1;

// nilai awal untuk nomor urut data
$noData = 1;

while ($data = mysql_fetch_array($hasil))
{
// menampilkan no. urut data
xlsWriteNumber($noBarisCell,0,$noData);

// menampilkan data nim
xlsWriteLabel($noBarisCell,1,$data['nim']);

// menampilkan data nama mahasiswa
xlsWriteLabel($noBarisCell,2,$data['namaMhs']);

// menampilkan data nilai
xlsWriteNumber($noBarisCell,3,$data['nilai']);

// menentukan status kelulusan
if ($data['nilai'] >= 60) $status = "LULUS";
else $status = "TIDAK LULUS";

// menampilkan status kelulusan
xlsWriteLabel($noBarisCell,4,$status);

// increment untuk no. baris cell dan no. urut data
$noBarisCell++;
$noData++;
}

// memanggil function penanda akhir file excel
xlsEOF();
exit();

?>

Sabtu, Februari 4

Mengambil attachments gambar pada email

<Html>
<head>
    <link rel="stylesheet" href="../css/gallery.css" type="text/css" />
<style>
#contain{
width:990px;
height:auto;
border-radius:0px 20px 0px 20px;
padding:5px;
border:1px solid #999999;
-moz-box-shadow: 0 0 5px 5px #888;
-webkit-box-shadow: 0 0 5px 5px#888;
box-shadow: 0 0 10px 10px #888;

}
.isi{
width:320px;
height:320px;
margin:5px;

}
</style>

</head>

<body>
<?php

   $ServerName = "{MAILSERVER:PORT/novalidate-cert}INBOX"; // For a IMAP connection    (PORT 143)

   $UserName = "USERNAME";
   $PassWord = "PASSWORD";

if (!$mbox = imap_open($ServerName, $UserName,$PassWord))die("Could not open Mailbox - try again later!");

if ($hdr = imap_check($mbox))
    $msgCount = $hdr->Nmsgs;
else
    die ("Failed to get mail");

$MN=$msgCount;
                    ?>
                    
                    <?php
while($MN>=1)
{

$overview=imap_fetch_overview($mbox,"$MN");

foreach ($overview as $message) {
    $msgStruct = imap_fetchstructure($mbox, $message->msgno);

    // if it has parts, there are attachments that need reading
    if ($msgStruct->parts) {
        foreach ($msgStruct->parts as $key => $part) {
            switch (strtoupper($part->subtype)) {
                case 'GIF': case 'JPEG':case 'PNG':
                    $fileContent = imap_fetchbody($mbox, $message->msgno, $key + 1);

                    ## Mulai Proses pembuatan width dan height ##
                    //Membuat gambar dari hasil decode
                    $img = 'data:image/jpg;base64,' .$fileContent;

                    //membuat widht dan height baru dengan hasil 25%
                    $percent = 0.25;
                    list($width, $height, $type, $attr) = getimagesize($img);
                    $newwidth = 320;//$width * $percent;
                    $newheight = 320;//$height * $percent;

                    ## Akhir Proses pembuatan width dan height ##

                    //menampilkan gambar  
                    //echo '<img src="data:image/jpg;base64,' . $fileContent . '" class="isi" height="'.$newheight.'" width="'.$newwidth.'" />'."";

                    ## Mulai Proses menyimpan file pada folder ##

                    // encode decode proses
                    $encode = base64_encode($fileContent);
                    $img = 'data:image/jpg;base64,' . base64_decode($encode);
                    //$img = 'data:image/jpeg;base64,'.$fileContent;
                    // penyesuaian karakter untuk proses penyimpanan
                    $comma = strpos($img, ',');
                    $data = base64_decode(substr($img, $comma+1));

                    //proses pembuatan nama file
                    //$encode = base64_encode($fileContent);
                    $vowels = array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",);
                    $str = str_replace($vowels, "", $encode);
                    if (strlen($str) > 1000){

                        $trim = substr($str,1000,6);
                        $name = $trim ."_". $MN ;

                    }
                    elseif(strlen($str) > 100) {

                        $trim = substr($str,100,6);
                        $name = $trim ."_". $MN ;
                    }
                    else {

                        $trim = substr($str,10,6);
                        $name = $trim ."_". $MN ;
                    }

                    //set rirektory file dan nama file
                    $myname = '../image/mail_image/'.$name.'.jpg';

                    //proses simpan hasil penyesuaian karakter
                    file_put_contents($myname, $data);

                    ## akhir Proses menyimpan file pada folder ##
            break;
            }
        }
    }
}
$MN--;
}
                    ?>
                    
                    <?php
    ## Baca semua email ##
    $MN--;

    ## Baca Email Terakhir ##
    //$MN=0;

//redirect ke demo2.php
header("Location: ../demo/demo2.php");
exit;
?>

</body>
</Html>

Sabtu, Januari 21

File pertama CI mode CRUD

model_barang.php

<?php
class Model_barang extends CI_Model{

    /** Begin Proses CRUD pada tabel Suplyer **/
    //mengambil semua data pada tabel suplyer
    function get_suply(){
        $query = $this->db->get('suplyer');

        if ($query->num_rows() > 0){

        foreach($query->result() as $bar){
             $data[] = $bar;
        }
        return $data;
        }
    }

    //fungsi insert ke tabel suplyer
    function insert_suply(){
        //mengambil referensi dari form update
        $suply = $_POST['suply'];
        $ket = $_POST['ket'];

        //proses array
        $data = array (
            'nama_suplyer' => $suply,
            'ket' => $ket
        );

        $this->db->insert('suplyer',$data);
    }

    //mengambil semua data pada tabel barang dengan referensi id
    function suply_update(){
        $id = $this->uri->rsegment(3); //mengambil nilai segmen ke-3
        $sqlquery = 'SELECT * FROM suplyer WHERE id_suplyer =?';
        $query = $this->db->query($sqlquery, $id);

      if ($query->num_rows() > 0){
             foreach($query->result() as $bar){
                 $data[] = $bar;
             }
        return $data;
        }
    }

    //update data tabel pada tabel suplyer
    function pro_ups(){
        //mengambil referensi dari form update
        $id = $_POST['id'];
        $suply = $_POST['suply'];
        $ket = $_POST['ket'];

        //proses array
        $data = array (
            'nama_suplyer' => $suply,
            'ket' => $ket
        );

        //update proses
        $this->db->where('id_suplyer', $id);
        $this->db->update('suplyer', $data);
    }

    //menghaus baris dengan referensi id
    function hapus_suply(){
        $id = $this->uri->rsegment(3); //mengambil nilai segmen ke-3

        $this->db->where('id_suplyer', $id);
        $this->db->delete('suplyer');
    }
    /** End Proses CRUD pada tabel Suplyer **/

    /** Begin Proses CRUD pada tabel Barang **/
    //mengambil semua data pada tabel barang
    function get_barang(){
        $query = $this->db->get('barang');
        if ($query->num_rows() > 0){

        foreach($query->result() as $bar){
            $data[] = $bar;
        }
        return $data;
        }
    }

    //fungsi insert ke tabel suplyer
    function insert_barang(){
       //mengambil referensi dari form update
        $suply = $_POST['suply'];
        $id_suply = $_POST['id'];
        $sn = $_POST['sn'];
        $jenis = $_POST['jenis'];
        $jumlah = $_POST['jumlah'];

        //proses array
        $data = array (
            'nama_suplyer' => $suply,
            'id_suplyer' => $id_suplyer,
            'sn' => $sn ,
            'jumlah' => $jumlah,
            'jenis' => $jenis
        );

        $this->db->insert('barang',$data);
    }

    function lihat_barang(){
        $id = $this->uri->rsegment(3); //mengambil nilai segmen ke-3
        echo $id;
        /**
        $sqlquery = 'SELECT * FROM suplyer WHERE id_suplyer =?';
        $query = $this->db->query($sqlquery, $id);
        if ($query->num_rows() > 0){

           foreach($query->result() as $bar){
              $data[] = $bar;
         }
        return $data;
        }
        **/
    }
    /** End Proses CRUD pada tabel barang **/
}
?>

barang.php

<?php
class Barang extends CI_Controller{
     function barang(){
        parent::__construct();

        $this->load->model('model_barang');

    }

    function index(){
        $data['title'] = 'Daftar Suplyer';
        $data['head'] = 'Daftar Suplyer';

        //memanggil fungsi dari model_barang.php
        $data['baris'] = $this->model_barang->get_suply();
        $this->load->view('suplyer', $data);
   }

    /** Begin Proses tabel Suplyer **/
    function insert_suply(){
        $this->model_barang->insert_suply();
        redirect('../barang');
    }

    function suply_update(){
        $data['title'] = 'Data Suplyer';
        $data['head'] = 'Ubah Data Suplyer';

    //memanggil fungsi dari model_barang.php
        $data['baris'] = $this->model_barang->suply_update();

        $this->load->view('edit_suplyer', $data);
    }

    function ups(){
        $this->model_barang->pro_ups();

        redirect('../barang');

    }

    function hapus_suply(){

        $this->model_barang->hapus_suply();
        redirect('../barang');
    }
    /** End proses tabel suplyer **/

    /** Begin proses tabel barang **/

    //Pilih Suplyer
    function finsert_barang(){
        $data['title'] = 'Data barang';
        $data['head'] = 'Insert Data Barang';

    //memanggil fungsi dari model_barang.php
        $data['baris'] = $this->model_barang->suply_update();

        $this->load->view('stok_barang', $data);
    }

    //input proses
    function view_barang(){
        echo "OK";
    }

    /** End proses tabel barang **/

}

?>

suplyer.php

<html
<head>
    <title><?=$title?></title>
</head>

<body>
    <div align="center">
        <h3><?=$head?></h3>
        <div>
            <?php echo form_open('../barang/insert_suply');?>
            <label for="sn">Nama Suplyer :</label>
            <input type="text" name="suply" id="suply" maxlength="100" />

            <label for="nama_barang">Keterangan :</label>
            <input type="text" name="ket" id="ket" />

            <input type="submit" value="Simpan">

            <?php form_close()?>
        </div>
<hr>
        <div>
            <?php
                if ($baris > 0){
            ?>
            
            <table border="1" cellpadding="2">
                <tr>
                    <th>Nama Suplyer</th>
                    <th>Keterangan</th>
                    <th>Ubah</th>
                    <th>Hapus</th>
            </tr>

            <?php foreach ($baris as $bar) { ?>
                <tr>
                    <td><?=anchor('../barang/finsert_barang/'.$bar->id_suplyer,$bar->nama_suplyer);?></td>
                    <td><?=$bar->ket?></td>
                    <td><?=anchor('../barang/suply_update/'.$bar->id_suplyer,'Update');?></td>
                    <td><?=anchor('../barang/hapus_suply/'.$bar->id_suplyer,'Hapus');?></td>
               </tr>

            <?php    } }?>
            </table>
            
        </div>
    </div>
</body>
</html>

edit_suplyer.php

<html
<head>
    <title><?=$title?></title>
</head>

<body>
    <h3><?=$head?></h3>
    <?php foreach ($baris as $bar) { ?>
       <div>
            <?php echo form_open('../barang/ups');?>

            <input type="hidden" name="id" value="<?=$bar->id_suplyer?>" />

            <label for="sn">Nama Suplyer :</label>
            <input type="text" name="suply" id="suply" maxlength="100" />

            <label for="nama_barang">Keterangan :</label>
            <input type="text" name="ket" id="ket" />

            <input type="submit" value="Simpan">

            <?php form_close()?>
       </div>
   <?php    } ?>
</body>
</html>