Skip to content

Instantly share code, notes, and snippets.

@widoyo

widoyo/upload.php

Last active August 29, 2015 14:01
Show Gist options
  • Save widoyo/395b396f10baf7b5b10a to your computer and use it in GitHub Desktop.
Save widoyo/395b396f10baf7b5b10a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
upload.php
<?
date_default_timezone_set('Asia/Jakarta');
error_reporting(E_ERROR);
//################
//Connect ke Mysql
// $db = mysql_connect("localhost" , "root", "root") or die ("could not connect to mysql.");
$db = mysql_connect("www.sparelog.com" , "rachmatg_tinem", "tinem") or die (date("Y M D d H:i:s") ." >> could not connect to sparelog db.\r\n");
echo date("Y M D d H:i:s") . " >> Sparelog Connection OK \r\n";
if (!$db) exit;
// $connect = mysql_select_db("sparelog",$db) or die (date("Y M D d H:i:s") ." >> Could not connect to MySQL database.\r\n");
$connect = mysql_select_db("rachmatg_sparelog",$db) or die (date("Y M D d H:i:s") ." >> could not select sparelog db.\r\n");
//################
//Connect ke Oracle
$connection = Ora_Logon("hcpt_baru@tinem","130969") or die (date("Y M D d H:i:s") ." >> Could not connect to oracle.\r\n");
if (!$connection) exit;
echo date("Y M D d H:i:s") ." >> Oracle Connection OK \r\n";
//################
//Select dari table upload yang belum upload
$sql_delete_ora = "select rmr_id, nr from upload where upload='N' and action='DELETE'";
$conn_delete = ora_open($connection);
$conn_update_delete = ora_open($connection);
ora_parse($conn_delete, $sql_delete_ora) or die("Salah sql \r\n");
ora_exec($conn_delete) or die("Nggak eksekusi \r\n");
$delcancel = 0;
while (ora_fetch($conn_delete)) {
$query_delete_cancel ="delete from cases where rmr_id='" . ora_getcolumn($conn_delete, 0) . "' and nr='" . ora_getcolumn($conn_delete, 1) . "'";
echo $query_delete_cancel;
$ResultSet = mysql_query($query_delete_cancel, $db) or die ("cannot delete record update\r\n");
$sql_update_delete = "update upload set upload='Y' where RMR_ID='". ora_getcolumn($conn_delete, 0) ."' and nr='" . ora_getcolumn($conn_delete, 1) . "' and action='DELETE' ";
ora_parse($conn_update_delete, $sql_update_delete);
ora_exec($conn_update_delete);
$delcancel= $delcancel+1;
}
if($delcancel) echo date("Y M D d H:i:s") . " >> ".$delcancel." record cancel deleted\r\n";
$sql_oracle = "select rmr_id, max(tgl_update) test from upload where upload='N' and (action <>'DELETE' or action is null) group by rmr_id";
$conn = ora_open($connection);
$conn_vcase = ora_open($connection);
ora_parse($conn, $sql_oracle) or die("Salah sql \r\n");
ora_exec($conn) or die("Nggak eksekusi \r\n");
//$rmr = "";
while (ora_fetch($conn)) {
//echo "masuk";
//$rmr = "select rmr_id from update";
$rmr = $rmr ."'". ora_getcolumn($conn, 0) ."',";
}
//echo "rmr 1: " .$rmr;
if (strlen($rmr) < 4 ){die ("Nggak ada data \r\n");}
//################
//select dari v_case dengan rmr yang terupdate
$sql_vcase = "
select RMR_ID,
MDR_ID,
INITIAL_AT,
NAMA_GRP_MODUL,
MODULE_NAME,
S_CODE,
SERNUM,
PRICE,
CUSTOMER,
REQUESTER,
DEST_ADDR,
CITY,
IDSITE,
SITE,
REGION,
PHONE,
FAX,
to_char(RECEIVED,'YYYY-MM-DD HH24:MI:SS'),
CONFIRMED,
INDELIVERY,";
$sql_vcase .="
MODULE_NAME_OUT,
S_CODE_OUT,
PRICE_OUT,
DELIVERED,
SERIAL_OUT,
SERIALGOOD,
DATEFLM,
REPLENISHED1,
SERIAL_R1,
MODULE_NAME_R1,
S_CODE_R1,
REPLENISHED2,
MODULE_NAME_R2,
S_CODE_R2,
DEFFECT,
NAMA_MODUL_FAULTY,
SERIAL_BROKEN,
to_char(REPAIR_DATE,'YYYY-MM-DD HH24:MI:SS'),
RETURN_GOOD,";
$sql_vcase .="
INDEX_FAULTY,
POOL_ID,
GRP,
ID_EQ,
MTTR_OK,
MTTR,
REAL_MTTR,
KONDISI_MTTR,
NOMODULE,
NR,
REMARK,
FRRNUMBER,
status_mttr,
teknologi,
kategori,
supervisor,
remark_faulty,
shipment_number,
PO_SO_NUMBER,
shipment_number1,
po_so_number1,
severity_sap,
base_code,
delivery_number,
pgi_date,
unreturned,
received_name
from v_case_new_rev where rmr_id in (" . substr($rmr,0,-1) . ")";
//echo "$sql_vcase: ";
//ECHO"";
ora_parse($conn, $sql_vcase) or die ("Error ora parse rmr updated");
ora_exec($conn);
$counter=0;
while (ora_fetch($conn)) {
//################/
//delete di mysql yang rmrnya sesuai
$query_delete ="delete from cases where rmr_id='" . ora_getcolumn($conn, 0) . "' and nr='" . ora_getcolumn($conn, 48) . "'";
//echo $query_delete;
$ResultSet = mysql_query($query_delete, $db);// or die ("cannot delete record insert\r\n");
//################
//insertkan di mysql yang rmrnya sesuai
$query_insert ="insert into cases (RMR_ID,
MDR_ID,
INITIAL_AT,
NAMA_GRP_MODUL,
MODULE_NAME,
S_CODE,
SERNUM,
PRICE,
CUSTOMER,
REQUESTER,
DEST_ADDR,
CITY,
IDSITE,
SITE,
REGION,
PHONE,
FAX,
RECEIVED,
CONFIRMED,
INDELIVERY,";
$query_insert .="MODULE_NAME_OUT,
S_CODE_OUT,
PRICE_OUT,
DELIVERED,
SERIAL_OUT,
SERIALGOOD,
DATEFLM,
REPLENISHED1,
SERIAL_R1,
MODULE_NAME_R1,
S_CODE_R1,
REPLENISHED2,
MODULE_NAME_R2,
S_CODE_R2,
DEFFECT,
NAMA_MODUL_FAULTY,
SERIAL_BROKEN,
REPAIR_DATE,
RETURN_GOOD,
INDEX_FAULTY,
POOL_ID,";
$query_insert .=" GRP,
ID_EQ,
MTTR_OK,
MTTR,
REAL_MTTR,
KONDISI_MTTR,
NOMODULE,
NR,
REMARK,
FRRNUMBER,
status_mttr,
teknologi,
kategori,
supervisor,
remark_faulty,
shipment_number,
PO_SO_NUMBER,
shipment_number2,
po_so_number2,
severity_sap,
base_code,
delivery_number,
pgi_date,
unreturned,
received_name) values ('";
$query_insert .=addslashes(ora_getcolumn($conn, 0)) . "','" .addslashes(ora_getcolumn($conn, 1)) . "','" .addslashes(ora_getcolumn($conn, 2)) . "','" .addslashes(ora_getcolumn($conn, 3)) . "','" .addslashes(ora_getcolumn($conn, 4)) . "','" .addslashes(ora_getcolumn($conn, 5)) . "','" ;
$query_insert .=addslashes(ora_getcolumn($conn, 6)) . "','" .addslashes(ora_getcolumn($conn, 7)) . "','" .addslashes(ora_getcolumn($conn, 8)) . "','" .addslashes(ora_getcolumn($conn, 9)) . "','" .addslashes(ora_getcolumn($conn, 10)) . "','" .addslashes(ora_getcolumn($conn, 11)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 12)) . "','" .addslashes(ora_getcolumn($conn, 13)) . "','" .addslashes(ora_getcolumn($conn, 14)) . "','" .addslashes(ora_getcolumn($conn, 15)) . "','" .addslashes(ora_getcolumn($conn, 16)) . "','" .addslashes(ora_getcolumn($conn, 17)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 18)) . "','" .addslashes(ora_getcolumn($conn, 19)) . "','" .addslashes(ora_getcolumn($conn, 20)) . "','" .addslashes(ora_getcolumn($conn, 21)) . "','" .addslashes(ora_getcolumn($conn, 22)) . "','" .addslashes(ora_getcolumn($conn, 23)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 24)) . "','" .addslashes(ora_getcolumn($conn, 25)) . "','" .addslashes(ora_getcolumn($conn, 26)) . "','" .addslashes(ora_getcolumn($conn, 27)) . "','" .addslashes(ora_getcolumn($conn, 28)) . "','" .addslashes(ora_getcolumn($conn, 29)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 30)) . "','" .addslashes(ora_getcolumn($conn, 31)) . "','" .addslashes(ora_getcolumn($conn, 32)) . "','" .addslashes(ora_getcolumn($conn, 33)) . "','" .addslashes(ora_getcolumn($conn, 34)) . "','" .addslashes(ora_getcolumn($conn, 35)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 36)) . "','" .addslashes(ora_getcolumn($conn, 37)) . "','" .addslashes(ora_getcolumn($conn, 38)) . "','" .addslashes(ora_getcolumn($conn, 39)) . "','" .addslashes(ora_getcolumn($conn, 40)) . "','" .addslashes(ora_getcolumn($conn, 41)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 42)) . "','" .addslashes(ora_getcolumn($conn, 43)) . "','" .addslashes(ora_getcolumn($conn, 44)) . "','" .addslashes(ora_getcolumn($conn, 45)) . "','" .addslashes(ora_getcolumn($conn, 46)) . "','" .addslashes(ora_getcolumn($conn, 47)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 48)) . "','" .addslashes(ora_getcolumn($conn, 49)) . "','" .addslashes(ora_getcolumn($conn, 50)) . "','" .addslashes(ora_getcolumn($conn, 51)) . "','" .addslashes(ora_getcolumn($conn, 52)) . "','" .addslashes(ora_getcolumn($conn, 53)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 54)) . "','" .addslashes(ora_getcolumn($conn, 55)) . "','" .addslashes(ora_getcolumn($conn, 56)) . "','" .addslashes(ora_getcolumn($conn, 57)) . "','" .addslashes(ora_getcolumn($conn, 58)) . "','" .addslashes(ora_getcolumn($conn, 59)) . "','";
$query_insert .=addslashes(ora_getcolumn($conn, 60)) . "','" .addslashes(ora_getcolumn($conn, 61)) . "','" .addslashes(ora_getcolumn($conn, 62)) . "','" .addslashes(ora_getcolumn($conn, 63)) . "','" .addslashes(ora_getcolumn($conn, 64)) . "','" .addslashes(ora_getcolumn($conn, 65)) ."')";
//echo $query_insert;
//$query_insert =addslashes(ora_getcolumn($conn, 23));
//ECHO "-".$query_insert;
$ResultSet = mysql_query($query_insert, $db) or die ("cannot insert record:$query_insert\r\n");
$sql_update = "update upload set upload='Y' where RMR_ID='". ora_getcolumn($conn, 0) ."'";
ora_parse($conn_vcase, $sql_update);
ora_exec($conn_vcase);
$counter++;
}
//################
//Update Status
if($counter){
$varUpdate = Date("l d-M-y H:i");
$SQLupdate = "UPDATE `updated` SET `updated` = '" . $varUpdate . "' WHERE `no` = 1";
mysql_query($SQLupdate, $db);
}
echo date("Y M D d H:i:s") . " >> ".$counter." record updated\r\n";
//UPDATE SISANYA
$sql_sisa = "update upload set upload='Y' where rmr_id in (select rmr_id from upload minus select rmr_id from v_case_new_rev) and action<>'DELETE' or (rmr_id like '%HW' or rmr_id like '%CC')";
$conn_sisa = ora_open($connection);
ora_parse($conn_sisa, $sql_sisa) or die("Salah sql sisa\r\n");
ora_exec($conn_sisa) or die("Nggak eksekusi sisa\r\n");
ora_logoff ($connection);
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment