Location: PHPKode > projects > TelDaBase > teldabase-1.4/teldabase/rechverw.php
<?
/***************************************************************/
/*                                                             */
/* TelDaBase                                                   */
/* =========                                                   */
/*                                                             */
/* Copyright (C) 2004 Wolfgang Barthel                         */
/*                                                             */
/*                    http://www.teldabase.de                  */
/*                    hide@address.com */
/*                                                             */
/* see index.php and LICENSE                                   */
/*                                                             */
/***************************************************************/

/***************************************************************/
/* rechverw.php                                                */
/* ============                                                */
/* Einlesen der Rechnung und Zuordnung der Gespräche durch     */
/* Vergleich mit früheren Rechnungen und der Asterisk-Datenbank*/
/***************************************************************/


/***************************************************************/
/* set_rechnungsid                                             */
/* ===============                                             */
/* Ermittelt, ob die angegebene Rechnungs-ID gültig ist. Wenn  */
/* ja, wird diese zurückgegeben, ansonsten die ID der neuesten */
/* Rechnung bzw. false wenn noch gar keine Rechnung vorhanden  */
/* ist.                                                        */
/***************************************************************/

function set_rechnungsid($link)
{
    require("conf.php");
    $rechnungsid = false;
    
    if((isset($_GET["rechnungsid"]))
       && (is_numeric($_GET["rechnungsid"])))
    {
	$query = "select rechnung_id, rech_mwst, rech_aufschlag from Rechnungen
		   where rechnung_id='" . $_GET["rechnungsid"] . "'";
	$result = mysql_query($query, $link) 
	  or die ("Query |$query| failed:" . mysql_error());
	if(mysql_numrows($result) == 1)  /* (Genau eine) Rechnung existiert */
	  $rechnungsid = true;
    }
    
    if($rechnungsid === false)
    {
	if($_GET["aktion"] == "delete")  /* Angegebene Rechnung existiert nicht und */
					 /* kann deshalb nicht gelöscht werden */
	{
	    unset($_GET["aktion"]);
	}
	
	$query = "select rechnung_id, rech_mwst, rech_aufschlag 
		   from Rechnungen order by rech_datum desc limit 1";
	$result = mysql_query($query, $link)
	  or die ("Query |$query| failed:" . mysql_error());
	if(mysql_numrows($result) == 0)  /* noch gar keine Rechnung da */
	  return false;
    }

    $row = mysql_fetch_assoc($result); /* neueste Rechnung */

    $_GET["rechnungsid"] = $row["rechnung_id"];

    return($row["rechnung_id"]);
}

/****************************************/
/* list_rechnungen                      */
/* ===============                      */
/* Gibt eine Liste aller Rechnungen in  */
/* der db-aus, in der sie ausgewählt    */
/* oder bearbeitet werden können        */
/****************************************/

function list_rechnungen($link)
{
   $query = "select rechnung_id,  date_format(rech_datum,\"%d.%m.%Y\") as rech_date,
             rech_betrag from Rechnungen order by rech_datum desc;";
   $result = mysql_query($query) 
      or die ("Query |$query| failed:" . mysql_error());

   if(mysql_numrows($result) == 0)
   {
      rechnung_formular($link, false);
      return;
   }

   ?>

   <p>
   <a href="<?= $_SERVER["PHP_SELF"]?>?aktmenu=Rechnungen&amp;aktion=neu">
   Neue Rechnung einlesen</a>
   </p>

	
   <table>
   <tr>
   <th>
       Datum
   </th>
   <th>
       Betrag
   </th>
   </tr>
   
   <?
   while($row =  mysql_fetch_assoc($result))
   {
   ?>
   <tr>
   <td>Rechnung vom <?= $row["rech_date"] ?>&nbsp;</td>
   <td style="text-align:right"><?= 
	 number_format($row["rech_betrag"]/100, 2, ",", "") ?> &euro;</td>
   <td><a href="<?= $_SERVER["PHP_SELF"]
          ."?aktmenu=Ankreuzen&amp;rechnungsid=" . $row["rechnung_id"]?>">
	    Ankreuzen</a></td>
   <td><a href="<?= $_SERVER["PHP_SELF"]
          ."?aktmenu=Auswertung&amp;rechnungsid=" . $row["rechnung_id"]?>">
	    Auswertung</a></td>
   <td><a href="<?= $_SERVER["PHP_SELF"]
          ."?aktmenu=Rechnungen&amp;aktion=edit&amp;rechnungsid=" . $row["rechnung_id"]?>">
	    Bearbeiten</a></td>
   </tr>
   <?
   }
   ?>
   </table>
   <?

}


