curl->mandiri-cron.php

#!/usr/local/bin/php
<?php
/*
    2014-05-21: v2.5
    - kalo terjadi perubahan posisi transaksi di bank, maka saldo jd kacau
    2014-05-20: v2.2
    - width keterangan mandiri ada yg sampe 107char, table harus di-ALTER
    - keterangan kadang ada special char, hrs dimanipulasi
    2014-05-19: v2.1
    - ganti algo crosschek
    - masiiih aja ada yg double
    2014-05-19: v2.0
    - design ala bca
    - gotcha....i got u
    - gk ada saldo harian: HARUS diakalin, HARUS BISA
    - embeeerrr... style table-nya lbh ribed
    - penasaran, pake php+curl ala bca
    2014-05-18 : v1.1
    - cross-match dgn table order -> email
    - detect & clean double entry ? gmn caranya yach? key-nya susah
*/

        $MYSQL=false;
// mandatory utk php 4.5 and above
        date_default_timezone_set('Asia/Jakarta');

// utk manipulasi & validasi tanggal
// mandiri gk perlu manipulasi krn udah dd/mm/yyyy
//      define('JANUARI',1);
//      define('DESEMBER',12);

        $pastday = 7;           // cek mutasi dari $pastday hari lalu sampai hari ini
        $isFirst = true;

// userid & PIN mandiri & Account
        define('USERID','blablaba99');
        define('PASSWD','123456');
        define('ACCNUM','1234567890123');

if($MYSQL) {
// config mysql
        define('DB_HOSTNAME', 'localhost');
        define('DB_USERNAME', 'userdbnya');
        define('DB_PASSWORD', 'passwdnya');
        define('DB_DATABASE', 'dbnamenya');
        $mysqlId = mysql_connect(DB_HOSTNAME,DB_USERNAME,DB_PASSWORD);
        mysql_select_db(DB_DATABASE,$mysqlId);

// baca dulu last_id di table
        $result = mysql_query("select max(id) from `mbank_mandiri`",$mysqlId);
        list($last_id) = mysql_fetch_row($result);
        mysql_free_result($result);

// kalo last_id positif => table udah ada isinya
// baca dulu saldo terakhir
        $last_saldo = 0;
        if($last_id) {
                $result = mysql_query("SELECT saldo from mbank_mandiri WHERE id='$last_id'",$mysqlId);
                list($last_saldo) = mysql_fetch_row($result);
                mysql_free_result($result);
                $isFirst = false;
        }
}

// penetapan tanggal from-to mutasi
        if($isFirst) {          // kalo table msh kosong, ambil dr tanggal 1
                $from_date = date("Y-m-01");
        } else {
                $from_date = date("Y-m-d",strtotime("-$pastday day"));
        }
        $to_date = date("Y-m-d");

//      ###### ATO BATAS MUTASI DITETAPKAN SECARA MANUAL
//      $from_date = "2014-04-10";
//      $to_date = "2014-04-20";

// load Class
        include('mandiri.class.php');
        $mandiri = new bankMandiri;
        $mandiri->userid = USERID;
        $mandiri->password = PASSWD;
        $mandiri->rekening = ACCNUM;

        $soawal = 0;
        $soakhir = 0;
        $insert = 0;
        $isbank = true;
        $TMPTAB = "mandiri_swap";

// ################ START ACTION ################# \\
if($mandiri->login()){
        $result = $mandiri->mutasi($from_date,$to_date);
        $mandiri->logout();

        $soawal = $result['soawal'];
        $mutasi = $result['mutasi'];
        $soakhir= $result['soakhir'];

  if($MYSQL) {
        $nrow = count($mutasi);
        if(!$nrow) {    // gk dpt hasil mutasi
                mysql_query("INSERT INTO mbank_logman SET tanggal=NOW(), keterangan='Hasil Check Mutasi Zero/Error'",$mysqlId);
                $isbank = false;
        }

        if($isbank) {
                // cek perubahan saldo
                if(floor($soakhir) == floor($last_saldo)) {             // saldo gk berubah
                        mysql_query("INSERT INTO mbank_logman SET tanggal=NOW(),keterangan='Tidak Ada Perubahan Saldo'",$mysqlId);
                        $isbank = false;
                }
        }

        if($isbank) {
                $saldo = $soawal;
                $query = "CREATE TEMPORARY TABLE $TMPTAB LIKE mbank_mandiri";
                mysql_query($query,$mysqlId);
                $query = "INSERT INTO $TMPTAB SELECT * FROM mbank_mandiri WHERE tanggal >= '$from_date'";
                mysql_query($query,$mysqlId);
                // Lets Go Bussiness
                for($i=0;$i<count($mutasi);$i++) {
                        $tgl            = $mutasi[$i]['tanggal'];
                        $keterangan = $mutasi[$i]['keterangan'];
                        $debet          = $mutasi[$i]['debet'];
                        $credit         = $mutasi[$i]['credit'];
                        $saldo          += ($credit - $debet);
                        $keterangan = CheckWord($keterangan);

                        // krn format tanggal mandiri dd/mm/yyyy maka hrs dimanipulasi agan compat dgn mysql
                        $tgl = str_replace("/","-",$tgl);
                        $tanggal = date("Y-m-d",strtotime($tgl));

                        if($isFirst) {          // kalo table kosong gk usah check, langsung insert aja
                                InsertDATA($tanggal,$keterangan,$debet,$credit,$saldo,$mysqlId);
                                $insert++;
                        } else {
                                // cek dulu, agar gk ada data ganda
                                $query = "SELECT id FROM $TMPTAB WHERE tanggal='$tanggal' AND keterangan='$keterangan' AND debet='$debet' AND credit='$credit'";
                                $result = mysql_query($query,$mysqlId);
                                if($result) {
                                        if(mysql_num_rows($result)) {   // udah ada, skip
                                                mysql_free_result($result);
                                        } else {
                                                InsertDATA($tanggal,$keterangan,$debet,$credit,$saldo,$mysqlId);
                                                $insert++;
                                        }
                                } else {
                                        InsertDATA($tanggal,$keterangan,$debet,$credit,$saldo,$mysqlId);
                                        $insert++;
                                }

                        }

                }

                mysql_query("INSERT INTO mbank_logman SET tanggal=NOW(), keterangan='Sukses dengan $insert INSERT'",$mysqlId);
                mysql_query("DROP TABLE $TMPTAB",$mysqlId);
                if(!$isFirst) {
                        ReCheckDouble($from_date,$mysqlId);
                        KoreksiSaldo($TMPTAB,$soakhir,$from_date,$mysqlId);
                }
        }
        mysql_close($mysqlId);

  } else {
        print_r($result);
//      print_r($mutasi);
  }

} else {                                // ERROR LOGIN
        if($MYSQL) {
                mysql_query("INSERT INTO mbank_logman SET tanggal=NOW(), keterangan='Error Login Internet Banking / Wait 10mnt'",$mysqlId);
                mysql_close($mysqlId);
        } else {
                echo "Error Login Internet Banking / Wait 10mnt\n";
        }
}

