Blame | Last modification | View Log | RSS feed
<?
function SelectTemp($db, $prnum) {
$query = "select tmp_lfd, tmp_miname, tmp_periode, tmp_plan, ";
$query .= "tmp_ist, tmp_taname, tmp_status, tmp_hash, tmp_plnum ";
$query .= "from TempPlan order by tmp_periode";
if (!($result = QueryDB($db, $query)))
return;
$anz = numrowsDB($result);
$i = 0;
$dbstr = "\nLFD. Ma. Periode Plan IST PlNr. St. PrNr. Hashwert\n";
// 1234567890123456789012345678901234567890123456789
// xxxxx_xxxxx_xx.xx.xxxx_xx,xxx_xx,xxx_xxxxx_xx_xxxxxx
while ($i < $anz) {
$data = fetchDB($result, $i);
$tmp_lfd = $data[0];
$tmp_miname = $data[1];
$tmp_periode = $data[2];
$tmp_plan = $data[3];
$tmp_ist = $data[4];
$tmp_taname = $data[5];
$tmp_status = $data[6];
$tmp_hash = $data[7];
$tmp_plnum = $data[8];
$ss = sprintf("%5d %5d %02d.%02d.%4d %6s %6s %5d %2d %6d %s\n", $tmp_lfd,
$tmp_miname, gmdate("j", $tmp_periode), gmdate("n", $tmp_periode),
gmdate("Y", $tmp_periode), FormatNum($tmp_plan, 3), FormatNum($tmp_ist, 3),
$tmp_plnum, $tmp_status, $prnum, $tmp_hash);
$dbstr .= $ss;
$i++;
}
if ($anz > 0)
DEBUG($dbstr);
}
function CreateTempPlan($db, $pl_num, $prnum, $dfrom, $dto, $quiet=false, $minum=0, $ta=false, $ph=false) {
global $leneinheit;
global $phase;
$mi_num = $_REQUEST['mi_num'];
$f_tasks = $_REQUEST['fields_0'];
$pjclosed = $_REQUEST['pjclosed'];
if ($phase)
$f_phase = $_REQUEST['fields_6'];
if ((!isset($mi_num) || $mi_num == 0) && $minum > 0)
$mi_num = $minum;
if ($ta == true)
$f_tasks = $ta;
if ($ph == true)
$f_phase = $pa;
if (isset($pjclosed) && CheckTrue($pjclosed))
$pjc = ",6";
# Als erstes erzeugen wir eine temporaere Tabelle in die wir die
# einzelnen Daten unseres Plans zusammentragen und korrekt zuordnen.
#
$query = "create local temporary table TempPlan (";
$query .= "tmp_lfd integer not null unique, tmp_miname integer,";
$query .= "tmp_phase integer, tmp_periode integer, tmp_plan double precision,";
$query .= "tmp_ist double precision, tmp_taname varchar(50), tmp_notiz varchar(8192),";
$query .= "tmp_status smallint, tmp_hash char(34), tmp_plnum integer, ";
$query .= "tmp_start integer, tmp_duration smallint, ";
$query .= "constraint \"tempplan_pkey\" primary key (\"tmp_lfd\"))";
$result = QueryDB($db, $query);
if (!$result)
return false;
# Ermitteln ob es sich um ein Projekt oder um ein Konto handelt.
$query = "select pr_status from project where pr_num = $prnum";
if (!($result = QueryDB($db, $query)))
return false;
$data = fetchDB($result, 0);
$pr_status = $data[0];
# Nun erzeugen wir einen Plan, ausgehend von den Tasks. Das ist der
# erste von insgesamt zwei Schritten!
# Hier muessen wir zwischen einem Projekt und einem Konto
# unterscheiden. Konten haben keine Zuordnungen zu einem Task!
#
if ($pr_status == 0) { // 0 = Projekt!
$query = "select al_hours, al_pstart";
if ($phase && $f_phase)
$query .= ", al_phase ";
if ($f_tasks)
$query .= ", ta_name ";
$query .= ", ta_notiz, al_ressource, ta_hash, ta_plnum, pl_status, ";
$query .= "ta_start, ta_duration ";
$query .= "from allocation, task, plan where ";
// $query .= "pl_num = ta_plnum and pl_prnum = $prnum and ";
$query .= "pl_num = ta_plnum and pl_num = $pl_num and ";
// $query .= "ta_num = al_task and ta_plnum = $pl_num and ";
$query .= "ta_num = al_task and pl_status in (2,3,4,5 $pjc) and ";
$query .= "ta_meeting = 0 and al_pstart between $dfrom and $dto ";
if (isset($mi_num) && $mi_num > 0)
$query .= "and al_ressource = $mi_num ";
} else {
$query = "select distinct on (wd_minum) ta_num, ta_name,wd_minum,";
$query .= "ta_hash, ta_plnum, ta_start, ta_duration from wdone, task ";
$query .= "where ta_plnum = $pl_num and wd_task = ta_num ";
if (isset($mi_num) && $mi_num > 0)
$query .= "and wd_minum = $mi_num ";
}
if (!($result = QueryDB($db, $query)))
return false;
$numrows = numrowsDB($result);
if ($numrows <= 0) {
if (!$quiet)
Error("Das Projekt $prnum enthält für den Zeitraum vom " . gmdate("n.Y", $dfrom) . " bis " . gmdate("n.Y", $dto) . " einen Plan ohne Tasks ($mi_num)!");
}
$rows = 0;
$zaehler = 1;
while ($rows < $numrows) {
$data = fetchDB($result, $rows);
if ($pr_status == 0) {
$al_hours = $data[0];
$al_pstart = $data[1];
$x = 2;
if ($phase && $f_phase) {
$al_phase = $data[$x];
$x++;
} else
$al_phase = 0;
if ($f_tasks) {
$ta_name = $data[$x];
$x++;
} else
$ta_name = "";
$ta_notiz = $data[$x];
$x++;
$al_ressource = $data[$x];
$x++;
$ta_hash = $data[$x];
$x++;
$ta_plnum = $data[$x];
$x++;
$pl_status = $data[$x];
$x++;
$ta_start = $data[$x];
$x++;
$ta_duration = $data[$x];
} else {
$al_hours = 0;
$al_pstart = $dfrom;
$ta_num = $data[0];
$al_phase = 0;
if ($f_tasks)
$ta_name = $data[1];
else
$ta_name = "";
$al_ressource = $data[2];
$ta_notiz = "";
$ta_hash = $data[3];
$ta_plnum = $data[4];
$ta_start = $data[5];
$ta_duration = $data[6];
$pl_status = 0;
}
$mon = gmdate("n", $al_pstart);
$year = gmdate("Y", $al_pstart);
$al_pstart = gmmktime(0, 0, 0, $mon, 1, $year);
$flag = false;
$query = "select tmp_lfd, tmp_plan, tmp_plnum, tmp_hash, tmp_taname from TempPlan where ";
$query .= "tmp_periode = $al_pstart and tmp_phase = $al_phase ";
$query .= "and tmp_miname = $al_ressource ";
if ($f_tasks)
$query .= "and tmp_hash = '$ta_hash' ";
// $query .= "and (tmp_hash = '$ta_hash' or tmp_taname = '$ta_name') ";
if (!($res = QueryDB($db, $query)))
return false;
if (numrowsDB($res) > 0) {
$data = fetchDB($res, 0);
$tmp_lfd = $data[0];
$tmp_plan = $data[1];
$tmp_plnum = $data[2];
$tmp_hash = $data[3];
$tmp_taname = $data[4];
if ($tmp_plnum < $ta_plnum && ($tmp_hash == $ta_hash || ($tmp_taname == $ta_name && $f_tasks))) {
$tmp_plan = $al_hours;
$tmp_plnum = $ta_plnum;
$tmp_hash = $ta_hash;
} else if (!$f_tasks)
$tmp_plan += $al_hours;
$query = "update TempPlan set tmp_plan = $tmp_plan, tmp_plnum = $tmp_plnum, tmp_hash = '$tmp_hash' ";
$query .= "where tmp_lfd = $tmp_lfd";
if (!QueryDB($db, $query))
return false;
} else
$flag = true;
if ($flag) {
$ta_name = addslashes($ta_name);
$ta_notiz = addslashes($ta_notiz);
$query = "insert into TempPlan (tmp_lfd, tmp_miname, tmp_phase,";
$query .= "tmp_periode, tmp_plan, tmp_ist,tmp_taname,tmp_notiz,";
$query .= "tmp_status, tmp_hash, tmp_plnum, tmp_start, tmp_duration) ";
$query .= "values ($zaehler, $al_ressource, $al_phase, $al_pstart,";
$query .= "$al_hours, 0, '$ta_name', '$ta_notiz', $pr_status,";
$query .= "'$ta_hash', $ta_plnum, $ta_start, $ta_duration)";
if (!QueryDB($db, $query))
return false;
$zaehler++;
}
$rows++;
}
# Nun suchen wir alle IST-Meldungen und ergaenzen die Daten, sofern
# sie bereits vorhanden sind, oder fuegen einen neuen Datensatz ein.
#
$query = "select wd_minum, wd_datum, wd_hours / $leneinheit,";
$query .= "ta_hash, ta_plnum, ta_start, ta_duration, wd_task ";
if ($phase && $f_phase)
$query .= ", wd_phase ";
if ($f_tasks)
$query .= ", ta_name ";
$query .= "from wdone, task where ";
$query .= "wd_prnum = $prnum and ta_num = wd_task and ";
$query .= "wd_datum between $dfrom and $dto ";
if (isset($mi_num) && $mi_num > 0)
$query .= "and wd_minum = $mi_num ";
if (!($result = QueryDB($db, $query)))
return false;
$numrows = numrowsDB($result);
if ($numrows <= 0) {
if (!$quiet)
Error("Zum Projekt $prnum gibt es für den Zeitraum vom " . gmdate("n.Y", $dfrom) . " bis " . gmdate("n.Y", $dto) . " keine IST-Buchungen ($mi_num)!");
return true;
}
$rows = 0;
while ($rows < $numrows) {
$data = fetchDB($result, $rows);
$wd_minum = $data[0];
$wd_datum = $data[1];
$wd_ist = $data[2];
$ta_hash = $data[3];
$ta_plnum = $data[4];
$ta_start = $data[5];
$ta_duration = $data[6];
$wd_task = $data[7];
$x = 8;
if ($phase && $f_phase) {
$wd_phase = $data[$x];
$x++;
} else
$wd_phase = 0;
if ($f_tasks) {
$ta_name = $data[$x];
$x++;
} else
$ta_name = "";
$mon = gmdate("n", $wd_datum);
$year = gmdate("Y", $wd_datum);
$per = gmmktime(0, 0, 0, $mon, 1, $year);
$slash_name = addslashes($ta_name);
$query = "select tmp_lfd, tmp_ist, tmp_plan, tmp_plnum, tmp_hash ";
$query .= "from TempPlan where ";
$query .= "tmp_miname = $wd_minum and tmp_periode =$per ";
if ($f_tasks)
$query .= "and (tmp_hash = '$ta_hash' or tmp_taname = '$slash_name')";
if ($f_phase)
$query .= " and tmp_phase = $wd_phase";
$result2 = QueryDB($db, $query);
if (!$result2)
return false;
$nr = numrowsDB($result2);
if ($nr > 0) {
$data = fetchDB($result2, 0);
$tmp_lfd = $data[0];
$tmp_ist = $data[1];
$tmp_plan = $data[2];
$tmp_plnum = $data[3];
$tmp_hash = $data[4];
$ist_alt = $wd_ist;
$wd_ist += $tmp_ist;
$query = "update TempPlan set tmp_ist = $wd_ist ";
# Handelt es sich um einen unterschiedlichen Plan und es
# wurden keine Tasks gewaehlt, dann muessen wir den Planwert
# suchen und ebenfalls aufaddieren.
if ($ta_plnum < $pl_num && $ist_alt > 0 && $tmp_plan <= 0.0 && !$f_tasks && $ta_hash != $tmp_hash) {
$mon = gmdate("n", $wd_datum);
$year = gmdate("Y", $wd_datum);
$wd_datum = gmmktime(0, 0, 0, $mon, 1, $year);
$per_end = gmmktime(0, 0, 0, $mon, daysinmonth($mon, $year), $year);
$xquery = "select al_hours from allocation where ";
$xquery .= "al_task = $wd_task and al_ressource = $wd_minum and ";
$xquery .= "al_pstart between $wd_datum and $per_end";
if (!($resal = QueryDB($db, $xquery)))
return false;
if (numrowsDB($resal) > 0) {
$data = fetchDB($resal, 0);
$al_hours = $data[0];
$query .= ", tmp_plan = tmp_plan + $al_hours ";
}
}
$query .= "where tmp_lfd = $tmp_lfd";
if (!QueryDB($db, $query))
return false;
} else {
# Da es sich um einen neuen IST-Wert handelt, der moeglicherweise
# von einem aelteren Plan stammt, muessen wir den Planwert dazu
# ermitteln.
# Korrektur 12.07.2006: Planwerte von aelteren Plaenen duerfen
# nicht hinzaddiert werden!
$mon = gmdate("n", $wd_datum);
$year = gmdate("Y", $wd_datum);
$wd_datum = gmmktime(0, 0, 0, $mon, 1, $year);
$per_end = gmmktime(0, 0, 0, $mon, daysinmonth($mon, $year), $year);
// $query = "select al_hours from allocation where ";
// $query .= "al_task = $wd_task and al_ressource = $wd_minum and ";
// $query .= "al_pstart between $wd_datum and $per_end";
// if (!($resal = QueryDB($db, $query)))
// return false;
// if (numrowsDB($resal) > 0) {
// $data = fetchDB($resal, 0);
// $al_hours = $data[0];
// } else
$al_hours = 0.0;
$ta_name = addslashes($ta_name);
$query = "insert into TempPlan (tmp_lfd, tmp_miname, tmp_phase,";
$query .= "tmp_periode, tmp_plan,tmp_ist,tmp_taname,tmp_status,";
$query .= "tmp_hash, tmp_plnum, tmp_start, tmp_duration) ";
$query .= "values ($zaehler, $wd_minum, $wd_phase, $wd_datum,";
$query .= "$al_hours, $wd_ist, '$ta_name', $pr_status,";
$query .= "'$ta_hash', $ta_plnum, $ta_start, $ta_duration)";
if (!QueryDB($db, $query))
return false;
$zaehler++;
}
$rows++;
}
return true;
}
function ShowTempPlan($db=-1, $pr_num, $dfrom, $dto) {
global $datetime;
if ($db == -1)
$db = OpenDB();
$query = "select tmp_lfd, mi_nname, mi_vname, tmp_periode, tmp_plan,";
$query .= "tmp_ist, tmp_taname, tmp_notiz, tmp_status, tmp_hash, tmp_plnum ";
$query .= "from TempPlan, mitarbeiter where ";
$query .= "mi_num = tmp_miname order by tmp_hash";
if (!($result = QueryDB($db, $query)))
return false;
$anz = numrowsDB($result);
if ($anz <= 0)
return true;
?>
<table class="input">
<tr>
<td><table border=0>
<tr>
<td>Projektnummer:</td>
<td><? echo "$pr_num"; ?></td>
</tr>
<tr>
<td>Datum von:</td>
<td><? echo gmdate($datetime, $dfrom); ?></td>
</tr>
<tr>
<td>Datum bis:</td>
<td><? echo gmdate($datetime, $dto); ?></td>
</tr>
</table>
</td>
</tr>
</table>
<table class="sel">
<tr>
<th class="sel">Lfd.</th>
<th class="sel">Pl.Nr.</th>
<th class="sel">Mitarbeiter</th>
<th class="sel">Periode</th>
<th class="sel">Plan</th>
<th class="sel">IST</th>
<th class="sel">Taskname</th>
<th class="sel">Notiz</th>
<th class="sel">Status</th>
<th class="sel">Hash</th>
</tr>
<?
$i = 0;
while ($i < $anz) {
$data = fetchDB($result, $i);
$tmp_lfd = $data[0];
$mi_nname = $data[1];
$mi_vname = $data[2];
$tmp_periode = $data[3];
$tmp_plan = $data[4];
$tmp_ist = $data[5];
$tmp_taname = $data[6];
$tmp_notiz = $data[7];
$tmp_status = $data[8];
$tmp_hash = $data[9];
$tmp_plnum = $data[10];
echo "<tr>\n<td class=\"selnum\">$tmp_lfd</td>\n";
echo "<td class=\"sel\">$tmp_plnum</td>\n";
echo "<td class=\"sel\">$mi_nname $mi_vname</td>\n";
echo "<td class=\"sel\">" . gmdate($datetime, $tmp_periode) . "</td>\n";
echo "<td class=\"selnum\">" . FormatNum($tmp_plan, 3) . "</td>\n";
echo "<td class=\"selnum\">" . FormatNum($tmp_ist, 3) . "</td>\n";
echo "<td class=\"sel\">$tmp_taname</td>\n";
echo "<td class=\"sel\">$tmp_notiz</td>\n";
echo "<td class=\"sel\">";
if ($tmp_status == 0)
echo "Projekt";
else
echo "Konto";
echo "</td>\n";
echo "<td class=\"sel\">$tmp_hash</td>\n</tr>\n";
$i++;
}
echo "</table>";
return true;
}
?>