/****************************************/
/* rechnung_loeschen                    */
/* =================                    */
/* Löscht eine Rechnung und alle zuge-  */
/* hörigen EZV aus der DB               */
/****************************************/

function rechnung_loeschen($link, $rechnungsid)
{

   $rquery = "select rechnung_id,  rech_betrag from Rechnungen where rechnung_id='$rechnungsid'";
   $rresult = mysql_query($rquery) or die ("Query |$rquery| failed:" . mysql_error());

   if(mysql_numrows($rresult) == 1)
   {
      $query = "delete from Rechnungen where rechnung_id='".$_GET['rechnungsid']."'";
      $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
      $query = "delete from EZV where rechnung_id='".$_GET['rechnungsid']."'";
      $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
      $query = "delete from Grundgebuehr where rechnung_id='".$_GET['rechnungsid']."'";
      $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
      $query = "delete from Konto where rechnung_id='".$_GET['rechnungsid']."'";
      $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
   }
   
   echo("<h2>Rechnung gelöscht</h2>");
}


/*****************************************/
/* rechnung_formular_auswerten           */
/* ===========================           */
/* übernimmt Änderungen an Rechnungsdaten*/
/*****************************************/

function rechnung_formular_auswerten($link, $rechnungsid)
{
   /* angegebene Rechnung vorhanden? */
   $rquery = "select rechnung_id,  rech_betrag from Rechnungen where rechnung_id='".$rechnungsid."'";
   $rresult = mysql_query($rquery) or die ("Query |$rquery| failed:" . mysql_error());
   if(mysql_numrows($rresult) == 1)
   {
      foreach ($_POST as $k => $v )
      {
	 if(is_numeric($k))
	 {
	    $query = "delete from Grundgebuehr where rechnung_id = '".$rechnungsid."' and bewohner_id='".$k."'";
	    $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());

	    kommazupunkt($v);
	    $v *= 100;
	     
	    if($v > 0)
	       $query = "insert into Grundgebuehr (rechnung_id, bewohner_id, ggb_anteil) values('".$rechnungsid."', $k,$v)";
	    $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
	 }
      }

      $betrag = $_POST['betrag'];
      kommazupunkt($betrag);
      $betrag *= 100;
       
      $query = "update Rechnungen set rech_betrag=$betrag where
		  rechnung_id = '".$rechnungsid."'";
      mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
      
      header("Location: ".$_SERVER['PHP_SELF']."?aktmenu=Rechnungen&amp;rechnungsid=".$rechnungsid);  
   }
}


/****************************************/
/* neue_rechnung_einlesen               */
/* ======================               */
/* liest eine neue Rechnung in die DB   */
/* ein                                  */
/****************************************/

