Import existing FWAKLR forms
authorJoey Schulze <joey@infodrom.org>
Thu, 3 Mar 2011 19:06:59 +0000 (20:06 +0100)
committerJoey Schulze <joey@infodrom.org>
Thu, 3 Mar 2011 19:06:59 +0000 (20:06 +0100)
22 files changed:
akkord_geraete.php [new file with mode: 0644]
akkord_mitarbeiter.php [new file with mode: 0644]
anbaugeraete.php [new file with mode: 0644]
arbeitsarten.php [new file with mode: 0644]
arbeitsberichte.php [new file with mode: 0644]
gebiet.php [new file with mode: 0644]
geraete.php [new file with mode: 0644]
gereinigt.php [new file with mode: 0644]
korrektur.php [new file with mode: 0644]
kosten_geraete.php [new file with mode: 0644]
kosten_kostenstellen.php [new file with mode: 0644]
kosten_mitarbeiter.php [new file with mode: 0644]
kostenstellen.php [new file with mode: 0644]
materialien.php [new file with mode: 0644]
materialverbrauch.php [new file with mode: 0644]
overview_geraete.php [new file with mode: 0644]
overview_gewaesser.php [new file with mode: 0644]
overview_kosten.php [new file with mode: 0644]
overview_kostenstellen.php [new file with mode: 0644]
overview_mitarbeiter.php [new file with mode: 0644]
overview_stunden.php [new file with mode: 0644]
personal.php [new file with mode: 0644]

