Export/Import Data dari MySQL Menggunakan PHP dengan Format XML

Status : Draft

Latar Balakang

Pada salah satu aplikasi yang kita kembangkan, Tirta ERP [1], kita memerlukan proses export/import data custom, karena proses export/import data menggunakan database yang kita gunakan, MySQL, sudah tidak memenuhi kebutuhan lagi. Oleh karena itu diperlukan utilitas export/import data yang custom menggunakan format data xml.

Solusi

Untuk export, kita tetap menggunakan mysqldump, tetapi dengan opsi --xml. Sedangkan untuk proses import kita menggunakan php.

Contoh Implementasi Import

	<?php
	/**
	*
	* @package lib.task
	* @author Wildan Maulana, OpenThink Labs
	*/
	include sfConfig::get('sf_lib_dir').DIRECTORY_SEPARATOR.'vendor'.DIRECTORY_SEPARATOR.'sabre'.DIRECTORY_SEPARATOR.'autoload.php';
	class tirtaerpImportDariDistribusiTask extends sfBaseTask
	{
	public function configure()
	{
	$this->namespace = 'tirtaerp';
	$this->name = 'import-dari-distribusi';
	$this->briefDescription = 'Import Data Dari Distribusi ke DUT kemudian ke water';
	$this->detailedDescription = "
	[./symfony tirtaerp:import-dari-distribusi|INFO]
	Untuk informasi selanjutnya, silahkan baca : http://tirtaerp.openthinklabs.com/2015/12/dokumentasi-tirta-erp-task-import-dari-distribusi.html
	" ;
	$this->addOptions(array(
	new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'Koneksi database yang ingin digunakan', 'doctrine'),
	new sfCommandOption('filename', null, sfCommandOption::PARAMETER_REQUIRED, 'Nama file yang ingin diimport', 'null'),
	new sfCommandOption('application', null, sfCommandOption::PARAMETER_REQUIRED, 'Application', 'distribusi'),
	));
	}
	public function execute($arguments = array(), $options = array())
	{
	sfContext::createInstance($this->configuration);
	$options['connection'] = "doctrine";
	$databaseManager = new sfDatabaseManager($this->configuration);
	$connection = $databaseManager->getDatabase(isset($options['connection']) ? $options['connection'] : null)->getConnection();
	$connection->setAttribute(Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS, true );
	$dir_target = date('dmyhis')."transfer_from_depot";
	$dest_dir = sfConfig::get('sf_data_dir').DIRECTORY_SEPARATOR."backup".DIRECTORY_SEPARATOR."distribusi".DIRECTORY_SEPARATOR.$dir_target.DIRECTORY_SEPARATOR;
	$this->logSection("tirtaerp:import-data-distribusi","Mulai proses import.\n");
	$zip = new ZipArchive;
	$zipfile = $options["filename"];
	$res = $zip->open($zipfile);
	if ($res === TRUE) {
	$zip->extractTo($dest_dir);
	$zip->close();
	echo 'extract ok';
	} else {
	echo 'extract failed, code:' . $res;
	}
	$zipfilename = basename($options["filename"]) ;
	$log_message = "<p class='alert alert-info'>Mulai memproses file {$zipfilename}</p>" ;
	LogTable::doSaveOrUpdateByReferensi($zipfilename,array('kategori'=>'transfer_distribusi_ke_dut',
	'referensi'=>$zipfilename,
	'detail'=>$log_message,
	'status'=>0));
	$mapping = array(
	"metadata.txt"=>"metadata.txt",
	"tbl_depot.xml"=>"TblDepotTable",
	"tbl_depot_produk.xml"=>"TblDepotProdukTable",
	"tbl_wilayah.xml"=>"TblWilayahTable",
	"tbl_sales.xml"=>"TblSalesTable",
	"tbl_customer.xml"=>"TblCustomerTable",
	"hs_hr_employee.xml"=>"EmployeeTable",
	"tbl_pengemudi.xml"=>"TblPengemudiTable",
	"tbl_kendaraan.xml"=>"TblKendaraanTable",
	"tbl_kenek.xml"=>"TblKenekTable",
	"tbl_jadwal.xml"=>"TblJadwalTable",
	"tbl_ubahstatuscust.xml"=>"TblUbahstatuscustTable",
	"tbl_penjualan.xml"=>"TblPenjualanTable",
	"serah_terima_amdk.xml"=>"SerahTerimaAmdkTable",
	"serah_terima_amdk_detail.xml"=>"SerahTerimaAmdkDetailTable",
	"serah_terima_amdk_resume.xml"=>"SerahTerimaAmdkResumeTable",
	"st_tujuan_agen.xml"=>"StTujuanAgenTable",
	"kenek_serah_terima.xml"=>"KenekSerahTerimaTable",
	"st_amdk_pabrik.xml"=>"StAmdkPabrikTable",
	"st_amdk_pabrik_detail.xml"=>"StAmdkPabrikDetailTable",
	"helper_st_amdk_pabrik.xml"=>"HelperStAmdkPabrikTable",
	"tbl_botoljaminan.xml"=>"TblBotoljaminanTable",
	"tbl_peminjaman_dispenser"=>"TblPeminjamanDispenserTable");
	foreach($mapping as $filename=>$classname) {
	if(file_exists($dest_dir.$filename)) {
	if($filename == "metadata.txt") {
	$row = 1;
	if (($handle = fopen($dest_dir.$filename, "r")) !== FALSE) {
	while (($data = fgetcsv($handle)) !== FALSE) {
	if($row == 1){
	TblJadwalTable::doDeleteByCkodeDepot($data);
	}
	$row++;
	}
	fclose($handle);
	}
	continue ;
	}
	$reader = new Sabre\Xml\Reader();
	$this->logSection("tirtaerp:import-data-distribusi","Mulai memproses {$filename}\n");
	$log_message .= "<p class='alert alert-info'>Mulai memproses {$filename}</p>";
	LogTable::doSaveOrUpdateByReferensi($zipfilename,array('kategori'=>'transfer_distribusi_ke_dut',
	'referensi'=>$zipfilename,
	'detail'=>$log_message,
	'status'=>0));
	$reader->xml(file_get_contents($dest_dir.$filename));
	$data_xml = $reader->parse();
	$mysqldump = $data_xml["value"];
	$database = ($mysqldump[0]) ;
	$table_data = $database["value"];
	$rows = $table_data[0]["value"];
	if(count($rows) == 0) {
	$this->logSection("tirtaerp:import-data-distribusi","Tidak ada data di {$filename}\n");
	continue ;
	}
	$count = 0 ;
	$total = count($rows);
	foreach($rows as $key=>$row) {
	$data_row = array();
	foreach($row["value"] as $col_index=>$col_val) {
	$data_row[$col_val["attributes"]["name"]] = $col_val["value"];
	}
	/**
	* untuk debugging
	if($filename == "helper_st_amdk_pabrik.xml") {
	print_r($data_row);
	echo "\n";
	echo $mapping[$filename]."\n";
	exit;
	}
	**/
	$classname = $mapping[$filename];
	switch ($filename) {
	case "tbl_sales.xml":
	$classname::doSaveOrUpdate($data_row['ckode_sales'],$data_row);
	break;
	case "tbl_depot.xml":
	$classname::doSaveOrUpdate($data_row['ckode_depot'],$data_row);
	break;
	case "tbl_depot_produk.xml":
	$classname::doSave($data_row["ckode_depot"], $data_row['stock_id']);
	break;
	case "tbl_kendaraan.xml":
	if($data_row["created_at"] == "0000-00-00 00:00:00") {
	$data_row["created_at"] = date('Y-m-d h:i:s');
	}
	if($data_row["updated_at"] == "0000-00-00 00:00:00") {
	$data_row["updated_at"] = date('Y-m-d h:i:s');
	}
	if(!$data_row["created_by"]) {
	$data_row["created_by"] = 2;
	}
	if(!$data_row["updated_by"]) {
	$data_row["updated_by"] = 2;
	}
	$classname::doSaveOrUpdate($data_row["cnopolisi"],$data_row["ckode_pengemudi"],
	$data_row["cketerangan"],$data_row["cstatus"],
	$data_row["ckode_kendaraan"],
	$data_row["ckode_depot"],$data_row["ctransfer"],
	$data_row["created_at"],$data_row["updated_at"],
	$data_row["created_by"],$data_row["updated_by"]
	);
	break;
	case "tbl_wilayah.xml":
	echo $data_row["ckode_pos"]."\n";
	$classname::doSaveOrUpdate($data_row["ckodepos"],$data_row);
	break;
	case "tbl_customer.xml":
	echo $data_row["ckode_customer"]."\n";
	$classname::doSaveOrUpdate($data_row["ckode_customer"], $data_row["ckode_sales"],
	$data_row["ckode_depot"],$data_row["cnopolisi"],
	$data_row["ckode_usaha"], $data_row["ckode_pos"],
	$data_row["cnama"], $data_row["calamat"],
	date::sql2date($data_row["dtgl_masuk"]), $data_row["cattn"],
	$data_row["ctelepon"],$data_row["cnpwp"],
	$data_row["nterm"], $data_row["cstatus"],
	$data_row["nbotol_pinjaman"], $data_row["nuang_jaminan"],
	$data_row["ctelepon2"], $data_row["ctransfer"],
	$data_row["ccetak_harga"], $data_row["cifc_ar"],
	$data_row["cpayment"], $data_row["calamat_tagih"],
	$data_row["cnama_singkat"]);
	break;
	case "tbl_jadwal.xml":
	echo $data_row["ckode_customer"]."\n";
	$classname::doSaveOrUpdate($data_row["cno_jadwal"], $data_row["ckode_produk"],
	$data_row["ckode_customer"],$data_row["njumlah"], $data_row["nharga_satuan"],
	date::sql2date($data_row["dtgl_kirim_awal"]), $data_row["cjadwal_kirim"]);
	break;
	case "tbl_ubahstatuscust.xml":
	$classname::doSaveOrUpdate($data_row["cno_trans"], $data_row["ckode_customer"], $data_row["ckode_depot"], date::sql2date($data_row["dtanggal"]),
	$data_row["cstatus"], $data_row["cketerangan"],$data_row["is_surveyed"],$data_row["surveyor"], $data_row["is_clear"], $data_row["dtanggal_survey"]);
	break;
	case "tbl_pengemudi.xml":
	$classname::doSaveOrUpdate($data_row);
	break;
	case "hs_hr_employee.xml":
	if(!empty($data_row["emp_number"])) {
	$classname::doSaveOrUpdate($data_row["emp_number"],$data_row["employee_id"],
	$data_row["emp_firstname"],$data_row["emp_middle_name"],
	$data_row["emp_lastname"],$data_row["emp_dri_lice_num"],
	$data_row["emp_street1"], $data_row["emp_status"], $data_row["ckode_depot"]);
	}
	break;
	case "tbl_kenek.xml":
	$classname::doSaveOrUpdate($data_row["ckode_kenek"],$data_row["emp_number"],$data_row["cstatus"]);
	break;
	case "tbl_penjualan.xml":
	$data_row["dtanggal"] = date::sql2date($data_row["dtanggal"]);
	if($data_row["created_at"] == "0000-00-00 00:00:00") {
	$data_row["created_at"] = date('Y-m-d H:i:s');
	}
	if($data_row["updated_at"] == "0000-00-00 00:00:00") {
	$data_row["updated_at"] = date('Y-m-d H:i:s');
	}
	if(!$data_row["created_by"]) {
	$data_row["created_by"] = 2;
	}
	if(!$data_row["updated_by"]) {
	$data_row["updated_by"] = 2;
	}
	$classname::doSaveOrUpdate($data_row["cnofaktur"],$data_row);
	break;
	case "serah_terima_amdk.xml":
	if($data_row["created_at"] == "0000-00-00 00:00:00") {
	$data_row["created_at"] = date('Y-m-d H:i:s');
	}
	if($data_row["updated_at"] == "0000-00-00 00:00:00") {
	$data_row["updated_at"] = date('Y-m-d H:i:s');
	}
	if(!$data_row["created_by"]) {
	$data_row["created_by"] = 2;
	}
	if(!$data_row["updated_by"]) {
	$data_row["updated_by"] = 2;
	}
	$classname::doSaveOrUpdate($data_row["id"],$data_row);
	break;
	case "serah_terima_amdk_detail.xml":
	$classname::doSaveOrUpdate($data_row["serah_terima_amdk_id"],$data_row["stock_id"],
	$data_row["b_botol_isi"],$data_row["p_botol_isi_baik"],
	$data_row["p_botol_isi_bocor"],$data_row["p_botol_isi_pecah"],
	$data_row["p_botol_isi_hilang"],$data_row["p_botol_kosong_baik"],
	$data_row["p_botol_kosong_pecah"],$data_row["p_botol_kosong_hilang"],
	$data_row["p_botol_air_kotor"],$data_row["p_botol_lain_merek"],
	$data_row["p_penjualan_galon_tunai"],$data_row["p_penjualan_galon_kredit"],
	$data_row["p_retur_baik"],$data_row["p_retur_pecah"],$data_row["p_retur_pp_is_konsumen"]);
	break;
	case "serah_terima_amdk_resume.xml":
	$classname::doSaveOrUpdate($data_row["serah_terima_amdk_id"],$data_row["brand_id"],
	$data_row["p_penjualan_agen_galon"],$data_row["p_penjualan_agen_karton"],
	$data_row["p_penjualan_user_galon"],$data_row["p_penjualan_user_karton"],
	$data_row["b_faktur"],$data_row["p_faktur"],$data_row["b_dispenser"],
	$data_row["p_dispenser"],$data_row["p_penjualan_cash"],$data_row["p_penjualan_botol"],
	$data_row["biaya_pengiriman"],$data_row["cketerangan"]);
	break;
	case "st_tujuan_agen.xml":
	$classname::doSave($data_row["serah_terima_amdk_id"], $data_row["ckode_customer"]);
	break;
	case "kenek_serah_terima.xml":
	$classname::doSaveOrUpdate($data_row["serah_terima_amdk_id"], $data_row["ckode_kenek"]);
	break;
	case "st_amdk_pabrik.xml":
	$classname::doSaveOrUpdate($data_row["id"], $data_row);
	break;
	case "st_amdk_pabrik_detail.xml":
	$classname::doSaveOrUpdateFromArray($data_row);
	break;
	case "helper_st_amdk_pabrik.xml":
	$classname::doSave($data_row);
	break;
	case "tbl_botoljaminan.xml":
	if($data_row["dtanggal_jatuh_tempo"] == 0) {
	$dtanggal_jatuh_tempo = "" ;
	} else {
	$dtanggal_jatuh_tempo = $data_row["dtanggal_jatuh_tempo"];
	}
	if($data_row["dtanggal"] == 0) {
	$dtanggal = "";
	} else {
	$dtanggal = date::sql2date($data_row["dtanggal"]);
	}
	$classname::doSaveOrUpdate($data_row["cnotransaksi"], $data_row["cnobukti"],
	$data_row["ckode_customer"],$data_row["cjenis_trans"],
	$data_row["njaminan"], $data_row["nkwantitas"],
	$data_row["cketerangan"], $dtanggal,
	$dtanggal_jatuh_tempo, $data_row['ctransfer'],$data_row["cnotransaksi"],
	$data_row['created_by'], $data_row['updated_by']);
	break;
	case "tbl_peminjaman_dispenser":
	$classname::doSaveOrUpdate($data_row["cnotransaksi"], $data_row["cnobukti"],
	$data_row["ckode_customer"],$data_row["cjenis_trans"],
	$data_row["njaminan"], $data_row["nkwantitas"],
	$data_row["cketerangan"], date::sql2date($data_row["dtanggal"]),
	$data_row["nsewa"]);
	break;
	}
	$count++;
	$this->logSection("tirtaerp:import-data-distribusi","{$filename} = {$count}/{$total}");
	}
	$this->logSection("tirtaerp:import-data-distribusi","Selesai memproses {$filename}\n");
	$log_message .= "<p class='alert alert-success'>Selesai memproses {$filename}</p>";
	LogTable::doSaveOrUpdateByReferensi($zipfilename,array('kategori'=>'transfer_distribusi_ke_dut',
	'referensi'=>$zipfilename,
	'detail'=>$log_message,
	'status'=>0));
	}
	}
	$this->logSection("tirtaerp:import-data-distribusi","Selesai proses import\n");
	$log_message .= "<p class='alert alert-success'>Seluruh file telah berhasil diimport</p>";
	LogTable::doSaveOrUpdateByReferensi($zipfilename,array('kategori'=>'transfer_distribusi_ke_dut',
	'referensi'=>$zipfilename,
	'detail'=>$log_message,
	'status'=>1));
	}
	}

view raw tirtaerpImportDariDistribusiTask.class.php hosted with ❤ by GitHub

Referensi

1. Tirta ERP, "ERP Hijau" untuk Industri AMDK, http://tirtaerp.openthinklabs.com/
2. Best Practice for Exporting an App's Data to XML via PHP/MySQL?, http://stackoverflow.com/questions/4581874/best-practice-for-exporting-an-apps-data-to-xml-via-php-mysql
3. An XML library for PHP you may not hate., http://evertpot.com/an-xml-library-you-may-not-hate/
4. sabre/xml, http://sabre.io/xml/