<?php
/**
* @author Bettina Schwarzer, Fritz-Haber-Institut
* @copyright 08/2011
*
* Report Vertrag
*
*/
error_reporting(E_ALL ^ E_NOTICE);
session_start();
if (! isset($_SESSION["userid"]))
{
include_once ($_SERVER["DOCUMENT_ROOT"] . "/fhiiqm/inc/func_lib.inc.php");
login($_SERVER["PHP_SELF"]);
exit;
}
if (is_null($_SESSION["recht"]) || (!is_null($_SESSION["recht"]) && !in_array("vr",$_SESSION["recht"]) && !in_array("ve",$_SESSION["recht"])))
{
header("Location: /fhiiqm/start.php");
exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link href="/fhiiqm/css/db.css" rel="STYLESHEET" type="TEXT/CSS" media="screen"/>
<link href="/fhiiqm/css/db_print.css" rel="STYLESHEET" type="TEXT/CSS" media="print"/>
<title>Bericht Vertrag-Kosten</title>
</head>
<body >
<?php
echo " ";
echo "<div class=\"float-br smaller\" valign='top'>";
echo " user: " . $_SESSION["userid"];
echo " <a class='sc' href='/fhiiqm/logout.php' title='Session beenden'>logout</a></div>\n";
echo "<div class=\"float-r\"><img src=\"/fhiiqm/img/document_mark_as_final.png\" border=\"0\" alt=\"Bericht\" title=\"Bericht\"/></div>\n";
$vr = $_POST["vr"]; // Parameter aus Form
$create = $_POST["create"];
$rvnr = 1; // Report-Kz
// print_r($vr); echo "<br /><br />";
echo "<div align='center'><h3>Bericht: Vertrag - Kosten pro Jahr</h3></div>\n";
if (!isset($create) || !$create)
{
include($_SERVER["DOCUMENT_ROOT"]. "/fhiiqm/form/vertrag_report_form.inc.php");
echo "<p> </p><p> </p><p> </p><p> </p><p> </p><p> </p><p> </p><p> </p>";
echo "<p> <a href=\"/fhiiqm/vertrag_report.php\" target=\"_self\" title=\"Berichte\" class='sc'>« zur Berichtsübersicht</a></p>\n";
}
else
{
// Kriterien sind klar
echo "<div align='center'>\n";
include_once($_SERVER['DOCUMENT_ROOT'] ."/fhiiqm/inc/dbconnect.inc.php");
// include ($_SERVER['DOCUMENT_ROOT'] ."/fhiiqm/inc/func_lib.inc.php");
$dbc = new dbconnection();
$sql1 = "SELECT v.contract_ID,contract_name,contract_l,contract_begin,contract_end,v.vtyp_ID,
pv.produkt_ID,prod_name,prod_group_ID,kosten,k_year,vtyp_kurz
FROM fhiiqm.Vertrag v
LEFT OUTER JOIN fhiiqm.Vertrag_Kosten k ON v.contract_ID=k.contract_ID
LEFT OUTER JOIN fhiiqm.Produkt_Vertrag pv ON v.contract_ID=pv.contract_ID
LEFT OUTER JOIN fhiiqm.Produkt p ON pv.produkt_ID=p.produkt_ID
LEFT OUTER JOIN fhiiqm.Vertrag_Typ t ON v.vtyp_ID=t.vtyp_ID ";
// WHERE (contract_end IS NULL OR YEAR(contract_end)>=YEAR(Current_Date()))";
$sql2 = "SELECT SUM(kosten) AS sumkosten, k_year
FROM fhiiqm.Vertrag v
LEFT OUTER JOIN fhiiqm.Vertrag_Kosten k ON v.contract_ID=k.contract_ID
LEFT OUTER JOIN fhiiqm.Produkt_Vertrag pv ON v.contract_ID=pv.contract_ID
LEFT OUTER JOIN fhiiqm.Produkt p ON pv.produkt_ID=p.produkt_ID
LEFT OUTER JOIN fhiiqm.Vertrag_Typ t ON v.vtyp_ID=t.vtyp_ID ";
$sql3 = "SELECT COUNT(DISTINCT v.contract_ID) AS anzv
FROM fhiiqm.Vertrag v
LEFT OUTER JOIN fhiiqm.Vertrag_Kosten k ON v.contract_ID=k.contract_ID
LEFT OUTER JOIN fhiiqm.Produkt_Vertrag pv ON v.contract_ID=pv.contract_ID
LEFT OUTER JOIN fhiiqm.Produkt p ON pv.produkt_ID=p.produkt_ID
LEFT OUTER JOIN fhiiqm.Vertrag_Typ t ON v.vtyp_ID=t.vtyp_ID ";
if (!$vr["bind"]) $bind = " AND "; else $bind = $vr["bind"];
if ($vr["prodg"]> -1)
{
if ($where) $where .= " $bind prod_group_ID = $vr[prodg] "; else $where = " prod_group_ID = $vr[prodg] ";
if ($krit) $bd = " ".$bind; else $bd = "";
$sql = "SELECT prod_group_name FROM Produkt_Gruppe WHERE prod_group_ID = " . $vr["prodg"];
$res = $dbc -> querySingleItem($sql);
$krit .= $bd." [Produktgruppe = '$res']";
}
if ($vr["prod"]> -1)
{
if ($where) $where .= " $bind pv.produkt_ID= $vr[prod] "; else $where = " pv.produkt_ID= $vr[prod] ";
if ($krit) $bd = " ".$bind; else $bd = "";
$sql = "SELECT prod_name FROM Produkt WHERE produkt_ID = " . $vr["prod"];
$res = $dbc -> querySingleItem($sql);
$krit .= $bd. " [Produkt = '$res']";
}
if ($vr["vtyp"]> -1)
{
if ($where) $where .= " $bind v.vtyp_ID = $vr[vtyp] "; else $where = " v.vtyp_ID = $vr[vtyp] ";
if ($krit) $bd = " ".$bind; else $bd = "";
$sql = "SELECT vtyp_bezeichnung FROM Vertrag_Typ WHERE vtyp_ID = " . $vr["vtyp"];
$res = $dbc -> querySingleItem($sql);
$krit .= $bd." [Vertragtyp = '$res']";
}
if ($vr["jahr"]> -1)
{
if ($where) $where .= " $bind k_year = $vr[jahr] "; else $where = " k_year = $vr[jahr] ";
// $hav = " HAVING k_year = $vr[jahr]";
if ($krit) $bd = " ".$bind; else $bd = "";
$krit .= $bd." [Kostenjahr = '" . $vr["jahr"] ."']";
}
if ($where) $where = " WHERE $where";
if ($vr["sort1"]>0 || $rag["sort2"]>0)
{
if ($vr["sort1"]>0) $ord = " ORDER BY " . $vr["sort1"];
if ($vr["sort2"]>0) $ord .= ",".$vr["sort2"];
}
$sql1 .= $where . $ord;
// echo "<br /><br />sql1 = $sql1<br /><br />\n";
$result = $dbc ->queryObjectArray($sql1);
if ($dbc->error) echo $dbc->error . "<br />";
if ($krit )echo "<p>Filter: $krit</p>";
if ($result)
{
$bg1 = "#F8F8F8";
$bg2 = "#DEDFE1";
$bg = "#FFFFFF";
echo "<table border='1' cellspacing='0' cellpadding='2' width=65%>\n";
// Listenkopf
echo "<tr bgcolor='#68ACBF'>";
echo "<th width='25%'>Bezeichnung</th>";
echo "<th width='40%'>Beschreibung</th>";
echo "<th width='3%'>Typ</th>";
echo "<th width='15%'>zu Produkt</th>";
echo "<th width='12%'>Kosten (€)</th>";
echo "<th width='5%'>Jahr</th></tr>\n";
foreach ($result as $row)
{
if ($bg == $bg1) $bg = $bg2; else $bg = $bg1;
echo "<tr bgcolor='" . $bg . "'>";
echo "<td>$row->contract_name</td>";
echo "<td>$row->contract_l</td>";
echo "<td>$row->vtyp_kurz</td>";
echo "<td>$row->prod_name</td>";
echo "<td class='right'>". number_format($row->kosten,2,",",".") ."</td>";
echo "<td class='center'>$row->k_year</td></tr>\n";
}
// Summe Kosten
$sql2 .= $where . " GROUP BY k_year";
// echo "<br /><br />sql2 = $sql2<br /><br />\n";
$result = $dbc ->queryObjectArray($sql2);
if ($dbc->error) echo $dbc->error . "<br />";
if ($result)
{
// Anzahl gefilterter Vertraege
if ($where) $sql3 .= " $where";
// echo "sql3 = $sql3<br /><br />";
$rc = $dbc -> querySingleItem($sql3);
$i = 0;
foreach ($result as $row)
{
if ($bg == $bg1) $bg = $bg2; else $bg = $bg1;
echo "<tr bgcolor='" . $bg . "'>";
if ($rc && $i == 0)
echo "<td colspan='3'><b>Anzahl Verträge: $rc</b></td>";
else
echo "<td colspan='3'> </td>";
echo "<td><b>∑ Kosten</b></td>";
echo "<td class='right'><b>". number_format($row->sumkosten,2,",",".") ."</b></td>";
echo "<td class='center'><b>$row->k_year</b></td></tr>\n";
$i++;
}
}
echo "</table>\n";
echo "<p></p><table width=65%><tr><td class='right'><a href=\"javascript:window.print()\" class='sc'>
<img src=\"/fhiiqm/img/printer.png\" alt=\"Bericht drucken\" border=\"0\" align=\"right\" title=\"Bericht drucken\"></a></td></tr></table>";
}
else echo "<p class='red'> Leider gibt es kein Ergebnis für die von Ihnen gewählten Kriterien!</p>";
echo "</div>\n";
echo "<br /><br /> <a href=\"" . $_SERVER["PHP_SELF"] . "\" target=\"_self\"
title=\"Report Vertrag-Kosten\" class=\"sc\">neuer Bericht Vertrag - Kosten</a></p>\n";
echo "<p> <a href=\"/fhiiqm/vertrag_report.php\" target=\"_self\" title=\"Berichte\" class='sc'>« zur Berichtsübersicht</a></p>\n";
}
?>
</body>
</html>