function neue_rechnung_einlesen($link, $ast_link)
{
   require("conf.php");
   $fh = fopen($_FILES['rechnung']['tmp_name'],'r');
   if($fh)
   {
      fgets($fh,512);
      fgets($fh,512);
      $lines = fgets($fh,512);
   }
   else {
      echo("Einlesen nicht erfolgreich!<br />Existiert die Datei?<br />
Ist in php der Fileupload deaktiviert?");
      return;
   }
   
   preg_match("/.*vom (\d\d).(\d\d).(\d\d\d\d)/",$lines,$date);
   $Rechnungsdatum = "\"$date[3]-$date[2]-$date[1]\"";
   
   $query = "select rechnung_id from Rechnungen where rech_datum = '$Rechnungsdatum'";
   $result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
   if(mysql_numrows($result) > 0)
      die("Rechung schon vorhanden");
   
   $betrag = str_replace(",",".",$_POST['betrag']);
   
   $query = "insert into Rechnungen (rech_datum, rech_betrag, rech_mwst, rech_aufschlag)
      values('$Rechnungsdatum', '" . intval(round(100 * $betrag))
        . "',". intval(round($conf["mwst"] * 100))
	. ",". intval(round($conf["aufschlag"] *100)) .")";
   mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
   
   $rechnung_id = mysql_insert_id();
   
   foreach ($_POST as $k => $v )
   {
      $v = str_replace(",",".", $v);

      if(is_numeric($k) and ($v > 0))
      {
	 $query = "insert into Grundgebuehr 
		    (rechnung_id, bewohner_id, ggb_anteil) values($rechnung_id, $k, "
	    . 100 * $v .")";
	 $result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
      }
   }
   
   fgets($fh,512);
   
   $kopfline = fgets($fh,512);
   
   $kopfline = chop($kopfline);

   $kopf = split(",", $kopfline);
   
   
   fgets($fh,512);
   
   
$j = 0;

$ende = $_FILES['rechnung']['size'];


while (!feof($fh))
{
  $lines = fgets($fh,512);
      
  $lines = chop($lines);

$felder = preg_split("/,(?=\")/", $lines);

/* Lese alle Verbindungsdaten ein, Anführungszeichen um Daten werden entfernt */

if(sizeof($felder) > 5)
{
for($i = 0; $i < sizeof($felder); $i++)
{
    $ezv[$j]["$kopf[$i]"] = preg_replace("/\"(.*)\"/","$1",$felder[$i]);
}


/* Komma durch Punkt in Beträgen ersetzen */

    kommazupunkt($ezv[$j]["Nettogesamtbetrag (Euro)"]);
    $j++;
}
}


/* vorhandene Anbieter, Tarife, Anschlüsse und Bewohner laden */

$anbieter_id = array();

$query = "select anbieter_id,anb_name from Anbieter";
$result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());

while($row = mysql_fetch_assoc($result))
{
	$anbieter_id[$row["anb_name"]] = $row["anbieter_id"];
}		        	

$tarif_id = array();

$query = "select tarif_id,tarif_name from Tarife";
$result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());

while($row = mysql_fetch_assoc($result))
{
	$tarif_id[$row["tarif_name"]] = $row["tarif_id"];
}		        	

$anschluss_id = array();

$query = "select anschluss_id, ans_nummer, bewohner_id from Anschluesse";
$result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());

while($row = mysql_fetch_assoc($result))
{
	$anschluss_id[$row['ans_nummer']] = $row["anschluss_id"];
	$stdbewohner[$row['ans_nummer']] = $row["bewohner_id"];
}		        	

$bewohner_id = array();

$query = "select bewohner_id, bew_name from Bewohner";
$result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());

while($row = mysql_fetch_assoc($result))
{
	$bewohner_id[$row['bew_name']] = $row["bewohner_id"];
}		        	

/* Das tel_nummer Feld im Telefonbuch ist unique, so dass wir einfach versuchen,
 alle Nummern einzutragen. 
 Wenn sie schon vorhanden ist, dann scheitert der query, dass können wir dann ignorieren. 
 Vorher entfernen wir ggf. den Prefix "0049" und die Vorwahl bei Ortsgesprächen 
 */

 $query = "insert ignore into Telefonbuch (tel_nummer,tel_ortsnetz) values ";

 $trenner = "";   
    
