| Server IP : 52.223.31.75 / Your IP : 172.31.32.98 [ Web Server : Apache/2.4.66 () OpenSSL/1.0.2k-fips PHP/7.4.33 System : Linux ip-172-31-14-81.eu-central-1.compute.internal 4.14.281-212.502.amzn2.x86_64 #1 SMP Thu May 26 09:52:17 UTC 2022 x86_64 User : apache ( 48) PHP Version : 7.4.33 Disable Function : NONE Domains : 4 Domains MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : OFF Directory : /var/www/ripara.co/app/helpers/ |
Upload File : |
<?php
class DBLinkOCI{
protected $ocidb;
protected $tablename;
public function __construct($inifile){
$defaultconf = array(
'auth' => array(
'hostspec' => 'localhost',
'database' => 'd2pcrm',
'username' => '',
'password' => '',
'tablename' => ''
),
);
$ini = array();
$ini = parse_ini_file($inifile,true);
if (!$ini) $this->Fail('Invalid database ini file');
$ini = array_merge($defaultconf,array_intersect_key($ini,$defaultconf));
/**
init
*/
require_once "PDOOCI/PDO.php";
// $con = new PDOOCI\PDO($ini['auth']["hostspec"]."/".$ini['auth']["database"], $ini['auth']['username'], $ini['auth']["password"]);
$this->Connect($ini['auth']);
$this->tablename = $ini['auth']['tablename'];
}
/**
*
*
* Connect to database old*/
protected function Connect($auth){
// Create connection to Oracle
$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ".$auth["hostspec"].")(PORT = 1521)))(CONNECT_DATA=(SID=".$auth["database"].")))";
$this->ocidb = $conn = ocilogon($auth["username"],$auth["password"],$db); //OLD $conn = oci_connect($auth["username"], $auth["password"], $auth["hostspec"]."/".$auth["database"]);//
if (!$conn) {
$m = oci_error();
echo $m['message'], "\n";
die;
}
else {
// print "Connected to Oracle!";
}
}
public function getConnection(){
return $this->ocidb;
}
/**
*
*
* QUERY
*
*
*/
private function convertidata($campo){
if(trim($campo)){
//sfaso - 2 ore se legale oppure -1 ora se solare
return new MongoDate(strtotime(str_replace('/', '-', trim($campo)))-((date("I",strtotime("now"))+1)*3600));
}else{
return new MongoDate(strtotime('1970'));
}
}
public function getCount($lastTimeInsert){
$query= 'SELECT COUNT(*) AS "TOTALE" FROM '. $this->tablename.date('n') ." WHERE ACKEND > TO_DATE('".$lastTimeInsert."', 'MM/DD/YYYY HH24:MI:SS')";
error_log("OCI Query TOTALE:".$query);
$stid = oci_parse($this->ocidb, $query);
if (!$stid) {
$e = oci_error($this->ocidb);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
$e = oci_error($stid);
error_log('OCI ERROR:'.$e);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
while (($record = oci_fetch_assoc($stid)) != false){
oci_free_statement($stid);
error_log('OCI Totale Record:'. $record['TOTALE']);
return $record['TOTALE'];
}
}
public function getRecords($lastTimeInsert = null, $day = null, $mese = null, $tmpMongo, $api){
$toRet = array();
$bigMemory = array();
if($day == null){
$day = date('d');
}
$query = 'SELECT * FROM '. $this->tablename.date('n');
if($lastTimeInsert){
$query= "SELECT PARTID, CALLID, CALLIDNUM, CALLEENO, CALLERNO, SERVICENO, TRKNO, TRKGRPNO, MODNO, DEVICETYPE, WAITCAUSE, RELEASECAUSE, SUBCCNO, VDN, MEDIATYPE, UVID, ORGCCNO, ORGCALLID, ORGCALLEENO, ORGSERVICENO, SERCCNO, SERSERVICE, USERLEVEL, USERTYPE, CALLINCAUSE, ENTERREASON, LEAVEREASON, BILLINFO, PRESERVICENO, PREDEVICETYPE, PREDEVICENO, DEVICENO, DEVICEIN, PREDEVICEIN, MEDIAINFOTYPE, SKILLID, LOCATIONID, BILLINFO1, BILLINFO2, BILLINFO3, BILLINFO4, OBSSERVICEID, OBSUNIQUEID, CURRENTSKILLID, UAPID, NETENTID, SUBMEDIATYPE,INITVDNID, TO_CHAR(CALLEND, 'yyyy-mm-dd hh24:mi:ss') CALLEND, TO_CHAR(CALLBEGIN, 'yyyy-mm-dd hh24:mi:ss') CALLBEGIN, TO_CHAR(ACKEND, 'yyyy-mm-dd hh24:mi:ss') ACKEND, TO_CHAR(ACKBEGIN, 'yyyy-mm-dd hh24:mi:ss') ACKBEGIN, TO_CHAR(WAITEND, 'yyyy-mm-dd hh24:mi:ss') WAITEND, TO_CHAR(WAITBEGIN, 'yyyy-mm-dd hh24:mi:ss') WAITBEGIN FROM ". $this->tablename. $mese ." WHERE PARTID = ". $day." AND CALLEND > TO_DATE('".$lastTimeInsert."', 'MM/DD/YYYY HH24:MI:SS') order by CALLEND";
//test
// $query= "SELECT PARTID, CALLID, CALLIDNUM, CALLEENO, CALLERNO, SERVICENO, TRKNO, TRKGRPNO, MODNO, DEVICETYPE, WAITCAUSE, RELEASECAUSE, SUBCCNO, VDN, MEDIATYPE, UVID, ORGCCNO, ORGCALLID, ORGCALLEENO, ORGSERVICENO, SERCCNO, SERSERVICE, USERLEVEL, USERTYPE, CALLINCAUSE, ENTERREASON, LEAVEREASON, BILLINFO, PRESERVICENO, PREDEVICETYPE, PREDEVICENO, PREDEVICEIN, MEDIAINFOTYPE, SKILLID, LOCATIONID, BILLINFO1, BILLINFO2, BILLINFO3, BILLINFO4, OBSSERVICEID, OBSUNIQUEID, CURRENTSKILLID, UAPID, NETENTID, SUBMEDIATYPE,INITVDNID, TO_CHAR(CALLEND, 'yyyy-mm-dd hh24:mi:ss') CALLEND, TO_CHAR(CALLBEGIN, 'yyyy-mm-dd hh24:mi:ss') CALLBEGIN, TO_CHAR(ACKEND, 'yyyy-mm-dd hh24:mi:ss') ACKEND, TO_CHAR(ACKBEGIN, 'yyyy-mm-dd hh24:mi:ss') ACKBEGIN, TO_CHAR(WAITEND, 'yyyy-mm-dd hh24:mi:ss') WAITEND, TO_CHAR(WAITBEGIN, 'yyyy-mm-dd hh24:mi:ss') WAITBEGIN FROM ". $this->tablename. "1 WHERE PARTID = 1 AND CALLEND > TO_DATE('02/01/2015', 'MM/DD/YYYY HH24:MI:SS') order by CALLEND";
}
try{
error_log("OCI Query:".$query);
$stid = oci_parse($this->ocidb, $query);
if (!$stid) {
$e = oci_error($this->ocidb);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
$e = oci_error($stid);
error_log('OCI ERROR:'.$e);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Fetch the results of the query
$toRet = array();
error_log('OCI Ok');
$aggiornamento['StartTime'] = new MongoDate(strtotime("now"));
$bigMemory = array();
$maxDataTrovata = 0;
// while($record = next($records)){
//foreach ($records as $record) {
while (($record = oci_fetch_assoc($stid)) != false){
// echo "$cont.";
$cont++;
$tmpDate = $record;
if(strtotime( str_replace('/', '-', trim($tmpDate['CALLEND'])) ) > $maxDataTrovata){
$maxDataTrovata = strtotime(str_replace('/', '-', trim($tmpDate['CALLEND'])));
}
//sistemo le date
$tmpDate['WAITBEGIN'] = $this->convertidata($record['WAITBEGIN']);
$tmpDate['WAITEND'] = $this->convertidata($record['WAITEND']);
$tmpDate['ACKBEGIN'] = $this->convertidata($record['ACKBEGIN']);
$tmpDate['ACKEND'] = $this->convertidata($record['ACKEND']);
$tmpDate['CALLBEGIN'] = $this->convertidata($record['CALLBEGIN']);
$tmpDate['CALLEND'] = $this->convertidata($record['CALLEND']);
$tmpDate['CONVERSATIONTIME'] = strtotime(str_replace('/', '-', trim($tmpDate['CALLEND']))) - strtotime(str_replace('/', '-', trim($tmpDate['CALLBEGIN'])));
//preparo indicizzazione
$alfa = explode(" ",trim($tmpDate['CALLEND']));
if(count($alfa)){
$ind_data = explode("/", $alfa[0]);
$ind_ora = explode(":", $alfa[1]);
$tmpDate['TIME_AAAA'] = intval($ind_data[0]);
$tmpDate['TIME_MM'] = intval($ind_data[1]);
$tmpDate['TIME_GG'] = intval($ind_data[2]);
$tmpDate['TIME_HH'] = intval($ind_ora[0]);
$tmpDate['TIME_II'] = intval($ind_ora[1]);
$tmpDate['TIME_SS'] = intval($ind_ora[2]);
}else{
$tmpDate['TIME_AAAA'] = intval(date("Y"));
$tmpDate['TIME_MM'] = intval(1);
$tmpDate['TIME_GG'] = intval(1);
$tmpDate['TIME_HH'] = intval(12);
$tmpDate['TIME_II'] = intval(0);
$tmpDate['TIME_SS'] = intval(0);
}
$toRet[] = $tmpDate;
/* if($cont%10000==0){
error_log("OCI Elaborato:".$cont);
}
if($cont%20000==0){
error_log("Inserimento fino a".$cont." in mongo da oci");
$tmpMongo->batchInsertJam("CTI",$toRet);
error_log("INSERITI fino a".$cont." in mongo da oci");
$toRet = array();
}
if($cont%50000==0 && $cont<50001){
$bigMemory = array_merge($bigMemory, $toRet);
}
/*if($cont%150000==0){
//prevengo il crash di apache per outmemory
return $maxDataTrovata;
}*/
}
/*if($cont<20000 && $cont >0){*/
error_log("Inserimento fino da 0 a".$cont." in mongo da oci");
if(count($toRet)){
$tmpMongo->batchInsertJam("CTI",$toRet);
}
error_log("INSERITI ok fino da 0 a".$cont." in mongo da oci");
$bigMemory = $toRet;
/*}*/
error_log("1) INSERIMENTO in mongo da oci COMPLETATO");
//verifico allarmi
error_log("2) Inizio verifica Allarmi CTI");
if(count($bigMemory)){
$api->verificaAllarmi($bigMemory, "CTI");
}
error_log("3) Fine verifica Allarmi CTI");
//credo dati grafico home
//inserisco log di giro chrone
$aggiornamento['EndTime'] = new MongoDate(strtotime("now"));
$aggiornamento['Records'] = count($bigMemory);
$aggiornamento['EndTimeLastRecord'] = $maxDataTrovata;
error_log("4) MEMORIZZO DATI GRAFICO ");
//MEMORIZZO DATI GRAFICO
if(count($bigMemory)){
$aggiornamento['grafico'] = $api->getGraficoHome($bigMemory, "CTI");
}
error_log('5) INSERISCO RECORD DEL CHRNOE ');
$tmpMongo->inserisciRecord("CTIchrone", $aggiornamento);
// oci_free_statement($stid);
error_log("END) CTI COMPLETATO!");
}//fine try
catch (Exception $e){
echo 'errore nella query';
error_log("!!!!!!!!!!!!!!!!!!!ERRORE NELLA QUERY!!!!!!!!!!!!!!!! ". $query . "--->". $e);
}
return $maxDataTrovata;
}
public function getRecordsRange($lastTimeInsert = null, $day = null, $mese = null, $tmpMongo, $api){
if($day == null){
$day = date('d');
}
//aggiungo mezzora per fare il range
$lastTimeInsertEnd = date('m/d/Y H:i:s',strtotime($lastTimeInsert)+(60*5));
$query = 'SELECT * FROM '. $this->tablename.date('n');
error_log('1) CTI giornata aggiornamento in corso --------------->da:'.$lastTimeInsert. ' a:'.$lastTimeInsertEnd."<------------------");
if($lastTimeInsert){
$query= "SELECT PARTID, CALLID, CALLIDNUM, CALLEENO, CALLERNO, SERVICENO, TRKNO, TRKGRPNO, MODNO, DEVICETYPE, WAITCAUSE, RELEASECAUSE, SUBCCNO, VDN, MEDIATYPE, UVID, ORGCCNO, ORGCALLID, ORGCALLEENO, ORGSERVICENO, SERCCNO, SERSERVICE, USERLEVEL, USERTYPE, CALLINCAUSE, ENTERREASON, LEAVEREASON, BILLINFO, PRESERVICENO, PREDEVICETYPE, PREDEVICENO, DEVICENO, DEVICEIN, PREDEVICEIN, MEDIAINFOTYPE, SKILLID, LOCATIONID, BILLINFO1, BILLINFO2, BILLINFO3, BILLINFO4, OBSSERVICEID, OBSUNIQUEID, CURRENTSKILLID, UAPID, NETENTID, SUBMEDIATYPE,INITVDNID, TO_CHAR(CALLEND, 'yyyy-mm-dd hh24:mi:ss') CALLEND, TO_CHAR(CALLBEGIN, 'yyyy-mm-dd hh24:mi:ss') CALLBEGIN, TO_CHAR(ACKEND, 'yyyy-mm-dd hh24:mi:ss') ACKEND, TO_CHAR(ACKBEGIN, 'yyyy-mm-dd hh24:mi:ss') ACKBEGIN, TO_CHAR(WAITEND, 'yyyy-mm-dd hh24:mi:ss') WAITEND, TO_CHAR(WAITBEGIN, 'yyyy-mm-dd hh24:mi:ss') WAITBEGIN FROM ". $this->tablename. $mese ." WHERE PARTID = ". $day." AND CALLEND > TO_DATE('".$lastTimeInsert."', 'MM/DD/YYYY HH24:MI:SS') AND CALLEND < TO_DATE('".$lastTimeInsertEnd."', 'MM/DD/YYYY HH24:MI:SS') order by CALLEND";
//test
// $query= "SELECT PARTID, CALLID, CALLIDNUM, CALLEENO, CALLERNO, SERVICENO, TRKNO, TRKGRPNO, MODNO, DEVICETYPE, WAITCAUSE, RELEASECAUSE, SUBCCNO, VDN, MEDIATYPE, UVID, ORGCCNO, ORGCALLID, ORGCALLEENO, ORGSERVICENO, SERCCNO, SERSERVICE, USERLEVEL, USERTYPE, CALLINCAUSE, ENTERREASON, LEAVEREASON, BILLINFO, PRESERVICENO, PREDEVICETYPE, PREDEVICENO, PREDEVICEIN, MEDIAINFOTYPE, SKILLID, LOCATIONID, BILLINFO1, BILLINFO2, BILLINFO3, BILLINFO4, OBSSERVICEID, OBSUNIQUEID, CURRENTSKILLID, UAPID, NETENTID, SUBMEDIATYPE,INITVDNID, TO_CHAR(CALLEND, 'yyyy-mm-dd hh24:mi:ss') CALLEND, TO_CHAR(CALLBEGIN, 'yyyy-mm-dd hh24:mi:ss') CALLBEGIN, TO_CHAR(ACKEND, 'yyyy-mm-dd hh24:mi:ss') ACKEND, TO_CHAR(ACKBEGIN, 'yyyy-mm-dd hh24:mi:ss') ACKBEGIN, TO_CHAR(WAITEND, 'yyyy-mm-dd hh24:mi:ss') WAITEND, TO_CHAR(WAITBEGIN, 'yyyy-mm-dd hh24:mi:ss') WAITBEGIN FROM ". $this->tablename. "1 WHERE PARTID = 1 AND CALLEND > TO_DATE('02/01/2015', 'MM/DD/YYYY HH24:MI:SS') order by CALLEND";
}
try{
//error_log("OCI Query:".$query);
$stid = oci_parse($this->ocidb, $query);
if (!$stid) {
$e = oci_error($this->ocidb);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
$e = oci_error($stid);
error_log('OCI ERROR:'.$e);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Fetch the results of the query
$toRet = array();
error_log('OCI Ok');
$aggiornamento['StartTime'] = new MongoDate(strtotime("now"));
$maxDataTrovata = 0;
// while($record = next($records)){
//foreach ($records as $record) {
while (($record = oci_fetch_assoc($stid)) != false){
// echo "$cont.";
$cont++;
$tmpDate = $record;
if(strtotime( str_replace('/', '-', trim($tmpDate['CALLEND'])) ) > $maxDataTrovata){
$maxDataTrovata = strtotime(str_replace('/', '-', trim($tmpDate['CALLEND'])));
}
//sistemo le date
$tmpDate['WAITBEGIN'] = $this->convertidata($record['WAITBEGIN']);
$tmpDate['WAITEND'] = $this->convertidata($record['WAITEND']);
$tmpDate['ACKBEGIN'] = $this->convertidata($record['ACKBEGIN']);
$tmpDate['ACKEND'] = $this->convertidata($record['ACKEND']);
$tmpDate['CALLBEGIN'] = $this->convertidata($record['CALLBEGIN']);
$tmpDate['CALLEND'] = $this->convertidata($record['CALLEND']);
$tmpDate['CONVERSATIONTIME'] = strtotime(str_replace('/', '-', trim($tmpDate['CALLEND']))) - strtotime(str_replace('/', '-', trim($tmpDate['CALLBEGIN'])));
//preparo indicizzazione
$alfa = explode(" ",trim($tmpDate['CALLEND']));
if(count($alfa)){
$ind_data = explode("/", $alfa[0]);
$ind_ora = explode(":", $alfa[1]);
$tmpDate['TIME_AAAA'] = intval($ind_data[0]);
$tmpDate['TIME_MM'] = intval($ind_data[1]);
$tmpDate['TIME_GG'] = intval($ind_data[2]);
$tmpDate['TIME_HH'] = intval($ind_ora[0]);
$tmpDate['TIME_II'] = intval($ind_ora[1]);
$tmpDate['TIME_SS'] = intval($ind_ora[2]);
}else{
$tmpDate['TIME_AAAA'] = intval(date("Y"));
$tmpDate['TIME_MM'] = intval(1);
$tmpDate['TIME_GG'] = intval(1);
$tmpDate['TIME_HH'] = intval(12);
$tmpDate['TIME_II'] = intval(0);
$tmpDate['TIME_SS'] = intval(0);
}
$toRet[] = $tmpDate;
/* if($cont%10000==0){
error_log("OCI Elaborato:".$cont);
}
if($cont%20000==0){
error_log("Inserimento fino a".$cont." in mongo da oci");
$tmpMongo->batchInsertJam("CTI",$toRet);
error_log("INSERITI fino a".$cont." in mongo da oci");
$toRet = array();
}
if($cont%50000==0 && $cont<50001){
$bigMemory = array_merge($bigMemory, $toRet);
}
/*if($cont%150000==0){
//prevengo il crash di apache per outmemory
return $maxDataTrovata;
}*/
}
//verifico allarmi
error_log("2) Inizio verifica Allarmi CTI");
if(count($toRet)){
$api->verificaAllarmi($toRet, "CTI");
}
error_log("3) Fine verifica Allarmi CTI");
//credo dati grafico home
//inserisco log di giro chrone
$aggiornamento['EndTime'] = new MongoDate(strtotime("now"));
$countaDaInserire = count($toRet);
$aggiornamento['Records'] = $countaDaInserire;
error_log("4) MEMORIZZO DATI GRAFICO ");
//MEMORIZZO DATI GRAFICO
if(count($toRet)){
$aggiornamento['grafico'] = $api->getGraficoHome($toRet, "CTI");
}
error_log('5) INSERISCO RECORD DEL CHRNOE -> Numero Records:'. $aggiornamento['Records']);
$tmpMongo->inserisciRecord("CTIchrone", $aggiornamento);
/*if($cont<20000 && $cont >0){*/
error_log("Inserimento fino da 0 a".$cont." in mongo da oci");
if($countaDaInserire){
for ($i=0; $i < ceil($countaDaInserire/1000); $i++) {
$tmpMongo->batchInsertJam("CTI",array_splice($toRet, $i, 1000));
// error_log("INSERITI ok fino da 0 a".(($i+1)*1000)." in mongo da oci");
}
}
/*}*/
error_log("1) INSERIMENTO in mongo da oci COMPLETATO");
// oci_free_statement($stid);
error_log("END) CTI COMPLETATO!");
}//fine try
catch (Exception $e){
echo 'errore nella query';
error_log("!!!!!!!!!!!!!!!!!!!ERRORE NELLA QUERY!!!!!!!!!!!!!!!! ". $query. "---->".$e);
}
return $maxDataTrovata;
}
}
?>