diff --git a/akkord_geraete.php b/akkord_geraete.php
new file mode 100644 (file)
index 0000000..933fb99
--- /dev/null
@@ -0,0 +1,57 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['akkord_geraete.year'])) $_SESSION['akkord_geraete.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Akkordstunden für Geräte',
+             'join' => array('gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['akkord_geraete.year']
+                      . " AND akkord = 1"
+                      . " GROUP BY gerate.id,gerate.geraet,arbeitsarten.arbeitsart",
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['akkord_geraete.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'gerate.id',
+                                           ),
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'sql' => "gerate.geraet",
+                                           'width' => 170,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'arbeitsart' => array(
+                                           'name' => 'Arbeitsart',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'stunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => 'sum(geraetstunden)',
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/akkord_mitarbeiter.php b/akkord_mitarbeiter.php
new file mode 100644 (file)
index 0000000..bcdd099
--- /dev/null
@@ -0,0 +1,66 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['akkord_mitarbeiter.year'])) $_SESSION['akkord_mitarbeiter.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Akkordstunden für Mitarbeiter',
+             'join' => array('personal ON einsatz.personal = personal.id',
+                             'gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['akkord_mitarbeiter.year']
+                      . " AND akkord = 1"
+                      . " GROUP BY personal.id,personal.name,personal.vorname,personal.persstdsatz,arbeitsarten.arbeitsart",
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['akkord_mitarbeiter.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'personal.id',
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Mitarbeiter',
+                                           'sql' => "name || ', ' || vorname",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'arbeitsart' => array(
+                                           'name' => 'Arbeitsart',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'stunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'sql' => 'sum(stunden)',
+                                           'width' => 70,
+                                           ),
+                             'gesamtkosten' => array(
+                                           'name' => 'Kosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => 'sum(stunden) * personal.persstdsatz',
+                                           'visible' => false,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/anbaugeraete.php b/anbaugeraete.php
new file mode 100644 (file)
index 0000000..c6925c9
--- /dev/null
@@ -0,0 +1,75 @@
+<?php
+
+$mask = array(
+             'table' => 'anbaugeraete',
+             'title' => 'Anbaugeräte',
+             'join' => array('geraetetypen ON anbaugeraete.geraetetyp = geraetetypen.id'),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'anbaugeraete.id',
+                                           ),
+                             'anbaugeraet' => array(
+                                           'name' => 'Anbaugerät',
+                                           'width' => 150,
+                                           ),
+                             'geraetetyp' => array(
+                                           'name' => 'Gerätetyp',
+                                           'width' => 70,
+                                           'sql' => 'geraetetypen.name',
+                                           'specs' => "ClassName: 'aligncenter'",
+                                           ),
+                             'anbgerstdsatz' => array(
+                                           'name' => 'Stundensatz',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 90,
+                                           ),
+                             'anbkst_intern' => array(
+                                           'name' => 'interne Kosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 100,
+                                           ),
+                             'anbkst_extern' => array(
+                                           'name' => 'externe Kosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 100,
+                                           ),
+                             ),
+             'edit' => array(
+                             'anbaugeraet' => array(
+                                           'name' => 'Anbaugerät',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'geraetetyp' => array(
+                                           'name' => 'Gerätetyp',
+                                           'type' => 'select',
+                                           'options' => 'SELECT id,name AS text FROM geraetetypen ORDER BY name',
+                                           ),
+                             'anbgerstdsatz' => array(
+                                           'name' => 'Stundensatz',
+                                           'type' => 'decimal',
+                                           'size' => 15,
+                                           'required' => true,
+                                           ),
+                             'anbkst_intern' => array(
+                                           'name' => 'Kosten intern',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'required' => true,
+                                           ),
+                             'anbkst_extern' => array(
+                                           'name' => 'Kosten extern',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'required' => true,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/arbeitsarten.php b/arbeitsarten.php
new file mode 100644 (file)
index 0000000..8f69dba
--- /dev/null
@@ -0,0 +1,26 @@
+<?php
+
+$mask = array(
+             'table' => 'arbeitsarten',
+             'title' => 'Arbeitsarten',
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           ),
+                             'arbeitsart' => array(
+                                           'name' => 'Arbeitsart',
+                                           'width' => 200,
+                                           ),
+                             ),
+             'edit' => array(
+                             'arbeitsart' => array(
+                                           'name' => 'Arbeitsart',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/arbeitsberichte.php b/arbeitsberichte.php
new file mode 100644 (file)
index 0000000..cfd7656
--- /dev/null
@@ -0,0 +1,375 @@
+<?php
+
+$jscode[] = <<<EOC
+
+Rico.loadModule('Calendar');
+
+function get_number(name)
+{
+  var elems = name.split(':');
+  if (elems.length == 1) return 0;
+  return Number(elems[elems.length-1]);
+}
+
+function select_gebiet_callback(data)
+{
+    select_update('edit_kostenstelle',data.info,1);
+}
+
+function select_gebiet(obj)
+{
+    get_info('kostenstellen', {gebiet: obj.options[obj.selectedIndex].value}, select_gebiet_callback);
+}
+
+function select_geraet_callback(data)
+{
+    var num = get_number(data.parameter.id);
+
+    var id = 'edit_anbaugeraet';
+    if (num > 0) id += ':' + num;
+
+    var obj = document.getElementById(id);
+
+    if (data.info.length) {
+      if (obj.options.length == 0)
+       obj.options[0] = new Option('Bitte wählen Sie','',false,false);
+      select_update(id,data.info, 1);
+    } else
+      obj.options.length = 0;
+
+}
+
+function select_geraet(obj)
+{
+    get_info('anbaugeraete',
+            {geraet: obj.options[obj.selectedIndex].value, id: obj.id},
+            select_geraet_callback);
+}
+
+function form_onload()
+{
+    get_info('gebiete', {}, function (data) { select_update('edit_gebiet',data.info, 1); });
+    get_info('ordnungen', {}, function (data) { select_update('edit_ordnung',data.info,1); });
+    get_info('mitarbeiter', {}, function (data) { select_update('edit_personal',data.info, 1); });
+    get_info('geraete', {}, function (data) { select_update('edit_geraet',data.info, 1); });
+    get_info('arbeitsarten', {}, function (data) { select_update('edit_arbeitsart',data.info,1); });
+}
+
+function replicate_personal()
+{
+    var elem;
+    var personal = document.getElementById('edit_personal');
+    var stunden = document.getElementById('edit_stunden');
+
+    var number = get_number(personal.parentNode.children[personal.parentNode.children.length-1].name) + 1;
+
+    elem = document.createElement('br');
+    personal.parentNode.appendChild(elem);
+
+    elem = document.createElement('select');
+    elem.id = 'edit_personal:' + number;
+    elem.name = 'personal:' + number;
+    elem.options[0] = new Option('Bitte wählen Sie','',false,false);
+    personal.parentNode.appendChild(elem);
+    get_info('mitarbeiter', {}, function (data) { select_update('edit_personal:'+number,data.info, 1); });
+
+    elem = document.createElement('br');
+    stunden.parentNode.appendChild(elem);
+
+    elem = document.createElement('input');
+    elem.id = 'edit_stunden:' + number;
+    elem.name = 'stunden:' + number;
+    elem.type = 'text';
+    elem.size = 5;
+    stunden.parentNode.appendChild(elem);
+
+    return false;
+}
+
+function replicate_geraete()
+{
+    var elem;
+    var geraet = document.getElementById('edit_geraet');
+    var anbaugeraet = document.getElementById('edit_anbaugeraet');
+    var geraetstunden = document.getElementById('edit_geraetstunden');
+
+    var number = get_number(geraet.parentNode.children[geraet.parentNode.children.length-1].name) + 1;
+
+    elem = document.createElement('br');
+    geraet.parentNode.appendChild(elem);
+
+    elem = document.createElement('select');
+    elem.id = 'edit_geraet:' + number;
+    elem.name = 'geraet:' + number;
+    elem.options[0] = new Option('Bitte wählen Sie','',false,false);
+    elem.onchange = function() { select_geraet(this); };
+    geraet.parentNode.appendChild(elem);
+    get_info('geraete', {}, function (data) { select_update('edit_geraet:'+number,data.info, 1); });
+
+    elem = document.createElement('br');
+    anbaugeraet.parentNode.appendChild(elem);
+
+    elem = document.createElement('select');
+    elem.id = 'edit_anbaugeraet:' + number;
+    elem.name = 'anbaugeraet:' + number;
+    anbaugeraet.parentNode.appendChild(elem);
+
+    elem = document.createElement('br');
+    geraetstunden.parentNode.appendChild(elem);
+
+    elem = document.createElement('input');
+    elem.id = 'edit_geraetstunden:' + number;
+    elem.name = 'geraetstunden:' + number;
+    elem.type = 'text';
+    elem.size = 5;
+    geraetstunden.parentNode.appendChild(elem);
+
+    return false;
+}
+
+function einsatz_check()
+{
+    var fields = {
+       datum: 'Datum',
+       gebiet: 'Gebiet',
+       kostenstelle: 'Gewässer',
+       ordnung: 'Gewässerordnung',
+       arbeitsart: 'Arbeiten',
+       personal: 'Mitarbeiter',
+       stunden: 'Stunden'};
+
+    for (id in fields) {
+       var elem = document.getElementById('edit_' + id);
+
+       if (elem.nodeName.toLowerCase() == 'input') {
+           if (!elem.value.length) {
+               alert('Das Feld ' + fields[id] + ' muß ausgefüllt werden');
+               return false;
+           }
+           continue;
+       }
+
+       if (elem.nodeName.toLowerCase() == 'select') {
+           if (!elem.options[elem.selectedIndex].value.length) {
+               alert('Das Feld ' + fields[id] + ' muß ausgefüllt werden');
+               return false;
+           }
+           continue;
+       }
+    }
+
+    return true;
+}
+
+function einsatz_callback(data)
+{
+    info('Neuer Arbeitsbericht gespeichert');
+    window.location.reload();
+}
+
+function einsatz_submit()
+{
+    if (!einsatz_check()) return false;
+
+    var form = document.getElementById('edit_form');
+    var source = document.getElementById('source');
+    if (!source || !form) return false;
+
+    ajax_request('insert', Form.serialize(form) + '&source=' + source.innerHTML, einsatz_callback);
+
+    return false;
+}
+
+Rico.onLoad(form_onload);
+
+EOC;
+
+$formular = <<<EOC
+<div class="form">
+<form id="edit_form">
+
+<table width="100%" cellspacing="2" border="0">
+<tr>
+ <td width="25%"><label for="edit_datum">Datum</label></td>
+ <td width="25%"><label for="edit_gebiet">Gebiet</label></td>
+ <td width="25%"><label for="edit_kostenstelle">Gewässer</label></td>
+ <td width="25%"><label for="edit_ordnung">Gewässerordnung</label></td>
+</tr>
+<tr>
+ <td width="25%"><input id="edit_datum" name="datum" size="8">&nbsp;<img class="calendar" src="images/icons/calendar.gif" onclick="calendar('edit_datum',event)"></td>
+ <td width="25%"><select id="edit_gebiet" name="gebiet" onchange="select_gebiet(this)"><option value="">Bitte wählen Sie</option></select></td>
+ <td width="25%"><select id="edit_kostenstelle" name="kostenstelle"><option value="">Bitte wählen Sie</option></select></td>
+ <td width="25%"><select id="edit_ordnung" name="ordnung"><option value="">Bitte wählen Sie</option></select></td>
+</tr>
+</table>
+
+<table width="100%" cellspacing="2" border="0" style="border: 1px solid #CCC; border-bottom: none; background: #e5e5e5;">
+<tr>
+ <td width="30%"><label for="edit_personal">Mitarbeiter</label></td>
+ <td width="30%"><label for="edit_stunden">Stunden</label></td>
+ <td width="30%">&nbsp;</td>
+ <td width="10%">&nbsp;</td>
+</tr>
+<tr>
+ <td width="30%"><select id="edit_personal" name="personal"><option value="">Bitte wählen Sie</option></select></td>
+ <td width="30%"><input id="edit_stunden" name="stunden" size="5" type="text"></td>
+  <td width="30%">&nbsp;</td>
+ <td width="10%" align="right"valign="bottom"><button onclick="return replicate_personal()" style="color: red; font-weight: bold;">+&nbsp;</button></td>
+</tr>
+</table>
+
+<table width="100%" cellspacing="2" border="0" style="border: 1px solid #CCC; background: #e5e5e5;">
+<tr>
+ <td width="30%"><label for="edit_geraet">Gerät</label></td>
+ <td width="30%"><label for="edit_anbaugeraet">Anbaugerät</label></td>
+ <td width="30%"><label for="edit_geraetstunden">Stunden</label></td>
+ <td width="10%">&nbsp;</td>
+</tr>
+<tr>
+ <td width="30%"><select id="edit_geraet" name="geraet" onchange="select_geraet(this)"><option value="">Bitte wählen Sie</option></select></td>
+ <td width="30%"><select id="edit_anbaugeraet" name="anbaugeraet"></select></td>
+ <td width="30%"><input id="edit_geraetstunden" name="geraetstunden" size="5" type="text"></td>
+ <td width="10%" align="right"valign="bottom"><button onclick="return replicate_geraete()" style="color: red; font-weight: bold;">+&nbsp;</button></td>
+</tr>
+</table>
+
+<table width="100%" cellspacing="2" border="0" style="margin-top: 5px;">
+<tr>
+ <td width="50%"><label for="edit_bemerkung">Bemerkung</label></td>
+ <td width="25%"><label for="edit_arbeitsart">Arbeiten</label></td>
+ <td width="25%"><label for="edit_akkord">Akkord</label></td>
+</tr>
+<tr>
+ <td width="50%" valign="top"><textarea id="edit_bemerkung" name="bemerkung" cols="66" rows="5"></textarea></td>
+ <td width="25%" valign="top"><select id="edit_arbeitsart" name="arbeitsart"><option value="">Bitte wählen Sie</option></select></td>
+ <td width="25%" valign="top"><input id="edit_akkord" name="akkord" type="checkbox"></td>
+</tr>
+</table>
+
+
+<div align="center" style="margin-bottom: 5px;"><button onclick="return einsatz_submit()">Hinzufügen</button></div>
+</form>
+</div>
+EOC;
+
+function einsatz_insert($mask)
+{
+  $date = explode('.',$_POST['datum']);
+  $date = sprintf('%04d-%02d-%02d', $date[2], $date[1], $date[0]);
+
+  $sql = sprintf("INSERT INTO einsatz (datum,ordnung,gebiet,kostenstelle,arbeiten,akkord,bemerkung,sys_user,sys_edit) " .
+                "VALUES ('%s',%d,%d,%d,%d,%d,%s,'%s',now())",
+                $date,
+                $_POST['ordnung'],
+                $_POST['gebiet'],
+                $_POST['kostenstelle'],
+                $_POST['arbeitsart'],
+                $_POST['akkord']==='on'?1:0,
+                empty($_POST['bemerkung']) ? 'NULL' : "'" . pg_escape_string($_POST['bemerkung']) . "'",
+                pg_escape_string($_SESSION['sys']['login']));
+
+  $sth = pg_query($sql);
+
+  if ($sth === false) {
+    error_log($sql . ': ' . pg_last_error());
+    return array('error' => pg_last_error(),
+                 'sql' => $sql);
+  }
+
+  $sql = "SELECT currval('einsatz_id_seq')";
+  $sth =  pg_query($sql);
+
+  if ($sth === false) {
+    error_log($sql . ': ' . pg_last_error());
+    return array('error' => pg_last_error(),
+                 'sql' => $sql);
+  }
+
+  $row = pg_fetch_assoc($sth);
+  $einsatz = $row['currval'];
+
+  $num = 0;$app='';
+  while (true) {
+    if ($num) {
+      $app = ':'.$num;
+      if (empty($_POST['personal'.$app])) break;
+    }
+    $_POST['stunden'.$app] = str_replace(',','.',$_POST['stunden'.$app]);
+    $sql = sprintf("INSERT INTO einsatzpersonal (einsatz,personal,stunden,sys_user,sys_edit) " .
+                  "VALUES (%d,%d,%.2f,'%s',now())",
+                  $einsatz,
+                  $_POST['personal'.$app],
+                  $_POST['stunden'.$app],
+                  pg_escape_string($_SESSION['sys']['login']));
+
+    $sth = pg_query($sql);
+
+    if ($sth === false) {
+      error_log($sql . ': ' . pg_last_error());
+      return array('error' => pg_last_error(),
+                   'sql' => $sql);
+    }
+
+    $num++;
+  }
+
+  $num = 0;$app='';
+  while (true) {
+    if ($num) $app = ':'.$num;
+    if (empty($_POST['geraet'.$app])) break;
+    $_POST['geraetstunden'.$app] = str_replace(',','.',$_POST['geraetstunden'.$app]);
+    $sql = sprintf("INSERT INTO einsatzgeraete (einsatz,geraet,anbaugeraet,stunden,sys_user,sys_edit) " .
+                  "VALUES (%d,%d,%s,%.2f,'%s',now())",
+                  $einsatz,
+                  $_POST['geraet'.$app],
+                  empty($_POST['anbaugeraet'.$app]) ? 'NULL' : intval($_POST['anbaugeraet'.$app]),
+                  $_POST['geraetstunden'.$app],
+                  pg_escape_string($_SESSION['sys']['login']));
+
+    $sth = pg_query($sql);
+
+    if ($sth === false) {
+      error_log($sql . ': ' . pg_last_error());
+      return array('error' => pg_last_error(),
+                   'sql' => $sql);
+    }
+
+    $num++;
+  }
+
+  return array('status' => true);
+}
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Arbeitsberichte',
+             'html' => $formular,
+             'insert' => einsatz_insert,
+             'info' => array(
+                             'gebiete' => array(
+                                           'sql' => 'SELECT id,gebiet AS text FROM gebiet ORDER BY text',
+                                           ),
+                             'ordnungen' => array(
+                                           'sql' => 'SELECT id,name AS text FROM ordnungen ORDER BY id',
+                                           ),
+                             'mitarbeiter' => array(
+                                           'sql' => 'SELECT id,name || \', \' || vorname AS text FROM personal WHERE aktiv = 1 ORDER BY text',
+                                           ),
+                             'geraete' => array(
+                                           'sql' => 'SELECT id,geraet AS text FROM gerate ORDER BY text',
+                                           ),
+                             'arbeitsarten' => array(
+                                           'sql' => 'SELECT id,arbeitsart AS text FROM arbeitsarten ORDER BY text',
+                                           ),
+                             'kostenstellen' => array(
+                                           'sql' => 'SELECT id,kostenstelle AS text FROM kostenstellen WHERE gebiet = {gebiet} ORDER BY text',
+                                           ),
+                             'anbaugeraete' => array(
+                                           'sql' => 'SELECT anbaugeraete.id,anbaugeraet as text FROM anbaugeraete ' .
+                                                    'JOIN gerate ON anbaugeraete.geraetetyp = gerate.geraetetyp ' .
+                                                    'WHERE gerate.id = {geraet} ORDER BY text',
+                                           ),
+                                  ),
+             );
+
+?>
diff --git a/gebiet.php b/gebiet.php
new file mode 100644 (file)
index 0000000..79aa1c7
--- /dev/null
@@ -0,0 +1,27 @@
+<?php
+
+$mask = array(
+             'table' => 'gebiet',
+             'title' => 'Gebiete',
+             'sort' => 1,
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'width' => 300,
+                                           ),
+                             ),
+             'edit' => array(
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/geraete.php b/geraete.php
new file mode 100644 (file)
index 0000000..7c4b2fe
--- /dev/null
@@ -0,0 +1,76 @@
+<?php
+
+$mask = array(
+             'table' => 'gerate',
+             'title' => 'Geräte',
+             'join' => array('geraetetypen ON gerate.geraetetyp = geraetetypen.id'),
+             'sort' => 1,
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'gerate.id',
+                                           ),
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'width' => 200,
+                                           ),
+                             'geraetetyp' => array(
+                                           'name' => 'Gerätetyp',
+                                           'width' => 70,
+                                           'sql' => 'geraetetypen.name',
+                                           'specs' => "ClassName: 'aligncenter'",
+                                           ),
+                             'gerstdsatz' => array(
+                                           'name' => 'Stundensatz',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 80,
+                                           ),
+                             'geraetkst_intern' => array(
+                                           'name' => 'interne Kosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 100,
+                                           ),
+                             'geraetkst_extern' => array(
+                                           'name' => 'externe Kosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 100,
+                                           ),
+                             ),
+             'edit' => array(
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'geraetetyp' => array(
+                                           'name' => 'Gerätetyp',
+                                           'type' => 'select',
+                                           'options' => 'SELECT id,name AS text FROM geraetetypen ORDER BY name',
+                                           ),
+                             'gerstdsatz' => array(
+                                           'name' => 'Stundensatz',
+                                           'type' => 'decimal',
+                                           'size' => 15,
+                                           'required' => true,
+                                           ),
+                             'geraetkst_intern' => array(
+                                           'name' => 'Gerätekosten intern',
+                                           'type' => 'decimal',
+                                           'size' => 15,
+                                           'required' => true,
+                                           ),
+                             'geraetkst_extern' => array(
+                                           'name' => 'Gerätekosten extern',
+                                           'type' => 'decimal',
+                                           'size' => 15,
+                                           'required' => true,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/gereinigt.php b/gereinigt.php
new file mode 100644 (file)
index 0000000..15637cc
--- /dev/null
@@ -0,0 +1,111 @@
+<?php
+
+$jscode[] = "Rico.moduleDependencies['checkboxDB'] = ['+LiveGrid', '../ricoTableColumnDB.js'];";
+$jscode[] = "Rico.loadModule('checkboxDB');";
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['gereinigt.year'])) $_SESSION['gereinigt.year'] = date('Y');
+
+$details = <<<EOC
+<div style="padding-left: 3px; margin-top: -10px;">
+<b>Bemerkungen</b><br>
+<div id="detail_bemerkungen"></div>
+</div>
+EOC;
+
+function gebiete()
+{
+  $sql = "SELECT id,gebiet FROM gebiet ORDER BY id";
+  $sth = pg_query($sql);
+
+  $result = array();
+  while ($row = pg_fetch_assoc($sth))
+    $result[] = sprintf("%d: '%s'", $row['id'], $row['gebiet']);
+
+  return '{' . implode(', ', $result) . '}';
+}
+
+$mask = array(
+             'table' => 'kostenstellen',
+             'title' => 'Gewässer gereinigt?',
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(true),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['gereinigt.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'sql' => 'kostenstellen.id',
+                                           'visible' => false,
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'sql' => 'gebiet',
+                                           'control' => "new Rico.TableColumn.lookup(".gebiete().", 0, '')",
+                                           'specs' => "filterUI: 's'",
+                                           'width' => 270,
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'specs' => "filterUI: 's'",
+                                           'width' => 270,
+                                           ),
+                             'gereinigt' => array(
+                                           'name' => 'gereinigt',
+                                           'width' => 65,
+                                           'sql' => '(SELECT count(*) FROM gereinigt WHERE jahr = '
+                                               . intval($_SESSION['gereinigt.year'])
+                                               . ' AND gereinigt.kostenstelle = kostenstellen.id)',
+                                           'control' => "new Rico.TableColumn.checkboxDB(0, 'ajax/ricoUpdateConnection.php')",
+                                           'specs' => "filterUI: 'c', ClassName: 'aligncenter', canSort: false",
+                                           'update' => array('table' => 'gereinigt',
+                                                             'basecol' => 'jahr',
+                                                             'baseval' => $_SESSION['gereinigt.year'],
+                                                             'refcol' => 'kostenstelle',
+                                                             'reftable' => 'kostenstellen',
+                                                             'refid' => 'kostenstellen.id',
+                                                             ),
+                                           ),
+                             ),
+             'details' => array(
+                                'html' => $details,
+                                'list' => array(
+                                                'bemerkungen' => array('fetch' => fetch_bemerkungen),
+                                                ),
+
+                             ),
+             );
+
+function fetch_bemerkungen()
+{
+  $sql = <<<EOC
+SELECT datum, bemerkung FROM einsatz
+WHERE date_part('year', datum) = %d
+    AND kostenstelle = %d
+    AND bemerkung IS NOT NULL
+ORDER BY datum
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['gereinigt.year'], $_POST['id']);
+
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  if (!count($res)) return 'keine Bemerkungen vorhanden';
+
+  $ret = '';
+  foreach ($res as $row) {
+    $d = explode('-',$row['datum']);
+    $ret .= sprintf('<p style="margin-top: 0; margin-bottom: 2px;">%d.%d: %s</p>', $d[2], $d[1], $row['bemerkung']);
+  }
+
+  return $ret;
+}
+?>
diff --git a/korrektur.php b/korrektur.php
new file mode 100644 (file)
index 0000000..093c2ed
--- /dev/null
@@ -0,0 +1,679 @@
+<?php
+
+$jscode[] = <<<EOC
+
+post_save = false;
+
+function button_delete(obj)
+{
+  var foo = this.id.split(':');
+  var number = foo[1];
+  var bar = foo[0].split('_');
+  var name = bar[1];
+  var id = document.getElementById('edit_id:'+number);
+  var source = document.getElementById('edit_source');
+
+  id.parentNode.style.display = 'none';
+
+  info('');
+  var params = 'id='+id.value + '&source='+source.value + '&table=einsatz'+name;
+
+  ajax_request('delete', params, false);
+
+  return false;
+}
+
+var personal = false;
+var geraete = false;
+var gebiete = false;
+var kostenstelle_selected = false;
+var ordnungen = false;
+var arbeiten = false;
+var akkord = [{id: 0, text: 'kein Akkord'},
+             {id: 1, text: 'Akkord'}];
+function label_create(name, number, text)
+{
+  var elem = document.createElement('label');
+  elem.for = 'edit_'+name+':' + number;
+  elem.innerHTML = text;
+  return elem;
+}
+
+function input_create(name,number,size,value)
+{
+  var elem = document.createElement('input');
+  var appendix = '';
+  if (number !== false) appendix = ':' + number;
+
+  elem.id = 'edit_'+name+appendix;
+  elem.name = name + appendix;
+  elem.type = 'text';
+  elem.size = size;
+  elem.value = value;
+  return elem;
+}
+
+function button_create(name,number)
+{
+  var elem = document.createElement('button');
+  var appendix = '';
+  if (number !== false) appendix = ':' + number;
+
+  elem.id = 'button_'+name+appendix;
+  elem.onclick = button_delete;
+  elem.innerHTML = 'Löschen';
+  return elem;
+}
+
+function select_create(name,number,options,selected)
+{
+  var elem = document.createElement('select');
+  var appendix = '';
+  if (number !== false) appendix = ':' + number;
+
+  elem.id = 'edit_'+name+appendix;
+  elem.name = name + appendix;
+
+  var idx;
+  for (var i=0; i < options.length; i++) {
+    elem.options[i] = new Option(options[i].text,options[i].id,false,false);
+    if (options[i].id == selected) idx = i;
+  }
+  elem.selectedIndex = idx;
+
+  return elem;
+}
+
+function textarea_create(name,number,cols,value)
+{
+  var elem = document.createElement('textarea');
+  var appendix = '';
+  if (number !== false) appendix = ':' + number;
+
+  elem.id = 'edit_'+name+appendix;
+  elem.name = name + appendix;
+  elem.value = value;
+  elem.cols = cols;
+
+  return elem;
+}
+
+function personal_add_record(obj,number,data)
+{
+  var div = document.createElement('div');
+  div.style.margin = '2px';
+  div.style.padding = '2px';
+  div.style.paddingLeft = '4px';
+  div.style.marginBottom = '4px';
+  div.style.border = '1px solid #aaaaaa';
+  div.style.backgroundColor = '#dbdbdb';
+  div.style.overflow = 'hidden';
+  obj.appendChild(div);
+
+  var elem = input_create('id',number,20,data.id);
+  elem.type = 'hidden';
+  div.appendChild(elem);
+  elem = label_create('personal',number,'Mitarbeiter');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('personal',number,personal,data.personal);
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = label_create('stunden',number,'Stunden');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = input_create('stunden',number,5,data.stunden);
+  div.appendChild(elem);
+  elem = button_create('personal',number);
+  elem.style.marginLeft = '20px';
+  div.appendChild(elem);
+}
+
+function select_geraet_callback(data)
+{
+  var id = 'edit_anbaugeraet:' + data.parameter.number;
+  var obj = document.getElementById(id);
+  var fix = 0;
+
+  if (data.parameter.selected == null) {
+    obj.options[0] = new Option('Bitte wählen Sie','',false,false);
+    fix = 1;
+  }
+
+  if (data.info.length)
+    select_update(id,data.info, fix);
+  else
+    obj.options.length = 0;
+
+  if (data.parameter.selected != null)
+    for (var i=0; i < data.info.length; i++)
+      if (data.info[i].id == data.parameter.selected)
+       obj.selectedIndex = i;
+}
+
+function select_geraet(event)
+{
+  var foo = event.originalTarget.id.split(':');
+  var number = foo[1];
+
+  get_info('anbaugeraete',
+          {geraet: event.originalTarget.options[event.originalTarget.selectedIndex].value, number: number},
+          select_geraet_callback);
+}
+
+function geraete_add_record(obj,number,data)
+{
+  var div = document.createElement('div');
+  div.style.margin = '2px';
+  div.style.padding = '2px';
+  div.style.paddingLeft = '4px';
+  div.style.marginBottom = '4px';
+  div.style.border = '1px solid #aaaaaa';
+  div.style.backgroundColor = '#dbdbdb';
+  div.style.overflow = 'hidden';
+  obj.appendChild(div);
+
+  var elem = input_create('id',number,20,data.id);
+  elem.type = 'hidden';
+  div.appendChild(elem);
+  elem = label_create('personal',number,'Gerät');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('geraet',number,geraete,data.geraet);
+  elem.onchange = select_geraet;
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+
+  elem = label_create('anbaugeraet',number,'Anbaugerät');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('anbaugeraet',number,[],data.anbaugeraet);
+  div.appendChild(elem);
+
+  get_info('anbaugeraete',
+          {geraet: data.geraet, number: number, selected: data.anbaugeraet, id: elem.id},
+          select_geraet_callback);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+
+  elem = label_create('stunden',number,'Stunden');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = input_create('stunden',number,5,data.stunden);
+  div.appendChild(elem);
+  elem = button_create('geraete',number);
+  elem.style.marginLeft = '20px';
+  div.appendChild(elem);
+}
+
+function kostenstellen_callback(data)
+{
+  select_update('edit_kostenstelle',data.info,0);
+
+  var obj = document.getElementById('edit_kostenstelle');
+  for (var i=0; i < obj.options.length; i++)
+    if  (obj.options[i].value == kostenstelle_selected) {
+      obj.selectedIndex = i;
+      break;
+    }
+}
+
+function select_gebiet_callback(data)
+{
+  var id = 'edit_kostenstelle';
+  var obj = document.getElementById(id);
+  var fix = 0;
+
+  if (data.parameter.selected == null) {
+    obj.options[0] = new Option('Bitte wählen Sie','',false,false);
+    fix = 1;
+  }
+
+  if (data.info.length)
+    select_update(id,data.info, fix);
+  else
+    obj.options.length = 0;
+
+  if (data.parameter.selected != null)
+    for (var i=0; i < data.info.length; i++)
+      if (data.info[i].id == data.parameter.selected)
+       obj.selectedIndex = i;
+}
+
+function select_gebiet(event)
+{
+  get_info('kostenstellen', {gebiet: event.originalTarget.options[event.originalTarget.selectedIndex].value},
+          select_gebiet_callback);
+}
+
+function einsatz_add_record(obj,data)
+{
+  var div = document.createElement('div');
+  div.style.margin = '2px';
+  div.style.padding = '2px';
+  div.style.paddingLeft = '4px';
+  div.style.marginBottom = '4px';
+  div.style.border = '1px solid #aaaaaa';
+  div.style.backgroundColor = '#dbdbdb';
+  div.style.overflow = 'hidden';
+  obj.appendChild(div);
+
+  var id = document.getElementById('edit_id');
+  id.value = data.id;
+
+  var elem = label_create('datum',false,'Datum');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = input_create('datum',false,12,data.datum);
+  div.appendChild(elem);
+
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = label_create('gebiet',false,'Gebiet');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('gebiet',false,gebiete,data.gebiet);
+  elem.onchange = select_gebiet;
+  div.appendChild(elem);
+
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = label_create('kostenstelle',false,'Gewässer');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  kostenstelle_selected = data.kostenstelle;
+  get_info('kostenstellen', {gebiet: data.gebiet, selected: data.kostenstelle}, select_gebiet_callback);
+
+  elem = select_create('kostenstelle',false,[],data.kostenstelle);
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+
+  elem = label_create('ordnung',false,'Ordnung');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('ordnung',false,ordnungen,data.ordnung);
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+
+  elem = label_create('arbeiten',false,'Arbeiten');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('arbeiten',false,arbeiten,data.arbeiten);
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+
+  elem = label_create('akkord',false,'Akkord');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = select_create('akkord',false,akkord,data.akkord);
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+
+  elem = label_create('bemerkung',false,'Bemerkung');
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+  elem = textarea_create('bemerkung',false,28,data.bemerkung);
+  div.appendChild(elem);
+  elem = document.createElement('br');
+  div.appendChild(elem);
+}
+
+function personal_load_callback(data)
+{
+  var div = document.getElementById('form_edit_parent');
+
+  while (div.childNodes.length)
+    div.removeChild(div.firstChild);
+
+  for (var i=0; i < data.info.length; i++) {
+    personal_add_record(div,i,data.info[i]);
+  }
+}
+
+function geraete_load_callback(data)
+{
+  var div = document.getElementById('form_edit_parent');
+
+  while (div.childNodes.length)
+    div.removeChild(div.firstChild);
+
+  for (var i=0; i < data.info.length; i++) {
+    geraete_add_record(div,i,data.info[i]);
+  }
+}
+
+function einsatz_load_callback(data)
+{
+  var div = document.getElementById('form_edit_parent');
+
+  while (div.childNodes.length)
+    div.removeChild(div.firstChild);
+
+  einsatz_add_record(div,data.info[0]);
+}
+
+function personal_load(id)
+{
+  get_info('einsatzpersonal', {id: id}, personal_load_callback);
+
+  var title = document.getElementById('mask_edit_title');
+  title.innerHTML = 'Personaleinsatz bearbeiten';
+
+  var type = document.getElementById('edit_data_type');
+  type.value = 'personal';
+
+  var button = document.getElementById('button_delete');
+  button.style.display = 'none';
+}
+
+function geraete_load(id)
+{
+  get_info('einsatzgeraete', {id: id}, geraete_load_callback);
+
+  var title = document.getElementById('mask_edit_title');
+  title.innerHTML = 'Geräteeinsatz bearbeiten';
+
+  var type = document.getElementById('edit_data_type');
+  type.value = 'geraete';
+
+  var button = document.getElementById('button_delete');
+  button.style.display = 'none';
+}
+
+function einsatz_load(id)
+{
+  get_info('einsatz', {id: id}, einsatz_load_callback);
+
+  var title = document.getElementById('mask_edit_title');
+  title.innerHTML = 'Einsatz bearbeiten';
+
+  var type = document.getElementById('edit_data_type');
+  type.value = 'einsatz';
+
+  var button = document.getElementById('button_delete');
+  button.style.display = '';
+}
+
+function load_data()
+{
+  get_info('mitarbeiter', {}, function (data) { personal = data.info });
+  get_info('geraete', {}, function (data) { geraete = data.info });
+  get_info('gebiete', {}, function (data) { gebiete = data.info });
+  get_info('ordnungen', {}, function (data) { ordnungen = data.info });
+  get_info('arbeiten', {}, function (data) { arbeiten = data.info });
+}
+
+function form_onload()
+{
+  setTimeout(load_data, 500);
+  var form = document.getElementById('form_edit');
+
+  var div = document.createElement('div');
+  div.id = 'form_edit_parent';
+  form.insertBefore(div,form.firstChild);
+
+  var elem = document.createElement('input');
+  elem.id = 'edit_data_type';
+  elem.name = 'data_type';
+  elem.type = 'hidden';
+  form.insertBefore(elem,form.firstChild);
+
+  var status = document.getElementById('form_status');
+  status.style.display = 'none';
+}
+
+Rico.onLoad(form_onload);
+
+EOC;
+
+function korrektur_save($mask)
+{
+  if ($_POST['data_type'] == 'personal') {
+    $num = 0;
+    while (true) {
+      $app = ':'.($num++);
+      if (empty($_POST['id'.$app])) break;
+
+      $sql = sprintf("SELECT personal,stunden FROM einsatzpersonal WHERE id = %d", $_POST['id'.$app]);
+      $sth = pg_query($sql);
+      $row = pg_fetch_assoc($sth);
+
+      $_POST['stunden'.$app] = str_replace(',','.',$_POST['stunden'.$app]);
+
+      if ($row['personal'] != $_POST['personal'.$app] ||
+         $row['stunden'] != $_POST['stunden'.$app]) {
+       $sql = sprintf("UPDATE einsatzpersonal SET personal=%d,stunden=%.2f,sys_user='%s',sys_edit=now() WHERE id = %d",
+                      $_POST['personal'.$app], $_POST['stunden'.$app],
+                      pg_escape_string($_SESSION['sys']['login']),
+                      $_POST['id'.$app]);
+       pg_query($sql);
+      }
+    }
+  } elseif ($_POST['data_type'] == 'geraete') {
+    $num = 0;
+    while (true) {
+      $app = ':'.($num++);
+      if (empty($_POST['id'.$app])) break;
+
+      $sql = sprintf("SELECT geraet,anbaugeraet,stunden FROM einsatzgeraete WHERE id = %d", $_POST['id'.$app]);
+      $sth = pg_query($sql);
+      $row = pg_fetch_assoc($sth);
+
+      $_POST['stunden'.$app] = str_replace(',','.',$_POST['stunden'.$app]);
+
+      if ($row['geraet'] != $_POST['geraet'.$app] ||
+         ($row['anbaugeraet'] === NULL && !empty($_POST['anbaugeraet'.$app])) ||
+         ($row['anbaugeraet'] !== NULL && $row['anbaugeraet'] != $_POST['anbaugeraet'.$app]) ||
+         $row['stunden'] != $_POST['stunden'.$app]) {
+       $anbaugeraet = empty($_POST['anbaugeraet'.$app]) ? 'NULL' : intval($_POST['anbaugeraet'.$app]);
+
+       $sql = sprintf("UPDATE einsatzgeraete SET geraet=%d,anbaugeraet=%s,stunden=%.2f,sys_user='%s',sys_edit=now() WHERE id = %d",
+                      $_POST['geraet'.$app],
+                      $anbaugeraet,
+                      $_POST['stunden'.$app],
+                      pg_escape_string($_SESSION['sys']['login']),
+                      $_POST['id'.$app]);
+       pg_query($sql);
+      }
+    }
+  } elseif ($_POST['data_type'] == 'einsatz') {
+    $sql = sprintf("SELECT datum,ordnung,gebiet,kostenstelle,arbeiten,akkord,bemerkung FROM einsatz WHERE id = %d", $_POST['id']);
+    $sth = pg_query($sql);
+    $row = pg_fetch_assoc($sth);
+
+    if ($row['datum'] != $_POST['datum'] ||
+       $row['ordnung'] != $_POST['ordnung'] ||
+       $row['gebiet'] != $_POST['gebiet'] ||
+       $row['kostenstelle'] != $_POST['kostenstelle'] ||
+       $row['arbeiten'] != $_POST['arbeiten'] ||
+       $row['akkord'] != $_POST['akkord'] ||
+       $row['bemerkung'] != $_POST['bemerkung']) {
+
+      $bemerkung = empty($_POST['bemerkung']) ? 'NULL' : "'".pg_escape_string($_POST['bemerkung'])."'";
+      $sql = sprintf("UPDATE einsatz SET datum='%s',ordnung=%d,gebiet=%d,kostenstelle=%d,arbeiten=%d,akkord=%d,bemerkung=%s, " .
+                    "sys_user='%s',sys_edit=now() WHERE id = %d",
+                    pg_escape_string($_POST['datum']),
+                    $_POST['ordnung'],
+                    $_POST['gebiet'],
+                    $_POST['kostenstelle'],
+                    $_POST['arbeiten'],
+                    $_POST['akkord'],
+                    $bemerkung,
+                    pg_escape_string($_SESSION['sys']['login']),
+                    $_POST['id']);
+      pg_query($sql);
+    }
+  } else {
+    return array('status' => false, 'error' => 'Unknown data type "'.$_POST['data_type'].'" for this call');
+  }
+
+  return array('status' => true);
+}
+
+function korrektur_delete($mask)
+{
+  if (!empty($_POST['table']) && is_array($mask['tables_delete']) && in_array($_POST['table'], $mask['tables_delete']))
+    return delete_or_copy_row($_POST['table'],$_POST['id']);
+
+  $id = $_POST['id'];
+  $subtables = array('einsatzpersonal','einsatzgeraete');
+  foreach ($subtables as $table) {
+    $sql = sprintf("SELECT id FROM %s WHERE einsatz = %d", $table, $id);
+    $sth = pg_query($sql);
+    $row = pg_fetch_assoc($sth);
+
+    $ret = delete_or_copy_row($table, $row['id']);
+
+    if ($ret['status'] !== true) return $ret;
+  }
+
+  return delete_or_copy_row($mask['table'], $id);
+}
+
+function gebiete()
+{
+  $sql = "SELECT id,gebiet FROM gebiet ORDER BY id";
+  $sth = pg_query($sql);
+
+  $result = array();
+  while ($row = pg_fetch_assoc($sth))
+    $result[] = sprintf("%d: '%s'", $row['id'], $row['gebiet']);
+
+  return '{' . implode(', ', $result) . '}';
+}
+
+$mask = array(
+             'table' => 'einsatz',
+             'tables_delete' => array('einsatzpersonal','einsatzgeraete'),
+             'title' => 'Korrektur von Arbeitsberichten',
+             'join' => array(
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'sql' => 'einsatz.id',
+                                           'width' => 60,
+                                           'visible' => false,
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'width' => 230,
+                                           'sql' => 'einsatz.gebiet',
+                                           'control' => "new Rico.TableColumn.lookup(".gebiete().", 0, '')",
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'width' => 230,
+                                           'sql' => 'kostenstellen.kostenstelle',
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Personal',
+                                           'width' => 230,
+                                           'sql' => "(SELECT name || ', ' || vorname FROM personal " . 
+                                                    "JOIN einsatzpersonal ON einsatzpersonal.personal = personal.id " .
+                                                    "WHERE einsatzpersonal.einsatz = einsatz.id LIMIT 1)",
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'controla' => array(
+                                           'name' => 'Personal',
+                                           'width' => 70,
+                                           'control' => "new Rico.TableColumn.link('javascript:personal_load({0})')",
+                                           'specs' => "ClassName: 'aligncenter', canSort: false",
+                                           'sql' => "'bearbeiten'",
+                                           ),
+                             'controlb' => array(
+                                           'name' => 'Geräte',
+                                           'width' => 70,
+                                           'control' => "new Rico.TableColumn.link('javascript:geraete_load({0})')",
+                                           'specs' => "ClassName: 'aligncenter', canSort: false",
+                                           'sql' => "'bearbeiten'",
+                                           ),
+                             'controlc' => array(
+                                           'name' => 'Einsatz',
+                                           'width' => 70,
+                                           'control' => "new Rico.TableColumn.link('javascript:einsatz_load({0})')",
+                                           'specs' => "ClassName: 'aligncenter', canSort: false",
+                                           'sql' => "'bearbeiten'",
+                                           ),
+                             ),
+             'edit' => array(),
+             'save' => korrektur_save,
+             'delete' => korrektur_delete,
+             'buttons' => array(
+                       'insert' => false,
+                       ),
+             'info' => array(
+                             'einheit' => array(
+                                           'sql' => 'SELECT einheiten.einheit FROM einheiten ' .
+                                                    'JOIN materialien ON materialien.einheit = einheiten.id ' .
+                                                    'WHERE materialien.id = {id}',
+                                           ),
+                             'einsatz' => array(
+                                           'sql' => "SELECT id,ordnung,datum,kostenstelle,gebiet,arbeiten,akkord,bemerkung " .
+                                           'FROM einsatz ' .
+                                           'WHERE id = {id}',
+                                           ),
+                             'einsatzpersonal' => array(
+                                           'sql' => "SELECT id,personal,stunden " .
+                                           'FROM einsatzpersonal ' .
+                                           'WHERE einsatz = {id}',
+                                           ),
+                             'einsatzgeraete' => array(
+                                           'sql' => "SELECT id,geraet,anbaugeraet,stunden " .
+                                           'FROM einsatzgeraete ' .
+                                           'WHERE einsatz = {id}',
+                                           ),
+                             'gebiete' => array(
+                                           'sql' => 'SELECT id,gebiet AS text FROM gebiet ORDER BY text',
+                                           ),
+                             'kostenstellen' => array(
+                                           'sql' => 'SELECT id,kostenstelle AS text FROM kostenstellen WHERE gebiet = {gebiet} ORDER BY text',
+                                           ),
+                             'ordnungen' => array(
+                                           'sql' => 'SELECT id,name AS text FROM ordnungen ORDER BY text',
+                                           ),
+                             'arbeiten' => array(
+                                           'sql' => 'SELECT id,arbeitsart AS text FROM arbeitsarten ORDER BY text',
+                                           ),
+                             'mitarbeiter' => array(
+                                           'sql' => 'SELECT id,name || \', \' || vorname AS text FROM personal WHERE aktiv = 1 ORDER BY text',
+                                           ),
+                             'geraete' => array(
+                                           'sql' => 'SELECT id,geraet AS text FROM gerate ORDER BY text',
+                                           ),
+                             'anbaugeraete' => array(
+                                           'sql' => 'SELECT anbaugeraete.id,anbaugeraet as text FROM anbaugeraete ' .
+                                                    'JOIN gerate ON anbaugeraete.geraetetyp = gerate.geraetetyp ' .
+                                                    'WHERE gerate.id = {geraet} ORDER BY text',
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/kosten_geraete.php b/kosten_geraete.php
new file mode 100644 (file)
index 0000000..525f053
--- /dev/null
@@ -0,0 +1,71 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['kosten_geraete.year'])) $_SESSION['kosten_geraete.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Kostenaufwand für Geräte',
+             'join' => array('gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['kosten_geraete.year']
+                      . " GROUP BY gerate.id,gerate.geraet,kostenstellen.kostenstelle,laenge",
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['kosten_geraete.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'gerate.id',
+                                           ),
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'sql' => "gerate.geraet",
+                                           'width' => 170,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'sql' => "kostenstellen.kostenstelle",
+                                           'width' => 200,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'laenge' => array(
+                                           'name' => 'Länge',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 70,
+                                           ),
+                             'gesamtkosten' => array(
+                                           'name' => 'Gesamtkosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => 'sum(geraetstunden * gerate.geraetkst_intern + ' .
+                                                    'geraetstunden * anbaugeraete.anbkst_intern)',
+                                           ),
+                             'prolfdm' => array(
+                                           'name' => 'pro lfdm',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => 'sum(geraetstunden * gerate.geraetkst_intern + ' .
+                                                    'geraetstunden * anbaugeraete.anbkst_intern) / laenge',
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/kosten_kostenstellen.php b/kosten_kostenstellen.php
new file mode 100644 (file)
index 0000000..67db473
--- /dev/null
@@ -0,0 +1,68 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['kosten_kostenstellen.year'])) $_SESSION['kosten_kostenstellen.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Kostenaufwand für Kostenstellen',
+             'join' => array('personal ON einsatz.personal = personal.id',
+                             'gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['kosten_kostenstellen.year']
+                      . " GROUP BY kostenstellen.kostenstelle,kostenstellen.id,laenge",
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['kosten_kostenstellen.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'kostenstellen.id',
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'sql' => "kostenstellen.kostenstelle",
+                                           'width' => 250,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'laenge' => array(
+                                           'name' => 'Länge',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 70,
+                                           ),
+                             'gesamtkosten' => array(
+                                           'name' => 'Gesamtkosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => 'sum(stunden * personal.persstdsatz + ' . 
+                                                    'geraetstunden * gerate.geraetkst_intern + ' .
+                                                    'geraetstunden * anbaugeraete.anbkst_intern)',
+                                           ),
+                             'prolfdm' => array(
+                                           'name' => 'pro lfdm',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => 'sum(stunden * personal.persstdsatz + ' . 
+                                                    'geraetstunden * gerate.geraetkst_intern + ' .
+                                                    'geraetstunden * anbaugeraete.anbkst_intern) / laenge',
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/kosten_mitarbeiter.php b/kosten_mitarbeiter.php
new file mode 100644 (file)
index 0000000..1d980a6
--- /dev/null
@@ -0,0 +1,64 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['kosten_mitarbeiter.year'])) $_SESSION['kosten_mitarbeiter.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Kostenaufwand für Mitarbeiter',
+             'join' => array('personal ON einsatz.personal = personal.id',
+                             'gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['kosten_mitarbeiter.year']
+                      . " GROUP BY personal.id,personal.name,personal.vorname,personal.persstdsatz,arbeitsarten.arbeitsart",
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['kosten_mitarbeiter.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'personal.id',
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Mitarbeiter',
+                                           'sql' => "vorname || ' ' || name",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'arbeitsart' => array(
+                                           'name' => 'Arbeitsart',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'stunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'sql' => 'sum(stunden)',
+                                           'width' => 70,
+                                           ),
+                             'gesamtkosten' => array(
+                                           'name' => 'Kosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => 'sum(stunden) * personal.persstdsatz',
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/kostenstellen.php b/kostenstellen.php
new file mode 100644 (file)
index 0000000..4992fe4
--- /dev/null
@@ -0,0 +1,65 @@
+<?php
+
+function gebiete()
+{
+  $sql = "SELECT id,gebiet FROM gebiet ORDER BY id";
+  $sth = pg_query($sql);
+
+  $result = array();
+  while ($row = pg_fetch_assoc($sth))
+    $result[] = sprintf("%d: '%s'", $row['id'], $row['gebiet']);
+
+  return '{' . implode(', ', $result) . '}';
+}
+
+$mask = array(
+             'table' => 'kostenstellen',
+             'title' => 'Kostenstellen',
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'sql' => 'kostenstellen.id',
+                                           'visible' => false,
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'specs' => "filterUI: 't'",
+                                           'width' => 220,
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'sql' => 'kostenstellen.gebiet',
+                                           'control' => "new Rico.TableColumn.lookup(".gebiete().", 0, '')",
+                                           'specs' => "filterUI: 's'",
+                                           'width' => 220,
+                                           ),
+                             'laenge' => array(
+                                           'name' => 'Länge',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 65,
+                                           ),
+                             ),
+             'edit' => array(
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'type' => 'select',
+                                           'option_empty' => 'Bitte wählen Sie',
+                                           'options' => 'SELECT id,gebiet AS text FROM gebiet ORDER BY gebiet',
+                                           ),
+                             'laenge' => array(
+                                           'name' => 'Länge',
+                                           'type' => 'decimal',
+                                           'width' => 65,
+                                           'required' => true,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/materialien.php b/materialien.php
new file mode 100644 (file)
index 0000000..7ecfb50
--- /dev/null
@@ -0,0 +1,55 @@
+<?php
+
+$mask = array(
+             'table' => 'materialien',
+             'title' => 'Materialien',
+             'sort' => 1,
+             'join' => array('einheiten ON materialien.einheit = einheiten.id'),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'materialien.id',
+                                           ),
+                             'material' => array(
+                                           'name' => 'Material',
+                                           'width' => 270,
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'einheit' => array(
+                                           'name' => 'Einheit',
+                                           'width' => 70,
+                                           'sql' => 'einheiten.einheit',
+                                           'specs' => 'canSort: false',
+                                           ),
+                             'preis' => array(
+                                           'name' => 'Preis',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 50,
+                                           ),
+                             ),
+             'edit' => array(
+                             'material' => array(
+                                           'name' => 'Material',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'einheit' => array(
+                                           'name' => 'Einheit',
+                                           'type' => 'select',
+                                           'options' => 'SELECT id,einheit AS text FROM einheiten ORDER BY einheit',
+                                           'size' => 10,
+                                           ),
+                             'preis' => array(
+                                           'name' => 'Preis',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'comment' => 'immer brutto',
+                                           'required' => true,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/materialverbrauch.php b/materialverbrauch.php
new file mode 100644 (file)
index 0000000..cc79bce
--- /dev/null
@@ -0,0 +1,172 @@
+<?php
+
+$jscode[] = <<<EOC
+
+post_save = materialverbrauch_post_save;
+pre_insert = materialverbrauch_pre_insert;
+
+function materialverbrauch_post_save()
+{
+  var einsatz = document.getElementById('edit_einsatz');
+
+  var params = 'source=' + grid.tableId.substr(5) + '&id=' + einsatz.value;
+  ajax_request('details', params, details_callback);
+
+  var material = document.getElementById('edit_material');
+  material.selectedIndex = 0;
+}
+
+function materialverbrauch_pre_insert()
+{
+  var material = document.getElementById('edit_material');
+  var einsatz = document.getElementById('edit_einsatz');
+  var menge = document.getElementById('edit_menge');
+
+  if (!material.options[material.selectedIndex].value.length) {
+    alert('Sie haben kein Material ausgewählt.');
+    return false;
+  }
+
+  if (!einsatz.value.length) {
+    alert('Sie haben keinen Einsatzbericht ausgewählt.');
+    return false;
+  }
+
+  if (!menge.value.length) {
+    alert('Sie haben keine Menge angegeben.');
+    return false;
+  }
+
+  return true;
+}
+
+function einheit_callback(data)
+{
+  var einheit = document.getElementById('edit_einheit');
+
+  if (!einheit) return false;
+
+  if (data.info[0].einheit.length)
+    einheit.innerHTML = data.info[0].einheit;
+}
+
+function select_material(obj)
+{
+  get_info('einheit', {id: obj.options[obj.selectedIndex].value}, einheit_callback);
+}
+
+EOC;
+
+function fetch_tbody()
+{
+  $sql = 'SELECT materialien.material,menge,einheiten.einheit FROM materialverbrauch '
+    . 'JOIN materialien ON materialverbrauch.material = materialien.id '
+    . 'JOIN einheiten ON materialien.einheit = einheiten.id '
+    . 'WHERE einsatz = ' . intval($_POST['id']) . ' '
+    . 'ORDER BY materialien.material';
+
+  $data = query_db($sql);
+
+  $ret = '';
+  foreach ($data as $row)
+    $ret .= sprintf('<tr><td>%s</td><td align="right">%.2f %s</td></tr>',
+                   $row['material'], $row['menge'], $row['einheit']);
+
+  return $ret;
+}
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Materialverbrauch zu Arbeitsberichten',
+             'join' => array(
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             'gebiet ON kostenstellen.gebiet = gebiet.id',
+                             ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'sql' => 'einsatz.id',
+                                           'width' => 60,
+                                           'visible' => false,
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'width' => 230,
+                                           'sql' => 'gebiet.gebiet',
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'width' => 230,
+                                           'sql' => 'kostenstellen.kostenstelle',
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Personal',
+                                           'width' => 230,
+                                           'sql' => "(SELECT name || ', ' || vorname FROM personal JOIN einsatzpersonal ON einsatzpersonal.personal = personal.id WHERE einsatzpersonal.einsatz = einsatz.id LIMIT 1)",
+                                           ),
+                             'material' => array(
+                                           'name' => 'Material',
+                                           'width' => 70,
+                                           'control' => "new Rico.TableColumn.checkbox('t','f','0',true)",
+                                           'sql' => '(SELECT count(*) > 0 FROM materialverbrauch WHERE einsatz = einsatz.id)',
+                                           'specs' => "ClassName: 'aligncenter', canSort: false",
+                                           ),
+                             ),
+             'details' => array(
+                                'html' => '<table id="materialverbrauch_table" cellspacing="0" style="margin-top: -13px;" border="0" width="100%">'.
+                                '<thead>'.
+                                '<tr bgcolor="#ffd76d" style="color: #444;"><th>Material</th><th>Menge</th></tr>'.
+                                '</thead>'.
+                                '<tbody id="detail_materialverbrauch_tbody"></tbody>'.
+                                '</table>',
+                                'list' => array(
+                                                'materialverbrauch_tbody' => array(
+                                                                                   'fetch' => fetch_tbody,
+                                                                                   ),
+                                                ),
+                                ),
+             'edit_title' => 'Materialverbrauch vermerken',
+             'edit_table' => 'materialverbrauch',
+             'edit' => array(
+                             'einsatz' => array(
+                                           'name' => 'Einsatz',
+                                           'type' => 'hidden',
+                                           'size' => 10,
+                                           'sql' => 'id',
+                                           ),
+                             'material' => array(
+                                           'name' => 'Material',
+                                           'type' => 'select',
+                                           'options' => 'SELECT id,material AS text FROM materialien ORDER BY text',
+                                           'option_empty' => 'Bitte wählen Sie',
+                                           'onchange' => 'select_material(this)',
+                                           'sql' => false,
+                                           ),
+                             'menge' => array(
+                                           'name' => 'Menge',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'comment' => ' <span id="edit_einheit"></span>',
+                                           'sql' => false,
+                                           'required' => true,
+                                           ),
+                             ),
+             'buttons' => array(
+                                'save' => false,
+                                'delete' => false,
+                                ),
+             'info' => array(
+                             'einheit' => array(
+                                           'sql' => 'SELECT einheiten.einheit FROM einheiten ' .
+                                                    'JOIN materialien ON materialien.einheit = einheiten.id ' .
+                                                    'WHERE materialien.id = {id}',
+                                           ),
+                                  ),
+             );
+
+?>
diff --git a/overview_geraete.php b/overview_geraete.php
new file mode 100644 (file)
index 0000000..0c23628
--- /dev/null
@@ -0,0 +1,70 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['overview_geraete.year'])) $_SESSION['overview_geraete.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Stundenübersicht pro Gerät',
+             'join' => array('gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['overview_geraete.year'],
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['overview_geraete.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'einsatz.id',
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           ),
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'sql' => "gerate.geraet",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'anbaugeraet' => array(
+                                           'name' => 'Anbaugerät',
+                                           'sql' => "anbaugeraete.anbaugeraet",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'arbeitsarten' => array(
+                                           'name' => 'Arbeiten',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'geraetstunden' => array(
+                                           'name' => 'Gerätstunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           ),
+                             'anbaugeraetstunden' => array(
+                                           'name' => 'Anbaugerätstunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/overview_gewaesser.php b/overview_gewaesser.php
new file mode 100644 (file)
index 0000000..b057886
--- /dev/null
@@ -0,0 +1,137 @@
+<?php
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Einsatzübersicht nach Gewässer',
+             'subtitle' => 'Altenoyther Feld',
+             'join' => array('personal ON einsatz.personal = personal.id',
+                             'gebiet ON einsatz.gebiet = gebiet.id',
+                             'gerate ON einsatz.geraet = gerate.id',
+                             'anbaugeraete ON einsatz.anbaugeraet = anbaugeraete.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                        . ' AND einsatz.gebiet = 41', # 20 Altenoyther Feld
+             'sort' => 1,
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'einsatz.id',
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Personal',
+                                           'sql' => "personal.name || ', ' || personal.vorname",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'stunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           ),
+                             'stunde' => array(
+                                           'name' => 'Stunde',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           'sql' => 'stunden * personal.persstdsatz',
+                                           ),
+                             'lfdm' => array(
+                                           'name' => 'Lfdm',
+                                           'visible' => false,
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 3, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           'sql' => '(stunden * personal.persstdsatz) / laenge',
+                                           ),
+#
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'sql' => 'gerate.geraet',
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'geraetstunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           ),
+                             'geraetstunde' => array(
+                                           'name' => 'Stunde',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           'sql' => 'geraetstunden * gerate.gerstdsatz',
+                                           ),
+                             'geraetlfdm' => array(
+                                           'name' => 'Lfdm',
+                                           'visible' => false,
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 3, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           'sql' => '(geraetstunden * gerate.gerstdsatz) / laenge',
+                                           ),
+#
+                             'anbaugeraet' => array(
+                                           'name' => 'Anbaugerät',
+                                           'sql' => 'anbaugeraete.anbaugeraet',
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'anbaugeraetstunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           ),
+                             'anbaugeraetstunde' => array(
+                                           'name' => 'Stunde',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           'sql' => 'anbaugeraetstunden * anbaugeraete.anbgerstdsatz',
+                                           ),
+                             'anbaugeraetlfdm' => array(
+                                           'name' => 'Lfdm',
+                                           'visible' => false,
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 3, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           'sql' => '(anbaugeraetstunden * anbaugeraete.anbgerstdsatz) / laenge',
+                                           ),
+                             ),
+             'details' => array(
+                                'title' => 'Details',
+                                'subtitle' => 'Informationen zum Gewässer',
+                                'list' => array(
+                                     'gewaesser' => array(
+                                                   'name' => 'Gewässer',
+                                                   ),
+                                     'laenge' => array(
+                                                   'name' => 'Länge',
+                                                   ),
+                                     'kosten' => array(
+                                                   'name' => 'Gesamtkosten',
+                                                   'format' => '%.2f',
+                                                   ),
+                                     'lfdm' => array(
+                                                   'name' => 'Lfdm',
+                                                   'format' => '%.2f',
+                                                   ),
+                                     'zusatz' => array(
+                                                   'name' => 'Zusätze',
+                                                   ),
+                                                ),
+                                   ),
+             );
+
+?>
diff --git a/overview_kosten.php b/overview_kosten.php
new file mode 100644 (file)
index 0000000..5942301
--- /dev/null
@@ -0,0 +1,294 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['overview_kosten.year'])) $_SESSION['overview_kosten.year'] = date('Y');
+
+$details = <<<EOC
+<div style="padding-left: 3px; margin-top: -10px;">
+<b>Gebiet</b>: <span id="detail_gebiet"></span><br />
+<b>Gewässer</b>: <span id="detail_kostenstelle"></span><br />
+<b>Länge</b>: <span id="detail_laenge"></span><br />
+<b>Gesamtkosten</b>: <span id="detail_gewaesserkosten"></span><br />
+<b>Personalkosten</b>: <span id="detail_personalkosten"></span><br />
+<b>Gerätekosten</b>: <span id="detail_geraetekosten"></span><br />
+<b>Gewässer gereinigt</b>: <span id="detail_gereinigt"></span>
+</div>
+EOC;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Kostenaufwand für Kostenstellen',
+             'join' => array(
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'gebiet ON einsatz.gebiet = gebiet.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             'ordnungen ON einsatz.ordnung = ordnungen.id',
+                             ),
+#            'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+#                     . " AND date_part('year', datum) = " . $_SESSION['overview_kosten.year'],
+             'where' => "date_part('year', datum) = " . $_SESSION['overview_kosten.year'],
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['overview_kosten.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'width' => 60,
+                                           'sql' => 'einsatz.id',
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           ),
+                             'akkord' => array(
+                                           'name' => 'Akkord',
+                                           'control' => "new Rico.TableColumn.checkbox('1','0','0',true)",
+                                           'width' => 50,
+                                           'specs' => "ClassName: 'aligncenter'",
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'sql' => "gebiet.gebiet",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'sql' => "kostenstellen.kostenstelle",
+                                           'width' => 250,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'ordnung' => array(
+                                           'name' => 'Ordnung',
+                                           'sql' => "ordnungen.name",
+                                           'width' => 80,
+                                           'specs' => "filterUI: 's'",
+                                           'visible' => false,
+                                           ),
+                             'arbeitsart' => array(
+                                           'name' => 'Arbeiten',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 250,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'laenge' => array(
+                                           'name' => 'Länge',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 70,
+                                           ),
+                             'personalkosten' => array(
+                                           'name' => 'Mitarbeiterkosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => '
+(SELECT sum(stunden * personal.persstdsatz) FROM einsatzpersonal
+JOIN personal ON einsatzpersonal.personal = personal.id
+WHERE einsatz = einsatz.id)
+',
+                                           ),
+                             'geraetkosten' => array(
+                                           'name' => 'Gerätkosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => '
+(SELECT sum(stunden * gerate.geraetkst_intern + stunden * CASE WHEN anbaugeraete.anbkst_intern IS NULL THEN 0.0 ELSE anbaugeraete.anbkst_intern END)
+FROM einsatzgeraete
+JOIN gerate ON einsatzgeraete.geraet = gerate.id
+LEFT JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+WHERE einsatz = einsatz.id)
+',
+                                           ),
+                             'gesamtkosten' => array(
+                                           'name' => 'Gesamtkosten',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 90,
+                                           'sql' => '
+(SELECT sum(stunden * personal.persstdsatz) FROM einsatzpersonal
+JOIN personal ON einsatzpersonal.personal = personal.id
+WHERE einsatz = einsatz.id)
++
+CASE WHEN
+(SELECT sum(stunden * gerate.geraetkst_intern + stunden * CASE WHEN anbaugeraete.anbkst_intern IS NULL THEN 0.0 ELSE anbaugeraete.anbkst_intern END)
+FROM einsatzgeraete
+JOIN gerate ON einsatzgeraete.geraet = gerate.id
+LEFT JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+WHERE einsatz = einsatz.id) IS NULL THEN 0.0
+ELSE (SELECT sum(stunden * gerate.geraetkst_intern + stunden * CASE WHEN anbaugeraete.anbkst_intern IS NULL THEN 0.0 ELSE anbaugeraete.anbkst_intern END)
+FROM einsatzgeraete
+JOIN gerate ON einsatzgeraete.geraet = gerate.id
+LEFT JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+WHERE einsatz = einsatz.id) END
+',
+                                           ),
+                             'prolfdm' => array(
+                                           'name' => 'pro lfdm',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => '
+((SELECT sum(stunden * personal.persstdsatz) FROM einsatzpersonal
+JOIN personal ON einsatzpersonal.personal = personal.id
+WHERE einsatz = einsatz.id)
++
+CASE WHEN
+(SELECT sum(stunden * gerate.geraetkst_intern + stunden * CASE WHEN anbaugeraete.anbkst_intern IS NULL THEN 0.0 ELSE anbaugeraete.anbkst_intern END)
+FROM einsatzgeraete
+JOIN gerate ON einsatzgeraete.geraet = gerate.id
+LEFT JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+WHERE einsatz = einsatz.id) IS NULL THEN 0.0
+ELSE (SELECT sum(stunden * gerate.geraetkst_intern + stunden * CASE WHEN anbaugeraete.anbkst_intern IS NULL THEN 0.0 ELSE anbaugeraete.anbkst_intern END)
+FROM einsatzgeraete
+JOIN gerate ON einsatzgeraete.geraet = gerate.id
+LEFT JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+WHERE einsatz = einsatz.id) END) / laenge
+',
+
+
+                                           ),
+                             ),
+             'details' => array(
+                                'html' => $details,
+                                'list' => array(
+                                                'gebiet' => array('fetch' => fetch_gebiet),
+                                                'kostenstelle' => array('fetch' => fetch_kostenstelle),
+                                                'laenge' => array('fetch' => fetch_laenge),
+                                                'gewaesserkosten' => array('fetch' => fetch_gewaesserkosten),
+                                                'personalkosten' => array('fetch' => fetch_personalkosten),
+                                                'geraetekosten' => array('fetch' => fetch_geraetekosten),
+                                                'gereinigt' => array('fetch' => fetch_gereinigt),
+                                                ),
+
+                             ),
+             );
+
+function fetch_gebiet()
+{
+  $sql = 'SELECT gebiet.gebiet FROM gebiet JOIN einsatz ON einsatz.gebiet = gebiet.id WHERE einsatz.id = ' . intval($_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['gebiet'];
+}
+
+function fetch_kostenstelle()
+{
+  $sql = 'SELECT kostenstellen.kostenstelle FROM kostenstellen JOIN einsatz ON einsatz.kostenstelle = kostenstellen.id WHERE einsatz.id = ' . intval($_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['kostenstelle'];
+}
+
+function fetch_laenge()
+{
+  $sql = 'SELECT laenge FROM kostenstellen JOIN einsatz ON einsatz.kostenstelle = kostenstellen.id WHERE einsatz.id = ' . intval($_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['laenge'] . ' m';
+}
+
+function fetch_gewaesserkosten()
+{
+  $sql = <<<EOC
+  SELECT sum(stunden * persstdsatz) AS gesamt
+  FROM einsatzpersonal
+  JOIN personal ON einsatzpersonal.personal = personal.id
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_kosten.year'], $_POST['id']);
+  $pkosten = query_db($sql);
+  if ($pkosten === false) return '';
+
+  $sql = <<<EOC
+  SELECT sum(stunden * gerate.geraetkst_intern +
+             stunden * anbaugeraete.anbkst_intern) AS gesamt
+  FROM einsatzgeraete
+  JOIN gerate ON einsatzgeraete.geraet = gerate.id
+  JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_kosten.year'], $_POST['id']);
+  $gkosten = query_db($sql);
+  if ($gkosten === false) return '';
+
+  return sprintf('%.2f &euro;', $pkosten[0]['gesamt'] + $gkosten[0]['gesamt']);
+}
+
+function fetch_personalkosten()
+{
+  $sql = <<<EOC
+  SELECT sum(stunden * persstdsatz) AS gesamt
+  FROM einsatzpersonal
+  JOIN personal ON einsatzpersonal.personal = personal.id
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_kosten.year'], $_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return sprintf('%.2f &euro;', $res[0]['gesamt']);
+}
+
+function fetch_geraetekosten()
+{
+  $sql = <<<EOC
+  SELECT sum(stunden * gerate.geraetkst_intern +
+             stunden * anbaugeraete.anbkst_intern) AS gesamt
+  FROM einsatzgeraete
+  JOIN gerate ON einsatzgeraete.geraet = gerate.id
+  JOIN anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_kosten.year'], $_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return sprintf('%.2f &euro;', $res[0]['gesamt']);
+}
+
+function fetch_gereinigt()
+{
+  $sql = <<<EOC
+  SELECT count(*) AS count
+  FROM gereinigt
+  JOIN kostenstellen ON gereinigt.kostenstelle = kostenstellen.id
+  JOIN einsatz ON einsatz.kostenstelle = kostenstellen.id
+  WHERE jahr = %d AND einsatz.id = %d
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_kosten.year'], $_POST['id']);
+
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['count'] == 0 ? 'nein' : 'ja';
+}
+
+?>
diff --git a/overview_kostenstellen.php b/overview_kostenstellen.php
new file mode 100644 (file)
index 0000000..0428cbd
--- /dev/null
@@ -0,0 +1,92 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['overview_kostenstellen.year'])) $_SESSION['overview_kostenstellen.year'] = 0;
+
+function gebiete()
+{
+  $sql = "SELECT id,gebiet FROM gebiet ORDER BY id";
+  $sth = pg_query($sql);
+
+  $result = array();
+  while ($row = pg_fetch_assoc($sth))
+    $result[] = sprintf("%d: '%s'", $row['id'], $row['gebiet']);
+
+  return '{' . implode(', ', $result) . '}';
+}
+
+$mask = array(
+             'table' => 'kostenstellen',
+             'title' => 'Übersicht nach Kostenstellen',
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['overview_kostenstellen.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'width' => 60,
+                                           'sql' => 'kostenstellen.id',
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'sql' => 'kostenstellen.gebiet',
+                                           'control' => "new Rico.TableColumn.lookup(".gebiete().", 0, '')",
+                                           'width' => 220,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Gewässer',
+                                           'sql' => "kostenstellen.kostenstelle",
+                                           'width' => 220,
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'laenge' => array(
+                                           'name' => 'Länge',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'visible' => false,
+                                           ),
+                             'personalstunden' => array(
+                                           'name' => 'Personalstunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => "(SELECT sum(stunden) FROM einsatzpersonal WHERE einsatz IN " .
+                                                    "(SELECT id FROM einsatz WHERE kostenstelle = kostenstellen.id " .
+                                                    "AND date_part('year', datum) = " . $_SESSION['overview_kostenstellen.year'] . "))",
+                                           ),
+                             'geraetstunden' => array(
+                                           'name' => 'Gerätestunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => "(SELECT sum(stunden) FROM einsatzgeraete WHERE einsatz IN " .
+                                                    "(SELECT id FROM einsatz WHERE kostenstelle = kostenstellen.id " .
+                                                    "AND date_part('year', datum) = " . $_SESSION['overview_kostenstellen.year'] . "))",
+                                           ),
+                             'stdlfdm' => array(
+                                           'name' => 'Stunden pro lfdm',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => "((SELECT sum(stunden) FROM einsatzpersonal WHERE einsatz IN " .
+                                                    "(SELECT id FROM einsatz WHERE kostenstelle = kostenstellen.id " .
+                                                    "AND date_part('year', datum) = " . $_SESSION['overview_kostenstellen.year'] . ")) + ".
+                                                    "(SELECT sum(stunden) FROM einsatzgeraete WHERE einsatz IN " .
+                                                    "(SELECT id FROM einsatz WHERE kostenstelle = kostenstellen.id " .
+                                                    "AND date_part('year', datum) = " . $_SESSION['overview_kostenstellen.year'] . "))) / laenge",
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/overview_mitarbeiter.php b/overview_mitarbeiter.php
new file mode 100644 (file)
index 0000000..61b45ec
--- /dev/null
@@ -0,0 +1,57 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['overview_mitarbeiter.year'])) $_SESSION['overview_mitarbeiter.year'] = 0;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Gesamtstundenaufwand pro Mitarbeiter',
+             'join' => array('personal ON einsatz.personal = personal.id',
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             ),
+             'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+                      . " AND date_part('year', datum) = " . $_SESSION['overview_mitarbeiter.year'],
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['overview_mitarbeiter.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'sql' => 'einsatz.id',
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Mitarbeiter',
+                                           'sql' => "personal.name || ', ' || personal.vorname",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'arbeitsarten' => array(
+                                           'name' => 'Arbeiten',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'stunden' => array(
+                                           'name' => 'Stunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           ),
+                             ),
+             );
+
+?>
diff --git a/overview_stunden.php b/overview_stunden.php
new file mode 100644 (file)
index 0000000..1caeef0
--- /dev/null
@@ -0,0 +1,253 @@
+<?php
+
+include_once($_SESSION['sys']['basedir'].'/lib/overview_utils.php');
+
+if (empty($_SESSION['overview_stunden.year'])) $_SESSION['overview_stunden.year'] = 0;
+
+$details = <<<EOC
+<div style="padding-left: 3px; margin-top: -10px;">
+<b>Gebiet</b>: <span id="detail_gebiet"></span><br>
+<b>Gewässer</b>: <span id="detail_kostenstelle"></span><br>
+<b>Länge</b>: <span id="detail_laenge"></span><br>
+<b>Gesamtstunden</b>: <span id="detail_gewaesserstunden"></span><br>
+<b>Personalstunden</b>: <span id="detail_personalstunden"></span><br>
+<b>Gerätestunden</b>: <span id="detail_geraetestunden"></span><br>
+<b>Gewässer gereinigt</b>: <span id="detail_gereinigt"></span>
+</div>
+EOC;
+
+$mask = array(
+             'table' => 'einsatz',
+             'title' => 'Stundenübersicht nach Kostenstellen',
+             'join' => array(
+                             'arbeitsarten ON einsatz.arbeiten = arbeitsarten.id',
+                             'einsatzpersonal ON einsatzpersonal.einsatz = einsatz.id',
+                             'ordnungen ON einsatz.ordnung = ordnungen.id',
+                             'LEFT einsatzgeraete ON einsatzgeraete.einsatz = einsatz.id',
+                             'gebiet ON einsatz.gebiet = gebiet.id',
+                             'kostenstellen ON einsatz.kostenstelle = kostenstellen.id',
+                             'personal ON einsatzpersonal.personal = personal.id',
+                             'LEFT gerate ON einsatzgeraete.geraet = gerate.id',
+                             'LEFT anbaugeraete ON einsatzgeraete.anbaugeraet = anbaugeraete.id',
+                             ),
+#            'where' => "arbeitsarten.arbeitsart <> 'Urlaub' AND arbeitsarten.arbeitsart <> 'Krank' AND arbeitsarten.arbeitsart <> 'Sonstige Fehltage'"
+#                     . " AND date_part('year', datum) = " . $_SESSION['overview_stunden.year'],
+             'where' => "date_part('year', datum) = " . $_SESSION['overview_stunden.year'],
+             'select' => array(
+                               'title' => 'Auswahl',
+                               'options' => discover_years(),
+                               'default' => 'Jahr wählen',
+                               'selected' => $_SESSION['overview_stunden.year'],
+                               'onchange' => "setvar(this,'year',select_year_calback,select_year_status)",
+                               ),
+             'variables' => array(
+                                  'year' => array(),
+                                  ),
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           'width' => 60,
+                                           'sql' => 'einsatz.id',
+                                           ),
+                             'datum' => array(
+                                           'name' => 'Datum',
+                                           'type' => 'date',
+                                           'width' => 70,
+                                           ),
+                             'akkord' => array(
+                                           'name' => 'Akkord',
+                                           'control' => "new Rico.TableColumn.checkbox('1','0','0',true)",
+                                           'width' => 50,
+                                           'specs' => "ClassName: 'aligncenter'",
+                                           ),
+                             'gebiet' => array(
+                                           'name' => 'Gebiet',
+                                           'sql' => "gebiet.gebiet",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'kostenstelle' => array(
+                                           'name' => 'Kostenstelle',
+                                           'sql' => "kostenstellen.kostenstelle",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'ordnung' => array(
+                                           'name' => 'Ordnung',
+                                           'sql' => "ordnungen.name",
+                                           'width' => 80,
+                                           'specs' => "filterUI: 's'",
+                                           'visible' => false,
+                                           ),
+                             'arbeitsarten' => array(
+                                           'name' => 'Arbeiten',
+                                           'sql' => "arbeitsarten.arbeitsart",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'personal' => array(
+                                           'name' => 'Mitarbeiter',
+                                           'sql' => "personal.name || ', ' || personal.vorname",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'stunden' => array(
+                                           'name' => 'Mitarbeiterstunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => 'einsatzpersonal.stunden'
+                                           ),
+                             'geraet' => array(
+                                           'name' => 'Gerät',
+                                           'sql' => "gerate.geraet",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'anbaugeraet' => array(
+                                           'name' => 'Anbaugerät',
+                                           'sql' => "anbaugeraete.anbaugeraet",
+                                           'width' => 150,
+                                           'specs' => "filterUI: 's'",
+                                           ),
+                             'geraetstunden' => array(
+                                           'name' => 'Gerätstunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => 'einsatzgeraete.stunden'
+                                           ),
+                             'gesamtstunden' => array(
+                                           'name' => 'Gesamtstunden',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'sql' => 'einsatzpersonal.stunden + einsatzgeraete.stunden',
+                                           'visible' => false,
+                                           ),
+                             ),
+             'details' => array(
+                                'html' => $details,
+                                'list' => array(
+                                                'gebiet' => array('fetch' => fetch_gebiet),
+                                                'kostenstelle' => array('fetch' => fetch_kostenstelle),
+                                                'laenge' => array('fetch' => fetch_laenge),
+                                                'gewaesserstunden' => array('fetch' => fetch_gewaesserstunden),
+                                                'personalstunden' => array('fetch' => fetch_personalstunden),
+                                                'geraetestunden' => array('fetch' => fetch_geraetestunden),
+                                                'gereinigt' => array('fetch' => fetch_gereinigt),
+                                                ),
+
+                             ),
+             );
+
+function fetch_gebiet()
+{
+  $sql = 'SELECT gebiet.gebiet FROM gebiet JOIN einsatz ON einsatz.gebiet = gebiet.id WHERE einsatz.id = ' . intval($_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['gebiet'];
+}
+
+function fetch_kostenstelle()
+{
+  $sql = 'SELECT kostenstellen.kostenstelle FROM kostenstellen JOIN einsatz ON einsatz.kostenstelle = kostenstellen.id WHERE einsatz.id = ' . intval($_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['kostenstelle'];
+}
+
+function fetch_laenge()
+{
+  $sql = 'SELECT laenge FROM kostenstellen JOIN einsatz ON einsatz.kostenstelle = kostenstellen.id WHERE einsatz.id = ' . intval($_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['laenge'] . ' m';
+}
+
+function fetch_gewaesserstunden()
+{
+  $sql = <<<EOC
+  SELECT sum(stunden) AS gesamt
+  FROM einsatzpersonal
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_stunden.year'], $_POST['id']);
+  $pstunden = query_db($sql);
+  if ($pstunden === false) return '';
+
+  $sql = <<<EOC
+  SELECT sum(stunden) AS gesamt
+  FROM einsatzgeraete
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_stunden.year'], $_POST['id']);
+  $gstunden = query_db($sql);
+  if ($gstunden === false) return '';
+
+  return sprintf('%.2f', $pstunden[0]['gesamt'] + $gstunden[0]['gesamt']);
+}
+
+function fetch_personalstunden()
+{
+  $sql = <<<EOC
+  SELECT sum(stunden) AS gesamt
+  FROM einsatzpersonal
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_stunden.year'], $_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return sprintf('%.2f', $res[0]['gesamt']);
+}
+
+function fetch_geraetestunden()
+{
+  $sql = <<<EOC
+  SELECT sum(stunden) AS gesamt
+  FROM einsatzgeraete
+  WHERE einsatz IN
+    (SELECT id FROM einsatz WHERE date_part('year', datum) = %d AND kostenstelle =
+    (SELECT kostenstelle FROM einsatz WHERE id = %d))
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_stunden.year'], $_POST['id']);
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return sprintf('%.2f', $res[0]['gesamt']);
+}
+
+function fetch_gereinigt()
+{
+  $sql = <<<EOC
+  SELECT count(*) AS count
+  FROM gereinigt
+  JOIN kostenstellen ON gereinigt.kostenstelle = kostenstellen.id
+  JOIN einsatz ON einsatz.kostenstelle = kostenstellen.id
+  WHERE jahr = %d AND einsatz.id = %d
+EOC;
+
+  $sql = sprintf($sql, $_SESSION['overview_kosten.year'], $_POST['id']);
+
+  $res = query_db($sql);
+  if ($res === false) return '';
+
+  return $res[0]['count'] == 0 ? 'nein' : 'ja';
+}
+
+?>
diff --git a/personal.php b/personal.php
new file mode 100644 (file)
index 0000000..03182d0
--- /dev/null
@@ -0,0 +1,178 @@
+<?php
+
+$mask = array(
+             'table' => 'personal',
+             'title' => 'Personalinformationen',
+             'sort' => 1,
+             'list' => array(
+                             'id' => array(
+                                           'name' => 'ID',
+                                           'visible' => false,
+                                           ),
+                             'name' => array(
+                                           'name' => 'Name',
+                                           'width' => 100,
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'vorname' => array(
+                                           'name' => 'Vorname',
+                                           'width' => 100,
+                                           'specs' => "filterUI: 't'",
+                                           ),
+                             'anschrift' => array(
+                                           'name' => 'Anschrift',
+                                           'width' => 160,
+                                           ),
+                             'plz' => array(
+                                           'name' => 'PLZ',
+                                           'width' => 40,
+                                           ),
+                             'ort' => array(
+                                           'name' => 'Wohnort',
+                                           'width' => 100,
+                                           ),
+                             'telefon' => array(
+                                           'name' => 'Telefon',
+                                           'width' => 100,
+                                           'visible' => false,
+                                           'specs' => 'canSort: false',
+                                           ),
+                             'mobil' => array(
+                                           'name' => 'Mobil',
+                                           'width' => 100,
+                                           'visible' => false,
+                                           'specs' => 'canSort: false',
+                                           ),
+                             'email' => array(
+                                           'name' => 'E-Mail',
+                                           'width' => 100,
+                                           'visible' => false,
+                                           ),
+                             'persstdsatz' => array(
+                                           'name' => 'Satz',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 50,
+                                           ),
+                             'jahresakkord' => array(
+                                           'name' => 'Akkord',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           ),
+                             'akkord_vorjahr' => array(
+                                           'name' => 'Akkord Vorjahr',
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignrightpad', canSort: false",
+                                           'width' => 60,
+                                           'visible' => false,
+                                           ),
+                             'akkordzuschlag' => array(
+                                           'name' => 'Zuschlag',
+                                           'visible' => false,
+                                           'type' => 'number',
+                                           'specs' => "decPlaces: 2, ClassName: 'alignright', canSort: false",
+                                           'width' => 70,
+                                           ),
+                             ),
+/*
+             'details' => array(
+                                'title' => 'Details',
+                                'subtitle' => 'Informationen zur Person',
+                                'list' => array(
+                                     'personal' => array(
+                                                   'name' => 'Name',
+                                                   ),
+                                     'vorname' => array(
+                                                   'name' => 'Vorname',
+                                                   ),
+                                     'persstdsatz' => array(
+                                                   'name' => 'Stundensatz',
+                                                   'format' => '%.2f',
+                                                   ),
+                                     'jahresakkord' => array(
+                                                   'name' => 'Jahresakkord',
+                                                   'format' => '%.2f',
+                                                   ),
+                                     'akkordzuschlag' => array(
+                                                   'name' => 'Akkordzuschlag',
+                                                   'format' => '%.2f',
+                                                   ),
+                                                ),
+                                   ),
+*/
+             'edit' => array(
+                             'name' => array(
+                                           'name' => 'Name',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'vorname' => array(
+                                           'name' => 'Vorname',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'anschrift' => array(
+                                           'name' => 'Anschrift',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'required' => true,
+                                           ),
+                             'plz' => array(
+                                           'name' => 'PLZ',
+                                           'type' => 'number',
+                                           'size' => 6,
+                                           'null' => true,
+                                           ),
+                             'ort' => array(
+                                           'name' => 'Wohnort',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'null' => true,
+                                           ),
+                             'telefon' => array(
+                                           'name' => 'Telefon',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'null' => true,
+                                           ),
+                             'mobil' => array(
+                                           'name' => 'Mobil',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'null' => true,
+                                           ),
+                             'email' => array(
+                                           'name' => 'E-Mail',
+                                           'type' => 'text',
+                                           'size' => 24,
+                                           'null' => true,
+                                           ),
+                             'persstdsatz' => array(
+                                           'name' => 'Stundensatz',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'null' => true,
+                                           ),
+                             'jahresakkord' => array(
+                                           'name' => 'Jahresakkord',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'required' => true,
+                                           ),
+                             'akkord_vorjahr' => array(
+                                           'name' => 'Akkord Vorjahr',
+                                           'type' => 'decimal',
+                                           'size' => 10,
+                                           'null' => true,
+                                           ),
+                             'aktiv' => array(
+                                           'name' => 'aktiv',
+                                           'type' => 'boolean',
+                                           ),
+                             ),
+             );
+
+?>