for($i = 0; $i < count($ezv); ++$i)
{
    if(strpos($ezv[$i]['Zielrufnummer'], "0049") === 0)
    {
	$tmpnr = substr($ezv[$i]['Zielrufnummer'], 4);
	$ezv[$i]['Zielrufnummer'] = "0" . $tmpnr;
    }

    if($conf["entf_lok_vorwahl"] 
       and strpos($ezv[$i]['Zielrufnummer'], $conf["vorwahl"]) === 0)
    {
	$tmpnr = substr($ezv[$i]['Zielrufnummer'], strlen($conf["vorwahl"]));
	$ezv[$i]['Zielrufnummer'] = $tmpnr;
    }

    $query .= "$trenner ('" .$ezv[$i]['Zielrufnummer']
      ."', '".$ezv[$i]['Zielortsnetz/Ursprungsort/Land']."')";
    $trenner = ", ";
}

    if(!empty($ezv))
      mysql_query($query, $link);
    
											   
foreach ($ezv as $row)
{
/* Neue Anbieter, Tarife, Anschlüsse eintragen */

 if(!array_key_exists($row['Anbieter'], $anbieter_id))
 {
  $query = "insert into Anbieter (anb_name) values ('".$row['Anbieter']."');";
  mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
  $anbieter_id[$row["Anbieter"]] = mysql_insert_id();
 }

 if(!array_key_exists($row['Service/Tarifart'], $tarif_id))
 {
  $query = "insert into Tarife (tarif_name) values ('".$row['Service/Tarifart']."');";
  mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
  $tarif_id[$row['Service/Tarifart']] = mysql_insert_id();
 }

 if(!array_key_exists($row['Anschluss'], $anschluss_id))
 {
  $query = "insert into Anschluesse (ans_nummer) values ('".$row['Anschluss']."');";
  mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
  $anschluss_id[$row['Anschluss']] = mysql_insert_id();
  $stdbewohner[$row['Anschluss']] = NULL;
 }		        	

}

/* Einzelverbindungen eintragen */

    $fields = "rechnung_id, anbieter_id, anschluss_id, ezv_datum, ezv_dauer, telefonbuch_id,
		tarif_id, ezv_einheiten, ezv_nettogesamtbetrag, bewohner_id";

    $ezvtrenner = "insert into EZV ($fields) values ";
    
