#!/usr/bin/php7.0 
<?php
require_once "XML/RSS.php";

$c=oci_connect("feed", "feed87", "");

if ( ! $c ) {
     echo "Unable to connect: " . var_dump( OCIError() );
     die();
} else {
    null;
}

$aantal_totaal=0;
$aantal_totaal_nieuw=0;
$logging="";
$datum_start=date('d-m-Y G:m:s');

$q1="select id, title, url, description, displayname from feedchannel where state>0 and id=121";
$s1 = oci_parse($c, $q1);
$logging.="<table>";
oci_execute($s1, OCI_DEFAULT);
while (OCIFetch($s1)) {

 $kanaal=ociresult($s1, "URL");
 $kanaalnaam=ociresult($s1, "DESCRIPTION");
 $kanaalkort=ociresult($s1, "DISPLAYNAME");
 
 $rss2 = "XML_RSS";
 $rss = new $rss2($kanaal);
 $rss->parse();

 $aantal_items_opgehaald=0;
 $aantal_items_nieuw=0;  

 foreach ($rss->getItems() as $item) {

   $q2 = "select * from paw_feeditems where url='".$item['link']."'";
   $s2 = oci_parse($c, $q2);

   oci_execute($s2, OCI_DEFAULT);
   $bestaat_al=0;
   $aantal_items_opgehaald++;

   while (OCIFetch($s2)) { 
     $bestaat_al++;
   } 
   
   $id=ociresult($s1, "ID"); 

   $des=str_replace("'","''",$item['description']);
   $s2 = oci_parse($c, "alter session set nls_date_language='american'");
   oci_execute($s2, OCI_DEFAULT);

   $s2 = oci_parse($c, "alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'");
   oci_execute($s2, OCI_DEFAULT);
   if ($bestaat_al==0) { 
    
    $aantal_items_nieuw++;
    $datum_lengte=strlen($item['pubdate']);
    if ($datum_lengte==0) {
    
      $datum_lengte=strlen($item['posted']);
      if ($datum_lengte==0) {
       // huxley levert geen datum aan
       $datum="sysdate";
       $datum_org="[leeg]";
      } else {
       // computer futures
       $datum="to_date('" . substr($item['posted'],0)    . "','dd/mm/yyyy hh24:mi:ss')";
       $datum_org=$item['posted'];
      }
    } else if ($datum_lengte<20)  {
     $datum="to_date('" . substr($item['pubdate'],0) . " " . date('G:m') . "','dd-mm-yyyy hh24:mi')";
     $datum_org=$item['pubdate'];
    } else {
	    $datum="to_date('" . substr($item['pubdate'],5,20) . "','dd mon yyyy hh24:mi:ss','NLS_DATE_LANGUAGE=american')";
     $datum_org=$item['pubdate'];
     if (substr($item['pubdate'],17,8)=='00:00:00') { 
      $datum="to_date('" . substr($item['pubdate'],5,12) . " " . date('G:m') . "','dd-mm-yyyy hh24:mi')";
     } 
    }
    //$datum=date('d-m-Y G:m:s',strtotime($item['pubdate']));
    echo $datum;
    $des=$item['description'];
    
    $q3="begin save_rss(:id_loc, :titel_loc, :url_loc, :description_loc); end;";
    $q2="insert into paw_feeditems (CHANNELID              
         ,TITLE                  
         ,URL                    
         ,STATE  
         ,publishdate                
         ,fetchdate 
         ,datum                
         ,DESCRIPTION)
     values (:id_loc
         ,:titel_loc
         ,:url_loc
         ,1
         ,'$datum_org'
         ,sysdate
         ,$datum
         ,empty_clob())
    returning description into :description_loc";
    $s2 = oci_parse($c, $q2);  
    $clob = oci_new_descriptor($c, OCI_D_LOB);
    oci_bind_by_name($s2, ":description_loc", $clob, -1, OCI_B_CLOB);
    oci_bind_by_name($s2, ":id_loc", $id);
    oci_bind_by_name($s2, ":titel_loc", $item['title']);
    oci_bind_by_name($s2, ":url_loc", $item['link']);
	
    if (!oci_execute($s2, OCI_DEFAULT))
    {
	   echo "Problem executing\n<br>";
	   $e = oci_error($s2);   // For oci_connect errors pass no handle
	   echo htmlentities($e['message']);
	}

    if (!$clob->write($des))
	{
	   $e = oci_error();   // For oci_connect errors pass no handle
	   echo htmlentities($e['message']);
	}
	
	$s2 = oci_parse($c, "commit");
    oci_execute($s2, OCI_DEFAULT);
    $clob->free();
 
   } // controle nieuwe items   
 } // items van RSS feed
 #$logging.="<tr><td>$id</td><td>$kanaalkort</td><td>Opgehaald: $aantal_items_opgehaald</td><td>Nieuw: $aantal_items_nieuw</td></tr>\n";
 $aantal_totaal+=$aantal_items_opgehaald;
 $aantal_totaal_nieuw+=$aantal_items_nieuw;

} 
$logging.="</table>";
$datum_einde=date('d-m-Y G:m:s');

$headers = "MIME-Version: 1.0\n";
$headers .= "Content-type: text/html; charset=\"iso-8859-1\"\n";
$headers .= "Content-Disposition: inline\n";
$headers .= "From: oracle11g <oracle11g@bdg.stumpel.com>\n";
$headers .= "Reply-To: rik stumpel <rik@stumpel.com>\n";

$sql = "select count(1) aantal from paw_feeditems f, feedchannel c where f.channelid=c.id and f.datum > sysdate -14 and f.state=1";
$s2 = OCIParse($c, $sql );
OCIExecute($s2, OCI_DEFAULT);
$huidig_aantal=0;
while (OCI_Fetch($s2)) {
 $huidig_totaal=ociresult($s2, "AANTAL");
}
 
if ($aantal_totaal_nieuw>0) {
 $subject="T=$huidig_totaal, Q=$aantal_totaal, N=$aantal_totaal_nieuw";
 $mailto="rik@stumpel.com";
 // ************ NIET MEER MAILEN
 //$success = mail($mailto, $subject, $logging, $headers);
 // opnieuw indexeren
 $q2 = "begin CTX_DDL.SYNC_INDEX('FEI_IND2'); end;";
 $s2 = OCIParse($c, $q2);
 OCIExecute($s2, OCI_DEFAULT);
}
    
$s2 = oci_parse($c, "alter session set nls_date_language='american'");
oci_execute($s2, OCI_DEFAULT);

$s2 = oci_parse($c, "alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'");
oci_execute($s2, OCI_DEFAULT);

$q_log="insert into paw_batches(datum_start,datum_einde,aantal_opgehaald,aantal_nieuw, logging, aantal_huidig) values (:ds,:de,:ao,:an,:log,:ah)";
$s2 = oci_parse($c, $q_log);

oci_bind_by_name($s2, ":ds", $datum_start);
oci_bind_by_name($s2, ":de", $datum_einde);
oci_bind_by_name($s2, ":ao", $aantal_totaal);
oci_bind_by_name($s2, ":an", $aantal_totaal_nieuw);
oci_bind_by_name($s2, ":log", $logging);
oci_bind_by_name($s2, ":ah", $huidig_totaal);
 
oci_execute($s2, OCI_DEFAULT);
oci_commit($c);

?>