#BRJAVA
DATEI Aktuelle-Bewertung-des-Arbeitsschutzsystems.htm
INPUT
$Var0#BS Nummer - (like)###a.bs_nr like '$Var0_NORM%'
$Var5#BS Art:##$KAT=BS_ART#a.bs_art=$Var5
$Var1#BS Name - (like)###a.bs_name_norm like '$Var1_NORM%'
$Var2#BS Straße - (like)###a.bs_strasse_norm like '$Var2_NORM_STR%'
$Var3#BS Postleitzahl - (like)## #a.bs_plz like '$Var3_NORM%'
$Var4#BS Ort - (like)## #a.bs_gem_norm like '$Var4_NORM%'
$AMT#BezReg# #$KAT=AFAS
SQL
SELECT distinct left( p.gem_schl,3 ) + '%' as gem_schl
FROM plz_ort p p: Synonym
WHERE left( p.gem_schl, 2 ) = '05'
AND p.gem_name = '$AMT'
UNION
SELECT '%'
FROM plz_ort
Report NULL
SQL
LET $GKZ = '$Column(1)';
##---------------------------------------------------------------------------------------------------------
## Header des Reports erstellen
##---------------------------------------------------------------------------------------------------------
REPORT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>
<title>Bezirksregierung Münster Domplatz 1-3 48143 Münster</title>
</head>
<body>
<table style="width:98%;" align="center" border="1" cellspacing="1"> HTML-Tabelle Nr. 1
<tr>
<td align="center" bgcolor="lightblue"><b>Aktuelle Bewertung des betrieblichen
Arbeitsschutzsystems vom $DATUM / $MA_NR</b></td>
</tr>
</table>
<br>
<table style="width:98%;" align="center" border="1" cellspacing="1"> HTML-Tabelle Nr. 2
<tr>
<td width=10% bgcolor="lightblue"><b>Nummer:</b></td>
<td width=90% bgcolor="lightblue"><b>$Var0_Text</b></td>
</tr>
<tr>
<td width=10% bgcolor="lightblue"><b>Art:</b></td>
<td width=90% bgcolor="lightblue"><b>$var5_Text</b></td>
</tr>
<tr>
<td width=10% bgcolor="lightblue"><b>Name:</b></td>
<td width=90% bgcolor="lightblue"><b>$var1_Text</b></td>
</tr>
<tr>
<td width=10% bgcolor="lightblue"><b>Straße:</b></td>
<td width=90% bgcolor="lightblue"><b>$var2_Text</b></td>
</tr>
<tr>
<td width=10% bgcolor="lightblue"><b>Plz:</b></td>
<td width=90% bgcolor="lightblue"><b>$var3_Text</b></td>
</tr>
<tr>
<td width=10% bgcolor="lightblue"><b>Ort:</b></td>
<td width=90% bgcolor="lightblue"><b>$var4_Text</b></td>
</tr>
<tr>
<td width=10% bgcolor="lightblue"><b>BezReg:</b></td>
<td width=90% bgcolor="lightblue"><b>$AMT_Text</b></td>
</tr>
</table>
<br>
<table style="width:98%;" align="center" border="1" cellspacing="1"> HTML-Tabelle Nr. 3
<tr>
<th bgcolor="yellow">Name</th>
<th bgcolor="yellow">Straße</th>
<th bgcolor="yellow">Plz</th>
<th bgcolor="yellow">Ort</th>
<th bgcolor="yellow">Nummer</th>
<th bgcolor="yellow">Erstbew. am</th>
<th bgcolor="yellow">Status</th>
<th bgcolor="yellow">Bew. vom</th>
<th bgcolor="yellow">Status</th>
</tr>
SQL
##---------------------------------------------------------------------------------------------------------
## Abfrage ausführen
##---------------------------------------------------------------------------------------------------------
LET $FROM_1 = From bs_kontrolle b Where b.bs_nr = a.bs_nr and b.kategorie = -7
and b.gueltig_von not in ( SELECT b.gueltig_bis;
LET $FROM_2 = From bs_kontrolle b Where b.bs_nr = a.bs_nr and b.kategorie = -7 b: Synonym
and b.gueltig_bis > 0 ) );
LET $FROM_3 = From bs_kontrolle b Where b.bs_nr = a.bs_nr and b.kategorie = -7
and b.gueltig_von in ( SELECT min( b.gueltig_von );
LET $FROM_4 = From bs_kontrolle b Where b.bs_nr = a.bs_nr and b.kategorie = -7
and b.gueltig_von not in ( SELECT b.gueltig_bis;
LET $FROM_5 = From bs_kontrolle b Where b.bs_nr = a.bs_nr and b.kategorie = -7
and b.gueltig_bis > 0 ) ) );
LET $E_RED = WHEN cast( b.erstbewertung AS char ) = '4' THEN 'red';
LET $E_YELLOW = WHEN cast( b.erstbewertung AS char ) = '3' THEN 'yellow';
LET $E_GREEN = WHEN cast( b.erstbewertung AS char ) = '2' THEN 'green';
LET $B_RED = WHEN cast( b.bewertung AS char ) = '4' THEN 'red';
LET $B_YELLOW = WHEN cast( b.bewertung AS char ) = '3' THEN 'yellow';
LET $B_GREEN = WHEN cast( b.bewertung AS char ) = '2' THEN 'green';
Select distinct a.bs_name,
a.bs_strasse,
a.bs_plz,
a.bs_gem_name,
a.bs_nr,
( SELECT min( b.datum_erstbew = $BR_DATUM )
$FROM_1 $FROM_2 AS "Datum_ErstBew",
( SELECT 'd0' = CASE
$E_RED
$E_YELLOW
$E_GREEN
END
$FROM_3 $FROM_4 $FROM_5 AS "BgColor_ErstBew",
( SELECT 'd0' = CASE
$E_RED
$E_YELLOW
$E_GREEN
END
$FROM_3 $FROM_4 $FROM_5 AS "FontColor_ErstBew",
( SELECT b.erstbewertung
$FROM_3 $FROM_4 $FROM_5 AS "Erg_ErstBew",
b.datum_bew = $BR_DATUM AS "Datum_Bew",
CASE
$B_RED
$B_YELLOW
$B_GREEN
END AS "BgColor_Bew",
CASE
$B_RED
$B_YELLOW
$B_GREEN
END AS "FontColor_Bew",
b.bewertung AS "Erg_Bew",
b.ma_nr
From bs a a: Synonym
LEFT JOIN bs_kontrolle b ON a.bs_nr = b.bs_nr
Where $Var0
and $Var1
and $Var2
and $Var3
and $Var4
and $Var5
and a.bs_gem_schl like $GKZ
and a.gueltig_bis = 0
and b.gueltig_bis = 0
and b.kategorie = -7
Order By 1, 2, 3
REPORT
<tr>
<td>%s</td> Spaltenausgabe
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td bgcolor="%s"><font color="%s">%s</font></td>
<td>%s</td>
<td bgcolor="%s"><font color="%s">%s</font></td>
</tr>
SQL
##---------------------------------------------------------------------------------------------------------
REPORT
</table>
<br>
<table style="width:98%;" align="center" border="0" cellspacing="1">
<tr>
<td colspan="7" align="right">Ausgabe: $DATEI</td>
</tr>
</body>
</html>
Execute $DATEI
|