foreach ($ezv as $row)
{
   /* Datum und Uhrzeit ins Format YYYY-MM-DD HH:MM:SS konvertieren */
    preg_match("/(\d\d).(\d\d).(\d\d\d\d)/", $row['Datum (TT.MM.JJJJ)'], $datum);
    preg_match("/(\d\d):(\d\d):(\d\d)/", $row['Beginn (HH:MM:SS)'], $zeit);
    
    $teldate = "$datum[3]-$datum[2]-$datum[1] $zeit[1]:$zeit[2]:$zeit[3]";
    
    $query = "select telefonbuch_id, bewohner_id from Telefonbuch where tel_nummer = '"
      .$row['Zielrufnummer']. "'";
    $result = mysql_query($query, $link) or die ("Query |$query| failed:" . mysql_error());
    
    $telbuchrow = mysql_fetch_assoc($result);
    
    $telefonbuch_id = $telbuchrow["telefonbuch_id"];
    
    unset($anrufer_id);	
    
   if($ast_link !== false)  /* Asterisk-Daten benutzen */
   {
      /* In der Telefonrechnung stehen auch Ortsgespraeche ohne Vorwahl.
       * In der Asterisk-Datenbank steht die tatsaechlich gewaehlte Nummer,
       * also muessen wir bei Ortsgespraechen die lokale Vorwahl ergänzen.
       * Zur Vereinfachung prüfen wir immer, ob es mit oder ohne Vorwahl
       * eine Übereinstimmung gibt.                                       */
       
       /* Die Telekom-Uhr stimmt nicht immer sekundengenau mit der Rechneruhr überein.
	* Deshalb akzeptieren wir den Datensatz, wenn die Zeiten annähernd stimmen. */

       $query = "select accountcode from cdr where (Userfield = '".$row['Zielrufnummer'] .
	 "' or Userfield = concat('" . $conf["vorwahl"] ."', '".$row['Zielrufnummer']
	 . "')) and (abs(unix_timestamp('$teldate')  - unix_timestamp(calldate)) < 90)";

      $result = mysql_query($query, $ast_link) or die ("Query |$query| failed:" . mysql_error());

      if(mysql_numrows($result) == 0) /* nochmals mit größerem Zeitintervall probieren */
       {
	        $query = "select accountcode from cdr where (Userfield = '".$row['Zielrufnummer'] .
	 "' or Userfield = concat('" . $conf["vorwahl"] ."', '".$row['Zielrufnummer']
	 . "')) and (abs(unix_timestamp('$teldate')  - unix_timestamp(calldate)) < 180)";
	   $result = mysql_query($query, $ast_link) or die ("Query |$query| failed:" . mysql_error());
       }


      if(mysql_numrows($result) == 1) /* Genau einen Eintrag im Zeitfenster gefunden */
      {
	 $ast_row = mysql_fetch_assoc($result);
	  $anrufer_id = $bewohner_id[$ast_row["accountcode"]];
      }
       
      if(mysql_numrows($result) > 1) /* Mehr als einen Eintrag in Zeitfenster gefunden!
				      Das kann passieren, wenn kurz hintereinander dieselbe
				      Nummer gewählt wurde. Wir ordnen den Anruf einer Person
				      zu, wenn alle Anrufe von derselben Person stammen. */
       {
	   $ast_row = mysql_fetch_assoc($result); /* Erstes Ergebnis laden */
	   $anrufer_id = $bewohner_id[$ast_row["accountcode"]];
	   
	   /* stammen die weiteren Anrufe von derselben Person? */

	   while(isset($anrufer_id) and ($ast_row = mysql_fetch_assoc($result)))
	   {
	       if($bewohner_id[$ast_row["accountcode"]] != $anrufer_id)
		 unset($anrufer_id);
	   }
       }
   }
   else /* ($ast_link === false) */
   {
      $anrufer_id = $telbuchrow["bewohner_id"];  /* Gibt es Person, die als Standard für diese Nummer
						  * eingetragen ist? */
      
      if(!isset($anrufer_id))                    /* Oder gibt es wenigstens eine Person, die der 
						  * MSN zugeordnet ist, von der der Anruf geführt wurde*/
      { 
	 $anrufer_id = $stdbewohner[$row['Anschluss']];
      }
   }
    
    
    $values = "$rechnung_id," . $anbieter_id[$row['Anbieter']] .","
		. $anschluss_id[$row['Anschluss']].",'$teldate','"
		. $row['Dauer (HH:MM:SS)']."',"
		. $telefonbuch_id.",". $tarif_id[$row['Service/Tarifart']].", '"
		. $row['Tarifeinh. / Preis pro Min.(Euro)']."',"
		. 10000 * $row['Nettogesamtbetrag (Euro)'];
	
    if(isset($anrufer_id))
    {
	$values.= ",$anrufer_id";
    }
    else
    {		 
	$values.= ",NULL";
    }		
    
    $ezvquery .= $ezvtrenner . "($values)";
    $ezvtrenner = ", ";
}
    if($result = mysql_query($ezvquery, $link)) {} else 
    die("Query |$ezvquery| failed:" . mysql_error());


    fclose($fh);
    header("Location: ".$_SERVER['PHP_SELF']."?aktmenu=Ankreuzen&rechnungsid=$rechnung_id");
}


/****************************************/
/* rechnung_formular                    */
/* =================                    */
/* Formular zum Hochladen einer neuen   */
/* Rechnung                             */
/****************************************/

