Location: PHPKode > projects > TheoPlan > pm/tempplan.inc
<?
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&auml;lt f&uuml;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&uuml;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;
}
?>
Return current item: TheoPlan