// ############## PROGRAM EXIT HERE ###############

function CheckWord($str) {
    $xtr = trim($str);
    $xtr = str_replace("  "," ",$xtr);
        $xtr = str_replace("'","&rsquo;",$xtr);
    $xtr = str_replace("/ ","/",$xtr);
    $xtr = str_replace(" /","/",$xtr);
    return $xtr;
}

function InsertDATA($tgl,$ket,$dbt,$cdt,$sal,$mysqlId) {
        // id tanggal keterangan debet credit saldo retreived
        $query="INSERT INTO mbank_mandiri SET tanggal='$tgl',keterangan='$ket',debet='$dbt',credit='$cdt',saldo='$sal',retreived=NOW()";
        mysql_query($query,$mysqlId);
}

function KoreksiSaldo($table_name,$soakhir,$from_date,$mysqlId) {
        $result = mysql_query("SELECT max(id) FROM mbank_mandiri",$mysqlId);
        list($id) = mysql_fetch_row($result);
        mysql_free_result($result);

        $result = mysql_query("SELECT  saldo  FROM mbank_mandiri WHERE id='$id'",$mysqlId);
        list($sobuku) = mysql_fetch_row($result);
        mysql_free_result($result);

        if(floor($sobuku) == floor($soakhir)) { // saldo udah match
                return;
        }

        $query = "CREATE TEMPORARY TABLE $table_name LIKE mbank_mandiri";
        mysql_query($query,$mysqlId);
        $query = "INSERT INTO $table_name SELECT * FROM mbank_mandiri WHERE tanggal>='$from_date'";
        mysql_query($query,$mysqlId);

        $query = "SELECT id,debet,credit,saldo FROM $table_name order by id";
        $result = mysql_query($query,$mysqlId);
        $i = 0;
        $akhir = 0;
        while(list($id,$debet,$credit,$saldo) = mysql_fetch_row($result)) {
                if(!$i) {
                        $akhir = $saldo;
                } else {
                        $akhir += ($credit - $debet);
                        if( floor($saldo) != floor($akhir) ) {
                                mysql_query("UPDATE mbank_mandiri SET saldo='$akhir' WHERE id='$id'",$mysqlId);
                        }
                }
                $i++;
        }
        mysql_free_result($result);
        mysql_query("DROP TABLE " $table_name",$mysqlId);
}

function ReCheckDouble($from_date,$mysqlId) {
        $TMP1TAB = "mbank_swapx";
        $TMP2TAB = "mbank_swapy";
        $query = "CREATE TEMPORARY TABLE $TMP1TAB LIKE mbank_mandiri";
        mysql_query($query,$mysqlId);
        $query = "CREATE TEMPORARY TABLE $TMP2TAB LIKE mbank_mandiri";
        mysql_query($query,$mysqlId);

        $query = "INSERT INTO $TMP1TAB SELECT * FROM mbank_mandiri WHERE tanggal >= SUBDATE('$from_date', INTERVAL 3 DAY)";
        mysql_query($query,$mysqlId);
        $query = "INSERT INTO $TMP2TAB SELECT * FROM $TMP1TAB";
        mysql_query($query,$mysqlId);

        $query = "SELECT count(*) AS hitung,concat(date_format(tanggal,'%Y%m%d'),floor(saldo)) AS keynya FROM $TMP1TAB GROUP BY keynya";
        $result = mysql_query($query,$mysqlId);
        while(list($hitung,$keynya) = mysql_fetch_row($result)) {
                if($hitung>1) {
                        $query = "SELECT id FROM $TMP2TAB WHERE concat(date_format(tanggal,'%Y%m%d'),floor(saldo)) = '$keynya' order by id asc";
                        $res = mysql_query($query,$mysqlId);
                        $i = 0;
                        while(list($id) = mysql_fetch_row($res)) {
                                if($i>0) {
                                        mysql_query("DELETE FROM mbank_mandiri WHERE id='$id'",$mysqlId);
                                }
                                $i++;
                        }
                        mysql_free_result($res);
                }
        }
        mysql_free_result($result);
        $query = "DROP TABLE $TMP1TAB, $TMP2TAB";
        mysql_query($query,$mysqlId);
}
// ############################# EOS() ###############################
?>