function rechnung_formular($link, $rechnungsid)
{ 
   $query = "select bewohner_id,bew_name from Bewohner where bew_aktiv = 1 order by bew_name;";
   $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
   
   if(mysql_numrows($result) == 0)
   {
      echo("<h3>Es sind noch keine Personendaten eingegeben worden</h3>\n\n");
      echo("<a href=\"".$_SERVER['PHP_SELF']."?aktmenu=Setup&amp;aktion=personenadd"."\">zur Eingabe</a>");
      return;
   }

   if($rechnungsid !== false)
   {
      $rquery = "select rechnung_id, date_format(rech_datum,\"%d.%m.%Y\") as rech_date, rech_betrag from Rechnungen where rechnung_id='".$rechnungsid."'";
      $rresult = mysql_query($rquery) or die ("Query |$rquery| failed:" . mysql_error());
      if(mysql_numrows($rresult) == 1)
      {
	 $rrow = mysql_fetch_assoc($rresult);
	 $rechnungsbetrag = round($rrow["rech_betrag"] / 100, 2);

	 /* Alle Bewohner, die vorher einen Anteil hatten oder heute aktiv sind sollen angezeigt werden */
	 /* Stimmt die Anfrage ??? */

	 $query = "select Bewohner.bewohner_id,bew_name from Bewohner
		   left join Grundgebuehr using(bewohner_id)
		   where ((bew_aktiv = 1) or
		   ((bew_aktiv <> 1) and (rechnung_id ='". $rechnungsid 
	          ."'))) GROUP BY Bewohner.bewohner_id order by bew_name";
	 $result = mysql_query($query) or die ("Query |$query| failed:" . mysql_error());
      }

      else
      {
	 $rechnungsid = false;
      }
   }
   if($rechnungsid === false)
   {
      echo("<h2>Neue Rechnung einlesen</h2>\n");
      echo("<form action=\"". $_SERVER['PHP_SELF']."?aktmenu=Rechnungen&aktion=neusubmit" );
   }
   else
   {
      echo("<h2>Rechnung vom ".$rrow["rech_date"]."</h2>");
      echo("<form action=\"". $_SERVER['PHP_SELF']
	   ."?aktmenu=Rechnungen&aktion=editsubmit&rechnungsid=$rechnungsid");
   }
   echo("\" method=\"POST\" enctype=\"multipart/form-data\">\n");
      ?>
      <table>
      <tr>
      <th>Name</th>
      <th>Anteil an Fixkosten</th>
      </tr>
      <?
      while($row = mysql_fetch_assoc($result))
	{
	   $queryggb="select ggb_anteil from Grundgebuehr where rechnung_id ='"
	      . $rechnungsid ."' and bewohner_id='". $row["bewohner_id"]."'";
           $resultggb = mysql_query($queryggb) or die ("Query |$queryggb| failed:" . mysql_error());
	   $rowggb = mysql_fetch_assoc($resultggb);
	   if(!isset($rowggb["ggb_anteil"])) 
	   {
	      /* Default-Anteil an neuen Rechnung ist 1 für alle aktiven Bewohner */
	      if($rechnungsid === false)
		 $rowggb["ggb_anteil"] = "100";
	      else
		 $rowggb["ggb_anteil"] = "0"; 
	   }
	   
		echo("<tr><td>".$row["bew_name"]."</td><td><input name=\""
		     .$row["bewohner_id"]."\" type=\"text\" size=\"4\" value=\""
		     .round($rowggb["ggb_anteil"] / 100, 2) ."\"></td></tr>");
}
?>
</table>

<?
     if($rechnungsid === false)
     {
?>
<a href="<?= $_SERVER["PHP_SELF"] 
               ?>?aktmenu=Setup&amp;aktion=personenadd">Weitere Person hinzufügen</a>
<?
     }
?>

<p>
Gesamtbetrag: <input name="betrag" type="text" size="6" 
	value="<?= $rechnungsbetrag ?>"> Euro<br />
<?
     if($rechnungsid === false)
     {
?>
EZV-Datei: <input name="rechnung" type="file" size="30" maxlength="200000" accept="text/*">
<?
     }
?>
</p>
<input type="submit" name="weiter" value = "Weiter">	

</form>
<?
     if($rechnungsid !== false)
     {
?>
<br />
<p>
Oder: <a href="<?= $_SERVER["PHP_SELF"] ?>?aktmenu=Rechnungen&amp;rechnungsid=<?= $rechnungsid ?>&amp;aktion=delete">Rechnung komplett löschen</a> (Vorsicht: Alle Daten zur aktuellen Rechnung gehen verloren, incl. schon zugeordneter Gespräche)
</p>
<?
     }
      
}
?>
Return current item: TelDaBase