<?php require_once('Connections/MyCon.php'); ?>
<?php

$db=new mysqli($hostname_MyCon, $username_MyCon, $password_MyCon, $database_MyCon);
$trigger="
CREATE TRIGGER ProcessCDR before INSERT ON nextone
FOR EACH ROW
BEGIN

DECLARE destination VARCHAR(200) DEFAULT \"\";
DECLARE id BIGINT DEFAULT 0;
DECLARE vname VARCHAR(200) ;
DECLARE temp VARCHAR(200) DEFAULT IF (NEW.CallDestRegid=\"\",'n/a',NEW.CallDestRegid);
 
 update dumy set x=temp ;
 
SELECT v1.vendorid,v1.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID, EndPoint  from vendor v natural join vendorendpoints ) v1 
RIGHT JOIN 
(
SELECT IF(`CountryCode`=`OperatorList`,length(`Prefix`),length(`Prefix`)+length(`OperatorList`)) as length,`DestinationID`,`VendorID`,`Prefix`,`OperatorList`,`CountryCode`
from vendorextraction order by length desc) 
ve 
ON (v1.VendorID=ve.VendorID)
WHERE
substr(NEW.CalledStationId , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) 
AND
v1.EndPoint=temp
LIMIT  1; 


IF (id IS NOT NULL AND id >0 OR destination <> \"\") then
insert into ncdr (`VendorID`, `name` ,`destination`, `ip`, `duration`,`errortype`,`Called-Party`,`DATE`,`EndPoint`) values( id, vname, destination, NEW.NASIPAddress, NEW.SessionTime, NEW.DisconnectErrorType,  NEW.CalledStationId, NEW.CreationDate, temp );
ELSE
insert into unknown  (`VendorID`, `name` ,`destination`, `ip`, `duration`,`errortype`,`Called-Party`,`DATE`,`EndPoint`) values( id, vname, destination, NEW.NASIPAddress, NEW.SessionTime, NEW.DisconnectErrorType,  NEW.CalledStationId, NEW.CreationDate, temp );
END IF;


END

";
echo $trigger;
$db->multi_query($trigger);
?>